PostgreSQL indexing playbook — Design Review Checklist — Practical Guide (Oct 25, 2025)

PostgreSQL Indexing Playbook — Design Review Checklist

Level: Intermediate

As of October 25, 2025 — relevant for PostgreSQL 13 through 16

Prerequisites

Before diving into index design and optimisation, ensure you’re comfortable with PostgreSQL fundamentals, including SQL querying and an understanding of query plans via EXPLAIN. This guide targets PostgreSQL versions 13 to 16, covering stable features including B-tree, hash, GIN, GiST, BRIN, and bloom indexes, plus partial and expression indexes.

Confirm that your database is well-maintained with up-to-date statistics and VACUUM routines, as these directly affect planner decisions. Make sure you have query logs enabled for tracing slow queries (log_min_duration_statement) and the auto_explain extension configured if needed.

Hands-on Steps

Step 1: Analyse your queries and data access patterns

Start by identifying frequently run queries, especially those performing sequential scans or long execution times. The PostgreSQL pg_stat_statements extension is invaluable here, providing aggregated query statistics:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Examine these queries for WHERE clauses, JOIN conditions, ORDER BYs, and GROUP BYs that might benefit from indexing.

Step 2: Choose the appropriate index type

B-tree indexes remain the default and most versatile, suitable for equality and range queries. They support ordered retrieval, making them ideal for sorting and uniqueness constraints.

Hash indexes in PostgreSQL 13+ are stable and WAL-logged as of 10, but still have narrower use cases (equality only). Prefer B-tree unless you have a very specific workload.

GIN (Generalized Inverted Indexes) excel for jsonb containment, array types, and full-text search. They have slower writes but are crucial for complex searches.

GiST (Generalized Search Tree) indexes are appropriate for geometric data types or full-text search with ranking.

BRIN (Block Range Indexes) work well for very large, naturally ordered tables (e.g., timestamps) with small index sizes but less precise filtering.

Bloom indexes (contrib module) offer compact multi-column indexing with probabilistic matching, but no exact search.

Step 3: Consider partial and expression indexes

Partial indexes limit the index to rows matching a predicate, reducing index size and maintenance overhead:

CREATE INDEX idx_active_users ON users (last_login)
WHERE active = true;

Expression indexes build indexes on transformed values, useful if you query on functions or computed columns:

CREATE INDEX idx_lower_email ON users ((lower(email)));

Step 4: Verify columns and order in multi-column indexes

For multi-column indexes, order matters. PostgreSQL can efficiently use a multi-column B-tree index only if the query filters or sorts on a leading subset of those columns. Typically, put the most selective columns first.

Step 5: Manage index maintenance and bloat

Indexes incur overhead on INSERT/UPDATE/DELETE. Regularly run VACUUM and ANALYSE. Consider REINDEX operation if you detect index bloat or corruption via pg_stat_user_indexes and lookups for inflated index sizes.

Common Pitfalls

Over-indexing: Indexes speed up reads but slow writes. Avoid creating indexes that aren’t justified by query patterns.
Ignoring planner statistics: Bad or outdated statistics lead the planner to skip indexes or choose inefficient plans.
Wrong index types: Using B-tree for jsonb containment instead of GIN, or vice versa, often yields poor performance.
Misunderstanding multi-column indexes: Queries must include leftmost columns to benefit from them.
Relying on default fillfactors: Adjust fillfactor for indexes on write-heavy tables to reduce page splits.
Not validating index usage: Always confirm with EXPLAIN output that the index participates in query plans.

Validation

Step 1: Use EXPLAIN and EXPLAIN ANALYSE

To verify if an index is used, run:

EXPLAIN ANALYSE SELECT * FROM users WHERE last_name = 'Smith';

Look for lines showing Index Scan rather than Seq Scan. If the planner chooses sequential scan, review your query and statistics.

Step 2: Check index size and bloat metrics

Query index size:

SELECT indexrelid::regclass AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

For bloat detection, use extensions like pgstattuple or external tools (e.g., pg_repack).

Step 3: Monitor performance impact over time

Track query times and system load with logging and monitoring tools. Changes in workload may necessitate index adjustments or removals.

Checklist / TL;DR

Identify slow, frequent queries via pg_stat_statements.
Match index type to data and query: B-tree (default), GIN (jsonb, arrays), GiST (geometric), BRIN (large sorted datasets).
Use partial indexes to cover subsets of data.
Leverage expression indexes for functions or transformations in WHERE clauses.
Order multi-column indexes with highly selective columns first.
Monitor and maintain index health with VACUUM, ANALYSE, REINDEX.
Validate with EXPLAIN ANALYSE.
Avoid index overuse; balance read gain against write cost.