PostgreSQL is remarkably capable, but default configurations are conservative. With proper tuning, the same hardware can handle dramatically more load. Here are the optimizations that have made the biggest difference in our projects.
Start with Measurement
Before optimizing, understand where time is spent. PostgreSQL provides excellent tools for this.
Query Analysis
Enable pg_stat_statements to track query performance across your application. Identify which queries consume the most time—optimization effort should focus there.
Use EXPLAIN ANALYZE to understand how queries execute. Look for sequential scans on large tables, nested loops with high row counts, and sort operations that spill to disk.
Monitoring
Track key metrics: query latency, connection count, cache hit ratio, and disk I/O. Tools like pg_stat_activity show real-time query execution.
Index Optimization
Proper indexing is the highest-impact optimization for most applications.
The Right Indexes
Create indexes on columns used in WHERE clauses, JOIN conditions, and ORDER BY clauses. But do not create indexes speculatively—they have maintenance costs.
Composite indexes should have columns ordered by selectivity. The most selective column should come first.
Partial Indexes
For queries that filter on a condition frequently, partial indexes can be much smaller and faster. An index on active users is smaller than an index on all users.
Expression Indexes
If queries frequently filter on expressions, index the expression directly. This eliminates runtime calculation.
Covering Indexes
Include additional columns in an index to satisfy queries entirely from the index, avoiding table lookups. Use INCLUDE for columns that are selected but not filtered on.
Query Optimization
Avoid SELECT Star
Select only the columns you need. This reduces I/O and makes covering indexes more effective.
Batch Operations
Instead of inserting rows one at a time, batch them. Instead of multiple single-row updates, use a single UPDATE with IN or VALUES.
Efficient Pagination
Offset pagination degrades as offset increases. Use keyset pagination for large datasets—WHERE id > last_seen_id LIMIT 100.
Common Table Expressions
CTEs can improve readability but may prevent optimization in older PostgreSQL versions. In PostgreSQL 12+, the optimizer inlines CTEs when beneficial.
Connection Management
Connection Pooling
Each PostgreSQL connection consumes memory. Use a connection pooler like PgBouncer to multiplex many application connections over fewer database connections.
Connection Limits
Set appropriate connection limits based on available memory. Each connection uses significant memory for query processing.
Memory Configuration
shared_buffers
Set to 25% of available RAM as a starting point. This is PostgreSQL's main cache for table and index data.
work_mem
Controls memory for sort and hash operations. Too low causes disk spills; too high risks out-of-memory with concurrent queries. Start conservative and increase if you see disk sorts.
effective_cache_size
Tells the query planner how much memory is available for caching (including OS cache). Set to 75% of available RAM.
Write Performance
Checkpoint Tuning
Checkpoints flush dirty pages to disk. Spread them out to avoid I/O spikes. Increase checkpoint_completion_target and max_wal_size.
Synchronous Commit
For non-critical data, synchronous_commit can be disabled for significant write performance improvement at the cost of potential data loss on crash.
Batch Writes
Combine multiple writes into transactions. Each transaction has commit overhead.
Read Replicas
For read-heavy workloads, add read replicas to distribute load.
Streaming Replication
PostgreSQL's built-in streaming replication keeps replicas in near real-time sync. Route read queries to replicas.
Connection Routing
Your application needs to route writes to primary and reads to replicas. Connection poolers can handle this automatically.
Maintenance
VACUUM and ANALYZE
PostgreSQL requires regular maintenance. Enable autovacuum with appropriate settings. Large tables may need manual vacuum tuning.
Index Maintenance
Indexes can become bloated over time. REINDEX during maintenance windows if you see index bloat.
Statistics
Keep statistics up to date for the query planner to make good decisions. ANALYZE runs automatically but may need manual execution after large data changes.
When to Scale Out
At some point, a single PostgreSQL server cannot handle your load. Options include read replicas for read scaling, partitioning for very large tables, and sharding for write scaling.
But do not reach for these solutions prematurely. A well-tuned single PostgreSQL server handles more load than most applications will ever need.
Conclusion
PostgreSQL performance tuning is iterative. Measure, identify bottlenecks, make targeted changes, and measure again. Most applications achieve excellent performance with proper indexing, appropriate memory configuration, and connection pooling. More exotic optimizations are rarely necessary.
