.tip

Mar 7, 2026 · one of the small things

Always run EXPLAIN with ANALYZE and BUFFERS

Plain EXPLAIN shows you the planner's guess. EXPLAIN (ANALYZE, BUFFERS) shows what actually happened, including how many pages came from cache and how many from disk. Bad plans hide in the gap between estimated and actual row counts.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

If the actual rows are ten times the estimate, run ANALYZE on the table before you change the query.

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