Write amplification caused by updates in PostgreSQL

Summary: In this article, we’ll take a look at the write amplification issue that comes with updates in PostgreSQL, and corresponding solutions.

Table of Contents

Table updates scenario: Use case

In PostgreSQL, let’s create a table, insert some data, and update the table data, and check the number of pages modified by the update operation.

CREATE TABLE boat (id integer, name text, reg double precision);

INSERT INTO boat(id, name, reg)
  SELECT i, md5(i::text), log(i)
    FROM generate_series(1, 500000) as i;

CREATE INDEX boat_idx1 ON boat(id);
CREATE INDEX boat_idx2 ON boat(reg);

VACUUM ANALYZE boat;
CHECKPOINT;

Above, we performed a checkpoint operation to flush all dirty pages in the shared buffer. This allows us to check the modified pages using EXPLAIN ANALYZE:

EXPLAIN (analyze, buffers, wal, costs false)
  UPDATE boat SET name = 'trade winds'
    WHERE mod(id, 1000) = 1;
                             QUERY PLAN
---------------------------------------------------------------------
 Update on boat (actual time=119.992..119.993 rows=0 loops=1)
   Buffers: shared hit=8680 read=1012 dirtied=1509 written=4
   WAL: records=2000 fpi=1501 bytes=11616847
   ->  Seq Scan on boat (actual time=0.021..95.015 rows=500 loops=1)
         Filter: (mod(id, 1000) = 1)
         Rows Removed by Filter: 499500
         Buffers: shared hit=4673
 Planning:
   Buffers: shared hit=73 read=1
 Planning Time: 0.625 ms
 Execution Time: 120.126 ms
(11 rows)

The UPDATE operation above updated 500 records and did not modify columns involved in indexes. A total of 1509 pages were modified, including approximately 500 heap pages and 1000 index pages.

Due to a recent checkpoint occured, it generated 1501 full page image records in the WAL log, with a total WAL data size of 11.08 MB.

Note: The PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint, a mechanism known as full page writes. Its purpose is to prevent the database from being unrecoverable due to partial writes of data blocks in the event of an unexpected system crash. Additionally, if the location of a new tuple (identified by ctid) changes when updating a tuple, the corresponding index entries must also be modified and logged in the WAL. This further triggers full page writes for index pages, exacerbating WAL write amplification.

Table updates scenario: How it works

Here’s what happens to the tuples within a single page, when we perform table updates in PostgreSQL:

PostgreSQL adopts the Multi-Version Concurrency Control (MVCC) mechanism. When a tuple is updated or deleted, the original tuple is not immediately removed but is marked as invisible. This design supports transaction rollback and snapshot reads. After marking the old tuple as invisible, the update operation creates a new row version (new tuple) in the heap table.

A new index entry needs to be created in two scenarios: first, when the new tuple and the old tuple are not located in the same data page; second, when they are on the same data page but the update modifies columns related to indexes. For example, in an order table where the order status is frequently updated, new index entries must be created for each update. If the table has 3 indexes, 4 pages need to be modified for each update.

The solution by Redrock Postgres

Redrock Postgres introduced undo logs, implemented in-place updates when modifying tuples without the need to add index tuples. Because we do not need to modify the index, we help reduce index bloat by avoiding redundant index inserts.

In Redrock Postgres, let’s create a table, insert some data, and update the table data, and check the number of pages modified by the update operation.

CREATE TABLE boat (id integer, name text, reg double precision);

INSERT INTO boat(id, name, reg)
  SELECT i, md5(i::text), log(i)
    FROM generate_series(1, 500000) as i;

CREATE INDEX boat_idx1 ON boat(id);
CREATE INDEX boat_idx2 ON boat(reg);

VACUUM ANALYZE boat;
CHECKPOINT;

Above, we performed a checkpoint operation to flush all dirty pages in the shared buffer. This allows us to check the modified pages using EXPLAIN ANALYZE:

EXPLAIN (analyze, buffers, wal, costs false)
  UPDATE boat SET name = 'trade winds'
    WHERE mod(id, 1000) = 1;
                             QUERY PLAN
---------------------------------------------------------------------
 Update on boat (actual time=36.476..36.478 rows=0 loops=1)
   Buffers: shared hit=4757 read=8 dirtied=509
   WAL: records=507 fpi=503 bytes=4162628
   ->  Seq Scan on boat (actual time=0.026..32.624 rows=500 loops=1)
         Filter: (mod(id, 1000) = 1)
         Rows Removed by Filter: 499500
         Buffers: shared hit=3732
 Planning:
   Buffers: shared hit=5 read=3
 Planning Time: 0.450 ms
 Execution Time: 36.610 ms
(11 rows)

The UPDATE operation above updated 500 records and did not modify columns involved in indexes. A total of 509 pages were modified, including approximately 500 heap pages and a small number of other pages.

Due to a recent checkpoint occured, it generated 503 full page image records in the WAL log, with a total WAL data size of 3.97 MB.

Clearly, the update operation here did not modify any index pages. Moreover, the execution performance is relatively more efficient.