PostgreSQL Indexing for Backend Developers
Short answer
Add an index on columns you filter or join on frequently (especially in
WHERE, JOIN, and ORDER BY). Use
EXPLAIN ANALYZE to confirm a query uses the index, and avoid
over-indexing — every index slows down INSERT/UPDATE.
Create a basic index
CREATE INDEX idx_orders_user_id ON orders (user_id);Check if a query uses it
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;If you see Index Scan, the index is used. If you see
Seq Scan, it isn't.
FAQ
When should I NOT add an index?
On small tables, on columns updated very frequently, or on low-cardinality columns where a sequential scan is already fast.
What is a composite index?
An index on multiple columns, e.g. (user_id, created_at), useful when queries filter on both together.