Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 37 ยท Performance and Patterns

Indexes, Query Plans and Connection Pooling

Declare indexes in your models, read EXPLAIN QUERY PLAN output, design composite indexes, and tune pool_size, max_overflow, pool_pre_ping and pool_recycle.

When a single query is slow, the usual cause is that the database must read every row of a table to find the few you asked for. An index is a sorted lookup structure that lets it jump straight to the matching rows. Declaring the right indexes is the biggest single performance lever in most applications, and SQLAlchemy lets you declare them in your models so Alembic can create them.

Declaring indexes

Three ways, from simplest to most flexible:

Python
from sqlalchemy import Index, String
from sqlalchemy.orm import Mapped, mapped_column

class Order(Base):
    __tablename__ = "order_"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(120), index=True)        # 1. single column
    sku: Mapped[str] = mapped_column(String(40), unique=True)          # unique constraint + index
    customer_id: Mapped[int]
    status: Mapped[str]
    created: Mapped[str]

    __table_args__ = (
        Index("ix_order_customer_status", "customer_id", "status"),    # 2. composite
        Index("ix_order_created", "created", postgresql_where=text("status = 'open'")),  # 3. partial
    )

(The partial index needs from sqlalchemy import text; postgresql_where and sqlite_where restrict the index to matching rows on those databases.)

Primary keys and unique=True columns get an index automatically. Foreign key columns do not on most databases, so index them yourself when you join or filter on them often.

Read the query plan

Every database can tell you how it would run a query. In SQLite it is EXPLAIN QUERY PLAN; PostgreSQL and MySQL use EXPLAIN (and EXPLAIN ANALYZE to run it for real). The key words to look for are SCAN (reads the whole table) and SEARCH ... USING INDEX (jumps through an index).

Python Before and after adding an index

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The plan changes from a full scan to a search using ix_order_customer_status. With 20,000 rows you will not feel the difference; with 20 million you will.

Designing composite indexes

A composite index on (customer_id, status) is sorted by customer first, then status within each customer. That gives it three rules of thumb:

  • Leftmost prefix. It helps queries that filter on customer_id alone, or on customer_id and status, but not on status alone.
  • Equality columns first, range column last. For WHERE customer_id = ? AND created > ?, use (customer_id, created).
  • ORDER BY can use the index if the sort columns follow the equality columns: (customer_id, created) serves WHERE customer_id = ? ORDER BY created.
flowchart LR
    Q["WHERE customer_id = 42<br/>AND status = 'open'"] --> I["Index (customer_id, status)<br/>sorted tree"]
    I --> R["Row pointers<br/>few rows"]
    R --> T[("Table")]

The index narrows the search to a handful of row pointers; the table is only touched for those rows.

Indexes are not free: each one costs disk space and slows every INSERT, UPDATE and DELETE on its columns. Index what your slow queries filter, join and sort on. Do not index every column. Low-cardinality columns such as a boolean rarely help on their own.

Patterns that quietly defeat indexes

Python Functions on a column prevent index use

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Wrapping the column in a function (lower(email)) or using a leading wildcard usually forces a scan. Fixes: store a normalized column, create a functional index (Index("ix_lower_email", func.lower(User.email)), supported by PostgreSQL and SQLite), or use full-text search for substring needs. Note that SQLite's LIKE can only use an index for prefix patterns under specific collation settings, so always check the plan on your real database.

Connection pooling

Opening a database connection is expensive (network, authentication, TLS), so create_engine keeps a pool of connections and lends them out. The defaults suit small apps; for production tune four options.

Option Default Meaning
pool_size 5 Connections kept open
max_overflow 10 Extra connections allowed under load, closed afterwards
pool_timeout 30 s How long to wait for a free connection before an error
pool_recycle -1 (never) Replace connections older than N seconds (set below the server's idle timeout, for example 1800)
pool_pre_ping False Test each connection with a cheap ping when borrowed; recovers from dropped connections
Python
engine = create_engine(
    "postgresql+psycopg://app@db/prod",
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True,
    pool_recycle=1800,
)

Size the pool from your database's max_connections divided by the number of app processes. Ten web workers with pool_size=10, max_overflow=20 can open 300 connections, which will exhaust a default PostgreSQL server (100). If the numbers do not fit, put a pooler such as PgBouncer in front and use NullPool in the app.

The SQLite in-memory database used in this course uses a special single-connection pool, so pool settings only matter with server databases.

The most common pool bug is connection leaks: a session that is never closed keeps its connection checked out. A with Session(...) block everywhere prevents it. Symptoms are QueuePool limit of size 5 overflow 10 reached errors under load.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In an EXPLAIN QUERY PLAN output, which word means the database reads the whole table?

    SCAN is a full table read, while SEARCH ... USING INDEX jumps through an index.
  2. 2. You have an index on (customer_id, status). Which query can use it?

    A composite index serves its leftmost prefix, so filtering by customer_id works; status alone does not.
  3. 3. Why can WHERE lower(email) = ? ignore an ordinary index on email?

    The expression result is not what the index is sorted by.
  4. 4. What does pool_pre_ping=True do?

    A cheap test on checkout avoids errors from connections closed by firewalls or server restarts.

Summary

  • Declare indexes with index=True, unique=True or Index(...) in __table_args__.
  • Read the query plan: SCAN means a full read, SEARCH USING INDEX means an index lookup.
  • Composite indexes follow the leftmost-prefix rule: equality columns first, range last.
  • Functions on columns and leading wildcards often defeat indexes.
  • Tune pool_size, max_overflow, pool_recycle and pool_pre_ping; always close sessions to avoid leaks.

Next: The Repository and Unit of Work Patterns