Postgres Indexing Best Practices: B-tree, GIN, and Partial Indexes

Postgres indexes can improve selected reads while adding write and storage costs. Choose indexes from observed query patterns and check whether the planner uses them.

Understanding Postgres Index Types

B-tree handles many ordinary query patterns. GIN, partial, and covering indexes address other cases described below.

B-tree Indexes

B-tree is the default. It handles equality, range queries, and sorting efficiently. If you run queries with =, >, <, BETWEEN, LIKE 'prefix%', or ORDER BY, B-tree is the right choice.

-- Typical B-tree index: fast lookups by status and created_at range
CREATE INDEX ON orders (status, created_at DESC);

Column order in a composite B-tree index matters. Put the column with the highest cardinality that appears in equality conditions first. A query filtering on status = 'active' and sorting by created_at will use (status, created_at) efficiently; the reverse order will not.

GIN Indexes

GIN (Generalized Inverted Index) is designed for composite values: arrays, JSONB, and full-text search vectors. When a query asks "does this array contain X?" or "does this JSON document have this key?", B-tree cannot help. GIN can.

-- Index a JSONB column for containment queries
CREATE INDEX ON events USING GIN (payload);
 
-- Query that benefits from the GIN index
SELECT * FROM events WHERE payload @> '{"type": "purchase"}';

GIN indexes are larger and slower to update than B-tree. Use them deliberately, not as a default for every JSONB column.

Partial Indexes

A partial index indexes only the rows that match a WHERE condition. This is one of the most underused tools in Postgres.

-- Only index active users — ignore the 80% that are deactivated
CREATE INDEX ON users (email) WHERE deactivated_at IS NULL;

The index is smaller, faster to scan, and cheaper to maintain because inactive users never touch it. If your queries almost always filter by a fixed condition, a partial index may reduce the work needed for those queries.

Covering Indexes (INCLUDE)

A covering index stores additional columns alongside the index key. If a query needs only those columns, Postgres can answer it entirely from the index without touching the heap (the main table storage). This is called an index-only scan.

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

Now a query fetching status and total_cents for a tenant's recent orders may use an index-only scan when visibility checks permit it. Covering indexes use more disk, so apply them to queries on hot paths where the read-to-write ratio is high.

Postgres Indexing Best Practices

1. Always Index Foreign Keys

Postgres does not automatically index foreign key columns. A join from order_items to orders on order_id will do a sequential scan on order_items unless you have an index:

CREATE INDEX ON order_items (order_id);

This is one of the most common performance mistakes in Postgres. Run the following query to find unindexed foreign keys in your schema:

SELECT conrelid::regclass AS table,
       conname AS constraint,
       a.attname AS column
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
WHERE c.contype = 'f'
AND NOT EXISTS (
  SELECT 1 FROM pg_index i
  WHERE i.indrelid = c.conrelid
  AND c.conkey[1] = ANY(i.indkey)
);

2. Create Indexes Concurrently in Production

A standard CREATE INDEX blocks writes while it builds the index. In production, use CONCURRENTLY:

CREATE INDEX CONCURRENTLY ON orders (customer_id);

The concurrent build is slower and cannot run inside a transaction, but it does not block inserts or updates. Never add an index to a busy table without it.

3. Audit Index Usage

Postgres tracks index scans in pg_stat_user_indexes. Indexes with zero or near-zero scans are candidates for removal:

SELECT schemaname,
       tablename,
       indexname,
       idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Drop unused indexes. They impose write overhead with no read benefit. Reset statistics after major schema changes so you are reading current data: SELECT pg_stat_reset();.

4. Match Index Column Order to Query Patterns

A composite index (a, b, c) supports queries filtering on a, a AND b, and a AND b AND c. It does not efficiently support queries filtering only on b or c. Map your actual query patterns before building composite indexes.

5. Watch for Index Bloat

Postgres indexes accumulate dead tuples from updates and deletes. Over time, an index can become several times larger than necessary. Monitor bloat with extensions like pgstattuple, and run REINDEX CONCURRENTLY on indexes that have grown disproportionately.

Common Indexing Mistakes

• Indexing every column by default. More indexes mean more write overhead. Add indexes in response to measured slow queries, not as defensive pre-optimization.
• Forgetting to use CONCURRENTLY in production. A single unthrottled CREATE INDEX can lock a table long enough to cause downtime.
• Using a partial index with a non-immutable expression. The condition in a partial index must be stable. Avoid expressions involving now() or other volatile functions.
• Ignoring EXPLAIN (ANALYZE, BUFFERS) after adding an index. Verify the planner actually uses your new index. Sometimes the planner correctly ignores an index because a sequential scan is cheaper.

A Practical Indexing Workflow

1. Identify slow queries using pg_stat_statements or slow query logs.
2. Run EXPLAIN (ANALYZE, BUFFERS) to understand the current plan.
3. Identify the predicate or join condition driving the slowness.
4. Build the appropriate index type using CONCURRENTLY.
5. Re-run EXPLAIN to confirm the planner uses it.
6. Monitor pg_stat_user_indexes monthly to prune unused indexes.

Discuss Postgres query and index performance with LinkIT.