DEV Community: Franck Pachot

PostgreSQL Multi-Version: From Time Travel to Concurrency Control

Franck Pachot — Sun, 09 Aug 2026 21:04:53 +0000

A LinkedIn comment by Oleg Bartunov 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 proposals for LLL, part 1. 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 without LLM reviews. 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:

WAL versus a non-overwriting storage/transaction system.
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:

Date	Milestone	Result
1997	PostgreSQL 6.2 removes Time Travel	User-queryable tuple history was abandoned
July-August 1998	Low-Level Locking discussion and code	WAL/storage and concurrency control were separated, and lock modes were reorganized
October 1998	PostgreSQL 6.4	Lock-manager cleanup and new Low-Level Locking modes prepared the ground
November 27, 1998	New `HeapTuple` interface	The tuple API was reworked across heap, executor, index, vacuum, and catalog code
December 15, 1998	"Initial MVCC code"	Snapshot visibility entered the tree in a 65-file change by Vadim Mikheev
December 16-18, 1998	Serializable mode, isolation syntax, and lock modes	Transaction semantics became SQL-visible
January 29, 1999	Read Committed implemented as the default	Statement snapshots completed the principal 6.5 isolation behavior
March 28, 1999	Vacuum updated for MVCC	Cleanup learned the new visibility rules
May-June 1999	MVCC chapter and migration notes	The implementation acquired user-facing explanations before release
June 1999	PostgreSQL 6.5	MVCC, read committed, serializable mode, and transaction isolation shipped
April 2001	PostgreSQL 7.1	WAL supplied the modern crash-recovery foundation

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.

These costs have motivated proposals to reduce or replace PostgreSQL’s active-XID snapshot representation with a commit sequence number (CSN). In a 2013 pgsql-hackers proposal, Ants Aasma suggested assigning each committed transaction an ordered CSN so that a snapshot could be represented primarily by a cutoff value rather than by a list of concurrent XIDs. The proposal promised cheaper snapshot creation and potentially smaller snapshots, but required an XID-to-CSN mapping, retention rules for long-running snapshots, and new handling for subtransactions, crash recovery, hot standby, and asynchronous commits. Later discussion explored storing CSNs in a separate pg_csnlog, and prototype measurements showed substantial gains for some highly concurrent workloads but severe regressions when scans required many CSN lookups. As of 2026, this work remains experimental and has not been adopted by upstream PostgreSQL. It illustrates that XID-based visibility is not simply an obsolete design waiting to be replaced: alternatives exchange one set of costs for another, including commit-log lookups, cache pressure, storage overhead, and more complicated commit ordering.

This snapshot representation carries a real design trade-off:

Property	Benefit	Cost
`xmin`/`xmax` horizons plus an active-XID exception set	Visibility does not require assigning every commit a single global timestamp	Taking and retaining snapshots requires tracking concurrent transactions and copying or referencing their state
Tuple `xmin`/`xmax` plus transaction status	Commit and abort decisions stay compact and readers can evaluate versions independently	Visibility may require transaction-status lookups, hint bits, and careful wraparound rules
Statement or transaction snapshots	Readers do not block writers and repeated reads can receive a defined view	Old snapshots delay reclamation. Stronger isolation can require retries or SSI bookkeeping
No scalar commit sequence in an ordinary snapshot	Avoids making one scalar the complete visibility contract	"As of commit N" reasoning, global ordering, and distributed coordination need additional machinery
CSN-based snapshot proposal	Makes snapshots small and cheap to construct	Requires XID-to-CSN mapping, extra commit metadata, retention machinery, and careful handling of async commits and subtransactions

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:

the update does not change a column referenced by a non-summarizing index
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.

Interestingly, Oracle version 2 did not yet have before images, but it used after images for recovery, which were later replaced by redo logs. This reflects Oracle’s original block-oriented design: physical changes were protected using before and after images of database blocks rather than the more abstract logical redo and undo records.

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;

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:

   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

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 condition of correctness.

PostgreSQL's current behavior is easier to remember when read as this chain of decisions. It does not vacuum because its MVCC implementation is carelessly unfinished. It vacuums because versions are stored where ordinary table access can find them, and somebody must eventually prove that no legitimate observer can need them. It does not reserve page space merely to make inserts less dense. It reserves space so an updated version can remain on-page and avoid multiplying index work. It does not fear a forgotten transaction because locks are scarce. It fears the transaction's old claim on history.

The original Time Travel feature died. Its central physical possibility was repurposed from answering "what did this row look like then?" to answering "which version is true for me now?" That is the useful continuity between POSTGRES and PostgreSQL MVCC.

PostgreSQL 19 REPACK: Choosing the Right FILLFACTOR

Franck Pachot — Fri, 07 Aug 2026 14:01:32 +0000

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?

REPACK with the FILLFACTOR 100%

The ideal value 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=# alter table events set (fillfactor=33)
;

ALTER TABLE

The existing rows are now packed much more densely, using 4764 pages instead of 14556. After that, I've restored a low FILLFACTOR for future inserts so that there is room for two HOT updates:

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=25.079..25.080 rows=0.00 loops=1)
   Buffers: shared hit=6820
   WAL: records=1000 bytes=77000
   ->  Seq Scan on events (actual time=23.368..23.608 rows=1000.00 loops=1)
         Filter: (status = 'new'::text)
         Rows Removed by Filter: 262000
         Buffers: shared hit=4820
 Planning:
   Buffers: shared hit=9 dirtied=2
(9 rows)

                                 QUERY PLAN
----------------------------------------------------------------------------
 Update on events (actual time=24.752..24.753 rows=0.00 loops=1)
   Buffers: shared hit=6820
   WAL: records=1000 bytes=77000
   ->  Seq Scan on events (actual time=22.989..23.266 rows=1000.00 loops=1)
         Filter: (status = 'cur'::text)
         Rows Removed by Filter: 262000
         Buffers: shared hit=4820
(7 rows)

postgres=#

This is the best outcome: low space amplification for existing rows, allowing more data to fit in memory, and low write amplification for newly inserted rows.

I was able to calculate the right FILLFACTOR because my DML pattern is simple. In most applications, it isn't.

You probably don't want to REPACK existing rows using the same FILLFACTOR that was chosen for future inserts. Set it appropriately before REPACK, and restore it afterward. If it's too low, you may make bloat worse than it already is. If it's too high, you may generate more WAL in the hours or days following the operation.

As we saw in the first experiment, when rows are packed too densely to allow HOT updates, the first updates suffer from write amplification. However, those updates also create reusable space on the page, making future HOT updates possible.

My example with two updates was extreme because all new rows were updated two times in thr same call. If it happens over the next hours, the additional space used by the first updates' old versions is reusable by the next one, so you can have a larger fillfactor and 33% is a waste of space.

So, when in doubt, a FILLFACTOR close to 100%, is probably the safest choice when repacking a table that mostly contains cold rows. Leaving the size for one updated row allows the next update to be HOT, and when the MVCC horizon has passed, the size of the old version is released for new updates. Leave space to fit at least the final version of the rows plus one, or more if there are concurrent updates on the same page. The challenge is that choosing the "right" FILLFACTOR remains fundamentally a heuristic problem:

for INSERTs, you can estimate future growth based on the expected lifecycle of new rows
for REPACK, you're repacking existing rows whose future update likelihood is largely unknown. Analyzing the data first might be a good choice.

HorizonDB reduces WAL overhead with smarter FPI (full-page image) than traditional PostgreSQL

Franck Pachot — Sun, 02 Aug 2026 20:23:49 +0000

Azure HorizonDB exposes the familiar PostgreSQL statistics views because it is fully compatible with PostgreSQL. However, its compute and storage architecture differs from traditional PostgreSQL. I was curious whether these architectural differences appear in standard PostgreSQL statistics. I performed the same pgbench initialization steps and transactional workload on:

Azure Database for PostgreSQL Flexible Server, which runs the communtity PostgreSQL and extensions
Azure HorizonDB (Preview), which is PostgreSQL with disaggregated storage

This is not a performance or cost comparison. The instances are not equivalent in compute capacity. The objective is to compare what PostgreSQL itself reports through the cumulative statistics views:

pg_stat_io, which groups I/O operations by backend type, object, and context
pg_stat_checkpointer, which reports checkpoint requests, buffers written, and synchronization time
pg_stat_wal, which reports WAL records, full-page images, bytes, writes, and synchronizations

Each experiment below follows the same structure: the raw output, a table of the counters that matter, and the architectural signal that can reasonably be inferred from them.

The experiments follow the natural pgbench initialization order and are state-dependent: table generation, primary keys, foreign keys, and VACUUM each operate on the result of the preceding phase. The central question is why the same wal_fpi counter records full-page images for two reasons: checkpoint-based torn-page protection in conventional PostgreSQL and delivery of a base page image to HorizonDB storage.

Experimental method

I initialized the same pgbench scale factor on both systems: -s 800. I first recreated the empty pgbench tables with pgbench -iIdt -s 800, then ran the initialization phases separately. The scale factor creates 80 million rows in pgbench_accounts and approximately 10 GB of heap data.

For the initialization phases, I:

Issued CHECKPOINT and reset all shared statistics before table generation.
Ran one pgbench initialization step.
Issued CHECKPOINT, so that the statistics included the processing of dirty buffers created by that step.
Read pg_stat_wal, pg_stat_checkpointer, and pg_stat_io.
Reset the shared statistics before continuing to the next dependent step.

The final transactional workload differs slightly: the statistics had just been reset after the VACUUM phase. I then issued a checkpoint and ran pgbench without a final checkpoint.

I prepared the following query to read the IO statistics:

prepare delta_stat_io as
select
  pg_size_pretty(reads * op_bytes)   as read,
  pg_size_pretty(writes * op_bytes)  as write,
  pg_size_pretty(extends * op_bytes) as extend,
  pg_size_pretty(hits * op_bytes)    as hits,
  *
from pg_stat_io
where row(
  0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
) <> row( reads, read_time, writes, write_time, writebacks, writeback_time, extends, extend_time, hits, evictions, reuses, fsyncs, fsync_time )
order by coalesce(reads, 0) + coalesce(writes, 0) desc
;

I did not enable track_io_timing and track_wal_io_timing, so timing was not collected. I am interested in the number of calls, blocks, and bytes.

Configuration

I set up the two instances with shared buffer allocations that are intentionally close so that the I/O patterns can be compared: 12 GB for PostgreSQL and 11 GB for HorizonDB.

Conventional PostgreSQL buffers pages in two memory pools: userspace in PostgreSQL shared buffers and kernel space in the operating-system filesystem cache. HorizonDB avoids this double caching and provides compute replicas with a local NVMe page cache, allowing a larger share of RAM to be allocated to shared buffers. I provisioned Azure HorizonDB (Preview) with 2 vCores and 16 GiB RAM. Its 11241MB setting represents approximately 70% of that memory. Because PostgreSQL relies on the filesystem cache and allocates 25% of RAM to shared buffers, I provisioned Azure Database for PostgreSQL Flexible Server with 12 vCores and 48 GiB RAM.

Parameter	PostgreSQL	HorizonDB
`shared_buffers`	12GB	11241MB
`effective_cache_size`	36GB	11241MB

Note that effective_cache_size does not allocate memory. It is an estimate used by the planner. On conventional PostgreSQL, it typically includes the expected contribution of the operating-system filesystem cache, in addition to shared_buffers.

Among the other parameters, the most important difference is full_page_writes. In PostgreSQL, it is on, so the first modification of a page after a checkpoint can log a full-page image rather than just the change vector. This allows recovery to restore a page affected by a partial write. HorizonDB protects against torn pages in the distributed storage layer, so it is set to off to reduce the WAL generated.

The WAL-file-management parameters also differ:

Parameter	PostgreSQL	HorizonDB
`wal_init_zero`	on	off
`wal_recycle`	on	off
`max_wal_size`	2GB	12GB
`checkpoint_timeout`	10min	200s
`data_checksums`	on	off
`restart_after_crash`	on	off
`fsync`	on	on
`wal_sync_method`	fdatasync	fdatasync

These settings do not fully describe the storage implementation, but they show that HorizonDB does not manage WAL files and checkpoint scheduling exactly as conventional PostgreSQL does.

Experiment 1: Generate the table data

This first experiment establishes the baseline: the same logical work, the same buffer manager, and the first visible divergence in WAL composition.

I generated the heap data without indexes:

checkpoint;
select pg_stat_reset_shared();

\! pgbench -iIG -s 800

checkpoint;
select * from pg_stat_wal;
select * from pg_stat_checkpointer;
execute delta_stat_io;
select pg_stat_reset_shared();

PostgreSQL Flexible Server output:

generating data (server-side)...
done in 203.84 s (server-side generate 203.84 s).

CHECKPOINT

 wal_records | wal_fpi |  wal_bytes  | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time
-------------+---------+-------------+------------------+-----------+----------+----------------+---------------
    80009422 |     361 | 12160725691 |           560783 |    561412 |      778 |              0 |             0
(1 row)

 num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+----------------
         0 |            11 |                   0 |                 0 |                  0 |     121159 |     49123 |         1311879
(1 row)

  read   | write   | extend     | hits   | backend_type      | object   | context | reads | writes  | writebacks | extends | op_bytes | hits     | evictions | reuses | fsyncs
---------+---------+------------+--------+-------------------+----------+---------+-------+---------+------------+---------+----------+----------+-----------+--------+-------
         | 10 GB   |            |        | checkpointer      | relation | normal  |       | 1311879 |    1311879 |         |     8192 |          |           |        |     63
 296 kB  | 0 bytes | 10 GB      | 631 GB | client backend    | relation | normal  |    37 |       0 |          0 | 1311855 |     8192 | 82658063 |         0 |        |      0
 64 kB   | 0 bytes | 8192 bytes | 19 MB  | autovacuum worker | relation | normal  |     8 |       0 |          0 |       1 |     8192 |     2384 |         0 |        |      0
 0 bytes | 0 bytes | 0 bytes    | 136 kB | autovacuum worker | relation | vacuum  |     0 |       0 |          0 |       0 |     8192 |       17 |         0 |      0 |
(4 rows)

HorizonDB output:

generating data (server-side)...
done in 144.40 s (server-side generate 144.40 s).

CHECKPOINT

 wal_records | wal_fpi |  wal_bytes  | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time
-------------+---------+-------------+------------------+-----------+----------+----------------+---------------
    81321067 | 1312186 | 12544965939 |                0 |      2220 |     2125 |              0 |             0
(1 row)

 num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+----------------
         0 |             2 |                   0 |                 0 |                  0 |     109454 |         2 |         1311878
(1 row)

  read   | write   | extend   | hits   | backend_type      | object   | context | reads | writes  | writebacks | extends | op_bytes | hits     | evictions | reuses | fsyncs
---------+---------+----------+--------+-------------------+----------+---------+-------+---------+------------+---------+----------+----------+-----------+--------+-------
         | 10 GB   |          |        | checkpointer      | relation | normal  |       | 1311878 |    1311879 |         |     8192 |          |           |        |      0
 72 kB   | 0 bytes | 32 kB    | 17 MB  | autovacuum worker | relation | normal  |     9 |       0 |          0 |       4 |     8192 |     2212 |         0 |        |      0
 32 kB   | 0 bytes | 10 GB    | 631 GB | client backend    | relation | normal  |     4 |       0 |          0 | 1311855 |     8192 | 82656973 |         0 |        |      0
 0 bytes | 0 bytes | 0 bytes  | 40 kB  | background worker | relation | normal  |     0 |       0 |          0 |       0 |     8192 |        5 |         0 |        |      0
(4 rows)

At the PostgreSQL buffer-manager level, these executions are nearly identical:

Metric	PostgreSQL	HorizonDB
Relation blocks extended	1,311,855	1,311,855
Relation size extended	10 GB	10 GB
Shared-buffer hits	82,658,063	82,656,973
Hit volume	631 GB	631 GB
Checkpointer buffers written	1,311,879 (~10 GB)	1,311,878 (~10 GB)

The PostgreSQL query layer created the same number of relation pages, performed nearly the same number of shared-buffer accesses, and passed essentially the same number of dirty buffers to the checkpointer.

HorizonDB retains PostgreSQL's checkpointer process and buffer-management accounting. The statistics show an active checkpointer processing the same volume of dirty buffers. On HorizonDB, those writes maintain the compute replica's local SSD page cache but do not make relation pages durable. Durability and high availability are offloaded to the storage layer.

The volume processed by the checkpointer is the same, but the synchronization is not:

Metric	PostgreSQL	HorizonDB
Checkpointer `sync_time`	49,123 ms	2 ms
Relation `fsyncs`	63	0

Only PostgreSQL Flexible Server exposes conventional relation-file synchronization activity. Therefore, the writes counter cannot be interpreted the same way on both systems. It records a buffer write operation visible to PostgreSQL. On HorizonDB, that operation can populate or update the local cache without participating in durability.

The WAL remains at the core of durability. Both systems generated approximately 12 GB of WAL:

`pg_stat_wal` column	PostgreSQL Flexible Server	HorizonDB
`wal_bytes`	12,160,725,691	12,544,965,939
`wal_records`	80,009,422	81,321,067
`wal_fpi`	361	1,312,186
`wal_buffers_full`	560,783	0
`wal_write`	561,412	2,220
`wal_sync`	778	2,125

WAL plays a broader role in HorizonDB than in conventional PostgreSQL, and its generation is adapted to that role:

PostgreSQL writes permanent relation pages at checkpoint or eviction. WAL protects changes until those page writes become durable and provides the change stream for crash recovery and replication.
In HorizonDB's database-as-a-log architecture, compute sends WAL to durable storage instead of sending data pages. Storage can apply records asynchronously, or apply them to an earlier page version when that page is read. WAL is therefore part of both the write path and the page-read path.

Why wal_fpi is low on PostgreSQL and high on HorizonDB?

In PostgreSQL, a large heap load creates many new pages but logs very few full-page images in the WAL. The pages are created directly in shared buffers, and the WAL records describing their creation are sufficient to reconstruct them during recovery. Full-page images are generated when an existing page is read into shared buffers and modified after a checkpoint, because recovery then needs a reliable base image to apply incremental changes. In this case, the base is an empty page.

The high wal_fpi on HorizonDB may be surprising, especially since full_page_writes = off. These images were not produced by the "first modification after checkpoint" rule. Because pages from shared buffers are not written to storage as relation files, brand-new pages never reach the storage layer. HorizonDB therefore sends the full image rather than an incremental change vector. The 1,312,186 FPIs are close to, but not exactly equal to, the 1,311,855 blocks extended. Other activity in the interval accounts for the aggregate counters not being one-to-one. The resulting WAL volume is essentially the same on both systems.

Experiment 2: Create primary keys

This phase shows the first clear divergence in WAL generation. Building the B-tree indexes creates new index pages and can modify those pages again as the build proceeds.

\! pgbench -iIp -s 800

checkpoint;
select * from pg_stat_wal;
select * from pg_stat_checkpointer;
execute delta_stat_io;
select pg_stat_reset_shared();

Because the preceding statistics reset occurred after the data-generation checkpoint, this interval excludes the heap-loading phase.

PostgreSQL Flexible Server output

creating primary keys...
done in 123.93 s (primary keys 123.93 s).

CHECKPOINT

 wal_records | wal_fpi | wal_bytes  | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time
-------------+---------+------------+------------------+-----------+----------+----------------+---------------
     1975171 | 2187495 | 2506687103 |                0 |       459 |      459 |              0 |             0
(1 row)

 num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+----------------
         0 |             2 |                   0 |                 0 |                  0 |      84374 |       588 |         1311559
(1 row)

    read    | write   | extend     | hits    | backend_type      | object   | context  | reads | writes  | writebacks | extends | op_bytes | hits   | evictions | reuses | fsyncs
------------+---------+------------+---------+-------------------+----------+----------+-------+---------+------------+---------+----------+--------+-----------+--------+-------
            | 10 GB   |            |         | checkpointer      | relation | normal   |       | 1311559 |    1311559 |         |     8192 |        |           |        |     46
 136 kB     | 0 bytes | 0 bytes    | 124 MB  | client backend    | relation | normal   |    17 |       0 |          0 |       0 |     8192 |  15897 |         0 |        |      0
 40 kB      | 0 bytes | 0 bytes    | 1928 kB | background worker | relation | normal   |     5 |       0 |          0 |       0 |     8192 |    241 |         0 |        |      0
 8192 bytes | 0 bytes | 8192 bytes | 7720 kB | autovacuum worker | relation | normal   |     1 |       0 |          0 |       1 |     8192 |    965 |         0 |        |      0
 0 bytes    | 0 bytes | 0 bytes    | 736 kB  | autovacuum worker | relation | vacuum   |     0 |       0 |          0 |       0 |     8192 |     92 |         0 |      0 |
 0 bytes    | 0 bytes |            | 5117 MB | client backend    | relation | bulkread |     0 |       0 |          0 |         |     8192 | 654924 |         0 |      0 |
 0 bytes    | 0 bytes |            | 5129 MB | background worker | relation | bulkread |     0 |       0 |          0 |         |     8192 | 656552 |         0 |      0 |
(7 rows)

HorizonDB output

creating primary keys...
done in 76.90 s (primary keys 76.90 s).

CHECKPOINT

 wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time
-------------+---------+-----------+------------------+-----------+----------+----------------+---------------
       78812 |  219396 | 844376348 |                0 |       594 |      527 |              0 |             0
(1 row)

 num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+----------------
         0 |             1 |                   0 |                 0 |                  0 |      61364 |         1 |         1091695
(1 row)

    read    | write   | extend  | hits    | backend_type      | object   | context | reads | writes  | writebacks | extends | op_bytes | hits   | evictions | reuses | fsyncs
------------+---------+---------+---------+-------------------+----------+---------+-------+---------+------------+---------+----------+--------+-----------+--------+-------
            | 8529 MB |         |         | checkpointer      | relation | normal  |       | 1091695 |    1311555 |         |     8192 |        |           |        |      0
 96 kB      | 0 bytes | 0 bytes | 3762 MB | client backend    | relation | normal  |    12 |       0 |          0 |       0 |     8192 | 481524 |         0 |        |      0
 8192 bytes | 0 bytes | 32 kB   | 1132 MB | autovacuum worker | relation | normal  |     1 |       0 |          0 |       4 |     8192 | 144890 |         0 |        |      0
 0 bytes    | 0 bytes | 0 bytes | 6628 MB | background worker | relation | normal  |     0 |       0 |          0 |       0 |     8192 | 848358 |         0 |        |      0
(4 rows)

The logical work here is different from the heap load. Building a B-tree requires scanning the existing table, sorting the keys, and writing index pages. The statistics reflect another cache difference. On PostgreSQL Flexible Server, the table scan appears in the bulkread context. PostgreSQL uses a small ring of shared buffers for large scans so it does not displace useful pages from both shared buffers and the filesystem cache. HorizonDB reports the scan in the normal context. With one compute page-cache hierarchy rather than PostgreSQL plus the filesystem cache, it does not need the same protection against polluting two caches.

Metric	PostgreSQL	HorizonDB
shared-buffer hits	10 GB `bulkread`	11 GB `normal`
WAL records	1,975,171	78,812
Full-page images	2,187,495	219,396
WAL volume	2.51 GB	844 MB
Checkpointer writes	1,311,559	1,091,695
Relation `fsyncs`	46	0

The whole table was cached on both systems, so the indexes were built essentially from memory.

Both systems created the same indexes, yet HorizonDB generated about one third of the WAL volume and one tenth of the full-page images. The gap is wider than during heap generation. B-tree construction creates new index pages and subsequently modifies pages created during the build. On conventional PostgreSQL, modifications after the checkpoint can trigger full-page images because full_page_writes = on. On HorizonDB, new pages require base images, while later modifications can be represented by incremental WAL once storage has a valid base. HorizonDB still shows substantial checkpointer activity — more than one million dirty buffers processed — but avoids most checkpoint-driven FPI overhead.

One accounting detail is worth noting before it is misread. In the HorizonDB output, writes = 1,091,695 and writebacks = 1,311,555. These are different PostgreSQL accounting events and must not be added together to estimate physical storage traffic. Neither is necessarily a unique durable page write, particularly with a distributed storage layer underneath.

In HorizonDB, the PostgreSQL instance on compute still manages buffers, WAL, and checkpoints. Durability and recovery protection are offloaded from the traditional compute-side combination of relation-file writes, fsync operations, and checkpoint-driven full-page images to the storage layer.

Experiment 3: Create foreign keys

I created the foreign keys as the next natural pgbench initialization step:

\! pgbench -iIf -s 800

checkpoint;
select * from pg_stat_wal;
select * from pg_stat_checkpointer;
execute delta_stat_io;
select pg_stat_reset_shared();

Foreign-key creation primarily validates existing data. Because this article focuses on writes and full-page images, this read-oriented phase adds no useful architectural signal. I keep it in the sequence because the following VACUUM operates on the database state it produced, but omit its statistics.

Experiment 4: VACUUM — the key experiment

This is the most revealing experiment of the article.

The preceding checkpoint establishes a clean recovery boundary, and VACUUM then revisits nearly every page in the database. If a checkpoint-related page-protection mechanism exists, it must appear in the WAL statistics here.

VACUUM is also one of PostgreSQL's most disliked operational costs because its work can generate substantial I/O and WAL activity and is difficult to predict. Offloading durability work and avoiding checkpoint-driven FPIs make that maintenance path lighter and more predictable, even though VACUUM remains part of PostgreSQL itself.

\! pgbench -iIv -s 800

checkpoint;
select * from pg_stat_wal;
select * from pg_stat_checkpointer;
execute delta_stat_io;
select pg_stat_reset_shared();

PostgreSQL Flexible Server output

vacuuming...
done in 91.63 s (vacuum 91.63 s).

CHECKPOINT

 wal_records | wal_fpi |  wal_bytes  | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time
-------------+---------+-------------+------------------+-----------+----------+----------------+---------------
     1311609 | 1311543 | 1128836191  |                0 |       466 |      466 |              0 |             0
(1 row)

 num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+----------------
         0 |             2 |                   0 |                 0 |                  0 |      61867 |       707 |         1311543
(1 row)

  read   | write   | extend  | hits       | backend_type      | object   | context | reads | writes  | writebacks | extends | op_bytes | hits    | evictions | reuses | fsyncs
---------+---------+---------+------------+-------------------+----------+---------+-------+---------+------------+---------+----------+---------+-----------+--------+-------
         | 10 GB   |         |            | checkpointer      | relation | normal  |       | 1311543 |    1311543 |         |     8192 |         |           |        |     31
 0 bytes | 0 bytes | 344 kB  | 10 GB      | client backend    | relation | normal  |     0 |       0 |          0 |      43 |     8192 | 1330219 |         0 |        |      0
 0 bytes | 0 bytes | 0 bytes | 10 GB      | client backend    | relation | vacuum  |     0 |       0 |          0 |       0 |     8192 | 1341529 |         0 |      0 |
 0 bytes | 0 bytes | 0 bytes | 8376 kB    | autovacuum worker | relation | normal  |     0 |       0 |          0 |       0 |     8192 |    1047 |         0 |        |      0
 0 bytes | 0 bytes | 0 bytes | 8192 bytes | autovacuum worker | relation | vacuum  |     0 |       0 |          0 |       0 |     8192 |       1 |         0 |      0 |
(5 rows)

HorizonDB output

vacuuming...
done in 4.97 s (vacuum 4.97 s).

CHECKPOINT

 wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time
-------------+---------+-----------+------------------+-----------+----------+----------------+---------------
      994350 |      74 |  58687203 |                0 |        65 |       60 |              0 |             0
(1 row)

 num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+----------------
         0 |             1 |                   0 |                 0 |                  0 |      56522 |         1 |          994230
(1 row)

  read   | write   | extend     | hits   | backend_type      | object   | context | reads | writes | writebacks | extends | op_bytes | hits    | evictions | reuses | fsyncs
---------+---------+------------+--------+-------------------+----------+---------+-------+--------+------------+---------+----------+---------+-----------+--------+-------
         | 7767 MB |            |        | checkpointer      | relation | normal  |       | 994230 |     994230 |         |     8192 |         |           |        |      0
 88 kB   | 0 bytes | 8192 bytes | 667 MB | autovacuum worker | relation | normal  |    11 |      0 |          0 |       1 |     8192 |   85341 |         0 |        |      0
 0 bytes | 0 bytes | 248 kB     | 15 GB  | client backend    | relation | normal  |     0 |      0 |          0 |      31 |     8192 | 1947191 |         0 |        |      0
(3 rows)

Here are the interesting statistics:

Metric	PostgreSQL	HorizonDB
WAL records	1,311,609	994,350
Full-page images (FPI)	1,311,543	74
WAL bytes	1.13 GB	58.7 MB
Checkpointer buffers written	1,311,543	994,230

In PostgreSQL, WAL FPI corresponds to the number of buffers written. While this strong correlation suggests that checkpoint-triggered full-page images are in use, these aggregate counters do not confirm page-by-page accuracy: wal_fpi tracks images in WAL, whereas buffers_written reflects checkpointer write operations. This pattern aligns exactly with what full_page_writes = on aims to achieve after a checkpoint: the initial modification of a page logs a complete image, ensuring recovery does not depend on potentially partial relation-page writes.

HorizonDB shows the opposite pattern, with nearly one million dirty buffers handled by the PostgreSQL checkpointer, yet only 74 full-page images were produced. The checkpointer remains operational, and its writes primarily update the cache state instead of following PostgreSQL's conventional durable relation-file recovery method.

The WAL volume makes the effect concrete: the same maintenance operation produced roughly twenty times less WAL on HorizonDB (1,128,836,191 bytes versus 58,687,203 bytes).

This single experiment explains most of the WAL differences observed in the other phases. It isolates the recovery semantics from the workload itself: both systems modified a large number of existing pages after a checkpoint, but only conventional PostgreSQL had to protect them with full-page images.

The checkpointer counters confirm that the buffer processing is real on both sides:

Metric	PostgreSQL	HorizonDB
Buffers written	1,311,543	994,230
Checkpointer `sync_time`	707 ms	1 ms
Relation `fsyncs`	31	0

HorizonDB offloads the filesystem-oriented durability work traditionally associated with checkpoints: relation-file synchronization and checkpoint-driven full-page-image logging. Compute-side writes can still be useful for the local SSD cache, while page durability and crash recovery are handled in the storage layer.

Experiment 5: Transactional workload

Initialization exercises involve bulk operations for a specific purpose. This phase verifies if the VACUUM observation applies also to regular OLTP activity. I executed the built-in pgbench transaction for 15 minutes:

checkpoint;

\! pgbench -n -c 10 -T 900

select * from pg_stat_wal;
select * from pg_stat_checkpointer;
execute delta_stat_io;
select pg_stat_reset_shared();

The statistics had already been reset by the final statement of Experiment 4. The explicit checkpoint shown here is included in this interval, which matches num_requested = 1 in both outputs.

Unlike the initialization phases, I did not issue a final checkpoint before reading the statistics. The counters therefore show work performed during the 900-second interval, but not necessarily the eventual processing of every page dirtied by it.

PostgreSQL Flexible Server output

pgbench (16.2, server 17.10)

transaction type: <builtin: TPC-B (sort of)>
scaling factor: 800
query mode: simple
number of clients: 10
number of threads: 1
maximum number of tries: 1
duration: 900 s
number of transactions actually processed: 44053
number of failed transactions: 0 (0.000%)
latency average = 203.822 ms
initial connection time = 2294.257 ms
tps = 49.062339 (without initial connection time)

 wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time
-------------+---------+-----------+------------------+-----------+----------+----------------+---------------
      472835 |   85311 | 228228681 |                0 |     46112 |    46112 |              0 |             0
(1 row)

 num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+----------------
         1 |             1 |                   0 |                 0 |                  0 |         25 |         1 |           31920
(1 row)

    read    | write   | extend  | hits  | backend_type      | object   | context | reads | writes | writebacks | extends | op_bytes | hits    | evictions | reuses | fsyncs
------------+---------+---------+-------+-------------------+----------+---------+-------+--------+------------+---------+----------+---------+-----------+--------+-------
 318 MB     | 0 bytes | 8312 kB | 18 GB | client backend    | relation | normal  | 40714 |      0 |          0 |    1039 |     8192 | 2386621 |         0 |        |      0
            | 249 MB  |         |       | checkpointer      | relation | normal  |       |  31920 |      31904 |         |     8192 |         |           |        |      0
 8192 bytes | 0 bytes | 72 kB   | 99 MB | autovacuum worker | relation | normal  |     1 |      0 |          0 |       9 |     8192 |   12702 |         0 |        |      0
 0 bytes    | 0 bytes | 0 bytes | 31 MB | autovacuum worker | relation | vacuum  |     0 |      0 |          0 |       0 |     8192 |    3904 |         0 |      0 |
(4 rows)

HorizonDB output

pgbench (16.2, server 17.9 (Azure HorizonDB (1b3bcd789c4)(release)))

transaction type: <builtin: TPC-B (sort of)>
scaling factor: 800
query mode: simple
number of clients: 10
number of threads: 1
maximum number of tries: 1
duration: 900 s
number of transactions actually processed: 44530
number of failed transactions: 0 (0.000%)
latency average = 201.639 ms
initial connection time = 2302.203 ms
tps = 49.593642 (without initial connection time)

 wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time
-------------+---------+-----------+------------------+-----------+----------+----------------+---------------
      466156 |    1273 |  35362656 |                0 |     44789 |    44789 |              0 |             0
(1 row)

 num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+----------------
         4 |             1 |                   0 |                 0 |                  0 |        890 |        12 |           79389
(1 row)

  read   | write   | extend     | hits   | backend_type      | object   | context | reads | writes | writebacks | extends | op_bytes | hits    | evictions | reuses | fsyncs
---------+---------+------------+--------+-------------------+----------+---------+-------+--------+------------+---------+----------+---------+-----------+--------+-------
         | 620 MB  |            |        | checkpointer      | relation | normal  |       |  79389 |      79448 |         |     8192 |         |           |        |      0
 321 MB  | 0 bytes | 8448 kB    | 18 GB  | client backend    | relation | normal  | 41101 |      0 |          0 |    1056 |     8192 | 2421167 |         0 |        |      0
 0 bytes | 0 bytes | 8192 bytes | 141 MB | autovacuum worker | relation | normal  |     0 |      0 |          0 |       1 |     8192 |   18044 |         0 |        |      0
(3 rows)

The logical workload executed by both systems was almost identical:

Metric	PostgreSQL	HorizonDB
Transactions	44,053	44,530
Average latency	203.8 ms	201.6 ms
TPS	49.1	49.6
Client reads	318 MB	321 MB
Shared-buffer hits	18 GB	18 GB
WAL records	472,835	466,156

I am not using this as a performance result. It matters because it establishes that the two systems executed comparable transactional work and produced comparable buffer activity.

The difference is entirely in WAL composition:

Metric	PostgreSQL	HorizonDB
Full-page images	85,311	1,273
WAL volume	228 MB	35 MB

The WAL-record counts are similar, while PostgreSQL generated about 67 times more full-page images and 6.45 times the WAL volume. The similar transaction, buffer-access, and WAL-record counts indicate comparable logical work. They do not make the environments identical. The server versions, compute capacity, and checkpoint schedules differ, with one timed checkpoint on PostgreSQL Flexible Server and four on HorizonDB. Within those limits, the result is consistent with the different page-durability mechanisms observed in the preceding phases.

The PostgreSQL execution path stays recognizable on both systems:

Observation	PostgreSQL	HorizonDB
Relation reads visible	Yes	Yes
Shared-buffer hits visible	Yes	Yes
WAL writes visible	Yes	Yes
Checkpointer writes visible	Yes	Yes
Relation `fsyncs`	None in interval	None

This confirms that the behavior observed during VACUUM is not limited to bulk or maintenance operations. During normal transactional processing, HorizonDB still relies on PostgreSQL WAL generation and checkpoints but avoids full-page-image logging after checkpoints.

What the counters ultimately mean

The names and views of PostgreSQL processes remain familiar, but their actual meanings have shifted. A write showing up in the HorizonDB checkpointer indicates that PostgreSQL processed a dirty buffer and might have updated the local SSD cache on the compute replica. However, this does not mean a typical local relation-file write made the page durable.

This distinction aligns with the ARIES recovery algorithm, which allows the database to use a no-force policy—meaning commit doesn't require all pages to be written to their durable locations as long as the WAL is durable. HorizonDB extends this separation: compute relation writes serve as cache updates, while the durable WAL and page states are stored in the storage layer.

The role of WAL also evolves. In traditional PostgreSQL, WAL protects changes until relation pages are durable, supporting crash recovery and replication. In HorizonDB's database-as-a-log architecture, WAL becomes the primary write stream and can directly participate in reading a page by applying changes to an earlier stored version.

Conclusion

The experiments reveal two storage architectures beneath the same PostgreSQL database engine, with the same SQL processing and transaction semantics.

In PostgreSQL Flexible Server, dirty relation pages flow from shared buffers to durable relation files. Checkpoints coordinate those writes and full-page images protect recovery from partial page writes. WAL accompanies the data path mainly for crash recovery and replication.

In HorizonDB's compute instance, relation writes maintain a local SSD cache and are not the durability path. WAL is made durable in the storage layer, where it is also used to materialize pages read by the compute layer. A read can start from an earlier page image and apply WAL up to the requested LSN. Full-page images establish base versions for pages that storage has not seen before, rather than protecting local relation-file writes at every checkpoint boundary.

The familiar checkpointer and WAL counters therefore remain useful, but they describe logical PostgreSQL work at an architectural boundary. In HorizonDB, durability is offloaded, compute writes serve the cache, and WAL is part of both writing and reading the database.

WHERE $1::timestamptz IS NULL OR "timestamp" > $1

Franck Pachot — Mon, 27 Jul 2026 15:51:55 +0000

SQL is quite flexible, making it easy to write a single query that works in two situations: one without a filtering parameter and one with a parameterized filter. For example, I came across a benchmark comparing MongoDB and PostgreSQL that handles pagination effectively—by avoiding OFFSET and instead using the last value to fetch the next set of results. The first page uses only ORDER BY and LIMIT, while the following pages add an extra WHERE condition.

In the MongoDB version of this benchmark, the filter is handled within the application, which leads to two separate queries for these scenarios.

export async function getOrders(cursor) {
  const match = cursor ? { timestamp: { $gt: new Date(cursor) } } : {};
  const rows = await orders
    .aggregate([
      { $match: match },
      { $sort: { timestamp: 1 } },
      { $limit: PAGE_SIZE },
    ])

We can do the same in PostgreSQL using a single prepared statement. SQL is such a powerful language that it often feels tempting to write it this way:

SELECT *
     FROM orders
     WHERE $1::timestamptz IS NULL OR "timestamp" > $1
     ORDER BY "timestamp" ASC
     LIMIT ${PAGE_SIZE}

If $1 is NULL, the predicate evaluates to true for every row, effectively disabling the filter and returning the first rows in timestamp order without applying any filter. When $1 has a value, it filters the results using that specific value, enabling a more targeted search.

However, expressing both use cases in a single generic predicate may result in an execution plan that is suboptimal for some parameter values.

I gave it a try:

drop table if exists orders;

create table orders (
  order_id text primary key,
  "timestamp" timestamptz not null
);

create index idx_orders_timestamp
  on orders("timestamp");

insert into orders
select
    'ORD-'||g,
    '2025-01-01'::timestamptz + g * interval '1 minute'
from generate_series(1, 5000000) as g;

analyze orders;

prepare getorders(timestamptz, int) as
select * from orders
     where $1::timestamptz is null or "timestamp" > $1
     order by "timestamp" asc limit $2
;

explain (analyze, buffers, settings, costs off)
 execute getorders(date'2026-01-01'::timestamptz, 10);

Here is the execution plan:

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit (actual time=0.028..0.031 rows=10.00 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using idx_orders_timestamp on orders (actual time=0.027..0.029 rows=10.00 loops=1)
         Index Cond: ("timestamp" > '2026-01-01 00:00:00+00'::timestamp with time zone)
         Index Searches: 1
         Buffers: shared hit=4
 Planning Time: 0.117 ms
 Execution Time: 0.042 ms

This looks great because the planner was able to evaluate date'2026-01-01'::timestamptz is null during planning and simplify the predicate. The index scan is efficient, reading just about rows=10.00 entries.

However, let's see what happens when PostgreSQL switches to a generic plan instead of a custom one:


set plan_cache_mode to force_generic_plan;

explain (analyze, buffers, settings, costs off)
 execute getorders(date'2026-01-01'::timestamptz, 10);

It's still an Index Scan, but without an Index Cond to help narrow down the scan range:

                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Limit (actual time=71.945..71.948 rows=10.00 loops=1)
   Buffers: shared hit=4786
   ->  Index Scan using idx_orders_timestamp on orders (actual time=71.943..71.945 rows=10.00 loops=1)
         Filter: (($1 IS NULL) OR ("timestamp" > $1))
         Rows Removed by Filter: 525600
         Index Searches: 1
         Buffers: shared hit=4786
 Settings: plan_cache_mode = 'force_generic_plan'
 Planning Time: 0.030 ms
 Execution Time: 71.971 ms

Since the generic plan must work for all possible values of $1, PostgreSQL cannot transform the predicate into an Index Cond on "timestamp". As a result, it reads 525600 unnecessary index entries, which are then removed by the filter.

While the default setting plan_cache_mode = 'auto' often works well, it relies on a heuristic: PostgreSQL executes the statement a few times with custom plans, then compares the estimated cost of a generic plan with the average estimated cost of the custom plans. The decision is based on estimates rather than actual execution times, which means it may occasionally select a generic plan even when a custom plan would perform better for a particular workload.

One alternative is to rewrite the predicate so that PostgreSQL can always generate an index condition.


reset plan_cache_mode;

prepare getorders_better(timestamptz, int) as
select * from orders
     where timestamp > coalesce($1, '-infinity'::timestamptz)
     order by "timestamp" asc limit $2
;

With the new query, there is always an Index Cond. The main difference is whether the scan range starts at -infinity or at the supplied parameter value:

postgres=# explain execute getorders_better(null::timestamptz, 10)
;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.78 rows=10 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..174194.43 rows=5000000 width=19)
         Index Cond: ("timestamp" > '-infinity'::timestamp with time zone)
(3 rows)

postgres=# explain execute getorders_better(date'2026-01-01'::timestamptz, 10);
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.78 rows=10 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..155802.94 rows=4471972 width=19)
         Index Cond: ("timestamp" > '2026-01-01 00:00:00+00'::timestamp with time zone)
(3 rows)

postgres=# set plan_cache_mode to force_generic_plan;
SET

postgres=# explain execute getorders_better(null::timestamptz, 10)
;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..5807.41 rows=166667 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..58070.11 rows=1666667 width=19)
         Index Cond: ("timestamp" > COALESCE($1, '-infinity'::timestamp with time zone))
(3 rows)

postgres=# explain execute getorders_better(date'2026-01-01'::timestamptz, 10);
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..5807.41 rows=166667 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..58070.11 rows=1666667 width=19)
         Index Cond: ("timestamp" > COALESCE($1, '-infinity'::timestamp with time zone))
(3 rows)

This revised query is effective because the Index Scan is optimal for pagination with order by "timestamp" asc limit $2, even when a null parameter is used. However, this isn't always the case, as a generic plan must estimate selectivity without knowing the actual parameter values. In some situations, this can lead to poor cardinality estimates and suboptimal plan choices.

I used prepared statements because they are the standard way to execute parameterized queries, even with custom plans, as they prevent SQL injection. However, you might face the same problem if you construct a custom query using a hardcoded value while keeping the generic predicate. For instance, you could include your parameters in a WITH clause's common table expression.

postgres=# explain (analyze, buffers, costs off)
with param as ( select
 date'2026-01-01'::timestamptz as p1
)
select * from orders, param
     where p1::timestamptz is null or "timestamp" > p1
     order by "timestamp" asc limit 10
;
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=108.970..108.974 rows=10 loops=1)
   Buffers: shared hit=4786
   ->  Index Scan using idx_orders_timestamp on orders (actual time=108.969..108.972 rows=10 loops=1)
         Filter: ((('2026-01-01'::date)::timestamp with time zone IS NULL) OR ("timestamp" > ('2026-01-01'::date)::timestamp with time zone))
         Rows Removed by Filter: 525600
         Buffers: shared hit=4786
 Planning Time: 0.090 ms
 Execution Time: 108.996 ms
(8 rows)

Since ((('2026-01-01'::date)::timestamp with time zone IS NULL) is provably false, the OR expression could theoretically be simplified to a single predicate. However, PostgreSQL currently does not perform this transformation. The optimizer source code explicitly notes that OR branches proven to be always false could theoretically be removed, but this simplification is not currently implemented:

Currently, when processing OR expressions, we only return true when all of the OR branches are always false. This could perhaps be expanded to remove OR branches that are provably false. This may be a useful thing to do as it could result in the OR being left with a single arg. That's useful as it would allow the OR condition to be replaced with its single argument which may allow use of an index for faster filtering on the remaining condition.

Obviously, it's even worse if the WITH clause is materialized instead of being inlined, as the predicate is not even a Filter of the Index Scan with hardcoded values, but a parameterized Join Filter:

postgres=# explain (analyze, buffers, costs off)
with param as materialized ( select
 date'2026-01-01'::timestamptz as p1
)
select * from orders, param
     where p1::timestamptz is null or "timestamp" > p1
     order by "timestamp" asc limit 10
;

                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Limit (actual time=256.556..256.563 rows=10 loops=1)
   Buffers: shared hit=4786
   CTE param
     ->  Result (actual time=0.001..0.001 rows=1 loops=1)
   ->  Nested Loop (actual time=256.555..256.561 rows=10 loops=1)
         Join Filter: ((param.p1 IS NULL) OR (orders."timestamp" > param.p1))
         Rows Removed by Join Filter: 525600
         Buffers: shared hit=4786
         ->  Index Scan using idx_orders_timestamp on orders (actual time=0.028..75.350 rows=525610 loops=1)
               Buffers: shared hit=4786
         ->  CTE Scan on param (actual time=0.000..0.000 rows=1 loops=525610)
 Planning Time: 0.141 ms
 Execution Time: 256.590 ms
(13 rows)

Tese situations mirrors what happens when a prepared statement is executed with a generic plan, but because it uses a custom plan, changing the plan cache mode doesn't help. When using generic predicates, such as p1 is null or "column" > p1, that contain branches that may be false depending on parameter values, the planner cannot safely eliminate those branches to optimize to a sargable condition. The same principle applies: when possible, rewrite generic predicates into forms that allow PostgreSQL to generate index conditions.

For workloads where parameter values can lead to radically different optimal plans, relying on plan_cache_mode = auto may not always produce the desired result. In such cases, forcing custom plans with plan_cache_mode = force_custom_plan can be a simple way to guarantee that the optimizer considers the actual parameter values on every execution.

The final example demonstrates that the issue is not limited to generic plans. Even when the value is known at planning time through a single-row CTE, PostgreSQL does not simplify the OR expression into an indexable predicate.

Following ROWIDs Through an Oracle Unique Index Update

Franck Pachot — Sun, 26 Jul 2026 21:21:12 +0000

I've always been amazed by how Oracle Database handles updates to a unique column—performing set-based operations that don't violate the unique constraint, yet when executed row by row, it temporarily allows duplicates. I'm also surprised that it doesn't always report which value violates the constraint.

SQL> create table franck ( val int unique );

Table created.

SQL> insert into franck values (-1) , (1) ;

2 rows created.

SQL> select val from franck;

       VAL
----------
        -1
         1

SQL> update franck set val = -val ;

2 rows updated.

SQL> select val from franck;

       VAL
----------
         1
        -1

From a SQL perspective, this is expected behavior: the state before and after the update is valid with no duplicates. However, not all databases can run this without raising an error:

Db2, SQL Server, and Oracle handle it without error.
PostgreSQL raises ERROR: duplicate key value violates unique constraint "franck_val_key", DETAIL: Key (val)=(1) already exists. unless it runs with a deferred constraint.
MySQL or MariaDB raise Duplicate entry '1' for key 'franck.val'
SQLite raises { "code": "SQLITE_CONSTRAINT_UNIQUE" }
MongoDB raises E11000 duplicate key error collection: test.franck index: val_1 dup key: { val: 1 }

The first time I've seen Oracle Database execute this update without error was surprising for me because Oracle unique indexes store the indexed columns as the B-tree key and the ROWID as the associated data. Non-unique indexes add the ROWID to the physical key and are required for a deferrable unique constraint to allow temporary duplication before the end of the transaction. So how do non-deferrable unique indexes allow duplication during a single update statement? In this simple example, I would expect:

The initial index entries are: (-1): row #1 and (1): row #2
Updating the first row deletes the first entry (-1): row #1 and adds one with (1): row #1
Updating the second row deletes the corresponding entry (1): row #2 and adds one with (-1): row #2
The final non-deleted index entries are: (-1): row #2 and (1): row #1

Even if the final state is valid, this execution would raise a duplicate key error at step 2 because two entries have the same value: (1): row #1 and (1): row #2. So Oracle does something smarter. Why do other databases not do the same? What's the trade-off?

Let's look at the internals with another example: five rows with values from 1 to 5, and an update that increments them by one should create a temporary violation until the last row is updated.

Investigation with block dumps

My goal is to observe how a unique B-tree index evolves as Oracle executes a multi-row update that temporarily creates duplicate values.

The table contains five rows with values from 1 to 5, and a row-level trigger pauses for five seconds before each row update. This gives me enough time to dump the index leaf block and capture intermediate states in a second session.

I create the table:

drop table FRANCK purge;
create table FRANCK as select rownum as VAL from xmltable('1 to 5');
create unique index FRANCK on FRANCK ( VAL );
create or replace trigger FRANCK_SLEEP 
 before update on FRANCK 
 for each row begin dbms_session.sleep(5); end;
/
show errors

Here are my table's rows in the order they are scanned:

SQL> column "DUMP(VAL,16)" format a18
SQL> column "DUMP(ROWID,16)" format a39
SQL> select val, dump(val,16), dump(rowid,16)
     from FRANCK 
;

       VAL DUMP(VAL,16)       DUMP(ROWID,16)
---------- ------------------ ---------------------------------------
         1 Typ=2 Len=2: c1,2  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,0
         2 Typ=2 Len=2: c1,3  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,1
         3 Typ=2 Len=2: c1,4  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,2
         4 Typ=2 Len=2: c1,5  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,3
         5 Typ=2 Len=2: c1,6  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,4

I've displayed the value in hexadecimal, as well as the ROWID because the index entries are key -> ROWID and I'll inspect them with a hexadecimal dump.

I locate the index block and my session's trace file:

alter session set tracefile_identifier = 'franck';

column fileno    new_value fileno
column block     new_value block
column tracefile new_value tracefile

select header_file as fileno ,
       header_block+1 as block
   from dba_segments
    where segment_type='INDEX'
      and segment_name='FRANCK'
      and owner=sys_context('USERENV', 'CURRENT_SCHEMA')
;

select value as tracefile
 from v$diag_info
 where name = 'Default Trace File'
;

Each time I want to dump the block and read the entries, I run the following in the session where the variables were defined:

alter system checkpoint;
alter system dump datafile &fileno block &block;
host tail -20 &tracefile | egrep "^row|^col"

I'll dump the block multiple times while another session runs update FRANCK set VAL = VAL + 1;, showing:

the index entry (row# in the dump)
with the 6-byte ROWID (data in the dump)
and the key (col 0 in the dump).

Initial state

The index leaf block before the update contains one entry per table row:

row#0[8021] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 06

The six-byte data:(6) field is the ROWID stored in the unique index entry. The indexed value is stored in col 0 as it is a single-column index.

To help with the hexadecimal values, I've put them in a table to show the table rows and their index entries, with values in hexadecimal and decimal:

table value	table rowid	index entry	index key	index data
1 = c1 02	69 00 00	#0	c1 02 = 1	69 00 00
2 = c1 03	69 00 01	#1	c1 03 = 2	69 00 01
3 = c1 04	69 00 02	#2	c1 04 = 3	69 00 02
4 = c1 05	69 00 03	#3	c1 05 = 4	69 00 03
5 = c1 06	69 00 04	#4	c1 06 = 5	69 00 04

Running the update

I run the update in another session, and thanks to the trigger, it waits 5 seconds between each updated row:


update FRANCK set val = val + 1
;

The update starts the scan and processes the first table row, where the value 1 (c1 02) is incremented to 2 (c1 03).

Oracle first locates the index entry for the old value, acquires an exclusive lock on the row (the lock: 2 references ITL slot 2, whose transaction information identifies the modifying transaction), and marks it as deleted (-D- flag):

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 06

At this intermediate point, the old entry has been deleted, and no entry references the row containing the new value:

table value	table rowid	index entry	index key	index data
2 = c1 03	69 00 00	#0 deleted	c1 02 = 1	69 00 00
2 = c1 03	69 00 01	#1	c1 03 = 2	69 00 01
3 = c1 04	69 00 02	#2	c1 04 = 3	69 00 02
4 = c1 05	69 00 03	#3	c1 05 = 4	69 00 03
5 = c1 06	69 00 04	#4	c1 06 = 5	69 00 04

The next dump is interesting. Oracle does not add another entry for value 2 (c1 03). Instead, it updates the existing entry #1, which already has the right key c1 03 but for a different row. The old ROWID of entry #1 is replaced:

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 06

There are now two index entries for rowid 69 00 00, one deleted and one current:

table value	table rowid	index entry	index key	index data
2 = c1 03	69 00 00	#0 deleted	c1 02 = 1	69 00 00
2 = c1 03	69 00 00	#1	c1 03 = 2	69 00 00
3 = c1 04	69 00 02	#2	c1 04 = 3	69 00 02
4 = c1 05	69 00 03	#3	c1 05 = 4	69 00 03
5 = c1 06	69 00 04	#4	c1 06 = 5	69 00 04

Notice that there is no longer any entry for rowid 69 00 01 because Oracle has changed the ROWID stored in the existing key for value 2 (c1 03) entry from 69 00 01 to 69 00 00. This is why there's no duplicate key situation: all keys in the index are unique.

If the update stops after modifying only the first row, it can create duplicate key issues because the first two rows share the same value. It can also cause logical corruption since the second row lacks an index entry. One possible approach is for Oracle to track these uniqueness conflicts as violation counts during statement processing and confirm that none persist at the end. This strategy would enable Oracle to proceed, expecting that further updates will restore consistency.

The update then continues with the next table row. Rowid 69 00 01 changes from 2 to 3.

I guess that Oracle attempts to locate the entry with key value 2 (c1 03) pointing to 69 00 01 for deletion. However, it finds the key but associated with a different ROWID. Since there's nothing to delete, it leaves it as-is but decreases the violation counter, which then returns to zero, because the current update removes the value that was a duplicate.

As we have seen before, Oracle updates the index entry for the new value c1 04 = 3 to point to rowid 69 00 01:

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 06

Currently, there is an index entry for 69 00 01 with the value 3 (c1 04), but no entry exists for row 69 00 02, so continuing with my guess, the violation counter is incremented.

table value	table rowid	index entry	index key	index data
2 = c1 03	69 00 00	#0 deleted	c1 02 = 1	69 00 00
2 = c1 03	69 00 00	#1	c1 03 = 2	69 00 00
3 = c1 04	69 00 01	#2	c1 04 = 3	69 00 01
4 = c1 05	69 00 03	#3	c1 05 = 4	69 00 03
5 = c1 06	69 00 04	#4	c1 06 = 5	69 00 04

With this pattern, the ROWID associated with each key shifts one row earlier. The same pattern applies to keys 4 and 5. The violation counter may be decremented and then incremented again, remaining at 1.

After processing the row containing the value 4 (c1 05), the key with value 5 (c1 06) points to rowid 69 00 03. The violation counter remains at 1.

Once the row with value 5 (c1 06) is processed, Oracle encounters a situation where no existing key is reused. According to my guess, the violation counter decreases because the previous value no longer matches the current row. The new key value is 6 (c1 07), which did not previously exist. Only then is a new index entry created:

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 06

row#5[7966] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 07

The final state is:

table value	table rowid	index entry	index key	index data
2 = c1 03	69 00 00	#0 deleted	c1 02 = 1	69 00 00
2 = c1 03	69 00 00	#1	c1 03 = 2	69 00 00
3 = c1 04	69 00 01	#2	c1 04 = 3	69 00 01
4 = c1 05	69 00 02	#3	c1 05 = 4	69 00 02
5 = c1 06	69 00 03	#4	c1 06 = 5	69 00 03
6 = c1 07	69 00 04	#5	c1 07 = 6	69 00 04

All rows and index entries are consistent, and the violation counter is back to zero. The index structure I examined never had duplicate keys in the block dumps. Oracle maintains key uniqueness by reusing entries and updating their stored ROWIDs. The method Oracle uses to monitor unresolved uniqueness conflicts until the statement finishes isn't clear from these dumps, but a violation counter appears to be a possible implementation.

UNIQUE vs non-UNIQUE index

Without grasping these internals, one might assume a UNIQUE index isn't necessary for a UNIQUE constraint. However, now we see the performance benefit of this update pattern: it modifies existing index entries rather than deleting the old value and inserting the new one. I tested this with one million rows:

set linesize 200 pagesize 1000
drop table FRANCK purge;
create table FRANCK as select rownum as VAL from xmltable('1 to 1000000');
create unique index FRANCK on FRANCK ( VAL );
update FRANCK set val = val + 1 ;
analyze index FRANCK validate structure;
select height, br_rows, lf_rows, del_lf_rows, br_blks, lf_blks
 from index_stats
;

The index statistics display branch and leaf blocks along with entries. Following the update statement that affected all rows, the unique index only added a single deleted leaf entry (DEL_LF_ROWS):


    HEIGHT    BR_ROWS    LF_ROWS DEL_LF_ROWS    BR_BLKS    LF_BLKS
---------- ---------- ---------- ----------- ---------- ----------
         3       2220    1000001           1          5       2221

I tested the same process using a non-unique index:

set linesize 200 pagesize 1000
drop table FRANCK purge;
create table FRANCK as select rownum as VAL from xmltable('1 to 1000000');
create index FRANCK on FRANCK ( VAL );
update FRANCK set val = val + 1 ;
analyze index FRANCK validate structure;
select height, br_rows, lf_rows, del_lf_rows, br_blks, lf_blks
 from index_stats
;

Using the non-unique index allows temporary violations until the end of the transaction, if the constraint is deferred, but nearly every updated row in this test left a deleted entry, resulting in the number of leaf entries and leaf blocks being nearly twice as many as with the unique index:


    HEIGHT    BR_ROWS    LF_ROWS DEL_LF_ROWS    BR_BLKS    LF_BLKS
---------- ---------- ---------- ----------- ---------- ----------
         3       4830    1999530      999530         13       4831

In Oracle, a unique constraint does not always need a unique index. However, in this specific update pattern, the unique index involves significantly fewer index modifications and uses fewer leaf blocks compared to a non-unique index doing the same job.

Conclusion

The block dumps illustrate the physical behavior: during processing, Oracle reuses existing unique-key entries by updating their stored ROWIDs. When each value shifts to the next key, the associated ROWID also moves to the preceding row in the table. Only the last value, 6, necessitates a new index entry.

The dumps do not disclose the precise statement-level mechanism Oracle employs to differentiate a temporary collision—resolved later in the statement—from a genuine uniqueness violation. A conflict counter is one potential implementation, but this remains speculative. Confirming it would require more tracing or evidence beyond leaf-block states. To date, no public documentation confirms this implementation. In contrast, SQL Server exposes how it handles this issue, as explained by Craig Freedman in Maintaining Unique Indexes: the execution plan reveals a complex reorganization of the update order through Split, Filter, and Sort Collapse operators.

The behavior we observed on Oracle Database appears to be ideal for set-based updates, and Oracle's implementation seems relatively straightforward. Why do other databases like PostgreSQL differ? And why does SQL Server require more complex processing? I can suggest two reasons:

PostgreSQL allows duplicate keys in indexes because of its MVCC approach with heap-based visibility. It must retain deleted entries as long as the dead tuples are visible to ensure previous snapshot queries find the rows. Oracle uses block-level MVCC, so even if old entries aren't in the current block, they reappear when undo information is used to create a consistent read snapshot.
PostgreSQL displays the specific row causing a constraint violation, such as DETAIL: Key (val)=(2) already exists., immediately after the first update in my example. Oracle, however, defers error reporting until the statement ends, which can result in loss of this detailed info at error time (see oracle vs. postgres).

Typically, no single database is universally better. Each involves different trade-offs. One database enables efficient bulk updates, allowing temporary constraint violations without deferring constraints, while another specifies the exact row causing a constraint violation. SQL Server appears to blend these approaches: it avoids raising transient violations and indicates the offending value when a violation happens, at the cost of reordering the rows being updated.

If you liked this blog, you may enjoy seeing why SQL Server made different design choices. As a non-MVCC engine, it must protect updates from both false uniqueness violations and the Halloween problem. Louis Tochon just wrote an deep dive article about the Split/Sort/Collapse mechanism: https://www.dbi-services.com/blog/split-sort-collapse-how-sql-server-survives-an-overlapping-unique-update

B-tree block split: what's the impact?

Franck Pachot — Fri, 24 Jul 2026 16:20:52 +0000

Everybody knows that B-tree indexes provide logarithmic lookups, but what does that actually mean in PostgreSQL?
How many index entries fit in a page? When does a page split? When does the tree gain another level? How much additional WAL is generated? How many more buffers are touched during an index lookup?
PostgreSQL exposes enough information to observe all of this directly. One command and two extensions included in PostgreSQL contrib are particularly useful:

EXPLAIN (analyze, buffers, wal) for observing the buffer activity and WAL generated by DML on indexes
pageinspect for inspecting internal page structures
pgstattuple for gathering statistics about index pages

By inserting rows one at a time and collecting B-tree statistics after each insert, we can watch the index grow from a single page to a multi-level B-tree and correlate page splits with WAL generation and buffer accesses.

Inspecting the B-Tree Metadata with bt_metap()

The pageinspect extension is a low-level debugging tool that exposes information stored in table and index pages.
Block 0 of every B-tree index is the metapage. It stores information about the root page, the current tree depth, and other metadata.

postgres=# create extension if not exists pageinspect;
CREATE EXTENSION

postgres=# \x
Expanded display is on.

postgres=# select * from bt_metap('demo_pkey');
-[ RECORD 1 ]-------------+-------
magic                     | 340322
version                   | 4
root                      | 53194
level                     | 3
fastroot                  | 53194
fastlevel                 | 3
last_cleanup_num_delpages | 0
last_cleanup_num_tuples   | -1
allequalimage             | t

postgres=# \x
Expanded display is off.

The values come directly from BTMetaPageData:

magic: A constant (340322) used to verify the file is a B-tree.
version: The B-tree layout version.
root: The block number of the "true" root. It can change when the root block is split to add a level.
level: The distance from the true root to leaf pages
fastroot and fastlevel: the block number and level of the effective root from which B-tree searches start. They can let searches skip upper levels that contain only a single page (more info in the [README].(https://github.com/postgres/postgres/blob/REL_19_BETA2/src/backend/access/nbtree/README#L362))
last_cleanup_num_delpages: Pages deleted during the last VACUUM.
allequalimage: Optimization flag indicating if the index can use "deduplication" safely.

B-tree searches start at fastroot rather than necessarily at the true root. Starting from a page at fastlevel, PostgreSQL follows downlinks until it reaches the leaf page containing the relevant key range.

Collecting Statistics With pgstatindex()

Where pageinspect exposes internal metadata, pgstattuple can scan the index and provide aggregated statistics related to bloat and fragmentation.

postgres=# create extension if not exists pgstattuple;
CREATE EXTENSION

postgres=# \x
Expanded display is on.

postgres=# select * from pgstatindex('demo_pkey');
-[ RECORD 1 ]------+----------
version            | 4
tree_level         | 3
index_size         | 586776576
root_block_no      | 53194
internal_pages     | 351
leaf_pages         | 71276
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 69.04
leaf_fragmentation | 49.84

postgres=# \x
Expanded display is off.

This function returns:

tree_level: the level of the root page, with leaf pages at level 0. It is therefore also the number of downward links followed from the root to reach a leaf page.
index_size: Total size in bytes calculated from the total number of pages
internal_pages: counts of non-leaf pages, so the branches including the root, found during the scan.
leaf_pages: counts the leaf pages found during the scan, so the ones storing the index entries with the heap tuple identifier of the row (TID)
empty_pages: half-dead pages, which are empty but still present in the tree
deleted_pages: pages deleted from the tree and available for reuse
avg_leaf_density: the average percentage of usable leaf-page space occupied by index tuples. A low value can indicate unused space or bloat, although page splits also naturally leave free space.
leaf_fragmentation: Measures how many leaf pages are physically out of order.

Test setup

I've run the following to insert rows and display the B-tree statistics after each insert:

-- create a table with an index (the primary key)
drop table demo;
create table demo ( id uuid primary key);

-- get B-tree statistics
create extension if not exists pgstattuple;
select * from pgstatindex('demo_pkey');
create extension if not exists pageinspect;
select * from bt_metap('demo_pkey');

-- insert a row with random value for the indexed column,
-- display the index statistics and repeat
\x
explain ( analyze, buffers, wal, costs off )
insert into demo (id) values ( uuidv4() )
\;
select * from pgstatindex('demo_pkey')
\watch i=0.01

I used the following AWK script to parse the result, display one line per row inserted, and flag when the WAL size generated by the insert is different from the preceding:

awk -F"|" '
/Insert/{ rows=rows + 1 }
/WAL/{ wal = $2 }
/Buffer/{ buf = $2 }
$1~/^[a-z_]+ */ { sub(/ *$/,"",$1 ) ; val[$1]=$2 }
/^$/ && val["tree_level"]!="" {
 key=wal " " val["tree_level"]
 if ( key != prev ) {pre="+"} else {pre=" "}
 printf "%1s %8d rows, %4d levels, %4d internal, %6d leaf, %6.2f MB, %3d%%, last insert: %-28s %-50s\n", pre, rows, val["tree_level"], val["internal_pages"], val["leaf_pages"], val["index_size"] / 1024 / 1024, val["avg_leaf_density"], wal, buf
 val["tree_level"]=""
 prev=key
}
' | grep -C3 '^+'

The First Split

In this test, the first 291 index entries fit in a single page - 0 internal pages, only one leaf, which is also the root:

+        1 rows,    0 levels,    0 internal,      1 leaf,   0.02 MB,   0%, last insert:    WAL: records=3 bytes=233     Buffers: shared hit=4 dirtied=3 written=2
+        2 rows,    0 levels,    0 internal,      1 leaf,   0.02 MB,   0%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
         3 rows,    0 levels,    0 internal,      1 leaf,   0.02 MB,   1%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=2
         4 rows,    0 levels,    0 internal,      1 leaf,   0.02 MB,   1%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=2
         5 rows,    0 levels,    0 internal,      1 leaf,   0.02 MB,   1%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=2
--
       289 rows,    0 levels,    0 internal,      1 leaf,   0.02 MB,  99%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=2
       290 rows,    0 levels,    0 internal,      1 leaf,   0.02 MB,  99%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=2
       291 rows,    0 levels,    0 internal,      1 leaf,   0.02 MB,  99%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=2

In this test, most inserts generate two WAL records: one for the heap insertion and one for the index insertion. We see the average leaf density increase steadily to 99% as the root page fills.

The page is now full and must be split to insert the 292nd index entry. PostgreSQL creates a new root page above two leaf pages, resulting in a B-tree with one level:

+      292 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  50%, last insert:    WAL: records=3 bytes=1057    Buffers: shared hit=3 dirtied=2 written=2
+      293 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  50%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=5
       294 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  50%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       295 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  50%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       296 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  51%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
--
       321 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  55%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       322 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  55%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       323 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  55%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3

The split generates three WAL records instead of two. The next insert touches five shared buffers, while subsequent inserts stabilize at three: the heap page, the root page, and one leaf page.

This continues until those pages are full and must be split, with one more leaf page and one more WAL record for the one that had to split:

       323 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  55%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
+      324 rows,    1 levels,    1 internal,      3 leaf,   0.04 MB,  37%, last insert:    WAL: records=3 bytes=3799    Buffers: shared hit=5 dirtied=1 written=1
+      325 rows,    1 levels,    1 internal,      3 leaf,   0.04 MB,  37%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       326 rows,    1 levels,    1 internal,      3 leaf,   0.04 MB,  37%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       327 rows,    1 levels,    1 internal,      3 leaf,   0.04 MB,  37%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       328 rows,    1 levels,    1 internal,      3 leaf,   0.04 MB,  37%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
...
       645 rows,    1 levels,    1 internal,      3 leaf,   0.04 MB,  74%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       646 rows,    1 levels,    1 internal,      3 leaf,   0.04 MB,  74%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       647 rows,    1 levels,    1 internal,      3 leaf,   0.04 MB,  74%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
+      648 rows,    1 levels,    1 internal,      4 leaf,   0.05 MB,  55%, last insert:    WAL: records=3 bytes=3775    Buffers: shared hit=5 dirtied=1 written=1
+      649 rows,    1 levels,    1 internal,      4 leaf,   0.05 MB,  55%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       650 rows,    1 levels,    1 internal,      4 leaf,   0.05 MB,  56%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
+      651 rows,    1 levels,    1 internal,      5 leaf,   0.05 MB,  45%, last insert:    WAL: records=3 bytes=3775    Buffers: shared hit=5 dirtied=1 written=1
+      652 rows,    1 levels,    1 internal,      5 leaf,   0.05 MB,  45%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       653 rows,    1 levels,    1 internal,      5 leaf,   0.05 MB,  45%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       654 rows,    1 levels,    1 internal,      5 leaf,   0.05 MB,  45%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       655 rows,    1 levels,    1 internal,      5 leaf,   0.05 MB,  45%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
...
      1256 rows,    1 levels,    1 internal,      5 leaf,   0.05 MB,  86%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
      1257 rows,    1 levels,    1 internal,      5 leaf,   0.05 MB,  86%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
      1258 rows,    1 levels,    1 internal,      5 leaf,   0.05 MB,  86%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
+     1259 rows,    1 levels,    1 internal,      6 leaf,   0.06 MB,  72%, last insert:    WAL: records=3 bytes=3799    Buffers: shared hit=5 dirtied=1 written=1
+     1260 rows,    1 levels,    1 internal,      6 leaf,   0.06 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
      1261 rows,    1 levels,    1 internal,      6 leaf,   0.06 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
      1262 rows,    1 levels,    1 internal,      6 leaf,   0.06 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
      1263 rows,    1 levels,    1 internal,      6 leaf,   0.06 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3

This pattern continues until, in this test, the root page is full of downlinks to 291 leaf pages, so that it must be split into two internal pages, with a new root above, increasing the level to 2:

     61343 rows,    1 levels,    1 internal,    291 leaf,   2.29 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
     61344 rows,    1 levels,    1 internal,    291 leaf,   2.29 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
     61345 rows,    1 levels,    1 internal,    291 leaf,   2.29 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
+    61346 rows,    2 levels,    3 internal,    292 leaf,   2.31 MB,  72%, last insert:    WAL: records=4 bytes=6017    Buffers: shared hit=6 dirtied=4 written=3
+    61347 rows,    2 levels,    3 internal,    292 leaf,   2.31 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=6
     61348 rows,    2 levels,    3 internal,    292 leaf,   2.31 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61349 rows,    2 levels,    3 internal,    292 leaf,   2.31 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61350 rows,    2 levels,    3 internal,    292 leaf,   2.31 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
...
     61408 rows,    2 levels,    3 internal,    292 leaf,   2.31 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61409 rows,    2 levels,    3 internal,    292 leaf,   2.31 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61410 rows,    2 levels,    3 internal,    292 leaf,   2.31 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
+    61411 rows,    2 levels,    3 internal,    293 leaf,   2.32 MB,  72%, last insert:    WAL: records=3 bytes=3775    Buffers: shared hit=6 dirtied=1 written=1
+    61412 rows,    2 levels,    3 internal,    293 leaf,   2.32 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61413 rows,    2 levels,    3 internal,    293 leaf,   2.32 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61414 rows,    2 levels,    3 internal,    293 leaf,   2.32 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61415 rows,    2 levels,    3 internal,    293 leaf,   2.32 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
...
     61543 rows,    2 levels,    3 internal,    293 leaf,   2.32 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61544 rows,    2 levels,    3 internal,    293 leaf,   2.32 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61545 rows,    2 levels,    3 internal,    293 leaf,   2.32 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
+    61546 rows,    2 levels,    3 internal,    294 leaf,   2.33 MB,  72%, last insert:    WAL: records=3 bytes=3775    Buffers: shared hit=6 dirtied=1 written=1
+    61547 rows,    2 levels,    3 internal,    294 leaf,   2.33 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61548 rows,    2 levels,    3 internal,    294 leaf,   2.33 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61549 rows,    2 levels,    3 internal,    294 leaf,   2.33 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61550 rows,    2 levels,    3 internal,    294 leaf,   2.33 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4

The split that increased the level wrote 4 WAL records. The next inserts then filled one page, writing 2 WAL records (leaf + heap). In this test, inserts now touch four shared buffers instead of three because the index has one additional level. Leaf splits that do not have to split an internal page write 3 WAL records.

At this point, the tree contains three internal pages: one root page at level 2 and two branch pages at level 1. The leaf pages are at level 0.

When a branch page at level 1 becomes full, it is split. This does not increase the tree level as long as the new downlink can be inserted into the root page at level 2:

     80439 rows,    2 levels,    3 internal,    398 leaf,   3.14 MB,  69%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     80440 rows,    2 levels,    3 internal,    398 leaf,   3.14 MB,  69%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     80441 rows,    2 levels,    3 internal,    398 leaf,   3.14 MB,  69%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
+    80442 rows,    2 levels,    4 internal,    399 leaf,   3.16 MB,  69%, last insert:    WAL: records=4 bytes=7415    Buffers: shared hit=8 dirtied=2 written=2
+    80443 rows,    2 levels,    4 internal,    399 leaf,   3.16 MB,  69%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     80444 rows,    2 levels,    4 internal,    399 leaf,   3.16 MB,  69%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     80445 rows,    2 levels,    4 internal,    399 leaf,   3.16 MB,  69%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     80446 rows,    2 levels,    4 internal,    399 leaf,   3.16 MB,  69%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4

When there are so many branch pages at level 1 that their downlinks fill the root at level 2, the root must split. This happened in my example when the index reached 417.92 MB with 53193 leaf pages and 300 internal pages.

This is when I ran bt_metap() and pgstatindex() and obtained the result shown at the beginning of this post, with a 3-level B-tree.

Conclusion

B-tree indexes grow through a series of page splits. In this test, most inserts are inexpensive, touching one leaf page and generating two WAL records. Occasionally, a leaf page becomes full, triggering a split and additional WAL. More rarely, a branch page splits. Rarest of all are root splits, which add another level to the tree.

This experiment illustrates the write amplification inherent in B-trees, but also shows why they scale so well. The first additional level appeared after only a few hundred rows. The next required more than sixty thousand rows. The third required an index over 400 MB in size.

Page splits occur often enough to keep the tree balanced, while increases in tree level happen much more gradually. Even large PostgreSQL indexes therefore remain shallow. Their internal pages are also likely to remain cached because there are relatively few of them. For indexed queries that return table rows, the more significant I/O cost is often fetching heap pages rather than traversing the B-tree itself.

B+tree height after full delete: PostgreSQL fast root

Franck Pachot — Thu, 23 Jul 2026 21:23:36 +0000

Many databases use B+tree indexes, but they all differ. It's a sorted structure. The leaf pages are logically sorted so that a specific key value belongs to one page. A lookup by value reaches a single leaf page and either directly finds an entry for that value or immediately knows there's no entry with that key. When a page becomes full, it is split into two pages, each covering its own dedicated range. To find the right page, an internal page holds the range of values for the pages below. This internal page can become full, and a new level is added above it. Finally, at the highest level, there's a single internal page that is the root. A lookup always starts at the root and goes down to the leaves, following the branches of internal pages. In a traditional B+tree lookup, the cost is proportional to the height of the tree because the search starts at the root and descends to a leaf:

1 page to read when all fits in one leaf that is also the root (0 levels of internal pages, total height is 1). With small keys, this level can typically index hundreds of rows.
2 pages to read when there's one root that can list all leaf pages (1 level of internal page, total height is 2). With small keys, this level can typically index tens or hundreds of thousands of rows.
3 pages to read when there's one level of branches under the root (so 2 levels of internal pages, total height is 3). With small keys, this level can typically index millions of rows.

This means that finding one key within ten million rows may require traversing 3 index pages, where most of them are probably in cache given the small number of branches compared to the leaves. For a given index size, whatever the value you are looking for, it's always the same number of pages to read because the index is balanced (the commonly accepted meaning of the B in B+tree). This property is maintained because any page can split, but only splitting the root adds another level.

I've described how the height of an index can increase, impacting the cost of the lookup, as data grows, but do you know if your database can reduce the height of the B+tree when data is deleted?

I've been working with Oracle Database for a long time, and the answer is easy: the height of the B+tree index never decreases, even if you delete all rows, even if you coalesce the index, even if you shrink the index, until you completely rebuild it. Then I worked with PostgreSQL, which is famous for index bloat, and realized that the effective B+tree height can decrease even without a rebuild.

Oracle Database: reclaim levels with rebuild

I created a table with five million rows and an index on the ID, using a random 16-byte UUID:


drop table if exists bloat;

create table bloat ( id raw(16) constraint bloat_pkey primary key);

insert into bloat select uuid() from xmltable('1 to 5000000');

commit;

exec dbms_stats.gather_table_stats(user,'bloat');

I collect statistics on the logical structure of the index:


analyze index bloat_pkey validate structure;

select height-1 blevel, blocks pages, lf_blks leaf_pages, br_blks internal_pages from index_stats;

The result shows two levels of branches, with 40 internal pages including the root. There's one root at level 0 and 39 branches at level 1, addressing 24837 leaves:


    BLEVEL      PAGES LEAF_PAGES INTERNAL_PAGES
---------- ---------- ---------- --------------
         2      25728      24837             40

This means that finding a key in the index requires reading 3 pages. I can verify this with the execution plan:


set linesize 200
alter session set statistics_level=all;
select * from bloat where id = uuid_to_raw('00000000000000000000000000000000');
select * from dbms_xplan.display_cursor(format=>'allstats last');

This confirms that three buffers were read before determining that the key does not exist:


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7jxrrzwtd7aqd, child number 0
-------------------------------------
select * from bloat where id = uuid_to_raw('00000000000000000000000000000000')

Plan hash value: 1755540699

------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |      1 |        |      0 |00:00:00.01 |       3 |
|*  1 |  INDEX UNIQUE SCAN| BLOAT_PKEY |      1 |      1 |      0 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=UUID_TO_RAW('00000000000000000000000000000000'))

I delete all rows with an intermediate commit because undo is limited:

delete from bloat where rownum<=1e6;
commit;
delete from bloat where rownum<=1e6;
commit;
delete from bloat where rownum<=1e6;
commit;
delete from bloat where rownum<=1e6;
commit;
delete from bloat where rownum<=1e6;
commit;

The lookup still traverses three levels of logically empty pages:

------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |      1 |        |      0 |00:00:00.01 |       3 |
|*  1 |  INDEX UNIQUE SCAN| BLOAT_PKEY |      1 |      1 |      0 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------

The B-tree index didn't change after the delete - same height and same number of pages:


    BLEVEL      PAGES LEAF_PAGES INTERNAL_PAGES
---------- ---------- ---------- --------------
         2      25728      24837             40

Oracle can merge adjacent pages with a coalesce command:


alter index bloat_pkey coalesce;

This reduced the number of leaf and internal pages to the minimum possible while preserving the existing tree structure: one leaf page, one branch page, and the root page. The number of levels doesn't change, and the cost of the lookup remains the same:

    BLEVEL      PAGES LEAF_PAGES INTERNAL_PAGES
---------- ---------- ---------- --------------
         2      25728          1              2

------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |      1 |        |      0 |00:00:00.01 |       3 |
|*  1 |  INDEX UNIQUE SCAN| BLOAT_PKEY |      1 |      1 |      0 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------

Oracle can shrink the allocated blocks:


alter index bloat_pkey shrink space;

This didn't reduce anything further:


    BLEVEL      PAGES LEAF_PAGES INTERNAL_PAGES
---------- ---------- ---------- --------------
         2      25728          1              2


------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |      1 |        |      0 |00:00:00.01 |       3 |
|*  1 |  INDEX UNIQUE SCAN| BLOAT_PKEY |      1 |      1 |      0 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------

Finally, I need to rebuild the index to get one with a height that corresponds to an empty table:


alter index bloat_pkey rebuild online;

This is the correct size of an index with no rows - a single page that serves as both the root and the leaf:

    BLEVEL      PAGES LEAF_PAGES INTERNAL_PAGES
---------- ---------- ---------- --------------
         0          8          1              0

------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |      1 |        |      0 |00:00:00.01 |       1 |
|*  1 |  INDEX UNIQUE SCAN| BLOAT_PKEY |      1 |      1 |      0 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------

Oracle Database indexes can increase in height, but the height does not decrease automatically after deletions. Reclaiming levels requires an index rebuild. When a large amount of data is deleted, the cost of an index scan from the root to the leaf remains high until the index is rebuilt.

PostgreSQL

Here is a similar table containing five million rows:

drop table if exists bloat;
create table bloat ( id uuid constraint bloat_pkey primary key);
insert into bloat select uuidv4() from generate_series(1,5000000);
vacuum analyze bloat;

I use pgstattuple to collect and aggregate the index statistics:

create extension if not exists pgstattuple;
select tree_level blevel, index_size/8192 pages, leaf_pages, internal_pages from pgstatindex('bloat_pkey');

The result shows two levels of branches, with 111 internal pages including the root. There's one root at level 0 and 110 branches at level 1, addressing 24607 leaves:

 blevel | pages | leaf_pages | internal_pages
--------+-------+------------+----------------
      2 | 24719 |      24607 |            111

EXPLAIN ANALYZE shows an Index Only Scan that reads 3 pages, the root, one branch, and the leaf:

postgres=# explain (analyze, buffers, costs off) select * from bloat where id = '00000000000000000000000000000000';
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Index Only Scan using bloat_pkey on bloat (actual time=0.019..0.020 rows=0.00 loops=1)
   Index Cond: (id = '00000000-0000-0000-0000-000000000000'::uuid)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared hit=3
 Planning Time: 0.065 ms
 Execution Time: 0.034 ms
(7 rows)

I delete all rows and run the same:


postgres=# delete from bloat;

DELETE 5000000

postgres=# explain (analyze, buffers, costs off) select * from bloat where id = '00000000000000000000000000000000';

                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Index Only Scan using bloat_pkey on bloat (actual time=0.018..0.018 rows=0.00 loops=1)
   Index Cond: (id = '00000000-0000-0000-0000-000000000000'::uuid)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared hit=3
 Planning Time: 0.066 ms
 Execution Time: 0.032 ms
(7 rows)

postgres=# select tree_level blevel, index_size/8192 pages, leaf_pages, internal_pages from pgstatindex('bloat_pkey');

 blevel | pages | leaf_pages | internal_pages
--------+-------+------------+----------------
      2 | 24719 |      24607 |            111

The B-tree size and level remain the same after a delete because MVCC information is still present for transactions that may read a previous snapshot. After a while, autovacuum removes the dead index entries that are no longer visible to any transaction (you can also run VACUUM manually).

The PostgreSQL planner may prefer a sequential scan on an empty table, so I force an index scan to count how many index pages are read to find a key:


postgres=# set enable_seqscan to off;
SET

postgres=# explain (analyze, buffers, costs off) select * from bloat where id = '00000000000000000000000000000000';

                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Index Only Scan using bloat_pkey on bloat (actual time=0.013..0.013 rows=0.00 loops=1)
   Index Cond: (id = '00000000-0000-0000-0000-000000000000'::uuid)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared hit=1
 Planning Time: 0.158 ms
 Execution Time: 0.027 ms
(7 rows)

Only one page is read because a single root is sufficient to list the index entries of an empty table. No rebuild is required: regular autovacuum is sufficient.

If I check the index statistics, they still show two levels, with one root, one leaf, and a branch between them:

postgres=# select tree_level blevel, index_size/8192 pages, leaf_pages, internal_pages from pgstatindex('bloat_pkey')
;
 blevel | pages | leaf_pages | internal_pages
--------+-------+------------+----------------
      2 | 24719 |          1 |              2
(1 row)

PostgreSQL merged empty blocks with the regular VACUUM. However, similar to Oracle Database, the physical B-tree's height didn't decrease. Only its width at each level was reduced, with each level maintaining at least one page.

However, PostgreSQL can avoid traversing redundant upper levels. Internal pages determine which page to read at the lower level. If there's only one page at the level under the root, there's no need to read the root. We can start the scan at the highest level that has a single page. PostgreSQL keeps track of it as the "fast root". I can verify this from the index metadata, which I can read with the pageinspect extension:


postgres=#  create extension if not exists pageinspect;

CREATE EXTENSION

postgres=#  select * from bt_metap('bloat_pkey');

 magic  | version | root | level | fastroot | fastlevel | last_cleanup_num_delpages | last_cleanup_num_tuples | allequalimage
--------+---------+------+-------+----------+-----------+---------------------------+-------------------------+---------------
 340322 |       4 |  295 |     2 |    14512 |         0 |                     24715 |                      -1 | t
(1 row)

The physical number of levels is "level," and the true root is at block 295, but there's another root, "fastroot," with a different address, 14512. It was one of the internal pages when the table was full, or even a leaf in our case. As data was deleted, it remained alone at its level, and the levels above, up to the root, had a single entry, in a single page.

PostgreSQL records this page as the fast root and can start searches from it, bypassing upper levels that contain only a single pointer. Because the table is now empty, this page effectively serves as a leaf page. That's how an Index Scan reads only one page. The logical level, or "fastlevel," is recorded in the index's metadata page so that the query planner can evaluate the cost correctly.

I don't need to rebuild this index, but I can:

postgres=# reindex index concurrently bloat_pkey ;

REINDEX

postgres=#  select * from bt_metap('bloat_pkey')
;
 magic  | version | root | level | fastroot | fastlevel | last_cleanup_num_delpages | last_cleanup_num_tuples | allequalimage
--------+---------+------+-------+----------+-----------+---------------------------+-------------------------+---------------
 340322 |       4 |    0 |     0 |        0 |         0 |                         0 |                      -1 | t
(1 row)

postgres=# select tree_level blevel, index_size/8192 pages, leaf_pages, internal_pages from pgstatindex('bloat_pkey')
;
 blevel | pages | leaf_pages | internal_pages
--------+-------+------------+----------------
      0 |     1 |          0 |              0
(1 row)

After the rebuild, all statistics show that the B-tree has a single page.

postgres=# explain (analyze, buffers, costs off) select * from bloat where id = '00000000000000000000000000000000';

                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Index Only Scan using bloat_pkey on bloat (actual time=0.005..0.005 rows=0.00 loops=1)
   Index Cond: (id = '00000000-0000-0000-0000-000000000000'::uuid)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared hit=2
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.067 ms
 Execution Time: 0.021 ms
(9 rows)

You may wonder why two pages are read during execution. A clue is that one page is read during planning, even if you run it many times.

This is due to the rebuild with no DML to read and cache the metapage. Let's insert one row:

postgres=# insert into bloat select uuidv4();

INSERT 0 1

postgres=#  explain (analyze, buffers, costs off) select * from bloat where id = '00000000000000000000000000000000';

                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Index Only Scan using bloat_pkey on bloat (actual time=0.010..0.010 rows=0.00 loops=1)
   Index Cond: (id = '00000000-0000-0000-0000-000000000000'::uuid)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared hit=1
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.069 ms
 Execution Time: 0.024 ms
(9 rows)

postgres=#

During the planning phase, the system reads the index metapage (1 buffer) to obtain accurate statistics for cost estimation. In contrast, during execution, it relies on the relcache (rd_amcache) when available to prevent repeated metapage fetches. When REINDEX occurs, the relcache becomes invalid, requiring execution to read the index page again, increasing buffer usage to 2. After an INSERT, the relcache is updated with valid metadata, enabling execution to access the cached root location without extra buffer reads, keeping total buffer usage at 1.

Two variations

In this experiment, I deleted all rows. After vacuuming, PostgreSQL releases the full pages and reclaims them. However, it does not merge or coalesce nearly empty pages. I repeated the same example with a sparse delete, leaving 19393 rows- less than one entry per existing leaf page- and the number of pages has decreased, but the b-tree level has not decreased:

postgres=# delete from bloat where id::text not like '%42';

DELETE 4980462

postgres=# vacuum bloat;

VACUUM

postgres=# select tree_level blevel, index_size/8192 pages, leaf_pages, internal_pages from pgstatindex('bloat_pkey')
;
 blevel | pages | leaf_pages | internal_pages
--------+-------+------------+----------------
      2 | 24706 |      13202 |            121
(1 row)

postgres=# select * from bt_metap('bloat_pkey')
;
 magic  | version | root | level | fastroot | fastlevel | last_cleanup_num_delpages | last_cleanup_num_tuples | allequalimage
--------+---------+------+-------+----------+-----------+---------------------------+-------------------------+---------------
 340322 |       4 |  295 |     2 |      295 |         2 |                     11382 |                      -1 | t
(1 row)

Then I repeated the test, but with a range delete, resulting in 19563 rows with the same prefix:

postgres=# delete from bloat where id::text not like '42%';

DELETE 4980437

postgres=# vacuum bloat;

VACUUM

postgres=# select tree_level blevel, index_size/8192 pages, leaf_pages, internal_pages from pgstatindex('bloat_pkey')
;
 blevel | pages | leaf_pages | internal_pages
--------+-------+------------+----------------
      2 | 24621 |         88 |              3
(1 row)

postgres=# select * from bt_metap('bloat_pkey')
;
 magic  | version | root | level | fastroot | fastlevel | last_cleanup_num_delpages | last_cleanup_num_tuples | allequalimage
--------+---------+------+-------+----------+-----------+---------------------------+-------------------------+---------------
 340322 |       4 |  295 |     2 |      295 |         2 |                     24529 |                      -1 | t
(1 row)

Here, the leaf pages have decreased considerably because many pages were fully empty, but not enough to reduce the tree level.

Here is a very nice visualization of this behavior: Your index is half empty. Postgres can't squeeze it, thanks to Nik Samokhvalov and Kirk Wolak. There is no equivalent to the COALESCE command in PostgreSQL: it is automatic for empty pages, and needs a REBUILD to reclaim space from sparse ones.

Conclusion

The behavior of B+tree indexes after large deletions differs significantly between database engines.

PostgreSQL preserves the physical height of the B+tree after deletions, but VACUUM can reduce its effective traversal depth through a sophisticated fast root optimization. The index metadata page maintains two root pointers: the true root representing the physical tree height, and the fast root pointing to the lowest single-page level. This design, based on Lanin and Shasha's approach, allows PostgreSQL to bypass redundant upper levels that contain only a single pointer and start searches closer to the leaves.

The fast root pointer is adjusted atomically during page splits and deletions. When a page that is alone on its level splits, or when the next-to-last page on a level is deleted, PostgreSQL updates the fast root pointer as part of the atomic operation, with the metapage locked last to avoid deadlocks. The query planner uses the fast root level for cost estimation, ensuring accurate planning based on effective rather than physical height.

PostgreSQL also caches metadata in the relation cache to avoid fetching the metapage for every search, with validation checks to handle stale cached pointers. The physical tree remains unchanged, but lookups require fewer page accesses without any rebuild.

Oracle Database also preserves the physical structure of the index. Pages can be merged, and space can be reclaimed, but the height of the B+tree remains unchanged until the index is rebuilt. As a result, lookups continue to traverse the same number of levels even when the index contains few or no entries. A possible reason for the lack of a fast root optimization is Oracle's MVCC, where the root depends on the read snapshot, unlike PostgreSQL, where the index contains entries for all versions.

In both databases, a rebuild produces the smallest possible B+tree after a delete, according to the PCTFREE (Oracle) or FILLFACTOR (PostgreSQL) defined. The difference is that PostgreSQL can often recover most of the lookup efficiency automatically through regular vacuuming by maintaining a fast root, whereas Oracle requires an explicit rebuild to reclaim those levels. This applies only to deletions where you don't anticipate inserting the same amount, in which case it's better to retain the currently allocated structure and avoid future splits.

From Joins to Graph Edges: SQL/PGQ in PostgreSQL 19

Franck Pachot — Wed, 22 Jul 2026 17:01:35 +0000

In the previous post, Cypher graph queries on PostgreSQL with Apache AGE, I showed how to model and query a property graph in PostgreSQL using Apache AGE. The graph was materialized as vertices and edges stored in dedicated tables.
PostgreSQL 19, currently in beta, takes a different route for built-in support of graph queries. With SQL/PGQ, a property graph is defined as a logical model on top of existing relational tables, without duplicating the underlying data. In short:

Apache AGE: the graph is a stored data structure.
SQL/PGQ: the graph is a semantic layer over relational data.

Unlike graph databases, or extensions such as Apache AGE, SQL/PGQ (SQL Property Graph Queries) does not introduce a separate graph storage model. Property graphs are defined on top of existing relational tables. The data remains relational, while graph queries become another way to access it.

Relational model

I'll use the legendary EMP/DEPT schema from more than 45 years ago:

create table "department" (
    "deptno" integer primary key,
    "name"   text not null,
    "loc"    text not null
);

create table "employee" (
    "empno"  integer primary key,
    "name"   text not null,
    "job"    text not null,
    "mgr"    integer references "employee"("empno"),
    "deptno" integer not null references "department"("deptno"),
    "sal"    integer not null
);

insert into "department" ("deptno", "name", "loc") values
(10, 'Administration', 'New York'),
(20, 'Research',       'San Francisco'),
(30, 'Sales',          'Chicago'),
(40, 'Operations',     'Boston');

insert into "employee" ("empno", "name", "job", "mgr", "deptno", "sal") values
(7839, 'OATES',  'President', NULL, 10, 5000),
(7566, 'JONES',  'Manager',   7839, 20, 2975),
(7698, 'BLAKE',  'Manager',   7839, 30, 2850),
(7782, 'CLARK',  'Manager',   7839, 10, 2450),
(7788, 'SCOTT',  'Analyst',   7566, 20, 3000),
(7902, 'FORD',   'Analyst',   7566, 20, 3000),
(7999, 'WILSON', 'Analyst',   7566, 20, 2800),
(7876, 'ADAMS',  'Clerk',     7788, 20, 1100),
(7369, 'SMITH',  'Clerk',     7902, 20,  800),
(8000, 'JAKES',  'Clerk',     7999, 20, 1000),
(7499, 'ALLEN',  'Salesman',  7698, 30, 1600),
(7521, 'WARD',   'Salesman',  7698, 30, 1250),
(7654, 'MARTIN', 'Salesman',  7698, 30, 1250),
(7844, 'TURNER', 'Salesman',  7698, 30, 1500),
(7900, 'JAMES',  'Clerk',     7698, 30,  950),
(8001, 'CARTER', 'Salesman',  7698, 30, 1400),
(7934, 'MILLER', 'Clerk',     7782, 10, 1300);

The employee hierarchy is represented through a self-referencing foreign key employee.mgr -> employee.empno and the employee-department relationship through employee.deptno -> department.deptno. This is the same model that has been used for decades in relational databases.

This model can be queried with joins and when there is a variable level of joins, WITH RECURSIVE clause can iterate in them. However, the queries quickly become complex and you need to think about the graph traversal for each query.

Property graph definition

SQL/PGQ introduces a property graph definition on top of relational tables, allowing them to be queried as vertices and edges rather than through joins.

The graph definition maps tables to vertices and foreign-key relationships to edges:

create property graph "emp_dept_graph"
vertex tables (
    "department" label "department",
    "employee"   label "employee"
)
edge tables (
    "employee" as "reports"
        source key      ("empno") references "employee"   ("empno")
        destination key ("mgr")   references "employee"   ("empno")
        label           "reports_to",
    "employee" as"works"
        source key      ("empno")  references "employee"   ("empno")
        destination key ("deptno") references "department" ("deptno")
        label           "works_in"
);

Unlike Apache AGE, no vertices or edges are stored separately. PostgreSQL exposes existing rows as graph elements. The relational tables remain the source of truth. The property graph is a queryable layer that maps the relational model to a graph model.

Querying the graph

To find Jones's manager, the Cypher query was:

MATCH (:Employee {name:"JONES"})-[:REPORTS_TO]->(manager:Employee)
RETURN manager.name

The SQL/PGQ is similar, using ASCII art with () for vertices and -[]-> for edges, but closer to SQL:


postgres=# select * from graph_table (
    "emp_dept_graph"
    match
        (e is "employee" where e."name" = 'JONES')
        -[is "reports_to"]->
        (m is "employee")
    columns (
        m."name" as "manager_name"
    )
);

 manager_name
--------------
 OATES

(1 row)

This query is conceptually equivalent to:

select m."name" as "manager_name"
 from "employee" e
 join "employee" m on m."empno" = e."mgr"
 where e."name" = 'JONES'
;

but expressed as a graph pattern.

Both have the same execution plan except for the alias names:

                                QUERY PLAN
---------------------------------------------------------------------------
 Hash Join  (cost=1.22..2.47 rows=1 width=6)
   Hash Cond: (employee_1.empno = employee.mgr)
   ->  Seq Scan on employee employee_1  (cost=0.00..1.17 rows=17 width=10)
   ->  Hash  (cost=1.21..1.21 rows=1 width=8)
         ->  Seq Scan on employee  (cost=0.00..1.21 rows=1 width=8)
               Filter: (name = 'JONES'::text)

With such a simple query, SQL does not look particularly complex. However, it already reveals a limitation of the relational model where relationships are not first-class citizens. Rather than navigating the domain model through named associations such as "reports to" or "works in", SQL developers must understand the physical schema and determine, for each query, which columns can be combined through joins.

The relational model was deliberately designed to do the opposite of graph or document databases, with their network or hierarchical models: relationships are represented indirectly through business values rather than explicit links, allowing entities to be stored independently of pointers, access patterns, or traversal directions.

Foreign key constraints in SQL add information about those relationships and enforce referential integrity during inserts, updates, and deletes. However, they do not provide a queryable graph structure or predefined navigation paths. Queries ignore the foreign keys and still need to specify how relationships are traversed with join predicates.

SQL/PGQ restores that semantic layer by defining a graph model on top of relational data. Associations from the domain model become explicit graph edges, allowing queries to traverse relationships by their business meaning rather than by manually assembling joins between columns.

Combining relationships

Graph patterns become more expressive when traversing multiple relationships. To retrieve both Jones's manager and that manager's department:

postgres=# select * from graph_table (
    "emp_dept_graph"
    match
        (e is "employee" where e."name" = 'JONES')
        -[is "reports_to"]->
        (m is "employee")
        -[is "works_in"]->
        (d is "department")
    columns (
        m.name as "manager_name",
        d.name as "department_name"
    )
);

 manager_name | department_name
--------------+-----------------
 OATES        | Administration

(1 row)

The equivalent relational query would require two joins and a self-join.

Traversing hierarchies

The EMP table is famous for hierarchical queries. To find the manager's manager of JAKES:

postgres=# select * from graph_table (
    "emp_dept_graph"
    match
        (e is "employee" where e."name" = 'JAKES')
        -[is "reports_to"]->()-[is "reports_to"]->
        (m is "employee")
    columns (
        m."name" as "manager_name"
    )
);

 manager_name
--------------
 JONES

(1 row)

SQL/PGQ standard allows the -[is "reports_to"]->{2} syntax where the {2} quantifier specifies a traversal depth of exactly two relationships, but this is not supported in PostgreSQL 19 so I used -[is "reports_to"]->()-[is "reports_to"]-> instead.

PostgreSQL 19 implements the core SQL/PGQ functionality needed to define property graphs over relational tables and query them with graph pattern matching. However, many advanced graph features are not yet supported, including path variables, shortest-path search, variable-length traversals, path analytics, and advanced path pattern expressions. The list is in sqlfeatures.txt.

Relational and graph models

The EMP/DEPT example has been used for decades to explain relational modeling, self-referencing relationships, and hierarchical queries. SQL/PGQ introduces a new way to query the same data, but it does not change how the data is stored. Tables, primary keys, foreign keys, indexes, constraints, the optimizer, and storage structures remain unchanged.

This is an interesting evolution of database history. The relational model was created in part to move away from the navigational nature of hierarchical and network databases, where applications followed predefined links and access paths. By representing relationships through values rather than pointers, relational databases achieved data independence: the schema describes the data without embedding specific navigation patterns or application use cases.

SQL/PGQ does not reverse that design choice. Relational tables remain the source of truth, and relationships are still represented through values rather than pointers. Instead, it adds a semantic layer that maps the relational schema to the domain model. Relationships that exist implicitly through foreign keys and join predicates become explicit graph edges such as reports_to or works_in. It is like an in-database Object-Relational Mapper (ORM) for graph use cases.

The benefit is primarily developer experience. Applications can navigate the domain model through graph patterns rather than reconstructing relationships from foreign keys and joins in every query. The relational model keeps its flexibility and data independence, while SQL/PGQ provides a more natural way to express traversals and relationship-oriented queries.

PostgreSQL 19 brings this graph abstraction directly into standard SQL. Graph queries run on the same tables, indexes, optimizer, and execution engine as traditional relational queries, without introducing a separate graph storage model.

In that sense, SQL/PGQ is not a return to hierarchical or network databases. The relational model remains the foundation. SQL/PGQ simply adds a semantic mapping between relational structures and business relationships, making graph-oriented queries easier to express while preserving the data independence that made relational databases successful in the first place.

Cypher graph queries on PostgreSQL with Apache AGE

Franck Pachot — Tue, 21 Jul 2026 14:59:14 +0000

The cover image above compares two representations of the same data model, separated by more than 45 years: one shows a PostgreSQL extension for Visual Studio Code visualizing a graph with Apache AGE, and the other displays the employee hierarchy from the Oracle 2.3 User Guide. Hierarchical and graph traversal queries have long been a topic in relational databases. Early SQL, or SEQUEL, used employee-department and manager relationships, which led to the need for graph traversal syntax beyond self-joins. The first commercial RDBMS had a CONNECT BY syntax (see Oracle 2.3 User Guide), later replaced by recursive WITH clauses in the SQL standard. PostgreSQL 19 adds SQL/PGQ support for property graph queries. Meanwhile, NoSQL graph databases like Neo4j, with Cypher, have gained popularity, and this capability is now accessible in PostgreSQL via the Apache AGE extension.

Apache AGE on PostgreSQL

I've built a small example based on the legendary EMP-DEPT schema from 45 years ago, running on Azure, because, according to https://www.pgextensions.org/, it is the only managed service that supports it:

I'm using HorizonDB, the PostgreSQL-compatible managed service for enterprise workloads, which is currently in preview (but you can also use the free ghcr.io/pglayers/pglayers-azure:17 image from pglayers). I've enabled Apache AGE by adding it to the azure.extensions list:

postgres=> \dconfig azure.extensions

                List of configuration parameters

    Parameter     |                    Value
------------------+----------------------------------------------
 azure.extensions | pg_diskann,vector,pg_textsearch,azure_ai,age

postgres=> \dconfig server_version

                List of configuration parameters
   Parameter    |                     Value
----------------+-----------------------------------------------
 server_version | 17.9 (Azure HorizonDB (81895d42565)(release))

The example is deliberately straightforward. Besides the departments reference, it includes the employee entity, and one relationship: each employee's immediate manager. In the relational model, both are stored in the same table, with the manager relationship represented through a self-referencing foreign key. SQL handles such relationships using joins, with CONNECT BY or WITH RECURSIVE for graph structures. Apache AGE represents relationships as graph edges and supports openCypher syntax, significantly simplifying complex graph queries.

Graph model

Property graph databases model the same information differently than relational databases. Entities are represented as nodes (aka vertices), and relationships (aka edges) connect them. Rather than reconstructing relationships through joins, relationships are stored explicitly as graph edges and traversed using graph patterns.

I install the extension and set the search path to include ag_catalog with the Apache AGE functions and datatypes:


create extension if not exists age;

set search_path = "$user", public, ag_catalog;

I generate a graph, which is equivalent to a schema:


select create_graph('emp_dept_graph');

PostgreSQL can now execute Cypher queries against this graph by calling cypher() with the graph name and query, returning an agtype result.

Graph nodes (vertices `()` )

In Apache AGE, all Cypher queries are in dollar-quoted strings. The following creates the nodes for the departments:


select * from cypher('emp_dept_graph', $openCypher$
CREATE
(:Department { deptno:10, name:"Administration", loc:"New York" }),
(:Department { deptno:20, name:"Research",       loc:"San Francisco" }),
(:Department { deptno:30, name:"Sales",          loc:"Chicago" }),
(:Department { deptno:40, name:"Operations",     loc:"Boston" })
$openCypher$) AS (result agtype);

The parentheses () draw a node (think of an ASCII art version of a graph), (:Department) adds a label to it, and the JSON-like { deptno:40, name:'Operations', loc:'Boston'} adds properties as key-value pairs, similar to JSON.

I do the same to create the employees, without specifying their department, only their own properties:


select * from cypher('emp_dept_graph', $openCypher$
CREATE
(:Employee {empno:7839,name:"OATES",job:"President",sal:5000}),
(:Employee {empno:7566,name:"JONES",job:"Manager",sal:2975}),
(:Employee {empno:7698,name:"BLAKE",job:"Manager",sal:2850}),
(:Employee {empno:7782,name:"CLARK",job:"Manager",sal:2450}),
(:Employee {empno:7788,name:"SCOTT",job:"Analyst",sal:3000}),
(:Employee {empno:7902,name:"FORD",job:"Analyst",sal:3000}),
(:Employee {empno:7999,name:"WILSON",job:"Analyst",sal:2800}),
(:Employee {empno:7876,name:"ADAMS",job:"Clerk",sal:1100}),
(:Employee {empno:7369,name:"SMITH",job:"Clerk",sal:800}),
(:Employee {empno:8000,name:"JAKES",job:"Clerk",sal:1000}),
(:Employee {empno:7499,name:"ALLEN",job:"Salesman",sal:1600}),
(:Employee {empno:7521,name:"WARD",job:"Salesman",sal:1250}),
(:Employee {empno:7654,name:"MARTIN",job:"Salesman",sal:1250}),
(:Employee {empno:7844,name:"TURNER",job:"Salesman",sal:1500}),
(:Employee {empno:7900,name:"JAMES",job:"Clerk",sal:950}),
(:Employee {empno:8001,name:"CARTER",job:"Salesman",sal:1400}),
(:Employee {empno:7934,name:"MILLER",job:"Clerk",sal:1300})
$openCypher$) AS (result agtype);

The nodes are stored with their properties. Now I can define the relationships to form a graph.

Graph relationships (edges `-[]->`)

I'll add the relationship to show where an employee works in a department, and their position in the hierarchy.

In a SQL model, employees reference their department via a DEPTNO foreign key and their manager through an MGR foreign key, with the manager being another employee. In relational databases, relationships are represented by key values rather than direct pointers between rows, and entities are independent of the navigation between them. Instead, the department number and manager's employee number are attributes of the employee entity, and relationships are established during queries with joins. Simple many-to-one relationships are represented by foreign keys. However, more complex relationships, such as many-to-many relationships or relationships with their own attributes, require an additional association table.

In a graph database, relationships are at the core of the model. The nodes are the entities and the edges are the relationships. In ASCII art, this can be described as: (:Employee)-[:WORKS_IN]->(:Department).

In SQL, relationships are queried using joins, and prior to the JOIN syntax, they were expressed as a Cartesian product in the FROM clause, with a WHERE clause to filter the desired combinations. A similar approach applies here. To establish the employee-department relationship, I define the set of (:Employee), (:Department) pairs that represent where each employee works and create a -[:WORKS_IN]-> edge between them.


select * from cypher('emp_dept_graph', $openCypher$
MATCH (e:Employee),(d:Department)
WHERE
       (e.empno=7839 AND d.deptno=10)
    OR (e.empno=7782 AND d.deptno=10)
    OR (e.empno=7934 AND d.deptno=10)
    OR (e.empno=7566 AND d.deptno=20)
    OR (e.empno=7788 AND d.deptno=20)
    OR (e.empno=7902 AND d.deptno=20)
    OR (e.empno=7369 AND d.deptno=20)
    OR (e.empno=7876 AND d.deptno=20)
    OR (e.empno=7999 AND d.deptno=20)
    OR (e.empno=8000 AND d.deptno=20)
    OR (e.empno=7698 AND d.deptno=30)
    OR (e.empno=7499 AND d.deptno=30)
    OR (e.empno=7521 AND d.deptno=30)
    OR (e.empno=7654 AND d.deptno=30)
    OR (e.empno=7844 AND d.deptno=30)
    OR (e.empno=7900 AND d.deptno=30)
    OR (e.empno=8001 AND d.deptno=30)
CREATE (e)-[:WORKS_IN]->(d)
$openCypher$) AS (result agtype);

Here is a similar query to declare the employee-manager relationship as (:Employee)-[:REPORTS_TO ]->(:Employee):


select * from cypher('emp_dept_graph', $openCypher$
MATCH (e:Employee),(m:Employee)
WHERE
       (e.empno=7566 AND m.empno=7839)
    OR (e.empno=7698 AND m.empno=7839)
    OR (e.empno=7782 AND m.empno=7839)
    OR (e.empno=7788 AND m.empno=7566)
    OR (e.empno=7902 AND m.empno=7566)
    OR (e.empno=7999 AND m.empno=7566)
    OR (e.empno=7876 AND m.empno=7788)
    OR (e.empno=7369 AND m.empno=7902)
    OR (e.empno=8000 AND m.empno=7999)
    OR (e.empno=7499 AND m.empno=7698)
    OR (e.empno=7521 AND m.empno=7698)
    OR (e.empno=7654 AND m.empno=7698)
    OR (e.empno=7844 AND m.empno=7698)
    OR (e.empno=7900 AND m.empno=7698)
    OR (e.empno=8001 AND m.empno=7698) 
    OR (e.empno=7934 AND m.empno=7782)
CREATE (e)-[:REPORTS_TO { manager_level: 1 }]->(m)
$openCypher$ ) AS (result agtype);

To show an example of a relationship property, I've added the manager level using Cypher map syntax, which looks like JSON.

AGE Internals

Apache AGE stores metadata in two catalog tables:

postgres=> select * from ag_catalog.ag_graph
;

 graphid |      name      |   namespace
---------+----------------+----------------
    26064 | emp_dept_graph | emp_dept_graph

(1 row)

postgres=> select * from ag_catalog.ag_label
;

       name       | graph | id | kind |            relation             |        seq_name
------------------+-------+----+------+---------------------------------+-------------------------
 _ag_label_vertex | 26064 |  1 | v    | emp_dept_graph._ag_label_vertex | _ag_label_vertex_id_seq
 _ag_label_edge   | 26064 |  2 | e    | emp_dept_graph._ag_label_edge   | _ag_label_edge_id_seq
 Department       | 26064 |  3 | v    | emp_dept_graph."Department"     | Department_id_seq
 Employee         | 26064 |  4 | v    | emp_dept_graph."Employee"       | Employee_id_seq
 WORKS_IN         | 26064 |  5 | e    | emp_dept_graph."WORKS_IN"       | WORKS_IN_id_seq
 REPORTS_TO       | 26064 |  6 | e    | emp_dept_graph."REPORTS_TO"     | REPORTS_TO_id_seq

(6 rows)

The data is stored in nodes and edges tables:

postgres=> \d emp_dept_graph."Department"
                                                                        Table "emp_dept_graph.Department"
   Column   |  Type   | Collation | Nullable |                                                              Default
------------+---------+-----------+----------+-----------------------------------------------------------------------------------------------------------------------------------
 id         | graphid |           | not null | _graphid(_label_id('emp_dept_graph'::name, 'Department'::name)::integer, nextval('emp_dept_graph."Department_id_seq"'::regclass))
 properties | agtype  |           | not null | agtype_build_map()
Indexes:
    "Department_pkey" PRIMARY KEY, btree (id)
Inherits: emp_dept_graph._ag_label_vertex

postgres=> select * from emp_dept_graph."Department"
;
       id        |                                         properties
-----------------+---------------------------------------------------------------------------------------------
 844424930131969 | {"loc": "New York", "name": "Administration", "deptno": 10, "disp_label": "Administration"}
 844424930131970 | {"loc": "San Francisco", "name": "Research", "deptno": 20, "disp_label": "Research"}
 844424930131971 | {"loc": "Chicago", "name": "Sales", "deptno": 30, "disp_label": "Sales"}
 844424930131972 | {"loc": "Boston", "name": "Operations", "deptno": 40, "disp_label": "Operations"}

(4 rows)

postgres=> \d emp_dept_graph."WORKS_IN"
                                                                       Table "emp_dept_graph.WORKS_IN"
   Column   |  Type   | Collation | Nullable |                                                            Default
------------+---------+-----------+----------+-------------------------------------------------------------------------------------------------------------------------------
 id         | graphid |           | not null | _graphid(_label_id('emp_dept_graph'::name, 'WORKS_IN'::name)::integer, nextval('emp_dept_graph."WORKS_IN_id_seq"'::regclass))
 start_id   | graphid |           | not null |
 end_id     | graphid |           | not null |
 properties | agtype  |           | not null | agtype_build_map()
Indexes:
    "WORKS_IN_end_id_idx" btree (end_id)
    "WORKS_IN_start_id_idx" btree (start_id)
Inherits: emp_dept_graph._ag_label_edge

postgres=> select * from emp_dept_graph."WORKS_IN"
;
        id        |     start_id     |     end_id      | properties
------------------+------------------+-----------------+------------
 1407374883553281 | 1125899906842625 | 844424930131969 | {}
 1407374883553282 | 1125899906842626 | 844424930131970 | {}
 1407374883553283 | 1125899906842627 | 844424930131971 | {}
 1407374883553284 | 1125899906842628 | 844424930131969 | {}
 1407374883553285 | 1125899906842629 | 844424930131970 | {}
 1407374883553286 | 1125899906842630 | 844424930131970 | {}
 1407374883553287 | 1125899906842631 | 844424930131970 | {}
 1407374883553288 | 1125899906842632 | 844424930131970 | {}
 1407374883553289 | 1125899906842633 | 844424930131970 | {}
 1407374883553290 | 1125899906842634 | 844424930131970 | {}
 1407374883553291 | 1125899906842635 | 844424930131971 | {}
 1407374883553292 | 1125899906842636 | 844424930131971 | {}
 1407374883553293 | 1125899906842637 | 844424930131971 | {}
 1407374883553294 | 1125899906842638 | 844424930131971 | {}
 1407374883553295 | 1125899906842639 | 844424930131971 | {}
 1407374883553296 | 1125899906842640 | 844424930131971 | {}
 1407374883553297 | 1125899906842641 | 844424930131969 | {}

(17 rows)

Looking at the table definitions, I can prevent duplicates by creating the following UNIQUE indexes:


CREATE UNIQUE INDEX department_deptno_uix
ON emp_dept_graph."Department"
(
    (agtype_access_operator(properties, '"deptno"'))
);

CREATE UNIQUE INDEX employee_empno_uix
ON emp_dept_graph."Employee"
(
    (agtype_access_operator(properties, '"empno"'))
);

CREATE UNIQUE INDEX works_in_uix
ON emp_dept_graph."WORKS_IN"(start_id,end_id);

CREATE UNIQUE INDEX reports_to_uix
ON emp_dept_graph."REPORTS_TO"(start_id,end_id);

I can also create a GIN index on the properties to accelerate searches by a property value:

CREATE INDEX employee_properties_gin
ON emp_dept_graph."Employee"
USING gin (properties);

Let's examine some queries and their corresponding translations on internal tables and indexes.

Query

I have already used the MATCH clause to identify the combinations of employees and departments to create the edges.

To find the manager of Jones (:Employee {name:"JONES"}), I match the relationship with a variable -[:REPORTS_TO]->(manager) and return manager.name property.


postgres=> SELECT * FROM cypher('emp_dept_graph', $openCypher$
MATCH (:Employee {name:"JONES"})-[:REPORTS_TO]->(manager)
RETURN manager.name
$openCypher$) AS (
  manager agtype
);

 manager
---------
 "OATES"

(1 row)

I can add the department of the manager to the result by navigating through -[:WORKS_IN]->:

postgres=> SELECT *
FROM cypher('emp_dept_graph', $openCypher$
MATCH (:Employee {name:"JONES"})
      -[:REPORTS_TO]->(manager)
      -[:WORKS_IN]->(department)
RETURN manager.name, department.name
$openCypher$) AS (
  manager agtype,
  department agtype
);

 manager |    department
---------+------------------
 "OATES" | "Administration"

(1 row)

I can get the manager's manager with -[:REPORTS_TO]->()-[:REPORTS_TO]->(manager) but also with -[:REPORTS_TO*2]->:


postgres=> SELECT *
FROM cypher('emp_dept_graph', $openCypher$
MATCH (:Employee {name:"JAKES"})-[:REPORTS_TO*2]->(manager)
RETURN manager.name
$openCypher$) AS (
  manager agtype
);

 manager
---------
 "JONES"
(1 row)

I can also get all managers higher in the hierarchy with [:REPORTS_TO*2..] where *2.. defines the number of hops, from 2 to an unbounded upper limit.

Visualization

The following query returns all -[REPORTS_TO]-> relationships without specifying a start or an end. It represents the company's management hierarchy:

postgres=> SELECT * FROM cypher('emp_dept_graph', $openCypher$
  MATCH (e)-[r:REPORTS_TO]->(m)
  RETURN e,r,m
$openCypher$ ) AS (
  employee   agtype,
  reports_to agtype,
  manager    agtype
);
                                                                            employee                                                                            |                                                                     reports_to                                                                      |                                                                            manager
----------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"id": 1125899906842626, "label": "Employee", "properties": {"job": "Manager", "sal": 2975, "name": "JONES", "empno": 7566, "disp_label": "JONES"}}::vertex    | {"id": 1688849860263937, "label": "REPORTS_TO", "end_id": 1125899906842625, "start_id": 1125899906842626, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842625, "label": "Employee", "properties": {"job": "President", "sal": 5000, "name": "OATES", "empno": 7839, "disp_label": "OATES"}}::vertex
 {"id": 1125899906842627, "label": "Employee", "properties": {"job": "Manager", "sal": 2850, "name": "BLAKE", "empno": 7698, "disp_label": "BLAKE"}}::vertex    | {"id": 1688849860263938, "label": "REPORTS_TO", "end_id": 1125899906842625, "start_id": 1125899906842627, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842625, "label": "Employee", "properties": {"job": "President", "sal": 5000, "name": "OATES", "empno": 7839, "disp_label": "OATES"}}::vertex
 {"id": 1125899906842628, "label": "Employee", "properties": {"job": "Manager", "sal": 2450, "name": "CLARK", "empno": 7782, "disp_label": "CLARK"}}::vertex    | {"id": 1688849860263939, "label": "REPORTS_TO", "end_id": 1125899906842625, "start_id": 1125899906842628, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842625, "label": "Employee", "properties": {"job": "President", "sal": 5000, "name": "OATES", "empno": 7839, "disp_label": "OATES"}}::vertex
 {"id": 1125899906842629, "label": "Employee", "properties": {"job": "Analyst", "sal": 3000, "name": "SCOTT", "empno": 7788, "disp_label": "SCOTT"}}::vertex    | {"id": 1688849860263940, "label": "REPORTS_TO", "end_id": 1125899906842626, "start_id": 1125899906842629, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842626, "label": "Employee", "properties": {"job": "Manager", "sal": 2975, "name": "JONES", "empno": 7566, "disp_label": "JONES"}}::vertex
 {"id": 1125899906842630, "label": "Employee", "properties": {"job": "Analyst", "sal": 3000, "name": "FORD", "empno": 7902, "disp_label": "FORD"}}::vertex      | {"id": 1688849860263941, "label": "REPORTS_TO", "end_id": 1125899906842626, "start_id": 1125899906842630, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842626, "label": "Employee", "properties": {"job": "Manager", "sal": 2975, "name": "JONES", "empno": 7566, "disp_label": "JONES"}}::vertex
 {"id": 1125899906842631, "label": "Employee", "properties": {"job": "Analyst", "sal": 2800, "name": "WILSON", "empno": 7999, "disp_label": "WILSON"}}::vertex  | {"id": 1688849860263942, "label": "REPORTS_TO", "end_id": 1125899906842626, "start_id": 1125899906842631, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842626, "label": "Employee", "properties": {"job": "Manager", "sal": 2975, "name": "JONES", "empno": 7566, "disp_label": "JONES"}}::vertex
 {"id": 1125899906842635, "label": "Employee", "properties": {"job": "Salesman", "sal": 1600, "name": "ALLEN", "empno": 7499, "disp_label": "ALLEN"}}::vertex   | {"id": 1688849860263946, "label": "REPORTS_TO", "end_id": 1125899906842627, "start_id": 1125899906842635, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842627, "label": "Employee", "properties": {"job": "Manager", "sal": 2850, "name": "BLAKE", "empno": 7698, "disp_label": "BLAKE"}}::vertex
 {"id": 1125899906842636, "label": "Employee", "properties": {"job": "Salesman", "sal": 1250, "name": "WARD", "empno": 7521, "disp_label": "WARD"}}::vertex     | {"id": 1688849860263947, "label": "REPORTS_TO", "end_id": 1125899906842627, "start_id": 1125899906842636, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842627, "label": "Employee", "properties": {"job": "Manager", "sal": 2850, "name": "BLAKE", "empno": 7698, "disp_label": "BLAKE"}}::vertex
 {"id": 1125899906842637, "label": "Employee", "properties": {"job": "Salesman", "sal": 1250, "name": "MARTIN", "empno": 7654, "disp_label": "MARTIN"}}::vertex | {"id": 1688849860263948, "label": "REPORTS_TO", "end_id": 1125899906842627, "start_id": 1125899906842637, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842627, "label": "Employee", "properties": {"job": "Manager", "sal": 2850, "name": "BLAKE", "empno": 7698, "disp_label": "BLAKE"}}::vertex
 {"id": 1125899906842638, "label": "Employee", "properties": {"job": "Salesman", "sal": 1500, "name": "TURNER", "empno": 7844, "disp_label": "TURNER"}}::vertex | {"id": 1688849860263949, "label": "REPORTS_TO", "end_id": 1125899906842627, "start_id": 1125899906842638, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842627, "label": "Employee", "properties": {"job": "Manager", "sal": 2850, "name": "BLAKE", "empno": 7698, "disp_label": "BLAKE"}}::vertex
 {"id": 1125899906842639, "label": "Employee", "properties": {"job": "Clerk", "sal": 950, "name": "JAMES", "empno": 7900, "disp_label": "JAMES"}}::vertex       | {"id": 1688849860263950, "label": "REPORTS_TO", "end_id": 1125899906842627, "start_id": 1125899906842639, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842627, "label": "Employee", "properties": {"job": "Manager", "sal": 2850, "name": "BLAKE", "empno": 7698, "disp_label": "BLAKE"}}::vertex
 {"id": 1125899906842640, "label": "Employee", "properties": {"job": "Salesman", "sal": 1400, "name": "CARTER", "empno": 8001, "disp_label": "CARTER"}}::vertex | {"id": 1688849860263951, "label": "REPORTS_TO", "end_id": 1125899906842627, "start_id": 1125899906842640, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842627, "label": "Employee", "properties": {"job": "Manager", "sal": 2850, "name": "BLAKE", "empno": 7698, "disp_label": "BLAKE"}}::vertex
 {"id": 1125899906842641, "label": "Employee", "properties": {"job": "Clerk", "sal": 1300, "name": "MILLER", "empno": 7934, "disp_label": "MILLER"}}::vertex    | {"id": 1688849860263952, "label": "REPORTS_TO", "end_id": 1125899906842628, "start_id": 1125899906842641, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842628, "label": "Employee", "properties": {"job": "Manager", "sal": 2450, "name": "CLARK", "empno": 7782, "disp_label": "CLARK"}}::vertex
 {"id": 1125899906842632, "label": "Employee", "properties": {"job": "Clerk", "sal": 1100, "name": "ADAMS", "empno": 7876, "disp_label": "ADAMS"}}::vertex      | {"id": 1688849860263943, "label": "REPORTS_TO", "end_id": 1125899906842629, "start_id": 1125899906842632, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842629, "label": "Employee", "properties": {"job": "Analyst", "sal": 3000, "name": "SCOTT", "empno": 7788, "disp_label": "SCOTT"}}::vertex
 {"id": 1125899906842633, "label": "Employee", "properties": {"job": "Clerk", "sal": 800, "name": "SMITH", "empno": 7369, "disp_label": "SMITH"}}::vertex       | {"id": 1688849860263944, "label": "REPORTS_TO", "end_id": 1125899906842630, "start_id": 1125899906842633, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842630, "label": "Employee", "properties": {"job": "Analyst", "sal": 3000, "name": "FORD", "empno": 7902, "disp_label": "FORD"}}::vertex
 {"id": 1125899906842634, "label": "Employee", "properties": {"job": "Clerk", "sal": 1000, "name": "JAKES", "empno": 8000, "disp_label": "JAKES"}}::vertex      | {"id": 1688849860263945, "label": "REPORTS_TO", "end_id": 1125899906842631, "start_id": 1125899906842634, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842631, "label": "Employee", "properties": {"job": "Analyst", "sal": 2800, "name": "WILSON", "empno": 7999, "disp_label": "WILSON"}}::vertex

(16 rows)

The output looks like JSON but is actually agtype, containing internal identifiers, labels, and properties. This result can be visualized in VS Code using the Microsoft extension for PostgreSQL. After running the same query, you can click on the 🫧 "Switch to Graph" button on the right side of the tabular result to view it as:

Here is more information about this VS Code extension: https://marketplace.visualstudio.com/items?itemName=ms-ossdata.vscode-pgsql

Execution Plan

Using EXPLAIN (ANALYZE, BUFFERS), I can examine the PostgreSQL execution plan that reads the edges and nodes:

Plan:

                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Merge Join (actual time=0.046..0.094 rows=13 loops=1)
   Merge Cond: (m.id = r.end_id)
   Buffers: shared hit=46
   ->  Merge Append (actual time=0.013..0.016 rows=11 loops=1)
         Sort Key: m.id
         Buffers: shared hit=5
         ->  Index Scan using _ag_label_vertex_pkey on _ag_label_vertex m_1 (actual time=0.003..0.003 rows=0 loops=1)
               Buffers: shared hit=1
         ->  Index Scan using "Department_pkey" on "Department" m_2 (actual time=0.004..0.005 rows=4 loops=1)
               Buffers: shared hit=2
         ->  Index Scan using "Employee_pkey" on "Employee" m_3 (actual time=0.004..0.005 rows=7 loops=1)
               Buffers: shared hit=2
   ->  Materialize (actual time=0.021..0.051 rows=13 loops=1)
         Buffers: shared hit=41
         ->  Nested Loop (actual time=0.018..0.043 rows=13 loops=1)
               Buffers: shared hit=41
               ->  Index Scan using "REPORTS_TO_end_id_idx" on "REPORTS_TO" r (actual time=0.006..0.008 rows=13 loops=1)
                     Buffers: shared hit=2
               ->  Append (actual time=0.002..0.002 rows=1 loops=13)
                     Buffers: shared hit=39
                     ->  Seq Scan on _ag_label_vertex e_1 (actual time=0.000..0.000 rows=0 loops=13)
                           Filter: (id = r.start_id)
                     ->  Index Scan using "Department_pkey" on "Department" e_2 (actual time=0.000..0.000 rows=0 loops=13)
                           Index Cond: (id = r.start_id)
                           Buffers: shared hit=13
                     ->  Index Scan using "Employee_pkey" on "Employee" e_3 (actual time=0.001..0.001 rows=1 loops=13)
                           Index Cond: (id = r.start_id)
                           Buffers: shared hit=26
 Planning:
   Buffers: shared hit=6
 Planning Time: 0.293 ms
 Execution Time: 0.142 ms

By converting Cypher queries into SQL, Apache AGE enables the PostgreSQL query planner to select optimal access methods.

The GIN index I created can be used for equality predicates on property values:


postgres=> EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM cypher('emp_dept_graph', $openCypher$
  MATCH (e:Employee {job:"Salesman"})
  RETURN e.name, e.job, e.sal
  ORDER BY e.name
$openCypher$) AS (
  employee agtype,
  job      agtype,
  salary   agtype
);
                                                                    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=8.99..8.99 rows=1 width=96) (actual time=0.057..0.058 rows=5 loops=1)
   Sort Key: (agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(e.id, _label_name('26064'::oid, e.id), e.properties), '"name"'::agtype]))
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=4
   ->  Bitmap Heap Scan on "Employee" e  (cost=6.94..8.98 rows=1 width=96) (actual time=0.032..0.048 rows=5 loops=1)
         Recheck Cond: (properties @> '{"job": "Salesman"}'::agtype)
         Heap Blocks: exact=1
         Buffers: shared hit=4
         ->  Bitmap Index Scan on employee_properties_gin  (cost=0.00..6.94 rows=1 width=0) (actual time=0.010..0.010 rows=5 loops=1)
               Index Cond: (properties @> '{"job": "Salesman"}'::agtype)
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.138 ms
 Execution Time: 0.140 ms
(15 rows)

The main benefit of using PostgreSQL as a multi-model database is that extensions introduce new query APIs and data models, yet you don't need to learn the intricacies of a different database when troubleshooting performance issues.

Multiple relationships

The previous query was limited to one relationship (e)-[r:REPORTS_TO]->(m), but I can query all relationships with (e)-[r]->(m):

SELECT *
FROM cypher('emp_dept_graph', $openCypher$
MATCH (e)-[r]->(m)
RETURN e,r,m
$openCypher$ ) AS (
  e agtype,
  r agtype,
  m agtype
);

The graphical visualization shows all relationships as edges and nodes of different colors:

Conclusion

The EMP/DEPT example has been used for decades to explain relational modeling, self-referencing relationships, and hierarchical queries. The same data can also be represented as a property graph, where relationships become first-class graph elements rather than being represented through foreign-key values stored in columns.

Apache AGE exposes this model as an extension to PostgreSQL. Nodes, edges, properties, indexes, execution plans, and storage remain PostgreSQL objects. Cypher queries are translated into SQL execution plans, allowing graph traversals to benefit from the PostgreSQL optimizer and indexing infrastructure.

DocumentDB on YugabyteDB

Franck Pachot — Fri, 17 Jul 2026 21:00:14 +0000

The DocumentDB extension, providing MongoDB compatibility for PostgreSQL, is available in preview in YugabyteDB 2026.1, with some limitations, such as the absence of secondary indexes and lack of support for ARM processors. Still, it's interesting to see how it works.

I've launched a Docker container from the image containing version 2026.1.0.0, build 118:


docker run --rm -it -p 27017:27017 -p 15433:15433 \
yugabytedb/yugabyte:latest bash

I started one node, setting the necessary flags:


yugabyted start \
 --master_flags="allowed_preview_flags_csv=ysql_enable_documentdb,ysql_enable_documentdb=true,enable_pg_cron=true"  \
 --tserver_flags="allowed_preview_flags_csv=ysql_enable_documentdb,ysql_enable_documentdb=true,enable_pg_cron=true" \
--ui=true

The DocumentDB extension also offers a MongoDB-compatible endpoint. However, to observe the internals, I used the PostgreSQL client:


ysqlsh -h $HOSTNAME

From the PostgreSQL client, I used the DocumentDB API to run MongoDB-compatible commands from SQL. I imported a collection with ten thousand documents, each including a nested array of one hundred items:


create extension if not exists documentdb cascade;

select documentdb_api.drop_collection    ('db','coll1');

select documentdb_api.create_collection  ('db','coll1');
with docs(document) as (select
    json_build_object(
        '_id', n,
        'field1', n%100,
        'field2', md5(random()::text),
        'field3', md5(random()::text),
        'field4', md5(random()::text),
        'field5', md5(random()::text),
        'array', (
            select json_agg(child.id+ case when n%3=0 then 0 else random() end)
            from generate_series(1, 1e2) AS child(id)
        )
    ) from generate_series(1, 1e5) n
)
select count(documentdb_api.insert_one   ('db','coll1',
 document::text::documentdb_core.bson
)) from docs;
;

I checked a sample of data:


set documentdb_core.bsonUseEJson to true;

\pset pager off

select document from documentdb_api_catalog.bson_aggregation_pipeline(
    'db', '{"aggregate": "coll1", "pipeline": [
      {"$limit": 2 }
    ], "cursor": {}}'::documentdb_core.bson
);

Result:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               document                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 { "_id" : { "$numberInt" : "78047" }, "field1" : { "$numberInt" : "47" }, "field2" : "61160c5889651c7aeb9b53c9e8c16874", "field3" : "a5643ebcf7b4cb52ce5606347a48159d", "field4" : "1920dadda82764003c8666396927d184", "field5" : "38981fb4fe2874a16e71adb91eaf80b7", "array" : [ { "$numberDouble" : "1.6680338007661099642" }, { "$numberDouble" : "2.0321660675583723688" }, { "$numberDouble" : "3.7248612825324078912" }, { "$numberDouble" : "4.6682151188376419526" }, { "$numberDouble" : "5.352836859518445678" }, { "$numberDouble" : "6.1549238121424405534" }, { "$numberDouble" : "7.5698744025959001647" }, { "$numberDouble" : "8.3627292359089526741" }, { "$numberDouble" : "9.7284070730559299989" }, { "$numberDouble" : "10.648505386329139455" }, { "$numberDouble" : "11.819375264988172702" }, { "$numberDouble" : "12.564692810410857504" }, { "$numberDouble" : "13.578234292227129743" }, { "$numberDouble" : "14.840940698922509" }, { "$numberDouble" : "15.000110832002331307" }, { "$numberDouble" : "16.358619841052682631" }, { "$numberDouble" : "17.114466577365092803" }, { "$numberDouble" : "18.453576775946736177" }, { "$numberDouble" : "19.612164960288463789" }, { "$numberDouble" : "20.763510570791428478" }, { "$numberDouble" : "21.072906780595499043" }, { "$numberDouble" : "22.302558940939842813" }, { "$numberDouble" : "23.028762426311956801" }, { "$numberDouble" : "24.977457545570718622" }, { "$numberDouble" : "25.168495224042882086" }, { "$numberDouble" : "26.683268805530829582" }, { "$numberDouble" : "27.603359814890115587" }, { "$numberDouble" : "28.878206328994565411" }, { "$numberDouble" : "29.721073180897786159" }, { "$numberDouble" : "30.626948384420241922" }, { "$numberDouble" : "31.671570586699115069" }, { "$numberDouble" : "32.662353414214038594" }, { "$numberDouble" : "33.46460769319755002" }, { "$numberDouble" : "34.940574677532538317" }, { "$numberDouble" : "35.970141769064831294" }, { "$numberDouble" : "36.179330236215683669" }, { "$numberDouble" : "37.600489143561993899" }, { "$numberDouble" : "38.84836254286827284" }, { "$numberDouble" : "39.212520619284028101" }, { "$numberDouble" : "40.552350139480068947" }, { "$numberDouble" : "41.534399323092017653" }, { "$numberDouble" : "42.675192781144495768" }, { "$numberDouble" : "43.897435440034712428" }, { "$numberDouble" : "44.643362479639151275" }, { "$numberDouble" : "45.079069764447424973" }, { "$numberDouble" : "46.571893792280704361" }, { "$numberDouble" : "47.247632193766989417" }, { "$numberDouble" : "48.490043046330811194" }, { "$numberDouble" : "49.453768298556425975" }, { "$numberDouble" : "50.918392174574123032" }, { "$numberDouble" : "51.920252310666860751" }, { "$numberDouble" : "52.939591943997093892" }, { "$numberDouble" : "53.620526333881137759" }, { "$numberDouble" : "54.692199233976516837" }, { "$numberDouble" : "55.398818854086997021" }, { "$numberDouble" : "56.650202658333142836" }, { "$numberDouble" : "57.70283083519552747" }, { "$numberDouble" : "58.48719280187031444" }, { "$numberDouble" : "59.932029859433328056" }, { "$numberDouble" : "60.435350057667704959" }, { "$numberDouble" : "61.796201961995798513" }, { "$numberDouble" : "62.883084798688862804" }, { "$numberDouble" : "63.070790792109328038" }, { "$numberDouble" : "64.16759516733826274" }, { "$numberDouble" : "65.684735624962627298" }, { "$numberDouble" : "66.406523484084644338" }, { "$numberDouble" : "67.628489973539217317" }, { "$numberDouble" : "68.548155362022797021" }, { "$numberDouble" : "69.446152335761937024" }, { "$numberDouble" : "70.850816173934148878" }, { "$numberDouble" : "71.371987666701940611" }, { "$numberDouble" : "72.231763790086574772" }, { "$numberDouble" : "73.057257769223340915" }, { "$numberDouble" : "74.248606955094231807" }, { "$numberDouble" : "75.734788957354354011" }, { "$numberDouble" : "76.261763568307117112" }, { "$numberDouble" : "77.366290387804127704" }, { "$numberDouble" : "78.090952646323614772" }, { "$numberDouble" : "79.907761062715451317" }, { "$numberDouble" : "80.213292529749651294" }, { "$numberDouble" : "81.40122970782175571" }, { "$numberDouble" : "82.397874716128853834" }, { "$numberDouble" : "83.856288865693912271" }, { "$numberDouble" : "84.836437448365771274" }, { "$numberDouble" : "85.712489576106221989" }, { "$numberDouble" : "86.344972416913108759" }, { "$numberDouble" : "87.838719804924522805" }, { "$numberDouble" : "88.736218332587981195" }, { "$numberDouble" : "89.061374463030290372" }, { "$numberDouble" : "90.181943740431705692" }, { "$numberDouble" : "91.370555458410805727" }, { "$numberDouble" : "92.540259312764803212" }, { "$numberDouble" : "93.151492898837148005" }, { "$numberDouble" : "94.45972723544899452" }, { "$numberDouble" : "95.041905493739093913" }, { "$numberDouble" : "96.099361464158789659" }, { "$numberDouble" : "97.057660017948322206" }, { "$numberDouble" : "98.420677137980504767" }, { "$numberDouble" : "99.694426199374930775" }, { "$numberDouble" : "100.28095505763631934" } ] }
 { "_id" : { "$numberInt" : "84564" }, "field1" : { "$numberInt" : "64" }, "field2" : "e14fc6847516bbae0055d5e29a8331db", "field3" : "0d91f561a9af719b173283826fff7dc9", "field4" : "7c7009c2b6b2d63ada1f3c84ee9e55dc", "field5" : "43d6dbdb0613c6ed6979b797fd693c9a", "array" : [ { "$numberInt" : "1" }, { "$numberInt" : "2" }, { "$numberInt" : "3" }, { "$numberInt" : "4" }, { "$numberInt" : "5" }, { "$numberInt" : "6" }, { "$numberInt" : "7" }, { "$numberInt" : "8" }, { "$numberInt" : "9" }, { "$numberInt" : "10" }, { "$numberInt" : "11" }, { "$numberInt" : "12" }, { "$numberInt" : "13" }, { "$numberInt" : "14" }, { "$numberInt" : "15" }, { "$numberInt" : "16" }, { "$numberInt" : "17" }, { "$numberInt" : "18" }, { "$numberInt" : "19" }, { "$numberInt" : "20" }, { "$numberInt" : "21" }, { "$numberInt" : "22" }, { "$numberInt" : "23" }, { "$numberInt" : "24" }, { "$numberInt" : "25" }, { "$numberInt" : "26" }, { "$numberInt" : "27" }, { "$numberInt" : "28" }, { "$numberInt" : "29" }, { "$numberInt" : "30" }, { "$numberInt" : "31" }, { "$numberInt" : "32" }, { "$numberInt" : "33" }, { "$numberInt" : "34" }, { "$numberInt" : "35" }, { "$numberInt" : "36" }, { "$numberInt" : "37" }, { "$numberInt" : "38" }, { "$numberInt" : "39" }, { "$numberInt" : "40" }, { "$numberInt" : "41" }, { "$numberInt" : "42" }, { "$numberInt" : "43" }, { "$numberInt" : "44" }, { "$numberInt" : "45" }, { "$numberInt" : "46" }, { "$numberInt" : "47" }, { "$numberInt" : "48" }, { "$numberInt" : "49" }, { "$numberInt" : "50" }, { "$numberInt" : "51" }, { "$numberInt" : "52" }, { "$numberInt" : "53" }, { "$numberInt" : "54" }, { "$numberInt" : "55" }, { "$numberInt" : "56" }, { "$numberInt" : "57" }, { "$numberInt" : "58" }, { "$numberInt" : "59" }, { "$numberInt" : "60" }, { "$numberInt" : "61" }, { "$numberInt" : "62" }, { "$numberInt" : "63" }, { "$numberInt" : "64" }, { "$numberInt" : "65" }, { "$numberInt" : "66" }, { "$numberInt" : "67" }, { "$numberInt" : "68" }, { "$numberInt" : "69" }, { "$numberInt" : "70" }, { "$numberInt" : "71" }, { "$numberInt" : "72" }, { "$numberInt" : "73" }, { "$numberInt" : "74" }, { "$numberInt" : "75" }, { "$numberInt" : "76" }, { "$numberInt" : "77" }, { "$numberInt" : "78" }, { "$numberInt" : "79" }, { "$numberInt" : "80" }, { "$numberInt" : "81" }, { "$numberInt" : "82" }, { "$numberInt" : "83" }, { "$numberInt" : "84" }, { "$numberInt" : "85" }, { "$numberInt" : "86" }, { "$numberInt" : "87" }, { "$numberInt" : "88" }, { "$numberInt" : "89" }, { "$numberInt" : "90" }, { "$numberInt" : "91" }, { "$numberInt" : "92" }, { "$numberInt" : "93" }, { "$numberInt" : "94" }, { "$numberInt" : "95" }, { "$numberInt" : "96" }, { "$numberInt" : "97" }, { "$numberInt" : "98" }, { "$numberInt" : "99" }, { "$numberInt" : "100" } ] }
(2 rows)

yugabyte=#

DocumentDB stores documents as BSON, which differs from JSON, particularly in its support for additional data types. With documentdb_core.bsonUseEJson, the textual representation of documents is displayed as Extended JSON instead of hexadecimal. Extended JSON is a JSON format that preserves BSON data types in a human-readable form.

To look at the internals, I need the name of the PostgreSQL table that stores the "db.coll1" collection:


-- get the internal table name
select format('documentdb_data.documents_%s',collection_id) as tab
 from documentdb_api_catalog.collections
 where (database_name,collection_name) = ('db','coll1')
\gset
\d :tab

Result:


yugabyte-# \gset
yugabyte=# \d :tab

                   Table "documentdb_data.documents_2"

     Column      |         Type         | Collation | Nullable | Default
-----------------+----------------------+-----------+----------+---------
 shard_key_value | bigint               |           | not null |
 object_id       | documentdb_core.bson |           | not null |
 document        | documentdb_core.bson |           | not null |

Indexes:
    "collection_pk_2" PRIMARY KEY, lsm ((shard_key_value, object_id) HASH)

Check constraints:
    "shard_key_value_check" CHECK (shard_key_value = '2'::bigint)

yugabyte=#

There is only one index, on "object_id", which corresponds to "_id". In PostgreSQL, this index is a B-tree, while YugabyteDB uses LSM trees, based on RocksDB. The table is a single shard, in DocumentDB terms, above the PostgreSQL query layer, and the MongoDB emulation layer includes the "shard_key_value" when generating the internal SQL query. YugabyteDB further shards the data beneath the PostgreSQL layer by hashing the primary key.

I review the tablets, and there's only one:


select tablet_id, table_name, state, partition_key_start, partition_key_end
from yb_local_tablets
where ysql_schema_name||'.'||table_name = :'tab';
\gset
\setenv tablet_id :tablet_id
\! /home/yugabyte/bin/yb-admin --master_addresses $HOSTNAME:7100 list_tablet_servers $tablet_id

Result:

yugabyte=# \gset


yugabyte=# \setenv tablet_id :tablet_id

yugabyte=# \! /home/yugabyte/bin/yb-admin --master_addresses $HOSTNAME:7100 list_tablet_servers $tablet_id

Server UUID                             RPC Host/Port           Role
65fa009d2c15448db385b1abf46e1e97        3e439d75380b:9100       LEADER

yugabyte=#

This is also visible from the UI on http://localhost:15433/databases/tabYsql

Here is the execution plan for a point query by "_id":


yugabyte=# explain (costs off, analyze on, dist on, debug on, verbose on, summary off)
select document from documentdb_api_catalog.bson_aggregation_pipeline(
    'db', '{"aggregate": "coll1", "pipeline": [
      {"$match": { "_id": 42 , "array": 42 } }
    ], "cursor": {}}'::documentdb_core.bson
);
                                                                                                                          QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using collection_pk_2 on documentdb_data.documents_2 collection (actual time=0.805..0.808 rows=1 loops=1)
   Output: document
   Index Cond: ((collection.shard_key_value = '2'::bigint) AND (collection.object_id OPERATOR(documentdb_core.=) '{ "" : { "$numberInt" : "42" } }'::documentdb_core.bson))
   Filter: ((collection.document OPERATOR(documentdb_api_catalog.@=) '{ "_id" : { "$numberInt" : "42" } }'::documentdb_core.bson) AND (collection.document OPERATOR(documentdb_api_catalog.@=) '{ "array" : { "$numberInt" : "42" } }'::documentdb_core.bson))
   Storage Table Read Requests: 1
   Storage Table Read Execution Time: 0.605 ms
   Storage Table Read Ops: 1
   Storage Table Rows Scanned: 1
     Metric rocksdb_block_cache_hit: 5.000
     Metric rocksdb_block_cache_index_hit: 1.000
     Metric rocksdb_block_cache_filter_hit: 1.000
     Metric rocksdb_block_cache_data_hit: 1.000
     Metric rocksdb_bloom_filter_useful: 1.000
     Metric rocksdb_bloom_filter_checked: 2.000
     Metric rocksdb_number_db_seek: 1.000
     Metric rocksdb_number_db_next: 1.000
     Metric rocksdb_number_db_seek_found: 1.000
     Metric rocksdb_number_db_next_found: 1.000
     Metric rocksdb_iter_bytes_read: 2561.000
     Metric rocksdb_block_cache_multi_touch_hit: 5.000
     Metric docdb_keys_found: 1.000
     Metric ql_read_latency: sum: 98.000, count: 1.000
   Estimated Seeks: 1
   Estimated Nexts And Prevs: 1001
   Estimated Table Roundtrips: 0
   Estimated Index Roundtrips: 1
   Estimated Docdb Result Width: 1285
(27 rows)

yugabyte=#

This is an optimal plan with a single read request to the tablet, determined by the hash value of (shard_key_value, object_id), and a single RocksDB "seek" in the LSM tree to retrieve the row, followed by a single RocksDB "next".

Because secondary indexes are not supported yet in the preview integration, it is different if I query by another field:


yugabyte=# explain (costs off, analyze on, dist on, debug on, verbose on, summary off)
select document from documentdb_api_catalog.bson_aggregation_pipeline(
    'db', '{"aggregate": "coll1", "pipeline": [
      {"$match": { "field1": 42 , "array": 42 } }
    ], "cursor": {}}'::documentdb_core.bson
);
                                                                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using collection_pk_2 on documentdb_data.documents_2 collection (actual time=2.437..186.944 rows=334 loops=1)
   Output: document
   Index Cond: (collection.shard_key_value = '2'::bigint)
   Filter: ((collection.document OPERATOR(documentdb_api_catalog.@=) '{ "field1" : { "$numberInt" : "42" } }'::documentdb_core.bson) AND (collection.document OPERATOR(documentdb_api_catalog.@=) '{ "array" : { "$numberInt" : "42" } }'::documentdb_core.bson))
   Rows Removed by Filter: 99666
   Storage Table Read Requests: 98
   Storage Table Read Execution Time: 107.827 ms
   Storage Table Read Ops: 98
   Storage Table Rows Scanned: 100000
     Metric rocksdb_block_cache_hit: 4677.000
     Metric rocksdb_block_cache_index_hit: 196.000
     Metric rocksdb_block_cache_data_hit: 4285.000
     Metric rocksdb_number_db_seek: 98.000
     Metric rocksdb_number_db_next: 100097.000
     Metric rocksdb_number_db_seek_found: 98.000
     Metric rocksdb_number_db_next_found: 100096.000
     Metric rocksdb_iter_bytes_read: 135359786.000
     Metric rocksdb_block_cache_multi_touch_hit: 4677.000
     Metric docdb_keys_found: 100097.000
     Metric ql_read_latency: sum: 79697.000, count: 98.000
   Estimated Seeks: 98
   Estimated Nexts And Prevs: 100000
   Estimated Table Roundtrips: 0
   Estimated Index Roundtrips: 98
   Estimated Docdb Result Width: 1285
(25 rows)

This displays an Index Scan because YugabyteDB stores rows in the primary key index. However, there is no Index Cond aside from the shard_key_value, which stays constant for all rows in this table. As a result, the scan reads the entire index, like a full index traversal from beginning to end. Logically, it resembles a Seq Scan in PostgreSQL but is physically similar to an Index Only Scan without Heap Fetches. The WHERE clause predicate isn’t included in the Index Cond. Instead, the Filter and Rows Removed by Filter show how many rows are read from storage, retrieved by the query layer, and then filtered out. In the end, all rows are scanned and fetched in 98 storage requests, given the default fetch size of 1024, with filtering happening afterward at the query layer.

The preview enables testing DocumentDB features on YugabyteDB. Since Yugabyte leverages PostgreSQL for its query layer, the extension is compatible. However, due to differences in storage, indexes must be tailored accordingly. Yugabyte has created distributed versions of GIN (inverted indexes for full-text search) and HNSW (pgvector index for similarity search) but still needs to develop indexes for the DocumentDB extension (RUM and Extended RUM). I'll monitor progress through https://github.com/yugabyte/yugabyte-db/issues/31634, which tracks this development.

PostgreSQL as a converged database with pglayers-full

Franck Pachot — Fri, 10 Jul 2026 14:13:09 +0000

PostgreSQL is extensible, allowing it to serve as a multi-model or converged database with built-in data types and indexes. If you're interested in exploring its features, compiling and installing all extensions can be quite a bit of work, but having an image packed with extensions makes things much easier. For this, I recommend using https://pglayers.github.io/ (thanks to Ismael Mejía).

Start pglayers-full in Docker

I began by using the ghcr.io/pglayers/pglayers-full:17 image to start the container. Not only did I get all the extensions installed, but it also ships the MongoDB-compatible endpoint offered by DocumentDB:


# remove previous container with same name ⚠️
docker rm -f pg-documentdb

# install DocumentDB extension before it can open the MongoDB-compatible endpoint
echo 'CREATE EXTENSION IF NOT EXISTS documentdb_core CASCADE;
CREATE EXTENSION IF NOT EXISTS documentdb CASCADE;' > /tmp/01-documentdb.sql

# start the container, exposes the PostgreSQL and MongoDB-compatible ports n host, set 
docker run -d --name pg-documentdb \
  -p 5432:5432 -p 10260:10260 \
  -e POSTGRES_PASSWORD=xxxMongoDBxxx\
  -v /tmp/01-documentdb.sql:/docker-entrypoint-initdb.d/01-documentdb.sql:ro \
  ghcr.io/pglayers/pglayers-full:17 \
  -c max_worker_processes=64 \
  -c max_connections=200 \
  -c documentdb.pg_gw_username=postgres \
  -c documentdb.pg_gw_password=xxxMongoDBxxx\
  -c cron.database_name=postgres

I waited for the PostgreSQL endpoint to be up:


echo -n "Waiting to get PostgreSQL endpoint up on 5432"
until docker exec pg-documentdb pg_isready -p 5432 2>/dev/null |
 grep "5432 - accepting connections"
 do echo -n . ; sleep 1 ; done

I listed the available extensions:


docker exec -i pg-documentdb psql -U postgres -t -c "select 'Available extensions: '||string_agg(name,', ') from pg_available_extensions;"

I could already connect to the PostgreSQL endpoint and use those extensions. As I also wanted to use the MongoDB-compatible API, I downloaded the MongoDB image that contains the client (MongoSH):

docker pull mongo

I waited for the MongoDB endpoint to be up:

echo -n "Waiting to get MongoDB emulation up on 10260"
until docker logs pg-documentdb 2>/dev/null |
 grep "bound to port 10260"
 do echo -n . ; sleep 1 ; done
echo "Ready."

I can connect with MongoSH simply by linking the PostgreSQL container when starting the MongoDB client:

docker run -it --rm --link pg-documentdb:pg mongo \
  mongosh "mongodb://postgres:xxxMongoDBxxx@pg:10260/?tls=true&tlsAllowInvalidCertificates=true"

Now I define aliases to connect to PostgreSQL via the two endpoints, with psql and mongosh:


alias p='docker exec -it pg-documentdb psql -U postgres'

alias m='docker run --rm -it --link pg-documentdb:pg mongo mongosh "mongodb://postgres:xxxMongoDBxxx@pg:10260/?tls=true&tlsInsecure=true"'

Ready to explore all extensions. I'll go further with DocumentDB.

Import MongoDB Sample Dataset to PostgreSQL DocumentDB

I tested with some data using the MongoDB client image to access the Sample Dataset and imported it into PostgreSQL via the DocumentDB endpoint with mongoimport:

#                  ⬇️ using --link to quickly connect to the other container
docker run --rm -i --link pg-documentdb:pg mongo bash <<'SH'
 # get wget
 apt update -qqy
 apt install wget -qy
 # get sample data
 wget -q -c https://atlas-education.s3.amazonaws.com/sampledata.archive
 # restore sample data
mongorestore -j 5 --drop --uri "mongodb://postgres:xxxMongoDBxxx@pg:10260/?tls=true&tlsInsecure=true" --archive=sampledata.archive
 # create the index that failed because of "textIndexVersion": 3
 mongosh "mongodb://postgres:xxxMongoDBxxx@pg:10260/sample_mflix?tls=true&tlsInsecure=true" --eval '
SH

...

One index creation failed because it sets textIndexVersion 2. No worries, we will create the same index later, just without mentioning the version, which is specific to vanilla MongoDB.

Look at MongoDB-compatible and DocumentDB execution plans

I can connect to the MongoDB API, create a text index on the movies collection, and execute a query just like I would in MongoDB. I used the m alias defined above, but you can connect with any MongoDB client.

Current Mongosh Log ID: 6a50e7576d644ce7f3c3a7d7
Connecting to:          mongodb://<credentials>@pg:10260/?tls=true&tlsInsecure=true&directConnection=true&appName=mongosh+2.9.2
Using MongoDB:          7.0.0
Using Mongosh:          2.9.2

For mongosh info see: https://www.mongodb.com/docs/mongodb-shell/

To help improve our products, anonymous usage data is collected and sent to MongoDB periodically (https://www.mongodb.com/legal/privacy-policy).
You can opt-out by running the disableTelemetry() command.

[direct: mongos] test> disableTelemetry()
Telemetry is now disabled.

[direct: mongos] test> use sample_mflix;
switched to db sample_mflix

[direct: mongos] sample_mflix> show collections
comments
embedded_movies
movies
sessions
theaters
users

[direct: mongos] sample_mflix> db.movies.createIndex({
    "cast": "text",   
    "fullplot": "text",   
    "genres": "text",   
    "title": "text"   
  }, {   
    "name": "cast_text_fullplot_text_genres_text_title_text",  
    "weights": { "cast": 1, "fullplot": 1, "genres": 1, "title": 1 },  
    "default_language": "english",  
    "language_override": "language"
  }  
)

cast_text_fullplot_text_genres_text_title_text

[direct: mongos] sample_mflix> db.movies.find(
  {
    $text: {
      $search: "\"star wars\""
    }
  }
).explain("executionStats").executionStats
;

{
  nReturned: Long('13'),
  executionTimeMillis: 137.596,
  executionStartAtTimeMillis: 135.295,
  totalDocsExamined: Long('13'),
  totalKeysExamined: Long('13'),
  executionStages: {
    stage: 'FETCH',
    nReturned: Long('13'),
    executionTimeMillis: 137.596,
    executionStartAtTimeMillis: 135.295,
    totalDocsExamined: 13,
    totalKeysExamined: 13,
    numBlocksFromCache: 73,
    inputStage: {
      stage: 'FETCH',
      nReturned: Long('13'),
      executionTimeMillis: 137.57,
      executionStartAtTimeMillis: 135.292,
      totalDocsExamined: 13,
      totalKeysExamined: 13,
      exactBlocksRead: 12,
      numBlocksFromCache: 73,
      inputStage: {
        stage: 'IXSCAN',
        nReturned: Long('13'),
        executionTimeMillis: 0.831,
        executionStartAtTimeMillis: 0.831,
        indexName: 'cast_text_fullplot_text_genres_text_title_text',
        totalKeysExamined: 13,
        numBlocksFromCache: 10
      }
    }
  }
}

I've shown the execution plan to verify that the MongoDB-compatible text index was utilized.

Here is the plan displayed by the DocumentDB for VS Code extension:

I can also switch to the SQL API, using the p alias defined above, and run the same query to view the PostgreSQL execution plan for it:

psql (17.10 (Debian 17.10-1.pgdg13+1))
Type "help" for help.

postgres=# \pset pager off

postgres=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE, COSTS OFF)
SELECT document FROM documentdb_api_catalog.bson_aggregation_find(
  'sample_mflix',
  '{
     "find":"movies",
     "filter":{
       "$text":{
         "$search":"\"star wars\""
       }
     }
   }'::documentdb_core.bson
);
                                                                                                                                                                                                  QUERY PLAN                                                                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (DocumentDBApiQueryScan) (actual time=36.533..38.597 rows=13 loops=1)
   Output: document
   Buffers: shared hit=34
   ->  Bitmap Heap Scan on documentdb_data.documents_112 collection (actual time=36.530..38.587 rows=13 loops=1)
         Output: document
         Filter: documentdb_api_internal.bson_text_meta_qual(collection.document, '''star'' <-> ''war'''::tsquery, '\x0400000000000000ffffffff000000000000000000000000000000007b0000002c0000007f000000040000000000803f000000000000803f000000000000803f000000000000803f000000000400000063617374000800000066756c6c706c6f74000600000067656e72657300050000007469746c6500b83300006c616e677561676500'::bytea, true)
         Heap Blocks: exact=12
         Buffers: shared hit=34
         ->  Bitmap Index Scan on cast_text_fullplot_text_genres_text_title_text (actual time=1.208..1.209 rows=13 loops=1)
               Index Cond: (collection.document OPERATOR(documentdb_api_catalog.@#%) '''star'' <-> ''war'''::tsquery)
               Buffers: shared hit=10
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.340 ms
 Execution Time: 39.203 ms
(15 rows)

postgres=# \d documentdb_data.documents_112
                  Table "documentdb_data.documents_112"
     Column      |         Type         | Collation | Nullable | Default
-----------------+----------------------+-----------+----------+---------
 shard_key_value | bigint               |           | not null |
 object_id       | documentdb_core.bson |           | not null |
 document        | documentdb_core.bson |           | not null |
Indexes:
    "collection_pk_112" PRIMARY KEY, btree (shard_key_value, object_id)
    "documents_rum_index_152" documentdb_rum (document documentdb_api_catalog.bson_rum_text_path_ops (weights='{ "cast" : 1.0, "fullplot" : 1.0, "genres" : 1.0, "title" : 1.0 }', defaultlanguage=english, languageoverride=language))
Check constraints:
    "shard_key_value_check" CHECK (shard_key_value = '112'::bigint)

Both execution plans indicate that the text index was used to directly retrieve 13 keys (totalKeysExamined: 13 in the MongoDB-compatible plan and rows=13 in the PostgreSQL plan) from 10 index pages (numBlocksFromCache: 10, Buffers: shared hit=10) and then fetch the corresponding documents for the results.

Here is the plan dusplayed by the PostgreSQL for VS Code extension:

The MongoDB text index is implemented in PostgreSQL as a RUM index through the DocumentDB extension. This highlights PostgreSQL’s robust open-source ecosystem and community. Vanilla PostgreSQL offers a permissive license and supports extensions for additional data types, such as BSON. PostgresPro improved text search with RUM indexes that include scoring. Microsoft also added support for BSON text search with RUM. Ismael developed Docker images containing all necessary extensions. As a result, we can now easily connect and utilize this unified multi-model database.

The experimentation is limitless. With this pglayers-full image, you can use plenty of extensions (some were already enabled by DocumentDB):

postgres=# CREATE EXTENSION IF NOT EXISTS documentdb;

NOTICE:  extension "documentdb" already exists, skipping
CREATE EXTENSION

postgres=# CREATE EXTENSION IF NOT EXISTS vector;
NOTICE:  extension "vector" already exists, skipping
CREATE EXTENSION

postgres=# CREATE EXTENSION IF NOT EXISTS postgis;

NOTICE:  extension "postgis" already exists, skipping
CREATE EXTENSION

postgres=# CREATE EXTENSION IF NOT EXISTS timescaledb;

ERROR:  function "time_bucket" already exists with same argument types

postgres=# CREATE EXTENSION IF NOT EXISTS pg_textsearch;
CREATE EXTENSION
postgres=#

postgres=# CREATE EXTENSION IF NOT EXISTS pg_graphql;
CREATE EXTENSION

postgres=# CREATE EXTENSION IF NOT EXISTS orafce;
CREATE EXTENSION

postgres=# CREATE EXTENSION IF NOT EXISTS pg_duckdb;
CREATE EXTENSION

I selected DocumentDB for this demonstration because it offers numerous advantages as a document database. The DocumentDB extension for PostgreSQL provides a fully open-source engine for MongoDB applications—both the MongoDB client and PostgreSQL server are OSS, as is the DocumentDB extension, which is part of the Linux Foundation.

Multi-model emulations

Although other converged databases and MongoDB emulations exist, they typically lack the same level of flexibility and features. For example, the index shown here, combining document and full-text search capabilities, is not available in Oracle's MongoDB emulation. Instead of native support, it converts the emulation to built-in features, which may not support identical properties:

I haven't chosen a specific case here. I simply generated the index from the Sample Dataset supplied by MongoDB's beginner courses.

By the way, DocumentDB on PostgreSQL can even run faster that vanilla MongoDB on such case. If you run the same on Atlas, you will see that first retrieves postings for the individual words, with two index scans, merges them, fetches the documents, and only afterwards verifies that the phrase exists:

To compare with PostgreSQL RUM indexes in DocumentDB, you should use MongoDB Atlas Search Indexes (Lucene indexes maintained by eventually consistent change stream) rather than legacy $text index.

Native extensibility vs. bolt-on multi-model

Next time you hear about multi-model, converged databases, or native APIs, remember that PostgreSQL was built by Michael Stonebraker before those marketing labels (THE DESIGN OF POSTGRES - 1986). It was specifically designed to take a relational foundation and make it extensible enough to natively support complex objects and abstract data types. Over time, SQL databases experimented with ORDBMS, and NoSQL databases promoted multi-model approaches, which became quite popular. RDBMS vendors responded by adding different API layers on top of their SQL schemas, calling it converged. Meanwhile, the PostgreSQL ecosystem continued to innovate by integrating native data types and access methods through its very flexible, extensible architecture. This extension framework allows developers to plug in new data types and index types as native extensions rather than transformations in the query layer. It's highly compatible with Docker image layers, and projects like pglayers.github.io use it to improve the developer experience.

“PostgreSQL resolves uniqueness through heap tuple visibility”

Franck Pachot — Thu, 09 Jul 2026 15:56:54 +0000

I recently commented on Jonathan Lewis’s blog, Savepoint Funny, where I compared how PostgreSQL handles uniqueness differently: “PostgreSQL resolves uniqueness through heap tuple visibility". This deserves a more detailed explanation.

In Oracle, unique indexes store unique entries because the B-tree key is the index key, preventing duplicates. Non-unique indexes add the ROWID to ensure that all entries are physically unique, even when indexed column values are duplicated.

In PostgreSQL, all indexes, even unique ones, created explicitly by CREATE UNIQUE INDEX or implicitly to enforce a unique constraint, behave like non-unique indexes by appending the TID (tuple ID, similar to Oracle's ROWID) to the index key. This indicates that the index itself doesn't guarantee physical uniqueness, allowing multiple entries to have identical logical keys but point to different heap tuples. The actual uniqueness verification occurs at the heap level, not within the index entries.

Initially, this might seem unusual—a unique index that permits duplicates. However, PostgreSQL requires this because of its MVCC system. MVCC allows duplicate entries to coexist in an index, since they can represent different versions of the same logical row. Still, PostgreSQL must guarantee that no MVCC snapshot views two rows with the same index key. Oracle doesn't face this issue because its MVCC implementation also versions index blocks, allowing a single index version to maintain unique keys.

Let’s show that.

Page inspect

In PostgreSQL, the heap contains the table data, and index entries point to heap tuples. Visibility depends on the heap header, especially the transaction information. Index scans often visit the heap pages to check visibility, except for index-only scans, which use the heap's visibility maps as an optimization. B-tree indexes can store entries for multiple versions of the same logical row, including versions that are no longer visible to current snapshots. To ensure uniqueness, the B-tree must check the heap for matching keys to verify whether multiple entries point to visible heap tuples in the same MVCC snapshot.

I used pageinspect to look at heap and index pages. This is not application-level SQL. This is a physical page inspection, useful for debugging and understanding internals. I used it to see all tuple versions, including those not visible to my MVCC snapshot. In some ways, you can compare it to Oracle flashback query, which shows all versions of a row.

CREATE EXTENSION IF NOT EXISTS pageinspect;

DROP TABLE IF EXISTS demo_unique_mvcc;

CREATE TABLE demo_unique_mvcc
(
    id      bigint generated always as identity,
    email   text not null,
    payload text not null,
    marker  int  not null,
    CONSTRAINT demo_unique_mvcc_email_key UNIQUE (email)
) WITH ( FILLFACTOR = 10 );

CREATE INDEX demo_unique_mvcc_marker_idx
ON demo_unique_mvcc(marker);

ALTER TABLE demo_unique_mvcc SET (autovacuum_enabled = false);

I disabled auto-vacuum to avoid garbage collection during my experimentation.

Unique index

I created a unique constraint on email:

postgres=# \d demo_unique_mvcc
                     Table "public.demo_unique_mvcc"
 Column  |  Type   | Collation | Nullable |           Default
---------+---------+-----------+----------+------------------------------
 id      | bigint  |           | not null | generated always as identity
 email   | text    |           | not null |
 payload | text    |           | not null |
 marker  | integer |           | not null |
Indexes:
    "demo_unique_mvcc_email_key" UNIQUE CONSTRAINT, btree (email)
    "demo_unique_mvcc_marker_idx" btree (marker)

postgres=#

I also created another index on marker. This is deliberate. If I update only a non-indexed column, PostgreSQL may use HOT updates, so no new entries are needed in the indexes. I want a non-HOT update to show the general case, so I update an indexed column, marker, while keeping the same email. To prove my point, I've set FILLFACTOR to 10% so that HOT updates are possible.

I inserted one row:

postgres=# INSERT INTO demo_unique_mvcc(email, payload, marker)
           VALUES ('a@example.com', 'first version', 1)
;

INSERT 0 1

postgres=# SELECT ctid, xmin, xmax, *
           FROM demo_unique_mvcc
;

 ctid  | xmin | xmax | id |     email     |    payload    | marker
-------+------+------+----+---------------+---------------+--------
 (0,1) |  697 |    0 |  1 | a@example.com | first version |      1

(1 row)

The precise xmin may vary as it's your transaction identifier. What's crucial is the ctid. In this case, the visible row version is (0,1), which is the first tuple on the first page.

Now let’s look at the heap page:

postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
           FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
           ORDER BY lp
;

 lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
  1 |    697 |      0 | (0,1)  |       2306 |           4

(1 row)

This tuple was inserted by committed transaction 697, has not been deleted or updated (xmax is invalid), contains at least one variable-length column, and stores 4 attributes.

I inspect the unique index. For a B-tree index, block 0 is the metapage, so the first leaf page is usually block 1 for a tiny index:

postgres=# SELECT itemoffset, ctid, htid, dead,
    data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
           FROM bt_page_items('demo_unique_mvcc_email_key', 1)
           ORDER BY itemoffset
;

 itemoffset | ctid  | htid  | dead |                      data                       |        encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
          1 | (0,1) | (0,1) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000

(1 row)

One visible row. One index entry in block 1 of the index (ctid) addressing block 1 of the heap table (htid). Nothing surprising yet.

Multiple updates

I updated the row two times, but I did not change the unique key:

postgres=# UPDATE demo_unique_mvcc
           SET payload = 'second version', marker  = 2
           WHERE email = 'a@example.com'
;

UPDATE 1

postgres=# UPDATE demo_unique_mvcc
           SET payload = 'third version', marker  = 3
           WHERE email = 'a@example.com'
;

UPDATE 1

The email value did not change. Logically, there is still one row with email = 'a@example.com':

postgres=# SELECT ctid, xmin, xmax, *
           FROM demo_unique_mvcc
;

 ctid  | xmin | xmax | id |     email     |    payload    | marker
-------+------+------+----+---------------+---------------+--------
 (0,3) |  711 |    0 |  1 | a@example.com | third version |      3

(1 row)

Only one row is visible to my SQL query.

But the heap page tells a different physical story:

postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
           FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
           ORDER BY lp
;

 lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
  1 |    697 |    710 | (0,2)  |       1282 |           4
  2 |    710 |    711 | (0,3)  |       9474 |           4
  3 |    711 |      0 | (0,3)  |      10498 |           4

(3 rows)

There are three heap tuple versions. The first is no longer current and has been superseded by transaction 710, which has set xmax. The second one is the transaction 710 change superseded by transaction 711. The third is the current version with xmax set to 0. Note that t_ctid points to the next version, so it can chain from old versions to new ones, except for the current version, which points to itself. Typically, here, an index entry pointing to (0,1) (line pointer lp=1 in block 0) can continue to (0,2) and then (0,3).

The old versions are not visible to my current query, but they are still physically present because I disabled autovacuum and have not vacuumed the table.

Now inspect the unique index:

postgres=# SELECT itemoffset, ctid, htid, dead,
    data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
           FROM bt_page_items('demo_unique_mvcc_email_key', 1)
           ORDER BY itemoffset
;

 itemoffset | ctid  | htid  | dead |                      data                       |        encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
          1 | (0,1) | (0,1) | t    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
          2 | (0,2) | (0,2) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
          3 | (0,3) | (0,3) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000

(3 rows)

This is the important point.

The unique index can physically hold multiple entries for the same logical key (data) because these entries point to different versions of heap tuples (htid).

Duplicate key violations

I tried to insert the same email into a new row:

postgres=# INSERT INTO demo_unique_mvcc(email, payload, marker)
           VALUES ('a@example.com', 'another logical row', 10)
;

ERROR:  duplicate key value violates unique constraint "demo_unique_mvcc_email_key"
DETAIL:  Key (email)=(a@example.com) already exists.

postgres=#

This fails. The B-tree found matching key entries in the index. Then PostgreSQL checked the heap tuple visibility of the referenced tuples. At least one conflicting tuple is visible, so this is a uniqueness violation. The index alone did not decide everything. The heap visibility check decides whether the duplicate index entry is a real conflict.

The duplicate tuple was inserted into the heap before the statement was aborted, and this is visible from the physical history:

postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
           FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
           ORDER BY lp
;

 lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
  1 |    697 |    710 | (0,2)  |       1282 |           4
  2 |    710 |    711 | (0,3)  |       9474 |           4
  3 |    711 |      0 | (0,3)  |      10498 |           4
  4 |    712 |      0 | (0,4)  |       2050 |           4

(4 rows)

PostgreSQL does not determine visibility from xmin/xmax alone. The infomask bits tell whether the transaction status is already known (committed, aborted, invalid, updated, locked, etc.). When the bits are not set, PostgreSQL consults the transaction status and may later set hint bits in the tuple header:

postgres=# SELECT txid_status('744')
;

 txid_status
-------------
 aborted

(1 row)

postgres=# SELECT txid_status('742')
;

 txid_status
-------------
 committed

(1 row)

Nothing changed in the index. It was used to find the multiple versions, but a duplicate key was detected before updating it:

postgres=# SELECT itemoffset, ctid, htid, dead,
    data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
           FROM bt_page_items('demo_unique_mvcc_email_key', 1)
           ORDER BY itemoffset
;

 itemoffset | ctid  | htid  | dead |                      data                       |        encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
          1 | (0,1) | (0,1) | t    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
          2 | (0,2) | (0,2) | t    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
          3 | (0,3) | (0,3) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000

(3 rows)

It is not visible here, but even if uniqueness is detected on the heap tuples, it is still part of the index access methods because concurrency control must synchronize transactions by locking the index leaf page, which is the only place where all transactions updating the same key must go, since heap tuples could be in different blocks. This explains why a unique index is required to enforce unique constraints, even though duplicate checking itself is done on the heap.

Heap-Only Tuples

I mentioned HOT updates. As I've set a low fill factor and still have lots of free space in the block (as we confirm with ctid, all updates are within the same block), an update of a single indexed column may not add a new entry:

postgres=# UPDATE demo_unique_mvcc
           SET payload = 'fourth version' --, marker  = 2
           WHERE email = 'a@example.com'
;

UPDATE 1

postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
           FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
           ORDER BY lp
;

 lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
  1 |    697 |    710 | (0,2)  |       1282 |           4
  2 |    710 |    711 | (0,3)  |       9474 |           4
  3 |    711 |    713 | (0,5)  |       8450 |       16388
  4 |    712 |      0 | (0,4)  |       2050 |           4
  5 |    713 |      0 | (0,5)  |      10242 |       32772

(5 rows)

postgres=# SELECT itemoffset, ctid, htid, dead,
    data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
           FROM bt_page_items('demo_unique_mvcc_email_key', 1)
           ORDER BY itemoffset
;

 itemoffset | ctid  | htid  | dead |                      data                       |        encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
          1 | (0,1) | (0,1) | t    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
          2 | (0,2) | (0,2) | t    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
          3 | (0,3) | (0,3) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000

(3 rows)

The t_infomask2 added HEAP_ONLY_TUPLE (32768) to the number of attributes (4), indicating it has no index entry of its own. The entry pointing to (0,3) will follow the HOT chain to find the other version within the same page.

Deferred unique constraint

One last test. We have seen that a duplicate key insert leaves a tuple in the heap but no new entry in the index because the duplicate is detected during index update. However, if the unique constraint checking is deferred, the entry will be stored, and the detection happens at commit:


postgres=# ALTER TABLE demo_unique_mvcc   
           DROP CONSTRAINT demo_unique_mvcc_email_key
;  

postgres=# ALTER TABLE demo_unique_mvcc   
           ADD CONSTRAINT demo_unique_mvcc_email_key   
           UNIQUE (email) DEFERRABLE;

postgres=# SELECT itemoffset, ctid, htid, dead,
    data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
           FROM bt_page_items('demo_unique_mvcc_email_key', 1)
           ORDER BY itemoffset
;

 itemoffset | ctid  | htid  | dead |                      data                       |        encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
          1 | (0,3) | (0,3) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000

(1 row)

postgres=# BEGIN;

BEGIN

postgres=# SET CONSTRAINTS demo_unique_mvcc_email_key DEFERRED
;

SET CONSTRAINTS

postgres=# INSERT INTO demo_unique_mvcc(email, payload, marker)
           VALUES ('a@example.com', 'a duplicate row', 10);
;

INSERT 0 1

postgres=*# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
           FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
           ORDER BY lp
;

 lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
  1 |    697 |    710 | (0,2)  |       1282 |           4
  2 |    710 |    711 | (0,3)  |       9474 |           4
  3 |    711 |    713 | (0,5)  |       9474 |       16388
  4 |    712 |      0 | (0,4)  |       2562 |           4
  5 |    713 |      0 | (0,5)  |      10498 |       32772
  6 |    718 |      0 | (0,6)  |       2050 |           4

(6 rows)


postgres=*# SELECT itemoffset, ctid, htid, dead,
    data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
           FROM bt_page_items('demo_unique_mvcc_email_key', 1)
           ORDER BY itemoffset
;
 itemoffset | ctid  | htid  | dead |                      data                       |        encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
          1 | (0,3) | (0,3) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
          2 | (0,6) | (0,6) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
(2 rows)

postgres=*# COMMIT;

ERROR:  duplicate key value violates unique constraint "demo_unique_mvcc_email_key"
DETAIL:  Key (email)=(a@example.com) already exists.

postgres=# SELECT itemoffset, ctid, htid, dead,
    data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
           FROM bt_page_items('demo_unique_mvcc_email_key', 1)
           ORDER BY itemoffset
;

 itemoffset | ctid  | htid  | dead |                      data                       |        encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
          1 | (0,3) | (0,3) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
          2 | (0,6) | (0,6) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
(2 rows)

postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
           FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
           ORDER BY lp
;
 lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
  1 |    697 |    710 | (0,2)  |       1282 |           4
  2 |    710 |    711 | (0,3)  |       9474 |           4
  3 |    711 |    713 | (0,5)  |       9474 |       16388
  4 |    712 |      0 | (0,4)  |       2562 |           4
  5 |    713 |      0 | (0,5)  |      10498 |       32772
  6 |    718 |      0 | (0,6)  |       2050 |           4
(6 rows)

Like with many databases, unique constraint violations should be exceptional rather than part of the normal execution path. Do not use insert-first-then-update for an upsert use case, as it will increase table and maybe also index bloat.

Garbage collection

For the demo, I disabled it, but the auto-vacuum runs in the background and cleans up old versions. I do the equivalent with a manual VACUUM:

postgres=# vacuum;

VACUUM

postgres=# SELECT itemoffset, ctid, htid, dead,
    data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
           FROM bt_page_items('demo_unique_mvcc_email_key', 1)
           ORDER BY itemoffset
;

 itemoffset | ctid  | htid  | dead |                      data                       |        encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
          1 | (0,3) | (0,3) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000

(1 row)

postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
           FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
           ORDER BY lp
;

 lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
  1 |        |        |        |            |
  2 |        |        |        |            |
  3 |        |        |        |            |
  4 |        |        |        |            |
  5 |    713 |      0 | (0,5)  |      10498 |       32772

(5 rows)

Only the current heap tuple and index entry remain.

Conclusion

PostgreSQL unique indexes are not physically unique as Oracle practitioners may expect. A unique B-tree can have multiple entries with the same key because index entries identify tuple versions, not logical rows. Under MVCC, several versions of the same row coexist, each requiring an index entry.

Uniqueness isn't determined by the index key alone. PostgreSQL matches index entries to the heap and uses visibility information (xmin, xmax, transaction status, HOT chains) to determine whether two rows appear as duplicates in the same snapshot. This preserves MVCC while guaranteeing logical uniqueness. Raw pages viewed with PageInspect show duplicate index entries as normal, but duplicate visible rows are not. The unique index serves as a rendezvous point for concurrent transactions, with the final decision determined by heap tuple visibility.

Failed duplicate inserts and deferred constraints can leave behind invisible heap tuples or index entries. Uniqueness depends on the index, MVCC visibility, and heap metadata, not just the index.

Although this might seem like a performance weakness compared to block-level MVCC and using the B-tree key itself to detect duplicate key violations, PostgreSQL's design brings a major advantage: extensibility. New data types, operators, and even entirely new index access methods can be added without changing the core database engine.

PostgreSQL ships with multiple built-in index types:

B-tree for equality, ranges, ordering, and unique constraints
Hash for equality lookups
GiST for geometric, range, and extensible indexing
SP-GiST for partitioned search structures such as tries and quadtrees
GIN and RUM for inverted indexes on arrays, JSON documents, and full-text search
BRIN for very large naturally ordered tables
Bloom for space-efficient multi-column membership tests

Extensions further expand the indexing landscape:

BM25 indexes (ParadeDB, pg_textsearch) for relevance-ranked lexical search
IVFFlat/HNSW (pg_vector), and DiskANN indexes for vector embeddings and similarity search
Extended RUM indexes in DocumentDB to support MongoDB-style multi-key indexing

Oracle preserves uniqueness within its index structure, suitable for a fixed set of index types. In contrast, PostgreSQL separates index navigation from tuple visibility, enabling any index access method to conduct uniqueness checks independently of MVCC. An open-source, extensible system faces different trade-offs than proprietary commercial software: it needs to keep its code simple, well-organized, and with a clear separation of concerns to encourage community contributions, rather than prioritizing revenue-driven use cases.

DEV Community: Franck Pachot

PostgreSQL Multi-Version: From Time Travel to Concurrency Control

1. POSTGRES began with a no-overwrite storage argument

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

3. Low-Level Locking turned storage versions into snapshots

4. What changed between PostgreSQL 6.4 and 6.5

5. A snapshot is not a single number

6. An UPDATE is a short-lived history

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

8. VACUUM decides when history has stopped being evidence

9. MVCC did not by itself make SERIALIZABLE serializable

10. Undo-based alternatives revisit the storage choice

11. Oracle reaches consistent reads by a different physical route

12. Two heaps, seen directly

13. History moves between layers

14. The old decision is still visible in current operations

PostgreSQL 19 REPACK: Choosing the Right FILLFACTOR

FILLFACTOR 100% and first update

FILLFACTOR 100% and frequent updates

FILLFACTOR 100% with inserts and one update

FILLFACTOR 50% for one update

FILLFACTOR 33% for two updates

REPACK with the current FILLFACTOR

REPACK with the FILLFACTOR 100%

HorizonDB reduces WAL overhead with smarter FPI (full-page image) than traditional PostgreSQL

Experimental method

Configuration

Experiment 1: Generate the table data

Experiment 2: Create primary keys

Experiment 3: Create foreign keys

Experiment 4: VACUUM — the key experiment

Experiment 5: Transactional workload

What the counters ultimately mean

Conclusion

WHERE $1::timestamptz IS NULL OR "timestamp" > $1

Following ROWIDs Through an Oracle Unique Index Update

Investigation with block dumps

Initial state

Running the update

UNIQUE vs non-UNIQUE index

Conclusion

B-tree block split: what's the impact?

Inspecting the B-Tree Metadata with bt_metap()

Collecting Statistics With pgstatindex()

Test setup

The First Split

Conclusion

B+tree height after full delete: PostgreSQL fast root

Oracle Database: reclaim levels with rebuild

PostgreSQL

Two variations

Conclusion

From Joins to Graph Edges: SQL/PGQ in PostgreSQL 19

Relational model

Property graph definition

Querying the graph

Combining relationships

Traversing hierarchies

Relational and graph models

Cypher graph queries on PostgreSQL with Apache AGE

Apache AGE on PostgreSQL

Graph model

Graph nodes (vertices () )

Graph relationships (edges -[]->)

AGE Internals

Query

Visualization

Execution Plan

Multiple relationships

Conclusion

DocumentDB on YugabyteDB

PostgreSQL as a converged database with pglayers-full

Start pglayers-full in Docker

Import MongoDB Sample Dataset to PostgreSQL DocumentDB

Look at MongoDB-compatible and DocumentDB execution plans

Multi-model emulations

Native extensibility vs. bolt-on multi-model

“PostgreSQL resolves uniqueness through heap tuple visibility”

Page inspect

Unique index

Graph nodes (vertices `()` )

Graph relationships (edges `-[]->`)