When wal_compression stopped being a boolean

The largest thing in the log is a copy of a page

After every checkpoint, the first write to any page copies the whole page into the write-ahead log. That is what stands between a torn write during a crash and an unrecoverable database, and it is also why write-ahead log volume is so much larger than the volume of data actually changed. A one-byte update to a row writes eight kilobytes to the log if that page has not been touched since the last checkpoint.

Compressing those page images has been possible for a long time, and the setting controlling it was a boolean. On, and page images were compressed with the algorithm the server had built in. Off, and they were not. There was no third option and no way to say which algorithm, because there was only one.

PostgreSQL 15 changed the setting from a boolean into a choice. The General Performance section of the PostgreSQL 15 release notes records the addition of two further algorithms, noting that the same setting controls it.

What the setting is, on each version

SELECT name, setting, vartype, enumvals FROM pg_settings WHERE name = 'wal_compression';

On 14:

      name       | setting | vartype | enumvals 
-----------------+---------+---------+----------
 wal_compression | off     | bool    | 
(1 row)

On 15:

      name       | setting | vartype |        enumvals        
-----------------+---------+---------+------------------------
 wal_compression | off     | enum    | {pglz,lz4,zstd,on,off}
(1 row)

That is the change in one row. The type went from boolean to enumerated, and the accepted values now include the original algorithm by name, two alternatives, and the two boolean spellings that were the whole vocabulary before.

Keeping the boolean spellings is what makes this a clean upgrade rather than a breaking one. A configuration file, a monitoring query, or a deployment tool that sets the value to on continues to work and continues to mean what it meant, which is the original algorithm. That is why this page carries no break badge: the old form is still accepted and still does the same thing.

Going the other way is where it stops:

SET wal_compression = 'lz4';

ERROR:  parameter "wal_compression" requires a Boolean value

Which matters when a configuration is written for the newest cluster in a fleet and applied to all of them. A server that meets an unacceptable value in its configuration file does not start.

Measuring it, and the trap in the obvious experiment

The obvious experiment is to set the algorithm, run a workload, read the log volume, change the algorithm, and run it again. That experiment produces numbers that look decisive and are not comparable, and the reason is worth understanding before trusting any figure on this subject.

Page images are written once per page per checkpoint. An update that moves rows to new pages makes the table bigger, so the second run of the same workload touches more pages than the first, so it writes more page images. Whatever you measure second is penalised, and if you run the algorithms in the order everybody runs them, the uncompressed baseline is measured on the smallest version of the table.

The fix is to return the table to the same physical shape before each measurement and to record the page image count alongside the byte count, so that a comparison can be checked rather than assumed.

CREATE PROCEDURE rebuild_ticket() LANGUAGE plpgsql AS $$
BEGIN
  DROP TABLE IF EXISTS ticket;
  CREATE TABLE ticket (id integer PRIMARY KEY, payload text);
  INSERT INTO ticket SELECT g, repeat(md5(g::text), 4) FROM generate_series(1, 20000) AS g;
END $$;
CALL rebuild_ticket();

CREATE PROCEDURE
CALL

SET wal_compression = 'off';
CHECKPOINT; SELECT pg_stat_reset_shared('wal'); CHECKPOINT;
UPDATE ticket SET payload = repeat(md5((id + 1)::text), 4);
SELECT pg_sleep(2);
SELECT 'off' AS compression, wal_fpi, pg_size_pretty(wal_bytes) AS wal_written FROM pg_stat_wal;

CALL rebuild_ticket();
SELECT pg_sleep(2);
SET wal_compression = 'lz4';
CHECKPOINT; SELECT pg_stat_reset_shared('wal'); CHECKPOINT;
UPDATE ticket SET payload = repeat(md5((id + 1)::text), 4);
SELECT pg_sleep(2);
SELECT 'lz4' AS compression, wal_fpi, pg_size_pretty(wal_bytes) AS wal_written FROM pg_stat_wal;

CALL rebuild_ticket();
SELECT pg_sleep(2);
SET wal_compression = 'zstd';
CHECKPOINT; SELECT pg_stat_reset_shared('wal'); CHECKPOINT;
UPDATE ticket SET payload = repeat(md5((id + 1)::text), 4);
SELECT pg_sleep(2);
SELECT 'zstd' AS compression, wal_fpi, pg_size_pretty(wal_bytes) AS wal_written FROM pg_stat_wal;

SET
CHECKPOINT
 pg_stat_reset_shared 
----------------------
 
(1 row)

CHECKPOINT
UPDATE 20000
 pg_sleep 
----------
 
(1 row)

 compression | wal_fpi | wal_written 
-------------+---------+-------------
 off         |     471 | 10 MB
(1 row)

CALL
 pg_sleep 
----------
 
(1 row)

SET
CHECKPOINT
 pg_stat_reset_shared 
----------------------
 
(1 row)

CHECKPOINT
UPDATE 20000
 pg_sleep 
----------
 
(1 row)

 compression | wal_fpi | wal_written 
-------------+---------+-------------
 lz4         |     466 | 7911 kB
(1 row)

CALL
 pg_sleep 
----------
 
(1 row)

SET
CHECKPOINT
 pg_stat_reset_shared 
----------------------
 
(1 row)

CHECKPOINT
UPDATE 20000
 pg_sleep 
----------
 
(1 row)

 compression | wal_fpi | wal_written 
-------------+---------+-------------
 zstd        |     465 | 7385 kB
(1 row)

The page image counts land within a few of each other, which is what makes the byte counts comparable and is the whole point of rebuilding the table between runs. Two details in that block are there for the same reason. The pause after each rebuild lets the write-ahead log counters from the rebuild itself flush before the reset, because a backend reports those counters on a delay and an unflushed rebuild lands in the next measurement instead of the last one. The second checkpoint after the reset is what guarantees that the update that follows has to write a fresh page image for every page it touches.

The reduction here is large because the payload is repetitive text, which is the friendliest possible input for any of these algorithms. Real page images contain tuple headers, line pointers and free space, and they compress less well. Treat the shape of the result as transferable and the ratio as specific to this fixture.

Choosing between them

The trade is processor time against log volume, and it is paid in a place that makes it awkward: the compression happens in the backend that is doing the write, while holding the lock that protects the log insertion. Spending more time there costs concurrency, not just processor cycles.

Three things decide the answer in practice.

• Where the log goes. If it is streamed over a constrained link or archived to object storage that charges per byte, the volume reduction is money and latency. If it goes to a local disk with room to spare, it is neither.
• How much processor headroom the primary has. A cluster already running hot on processor has nothing to spend on the more expensive algorithm.
• Whether your build has the algorithms at all. They are compile-time options, and a server built without one will refuse the value even on 15.

The middle option is the usual answer for a fleet that has not measured: it is meaningfully cheaper in processor time than the original algorithm on most workloads and gives up little ratio. The heaviest option earns its place where log volume is the binding constraint and the primary has cycles to spare.

There is a fourth consideration that is easy to forget. Compression only applies to full page images, not to ordinary records, so the benefit scales with how many page images you write, which is governed by checkpoint spacing. A cluster checkpointing every thirty seconds writes page images constantly and has a great deal to gain; a cluster with widely spaced checkpoints has less. Fixing checkpoint spacing first sometimes removes the reason to change this setting at all, and the write-ahead log view added in PostgreSQL 14 is where both effects are visible.

What to change and how

The setting can be changed with a configuration reload and takes effect for records written after it. No restart, no rebuild of anything, and mixed compression within a single log stream is expected rather than a problem: each page image records how it was compressed, so recovery and replicas handle a stream whose algorithm changed partway through.

That makes it an unusually safe thing to try. Change it on a standby-facing primary during a quiet hour, watch the log volume counters for a few checkpoints, and change it back if the processor cost shows up. The measurement above is the shape of the experiment, with the workload replaced by your own and the rebuild replaced by patience, because on a production table you wait for the shape to settle rather than forcing it.

Where the bytes you save actually go

The volume reduction shows up in four places, and which of them you care about decides how much the setting is worth.

Archiving is the most direct. Fewer bytes means fewer segments, which means fewer calls to whatever archives them and less stored at the far end. On object storage priced by volume and by request, both terms fall together.

Streaming replication is the second, and it is the one that shows up as latency rather than as cost. A replica fed over a constrained link is limited by how fast bytes arrive, so halving the bytes halves the time a given transaction takes to reach it. On a cluster whose replication lag is bandwidth-bound rather than apply-bound, this setting is the single largest lever available.

Backup is the third and it is easy to double-count. A backup tool that compresses what it stores is compressing data that is already compressed, so the saving at rest is smaller than the saving in transit. The saving in transit is still real.

Recovery is the fourth and it moves in the opposite direction. Every page image has to be decompressed during replay, which is work the replaying process does single-threaded. On a cluster whose recovery time objective is tight, that is a cost to measure rather than assume, and it is paid exactly when you can least afford it.

One interaction is worth knowing before changing anything. This setting only does anything while full page writes are enabled, which they are by default and should stay unless you are on storage that guarantees atomic page writes. Turning full page writes off makes the compression setting irrelevant and takes on a risk most deployments should not. The counters on this page make the relationship visible: if the page image count is near zero, there is nothing here to compress and the setting will do nothing at all.

What it costs

Reading the counters costs nothing. The compression costs processor time proportional to how many page images you write, and the alternative algorithms shift where that cost sits rather than removing it.

The measurement itself has one real cost worth naming: the rebuild step in the block above drops and recreates the table, which is fine on a fixture and is not a thing you do to production data. That is why the honest experiment on a live cluster is the patient one. Anybody quoting a compression ratio from three workloads run back to back without controlling the page image count is quoting an artefact.