PostgreSQL Indexes

An index is a lookup structure that turns a full-table scan into a targeted seek. It is the single biggest lever on query performance.

CREATE INDEX idx_books_author ON books (author_id);
CREATE INDEX idx_books_price  ON books (price);
CREATE UNIQUE INDEX idx_books_isbn ON books (isbn);

What to index

• Foreign key columns (Postgres does not index them automatically).
• Columns used in WHERE, JOIN ... ON, and ORDER BY.
• Columns with high selectivity — many distinct values.

What not to index

• Small tables — a sequential scan is faster anyway.
• Columns with few distinct values (a boolean) unless used in a partial index.
• Every column "just in case" — each index slows down every INSERT/UPDATE/DELETE and consumes disk.

Composite indexes

Column order matters. This index:

CREATE INDEX idx_books_author_price ON books (author_id, price);

serves WHERE author_id = 5, and WHERE author_id = 5 AND price > 300 — but not WHERE price > 300 alone. Put the equality column first.

Partial indexes

Index only the rows you actually query — smaller and faster:

CREATE INDEX idx_active_books ON books (title) WHERE is_active = true;

Expression indexes

If you query with a function, index the same expression:

CREATE INDEX idx_books_lower_title ON books (lower(title));

-- now this can use the index
SELECT * FROM books WHERE lower(title) = 'palpasa cafe';

Index types

CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
CREATE INDEX idx_docs_data  ON documents USING GIN (data jsonb_path_ops);

EXPLAIN — check whether it's used

EXPLAIN ANALYZE
SELECT * FROM books WHERE author_id = 5;

Read the output for:

• Seq Scan — reading the whole table. Fine for small tables, a warning sign on large ones.
• Index Scan / Bitmap Heap Scan — the index is being used.
• Rows estimated vs actual — a big gap means the planner's statistics are stale; run ANALYZE books;.

Building indexes without downtime

CREATE INDEX locks the table against writes. On a live system:

CREATE INDEX CONCURRENTLY idx_books_author ON books (author_id);

It is slower and cannot run inside a transaction, but it doesn't block your application.

Maintenance

ANALYZE books;                       -- refresh planner statistics
REINDEX INDEX CONCURRENTLY idx_x;    -- rebuild a bloated index

-- find unused indexes
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan;

An index with idx_scan = 0 after weeks of production traffic is pure overhead — drop it.