LLM Best Practices

Postgres: Indexes

4 min read

View markdown source

Overview

Pick the index for the access pattern, not the column type. Postgres 17 ships B-tree, hash, GIN, GiST, SP-GiST, and BRIN; each wins a different shape of query. This page covers the five that matter, plus partial, expression, and covering indexes, and the production rules for adding them safely. The umbrella rules live in postgres.

Use B-tree by default for equality, range, and order

B-tree handles =, <, >, BETWEEN, IN, IS NULL, and ORDER BY on scalar columns. It is the implicit type when you write CREATE INDEX with no USING clause.

CREATE INDEX orders_user_id_created_at_idx
  ON orders (user_id, created_at DESC);

Order multi-column B-trees by selectivity and access pattern: the column you filter equality on first, then the range or sort column. A B-tree on (a, b) serves queries on a alone, but not on b alone; build the prefix you actually filter.

Reach for GIN on JSONB, full-text, and arrays

GIN indexes are inverted lists. Use them when a single row has many searchable values inside one column.

  • JSONB containment: CREATE INDEX events_payload_gin ON events USING GIN (payload jsonb_path_ops);. The jsonb_path_ops operator class is smaller and faster than the default when you only need @>.
  • Full-text search: index to_tsvector('english', body); see postgres-full-text-search.
  • Arrays: CREATE INDEX tags_gin ON posts USING GIN (tags); supports && and @> on text[] and int[].

GIN is slow to build and slow to update. Bulk loads run faster with the index dropped, then recreated.

Reach for GiST for ranges and spatial data

GiST is a balanced tree that supports overlap and nearest-neighbor queries. Use it when the column is a range, geometry, or tsrange constraint.

CREATE INDEX bookings_during_gist
  ON bookings USING GiST (during);
 
ALTER TABLE bookings
  ADD CONSTRAINT no_overlap EXCLUDE USING GiST (room_id WITH =, during WITH &&);

GiST also powers pg_trgm similarity search and PostGIS geometry indexes. See postgres-full-text-search for the trigram pattern.

Reach for BRIN on huge append-only tables

BRIN stores summaries per block range. Indexes are tiny (kilobytes for terabyte tables) and only useful when physical row order correlates with the indexed column. The classic case is time-series append.

CREATE INDEX events_created_at_brin
  ON events USING BRIN (created_at) WITH (pages_per_range = 32);

Match BRIN with CLUSTER or with a partitioning scheme that keeps inserts in order; see postgres-partitioning. On randomly inserted data, BRIN degrades to a sequential scan.

Add partial indexes for skewed predicates

When a predicate covers most rows, exclude them from the index. The index gets smaller and writes get cheaper.

CREATE INDEX users_active_email_idx
  ON users (email) WHERE deleted_at IS NULL;

Partial indexes are the standard answer to soft-delete patterns, feature flags, and status enums where one value dominates. Match the WHERE clause exactly; the planner only uses the partial index when the query predicate is a subset.

Add expression indexes for derived lookups

Index the expression the query filters on, not the raw column.

CREATE INDEX users_lower_email_idx ON users (lower(email));
-- matches: WHERE lower(email) = lower($1)

Use expression indexes for case-insensitive lookups, type casts, and JSON path extracts. The expression in the WHERE clause must match the indexed expression byte-for-byte for the planner to pick it.

Use covering indexes (INCLUDE) for index-only scans

Add non-key columns with INCLUDE to enable index-only scans. The planner skips the heap when every projected column lives in the index.

CREATE INDEX orders_user_id_created_at_idx
  ON orders (user_id, created_at DESC) INCLUDE (total_cents, status);

INCLUDE columns do not affect ordering or uniqueness. Use them for hot read paths; do not add every column to every index.

Build indexes CONCURRENTLY in production

Every CREATE INDEX takes an ACCESS EXCLUSIVE lock unless you use CONCURRENTLY. On a hot table that blocks every reader and writer until the build finishes.

CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders (user_id);

CONCURRENTLY cannot run inside a transaction. Disable the per-migration transaction wrapper; see migrations for the framework flags. A failed concurrent build leaves an INVALID index; drop it before retrying.

Do not index every WHERE clause

Indexes cost write amplification, vacuum work, and storage. The anti-pattern: a table with twenty single-column indexes added one per slow query.

  • Prefer composite indexes that serve several queries. (user_id, created_at) covers filters on user_id and on user_id plus a date range.
  • Drop indexes with zero scans in pg_stat_user_indexes after a release cycle.
  • Validate every new index against postgres-explain before merging. For the end-to-end slow-query debug loop, see debug-postgres-slow-query.

Graph View

Backlinks