Postgres Indexing for Application Developers

Why developers should own indexing

Most slow queries in web applications are not caused by PostgreSQL being slow; they are caused by missing or mismatched indexes. Application developers write the queries, so they are best placed to design the indexes that support them. A few concepts cover the vast majority of real cases.

An index is a separate data structure that lets PostgreSQL find rows without scanning the whole table. The default B-tree index keeps values sorted, so it supports equality, range conditions, sorting and prefix lookups efficiently. Every index also has a cost: it uses disk and memory and must be maintained on every insert and on most updates.

Read the plan before you add an index

Run EXPLAIN (ANALYZE, BUFFERS) on the slow query, ideally against production-like data. ANALYZE actually executes the query and shows real row counts and timings; BUFFERS shows how much data was read. Be careful running ANALYZE on statements that modify data, or wrap them in a transaction you roll back.

• Seq Scan: the whole table is read, fine for small tables, a warning sign for large ones
• Index Scan: rows are found through an index, then fetched from the table
• Index Only Scan: all needed columns come from the index itself
• Bitmap Heap Scan: index matches are collected first, then table pages are read in order
• A big gap between estimated and actual rows suggests stale statistics; run ANALYZE on the table

Composite indexes and column order

A composite index on several columns is used from the left. An index on (tenant_id, status, created_at) helps queries filtering on tenant_id, on tenant_id and status, or on all three, but not queries filtering only on status. As a rule of thumb, put columns compared with equality first and the column used for a range or sort last.

For a common query such as WHERE tenant_id = $1 AND status = 'open' ORDER BY created_at DESC LIMIT 20, the index above lets PostgreSQL jump to the right section and read rows already in order, stopping after 20. Without it, the database may sort thousands of rows to return a handful.

Covering, partial and expression indexes

These three variations solve specific problems neatly:

• Covering index: INCLUDE adds extra columns to the index leaf pages, so a query can be answered by an index-only scan without visiting the table
• Partial index: a WHERE clause indexes only some rows, such as WHERE deleted_at IS NULL, keeping the index small and fast
• Expression index: index the result of an expression, such as lower(email), and query with the same expression for case-insensitive lookups
• Unique partial index: enforce rules like one active subscription per user with a unique index filtered on status

Beyond B-tree: GIN, GiST and BRIN

GIN indexes suit values that contain many elements: jsonb documents queried with containment operators, arrays and full-text search on tsvector columns. With the pg_trgm extension, a GIN or GiST trigram index also speeds up LIKE and ILIKE searches with a leading wildcard, which a B-tree cannot help. GiST supports geometric and range types, and BRIN indexes are tiny and suit very large, append-only tables where values correlate with physical order, such as event logs by timestamp.

Common reasons an index is ignored

If PostgreSQL ignores an index you expected it to use, check these first:

• The query wraps the column in a function, such as date(created_at), without a matching expression index
• A type mismatch forces a cast on the column side
• The condition matches a large fraction of the table, so a sequential scan really is cheaper
• The composite index's leading column is not in the query
• Statistics are out of date after a large data load

Foreign keys and pagination

PostgreSQL automatically indexes primary keys and unique constraints, but not the referencing side of foreign keys. Add indexes on foreign key columns you join or filter on, and remember that deleting a parent row checks child tables, which is slow without one.

For pagination over large tables, prefer keyset pagination, WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20, backed by an index on (created_at, id). Unlike OFFSET, it stays fast on page 1,000.

A worked example

Take an orders table with millions of rows and the query SELECT id, total FROM orders WHERE customer_id = $1 AND status = 'paid' ORDER BY created_at DESC LIMIT 10. With only a single-column index on customer_id, the plan typically shows an index or bitmap scan that fetches every order for the customer, then a Sort node, then the Limit. For a busy customer that means reading and sorting thousands of rows to return ten.

An index on (customer_id, status, created_at DESC) INCLUDE (total) changes the plan to an Index Only Scan that reads exactly ten entries in order, with no sort. Index-only scans depend on the visibility map, so check the Heap Fetches figure in EXPLAIN ANALYZE; a high number means the table needs vacuuming more often.

How many indexes is too many?

There is no fixed number, but every index must earn its place. Write-heavy tables, such as event logs, should carry as few indexes as possible. Be careful indexing columns that change constantly, such as last_seen_at: PostgreSQL can apply cheap heap-only tuple (HOT) updates only when no indexed column changes, so indexing a frequently updated column makes every one of those updates more expensive.

Changing indexes safely in production

A plain CREATE INDEX blocks writes to the table while it builds. In production, use CREATE INDEX CONCURRENTLY, which avoids blocking writes but takes longer and cannot run inside a transaction block, so many migration tools need a flag to allow it. If a concurrent build fails, it leaves an invalid index that you must drop and recreate.

Find unused indexes with pg_stat_user_indexes, where idx_scan stays at zero over a representative period, and find the queries worth optimizing with the pg_stat_statements extension. Removing unused indexes speeds up writes and saves memory.

Further reading

If you are choosing a database, our PostgreSQL vs MySQL and MongoDB vs PostgreSQL comparisons and our relational database explainer are useful context. Nexzem's performance testing work often starts with exactly this kind of query and index review.