Repeated Updates on Table Row Performance Degradation in PostgreSQL Transaction

Summary: In this article, we’ll look into the performance degradation caused by repeatedly updating table rows in a PostgreSQL transaction, and the principles behind it.

Table of Contents

Preparing the Test

Below, we will create a table in a PostgreSQL database and insert some data to examine the performance of update operations.

CREATE TABLE pgbench_accounts (
  aid      int PRIMARY KEY,
  bid      int,
  filler   char(64),
  abalance int
);

INSERT INTO pgbench_accounts(aid, bid, filler, abalance)
  SELECT aid, 1, '', 0
    FROM generate_series(1, 10) as aid;

First, let’s create a simple stored procedure to repeatedly update the first row of the table in a loop:

CREATE OR REPLACE PROCEDURE transaction_update_test(updatebid boolean)
AS $$
DECLARE
  v_start_ts  timestamp;
  v_start_lsn pg_lsn;
  v_start_table_size bigint;
  v_start_index_size bigint;
  v_updates int := 100000;
  i int;
BEGIN
  v_start_ts  := clock_timestamp();
  v_start_lsn := pg_current_wal_insert_lsn();
  v_start_table_size := pg_relation_size('pgbench_accounts');
  v_start_index_size := pg_relation_size('pgbench_accounts_pkey');

  FOR i IN 1..v_updates LOOP
    IF updatebid THEN
      UPDATE pgbench_accounts SET bid = bid + 1 WHERE aid = 1;
    ELSE
      UPDATE pgbench_accounts SET filler = '' WHERE aid = 1;
    END IF;
  END LOOP;

  COMMIT;

  RAISE NOTICE 'Execution Time: %', (clock_timestamp() - v_start_ts);
  RAISE NOTICE 'Disk Usage (WAL Written): %',
               pg_size_pretty(pg_current_wal_insert_lsn() - v_start_lsn);
  RAISE NOTICE 'Disk Usage (Table Size): % (Initial: %)',
               pg_size_pretty(pg_relation_size('pgbench_accounts')),
               pg_size_pretty(v_start_table_size);
  RAISE NOTICE 'Disk Usage (Index Size): % (Initial: %)',
               pg_size_pretty(pg_relation_size('pgbench_accounts_pkey')),
               pg_size_pretty(v_start_index_size);
END $$ LANGUAGE plpgsql;

Performance Testing of Repeated Updates on a Single Row

We will test the following 2 different scenarios:

• Updating the bid column value: Incrementing the value of the bid column in the first row of the table in a loop.
• Updates with no change in column values: Updating the first row of the table in a loop without changing any column values.

Calling the stored procedure transaction_update_test in PostgreSQL with the updatebid parameter set to true yields the following results:

CALL transaction_update_test(true);
NOTICE:  Execution Time: 00:01:43.228422
NOTICE:  Disk Usage (WAL Written): 7345 kB
NOTICE:  Disk Usage (Table Size): 10 MB (Initial: 8192 bytes)
NOTICE:  Disk Usage (Index Size): 40 kB (Initial: 16 kB)

The stored procedure transaction_update_test was called in PostgreSQL with the updatebid parameter set to true and false respectively. The same tests were performed in Redrock Postgres, and the collected results are as follows:

As can be seen from the test results, the performance of repeatedly updating a single row within a PostgreSQL transaction is about 150 times worse than that of Redrock Postgres.

Conclusion

In PostgreSQL, when multiple UPDATE operations are performed on the same row of data, repeatedly updating the same row within a single transaction leads to a continuous performance degradation as the number of updates increases.

This phenomenon is an inevitable consequence of PostgreSQL’s MVCC (Multi-Version Concurrency Control) mechanism:

1. PostgreSQL’s UPDATE is an out-of-place update: Each UPDATE execution generates a brand-new row version and merely marks the old version as dead, rather than directly overwriting it.
2. In autocommit or independent transaction mode, once a transaction commits, the system can immediately determine that the old row versions are no longer visible to any active transactions. Consequently, the storage space can be reused later through space reclamation mechanisms, keeping the total number of row versions dynamically stable.
3. In an uncommitted long transaction, all historical row versions generated within the transaction can neither be reclaimed by the background process nor skipped by the update logic. Each new UPDATE must traverse the full version chain of that row from beginning to end to locate the latest version visible to the current transaction before executing the write. The cost of a single update grows linearly with the number of versions, resulting in an overall O(N²) time complexity, which ultimately manifests as a continuous drop in performance.

In contrast, Redrock Postgres achieves in-place updates when modifying tuples due to the introduction of undo logs, keeping update performance consistently stable.

References

PostgreSQL Community: How to avoid UPDATE performance degradation in a transaction

Stack Overflow: Why is Postgres transaction block so much slower?