As relational database tables scale past tens of millions of rows, unoptimized queries quickly exhaust CPU and I/O capacity. While developers routinely add indexes to fix slow queries, naive indexing creates new problems: bloated storage, degraded write performance (INSERT/UPDATE overhead), and indexes that the query planner completely ignores.
This engineering runbook explores advanced PostgreSQL indexing strategies to achieve sub-millisecond query performance under heavy concurrent workloads.
1. The Mechanics of Composite Index Ordering
When creating a multi-column (composite) index, column order determines whether the index can be utilized. PostgreSQL evaluates composite B-Tree indexes strictly from left to right.
The Rule of Cardinality & Filter Types:
- Equality First (
=): Place columns used in exact equality matches first. - High Cardinality Second: Among equality columns, place those with the highest number of unique values earlier in the index.
- Range Filters Last (
>,<,BETWEEN,IN): Place range columns at the end. Once PostgreSQL encounters a range condition, it cannot use subsequent index columns for index scans.
SQL
-- OPTIMAL: Equality on status first, followed by range scan on created_at
CREATE INDEX idx_orders_status_created_at
ON orders (status, created_at DESC);
-- Query that fully utilizes the composite index:
SELECT id, user_id, total_amount
FROM orders
WHERE status = 'COMPLETED'
AND created_at >= '2026-01-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;
2. Drastically Reducing Storage with Partial Indexes
Indexing every single row in a massive table wastes disk space and degrades write throughput. If your application frequently queries a small fraction of rows (e.g., pending tasks, active sessions, or unfulfilled orders), use a Partial Index.
SQL
-- Indexes ONLY rows that require processing (e.g., 2% of a 100M row table)
CREATE INDEX idx_jobs_pending_execution
ON background_jobs (scheduled_at, priority DESC)
WHERE status = 'PENDING';
Performance Comparison:
- Full Index Size: ~4.5 GB for 50 million rows.
- Partial Index Size: ~45 MB for 500,000 pending rows (99% reduction in disk and RAM overhead).
3. Covering Indexes with INCLUDE Clauses (Index-Only Scans)
An Index-Only Scan occurs when PostgreSQL retrieves all required query columns directly from the index B-Tree without fetching the table tuple from disk (Heap). You can append non-key columns to an index using the INCLUDE clause:
SQL
-- Key columns define the B-Tree structure; INCLUDE columns carry payload data
CREATE INDEX idx_users_email_covering
ON users (email)
INCLUDE (id, first_name, last_name, role);
-- Execution plan triggers a pure Index-Only Scan (Zero Heap Fetches):
SELECT id, first_name, last_name, role
FROM users
WHERE email = '[email protected]';
4. Verification Runbook with EXPLAIN (ANALYZE, BUFFERS)
Never assume an index works without inspecting the PostgreSQL Query Planner output. Run your query with BUFFERS enabled to measure actual memory/disk page reads:
SQL
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_amount
FROM orders
WHERE status = 'COMPLETED' AND created_at >= '2026-01-01'
LIMIT 50;
What to look for in execution logs:
Index Scan/Index Only Scan: Desired behavior.Shared Read/Hit: HighReadvalues indicate disk reads; highHitvalues indicate cache hits fromshared_buffers.Seq Scan: Red flag. Indicates the query planner ignored available indexes (often caused by type mismatches, functions on indexed columns likeWHERE LOWER(email) = ..., or stale table statistics). RunANALYZE orders;to refresh table statistics.


