Postgres Indexes That Actually Matter at Scale

Your Postgres is slow. You're tempted to add a read replica, bump the instance size, or migrate to "something faster."

Don't. 90% of slow Postgres queries get fixed by the same three index patterns. None of them are exotic.

The default index is wrong half the time

When most engineers add an index, they add a B-tree on a single column:

CREATE INDEX idx_users_email ON users(email);

Fine for WHERE email = 'x'. Useless for almost everything else.

Real queries look like this:

SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

A single-column index on customer_id filters, then Postgres has to fetch every matching row, filter by status, sort, and limit. On a customer with 10k orders, that's 10k disk reads.

Pattern 1: composite indexes in query order

CREATE INDEX idx_orders_customer_status_created
  ON orders(customer_id, status, created_at DESC);

Now the same query reads ~20 rows. The whole filter, sort, and limit happen via the index.

The order matters. Equality columns first, then range/sort columns. Get it wrong and Postgres can't use the index for sorting.

How to verify: EXPLAIN ANALYZE. Look for "Index Scan using idx_..." with no "Sort" node above it.

Pattern 2: partial indexes for hot subsets

99% of your orders table is status = 'completed'. The query above only ever wants status = 'pending'.

CREATE INDEX idx_orders_pending
  ON orders(customer_id, created_at DESC)
  WHERE status = 'pending';

This index is 100x smaller. It fits in memory. Lookups are nearly free.

Use partial indexes whenever:

• A column has skewed distribution (90%+ one value)
• Queries always filter for the rare value
• The "completed/cancelled/expired" pattern

Pattern 3: covering indexes to skip the heap

Postgres index lookups return row pointers. To return the actual row, it has to fetch from the heap (table data). For wide tables this is expensive.

CREATE INDEX idx_orders_listing
  ON orders(customer_id, status, created_at DESC)
  INCLUDE (total_cents, item_count);

If your query only needs customer_id, status, created_at, total_cents, item_count, Postgres reads from the index and never touches the heap. Index-only scan.

Use this for hot list endpoints. Don't use it for everything — you're duplicating data into the index.

What not to do

Don't index every foreign key by reflex. Postgres doesn't auto-index FKs but you only need them indexed if you query by them or delete from the parent table.

Don't add indexes to small tables. Under ~10k rows, sequential scan is faster.

Don't index high-write tables aggressively. Each index = write amplification. Profile first.

Don't index columns with low cardinality alone. WHERE deleted = false on a single-column boolean index is worse than a sequential scan. Use it as part of a composite or partial index.

How to find the missing indexes

SELECT
  schemaname,
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan,
  seq_tup_read / GREATEST(seq_scan, 1) AS avg_tup_per_scan
FROM pg_stat_user_tables
WHERE seq_scan > 1000
ORDER BY seq_tup_read DESC
LIMIT 20;

Tables at the top: high seq_scan, high rows-per-scan. Those are the ones missing indexes.

For specific slow queries: auto_explain extension. Set auto_explain.log_min_duration = '500ms'. Every slow query lands in the log with its plan.

The boring truth

Postgres is faster than your problem. Your problem is that you're missing the right index, or you have one but it's in the wrong column order. Fix the index, the migration to "something faster" disappears.

Add pg_stat_statements. Look at the top 10 queries by total time. Eight of them have a missing or wrong-order index. Fix those before you touch anything else.