Expecting Postgresql WAL to shrink in size but it's not

Question

We set our max_wal_size to 24 GB recently (from the 1 GB default), did some testing, and then set it to 12 GB followed by a server restart. When I query the size of the WAL on the filesystem (total size of files in pg_xlog directory), it still shows as about 20 GB. I have issued a manual checkpoint and restarted the server, but the WAL hasn't shrunk back down to 12 GB. This is a pretty simple implementation -- no replication nor archiving in place and there are no long-running transactions that exist. Am I misunderstanding how this is supposed to work? Does it not delete the old WAL files when you lower the max_wal_size value and when all transactions have been been completed and the server restarts?

Postgres version: 9.6.15

OS: Linux Ubuntu 16.04.6 LTS

Settings:

max_wal_size = 12GB

min_wal_size = 80MB

wal_keep_segments = 0

checkpoint_timeout = 15min

checkpoint_completion_target = 0.87

wal_compression = off

archive_mode = off

wal_level = minimal

Answer 1

It looks at "max_wal_size" to decide if WAL freed up by the just-finished checkpoint should be recycled or deleted. But WAL files that have already been recycled (renamed in anticipation of future re-use) in prior checkpoints while "max_wal_size" was high will not get deleted when "max_wal_size" is lowered. They will eventually work their way out of the system as they get reused and then deleted (rather then recycled again).

If you can't wait for that to happen, you will have to delete them manually, which is a rather perilous thing to do--if you mess it up you will destroy your database.

• DBEnthusiast: Thanks for the insight on this.Very helpful. Definitely will not attempt perilous deletes! Just wanted to better understand the behavior and what to expect. Thanks again.
• Laurenz Albe: Essentially, the more data modification activity you generate, the faster pg_xlog (pg_wal in recent versions) will shrink.
• DBEnthusiast: And I can confirm now that it has shrunk down to 10.7GB after running some additional data modification queries.