Postgres Performance at Scale: Indexing Strategies, Connection Pooling, and Query Tuning
A masterclass on optimizing PostgreSQL under high concurrent loads: B-tree vs BRIN indexing, EXPLAIN ANALYZE deep dive, and connection pool sizing.
1. The Pitfalls of Over-Indexing and Sequential Scans
Indexes are not free. Every additional index introduces write overhead during INSERT, UPDATE, and DELETE operations and consumes buffer cache memory. A common mistake is indexing individual columns separately rather than creating composite indexes that match the query filter and sorting order.
We analyze `pg_stat_user_tables` and `pg_stat_statements` to identify unused indexes and costly sequential scans across high-volume tables.
-- Query to find unused indexes in PostgreSQL
SELECT
schemaname || '.' || relname AS table,
indexrelname AS index,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
idx_scan as number_of_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique
AND idx_scan < 50
AND pg_relation_size(i.indexrelid) > 1024 * 1024
ORDER BY pg_relation_size(i.indexrelid) DESC;Architecture Takeaways:
- •Regularly audit `pg_stat_user_indexes` to drop dead indexes that bloat disk and write memory.
- •Composite indexes must follow the Equality-Range-Sort column ordering rule.
2. BRIN vs B-Tree for Time-Series and Append-Only Logs
For massive append-only tables (such as audit trails, telemetry logs, or clickstreams sorted by timestamp), standard B-tree indexes consume gigabytes of RAM. Block Range Indexes (BRIN) summarize min/max values for ranges of physical disk pages.
A BRIN index is often 95% smaller than a comparable B-tree index while providing identical scan speeds on time-ordered data.
Architecture Takeaways:
- •Use BRIN indexes on naturally ordered or timestamp-sorted append-only tables.
- •Massively conserve shared_buffers cache for critical transactional B-tree lookups.
3. Proper Connection Pool Sizing
A common misconception is that allocating hundreds of database connections improves throughput. In reality, PostgreSQL processes each connection in a separate OS process, leading to CPU cache thrashing and context switching overhead.
The optimal pool size is generally calculated as `(2 * CPU cores) + disk count`. Managing this with PgBouncer in transaction pooling mode allows thousands of application clients to share a lean pool of 20-30 active database connections.
Architecture Takeaways:
- •Keep PostgreSQL connection counts lean (typically under 50-100 total).
- •Deploy external connection pooling (PgBouncer) between serverless/containerized apps and the primary database.
Designing or scaling a mission-critical platform?
Our squad partners with engineering leaders to architect resilient systems, optimize high-throughput pipelines, and build production AI architectures.
Related Technical Publications
Designing Resilient Next.js Systems: Server Components, Edge Caching, and Connection Pooling
A hands-on breakdown of how we architect high-throughput Next.js platforms to eliminate database bottlenecking, reduce server memory footprints, and achieve sub-100ms global TTFB.
Deploying Agentic AI into Production: RAG Architecture without Hallucination Risks
How to implement retrieval-augmented generation (RAG) with hybrid lexical-vector search and strict deterministic validation guards in mission-critical business software.