PostgreSQL Indexing Playbook — Practical Guide (Feb 12, 2026)

PostgreSQL Indexing Playbook

Level: Intermediate

As of PostgreSQL 15 and 16 preview (2026), indexing remains one of the most vital levers for database performance optimisation. Getting it right can mean the difference between sub-second responses and painful query delays. This playbook offers a practical guide to selecting, creating, and validating indexes with an emphasis on real-world usage and PostgreSQL best practices.

Prerequisites

To follow this playbook, you should be comfortable:

With basic SQL: SELECT, WHERE, JOIN, and EXPLAIN statements.
Understanding of PostgreSQL schema and data types.
Access to a PostgreSQL instance version 14, 15, or the 16 preview, since some features and optimisations differ subtly across these versions.
Using the psql command-line or a similar database client.

You will also benefit from familiarity with query planning and execution concepts, though this guide will touch on these as needed.

Hands-on Steps

1. Understand Your Workload and Query Patterns

Good indexing decisions start with knowing your most frequent and performance-critical queries. Extract slow queries from your pg_stat_statements or logs, and identify columns often used in:

WHERE clauses
JOIN conditions
ORDER BY, GROUP BY
Foreign key references

2. Choose the Appropriate Index Type

PostgreSQL supports several index types; the most common are:

B-tree (default): Suitable for equality and range queries on scalar data types (numbers, text).
BRIN: Lightweight and efficient for very large, naturally ordered datasets (e.g., timestamps, serial IDs).
GIN: Ideal for full-text search, arrays, and JSONB.
GiST: Extensible; used for geometric types, full-text search, and some custom indexes.
Hash: Limited use, sometimes faster for simple equality; note PostgreSQL 10+ supports WAL-logged hash indexes.

When to choose B-tree vs. BRIN: Use B-tree indexes when queries require random access on indexed columns; use BRIN on very large tables where data is physically clustered and query range scans are typical, as BRIN indexes are smaller and faster to maintain.

3. Create Indexes Thoughtfully

Use the CREATE INDEX command with attention to naming and columns. For multi-column indexes, column order matters — place the most selective column first.

-- Create a B-tree index on the "email" column
CREATE INDEX idx_users_email ON users(email);

-- Multi-column index example: filter queries on (status, created_at)
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- Example of a GIN index on a JSONB column (PostgreSQL 9.4+)
CREATE INDEX idx_products_attrs_gin ON products USING GIN (attributes);

Consider CONCURRENTLY to build indexes without locking writes, especially on large production tables:

CREATE INDEX CONCURRENTLY idx_name ON table(column);

4. Exploit Partial and Expression Indexes

Partial indexes index a subset of rows matching a condition, reducing size and improving performance:

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

Expression indexes let you index a computed value — handy for functions or transformations:

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

5. Maintain Indexes and Monitor Usage

Vacuum and analyse regularly to keep planner statistics fresh. Use pg_stat_user_indexes and pg_stat_all_indexes to monitor index scans vs. usage:

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public';

Low scan counts may indicate an unused or redundant index, worth auditing or dropping.

Common Pitfalls

Over-indexing: More indexes increase write overhead and consume storage. Avoid indexing columns rarely used in queries.
Ignoring index column order: Multicolumn indexes only help queries with leading columns matched.
Misusing types: Using text indexes for UUIDs or integers may lead to suboptimal plans.
Not monitoring usage: Unused indexes add bloat and maintenance costs without benefit.
Expecting indexes to solve all performance issues: Sometimes query rewrite, partitioning, or caching is a better solution.

Validation

After creating indexes, validate their impact as follows:

Use EXPLAIN (ANALYSE, BUFFERS) on your critical queries to confirm index scan usage and time reductions.
Check planner output for “Index Scan”, “Bitmap Index Scan” or “Index-Only Scan”, indicating indexes are in effect.

EXPLAIN (ANALYSE, BUFFERS)
SELECT * FROM users WHERE lower(email) = 'alice@example.com';

Watch for unexpected Seq Scan if the planner considers the index too costly due to stale stats or misestimation.

Checklist / TL;DR

Identify frequent queries and filter conditions.
Pick B-tree for general use; BRIN for huge, naturally ordered tables.
Consider GIN or GiST for JSON, arrays, and full-text search.
Create CONCURRENTLY indexes in production to avoid locks.
Use partial and expression indexes to reduce index size and target queries.
Regularly monitor index usage and drop unused indexes.
Validate with EXPLAIN ANALYSE to ensure execution plans use indexes.