Postgres Indexes That Actually Matter

Indexes Aren't Free

Every index speeds up reads but slows down writes and takes disk space. Adding them blindly is the most common Postgres performance mistake. Start with no indexes and add them based on evidence.

When You Definitely Want an Index

• Foreign keys. Postgres doesn't auto-index them. If you ever JOIN or filter by a foreign key, add one.
• Columns used in `WHERE` clauses on big tables.
• Columns used in `ORDER BY` when you're paginating.
• Unique constraints. Postgres creates these automatically for @unique columns.

When to Skip

• Small tables (< 10k rows). Sequential scans are faster than index lookups.
• Low-cardinality columns (e.g. status with two values). An index on a column where half the rows match won't help.
• Columns you filter on once a week from an admin script. The write cost isn't worth it.

Finding Missing Indexes

The easiest way: run EXPLAIN ANALYZE on your slow query.

EXPLAIN ANALYZE
SELECT * FROM posts WHERE author_id = 42 ORDER BY created_at DESC LIMIT 20;

Look for Seq Scan on a big table — that's a signal you're missing an index. After adding the index, you should see Index Scan