PostgreSQL Indexing: A Practical Guide for App Developers

A query that feels instant on a few thousand rows in development can crawl on a few million in production, and the fix is usually an index. The hard part is knowing which index, in what column order, and what it costs on writes. This guide covers PostgreSQL indexing from an app developer's point of view: reading query plans, designing composite, covering, partial and expression indexes, choosing GIN or BRIN, and shipping indexes safely.

Examples use PostgreSQL 18 and a typical orders table.

What a B-tree index buys you

Without an index, answering WHERE customer_id = 42 means reading every row. A B-tree is a sorted copy of the indexed columns, stored as a shallow tree of pages: internal pages point down a level, leaf pages hold keys plus pointers to table rows, and each level is linked sideways so a scan can walk it in order. That gives you:

• Fast lookups: a few page reads from root to leaf, even on huge tables.
• Range scans: <, >, BETWEEN find the first match, then walk the leaves.
• Sorted output: B-tree is the only built-in index type that returns rows in order.

CREATE INDEX builds a B-tree by default. Primary keys and unique constraints get one automatically; foreign keys don't, and the referencing column usually deserves one.

Reading EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN shows what the planner intends. EXPLAIN (ANALYZE, BUFFERS) runs the query and reports what happened, including pages found in shared buffers (hit) versus read in (read). Because it really executes the statement, wrap an UPDATE or DELETE in BEGIN; ... ROLLBACK;.

Bitmap scans lose index order, so ORDER BY needs a separate sort. Here is an illustrative plan (example numbers, not a real system) for a customer's 20 latest paid orders, with only an index on customer_id:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Limit  (cost=4517.68..4517.73 rows=20 width=24) (actual time=39.313..39.320 rows=20.00 loops=1)
  Buffers: shared hit=212 read=975
  ->  Sort  (cost=4517.68..4519.80 rows=850 width=24) (actual time=39.311..39.316 rows=20.00 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        Buffers: shared hit=212 read=975
        ->  Bitmap Heap Scan on orders  (cost=13.43..4475.18 rows=850 width=24) (actual time=0.598..38.904 rows=900.00 loops=1)
              Recheck Cond: (customer_id = 42)
              Filter: (status = 'paid'::text)
              Rows Removed by Filter: 350
              Heap Blocks: exact=1180
              Buffers: shared hit=212 read=975
              ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..13.22 rows=1180 width=0) (actual time=0.412..0.412 rows=1250.00 loops=1)
                    Index Cond: (customer_id = 42)
                    Index Searches: 1
                    Buffers: shared hit=7
Planning Time: 0.214 ms
Execution Time: 39.402 ms

Read it inside out. The index found 1,250 entries. The heap scan visited 1,180 pages, 975 of them uncached, and discarded 350 non-paid rows; that's where the time went. Then the sort consumed 900 rows to return 20.

Also compare estimated and actual rows=; a large gap often means stale statistics, so run ANALYZE on the table. PostgreSQL 18 enables BUFFERS with ANALYZE automatically and prints row counts with two decimals; on older versions, spell BUFFERS out.

Composite indexes: getting column order right

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  status      text NOT NULL,
  total_cents bigint NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

My rule of thumb: equality columns first, then the range or sort column. For the query above:

CREATE INDEX orders_customer_status_created_idx
  ON orders (customer_id, status, created_at);

Every entry for (42, 'paid') now sits together, already sorted by created_at. PostgreSQL scans that slice backwards from the newest entry and stops after 20: no filter, no sort.

The multicolumn index docs give the precise rule: equality conditions on leading columns, plus an inequality on the first column without one, limit how much of the index is scanned. This leftmost-prefix rule decides what the index serves:

• customer_id = ?: yes.
• customer_id = ? AND status = ?: yes, already sorted by created_at.
• customer_id = ? AND status = ? AND created_at >= ?: yes, fully.
• customer_id = ? AND created_at >= ?: only customer_id reliably narrows the scan.
• status = ? alone: generally no. PostgreSQL 18's skip scan helps only when the skipped leading column has few distinct values.

Putting created_at first is the classic mistake: the scan walks entries in date order, checking customer and status one by one.

Covering indexes with INCLUDE

That index still visits the table for id and total_cents. PostgreSQL indexes point at a row's physical location, not its primary key, so id isn't free. INCLUDE stores non-key payload columns, so rebuilding the index like this lets it answer the query alone:

CREATE INDEX orders_customer_status_created_idx
  ON orders (customer_id, status, created_at)
  INCLUDE (id, total_cents);

The illustrative plan becomes:

Limit  (cost=0.43..5.12 rows=20 width=24) (actual time=0.031..0.046 rows=20.00 loops=1)
  Buffers: shared hit=4
  ->  Index Only Scan Backward using orders_customer_status_created_idx on orders  (cost=0.43..199.73 rows=850 width=24) (actual time=0.030..0.042 rows=20.00 loops=1)
        Index Cond: ((customer_id = 42) AND (status = 'paid'::text))
        Heap Fetches: 0
        Index Searches: 1
        Buffers: shared hit=4
Planning Time: 0.187 ms
Execution Time: 0.061 ms

Caveats:

• Included columns can't be searched on and don't count toward uniqueness. Only B-tree, GiST and SP-GiST support them.
• Index-only scans rely on the visibility map. Pages changed since the last VACUUM still need a table visit, shown as Heap Fetches.
• Wide payload columns bloat the index, and an oversized index entry makes inserts fail.

Partial indexes

A partial index covers only rows matching a predicate. If a job sweeps stale pending orders, and pending orders are a small slice of the table, index just that slice:

CREATE INDEX orders_pending_created_idx
  ON orders (created_at)
  WHERE status = 'pending';

The planner uses it only when it can prove the query's WHERE implies the predicate. A parameterized status = $1 can't be proven, so write the literal. Partial unique indexes also enforce uniqueness among live rows when you soft-delete, for example ON users (email) WHERE deleted_at IS NULL.

Expression indexes

If login runs WHERE lower(email) = lower($1), an index on email won't help. Index the expression:

CREATE UNIQUE INDEX users_email_lower_key ON users (lower(email));

Queries must use the same expression, and the unique version blocks emails that differ only in case. The expression is computed on every insert and non-HOT update, and its functions must be IMMUTABLE.

ORDER BY with LIMIT, and keyset pagination

When an index matches the ORDER BY, PostgreSQL reads the first N entries and stops. OFFSET erodes that: skipped rows are still computed, so page 500 costs far more than page 1. Keyset pagination seeks to where the last page ended:

CREATE INDEX orders_created_id_idx ON orders (created_at, id);

-- $1, $2 = created_at and id of the last row on the previous page
SELECT id, total_cents, created_at
FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The id tie-breaker makes the order unique, so rows are never skipped or repeated. The row comparison is lexicographic and usable as an index condition, and a backward scan satisfies the descending sort. You lose "jump to page 37", which infinite scroll doesn't need.

GIN and BRIN: when a B-tree is the wrong tool

GIN for jsonb, arrays and full-text search

GIN is an inverted index: one entry per element inside a value. Use it to search inside values:

CREATE INDEX products_attrs_gin ON products USING GIN (attributes jsonb_path_ops);
SELECT id FROM products WHERE attributes @> '{"color": "red"}';

The default jsonb_ops also supports key-exists operators (?, ?|, ?&); jsonb_path_ops drops them but is usually smaller and faster for containment. Arrays support &&, @> and <@, and full-text search indexes to_tsvector('english', body). GIN inserts are slower, softened by a pending list (fastupdate, on by default), and GIN can't return sorted rows.

BRIN for huge append-only tables

BRIN stores a summary, such as min and max, per range of adjacent pages (128 by default). It suits very large tables where a column tracks physical order, like created_at on an append-only events table:

CREATE INDEX events_created_brin ON events USING BRIN (created_at);

It's tiny but lossy: matching ranges are read in full and rechecked. If rows arrive out of order, ranges overlap and BRIN prunes far less.

What indexes cost

Write amplification. Every INSERT, and every UPDATE that isn't HOT, has to maintain each index on the table.

HOT updates. A heap-only tuple update skips index maintenance, but only if no indexed column changes (summarizing indexes like BRIN excepted) and the new version fits on the same page. So indexing updated_at on a busy table rules out HOT for every update. Lowering fillfactor (default 100) leaves room on each page. Compare n_tup_hot_upd with n_tup_upd in pg_stat_user_tables.

Bloat. Updates and deletes leave dead row versions, and mostly-emptied B-tree pages stay allocated. REINDEX INDEX CONCURRENTLY rebuilds without blocking writes.

Finding unused and missing indexes

Unused indexes tax writes for nothing. This lists non-unique indexes with zero scans:

SELECT s.relname, s.indexrelname, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;

Counters reset after an unclean shutdown and are tracked per server, so check replicas that serve reads before dropping anything. From PostgreSQL 16, last_idx_scan shows when an index was last used.

For missing indexes, look for tables with high seq_tup_read relative to idx_scan in pg_stat_user_tables, then use pg_stat_statements. It needs shared_preload_libraries (and a restart) plus CREATE EXTENSION pg_stat_statements:

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Sorting by total time surfaces cheap queries that run constantly. Then EXPLAIN each candidate.

CREATE INDEX CONCURRENTLY and its caveats

A plain CREATE INDEX allows reads but blocks writes until it finishes. On a busy table, write CREATE INDEX CONCURRENTLY instead. The docs list the caveats:

• It scans the table twice and waits for existing transactions, so it's slower.
• It can't run inside a transaction block, and many migration tools wrap each migration in one.
• A failure leaves an INVALID index that queries ignore but writes still maintain. Find it with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;, drop it and retry.
• A concurrent unique build can report violations to other sessions before it's ready.
• One concurrent build per table at a time; partitioned tables need per-partition builds.

A PostgreSQL indexing checklist

1. Capture EXPLAIN (ANALYZE, BUFFERS) with realistic parameters.
2. Order columns: equality, then range or sort.
3. Check whether an existing index already covers the leftmost prefix.
4. Consider INCLUDE, a partial predicate or an expression before widening.
5. Avoid indexing frequently updated columns that break HOT.
6. Build CONCURRENTLY, confirm it's valid, re-check the plan, and revisit idx_scan later.

Key takeaways

• Design indexes from the WHERE and ORDER BY clauses you actually run.
• In composite indexes, equality columns come first, range or sort last.
• INCLUDE, partial and expression indexes often beat a wider index.
• Prefer keyset pagination to large OFFSETs.
• GIN searches inside jsonb, arrays and text; BRIN suits huge, naturally ordered tables.
• Every index taxes writes, so prune unused ones.

Slow queries also show up as slow server responses, which drag down the metrics in my Core Web Vitals guide. For a full design where access patterns drive the schema, see the collaborative task board system design.