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);

VACUUM 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)
  UPDATE boat SET name = 'trade winds'
    WHERE mod(id, 1000) = 1;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Update on boat  (cost=0.00..12173.00 rows=0 width=0) (actual time=111.126..111.127 rows=0 loops=1)
   Buffers: shared hit=7686 read=506 dirtied=1009 written=4
   ->  Seq Scan on boat  (cost=0.00..12173.00 rows=2500 width=38) (actual time=0.027..95.165 rows=500 loops=1)
         Filter: (mod(id, 1000) = 1)
         Rows Removed by Filter: 499500
         Buffers: shared hit=4673
 Planning:
   Buffers: shared hit=13 read=1
 Planning Time: 0.332 ms
 Execution Time: 111.328 ms
(10 rows)

The above UPDATE operation updated 500 records and modified 1009 pages, involving approximately 500 heap table pages and 500 index pages.

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’s Multi-Version Concurrency Control (MVCC) system is designed to reduce locking, by ensuring that updates do not overwrite rows that might still be required by an older transaction, by creating a new row version tuple in the heap table. When the new tuple is not on the same data page as the old tuple, or although they are on the same data page but the update modifies index-related columns, new index entries need to be created. The old tuple is then marked as dead on the heap, ready for cleaning up later.

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);

VACUUM 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)
  UPDATE boat SET name = 'trade winds'
    WHERE mod(id, 1000) = 1;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Update on boat  (cost=0.00..11232.00 rows=0 width=0) (actual time=31.615..31.616 rows=0 loops=1)
   Buffers: shared hit=4753 read=7 dirtied=508
   ->  Seq Scan on boat  (cost=0.00..11232.00 rows=2500 width=38) (actual time=0.014..29.597 rows=500 loops=1)
         Filter: (mod(id, 1000) = 1)
         Rows Removed by Filter: 499500
         Buffers: shared hit=3732
 Planning:
   Buffers: shared hit=2 read=7
 Planning Time: 0.260 ms
 Execution Time: 31.707 ms
(10 rows)

The above UPDATE operation updated 500 records and modified 508 pages, involving approximately 500 heap table pages and a small number of other pages.

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