How a read-only transaction can keep a PostgreSQL table bloated

Fictional setting, real lab evidence. Fortex in this series is a fictional setting; Tom and Ben are characters. Their conversations illustrate the reasoning. Measurements and assertions come from the linked Docker lab, not Fortex production incidents.

The Fortex journey · Chapter 3/8

The transaction Ben meant to leave open

A Fortex report needed a consistent view across several reads. Ben chose REPEATABLE READ, fetched the data, and left the connection waiting for the export step. “I haven't written anything,” he explained. “Keeping it open a little longer should be harmless.”

Tom didn't tell him to close it immediately. “If another session updates the data while you wait, what should your next read return?”

“The old value. The report needs to stay consistent.”

“And who keeps that old value available?”

Ben glanced at his MySQL notes. He understood why deleting rows might not shrink a file, but hadn't connected retained history with a promise to a reader. Tom opened two lab sessions: one for Ben's report, one for the writer.

Enough MVCC to follow the experiment

“Does every SELECT cause bloat, then?” Ben asked.

“No. Your need for consistency is real,” Tom said. “Let's identify when the snapshot starts and ends before choosing a transaction boundary.”

The MySQL DELETE experiment separates row removal from disk reclamation. Here, the extra question is when history is safe to reclaim.

The scope is PostgreSQL 17.6, heap tables, REPEATABLE READ, one reading transaction, and a writing session. An autocommit SELECT that finishes is not the same as a long-lived transaction retaining a snapshot.

A tuple is a row version in the heap. A snapshot determines which versions a transaction may see. VACUUM reclaims eligible versions for reuse and performs other maintenance duties. In the lab, session A actually reads after BEGIN, establishing a snapshot before session B writes. BEGIN without the relevant read would not fully reproduce the situation. See transaction isolation.

The promise made to the old reader

“The writer creates versions,” Ben said. “My reader doesn't create them, but it can still need an earlier one.”

“Keep those roles separate,” Tom replied. “If we write ‘SELECT bloats tables’, someone will fix the wrong thing.”

A reads row 1. B updates it and commits. Under REPEATABLE READ, A must still be able to read according to its established snapshot. Removing the old version immediately would break that promise.

Cleanup therefore considers other transactions and mechanisms that might still require history. The routine vacuuming documentation explains reclaimability.

The diagram represents ordering, not measured elapsed time. One controlled update is enough to observe the mechanism without filling a real disk.

Controlled experimental conditions

Ben proposed excluding background cleanup from the measurement sequence. Tom agreed only for the disposable table. They documented that restriction beside the results so an experimental control wouldn't become operational advice.

The digest-pinned container has one CPU, a 512 MB memory limit and 256 MB of disposable tmpfs data, with no published port. The dataset contains 40,000 deterministic 256-character payloads.

Autovacuum is disabled only on this disposable table so background work cannot change the measurement stages. This is an experimental control, not production advice.

CREATE TABLE events (
  id INT PRIMARY KEY,
  payload TEXT NOT NULL
) WITH (autovacuum_enabled = false);
INSERT INTO events
SELECT n, repeat(md5(n::text), 8)
FROM generate_series(1, 40000) n;

The pgstattuple extension measures relation length and tuple classifications. It scans the relation and requires appropriate privileges; running it continuously against a large production table has costs. Its documentation describes classification and concurrent-update limitations.

Run python3 labs/engine.py postgres-mvcc-vacuum from the downloadable source. The driver coordinates sessions using acknowledgement markers rather than hoping a fixed sleep establishes the intended ordering.

Hold history, update, then attempt cleanup

Ben took over the two-session schedule. Alongside tuple counts, he requested another read from the original reader and the same read from a fresh session. “I want evidence that the snapshot still needs the old value, not just that a transaction is open.”

Session A begins and reads:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM events;
SELECT payload FROM events WHERE id = 1;
-- Keep the transaction open.

Session B updates the payloads and vacuums:

UPDATE events
SET payload = repeat(md5('updated-' || id::text), 8);
VACUUM (VERBOSE, TRUNCATE FALSE, ANALYZE) events;

TRUNCATE FALSE deliberately prevents ordinary VACUUM from cutting empty end pages, separating tuple cleanup from file shrinkage. It is an option of VACUUM, not the TRUNCATE TABLE statement.

While A remains open, the driver confirms A still sees the old payload and a new session sees the updated one. It then commits A, repeats the ordinary VACUUM, and finally runs VACUUM FULL.

The chart panels have different units: heap MiB and tuple count. Do not add or equate them. The published report and raw logs provide the measurements.

After A closes, ordinary VACUUM removes the historical tuples but retains the file length under the explicit TRUNCATE FALSE setting. VACUUM FULL rewrites it to about 11.58 MiB. Current row count stays 40,000, and an updated-payload fingerprint matches before and after cleanup.

Reported dead does not mean immediately reclaimable

“The tool calls it dead. Why can't VACUUM remove it?”

Tom pointed at session A's result. “Where would this read get its value if we reclaimed it now?” Ben wrote no longer needed beside dead and checked the tool's classification rules.

The report's dead_tuple_count reflects pgstattuple's visibility classification. It is not proof that every such tuple can immediately be removed by VACUUM. Here, the tool reports 40,000 old tuples while A still reads its original value and the first vacuum cannot reclaim them.

Running autovacuum more often does not remove a snapshot's need for history. Conversely, a dashboard showing an old connection is not proof that it is the cause. Inspect the actual transaction, snapshot, vacuum state, and other retention mechanisms. This experiment demonstrates one cause by introducing and removing it.

Three different actions, three different purposes

“Close the reader, reclaim tuples, possibly keep the file space for reuse,” Ben said, connecting the experiment to MySQL.

“You've separated the three jobs this time,” Tom replied. “Now choose the one Fortex needs.”

Consult VACUUM for locking and space requirements. Timeout behavior is a documented operational option, not an assertion tested here; see idle_in_transaction_session_timeout.

Repeatedly shrinking a table that immediately refills can create a costly allocation-and-rewrite cycle. Fix transaction lifetime separately from deciding whether allocated space must be returned.

Applying the causal result carefully

Ben dropped the plan to hold a transaction through file generation. He proposed a clear database boundary followed by work outside the transaction. Large exports would need a deliberate streaming or snapshot design rather than an assumption that everything fits in RAM. Tom asked him to check consistency requirements and snapshot duration before deciding.

This experiment does not show that any SELECT inevitably fills a server's disk after a fixed duration. Actual growth depends on write rate, transaction duration, relation count, headroom, and other files.

What it does establish is the chain: hold snapshot, create historical versions, observe blocked reclamation, release snapshot, observe reclamation with the same kind of VACUUM. No dataset or resource increase is needed to explain the difference.

For production diagnosis, identify which heap, index, TOAST or nonrelation files grow; inspect transaction age and real retention; distinguish vacuum not running, running too slowly, and being unable to reclaim. Find the owner of a suspicious session before terminating it. After removing the cause, reassess reuse before choosing a file rewrite.

Heap growth can also increase query work; continue with the index and Seq Scan investigation. A session that writes nothing can still influence when someone else's history becomes disposable.

The page Ben takes into tomorrow

Ben wrote: “Not writing data doesn't mean not retaining resources.” He was starting to examine a request's lifetime rather than isolated SQL statements.

The inventory task brought a harder question next: transactions could finish properly, every statement could succeed, and the result could still be wrong.

Previous chapter: There is an index. Why is PostgreSQL still scanning the table? · Next chapter: Two stock decrements, but only one takes effect · Start the journey