PostgreSQL Indexes: When They Help and When They Hurt

At work I spend a lot of time tuning PostgreSQL queries for our production systems. The biggest lesson: an index is a trade — faster reads in exchange for slower writes and more storage.

Read the plan, don't guess

Before adding an index, run EXPLAIN ANALYZE and look at where the time actually goes. A sequential scan on a small table is fine; on a million-row table it is not.

Composite index order matters

An index on (status, created_at) helps queries that filter by status first. The column you filter by most selectively usually belongs on the left.

Watch the write cost

Every INSERT and UPDATE has to maintain every index. On write-heavy tables, fewer, well-chosen indexes beat a pile of speculative ones.