pg_wal growth: four things hold segments

The directory holding the write-ahead log grows when segments are created faster than old ones are released. A segment may be released once every consumer that still needs it has finished with it, and there are only four kinds of consumer: a checkpoint that has not run yet, an archive command that is failing, a replication slot that is not advancing, and a retention setting that was asked for on purpose. The failure mode at the end is the server refusing to write.

Four holds that look identical from the outside

Size alone cannot tell them apart, and each wants a different response.

A checkpoint that has not happened is the benign case and the most common one. Segments before the last checkpoint can be recycled, so a server under a heavy write burst carries several checkpoints’ worth of log and settles back down afterwards. The ceiling here is soft: max_wal_size is a target that triggers a checkpoint, not a limit that is enforced.

A failing archive command is the case that never recovers on its own. Until a segment is reported archived, it is kept, so a command that exits non-zero for a week keeps a week of log. This one is visible in the archiver statistics and in a growing count of files still marked ready to archive.

A replication slot that is not advancing is the case with the worst reputation, because a slot keeps its position whether or not anything is connected to it, and a standby that was decommissioned without dropping its slot holds log forever.

Explicit retention is the honest one: wal_keep_size and, for slots, max_slot_wal_keep_size reserve log deliberately, and the second of them is the setting that converts an unbounded slot into a slot that gets invalidated instead.

Reading the size against the settings

The directory listing and the four settings together narrow it down in one pass.

SELECT count(*) AS segments, current_setting('wal_segment_size') AS segment_size FROM pg_ls_waldir();
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('max_wal_size', 'wal_keep_size', 'archive_mode', 'max_slot_wal_keep_size')
ORDER BY name;

 segments | segment_size 
----------+--------------
        6 | 16MB
(1 row)

          name          | setting | unit 
------------------------+---------+------
 archive_mode           | off     | 
 max_slot_wal_keep_size | -1      | MB
 max_wal_size           | 1024    | MB
 wal_keep_size          | 0       | MB
(4 rows)

Minus one in the slot limit means no limit, which is the shipped default and the reason an abandoned slot can fill a disk. The directory’s size is the segment count times the segment size, and with archiving off and no retention set the six segments above are what a server that has barely written anything keeps. There is nothing there to fix.

Working out which hold you have

Check the slots first, because that is the case that does not resolve itself: the distance from the current position back to each slot’s position is the log that slot alone is keeping. If the slots are close behind, look at the archiver rather than the checkpointer. The queries for both, and the choices about what to do with a slot whose consumer is gone, are in replication lag and slot health. The setting that turns an unbounded slot into a bounded one, and what the server records when it acts, is in replication slot invalidation.