PostgreSQL Performance Tuning: Memory, I/O, and Vacuum

PostgreSQL is renowned for its reliability and advanced features, but out-of-the-box configuration is often too conservative for production workloads. Performance tuning is essential to unlock its full potential. This article dives into three critical areas: memory allocation, I/O optimization, and vacuum management. You'll learn how to configure PostgreSQL to handle high concurrency, large datasets, and write-heavy loads without grinding to a halt. We'll cover real-world scenarios, debugging techniques, and common pitfalls. By the end, you'll be able to diagnose performance bottlenecks and apply targeted fixes. Whether you're running a small web app or a data warehouse, these tuning principles will help you get the most out of PostgreSQL.

1. Memory Configuration: Shared Buffers and Beyond

PostgreSQL uses several memory areas. The most critical is shared_buffers, which caches data blocks. Set it to 25% of RAM for a dedicated server. effective_cache_size tells the planner how much cache is available (including OS cache); set it to 50-75% of RAM. work_mem controls memory for sorts and hash tables; set per query (not per session) to avoid OOM. maintenance_work_mem is for VACUUM and CREATE INDEX; increase it to speed up maintenance. Example: For a 32GB RAM server, set shared_buffers=8GB, effective_cache_size=24GB, work_mem=64MB (per query), maintenance_work_mem=1GB.

-- Check current settings
SHOW shared_buffers;
SHOW effective_cache_size;
SHOW work_mem;
SHOW maintenance_work_mem;

-- Recommended settings for 32GB RAM (in postgresql.conf)
-- shared_buffers = 8GB
-- effective_cache_size = 24GB
-- work_mem = 64MB
-- maintenance_work_mem = 1GB

-- To apply without restart (requires superuser)
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
SELECT pg_reload_conf();

2. I/O Tuning: Checkpoints and WAL

PostgreSQL writes data to WAL (Write-Ahead Log) and flushes to data files during checkpoints. Checkpoint frequency is controlled by max_wal_size, min_wal_size, and checkpoint_completion_target. Increase max_wal_size to reduce checkpoint frequency (but increases recovery time). Set checkpoint_completion_target to 0.9 to spread writes. Use pg_stat_bgwriter to monitor checkpoints. Also tune wal_buffers (default 16MB is often enough). For write-heavy workloads, consider using SSDs and increasing checkpoint intervals.

-- Check checkpoint stats
SELECT * FROM pg_stat_bgwriter;

-- Current WAL settings
SHOW max_wal_size;
SHOW min_wal_size;
SHOW checkpoint_completion_target;

-- Recommended for write-heavy: increase max_wal_size
ALTER SYSTEM SET max_wal_size = '4GB';
ALTER SYSTEM SET min_wal_size = '1GB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
SELECT pg_reload_conf();

3. Autovacuum: Preventing Bloat and Transaction ID Wraparound

Autovacuum cleans up dead tuples and prevents transaction ID wraparound. It's controlled by autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor, and autovacuum_naptime. For large tables, reduce scale_factor to 0.01 and increase threshold to 1000. Monitor with pg_stat_user_tables. Use pgstattuple extension to measure bloat. For write-heavy tables, consider manual VACUUM during low traffic. Also set autovacuum_freeze_max_age to avoid wraparound (default 200 million).

-- Check autovacuum settings
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_naptime;

-- Per-table settings for a large table
ALTER TABLE orders SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);

-- Check dead tuples
SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE relname = 'orders';

-- Measure bloat
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders');

4. Query Planning: Effective Cache and Statistics

The query planner uses effective_cache_size to estimate if index scans are cheaper than sequential scans. Set it correctly to encourage index usage. Also, keep statistics up-to-date with default_statistics_target (default 100). Increase it for columns with skewed data. Use ANALYZE after bulk loads. Monitor with EXPLAIN (ANALYZE, BUFFERS) to see actual vs estimated rows.

-- Check effective_cache_size
SHOW effective_cache_size;

-- Increase statistics target for a column
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

-- Analyze a query plan
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending';

5. Indexing Strategies for Performance

Indexes speed up reads but slow writes. Use B-tree for equality and range queries, GIN for full-text and arrays, GiST for geometric data. Avoid over-indexing; use pg_stat_user_indexes to find unused indexes. Consider partial indexes for common WHERE clauses. Use covering indexes (INCLUDE columns) to avoid heap fetches. Monitor index bloat with pgstattuple.

-- Find unused indexes
SELECT schemaname, tablename, indexname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;

-- Create a partial index
CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

-- Create a covering index
CREATE INDEX idx_orders_covering ON orders (status) INCLUDE (created_at, total);

-- Check index bloat
SELECT * FROM pgstattuple('idx_orders_pending');

6. Connection Pooling and Resource Limits

PostgreSQL forks a process per connection, so too many connections waste memory. Use a connection pooler like PgBouncer to limit active connections. Set max_connections to a reasonable number (e.g., 100). Each connection uses work_mem, so reducing connections saves memory. Also tune max_worker_processes for parallel queries. Monitor with pg_stat_activity.

-- Check current connections
SELECT count(*) FROM pg_stat_activity;

-- Set max_connections (requires restart)
ALTER SYSTEM SET max_connections = '100';

-- For PgBouncer, configure pool_mode = transaction
-- Example pgbouncer.ini:
-- [databases]
-- mydb = host=localhost port=5432 dbname=mydb
-- [pgbouncer]
-- pool_mode = transaction
-- max_client_conn = 200
-- default_pool_size = 20

7. Monitoring and Continuous Tuning

Performance tuning is iterative. Use pg_stat_statements to track query performance over time. Set up alerts for high I/O, long queries, and bloat. Use pgBadger for log analysis. Regularly review configuration with tools like pgtune. Document changes and test in staging. Remember that hardware upgrades (SSD, more RAM) can complement tuning.

-- Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 5 queries by total time
SELECT query, total_time, calls, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

-- Check vacuum progress
SELECT * FROM pg_stat_progress_vacuum;

-- Check table bloat (using pgstattuple)
SELECT relname, (dead_tuple_len::numeric / tuple_len * 100) AS bloat_pct FROM pg_stat_user_tables, pgstattuple(relname::text) WHERE relname = 'orders';