PostgreSQL Indexes: A Practical Guide for Backend Engineers

The problem

A query that was fast at 10,000 rows becomes a sequential scan over 10 million rows, and a request that used to take 5ms now takes 800ms. The usual reflex is "add an index" — but indexes are not free, and the wrong index (or too many of them) can make write-heavy tables slower without measurably helping any query. Indexing is a trade-off between read latency, write latency, and storage, and making that trade-off well requires understanding what an index actually buys you for a given query shape.

Why it matters

Every table without the right index on a hot query path is a scaling cliff waiting to happen:

• A WHERE clause on an unindexed column forces a sequential scan — every row in the table gets read, even if only one row matches.
• Foreign key columns without an index make JOINs and cascading deletes scan the referencing table in full.
• On the other hand, every index you add is maintained on every INSERT, UPDATE, and DELETE — indexes you don't query for still cost write throughput and disk space.

Getting indexing right is one of the highest-leverage things a backend engineer can do to a slow system, and one of the easiest to get wrong in either direction.

How indexes work

By default, CREATE INDEX builds a B-tree: a balanced tree structure sorted by the indexed column(s), where a lookup costs roughly O(log n) comparisons instead of scanning every row. PostgreSQL's query planner chooses between an index scan (walk the B-tree, then fetch matching rows), an index-only scan (answer the query from the index alone, when every needed column is in the index), a bitmap heap scan (for less selective queries), or a sequential scan (read the whole table) — based on cost estimates, not on whether an index merely exists.

A B-tree index only helps queries that filter or sort on a prefix of its columns, using operators it supports (=, <, >, BETWEEN, IN, and ORDER BY on the same columns). It does not help LIKE '%term%', containment checks on jsonb, or array membership — those need different index types.

Composite indexes and column order

A composite index is sorted by its first column, then its second column within each value of the first, and so on — the same way a phone book is sorted by last name, then first name.

CREATE INDEX idx_orders_customer_status_created
    ON orders (customer_id, status, created_at);

This index serves:

-- Uses the full index
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC;
 
-- Uses only the customer_id prefix
SELECT * FROM orders WHERE customer_id = 42;

It does not meaningfully serve a query that filters on status alone — the leftmost-prefix rule means PostgreSQL can only use a composite index starting from its first column. If you regularly query status by itself, that needs its own index (or status needs to be first in a different composite index), not a reordering that breaks the customer_id queries.

Partial indexes

Most rows in a table are often irrelevant to the queries that matter. A partial index only indexes the rows matching a WHERE clause, which keeps it smaller and cheaper to maintain:

-- Most order lookups only care about orders that aren't soft-deleted
CREATE INDEX idx_orders_active
    ON orders (customer_id, created_at)
    WHERE deleted_at IS NULL;
 
-- Most queues only scan pending work
CREATE INDEX idx_jobs_pending
    ON jobs (run_at)
    WHERE status = 'pending';

For the jobs table, if 95% of rows are completed and only a small, constantly-changing fraction are pending, a partial index keeps the index small regardless of how large the completed history grows — and PostgreSQL will only use it for queries whose WHERE clause implies the same condition.

Unique indexes vs. UNIQUE constraints

ALTER TABLE ... ADD CONSTRAINT ... UNIQUE and CREATE UNIQUE INDEX create the same underlying structure — a unique B-tree index — but a constraint also registers intent in the schema (visible in \d table, usable by tools that introspect constraints) and participates in ON CONFLICT upsert targets:

CREATE UNIQUE INDEX idx_users_email ON users (lower(email));
 
INSERT INTO users (email, name) VALUES ($1, $2)
ON CONFLICT (email) DO NOTHING; -- requires a unique index/constraint on email

Note the lower(email) expression index above — it enforces case-insensitive uniqueness and also speeds up case-insensitive lookups, but only if queries filter using the same expression: WHERE lower(email) = lower($1).

GIN indexes for jsonb, arrays, and full-text search

B-tree indexes don't help containment queries. For jsonb columns, array columns, or full-text search, use a GIN (Generalized Inverted Index):

-- Querying a jsonb column for key/value containment
CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);
 
SELECT * FROM events
WHERE payload @> '{"type": "payment_failed"}';
 
-- Full-text search over article content
CREATE INDEX idx_articles_search
    ON articles USING GIN (to_tsvector('english', body));
 
SELECT * FROM articles
WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'graceful shutdown');

jsonb_path_ops produces a smaller, faster index than the default GIN operator class when your queries only use @> containment, at the cost of not supporting key-existence operators like ?.

Selectivity: why some indexes don't help

An index only helps if it lets PostgreSQL skip most of the table. Indexing a status column with only two possible values (active / inactive) split 50/50 rarely gets used — reading half the table via an index, with an extra lookup back to the heap for each row, is often more expensive than just scanning the table sequentially. The planner knows this and will often ignore such an index even though it exists.

Composite indexes sidestep this: pairing a low-selectivity column with a high-selectivity one (like customer_id) up front means the index is only ever walked within a narrow slice, where the low-selectivity column just adds precision.

Finding overhead and unused indexes

Every index is maintained on every write and consumes disk space and cache. Check what you're actually paying for and getting nothing from:

-- Indexes that have never been used since the last stats reset
SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

An index with idx_scan = 0 on a table that's been under real production traffic for weeks is a strong candidate to drop — it's pure write overhead with no read benefit.

Production considerations

Build indexes without blocking writes. A plain CREATE INDEX takes a lock that blocks concurrent writes to the table for the duration of the build. On a live production table, use:

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

CONCURRENTLY takes roughly twice as long and can't run inside a transaction block, but it doesn't block reads or writes. If it fails partway through, it can leave an invalid index behind — check pg_index.indisvalid and drop and retry if needed.

Watch for index bloat. Heavy UPDATE/DELETE churn on an indexed column leaves dead index entries that autovacuum reclaims, but under sustained load bloat can still accumulate. REINDEX CONCURRENTLY (PostgreSQL 12+) rebuilds an index without a long-lived exclusive lock.

Every foreign key needs an index — check, don't assume. PostgreSQL does not automatically index foreign key columns. An unindexed foreign key means every DELETE on the referenced table triggers a full scan of the referencing table to enforce the constraint.

Common mistakes

• Indexing every column on a table "just in case," which slows down every write for indexes that rarely serve a read.
• Wrong composite column order — indexing (status, customer_id) when every query filters customer_id first and only sometimes filters status means the index doesn't serve the common case as well as the reverse order would.
• Duplicate or redundant indexes — (customer_id) is redundant once (customer_id, status) exists, since the composite index already serves any query that only needs customer_id.
• Assuming an index helps because it exists. The planner ignores low-selectivity indexes; always verify with EXPLAIN ANALYZE rather than assuming a CREATE INDEX statement changed anything.
• Forgetting CONCURRENTLY on production tables, causing an avoidable write outage while an index builds.

Summary

Indexing is a targeted trade: pick B-tree for equality/range/sort queries, GIN for containment and full-text search, use composite indexes ordered by how queries actually filter, and use partial indexes to keep the index small when only a subset of rows matter. Every index has a write and storage cost, so add one because a real, measured query needs it, verify it's actually selective enough to be used, and periodically check pg_stat_user_indexes for indexes that are pure overhead.