Our PostgreSQL database generates approximately 4 TB of WAL files daily. However, on the DR (Disaster Recovery) side, the network and disk performance are not sufficient to process this amount of WAL files. What solutions can we apply for this issue? How can we reduce the number of WAL files? On application tables as create like create table t_1233 as select ... union select ...

Our PostgreSQL database generates approximately 4 TB of WAL files daily. However, on the DR (Disaster Recovery) side, the network and disk performance are not sufficient to process this amount of WAL files. What solutions can we apply for this issue? How can we reduce the number of WAL files? On application tables as create like create table t_1233 as select ... union select ...

• postgresql

• Does the application naively update rows regardless of whether columns in the SET clause change values, or does the WHERE clause include criteria to ensure that at least one of those values is changed? If the former, then that could result in much larger WAL files. Even if none of the column values is changed by an UPDATE statement, a new tuple is created for every row that statisfies the selection criteria. – JohnH Commented Mar 4 at 17:39
• Are there a lot of updates to wide tables but only a few columns are normally updated? If so, then consider revising the structure so that wide data that is seldom updated is stored in a separate table from the frequently updated columns. – JohnH Commented Mar 4 at 17:40

1 Answer 1

The only real answer to that is: perform fewer and smaller changes on the database. WAL is generated whenever you modify something in the database.

That said, you can try to avoid redundant writes as much as possible to reduce the WAL volume. First, you should check if most checkpoints are triggered by timeout:

SELECT num_timed, num_requested FROM pg_stat_checkpointer;

On PostgreSQL releases older than v17, that would be

SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter;

The first number should be way bigger than the second. If it isn't, you can reduce the number of checkpoints by increasing the parameter max_wal_size. You can further reduce the number of checkpoints by also increasing checkpoint_timeout.

Reducing the number of checkpoints can reduce the WAL volume, because PostgreSQL has to write the whole 8kB buffer (minus the hole in the middle) to WAL the first time the buffer is modified after a checkpoint. Fewer checkpoints lead to fewer “full-page images” written to WAL.

If that is not enough, you will have to get stronger hardware.