postgresql.conf tuner

The common starting point is 25% of total RAM for shared_buffers, but the optimal value depends on your workload and operating system. PostgreSQL relies heavily on the OS page cache for reading data, so allocating too much to shared_buffers starves the OS cache and can actually hurt performance through double-buffering — the same pages end up cached in both shared_buffers and the kernel page cache. On a 64 GB server, 16 GB for shared_buffers is a solid default, leaving ~48 GB for the OS to cache the rest of your data files, WAL, temp files, and sort spills. For very large databases (multiple TB), some workloads benefit from going higher — up to 40% of RAM — especially if the active working set fits in shared_buffers. On Windows, keep shared_buffers lower (around 512 MB to 1 GB) due to how Windows manages large memory allocations. After changing shared_buffers, set effective_cache_size to approximately 75% of total RAM — this does not allocate memory but tells the planner how much total cache (shared_buffers + OS cache) is likely available, which affects its cost estimates for index scans vs. sequential scans. Always enable huge_pages (set to try or on) when shared_buffers exceeds 8 GB on Linux to reduce TLB pressure and improve memory access performance.

work_mem controls the amount of memory each sort or hash operation can use before spilling to disk. The danger is that this limit applies *per operation per query per connection* — a single complex query with four sort nodes and two hash joins might allocate up to 6x work_mem, and if 100 connections run similar queries simultaneously, total memory usage can reach 600x work_mem. This is why the default of 4 MB is so conservative. For OLTP workloads with many concurrent connections, keep work_mem between 16 MB and 64 MB and use SET work_mem at the session or transaction level for known expensive queries. For OLAP workloads with few concurrent users running complex analytical queries, work_mem can be 256 MB to 2 GB because the connection count is low. A practical formula is: (Total RAM - shared_buffers) / (max_connections * 3) as an upper bound, then reduce by 50% for safety. Monitor with EXPLAIN (ANALYZE, BUFFERS) — look for Sort Method: external merge or hash Batches: >1, which indicate work_mem is too low for that query. PostgreSQL 13+ introduced hash_mem_multiplier (default 2.0 in PG 15+) which allows hash operations to use a multiple of work_mem without affecting sort allocations, giving you finer control.

For write-heavy workloads, the most impactful settings are max_wal_size, checkpoint_completion_target, wal_buffers, and wal_compression. max_wal_size (default 1 GB) controls how much WAL accumulates before a checkpoint is triggered — setting it to 4–16 GB reduces checkpoint frequency, which smooths out I/O spikes but increases crash recovery time. checkpoint_completion_target (default 0.9 since PG 14, was 0.5 before) spreads dirty page writes across the checkpoint interval rather than flushing them all at once, dramatically reducing I/O stalls. wal_buffers should be set to 64 MB for write-heavy systems (the default auto-sizing of 1/32 of shared_buffers is usually fine but capping at 64 MB prevents excess). PostgreSQL 15 introduced wal_compression = zstd which can reduce WAL volume by 50–70% for workloads with large tuple modifications — this saves disk I/O and speeds up replication with minimal CPU overhead. For replicated setups, also consider max_wal_senders, wal_keep_size, and wal_sender_timeout. Monitor checkpoint behavior with log_checkpoints = on and watch for messages about checkpoints occurring too frequently — if the log says "checkpoints are occurring too frequently," increase max_wal_size.

OLTP and OLAP workloads have fundamentally different resource profiles, and tuning them identically wastes capacity. OLTP workloads (web apps, transactional systems) have many concurrent short-lived queries touching small numbers of rows. They need: lower work_mem (16–64 MB) because hundreds of sessions multiply it, higher max_connections (or better, connection pooling with PgBouncer), conservative parallelism (max_parallel_workers_per_gather = 2 or 0 for latency-sensitive queries), and aggressive autovacuum to keep tables bloat-free for fast index lookups. OLAP workloads (data warehouses, analytics) have few concurrent users running complex queries scanning millions of rows. They need: high work_mem (256 MB–2 GB) for large sorts and hash joins, aggressive parallelism (max_parallel_workers_per_gather = 4–8), larger maintenance_work_mem for index builds and VACUUM, JIT compilation enabled for long-running queries (jit_above_cost tuned to your typical query cost), and max_wal_size set high (8–16 GB) because bulk loads generate enormous WAL. Mixed workloads are the hardest — use connection pooling, set conservative global defaults, and apply aggressive settings per-session for analytical queries using SET LOCAL.

Each major version introduces new parameters and changes defaults. In PostgreSQL 14: idle_in_transaction_session_timeout is available for killing abandoned sessions, huge_pages_status can be queried to verify huge pages are active, and compute_query_id enables query identification for pg_stat_statements without the extension loaded. The default for checkpoint_completion_target changed from 0.5 to 0.9 — if upgrading from PG 13 or earlier, remove any explicit setting of 0.9 (it is now the default) or update if you had 0.5. In PostgreSQL 15: wal_compression now supports zstd and lz4 in addition to pglz, and shared_memory_type on Linux defaults to mmap. The stats_temp_directory parameter was removed — delete it from your config if present. PostgreSQL 16 introduced reserved_connections for superuser access (complementing superuser_reserved_connections), log_checkpoints defaults to on, and allow_alter_system can lock down ALTER SYSTEM. PostgreSQL 17 brought wal_summary for incremental backups, improved default I/O concurrency handling, and io_combine_limit for controlling read-ahead. Always review the release notes for your target version and remove deprecated parameters — leftover settings from older versions can prevent PostgreSQL from starting after an upgrade.