21:28

Change This: wal_compression

30 March 2018

(An intermittent series on PostgreSQL parameters whose default settings you should change.)

Introduced in PostgreSQL 9.5, wal_compression is off by default, but you should probably turn it on.

First, what does it do? The documentation helpfully explains:

When this parameter is on, the PostgreSQL server compresses a full page image written to WAL when full_page_writes is on or during a base backup.

By default (that is, when full_page_writes = on, which is the default setting), the first time a data page is changed after a checkpoint, the entire data page is written into the write-ahead log. (Thomas Vondra has a good explanation of this and why it is done).

This results in a significant burst of extra WAL traffic after a checkpoint. (This is one of the reasons to increase the time period between checkpoints). This setting, when on, compresses those full pages images so that they don’t take up quite so much WAL space, reducing both the disk required to hold the WAL, and the amount of network traffic required to send the WAL information down to a streaming replica.

It’s almost always a benefit to turn it on. The only time it might hurt performance is if the database is heavily CPU-limited, so that the (small) extra CPU required to compress the WAL information is significant. That’s unlikely, so turn it on.

There is one comment.