Skip to main content

Initial Postgres tuning

🚑 First aid kit for Postgres performance

This guide covers the essential tuning parameters that provide the biggest performance impact with minimal effort - the 80/20 rule for Postgres optimization.

Before you start​

⚠️ Always backup your configuration before making changes:

-- Check current settings
SELECT name, setting, unit, context 
FROM pg_settings 
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem', 'effective_cache_size');

Essential memory settings​

shared_buffers​

Start with 25% of total RAM, maximum 8GB for most workloads:

-- Check current shared_buffers
SHOW shared_buffers;

-- View buffer cache hit ratio (should be >95%)
SELECT 
    round(
        (sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))) * 100, 2
    ) AS buffer_hit_ratio
FROM pg_statio_user_tables;

work_mem​

Start with total RAM / max_connections / 4:

-- Check current work_mem
SHOW work_mem;

-- Find queries using temporary files (indicates low work_mem)
SELECT query, temp_blks_written, temp_blks_read
FROM pg_stat_statements 
WHERE temp_blks_written > 0 
ORDER BY temp_blks_written DESC 
LIMIT 10;

effective_cache_size​

Set to 75% of total system RAM:

-- Check current effective_cache_size
SHOW effective_cache_size;

-- This parameter doesn't allocate memory, just helps the planner
SELECT pg_size_pretty(setting::bigint * block_size) as effective_cache_size
FROM pg_settings, (SELECT current_setting('block_size')::int as block_size) bs
WHERE name = 'effective_cache_size';

Checkpoint tuning​

Reduce checkpoint frequency to improve performance:

-- Check current checkpoint settings
SELECT name, setting, unit 
FROM pg_settings 
WHERE name LIKE '%checkpoint%';

-- Monitor checkpoint statistics
SELECT 
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,
    checkpoint_sync_time
FROM pg_stat_bgwriter;

WAL settings​

-- Check WAL settings
SELECT name, setting, unit 
FROM pg_settings 
WHERE name IN ('wal_buffers', 'synchronous_commit', 'wal_compression');

-- Monitor WAL activity
SELECT 
    pg_current_wal_lsn(),
    pg_walfile_name(pg_current_wal_lsn()) as current_wal_file;

Verification queries​

After applying changes, verify with these monitoring queries:

-- Overall database statistics
SELECT 
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    temp_files,
    temp_bytes
FROM pg_stat_database 
WHERE datname = current_database();

-- Check for performance issues
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables 
WHERE seq_scan > idx_scan 
ORDER BY seq_tup_read DESC;

Sample postgresql.conf changes​

# Memory settings
shared_buffers = 2GB
work_mem = 16MB  
maintenance_work_mem = 512MB
effective_cache_size = 6GB

# Checkpoint settings  
checkpoint_completion_target = 0.8
wal_buffers = 64MB

# Query planner
random_page_cost = 1.5
effective_io_concurrency = 200

🔄 Remember to restart PostgreSQL after configuration changes.