PostgreSQL Hot Row Update Performance Testing

Summary: In this article, we will explore the impact of long transactions on the performance of hot row updates in PostgreSQL.

Table of Contents

Hot Row Update Scenarios

Hot rows refer to data rows in a database that undergo frequent modification operations. Under high-concurrency scenarios, updating hot rows generates excessively long data row version chains in PostgreSQL tables, severely impacting system performance.

In scenarios such as major e-commerce promotions and high-frequency financial trading, the same record (e.g., account balance, product inventory) is rapidly and repeatedly modified. This generates an ultra-long data row version chain, causing a massive number of dead tuples to quickly accumulate in tables and indexes. This leads to a drastic bloat in file size, affecting subsequent update and query performance. If the execution time of an update operation increases, it will have a significant negative impact on the business level.

To address these types of problems, simply upgrading computer hardware configurations can no longer meet such low-latency demands. Below, we will construct a test case for this scenario to conduct testing and analysis.

Preparing the Base Data

Adjust the database configuration parameters (modify the postgresql.conf configuration file):

shared_buffers = 1GB
max_wal_size = 4GB
synchronous_commit = off

Note: We set the synchronous_commit parameter to off here to simulate a business workload supported by high-speed storage devices. It is not recommended to do this in an actual production environment.

Next, in a PostgreSQL 18.4 database, we will create a table and insert 100 rows of data.

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

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

Create a new test.sql file with a custom SQL script to define the business workload for frequently updating hot rows:

-- test.sql
UPDATE pgbench_accounts SET bid = bid + 1 WHERE aid = 100;

After preparing the base table and initial data, let’s start a session, begin a transaction within that session, and keep it in an idle state:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT filler FROM pgbench_accounts WHERE aid = 1;

Next, we will test the performance of frequently updating hot rows under two scenarios: with and without the idle transaction started above.

Running Benchmark Pressure Test

Run the test using pgbench combined with the -f parameter to specify the custom script:

pgbench --no-vacuum -f test.sql --time=600 --client=8 --jobs=8
pgbench (18.4)
transaction type: test.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
maximum number of tries: 1
duration: 600 s
number of transactions actually processed: 300144
number of failed transactions: 0 (0.000%)
latency average = 15.993 ms
initial connection time = 5.147 ms
tps = 500.216495 (without initial connection time)

Finally, we commit and end the idle transaction from earlier.

COMMIT;

Test Results Comparison

Summarizing and comparing the test results above, we have:

The presence of long transactions has a massive impact on the performance of PostgreSQL’s hot row updates. For Redrock Postgres, however, it is generally able to maintain stable performance regardless.

Conclusion

In PostgreSQL, when UPDATE operations are frequently executed on the same row of data in the presence of long transactions, performance will continuously degrade as the number of updates increases.

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

1. PostgreSQL’s UPDATE is not an in-place update: Each UPDATE execution generates a completely new row version, merely marking the old version as invalid rather than overwriting it directly.
2. In the presence of a long transaction, all historical row versions generated by updates can neither be reclaimed by background processes nor skipped by the update logic. Every new UPDATE requires traversing the row’s complete version chain from start to finish to locate the latest version visible to the current transaction before writing can be executed. The cost of a single update increases linearly with the number of versions, resulting in an overall operation time complexity of $\mathcal{O}(N^2)$, which ultimately manifests as a continuous decline in performance.

In contrast, Redrock Postgres maintains consistently stable update performance because it introduces an undo log, allowing for in-place updates when modifying tuples.