Database Indexing & Query Optimization: Handling 10M+ Rows in PostgreSQL

Database latency is the silent killer of enterprise software responsiveness. As data grows from thousands of rows into millions, unindexed queries that previously ran in 5 milliseconds begin locking tables and causing 10-second request timeouts. Systematic query profiling is essential for high-throughput backends.

1. Mastering EXPLAIN (ANALYZE, BUFFERS)

Never guess why a query is slow. Inspecting the actual PostgreSQL execution plan reveals sequential table scans (Seq Scan), inefficient nested loops, and memory spills to disk. Replacing full table scans with Index Scans reduces I/O by orders of magnitude.

-- Profile query execution plan and disk buffer reads
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT o.id, o.amount_cents, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2026-01-01'
  AND o.status = 'COMPLETED'
ORDER BY o.created_at DESC
LIMIT 50;

-- Composite B-Tree index supporting exact filter + range + sorting
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (amount_cents, user_id);

2. Choosing the Right Index Type

Standard B-Tree indexes work for equality and range filters. For massive append-only time-series datasets (logs, sensor telemetry), BRIN (Block Range Index) consumes 99% less disk space while delivering comparable lookup speeds. For JSONB columns, GIN (Generalized Inverted Index) is mandatory.

"An index is not free; each index adds write latency to INSERT and UPDATE queries. Audit unused indexes regularly using pg_stat_user_indexes."

3. Declarative Table Partitioning

When individual tables surpass 50 million rows, partitioning by time range (e.g. monthly partitions) allows PostgreSQL to skip scanning historical data entirely through partition pruning, keeping query caches lean.

• B-Tree: Universal index for equality (=) and range (<, >, BETWEEN) lookups.
• BRIN: 90%+ smaller index size for chronologically sorted log and analytics tables.
• GIN: Blazing fast inverted index for querying JSONB structures and array containment.
• PgBouncer: Connection pooling to prevent Postgres connection exhaustion under heavy load.