PostgreSQL Indexing for Real-World Performance

Overview

Indexing is the highest-leverage performance work in most apps. The trick is matching the index type to the query shape — and proving it with EXPLAIN ANALYZE.

B-tree

create index idx_orders_tenant_created
  on orders (tenant_id, created_at desc);

Composite order matters: equality columns first, then range/sort columns.

GIN

create index idx_docs_tags on documents using gin (tags);

Use GIN for array and JSONB containment queries.

Partial

create index idx_open_jobs on jobs (created_at)
  where status = 'open';

Reading EXPLAIN

explain analyze
select * from orders
where tenant_id = 't1'
order by created_at desc
limit 20;

Watch for Seq Scan on large tables — that's your signal to index.

FAQ

See the FAQ section above.