PostgreSQL Performance Tuning at Scale
From PgBouncer connection pooling to EXPLAIN ANALYZE mastery. Achieve 50K+ TPS with proper indexing and query optimization.
Connection Pooling with PgBouncer
PostgreSQL creates a new process for each connection, consuming ~10MB of memory per connection. At scale, 500 connections = 5GB just for connection overhead. PgBouncer provides lightweight connection pooling with transaction-level pooling.
Deploy PgBouncer as a sidecar container in Kubernetes or as a standalone service. Set default_pool_size to your PostgreSQL max_connections / number_of_PgBouncer_instances.
EXPLAIN ANALYZE Mastery
Every slow query investigation starts with EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT). Look for: Seq Scan on large tables (missing index), Nested Loop with large outer tables (consider Hash Join), and Sort operations (add index covering the ORDER BY).
The most impactful optimization is usually a covering index. If your query selects columns A, B and filters on C, create an index on (C) INCLUDE (A, B) for an index-only scan.
Achieving 50K+ TPS
At high throughput, tune shared_buffers to 25% of RAM, effective_cache_size to 75% of RAM, and work_mem based on RAM / max_connections / 4. Set random_page_cost = 1.1 for SSD storage.
Partition large tables by time range. Use pg_stat_statements to identify the top 10 queries by total time and optimize them. Consider read replicas for read-heavy workloads with pgpool-II or application-level routing.
| Parameter | Default | Recommended | Why |
|---|---|---|---|
| shared_buffers | 128 MB | 25% of RAM | Shared memory cache |
| effective_cache_size | 4 GB | 75% of RAM | Planner cost estimate |
| work_mem | 4 MB | RAM/conn/4 | Sort/hash operations |
| random_page_cost | 4.0 | 1.1 (SSD) | Index scan preference |
| max_parallel_workers | 8 | CPU cores | Parallel query |