Comparative Analysis of PostgreSQL Update Performance

Summary: In this article, we will explore the performance characteristics of PostgreSQL update operations and the underlying principles behind it.

Table of Contents

Table Data Update Scenario: Use Case

To clearly observe the WAL log overhead of UPDATE operations, we first set the parameter full_page_writes to off.

SHOW full_page_writes;
 full_page_writes
------------------
 off
(1 row)

We then create a table in the PostgreSQL database, insert sample data, perform table vacuum operations, and examine the performance of update operations.

\set fillerlen 64
\set fillfactor 100
\set scale 100
\set updatebid 0

DROP TABLE IF EXISTS pgbench_accounts;
CREATE TABLE pgbench_accounts (
  aid      int PRIMARY KEY,
  bid      int,
  filler   char(:fillerlen),
  abalance int
) WITH (fillfactor = :fillfactor);

INSERT INTO pgbench_accounts(aid, bid, filler, abalance)
  SELECT aid, (aid - 1) / 100000 + 1, '', 0
    FROM generate_series(1, :scale * 100000) as aid;

VACUUM ANALYZE pgbench_accounts;
CHECKPOINT;

A checkpoint operation was executed earlier to flush all dirty pages in the buffer pool. This allows us to check the modified pages via EXPLAIN ANALYZE:

EXPLAIN (analyze, buffers, wal, costs false)
  UPDATE pgbench_accounts
    SET bid = bid + :updatebid, abalance = abalance + random(-5000, 5000)
    WHERE mod(aid, 1000) = 1;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Update on pgbench_accounts (actual time=491.377..491.378 rows=0.00 loops=1)
   Buffers: shared hit=213611 dirtied=20142 written=133
   WAL: records=30003 bytes=2720132
   ->  Seq Scan on pgbench_accounts (actual time=0.026..449.655 rows=10000.00 loops=1)
         Filter: (mod(aid, 1000) = 1)
         Rows Removed by Filter: 9990000
         Buffers: shared hit=133334
 Planning:
   Buffers: shared hit=25 dirtied=1
 Planning Time: 0.156 ms
 Execution Time: 491.429 ms
(11 rows)

Note: In the above UPDATE operation, the updatebid parameter controls whether the bid column is updated. When updatebid is set to 0, the value of the bid column remains unchanged.

The above UPDATE operation modified 10,000 records without altering index-related columns. A total of 20,224 pages were modified, generating 30,003 WAL log entries with an overall WAL log size of 2.6 MB.

We ran the above test cases sequentially with fillerlen set to 64 and 128, fillfactor set to 100 and 80, and updatebid set to 0 and 1. The collected I/O metrics are shown below:

PostgreSQL Update Performance Data Analysis

Based on the above update performance test data, the following conclusions can be drawn:

1. When the fillfactor is set to 100, updates require modifying both heap table pages and index pages, with full heap tuples and index records written to WAL logs, resulting in the lowest update efficiency.
2. When the fillfactor is set to 80, HOT updates can be performed within a single heap table page. If only one column is updated, only the new value of that single column needs to be recorded in WAL logs, delivering the highest update efficiency.
3. With fillfactor set to 80, HOT updates are supported within a single heap table page. When two columns are updated, WAL logs need to record the values of both columns and the data between them. In the test results, when updatebid is set to 1 (updating the bid column), the WAL log size changes significantly as the filler column length increases from 64 to 128.

In addition, for the performance impact of indexes on PostgreSQL UPDATE operations, please refer to Write amplification caused by updates in PostgreSQL. In short, the time consumed by PostgreSQL’s update operations is proportional to the number of indexes in the table. In contrast, the update performance of Redrock Postgres can always remain stable.

Performance of Redrock Postgres

Redrock Postgres introduces undo logs, enabling in-place tuple updates without additional index record insertion. Since index modifications are eliminated, redundant index insert operations are avoided, effectively reducing index bloat.

We executed the same test cases on Redrock Postgres as above, and collected the corresponding I/O metrics.

The test results show that the I/O overhead remains relatively stable regardless of adjustments to the fillfactor parameter or single/multiple column updates. The behavior of update operations keeps consistent performance under all tested scenarios.