PostgreSQL Storage Bloat Caused by Large Transactions

Summary: In this article, we will explore the issue of storage bloat in PostgreSQL caused by large transactions and its associated impacts.

Table of Contents

Preparing Base Data

Below, we will create a table, insert some data, and perform a cleanup operation on the table data within a PostgreSQL 18.4 database.

CREATE TABLE bloat (id int PRIMARY KEY, filler text);
INSERT INTO bloat (id, filler)
  SELECT i, repeat('x', 128)
    FROM generate_series(1, 10000000) AS s(i);

VACUUM ANALYZE bloat;

Check the size of the space occupied by the table and its indexes:

SELECT relname, pg_size_pretty(pg_relation_size(oid))
  FROM pg_class WHERE relname LIKE 'bloat%';
  relname   | pg_size_pretty
------------+----------------
 bloat      | 1594 MB
 bloat_pkey | 214 MB
(2 rows)

Executing a Large Transaction with Bulk Updates

After preparing the base table and initial data, let’s start a transaction to execute a bulk update operation:

BEGIN;
UPDATE bloat SET filler = repeat('y', 128);

Check the size of the space occupied by the table and its indexes:

SELECT relname, pg_size_pretty(pg_relation_size(oid))
  FROM pg_class WHERE relname LIKE 'bloat%';
  relname   | pg_size_pretty
------------+----------------
 bloat      | 3189 MB
 bloat_pkey | 428 MB
(2 rows)

Space Reclamation After Transaction Completion

Let’s complete the large transaction initiated above.

COMMIT;

After waiting for about 10 minutes, check the size of the space occupied by the table and its indexes:

SELECT relname, pg_size_pretty(pg_relation_size(oid))
  FROM pg_class WHERE relname LIKE 'bloat%';
  relname   | pg_size_pretty
------------+----------------
 bloat      | 3189 MB
 bloat_pkey | 428 MB
(2 rows)

After the full-table update and automatic vacuum, PostgreSQL did not reclaim the space occupied by dead tuples.

Performance of Redrock Postgres

Next, let’s run the same test case in a Redrock Postgres database. Before executing the bulk update operation, let’s check the space occupied by the table and its indexes, as well as the current usage of undo segments in the database:

SELECT relname, pg_size_pretty(pg_relation_size(oid))
  FROM pg_class WHERE relname LIKE 'bloat%';
  relname   | pg_size_pretty
------------+----------------
 bloat      | 1447 MB
 bloat_pkey | 216 MB
(2 rows)

SELECT oid, relname, pg_size_pretty(pg_relation_size(oid)) AS undsize
  FROM pg_class WHERE relkind = 'u'
  ORDER BY pg_relation_size(oid) DESC LIMIT 4;
 oid  |  relname   | undsize
------+------------+---------
 8113 | pg_undo_13 | 1069 MB
 8103 | pg_undo_3  | 1040 kB
 8104 | pg_undo_4  | 1040 kB
 8102 | pg_undo_2  | 1040 kB
(4 rows)

The output shows that the previous INSERT operation generated 1069 MB of undo logs. Next, let’s execute the same bulk update operation:

BEGIN;
UPDATE bloat SET filler = repeat('y', 128);

The UPDATE operation above modified all 10,000,000 records in the table. Let’s check the table and index sizes, along with the undo segment usage, once again:

SELECT relname, pg_size_pretty(pg_relation_size(oid))
  FROM pg_class WHERE relname LIKE 'bloat%';
  relname   | pg_size_pretty
------------+----------------
 bloat      | 1447 MB
 bloat_pkey | 216 MB
(2 rows)

SELECT oid, relname, pg_size_pretty(pg_relation_size(oid)) AS undsize
  FROM pg_class WHERE relkind = 'u'
  ORDER BY pg_relation_size(oid) DESC LIMIT 4;
 oid  |  relname   | undsize
------+------------+---------
 8118 | pg_undo_18 | 1906 MB
 8113 | pg_undo_13 | 1069 MB
 8103 | pg_undo_3  | 1040 kB
 8104 | pg_undo_4  | 1040 kB
(4 rows)

The output shows that the previous UPDATE operation generated 1906 MB of undo logs. Let’s complete the large transaction initiated above.

COMMIT;

After waiting for about 1 hour, check the usage of the undo segments:

SELECT oid, relname, pg_size_pretty(pg_relation_size(oid)) AS undsize
  FROM pg_class WHERE relkind = 'u'
  ORDER BY pg_relation_size(oid) DESC LIMIT 4;
 oid  |  relname  | undsize
------+-----------+---------
 8102 | pg_undo_2 | 1040 kB
 8103 | pg_undo_3 | 1040 kB
 8104 | pg_undo_4 | 1040 kB
 8101 | pg_undo_1 | 1040 kB
(4 rows)

After background automatic cleanup occured, Redrock Postgres is able to fully shrink the expanded undo segment space.

Test Conclusion

In PostgreSQL, the autovacuum background process periodically cleans up dead tuples. However, this type of VACUUM only reclaims space and makes it reusable; in most cases, the extra space is not returned to the operating system. Instead, it remains within the same table to be reused for future writes.

In Redrock Postgres, when a transaction modifies a large number of rows, it inevitably consumes a significant amount of undo segment space—this is unavoidable. Special attention should be paid to UPDATE or DELETE transactions involving massive amounts of rows. In most cases, if supported by the application, batch execution is preferred (e.g., modifying or deleting a few thousand rows per transaction). However, once the space utilized by a specific undo segment exceeds 1 GB, the background maintenance process will automatically shrink it to reclaim the extra space it occupies.