JuniorTechnicalSQL

Which columns should you index and which should you not?

Try answering first

Model answer

An index speeds up reads on a column but costs disk space and slows every INSERT, UPDATE and DELETE, because the index must be maintained too. So index what queries actually use.

Good candidates:

Columns in frequent WHERE filters that return a small share of the table.
Join columns, especially foreign keys. PostgreSQL does not index foreign keys automatically, while MySQL InnoDB does.
Columns used in ORDER BY or GROUP BY on large result sets, ideally as part of a composite index.

Poor candidates:

Low-selectivity columns such as a boolean or a status with 3 values, because the planner will scan the table anyway. A partial index can still help when one value is rare.
Very small tables, where a scan is already cheap.
Columns that are rarely queried, or written constantly.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Look at real queries and their plans, not at column names, and remove indexes that statistics show are never used.

Key points

Index columns used in filters, joins and sorts
Every index slows writes and uses storage
Low-selectivity columns rarely benefit
Foreign keys need an index in PostgreSQL

Likely follow-ups

How do you find indexes that are never used?

Common mistakes

Indexing every column just in case
Forgetting to index foreign key columns used in joins