PostgreSQL ships with conservative defaults sized for a small machine, not for production hardware. Tuning means changing a focused set of parameters in postgresql.conf (or ALTER SYSTEM) that control four things: how aggressively the server protects committed data, how many clients can connect, how much memory each operation may use, and what the planner assumes about your storage. Most parameters never need touching. This guide covers the ones that do, grouped by concern, with current defaults for PostgreSQL 17/18 and a sane starting value for each. On managed PostgreSQL (RDS, Cloud SQL, Azure), many of these are provider-controlled or exposed through a parameter group rather than postgresql.conf.
Durability and WAL
The write-ahead log records every change before it touches a data file, which is what gives PostgreSQL its crash safety. The durability parameters trade write throughput against how much committed work you can lose in a crash. Leave the safety-critical ones at their defaults unless you have a specific reason and understand the failure mode.
| Parameter | Default | Recommended | Notes |
|---|---|---|---|
fsync |
on |
on |
Turning it off risks unrecoverable corruption on crash. Never disable on a database you care about. |
full_page_writes |
on |
on |
Protects against torn pages after a checkpoint. Disabling it invites silent corruption. |
wal_level |
replica |
replica |
minimal only if no replication or archiving; logical for logical decoding. |
synchronous_commit |
on |
on |
Set to remote_apply when synchronous_standby_names is set, so commits wait for the standby to apply them. off trades a small data-loss window for throughput. |
checkpoint_timeout |
5min |
15min |
Spreads checkpoint I/O over a longer window, reducing write spikes. Watch timed-vs-requested checkpoints in pg_stat_checkpointer. |
max_wal_size |
1GB |
larger (e.g. several GB) | A small value forces frequent requested checkpoints, which are expensive. Raise it so checkpoints are driven by checkpoint_timeout, not by WAL volume. |
checkpoint_completion_target |
0.9 |
0.9 |
Fraction of the interval over which checkpoint writes are spread. The default is already the long-standing recommendation. |
Checkpoint statistics moved: in PostgreSQL 17 and later the counters live in pg_stat_checkpointer, not the older pg_stat_bgwriter. If you raise checkpoint_timeout and max_wal_size, sample num_timed versus num_requested over a representative window. Requested checkpoints dominating means max_wal_size is still too small for your write rate.
A note on synchronous_commit: it is per-transaction and can be set in a session, so latency-tolerant batch jobs can run with off while critical transactions keep the durability guarantee. That is a more surgical lever than flipping it globally.
Connections
Connection settings cap concurrency and set the per-backend overhead, which interacts directly with the memory parameters below. PostgreSQL uses one OS process per connection, so connections are not cheap.
| Parameter | Default | Recommended | Notes |
|---|---|---|---|
max_connections |
100 |
sized to real concurrency | Hard limit; new connections are refused above it. Each connection reserves memory whether idle or active. |
listen_addresses |
localhost |
interfaces you firewall | Bind only to interfaces protected by firewall or VPC rules. |
superuser_reserved_connections |
3 |
3 |
Slots held back so a superuser can still log in when the pool is exhausted. |
Raising max_connections to absorb load is the common mistake. Past roughly 200-300 connections the per-process overhead and lock contention cost more than they buy, and each backend multiplies the effective work_mem footprint. The fix is a connection pooler - PgBouncer in transaction mode, pgpool, or RDS Proxy - which lets a few hundred application connections share a small set of backend connections. Pool first, raise max_connections second.
Memory
Memory parameters decide how much RAM each query operation and each maintenance task may use before spilling to disk. The trap is that several of them are per-operation or per-backend, so the real total is the configured value times the number of concurrent consumers.
| Parameter | Default | Recommended | Notes |
|---|---|---|---|
shared_buffers |
128MB |
25-40% of RAM | Server-wide buffer cache. Requires a restart. Above ~40% of RAM, the OS page cache is usually a better home for the memory. |
effective_cache_size |
4GB |
50-75% of RAM | Planner hint only - allocates nothing. A higher value biases the planner toward index scans. The source for much of this guidance omits it; do not. |
work_mem |
4MB |
raise cautiously | Per sort/hash operation, per backend. A query with several sorts across many connections can multiply this many times over. |
hash_mem_multiplier |
2.0 |
2.0 |
Hash operations may use work_mem * hash_mem_multiplier, so the effective hash budget is 8MB at defaults. |
maintenance_work_mem |
64MB |
256MB-1GB | Used by VACUUM, CREATE INDEX, and ALTER TABLE. Higher values speed up maintenance; only one or a few run at once, so the cost is bounded. |
temp_buffers |
8MB |
8MB |
Per-session buffers for temporary tables. Raise only if you lean heavily on temp tables. |
Tune work_mem from evidence, not by guessing. Set `log_temp_files` to capture operations that spill, and raise work_mem toward the size of the spilling sorts rather than across the board. A global increase multiplied by hundreds of backends is a classic out-of-memory cause. shared_buffers and effective_cache_size are easy to confuse: the first allocates real RAM, the second is purely advisory and overlaps the OS cache. See debugging a low cache hit ratio for how the two interact.
I/O and Planner Costs
These parameters describe your storage to the query planner. Wrong values make the planner mis-cost plans - typically avoiding index scans on fast storage because it assumes spinning disks.
| Parameter | Default | Recommended | Notes |
|---|---|---|---|
random_page_cost |
4.0 |
1.1 on SSD/NVMe |
Cost of a random page read relative to sequential. The default assumes HDD; on SSD, random reads are nearly as cheap as sequential. |
seq_page_cost |
1.0 |
1.0 |
Baseline; the ratio to random_page_cost is what matters. |
effective_io_concurrency |
16 (PG18); 1 (≤PG17) |
match your storage | PostgreSQL 18 raised the default from 1 to 16 to reflect modern SSD/NVMe and the new asynchronous I/O. On PG17 and earlier, raise it from 1 toward 200 for SSDs. |
maintenance_io_concurrency |
16 (PG18); 10 (≤PG17) |
match your storage | Same idea as effective_io_concurrency, applied to maintenance work. Also bumped to 16 in PG18. |
The effective_io_concurrency default is the parameter most often quoted with stale advice. Older guides recommend 2 for HDD, 200 for SSD, and 300 for a SAN against a historical default of 1. That default held from its introduction through PostgreSQL 17; PostgreSQL 18 changed it to `16` alongside the new asynchronous I/O subsystem. On PG17 and earlier, the old per-storage advice still applies as a starting point. On PG18, start from 16 and raise it for high-IOPS NVMe. On managed cloud storage the practical ceiling depends on the provisioned IOPS, so test rather than copy a number.
Logging and Monitoring
Logging parameters are how you find slow queries and runaway autovacuum without third-party tooling. The defaults log almost nothing, which is useless for diagnosis.
| Parameter | Default | Recommended | Notes |
|---|---|---|---|
log_min_duration_statement |
-1 (off) |
1000-5000 ms |
Logs any statement slower than the threshold. Start high (5s), then lower it as you fix the worst offenders. |
log_statement |
none |
ddl or mod |
mod logs all DDL plus data-modifying statements; ddl logs schema changes only. all is too noisy for production. |
log_autovacuum_min_duration |
10min (600000 ms) |
default | Logs autovacuum actions over the threshold. Keep the default unless investigating vacuum behavior; 0 logs every action. |
application_name |
empty | a recognizable name | Set per service so log lines and pg_stat_activity rows are attributable to the right application. |
log_min_duration_sample + log_statement_sample_rate |
off | optional | Sample a fraction of statements above a lower threshold instead of logging all of them, to bound log volume. |
The slow query log tells you a statement was slow, not why. Closing that gap means correlating pg_stat_statements, EXPLAIN plans, lock waits, checkpoint timing, and the configuration values above - usually by hand, after the incident. Pulse connects to PostgreSQL, tracks both the running configuration and live workload, and flags when a setting drifts out of line with the load it sees - a max_wal_size too small for the write rate, work_mem driving repeated temp-file spills, a checkpoint pattern dominated by requested rather than timed checkpoints - then traces each signal to the queries and settings responsible and recommends a concrete change for a human to approve. For ongoing tuning, that turns configuration review from a periodic audit into continuous feedback. The cloud vendors' own performance dashboards (RDS Performance Insights, Cloud SQL Query Insights, Azure Query Performance Insight) cover part of this for managed instances and are worth enabling at deploy time.
Frequently Asked Questions
Q: Where do I change PostgreSQL configuration parameters?
A: Edit postgresql.conf, or use ALTER SYSTEM SET parameter = value which writes to postgresql.auto.conf. Most parameters take effect with SELECT pg_reload_conf() or SIGHUP; ones marked as requiring a restart, such as shared_buffers and max_connections, need a full server restart. On managed PostgreSQL you change them through a parameter group, not the file.
Q: What is the difference between shared_buffers and effective_cache_size?
A: shared_buffers allocates real RAM for PostgreSQL's own buffer cache, typically set to 25-40% of system memory. effective_cache_size allocates nothing - it is a planner estimate of total cache available (shared buffers plus the OS page cache), set to roughly 50-75% of RAM, and a higher value makes the planner favor index scans.
Q: How should I size work_mem in PostgreSQL?
A: work_mem is allocated per sort or hash operation, per backend, so the total can be many times the configured value under concurrency. Keep the 4MB default unless logs show operations spilling to disk, enable log_temp_files to find them, and raise work_mem toward the size of the spilling sorts rather than globally.
Q: Is it safe to turn off fsync or synchronous_commit to speed up PostgreSQL?
A: Disabling fsync risks unrecoverable corruption on a crash and should never be used on data you care about. synchronous_commit = off is less dangerous - it only risks losing transactions committed in the last fraction of a second, with no corruption - and can be set per-transaction so only latency-tolerant work uses it.
Q: What is the default value of effective_io_concurrency in PostgreSQL?
A: It was 1 from its introduction through PostgreSQL 17. PostgreSQL 18 raised the default to 16 to match modern SSD and NVMe storage and the new asynchronous I/O subsystem. On PG17 and earlier, raise it manually from 1 toward 200 for SSDs.
Q: Why shouldn't I just raise max_connections instead of using a connection pooler?
A: PostgreSQL uses one OS process per connection, so beyond roughly 200-300 connections process overhead and lock contention degrade throughput, and each backend multiplies the effective work_mem footprint. A pooler like PgBouncer lets hundreds of application connections share a small set of backend connections, which scales far better.
Q: Which PostgreSQL parameters require a server restart?
A: shared_buffers, max_connections, wal_level, listen_addresses, and port require a restart. Most others, including work_mem, checkpoint_timeout, max_wal_size, random_page_cost, and the logging parameters, take effect with a configuration reload. The pg_settings view shows each parameter's context, which tells you what is needed.
Related Reading
- PostgreSQL Performance Tuning: The broader optimization picture - configuration, indexing, and query tuning together.
- Debugging Low Cache Hit Ratio: How
shared_buffersandeffective_cache_sizeaffect what gets served from memory. - PostgreSQL VACUUM: Autovacuum tuning and the
maintenance_work_memand logging settings that control it. - Connection Pooling with PgBouncer: Scaling client connections without inflating
max_connections. - Debugging Slow PostgreSQL Queries: Using the slow query log and
log_temp_filesto find what to tune.
