max_wal_size, explained

The setting that is easy to overlook

Every busy PostgreSQL server writes more to disk than the application asks it to. Some of that extra work is normal and healthy. But on write-heavy systems, part of it can be repeated, avoidable work - and how much of that happens is heavily influenced by one setting: max_wal_size. Nothing in daily operation points at it, and the advice that is easy to find online is generic - a fixed number, or a calculator that cannot see your workload. PostgreSQL ships a general-purpose upstream default of 1 GB - the project cannot know how large a server it will run on - and a genuinely busy server can produce that much transaction log in under a minute.

To see why that matters, you need two concepts. Both are simpler than they sound.

Two ideas: the transaction log, and the checkpoint

Before PostgreSQL changes anything in your data files - the files on disk that hold your tables and indexes - it first writes a description of the change to its transaction log (in PostgreSQL language: the WAL, or write-ahead log). The log is the database's insurance against crashes. If the server loses power in the middle of the work, PostgreSQL replays the log on startup and no committed data is lost.

The log cannot grow forever. So from time to time PostgreSQL saves its progress: it takes all the modified data pages - the 8 KB blocks PostgreSQL stores your tables in - that it holds in memory, and writes them out to the data files. This is a checkpoint. After a checkpoint completes, the log written before it is no longer needed for crash recovery, and PostgreSQL can reuse its disk space - as soon as backups and replication no longer need that part of the log either.

In normal operation, a checkpoint starts automatically for one of two reasons, whichever comes first:

• a timer - every 5 minutes by default (checkpoint_timeout);
• volume - the log since the last checkpoint approaches its size budget, 1 GB by default (max_wal_size).

On a quiet server the timer always wins, and there is nothing to tune. On a busy one, 1 GB fills quickly - and that is where the real cost begins.

Why frequent checkpoints are so expensive

Here is the less obvious part. After each checkpoint, the first change to any data page is logged in full: with PostgreSQL's default full_page_writes setting, the entire 8 KB page is copied into the transaction log, not just the few bytes that changed. This is intentional - it protects you from partially written pages if the server crashes during a write.

Now combine the two mechanisms. Each checkpoint resets that rule: after it, the first change to each page is logged in full again. When checkpoints run every few seconds, frequently changed pages are copied into the log in full over and over. That inflated log reaches its 1 GB budget even sooner, which forces the next checkpoint even earlier. The server ends up writing the same busy pages to disk again and again, and logging more data than the workload truly requires.

PostgreSQL does warn you: it prints "checkpoints are occurring too frequently" into the server log. It is easy to miss unless you already watch the server log. And the warning has a blind spot: by default it only appears when checkpoints are less than 30 seconds apart. A checkpoint every 40 seconds is still many times more frequent than the five-minute schedule, but the log stays silent.

What this looks like in numbers

We test every Guardioz tuning rule in an internal benchmark environment before we trust it with anyone's database. Here is one of those experiments: the same 30-minute run of pgbench, the benchmark tool that ships with PostgreSQL, using its default scenario - loosely based on TPC-B, with most statements being writes - repeated on PostgreSQL 18 with four different values of max_wal_size. The experiment compares PostgreSQL's own counters of transaction log volume, data-file write volume, and checkpoints started ahead of the timer - PostgreSQL counts those as "requested" checkpoints. It is not a storage-performance comparison. And because each run completes a different number of transactions, the write volumes are shown per completed transaction, so the settings can be compared fairly.

With max_wal_size at 1 GB, the server recorded a median of 189 requested checkpoints per 30-minute run - on this isolated benchmark server, all of them demanded by log growth: roughly one every ten seconds instead of one every five minutes. At 32 GB the median fell to zero, with a single requested checkpoint in one of the three runs. And after normalizing for completed work, PostgreSQL generated 5.3% less transaction log and recorded 6.9% less data-file write volume per transaction.

Five-minute illustration derived from the median checkpoint rates above; it is not a trace of an individual run.

Raising the setting from 32 GB to 128 GB changed these counters only slightly. Once frequent requested checkpoints had disappeared, a larger limit produced little additional benefit in this experiment, while increasing the worst case for disk usage and recovery time. The goal is enough, not maximum.

The trade-off

A larger max_wal_size has a real price. Disk space: PostgreSQL does not reserve the space up front, but under heavy write load the log can genuinely grow to the limit you set - so plan for it. Recovery time: after a crash, PostgreSQL replays the log written since the last checkpoint, so checkpoints spaced further apart can make that restart take longer. For most systems this is an acceptable trade - but make the choice on purpose, not by accident.

Also know that max_wal_size is a soft limit. The log can grow beyond it: under heavy load, when transaction log archiving (copying the transaction log to backup storage) is failing, or when the server is told to keep the log around for a replica that has fallen behind or disappeared (a replication slot, or a high wal_keep_size). Never size it so close to disk capacity that going past the limit could fill the disk.

How to tune it yourself

PostgreSQL exposes separate counters for checkpoints scheduled by the timer and checkpoints requested for other reasons - comparing them is where the diagnosis starts. Connect to your database with psql or any other SQL client you normally use, and run:

SELECT num_timed, num_requested, stats_reset FROM pg_stat_checkpointer;

(On PostgreSQL 16 and older, the same counters are checkpoints_timed and checkpoints_req in pg_stat_bgwriter.) Two things to know: the counters cover the whole PostgreSQL instance - every database in it, not only the one you are connected to - and they add up from the last statistics reset, whose time the stats_reset column shows. Take one reading immediately before a representative busy period and another immediately after it, then compare the differences.

• Nearly all timed? Leave the setting alone - even the 1 GB default is correct for that server. A change without a signal has nothing to gain, and something to risk.
• Many requested? That is often log-size pressure, but not only: manual CHECKPOINT commands, backups starting, and a few other events are counted here too. Confirm the cause first - with log_checkpoints enabled (the default since PostgreSQL 15), the server log names it for every checkpoint, and checkpoint starting: wal means log size. When log size is the cause, raise max_wal_size and check the counters again. The widely used rule among PostgreSQL experts is to raise it until checkpoints are almost always started by the timer - but never further than your disk can safely absorb. The log must have room to grow to the new limit, and somewhat past it (the limit is soft), and the disk still needs a healthy reserve of free space on top of that.
• Counters jump only during a nightly import or a restore? Then do not tune the whole server around a single short burst. If that job's speed genuinely matters to you, treat it as its own task; for everything else, tune for how the server works on a normal day.

The change itself is cheap: no restart, no downtime. For example, to set 8 GB - again in an SQL session, this time connected as a superuser:

ALTER SYSTEM SET max_wal_size = '8GB';
SELECT pg_reload_conf();

The 8 GB here only demonstrates the command - it is not a recommended value. The right number for your server comes from its own transaction log volume, and you confirm it the same way you found the problem: read the counters again over a busy period.

On managed services it is the same setting in a different place - a parameter group on Amazon RDS, server parameters on Azure Database for PostgreSQL, database flags on Google Cloud SQL - and all three apply it without a restart. Some of them also pick their own starting value - Azure, for example, sizes it from your disk - so check what your server actually runs: open an SQL session, the same way as above, and run SHOW max_wal_size;

On cloud storage, these writes are on your bill

On your own hardware, avoidable writes use up disk performance you need for real work, and they wear the disks. On cloud storage they can also cost you money, because cloud disks are billed for provisioned performance: you reserve a ceiling of disk operations per second (IOPS), and you pay for that reservation every month whether you use it or not. AWS (gp3, io1, io2), Azure (Premium SSD, Premium SSD v2, Ultra) and Google Cloud all price disk performance this way, in slightly different shapes - included baselines, per-IOPS charges, or IOPS that grow with the disk size you buy.

A database with frequent checkpoints keeps its disks busier than the workload requires, so the performance ceiling you reserve - and pay for - has to cover wasted writes too. Reduce them, then measure the workload again: when the need has dropped, the same database can fit into a smaller tier, or inside the baseline you already pay for. The saving arrives when you lower what you provision, not by itself. This is not only our view: Microsoft's own troubleshooting guide for high I/O on Azure PostgreSQL recommends exactly this - raise max_wal_size until checkpoints are started by the timer.

One exception worth knowing: Amazon Aurora replaces PostgreSQL's storage layer, so the write mechanics and the tuning procedure described here do not carry over to it directly - what max_wal_size does there depends on your Aurora engine version, so check its documentation and parameter group. Everything else in this article applies to RDS for PostgreSQL, Azure, Cloud SQL, and any server you run yourself.

You do not have to do this by hand

Everything above is what Guardioz automates. It starts with measurement: how much transaction log your server produces under its normal, everyday load, and whether checkpoints are being forced ahead of schedule by that log growth. When the measurements say the setting is fine, Guardioz tells you exactly that - no change needed. When there is a real signal, it recommends a max_wal_size fitted to what your server actually does - not a calculator average - ignores one-off bursts, and first checks that the data disk keeps a safe reserve of free space. The recommendation arrives with the evidence next to it, and applies without a restart.

Your PostgreSQL deserves better.