Cheatsheet for Postgres index types via SQLAlchemy.

Type decision

TypeBest forExample
B-tree (default)=, ranges, sortemail, created_at
GINJSONB, arrays, trigrams, FTSpayload, tags, tsvector
GiSTRanges, geometry, exclusionperiod TSTZRANGE
BRINHuge append-only time-seriesevents.occurred_at
HASHEquality-only, large colrarely needed

B-tree (default)

# Inline
email: Mapped[str] = mapped_column(unique=True, index=True)

# Composite
__table_args__ = (
    Index("ix_users_active_email", "active", "email"),
)

GIN — JSONB

__table_args__ = (
    Index(
        "ix_events_payload_gin",
        "payload",
        postgresql_using="gin",
        postgresql_ops={"payload": "jsonb_path_ops"},
    ),
)

jsonb_path_ops is faster + smaller for @> queries.

GIN — arrays

__table_args__ = (
    Index("ix_posts_tags_gin", "tags", postgresql_using="gin"),
)

GIN — trigram (substring search)

CREATE EXTENSION pg_trgm;

__table_args__ = (
    Index(
        "ix_users_name_trgm",
        "name",
        postgresql_using="gin",
        postgresql_ops={"name": "gin_trgm_ops"},
    ),
)

Now LIKE '%alice%' uses the index.

GIN — full-text (tsvector)

__table_args__ = (
    Index("ix_posts_search_gin", "search", postgresql_using="gin"),
)

GiST — ranges

__table_args__ = (
    Index("ix_bookings_period_gist", "period", postgresql_using="gist"),
)

Required for exclusion constraints on range overlaps.

GiST — geometry / PostGIS

from geoalchemy2 import Geometry

location: Mapped = mapped_column(Geometry("POINT", srid=4326))

__table_args__ = (
    Index("ix_places_loc_gist", "location", postgresql_using="gist"),
)

BRIN — huge append-only

__table_args__ = (
    Index("ix_events_occurred_brin", "occurred_at", postgresql_using="brin"),
)

Tiny (MBs for billions of rows). Slower than B-tree but trivial maintenance. Best when natural row order correlates with indexed value (auto for append-only timestamp).

HASH

__table_args__ = (
    Index("ix_events_type_hash", "event_type", postgresql_using="hash"),
)

PG 10+ is crash-safe. Rarely a clear win over B-tree.

Partial index (predicate)

__table_args__ = (
    Index(
        "ix_users_active",
        "email",
        postgresql_where=text("active = true"),
    ),
)

Only indexes rows matching. Queries that include the same predicate use it.

Expression index

__table_args__ = (
    Index("ix_users_lower_email", text("lower(email)")),
)

For WHERE lower(email) = ... queries.

Unique partial (soft-delete pattern)

__table_args__ = (
    Index(
        "ix_users_email_active",
        "email",
        unique=True,
        postgresql_where=text("deleted_at IS NULL"),
    ),
)

Allows reuse of an email after soft-delete.

INCLUDE columns (covering)

__table_args__ = (
    Index(
        "ix_orders_user",
        "user_id",
        postgresql_include=["status", "total"],
    ),
)

status and total stored in index leaves. Index-only scans for queries that need them.

CREATE INDEX CONCURRENTLY (migrations)

def upgrade():
    op.execute("CREATE INDEX CONCURRENTLY ix_users_email ON users (email)")

def downgrade():
    op.execute("DROP INDEX CONCURRENTLY ix_users_email")

In alembic.ini:

[alembic]
transaction_per_migration = false

CONCURRENTLY can’t run in a transaction.

REINDEX (for bloat)

REINDEX INDEX CONCURRENTLY ix_users_email;
REINDEX TABLE CONCURRENTLY users;

Online; reclaims bloat in heavy-write indexes.

Multi-column rules of thumb

# Query: WHERE a = ? AND b = ?
Index("ix_t_a_b", "a", "b")          # combined filter

# Query: WHERE a = ? OR b = ?
Index("ix_t_a", "a")
Index("ix_t_b", "b")                 # separate; planner can combine

Order matters for B-tree composite: leftmost prefix usable, rightmost requires the previous.

Size monitoring

SELECT
    schemaname, tablename, indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC LIMIT 20;

Unused indexes

SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

Drop indexes never scanned.

btree_gin extension (for multi-column GIN)

CREATE EXTENSION btree_gin;

Lets you build composite indexes like (category, payload) with GIN.

__table_args__ = (
    Index(
        "ix_events_cat_payload_gin",
        "category", "payload",
        postgresql_using="gin",
    ),
)

Common mistakes

  • No index on FK — slow joins / cascade deletes.
  • One per column instead of composite for combined predicates.
  • Big GIN on rarely-queried JSONB — expensive to maintain.
  • CREATE INDEX without CONCURRENTLY on hot tables.
  • Forgetting idx_scan = 0 cleanup.

Read this next

If you want my index-audit script + Prometheus exporter, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .