Calculating WAL Generation for DML Operations in PostgreSQL

 PostgreSQL provides the EXPLAIN command with the ANALYZE and WAL options to measure the WAL generated by a DML operation. This feature helps database administrators investigate the impact of INSERT, UPDATE, and DELETE statements on WAL generation.

In this article, we use EXPLAIN (ANALYZE, WAL) to measure the WAL generated when updating 10 million rows, compare the results with and without an index, and investigate the effect of enabling WAL compression.

1. Test environment

The test uses a person table containing 10 million rows. The table occupies approximately 880 MB, and WAL compression is initially disabled.

vahiddb=# \dt+
                                     List of tables
 Schema |  Name  | Type  |  Owner   | Persistence | Access method |  Size  | Description
--------+--------+-------+----------+-------------+---------------+--------+-------------
 public | person | table | postgres | permanent   | heap          | 880 MB |
(1 row)

vahiddb=# show wal_compression;
 wal_compression
-----------------
 off
(1 row)

2. Calculating WAL generated by an UPDATE

To measure WAL generation, execute the UPDATE statement with the following options:

vahiddb=# EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF)
UPDATE person
SET name = 'Vahid',
    last_name = 'Yousefzadeh';

                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Update on person (actual rows=0.00 loops=1)
   Buffers: shared hit=72295238 read=319243 dirtied=315766 written=273030
   WAL: records=30258061 fpi=212178 bytes=4014818468 fpi bytes=1551338708 buffers full=144689
   ->  Seq Scan on person (actual rows=10000000.00 loops=1)
         Buffers: shared hit=298 read=205744 dirtied=72743 written=109115
         WAL: records=145390 fpi=72743 bytes=610946323 fpi bytes=590673276 buffers full=65677
 Planning Time: 0.116 ms
 Execution Time: 160129.955 ms
(8 rows)

The statement updates all 10 million rows. The WAL section of the execution plan reports the number of WAL records, full-page images (FPIs), and total WAL bytes generated during execution.

WAL: records=30258061 fpi=212178 bytes=4014818468 fpi bytes=1551338708 buffers full=144689

The reported WAL volume is approximately 4.01 GB.

3. Measuring the effect of indexes

Next, create a composite index on the columns being updated:

vahiddb=#  create index indname_lname on person(name,last_name);
CREATE INDEX

Execute the same UPDATE statement with WAL compression disabled:

vahiddb=#  EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF)
UPDATE person
SET name = 'Vahid',
    last_name = 'Yousefzadeh';

                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Update on person (actual rows=0.00 loops=1)
   Buffers: shared hit=104133558 read=452220 dirtied=265562 written=95009
   WAL: records=40487705 fpi=151040 bytes=4310991885 fpi bytes=986566516 buffers full=126317
   ->  Seq Scan on person (actual rows=10000000.00 loops=1)
         Buffers: shared read=278835 written=27966
 Planning:
   Buffers: shared hit=18 read=3
 Planning Time: 0.504 ms
 Execution Time: 262993.363 ms
(9 rows)

The reported WAL volume increases from approximately 4.01 GB to 4.31 GB, while execution time increases from approximately 160 seconds to 263 seconds in the supplied runs:

WAL: records=40487705 fpi=151040 bytes=4310991885 fpi bytes=986566516 buffers full=126317

4. Measuring the effect of WAL compression

Enable WAL compression and repeat the test:

vahiddb=# set wal_compression=on;
SET

The non-indexed test reports approximately 2.65 GB of WAL, compared with 4.01 GB when compression is disabled. This represents an approximately 33.94% reduction in reported WAL volume:

vahiddb=# EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF)
UPDATE person
SET name = 'Vahid',
    last_name = 'Yousefzadeh';

                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Update on person (actual rows=0.00 loops=1)
   Buffers: shared hit=73576559 read=479691 dirtied=272363 written=192157
   WAL: records=30146290 fpi=170676 bytes=2652681749 fpi bytes=211050036 buffers full=30525
   ->  Seq Scan on person (actual rows=10000000.00 loops=1)
         Buffers: shared hit=408 read=278428 dirtied=22268 written=76164
         WAL: records=45527 fpi=22268 bytes=33702425 fpi bytes=26333321 buffers full=2863
 Planning:
   Buffers: shared hit=11
 Planning Time: 0.112 ms
 Execution Time: 154784.519 ms
(10 rows)

WAL compression primarily compresses full-page images rather than all WAL records. Consequently, its effectiveness depends partly on how much of the generated WAL consists of compressible full-page images.