PostgreSQL Performance Tuning for High-Traffic Production Systems

Why PostgreSQL Performance Matters

At ANSOL, our water utility clients process between 5–50 million meter readings per day. A poorly tuned PostgreSQL instance can become the bottleneck that cascades into customer-facing outages.

Key Configuration Parameters

-- postgresql.conf adjustments
shared_buffers = 8GB          -- 25% of RAM
effective_cache_size = 24GB   -- 75% of RAM
work_mem = 64MB
maintenance_work_mem = 2GB
max_connections = 200         -- use PgBouncer for more
checkpoint_completion_target = 0.9
wal_buffers = 64MB

Indexing Strategy

For time-series data, partial indexes and BRIN indexes dramatically outperform standard B-tree:

CREATE INDEX CONCURRENTLY idx_readings_recent
  ON meter_readings (meter_id, recorded_at DESC)
  WHERE recorded_at > NOW() - INTERVAL '30 days';

CREATE INDEX idx_readings_brin
  ON meter_readings USING BRIN (recorded_at)
  WITH (pages_per_range = 128);

Connection Pooling with PgBouncer

Never connect hundreds of app servers directly to PostgreSQL. Use PgBouncer in transaction mode with pool_size = 20–50 per database.

Query Optimization Checklist

1. Use EXPLAIN (ANALYZE, BUFFERS) – never plain EXPLAIN
2. Watch for sequential scans on large tables
3. Avoid SELECT * in application code
4. Use prepared statements to reduce parse overhead

Conclusion

Performance tuning is iterative. Measure, change one variable, measure again. The gains from a well-tuned PostgreSQL instance can reduce your cloud bill by 40–60%.