A LinkedIn comment by Oleg made me research this history. The comment explained that PostgreSQL MVCC (multiversion concurrency control) is not the forty-year-old design people often claim. Multiversion storage existed in Berkeley POSTGRES about forty years ago for time travel, but not for concurrency control. It was a different database with a different transaction and storage model. Vadim Mikheev added PostgreSQL's initial MVCC code in December 1998, and MVCC became a major feature of PostgreSQL 6.5 in June 1999. In 2026, that design pivot is about twenty-eight years old.

It nevertheless has an older storage heritage. Berkeley POSTGRES preserved old records for historical queries, archival storage, and recovery. PostgreSQL removed the user-facing Time Travel feature but retained a heap in which an update could create a new physical tuple and leave its predecessor for later reclamation. In 1998, that inherited property became raw material for a new purpose: consistent reads during concurrent writes.

The transition first appeared under the name LLL, Low-Level Locking. The lock-manager work prepared the conflict modes needed by writers. MVCC added the other half of the application-visible result, nonblocking reads, in which an ordinary reader uses a visible tuple version instead of taking a data lock that blocks a writer. In the July 1998 LLL discussion, Mikheev separated WAL and non-overwriting storage and transaction system from locking and multiversion concurrency control. WAL determines how changes survive a crash. MVCC determines which changes a transaction can see. PostgreSQL eventually used both.

• I created this piece with lots of help from GitHub Copilot, and I really value your feedback about this approach. I didn't rely on AI to save time, whether for me as a writer or for you as a reader: this article is actually longer and took more effort to get right than it would have if I had done it alone. What AI provided was a boost in quality. Honestly, I wouldn't have delved so deeply into research, review, and verification without its support.

1. POSTGRES began with a no-overwrite storage argument

Michael Stonebraker and Lawrence Rowe's 1987 paper, The Design of the POSTGRES Storage System, proposed a storage manager in which an update did not overwrite the old record in place. The current version remained on magnetic disk while historical versions could migrate to archival storage. A tuple carried temporal information and a link into its version history. Old values could be represented as differential records rather than complete copies.

This was not originally a concurrency-control design in the modern PostgreSQL sense. The paper's ambitions included historical queries, auditing, recovery, and support for optical archival media. Its conclusion named three design goals: instantaneous crash recovery, archival records on archival media, and asynchronous housekeeping. Past states were product data, not merely obsolete copies awaiting deletion.

The paper also described a background vacuum cleaner. Its job was to find obsolete historical records, move archival data from magnetic disk to tertiary storage, and reclaim expensive primary space. The name survives, but the continuity can be overstated. Modern PostgreSQL VACUUM does not transfer a queryable database history to write-once optical media. It determines which tuple and index versions are no longer needed by any relevant transaction, makes their space reusable, maintains visibility information, and freezes old transaction metadata. The family resemblance is real, but the contract is not the same.

The paper explicitly compared this design with conventional write-ahead logging. Its recovery argument was that committed old state had not been overwritten, so recovery could select valid versions instead of replaying a long log or performing long transaction rollbacks. That is the reason behind its claim of instantaneous crash recovery. In that 1987 design, no-overwrite was therefore proposed partly as an alternative recovery architecture to WAL, not just as Time Travel's storage format.

ARIES, published by C. Mohan and colleagues in 1992, later gave the database field a particularly influential WAL recovery algorithm built around repeating history during REDO, compensation log records, and fine-grained locking. It postdates the 1987 POSTGRES paper, so POSTGRES was not a reaction to ARIES. Both belong to a broader design space that was already comparing logging, shadowing/version retention, force policies, and recovery time.

No-overwrite storage and WAL still answer different questions:

• no-overwrite asks where a new logical row state is placed
• WAL asks what must reach durable log storage before a changed data page may be written, so crash recovery can redo or otherwise reconcile changes
• multiversion concurrency control asks which row state a transaction may see.

The early recovery promise did not survive intact into pre-WAL PostgreSQL. The PostgreSQL 7.1 WAL documentation states plainly that earlier releases forced data changes to disk at commit and still could not guarantee consistency after a crash. Partially written pages and broken index-to-heap relationships remained possible. PostgreSQL 7.1 added WAL in 2001 to provide REDO recovery and reduce commit-time I/O. Its first WAL release explicitly did not implement WAL-based UNDO. Aborted heap tuples could remain physically present and be rejected using transaction status until reclaimed.

PostgreSQL thus did not adopt ARIES wholesale, nor did WAL replace heap versioning. It combined WAL for durability and crash recovery with tuple versions for transaction visibility. That sequence confirms Mikheev's 1998 point that recovery architecture and concurrency control were separable.

2. Time Travel exposed physical history, and then became a liability

Early POSTGRES made tuple history visible through Time Travel queries. In that world, retaining an old version was not bloat by definition. It could be an answer to a future historical query.

That idea fits relational data in an interesting but qualified way. A normalized schema avoids repeating a product description or customer address in every order, but a historical query must then reconstruct a mutually consistent past across all related tables. System-managed temporal versions can make such joins possible, but normalization does not make them automatic or cheap.

Applications still implement Time Travel selectively even after the database feature disappeared. Tables commonly carry created_at and updated_at, audit tables retain changes, and periodic snapshots preserve larger states. An order document may deliberately embed the product description, tax, and price shown at purchase time, perhaps as JSON, JSONB or BSON (with DocumentDB extension). That duplication is not failed normalization: it records a business fact that must not change when the product catalog changes. The old feature attempted to retain every table's past uniformly. Modern applications usually choose which history has business value.

That promise had a cost. The archived manual says that, as of PostgreSQL 6.2, Time Travel was no longer supported, citing its performance impact, storage cost, and a pg_time file that grew "toward infinite size in a short period of time." The release arrived in October 1997. A November 1997 source commit by Vadim Mikheev was titled "Good Bye, Time Travel!" Cleanup continued into 1998.

This is the first important reversal. PostgreSQL discarded indefinite, user-addressable history, but it did not turn the heap into an overwrite-in-place store. Updates still had the useful ability to leave an older physical version behind. Removing Time Travel changed how long versions had to remain meaningful and who was entitled to see them. It did not erase the storage system's multiversion nature.

Before MVCC, old versions served a narrower transactional purpose. PostgreSQL 6.4's update path already inserted a replacement tuple, set its xmin, and marked the old tuple with the updating transaction's xmax. Its HeapTupleSatisfiesNow() test used those transaction IDs and commit or abort status to reject an uncommitted or aborted replacement and to decide whether the predecessor was still current. The versions therefore supported transactional commit and rollback within the inherited non-overwriting heap, even though ordinary readers did not yet receive the consistent snapshots introduced in 6.5. Vacuum reclaimed versions once the transaction rules no longer needed them. They were short-lived transaction state, not retained historical data.

The distinction is visible in the later release notes. PostgreSQL 6.5 describes its new MVCC as taking advantage of PostgreSQL's "natural multiversion nature." That wording is unusually revealing: MVCC was new as concurrency control, while multiversion storage was already natural to the system.

3. Low-Level Locking turned storage versions into snapshots

In July 1998, a pgsql-hackers thread titled "proposals for LLL, part 1" recorded the design choices while the implementation was being planned.

Mikheev separated two choices:

1. WAL versus a non-overwriting storage/transaction system.
2. Locking versus multiversion concurrency and consistency control.

He emphasized: "These are quite different issues!" and used Oracle as the counterexample to any assumption that WAL implied locking: Oracle combined redo logging with multiversion reads. His practical proposal was to implement multiversion control first and switch PostgreSQL to WAL later. That is close to the sequence the project followed: MVCC in 6.5, WAL in 7.1.

The acronym is explicit in the source. Mikheev's August 1, 1998 commit was titled "Lmgr cleanup, new locking modes for LLL." The changed lock-manager files placed those modes behind the preprocessor symbol LowLevelLocking. In this development context, LLL meant Low-Level Locking. The work reorganized the lock manager and introduced the relation lock modes later used by SQL commands such as LOCK TABLE and SELECT FOR UPDATE. It was preparation for the nonblocking reads delivered by MVCC, not the complete visibility mechanism itself.

Chronology matters:

So 1998 is the design and implementation pivot, not the date at which released PostgreSQL users first received MVCC. That date is June 9, 1999, with 6.5.

The contemporary milestone was concise. On December 16, 1998, Mikheev sent a pgsql-hackers message with the subject "MVCC works in serialized mode!" Its body began "CVS is just updated..." The matching source commit is titled "Serialized mode works!" This was the day after his 65-file initial MVCC commit, not a continuation of the 1987 Time Travel implementation under a new label. "Serialized" was the project's term at that point. It should not be read as the SSI-based Serializable semantics added in PostgreSQL 9.1. CVS was the code management system PostgreSQL used to store and manage the source code before switching to Git.

4. What changed between PostgreSQL 6.4 and 6.5

In PostgreSQL 6.4.2, the visibility core exposed two principal tuple tests, HeapTupleSatisfiesNow() and HeapTupleSatisfiesItself(). PostgreSQL 6.5 added:

• HeapTupleSatisfiesSnapshot() for visibility against a transaction snapshot
• HeapTupleSatisfiesDirty() for callers that must detect in-progress tuple state
• HeapTupleSatisfiesUpdate() for deciding whether a tuple can be changed
• GetSnapshotData(), implemented in the shared-memory process-state code, plus SetQuerySnapshot() and FreeXactSnapshot() for constructing and managing snapshot state

The corresponding header added QuerySnapshot, SerializableSnapshot, and a dirty snapshot object. Heap access, transaction management, the lock manager, and vacuum changed with them. This is important historically: MVCC was not a single visibility predicate dropped into an unchanged server. It required a new tuple interface, snapshot lifecycle, update-conflict rules, isolation-level behavior, and cleanup semantics across the executor and access methods.

Documentation developed alongside the code. On May 26, 1999, Thomas Lockhart integrated an MVCC chapter from material by Mikheev, followed by the wider 6.5 manual integration. Mikheev then added migration notes explaining the practical consequence of nonblocking reads: a row returned by SELECT was not thereby protected against a concurrent update or delete, so applications needing that guarantee had to use SELECT FOR UPDATE or an appropriate table lock. Lockhart gave those notes their own release-documentation subsection on June 3. In October 2000, Tom Lane substantially revised the MVCC chapter, sharpening the descriptions of isolation phenomena, Read Committed, serialization failures, and row locking. The documentation history shows the project turning a new storage behavior into an application contract.

5. A snapshot is not a single number

Oracle explanations often begin with a System Change Number: a query sees data as of a particular SCN, and undo is applied to reconstruct blocks consistent with it. PostgreSQL's ordinary MVCC snapshot is not represented by one global commit sequence number.

Conceptually, a PostgreSQL snapshot says:

• transactions below xmin are old enough that none was still running when the snapshot was taken
• transactions at or above xmax had not yet been assigned at that point and are therefore in the snapshot's future
• transaction IDs in xip were in progress and must be treated accordingly
• subtransaction state in subxip and its overflow handling refine that set
• curcid distinguishes commands inside the transaction, allowing a transaction to observe its own work with SQL's command-order semantics.

The exact structure has evolved. Current SnapshotData includes more fields and special snapshot kinds than this summary, and transaction-ID wraparound makes comparisons more subtle than ordinary integer ordering. But the durable idea is a pair of horizons plus an exception set, rather than a database-wide commit timestamp.

Current GetSnapshotData() in procarray.c constructs this information from the ProcArray, shared state that tracks active backend transactions. Heap access then asks HeapTupleSatisfiesMVCC() whether an individual tuple is visible under that snapshot.

For an ordinary heap tuple, the decisive physical fields include:

• t_xmin, the XID that inserted this tuple version
• t_xmax, normally the XID that deleted or superseded it, though the field can also encode row-lock or multitransaction state
• t_ctid, the physical location of this version or a newer version
• t_infomask bits that cache facts and qualify how the transaction fields are to be interpreted.

A simplified visibility question is: did the inserting transaction commit in time for this snapshot, and did no deleting transaction commit in time to hide it? Real code must also handle the current transaction, command IDs, subtransactions, aborted transactions, multixacts, locks, and hint bits.

Hint bits are a small but characteristic optimization. Once PostgreSQL learns a transaction's durable status from the commit log, it can cache selected status facts in the tuple header. Future readers may avoid repeating the lookup. This is why a read can eventually dirty a heap page even though it changes no logical row data.

This snapshot representation carries a real design trade-off:

A scalar commit sequence number can make comparison and historical positioning simple: a version committed before the snapshot number is potentially visible. But the system must assign, publish, and often persist that order correctly. Oracle's SCN shows that this can be engineered at scale. PostgreSQL's horizon-and-exceptions snapshot chose a different set of costs. Neither representation removes the need to handle active, aborted, and self-visible transactions.

The lineage from 1998 is therefore conceptual and structural, not a claim that today's SnapshotData or visibility function is unchanged source code.

6. An UPDATE is a short-lived history

Suppose transaction 500 inserts a row. Its tuple version has xmin = 500. Later, transaction 620 updates the row. PostgreSQL marks the old version as superseded using xmax = 620 and writes a new tuple whose xmin = 620.

While 620 is uncommitted:

• transaction 620 can see its own new version
• another transaction's snapshot normally sees the old version
• a concurrent writer of the same logical row waits on the row-level conflict.

After 620 commits, new snapshots see the new version. A snapshot that began earlier may still require the old one. Physical history has become a concurrency mechanism: several transactions can agree on different visible versions without a reader forcing the writer to wait.

This also explains why long transactions have a system-wide physical cost. Their old snapshot may keep a low visibility horizon, so versions that are dead to newer transactions cannot yet be removed. Replication slots, prepared transactions, and standby feedback can retain related horizons for different reasons. "Idle in transaction" is therefore not merely untidy client behavior. it can turn a local pause into heap and index retention elsewhere.

7. Indexes make versioning expensive, and HOT makes it tolerable

Ordinary PostgreSQL indexes point to heap item identifiers, not to a timeless logical row. A non-HOT update generally needs index entries for the new tuple version, including for indexes whose key values did not change. Old index entries cannot disappear while some snapshot might still follow them to a visible old heap version.

PostgreSQL 8.3, released in 2008, introduced Heap-Only Tuples (HOT). A HOT update is possible when:

1. the update does not change a column referenced by a non-summarizing index
2. the new tuple version fits on the same heap page.

The existing index entry can then continue to point to the root item identifier, and the heap carries an on-page chain to the appropriate version. Intermediate dead versions can be pruned during normal page access, including some SELECT operations. Their line pointers can be reused without waiting for a full-table vacuum pass.

Table fillfactor came first. It was added by PostgreSQL 8.2 in December 2006, in the same July 2006 patch that introduced index fillfactor. The 8.2 documentation already said that reserved space gave an update a chance to place its new row copy on the same page. HOT arrived in 8.3 and made that existing control more consequential because a same-page update that does not change indexed columns can also avoid new index entries. The trade-off is a larger base heap, potentially more I/O for scans, and wasted RAM, because the reserved free space is cached twice: once in shared_buffers and once in the OS page cache. A lower fillfactor is useful when observed update patterns justify the space, not as a universal setting.

HOT also corrects a common simplification: VACUUM is not the only code that removes dead tuple bodies. Page pruning can reclaim intra-page tuple space during ordinary operation. VACUUM remains necessary for broader heap cleanup, dead index entry removal, visibility-map maintenance, freezing, and space management.

Schema design changes how often HOT's conditions occur. Moving an order's frequently changing status into a narrow status table can avoid rewriting a very wide order row and improve cache locality. It also adds a relation, indexes, joins, and another consistency boundary. If the status column itself is indexed, changing it is not HOT-eligible. If status is an unindexed column in the order table and the page has room, the wide row can still receive a HOT update. Normalization is therefore a workload choice, not a HOT prerequisite.

8. VACUUM decides when history has stopped being evidence

A tuple is not removable merely because a newer version exists. It becomes reclaimable only when no transaction horizon relevant to cleanup can still need it. This turns VACUUM into the physical counterpart of snapshot semantics.

Routine vacuuming performs several distinct jobs:

• identifies dead tuple versions and makes heap space reusable
• removes or arranges removal of dead index entries
• updates the free-space and visibility maps
• marks pages all-visible, enabling index-only scans where the index has enough data to answer a query
• marks pages all-frozen where possible, allowing future anti-wraparound work to skip them
• when requested with ANALYZE, refreshes planner statistics, although VACUUM and ANALYZE are separate operations.

The visibility map keeps two conservative bits per heap page: all-visible and all-frozen. A write clears the relevant promise. Vacuum can set it again after proving the condition. The map is thus a compact summary of work that the heap's versioning design would otherwise force every index-only scan or anti-wraparound vacuum to repeat.

Freezing addresses the fact that normal PostgreSQL transaction IDs are 32-bit, as explained in the documentation on wraparound prevention. Age is interpreted in a moving, modulo-$2^{32}$ space, with roughly two billion XIDs on either side of the current point. If old tuple metadata were left untreated through wraparound, an ancient inserting XID could appear to be in the future and data could seem to disappear. Modern freezing records that an old inserting transaction is unconditionally in the past, commonly through a frozen status bit while preserving the original xmin value in supported page formats.

Autovacuum is consequently part of correctness, not an optional bloat-polishing service. Even a table configured to disable ordinary autovacuum can still receive anti-wraparound vacuuming.

9. MVCC did not by itself make SERIALIZABLE serializable

Another later correction is worth adding to the timeline. A stable snapshot prevents dirty, nonrepeatable, and phantom reads as those phenomena are commonly described, but snapshot isolation can still permit write-skew anomalies. Before PostgreSQL 9.1, the isolation level named SERIALIZABLE was essentially the behavior now called REPEATABLE READ. Those level names try to map to SQL standard definitions built on phenomena observed in non-MVCC, lock-based databases, but what PostgreSQL actually provided under either name was Snapshot Isolation.

PostgreSQL 9.1 added Serializable Snapshot Isolation (SSI), credited in the release notes to Kevin Grittner and Dan Ports. SSI retains MVCC snapshots and tracks read/write dependencies to detect dangerous structures. When concurrent transactions would produce a result inconsistent with every serial ordering, one is aborted and should be retried.

This is another example of layering a new rule over the same physical versions. Tuple versions make nonblocking snapshots possible. They do not alone prove serializability.

10. Undo-based alternatives revisit the storage choice

PostgreSQL's heap format is not the only way to provide PostgreSQL semantics. Three projects have explored moving older versions out of the main tuple stream.

Zheap was an EnterpriseDB prototype for an undo-based PostgreSQL table format. It aimed to update rows in place, keep transaction information in page slots, follow undo chains for older snapshots, reduce tuple and index bloat, and avoid table-wide vacuum for ordinary space reclamation. Its own documentation listed unfinished recovery, rollback, logical decoding, snapshot-too-old, and table-access-method integration work. The original repository's last main work dates from 2019 and the PostgreSQL wiki was last updated in 2021. Zheap was not merged upstream. It remains useful as a concrete design exploration, not a current PostgreSQL storage option.

OrioleDB is a newer index-organized table engine whose architecture uses PostgreSQL's table-access-method and extension interfaces, while currently requiring a patched PostgreSQL build. Its MVCC keeps the current tuple at the head of an undo chain and older row versions in an undo log. It combines that with B-tree primary storage, page merging, copy-on-write checkpoints, 64-bit transaction IDs, and a row-level WAL used for recovery and replication. This moves old-version pressure away from PostgreSQL-style heap chains and removes dedicated table vacuuming for OrioleDB tables, but introduces undo retention, rollback, checkpoint, index, and recovery machinery of its own. As of 2026 the project describes itself as a public beta recommended for experiments and benchmarking, not production use.

YugabyteDB takes another route. Tables and indexes are distributed to tablets, and each tablet is an LSM-based storage engine built on a customized RocksDB. Versions enter immutable SST files, and compaction later merges files and reclaims obsolete data. New state does not overwrite immutable files in place, and cleanup happens asynchronously, echoing the original POSTGRES argument. Compaction is not PostgreSQL VACUUM, though. It is the LSM mechanism that performs the analogous garbage-collection job after MVCC's history-retention rules say a version is obsolete.

These projects do not reject MVCC. They keep the concurrency model and revisit where versions live, how current pages are reclaimed, and how WAL and undo share recovery work. They make Mikheev's 1998 separation of storage/recovery from concurrency control visible again.

11. Oracle reaches consistent reads by a different physical route

Oracle is not an arbitrary comparison here. Mikheev's own 1998 argument, in section 3, used Oracle as its counterexample: proof that combining WAL with multiversion reads was already possible, because Oracle was already doing it. That is why Oracle, specifically, is worth tracing in this much detail.

Oracle's use of multiversion concurrency control goes back further than PostgreSQL 6.5, and further than commonly assumed. According to Oracle vice president Ken Jacobs, writing in Oracle Magazine and reproduced on the Oracle community forums, Oracle version 3, released in March 1983, already introduced nonblocking queries, using data saved in a before image file for both queries and transaction rollback, avoiding read locks, although overall throughput was still limited by table-level locking. Version 4, in 1984, named the resulting guarantee read consistency. Version 6, in 1988, was a further rewrite that replaced table-level locking with row-level locking for better scalability.

In Oracle 5, before-image buffers are their own named memory area, separate from the general data buffers. Before images are tracked per object and used for a consistent-read "snapshot", and the "snapshot too old" error was already there. Oracle 5 already let a reader take an explicit SHARE lock without blocking other readers, but its default write behavior was still table-wide.

Oracle 6 shows the row-level rewrite directly, and complicates it. Its LOCK TABLE help topic adds two modes Oracle 5 never had, ROW SHARE and ROW EXCLUSIVE, described as allowing "concurrent use" and prohibiting only "entire table locks" rather than blocking the table outright. New DBA scripts shipped only with Oracle 6, blocking.sql and locktree.sql (Loaiza, November 1989), query the new v$lock view for row-level enqueue modes named Row-S, Row-X, Share, S/Row-X, and Exclusive, the same vocabulary Oracle still uses internally today. But row-level locking was not simply on by default in every Oracle 6 install: the shipped INIT.ORA sets row_locking = INTENT, and setting it to ALWAYS for full row-level locking required a separately licensed Transaction Processing Option (TPO).

By the time of Oracle7, released in 1992, the Concepts manual already used Multiversion Concurrency Control as a section heading, years before PostgreSQL 6.5 existed. It described rollback segments containing the old values changed by a transaction, called ordinary queries nonblocking, and stated the central result directly: readers do not block writers and writers do not block readers. Oracle9i later replaced manually managed rollback segments with automatic undo tablespaces, generalizing the older term rollback into undo. PostgreSQL and Oracle both provide multiversion read consistency, but "both use MVCC" should not conceal their opposite physical instincts.

PostgreSQL normally leaves old and new tuple versions in the table heap. Its indexes and cleanup machinery must live with those versions until they become globally irrelevant.

Oracle normally changes the current data block and stores its before-image information (as undo records) in what the older manuals called rollback segments and later manuals call undo. A query records an SCN. If it encounters a block changed after that SCN, Oracle applies those old values to construct a consistent-read copy. Oracle's own documentation explains why a reader can fail with "snapshot too old" when required rollback information has been reused. A PostgreSQL long-running snapshot more characteristically prevents cleanup and contributes to bloat. Both systems pay for old readers, but the pressure appears in different places.

The Oracle9i Flashback Query documentation exposed retained transaction history through AS OF SCN or AS OF TIMESTAMP. Oracle 10g added Flashback Version Query over an interval. Oracle 11g added Flashback Data Archive under the Total Recall name, with managed historical storage and a retention policy that avoids depending only on short-lived undo. The historical reversal is appealing. PostgreSQL abandoned database Time Travel and reused versions for concurrency. Oracle used versions for concurrency first, then exposed retained history as Flashback.

Oracle multi-versioning has always been at block level, but there is also a less-known way to version table rows. Oracle Workspace Manager (OWM) is a separate logical versioning layer. When a table is version-enabled, OWM renames the physical table with an _LT suffix and adds row columns beginning with WM_, including WM_VERSION, WM_NEXTVER, and WM_DELSTATUS. It then creates a view under the original table name with INSTEAD OF triggers. The view combines the current workspace metadata with those row-carried fields to expose only the versions relevant to the session's workspace.

Visibility information is attached to stored row versions and interpreted at read time, the same broad shape as PostgreSQL heap MVCC, but the two solve different problems. OWM supplies named workspaces, savepoints, refresh, merge, conflicts, and durable application-visible branches. Oracle undo supplies transaction rollback and consistent reads. Flashback supplies historical query and recovery facilities. These mechanisms can coexist precisely because none of them replaces the others.

12. Two heaps, seen directly

The physical difference is easiest to see by inserting one row and updating it twice, then asking each engine to show every version, the same way I inspected heap pages and B-tree entries directly in PostgreSQL resolves uniqueness through heap tuple visibility.

CREATE EXTENSION IF NOT EXISTS pageinspect;

DROP TABLE IF EXISTS mvcc_demo;
CREATE TABLE mvcc_demo (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   text NOT NULL,
  status text NOT NULL
) WITH (fillfactor = 50);
ALTER TABLE mvcc_demo SET (autovacuum_enabled = false);

INSERT INTO mvcc_demo (name, status) VALUES ('demo row', 'new');
UPDATE mvcc_demo SET status = 'active' WHERE name = 'demo row';
UPDATE mvcc_demo SET status = 'closed' WHERE name = 'demo row';

SELECT ctid, xmin, xmax, * FROM mvcc_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask
FROM heap_page_items(get_raw_page('mvcc_demo', 0))
ORDER BY lp;

An ordinary query sees one row:

 ctid  |   xmin   | xmax | id |   name   | status
-------+----------+------+----+----------+--------
 (0,3) | 46818350 |    0 |  1 | demo row | closed
(1 row)

The heap page still holds all three versions, chained by t_ctid:

 lp |  t_xmin  |  t_xmax  | t_ctid | t_infomask
----+----------+----------+--------+------------
  1 | 46818348 | 46818349 | (0,2)  |       1282
  2 | 46818349 | 46818350 | (0,3)  |       9474
  3 | 46818350 |        0 | (0,3)  |      10498
(3 rows)

EXPLAIN (ANALYZE, BUFFERS) shows what reading that page costs:

 Sort  (cost=59.83..62.33 rows=1000 width=20) (actual time=0.026..0.027 rows=3.00 loops=1)
   Sort Key: lp
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=1
   ->  Function Scan on heap_page_items  (cost=0.01..10.01 rows=1000 width=20) (actual time=0.021..0.021 rows=3.00 loops=1)
         Buffers: shared hit=1
 Planning Time: 0.025 ms
 Execution Time: 0.039 ms

Each version is a distinct physical tuple at a distinct ctid. t_xmax on one version is the same transaction as t_xmin on the next, so the chain records who superseded whom. Only the last line pointer, whose t_ctid points to itself, is the current version.

Oracle's Flashback Version Query shows the same idea from the other side. Insert one row and update it twice, each in its own committed transaction:

CREATE TABLE mvcc_demo (
  id     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   VARCHAR2(50) NOT NULL,
  status VARCHAR2(20) NOT NULL
);

INSERT INTO mvcc_demo (name, status) VALUES ('demo row', 'new');
COMMIT;
UPDATE mvcc_demo SET status = 'active' WHERE name = 'demo row';
COMMIT;
UPDATE mvcc_demo SET status = 'closed' WHERE name = 'demo row';
COMMIT;

Querying with the same name filter as the PostgreSQL query above, VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE reconstructs its whole history:

SET AUTOTRACE ON

SELECT VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID,
       VERSIONS_OPERATION, ROWID, name, status
FROM mvcc_demo
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE name = 'demo row'
ORDER BY VERSIONS_STARTSCN;

   VERSIONS_STARTSCN      VERSIONS_ENDSCN VERSIONS_XID     V ROWID                NAME       STATUS
-------------------- -------------------- ---------------- - -------------------- ---------- ----------
      50016514928749       50016514945961 0B000900F9FD0200 I AABJ5aAAAAACABzAAA   demo row   new
      50016514945961       50016514962782 08001D00E4240300 U AABJ5aAAAAACABzAAA   demo row   active
      50016514962782                      0B001000F8FD0200 U AABJ5aAAAAACABzAAA   demo row   closed

The ROWID is identical in every row, because Oracle updates it in place and reconstructs older states from undo rather than leaving old tuples behind.

AUTOTRACE shows what that reconstruction costs:

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    51 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY     |           |     1 |    51 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| MVCC_DEMO |     1 |    51 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  db block gets
         21  consistent gets
          0  physical reads
          3  rows processed

There is no index on name, so Oracle does a full table access and, for each row it scans, walks that row's undo chain to answer VERSIONS BETWEEN, turning one physical row into three logical rows at a cost of 21 consistent gets and zero physical reads, because the block was already cached. PostgreSQL's heap_page_items(get_raw_page(...)) above instead reads the one physical page directly, a single buffer hit, and every version is already sitting on it with nothing to reconstruct. Twenty-one buffer touches to rebuild three versions from undo, against one buffer touch to read three versions already stored in place, is the same trade-off as section 8, now expressed as measured numbers rather than as an architecture description: Oracle pays, on read, to reconstruct history that is not stored contiguously with the current row. PostgreSQL pays, on cleanup, to keep history stored contiguously until VACUUM removes it.

Both engines correctly answer "show me every version of this row," but the metadata that identifies a version is the mirror image of the other: PostgreSQL's ctid changes on every version while xmin/xmax name the transactions that created and superseded it, and Oracle's ROWID never changes while VERSIONS_STARTSCN/VERSIONS_ENDSCN/VERSIONS_XID name the time range and transaction instead. That is the distinction from section 11, now visible in two SELECT statements rather than two architecture descriptions.

13. History moves between layers

I discussed the operational consequences of these designs in my FOSDEM 2024 talk, Isolation Levels and MVCC in SQL Databases: A Technical Comparative Study. The broader lesson is not that one versioning architecture has eliminated the weaknesses of another. Each chooses where to retain history, how long to retain it, and who pays to reconstruct or remove it.

Application tables preserve business history only where it matters. PostgreSQL heap tuples preserve transaction history beside current rows. Oracle undo keeps before-images outside current data blocks. LSM engines preserve versions in immutable SST files until compaction. Azure HorizonDB moves the idea below tuples entirely. Its stateless compute replicas send only WAL to the storage layer, and its data storage nodes reconstruct a requested page by replaying that WAL, while Azure Blob storage keeps the durable, longer-term copy of those pages. Cold history in blob storage is the cloud-native descendant of the optical disks imagined in 1987. The analogy is about storage hierarchy, not identical implementations.

14. The old decision is still visible in current operations

The 1987 designers did not secretly implement today's PostgreSQL concurrency control. The 1999 implementation did not secretly contain HOT, visibility maps, modern freezing, SSI, or two decades of scalability work. Historical lineage is not identity.

But the old storage decision constrained and enabled what followed. Because an update could coexist with its predecessor:

• Time Travel could once expose old versions directly
• MVCC could later assign different versions to different snapshots
• VACUUM had to become the arbiter of when obsolete evidence could be reused
• indexes inherited version churn, leading eventually to HOT
• page free space became a concurrency-performance parameter through fillfactor
• old snapshots became an operational retention horizon
• 32-bit tuple transaction metadata made freezing a condi... (truncated)

Percona Search for MongoDB is Percona’s downstream distribution of mongot, the search engine that provides MongoDB’s full-text and vector search capabilities. With this addition, you can power your applications with AI and advanced search techniques – anywhere, and without vendor lock-in. It’s the same search engine that powers MongoDB Atlas Search.  Percona Search for MongoDB … Continued

The post The open way of Percona Search for MongoDB appeared first on Percona.

I see users excited by REPACK in PostgreSQL 19 (currently in beta), because it looks like a simple command that reduces table bloat. But it's not that simple.

Choosing the "right" FILLFACTOR is fundamentally a heuristic problem. For INSERTs, you can estimate future growth based on the expected lifecycle of newly inserted rows. For REPACK, you're repacking existing rows whose future update patterns are largely unknown.

Excess free space is a waste of space, not only on disk, but also in memory, twice: Linux filesystem cache and PostgreSQL shared buffers. However, for rows that are still likely to be updated, that free space helps avoid massive index maintenance thanks to HOT updates.

So you still need to think about FILLFACTOR, but now you need to think about it twice: what you set for future inserts and what you set for REPACK.

Let's look at a typical row lifecycle: you insert a row, it may be updated a few times, and then it is mostly queried and perhaps deleted in the future. A typical example is a customer order: it is inserted, updated while being processed, and then remains unchanged.

FILLFACTOR 100% and first update

I create a table with 4 indexes (primary key and 3 columns) and define no FILLFACTOR, so it defaults to 100, and insert one thousand rows:

drop table if exists events;
create table events(
 id bigserial primary key,
 payload text,
 status text default 'new',
 counter int default 0,
 x int,
 y int,
 z int
);
create index on events(x);
create index on events(y);
create index on events(z);
insert into events(payload)
 select repeat('x',100)
 from generate_series(1,1000)
;

An update increments the counter, which is not indexed, for each row:

postgres=# explain (analyze, costs off, buffers, wal, summary off)
           update events
            set counter=counter+1
            where status='new'
;

                                QUERY PLAN
--------------------------------------------------------------------------
 Update on events (actual time=9.306..9.307 rows=0.00 loops=1)
   Buffers: shared hit=11139 dirtied=27 written=28
   WAL: records=6074 bytes=522582
   ->  Seq Scan on events (actual time=0.389..0.685 rows=1000.00 loops=1)
         Filter: (status = 'new'::text)
         Buffers: shared hit=19 written=1
 Planning:
   Buffers: shared hit=2
(8 rows)

We observe a huge write amplification: updating one thousand rows generates more than six thousand WAL records because each update creates a new row version, updates the visibility information of the previous version, and updates four indexes.

This is typically where PostgreSQL experts recommend a FILLFACTOR lower than the default, to leave enough free space for new versions of rows in the same page. The physical location of the row does not change, the indexes do not need to be updated, only one heap page is modified (HOT update), and only one WAL record is generated per updated row.

But that's moving too fast. Don't change FILLFACTOR without understanding the lifecycle of the rows you insert. Let's see what happens with future updates to those rows.

FILLFACTOR 100% and frequent updates

In my case, if I continue updating the counter on the existing rows, after approximately one hundred updates the write amplification disappears, without changing FILLFACTOR (which was left at its default value of 100% when those rows were inserted):

postgres=# \watch count=120 interval=0.01

...
              Fri 07 Aug 2026 09:13:33 AM UTC (every 0.01s)

                                QUERY PLAN
--------------------------------------------------------------------------
 Update on events (actual time=2.076..2.077 rows=0.00 loops=1)
   Buffers: shared hit=3115
   WAL: records=1026 bytes=75854
   ->  Seq Scan on events (actual time=0.015..0.722 rows=1000.00 loops=1)
         Filter: (status = 'new'::text)
         Buffers: shared hit=1115
         WAL: records=26 bytes=4854
(7 rows)

After a while, all updates become HOT updates because the previous row versions have passed the visibility horizon. Space becomes reusable on each page and new row versions can be placed there. FILLFACTOR only affects inserts by reserving space when rows are created. Updates have their own space management mechanism, storing new versions and reusing space freed by older versions.

FILLFACTOR 100% with inserts and one update

That magic works only for rows that continue to be updated. In real life, however, new inserts also occur, and rows are rarely updated hundreds of times. Typically, new rows are queried and updated for a while, like events being processed, and become mostly read-only once the related business event is completed.

I insert more rows, with the status 'new', and update then to 'old':

postgres=# insert into events(payload)
           select repeat('x',100)
           from generate_series(1,1000)
\;
           explain (analyze, costs off, buffers, wal, summary off)
           update events
           set status='old' , counter=counter+1
           where status='new'
\watch c=120 i=0.01

...

               Fri 07 Aug 2026 09:40:08 AM UTC (every 0.01s)

                                 QUERY PLAN
----------------------------------------------------------------------------
 Update on events (actual time=19.743..19.743 rows=0.00 loops=1)
   Buffers: shared hit=16559 dirtied=25 written=25
   WAL: records=6096 bytes=528646
   ->  Seq Scan on events (actual time=10.412..10.672 rows=1000.00 loops=1)
         Filter: (status = 'new'::text)
         Rows Removed by Filter: 100000
         Buffers: shared hit=3647
         WAL: records=19 bytes=2950
(8 rows)

Now the write amplification is visible again because the updates concern newly inserted rows. Space can be reused once older row versions pass the visibility horizon, but new inserts will reuse that space. As a result, pages remain full, HOT updates are no longer possible, and updating a new row generates six WAL records again.

To improve this, we need to reserve some free space for updates by preventing inserts from consuming it. That's the purpose of FILLFACTOR.

FILLFACTOR 50% for one update

If I set FILLFACTOR to 50%, inserts will not consume free space on a page that is only half full. That space remains available for future row versions created by updates:

postgres=# alter table events set (fillfactor=50)
;

ALTER TABLE

postgres=# insert into events(payload)
           select repeat('x',100)
           from generate_series(1,1000)
\;
           explain (analyze, costs off, buffers, wal, summary off)
           update events
           set status='old' , counter=counter+1
           where status='new'
;
                                QUERY PLAN
--------------------------------------------------------------------------
 Update on events (actual time=11.322..11.322 rows=0.00 loops=1)
   Buffers: shared hit=6726
   WAL: records=1000 bytes=77000
   ->  Seq Scan on events (actual time=0.006..9.881 rows=1000.00 loops=1)
         Filter: (status = 'new'::text)
         Rows Removed by Filter: 101000
         Buffers: shared hit=4726
 Planning:
   Buffers: shared hit=2
(9 rows)

The write amplification is solved for this insert-then-update-once pattern. However, choosing the right FILLFACTOR requires knowing how many times newly inserted rows will be updated during the visibility horizon, as well as how their size may change after updates.

FILLFACTOR 33% for two updates

If the lifecycle of the inserted rows includes two updates, the second update brings back the write amplification we observed earlier:

postgres=#  insert into events(payload)
           select repeat('x',100)
           from generate_series(1,1000)
\;
           explain (analyze, costs off, buffers, wal, summary off)
           update events
           set status='cur' , counter=counter+1
           where status='new'
\;
           explain (analyze, costs off, buffers, wal, summary off)
           update events
           set status='old' , counter=counter+1
           where status='cur'
;
INSERT 0 1000
                                 QUERY PLAN
----------------------------------------------------------------------------
 Update on events (actual time=28.247..28.248 rows=0.00 loops=1)
   Buffers: shared hit=14428
   WAL: records=1052 bytes=84612
   ->  Seq Scan on events (actual time=10.694..26.768 rows=1000.00 loops=1)
         Filter: (status = 'new'::text)
         Rows Removed by Filter: 249000
         Buffers: shared hit=12428
         WAL: records=52 bytes=7612
(8 rows)

                                 QUERY PLAN
----------------------------------------------------------------------------
 Update on events (actual time=32.143..32.144 rows=0.00 loops=1)
   Buffers: shared hit=22535 read=6 dirtied=41 written=4
   WAL: records=3953 bytes=340927
   ->  Seq Scan on events (actual time=10.227..26.269 rows=1000.00 loops=1)
         Filter: (status = 'cur'::text)
         Rows Removed by Filter: 249000
         Buffers: shared hit=12428
(7 rows)

When I know this update pattern, I can avoid the write amplification with a FILLFACTOR that allows three versions of each row to fit in the same block:

postgres=# alter table events set (fillfactor=33)
;

ALTER TABLE

postgres=#  insert into events(payload)
           select repeat('x',100)
           from generate_series(1,1000)
\;
           explain (analyze, costs off, buffers, wal, summary off)
           update events
           set status='cur' , counter=counter+1
           where status='new'
\;
           explain (analyze, costs off, buffers, wal, summary off)
           update events
           set status='old' , counter=counter+1
           where status='cur'
;
INSERT 0 1000
                                 QUERY PLAN
----------------------------------------------------------------------------
 Update on events (actual time=28.462..28.462 rows=0.00 loops=1)
   Buffers: shared hit=14428
   WAL: records=1050 bytes=84494
   ->  Seq Scan on events (actual time=15.491..26.965 rows=1000.00 loops=1)
         Filter: (status = 'new'::text)
         Rows Removed by Filter: 251000
         Buffers: shared hit=12428
         WAL: records=50 bytes=7494
 Planning:
   Buffers: shared hit=2
(10 rows)

                                 QUERY PLAN
----------------------------------------------------------------------------
 Update on events (actual time=27.682..27.682 rows=0.00 loops=1)
   Buffers: shared hit=14427
   WAL: records=1000 bytes=77000
   ->  Seq Scan on events (actual time=14.726..26.205 rows=1000.00 loops=1)
         Filter: (status = 'cur'::text)
         Rows Removed by Filter: 251000
         Buffers: shared hit=12427
(7 rows)

The math can become complicated because row sizes may change and the number of updates may vary. Ultimately, choosing the right FILLFACTOR is an empirical exercise, balancing write amplification against space amplification.

The impact also depends on the number of indexes that must be maintained when HOT updates are not possible, so indexing strategy matters as well. Partial indexes can be used to support queries on cold data, such as rows with status='old', without affecting rows in earlier lifecycle stages.

REPACK with the current FILLFACTOR

As the Seq Scan buffers show, my table now has 12427 pages, which is also visible from pg_class:

postgres=# vacuum analyze events;

ANALYZE

postgres=# select reltuples, relpages, relallvisible, reltuples/relpages "avg tuples per page", reloptions
           from pg_class where oid='events'::regclass
;

 reltuples | relpages | relallvisible | avg tuples per page |   reloptions
-----------+----------+---------------+---------------------+-----------------
    262000 |    12427 |         11277 |  21.083125452643436 | {fillfactor=33}
(1 row)

If I run REPACK, it uses the current FILLFACTOR, so it doesn't actually reduce bloat:

postgres=# repack (verbose, analyze) events
;

INFO:  repacking "public.events" in physical order
INFO:  "public.events": found 2018 removable, 262000 nonremovable row versions in 12427 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.07 s, system: 0.02 s, elapsed: 0.19 s.
INFO:  analyzing "public.events"
INFO:  "events": scanned 14556 of 14556 pages, containing 262000 live rows and 0 dead rows; 30000 rows in sample, 262000 estimated total rows
INFO:  finished analyzing table "postgres.public.events"
avg read rate: 1775.146 MB/s, avg write rate: 0.366 MB/s
buffer usage: 94 hits, 14542 reads, 3 dirtied
WAL usage: 12 records, 3 full page images, 19814 bytes, 18132 full page image bytes, 0 buffers full
system usage: CPU: user: 0.05 s, system: 0.00 s, elapsed: 0.06 

REPACK

postgres=# select reltuples, relpages, relallvisible, reltuples/relpages "avg tuples per page", reloptions
           from pg_class where oid='events'::regclass
;
 reltuples | relpages | relallvisible | avg tuples per page |   reloptions
-----------+----------+---------------+---------------------+-----------------
    262000 |    14556 |             0 |  17.999450398461114 | {fillfactor=33}
(1 row)

I now have even fewer rows per block. To reduce bloat, I must change FILLFACTOR before running REPACK. But which value should I choose?

This depends on the lifecycle of existing rows, not the lifecycle of newly inserted rows. We're repacking pre-existing rows that may have already reached a stage where they will never be updated again. In my example, all rows are now in the 'old' state and will not be updated again, so I can pack them as densely as possible:

postgres=# select status, count(*) from events group by all
;

 status | count
--------+--------
 old    | 262000 
(1 row)

postgres=# alter table events set (fillfactor=100)
;
ALTER TABLE

postgres=# repack (verbose, analyze) events
;

DEBUG:  building index "pg_toast_21602_index" on table "pg_toast_21602" serially
DEBUG:  index "pg_toast_21602_index" can safely use deduplication
INFO:  repacking "public.events" in physical order
INFO:  "public.events": found 0 removable, 262000 nonremovable row versions in 14556 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.06 s, system: 0.00 s, elapsed: 0.17 s.
INFO:  analyzing "public.events"
INFO:  "events": scanned 4764 of 4764 pages, containing 262000 live rows and 0 dead rows; 30000 rows in sample, 262000 estimated total rows
INFO:  finished analyzing table "postgres.public.events"
avg read rate: 696.528 MB/s, avg write rate: 0.521 MB/s
buffer usage: 831 hits, 4012 reads, 3 dirtied
WAL usage: 12 records, 3 full page images, 20372 bytes, 18660 full page image bytes, 0 buffers full
system usage: CPU: user: 0.04 s, system: 0.00 s, elapsed: 0.04 s

REPACK

postgres=# select reltuples, relpages, relallvisible, reltuples/relpages "avg tuples per page", reloptions
           from pg_class where oid='events'::regclass
;

 reltuples | relpages | relallvisible | avg tuples per page |   reloptions
-----------+----------+---------------+---------------------+-----------------
    262000 |     4764 |             0 |   54.99580184718724 | {fillfactor=33}
(1 row)


postgres
                                    
                                    
                                    

                                        by Franck Pachot