.tip

Mar 10, 2026 · one of the small things

Index the rows you query, with a WHERE on the index

A queue table has 90 million rows, and 400 of them are pending. An index on status covers all 90 million. A partial index covers just the 400.

CREATE INDEX jobs_pending ON jobs (run_at) WHERE status = 'pending';

The index stays tiny, inserting finished rows never touches it, and the planner uses it whenever the query's WHERE matches the index's. It's the most underused feature in Postgres.

postgresqlperformance

~/tips/today — 2026-10-19

~cat tips/today

Never build an index on a live table without CONCURRENTLY

postgresqlmigrations

A plain CREATE INDEX takes a lock that blocks every write to the table for the whole build. On a big table that means minutes of outage that look like a hung app.

CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id);

It's slower and can't run inside a transaction, so you'll have to tell your migration tool. If it fails halfway, it leaves an invalid index behind, so check pg_index.indisvalid and drop it before you retry.

↵ open y yesterday l ls /tips

esc close