Postgres Performance Tuning Best Practices for Backend Engineers

Postgres performance tuning best practices start with knowing where your time is spent. I’ve seen teams waste weeks optimizing indexes when the real issue was a misconfigured work_mem or a runaway autovacuum. Let’s cut through the noise.

What are the most common Postgres performance bottlenecks?

The usual suspects are slow queries, lock contention, and I/O saturation. In my experience, slow queries often stem from missing indexes or poor planner estimates - not the query itself. I once spent a day tuning a query only to realize the index wasn’t being used because enable_indexscan was accidentally turned off in a session setting. Check pg_stat_activity for waiting queries and pg_locks for blockers. High buffers_read relative to buffers_hit means you’re hitting disk too often - time to look at shared_buffers or your indexing strategy.

Which configuration parameters actually matter for tuning?

Start with these five: shared_buffers, effective_cache_size, work_mem, maintenance_work_mem, and max_worker_processes. Set shared_buffers to 25% of RAM on a dedicated DB box - more if you’re not using the OS cache heavily. effective_cache_size should reflect total RAM available to Postgres and the OS cache combined. I’ve seen work_mem set too high cause swapping during complex sorts - start with 64MB and monitor pg_stat_activity for sort operations spilling to disk. For write-heavy workloads, bump max_worker_processes to match your CPU cores, but don’t go overboard - each worker eats memory.

How do I monitor Postgres performance in production?

I rely on pg_stat_statements for query-level insights - enable it in shared_preload_libraries and track total_time, calls, and rows. Pair it with pg_stat_bgwriter for checkpoint and buffer write stats. For system-level metrics, use pg_stat_replication if you’re streaming, and always monitor OS-level iostat, vmstat, and netstat. I’ve set up alerts in Prometheus using the Postgres exporter to flag when buffer_cache_hit_ratio drops below 95% or when deadlocks exceed 5/min. Don’t just collect metrics - act on them. A spike in temp_files often means you need to increase work_mem or rewrite a query.

What indexing strategies actually optimize queries?

B-tree indexes are your workhorse - use them for equality and range checks. But don’t index everything. I’ve seen tables with 20 indexes where writes slowed to a crawl because every INSERT had to update all of them. Use EXPLAIN ANALYZE to see if your index is being hit. For JSONB columns, a GIN index on jsonb_path_ops beats the default jsonb_ops for containment queries. Partial indexes save space - index only WHERE status = 'active' if that’s your hot path. And remember: indexes help reads, hurt writes. Benchmark your write load before adding one.

How should I handle connection pooling and worker scaling?

Never let your app open a raw Postgres connection per request. Use a pooler like PgBouncer in transaction mode - I’ve seen it cut connection overhead by 90% in FastAPI apps. Set max_connections in Postgres to 200-300, then let PgBouncer handle thousands of app connections via a smaller pool. In FastAPI, I use SQLAlchemy with pool_size=20 and max_overflow=10 behind PgBouncer. For async apps, asyncpg with a built-in pool works too - but never mix sync and async clients in the same service. If you’re using Kubernetes, scale your app pods based on CPU, not DB connections - let the pooler absorb the variance.

When should I tune vacuum and autovacuum for write-heavy workloads?

Autovacuum is non-negotiable - turning it off leads to transaction ID wraparound disaster. But defaults are too lazy for heavy writes. I tune autovacuum_vacuum_scale_factor to 0.05 (instead of 0.2) and autovacuum_analyze_scale_factor to 0.02 on write-heavy tables. Set autovacuum_naptime to 20s so it runs more frequently. Monitor pg_stat_user_tables for n_dead_tup - if it’s growing faster than vacuum can clean, you’ll see bloat. I’ve had to manually VACUUM FULL a table that hit 60% bloat because autovacuum couldn’t keep up - don’t let it get that far. For append-only tables, consider disabling autovacuum entirely and scheduling manual vacuums during low-traffic windows.

FAQ

How do I know if my Postgres instance is CPU-bound vs I/O-bound?
Check iostat for high %util on your data disk - if it’s consistently over 80%, you’re I/O-bound. If CPU is high but disk is idle, look at complex queries or high work_mem usage causing sorts/hashes in memory.

Is it ever safe to disable autovacuum?
Only on static, append-only tables with manual vacuum schedules - and even then, test wraparound risk first. For 99% of workloads, keep it on and tune the scale factors.

Should I increase max_connections to handle more traffic?
No. Raising max_connections without a pooler increases context switching and memory pressure. Use PgBouncer or similar to multiplex app connections safely.

What’s the fastest way to find missing indexes?
Run SELECT * FROM pg_stat_user_tables WHERE seq_scan > idx_scan; to find tables favoring sequential scans, then check pg_stat_user_indexes for zero-read indexes on those tables.

Key Takeaways

• Tune shared_buffers and work_mem based on RAM and workload - monitor for spills to disk.
• Use pg_stat_statements to find slow queries; fix missing indexes or planner issues before touching config.
• Always use a connection pooler - PgBouncer in transaction mode is essential for production scale.
• Adjust autovacuum scale factors for write-heavy tables; monitor bloat via n_dead_tup.
• Indexes speed reads but slow writes - benchmark your write path before adding new ones.