Skip to main content

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:

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​

SettingDefaultRecommended for Large Tables
autovacuum_vacuum_scale_factor0.20.02 - 0.05
autovacuum_analyze_scale_factor0.10.01 - 0.02
autovacuum_vacuum_cost_delay2ms0 (for fast storage)
autovacuum_vacuum_cost_limit-1 (200)1000 - 2000
autovacuum_vacuum_max_threshold (PG 18+)100,000,000Lower 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:

ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 0
);

Monitor autovacuum health​

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:

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 →

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.

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.