# PostgreSQL Configuration Tuning for Production > **Quick answer:** Start with `shared_buffers` at roughly 25% of RAM, `effective_cache_size` at 50–75%, and `random_page_cost` near 1.1 on SSD. Size `work_mem` from observed temp-file sizes rather than a formula, and remember it is allocated per sort or hash node, not per query. Configuration fixes resource starvation; it does not fix a bad query. PostgreSQL ships with conservative default settings designed to run on minimal hardware. These defaults are not appropriate for production workloads. The most impactful configuration changes are increasing `shared_buffers` to 25% of system RAM, setting `effective_cache_size` to 50-75% of RAM, adjusting `work_mem` based on query complexity and connection count, lowering `random_page_cost` to 1.1 for SSD storage, and tuning autovacuum to keep statistics current on high-write tables. Configuration tuning can produce large improvements without changing a single query, but only where configuration is the actual constraint. Moving `shared_buffers` off a 128 MB default on a 64 GB server, or stopping a report from spilling to disk with a right-sized `work_mem`, can change a query's runtime by an order of magnitude. Correcting an already-reasonable setting usually changes very little. Configuration fixes resource starvation; it does not fix a query that scans ten million rows to return twelve. Measure before and after rather than assuming a multiple. --- ## Memory configuration ### Shared_buffers PostgreSQL's internal buffer cache. This is the most important memory setting. - **Default:** 128 MB - **Recommended:** 25% of total system RAM - **Example:** 8 GB on a 32 GB server ``` shared_buffers = 8GB ``` 25% of RAM is a reasonable *starting point*, not a cap. Some workloads do better with more; above roughly 40% you are unlikely to beat a smaller setting, because PostgreSQL also relies on the OS page cache. Measure rather than assume. Double-caching occurs when `shared_buffers` is too large. Monitor effectiveness, including index blocks, which are usually the majority of buffer traffic on an OLTP workload: ```sql SELECT sum(heap_blks_read + idx_blks_read) AS reads, sum(heap_blks_hit + idx_blks_hit) AS hits, round(100.0 * sum(heap_blks_hit + idx_blks_hit) / nullif(sum(heap_blks_hit + idx_blks_hit + heap_blks_read + idx_blks_read), 0), 2) AS hit_ratio FROM pg_statio_user_tables; ``` Read this number carefully, because it is widely misused: - **The counters are cumulative since the last statistics reset**, not a current-state measurement. On a server that has been up for months, the number is an average over every workload it has ever run and will barely move in response to a change you make today. Reset stats, or diff two samples, to measure anything recent. - **A "hit" means the block was found in `shared_buffers`.** A "read" only means it was *not*, it may still have been served instantly from the OS page cache rather than from disk. A low ratio is therefore not proof of physical I/O. - **A high ratio proves nothing about health.** A hot, badly-planned query that repeatedly re-reads the same cached pages reports a hit ratio near 100% while burning CPU and returning slowly. High ratios routinely accompany bad plans. - **There is no official 95% (or 99%) target.** That threshold appears in no PostgreSQL documentation; it is folklore. Treat the hit ratio as a **trend, not a grade**. A ratio that drops steadily over weeks is a useful signal that the working set is outgrowing RAM. A single reading, compared against a made-up threshold, tells you nothing actionable, measure query latency and temp-file volume instead. ### Effective_cache_size This does not allocate memory. It tells the query planner how much total cache (shared_buffers + OS page cache) is available, which affects its cost estimates and plan choices. - **Default:** 4 GB - **Recommended:** 50-75% of total system RAM - **Example:** 20 GB on a 32 GB server ``` effective_cache_size = 20GB ``` Setting this too low causes the planner to avoid index scans in favor of sequential scans because it assumes data will not be cached. ### Work_mem Memory allocated per sort or hash operation within a query. A single complex query with multiple sorts and hash joins may allocate `work_mem` multiple times. - **Default:** 4 MB - **Recommended:** Depends on concurrency. Total `work_mem` usage = `work_mem` x concurrent queries x operations per query. ``` # Conservative for high-concurrency OLTP (200+ connections) work_mem = 16MB # Moderate for mixed workloads (50-100 connections) work_mem = 64MB # Aggressive for low-concurrency analytics (10-20 connections) work_mem = 256MB ``` Signs `work_mem` is too low: - EXPLAIN ANALYZE shows "Sort Method: external merge Disk: ..." - EXPLAIN ANALYZE shows "Batches: N" in Hash nodes (hash spilling to disk) Signs `work_mem` is too high: - OOM kills during high concurrency - System memory pressure ### Hash_mem_multiplier Hash-based nodes, hash joins and hash aggregates, do not get `work_mem`. They get `work_mem` × `hash_mem_multiplier`. - **Default:** 2.0 - **Recommended:** Raise this instead of `work_mem` when only hash nodes are spilling This matters for budgeting. With `work_mem = 64MB`, a single hash node may use **128 MB**, not 64 MB. The memory formula above understates peak usage by up to 2x on hash-heavy plans, so budget: ``` peak memory = work_mem x hash_mem_multiplier x concurrent queries x hash operations per query ``` If `EXPLAIN ANALYZE` shows `Batches: N` on Hash nodes while sorts are staying in memory, raise `hash_mem_multiplier` rather than `work_mem`. That gives the hash nodes more room without also handing every sort node in every concurrent query a larger allocation. ### Maintenance_work_mem Memory for maintenance operations: VACUUM, CREATE INDEX, ALTER TABLE. - **Default:** 64 MB - **Recommended:** 512 MB - 2 GB ``` maintenance_work_mem = 1GB ``` Higher values make VACUUM and index creation faster. Maintenance operations are less frequent than queries, so this can be set higher than `work_mem`, but autovacuum may allocate up to `autovacuum_max_workers` (default 3) times this value at once. Cap autovacuum separately with `autovacuum_work_mem`. **PostgreSQL 17 changed what this buys you for VACUUM.** Before 17, VACUUM capped its dead-tuple storage at roughly 1 GB no matter how high `maintenance_work_mem` was set, so values above 1 GB bought vacuum nothing, and a vacuum of a large bloated table still had to make multiple index passes. PostgreSQL 17 replaced that structure and removed the cap, so higher values now genuinely speed up vacuuming large bloated tables. (Index builds could always use the full value, on every version.) --- ## Planner configuration ### Random_page_cost The planner's assumed cost of reading a random page relative to sequential I/O. - **Default:** 4.0, already discounted on the assumption most random reads hit cache; it is *not* a literal spinning-disk ratio - **Recommended for SSD:** 1.1 - **Recommended for cloud storage (EBS, Persistent Disk):** 1.1 - 1.5 ``` random_page_cost = 1.1 ``` This is one of the most commonly misconfigured settings. Leaving it at 4.0 on SSD storage causes the planner to avoid index scans in favor of sequential scans, dramatically hurting performance. ### Effective_io_concurrency How many concurrent I/O operations the storage can usefully handle. - **Default:** 16 (was 1 before PostgreSQL 18) - **Recommended for SSD:** 200 - **Recommended for cloud NVMe:** 200 ``` effective_io_concurrency = 200 maintenance_io_concurrency = 200 ``` Before PostgreSQL 18 this setting only governed prefetching for bitmap heap scans, which is why it was easy to ignore. In PG18 it feeds the new **asynchronous I/O subsystem** and now applies to sequential scans and vacuum as well, that broader role is why the default jumped from 1 to 16. Two companions matter alongside it: - **`maintenance_io_concurrency`**, same idea, same default of 16, but used for vacuum and other maintenance work. Raise it with `effective_io_concurrency`, or vacuum will keep issuing I/O conservatively while your queries do not. - **`io_method`**, selects the AIO implementation. Default is `worker` (a pool of dedicated I/O worker processes). On Linux builds compiled with support, `io_uring` issues I/O directly from each backend and avoids the worker hop. This is a **restart-only** parameter. ### Seq_page_cost Leave at the default (1.0) in most cases. Adjust only if you have unusually fast or slow sequential I/O. ### Jit Just-in-time compilation of query expressions. - **Default:** `on`, with `jit_above_cost = 100000` - **Recommended:** `off` for OLTP-dominated workloads that see unexplained latency spikes JIT trades planning-time cost for faster execution. That is a good trade for long analytical queries, where a few hundred milliseconds of compilation is repaid many times over across millions of rows. It is a bad trade for short OLTP queries, and the trigger is the planner's **estimated** cost, not actual runtime, so a query that returns in 5 ms can still cross the 100,000 threshold and pay for compilation it will never recoup. Bad row estimates inflate cost and make this worse, which is why JIT is a common cause of "this query got slower for no reason after the upgrade." Diagnose it from `EXPLAIN ANALYZE`: if the output has a `JIT:` section whose `Generation`, `Inlining`, and `Optimization` times are comparable to total execution time, JIT is the problem. Fix it by setting `jit = off` (reloadable, no restart needed) or by raising `jit_above_cost` so only genuinely expensive plans qualify. --- ## Parallelism PostgreSQL can use multiple CPU cores for individual queries. ``` max_worker_processes = 8 # Total background workers. Default 8. Requires a RESTART. max_parallel_workers = 8 # Max workers for parallel queries. Default 8. max_parallel_workers_per_gather = 4 # Max workers per query node. Default is 2. This is an INCREASE. min_parallel_table_scan_size = 8MB # Minimum table size for parallel scan. Default 8MB. ``` Note that only `max_parallel_workers_per_gather` differs from its default here, and it is being raised, not lowered. The default is 2; 4 suits analytics-leaning workloads with spare CPU. Parallel queries help large analytical queries (aggregations, sorts, scans on big tables). They do not help OLTP workloads with many small queries. For OLTP-dominated workloads, leave `max_parallel_workers_per_gather` at its default of 2, or set it to 0 to disable parallel query entirely, to reserve CPU for concurrent connections. --- ## Write-Ahead log (WAL) ### Wal_buffers Buffer for WAL data before it is written to disk. - **Default:** -1 (auto: 1/32 of `shared_buffers`, but **capped at one WAL segment**. 16 MB with the default segment size) - **Recommended:** 64 MB explicitly on write-heavy servers. Requires a **restart** to take effect. That cap is the whole reason people set this explicitly. On a server with 8 GB of `shared_buffers`, 1/32 would be 256 MB, but the auto value clamps to 16 MB. Under heavy concurrent commit traffic that ceiling becomes a contention point, and raising `shared_buffers` will never move it. ### Checkpoint_timeout The maximum time between automatic checkpoints. This is the other half of checkpoint pacing, and it is the half people forget. - **Default:** 5min - **Recommended:** 15-30min for write-heavy workloads ``` checkpoint_timeout = 20min ``` A checkpoint fires at **whichever comes first**, the timeout expiring or WAL reaching `max_wal_size`. That means raising `max_wal_size` alone does nothing if you are still checkpointing on a 5-minute timer: you have given the server room it never uses. Raise both together, and accept the trade, fewer checkpoints means less repeated full-page-write I/O, but longer crash recovery. To see which trigger is actually firing, check `pg_stat_checkpointer` on PostgreSQL 17 and later, or `pg_stat_bgwriter` before that. If timed checkpoints dominate, raise `checkpoint_timeout`; if requested checkpoints dominate, raise `max_wal_size`. ### Checkpoint_completion_target How much of the checkpoint interval to spread the writes over. - **Default:** 0.9 (PostgreSQL 14+) - **Recommended:** 0.9 ### Max_wal_size Maximum WAL size between checkpoints. - **Default:** 1 GB (`max_wal_size`); 80 MB (`min_wal_size`) - **Recommended:** 4-16 GB for write-heavy workloads ``` max_wal_size = 8GB min_wal_size = 2GB ``` `min_wal_size` is the floor below which PostgreSQL stops removing WAL files and recycles them instead, raising it above the 80 MB default avoids churning through file creation on bursty write workloads. Larger values reduce checkpoint frequency, smoothing I/O at the cost of longer recovery times after a crash. --- ## Autovacuum tuning Autovacuum is critical for PostgreSQL performance. It reclaims dead rows and updates statistics that the query planner depends on. Insufficient autovacuum causes table bloat, index bloat, and stale statistics that lead to bad query plans. ### Default settings are too conservative for large tables | Setting | Default | Recommended for Large Tables | |---------|---------|------------------------------| | `autovacuum_vacuum_scale_factor` | 0.2 | 0.02 - 0.05 | | `autovacuum_analyze_scale_factor` | 0.1 | 0.01 - 0.02 | | `autovacuum_vacuum_cost_delay` | 2ms | 0 (for fast storage) | | `autovacuum_vacuum_cost_limit` | -1 (200) | 1000 - 2000 | | `autovacuum_vacuum_max_threshold` (PG 18+) | 100,000,000 | Lower for very large tables | On a table with 10 million rows, the default `autovacuum_vacuum_scale_factor = 0.2` means autovacuum does not trigger until 2 million dead rows accumulate. Setting it to 0.02 triggers at 200,000 dead rows. PostgreSQL 18 added `autovacuum_vacuum_max_threshold` as an in-core partial fix for exactly this problem: it is a hard ceiling on the scale-factor calculation, so the trigger point is the *lesser* of the scale-factor result and this value. At the default of 100 million, the ceiling starts to bite on tables above roughly 500 million rows, beyond that size it fires before the 20% scale factor ever would. Below that, the scale factor still governs, so on a 10-million-row table you still need to lower `autovacuum_vacuum_scale_factor` yourself. ### Per-Table autovacuum Tune autovacuum per table for high-write tables: ```sql ALTER TABLE events SET ( autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.005, autovacuum_vacuum_cost_delay = 0 ); ``` ### Monitor autovacuum health ```sql -- Tables with most dead tuples SELECT relname, n_dead_tup, n_live_tup, round(100.0 * n_dead_tup / greatest(n_live_tup, 1), 2) as dead_pct, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables WHERE n_dead_tup > 10000 ORDER BY n_dead_tup DESC; ``` --- ## Connection configuration ### Max_connections - **Default:** 100 - **Recommended:** Keep low and use a connection pooler Each PostgreSQL connection uses approximately 5-10 MB of RAM. Setting `max_connections = 1000` wastes memory and increases lock contention. Use PgBouncer or pgpool-II for connection pooling. ``` # With connection pooler max_connections = 100 - 200 # Without connection pooler (not recommended for production) max_connections = number of application servers * connections per server + overhead ``` ### Statement_timeout Prevent runaway queries from consuming resources indefinitely. ``` statement_timeout = 30000 # 30 seconds ``` Set shorter timeouts at the application level for specific endpoints that should respond quickly. --- ## Logging for performance analysis ``` # Enable slow query logging log_min_duration_statement = 1000 # Log queries taking more than 1 second # Enable pg_stat_statements shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track = 'top' pg_stat_statements.max = 10000 ``` Adding `pg_stat_statements` to `shared_preload_libraries` and restarting only loads the library. It does not create the view. You still have to install the extension in each database you want to track: ```sql CREATE EXTENSION pg_stat_statements; ``` Skipping this step is the usual reason `SELECT * FROM pg_stat_statements` returns "relation does not exist" on a server that was just restarted for it. --- ## Cloud-Specific considerations ### Amazon RDS / aurora - `shared_buffers` defaults to a formula over the `{DBInstanceClassMemory}` parameter-group *variable* (in 8 kB pages). It is a **static** parameter, overriding it requires a manual instance reboot - Set `random_page_cost = 1.1` (RDS uses SSD/NVMe) - Aurora has its own storage engine; some WAL settings behave differently - Enable Performance Insights for workload analysis ### Azure database for PostgreSQL - `shared_buffers` defaults to 25% of instance memory (usually correct) - Set `random_page_cost = 1.1` - Enable Query Store for query performance tracking ### Google cloud SQL / AlloyDB - `shared_buffers` managed by instance size - Set `random_page_cost = 1.1` - AlloyDB uses a columnar engine for analytics that changes tuning considerations --- ## How DBGorilla helps DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor) the evidence these settings should be based on, actual cache-hit behavior, temp-file volume from sorts that spilled, autovacuum lag, and the queries driving each, so the recommendation fits your workload instead of a generic table. And because configuration changes are the hardest thing to argue about without data, the agent can apply a candidate setting in an **experiment** against a clone and report the measured effect on your own workload, rather than you reasoning from a formula and finding out in production. It surfaces, explains, and measures; it does not change server configuration or restart anything. [Get started free →](https://app.dbgorilla.com/signup) ## Frequently asked questions **What is the single most impactful configuration change?** For most PostgreSQL deployments on modern hardware: setting `random_page_cost = 1.1` (if on SSD/NVMe storage) and `shared_buffers` to 25% of RAM. Together, these two changes fix the most common planner mistakes and cache inefficiencies. **Should I use PGTune to generate my configuration?** PGTune is a good starting point for initial settings. It generates recommendations based on hardware specs and workload type. However. It does not account for your specific query patterns, data distribution, or concurrency patterns. Treat PGTune output as a baseline, not a final configuration. **How do I know if my configuration is the bottleneck?** If EXPLAIN ANALYZE shows sort spills to disk, the planner avoids indexes on SSD storage, or cache hit ratios are low despite adequate RAM, configuration is likely part of the problem. If queries are slow because of missing indexes or N+1 patterns, fixing the queries will have more impact than tuning configuration. **Can I change PostgreSQL configuration without restarting?** Many settings can be changed with `ALTER SYSTEM SET ... ` and `SELECT pg_reload_conf()` without restart. However, `shared_buffers`, `max_connections`, `shared_preload_libraries`, `wal_buffers`, `max_worker_processes`, and, on PostgreSQL 18+. `io_method` all require a restart. Several of these are settings this article recommends changing, so plan a maintenance window rather than assuming a reload will do. Rather than memorizing the list, check the `context` column in `pg_settings`: `postmaster` means restart, `sighup` means reload, and `user` or `superuser` means it can be set per session with `SET`. ```sql SELECT name, setting, context FROM pg_settings WHERE name IN ('shared_buffers','wal_buffers','max_worker_processes','work_mem','jit'); ``` **How often should I revisit my configuration?** After major workload changes (new features, traffic growth, schema changes), after PostgreSQL major version upgrades, and quarterly as a general cadence. --- ## Related - [Why are my queries spilling to disk, and how do I size work_mem?](https://www.dbgorilla.com/learn/postgres/why-are-my-queries-spilling-to-disk-work-mem/) - [Why is autovacuum not keeping up?](https://www.dbgorilla.com/learn/postgres/why-is-autovacuum-not-keeping-up/) - [When to scale up vs optimize your queries](https://www.dbgorilla.com/learn/postgres/when-to-scale-up-vs-optimize-database-queries/) - [How to identify slow queries in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-identify-slow-queries-in-postgresql/)