Indexes — what they are and when to add them

An Index Is a Sorted Copy of a Column

Without an index, the database scans every row to find matches — a sequential scan. An index stores column values in sorted order alongside pointers back to the rows, enabling binary search instead of a full scan.

-- Index on a frequently filtered column
CREATE INDEX idx_orders_customer_id
  ON orders(customer_id);

-- Composite index: useful when queries filter on both columns
CREATE INDEX idx_orders_status_created
  ON orders(status, created_at);

-- Check what the planner chooses
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Always Index Foreign Keys

PostgreSQL does not automatically index foreign key columns. An un-indexed foreign key means a full table scan every time you join or look up related rows. Add an index on every foreign key column.

When Not to Index

Indexes slow down writes — every INSERT, UPDATE, and DELETE must also update the index. Don't index columns with low selectivity (like a boolean is_active where 99% of rows are true). Don't index small tables — a sequential scan is faster than index overhead for a few hundred rows.