PostgreSQL is one of the most powerful open-source databases available, but out-of-the-box defaults are deliberately conservative. Production tuning can yield 5-20x query performance improvements. Here's the systematic approach we use.
1 Start with EXPLAIN ANALYZE
Every tuning effort starts with understanding what PostgreSQL is actually doing. EXPLAIN ANALYZE executes the query and shows actual execution time, row counts and whether estimates match reality. Look for Seq Scan on large tables (missing index), nested loop joins with large row counts (may need hash join), and rows= estimates wildly different from actual (stale statistics — run ANALYZE).
2 Indexing Strategy
B-tree indexes are the default and work for most equality and range queries. Partial indexes dramatically reduce index size when you query a subset of rows frequently. Composite indexes must match your query's WHERE clause column order. Index-only scans (where all needed columns are in the index) are the fastest possible path — design for them on hot tables. Use pg_stat_user_indexes to identify unused indexes; they slow down writes for no read benefit.
3 Memory Configuration
shared_buffers should be 25% of total RAM. effective_cache_size should be 75% of RAM (it's a hint to the planner, not an allocation). work_mem controls memory per sort/hash operation — too low forces disk spills, too high causes OOM with many concurrent connections. Start at 16–64MB and adjust based on EXPLAIN output showing "Batches: N" for hash operations.
4 Vacuum and Autovacuum
Table bloat from dead tuples is one of the most common causes of degraded performance in busy PostgreSQL installations. Check pg_stat_user_tables for n_dead_tup. If autovacuum isn't keeping up, reduce autovacuum_vacuum_scale_factor on hot tables (from default 0.2 to 0.01 for large tables). For write-heavy tables, consider partitioning to limit vacuum scope.
5 Connection Pooling
PostgreSQL forks a process per connection — 500 open connections consume significant memory even if idle. Deploy PgBouncer in transaction mode between your application and PostgreSQL. Set PostgreSQL's max_connections to 100–200, and let PgBouncer manage thousands of application connections, multiplexing them onto the smaller pool. This is one of the highest-ROI changes for web applications.