DEV Community: Franck Pachot

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.

What happens when a PostgreSQL backend crashes?

Franck Pachot — Tue, 07 Jul 2026 14:40:55 +0000

Your connection to PostgreSQL is handled by a dedicated backend process. If that process crashes, you might think only your session is affected. However, because backend processes share memory, PostgreSQL assumes the shared state may have been corrupted and immediately terminates all other connections. Recovery is then performed before new connections are accepted.

Here is a short test to demonstrate it and to see what is visible in the PostgreSQL server log and what is received in the application. I run an ephemeral Docker container, show the log file, start ten pgbench clients, and kill my own session:


docker exec -it $(
docker run -d --rm -e POSTGRES_PASSWORD=xxx postgres -c logging_collector=on
sleep 3
) psql -U postgres <<'SQL'

\! sleep 1 ; echo '\n 🐘 Start pgbench in the background...\n' ; sleep 1

\! pgbench -i -U postgres postgres 
\! pgbench -c 5 -T 60 -P 1 -U postgres postgres & sleep 10

\! sleep 1 ; echo '\n 🐘 Tail the logfile...\n' ; sleep 1

select current_setting('data_directory')||'/'||pg_current_logfile() log
\gset
\setenv log :log
\! tail -f "$log" | sed -e "s/^/📜 /" &

\! sleep 1 ; echo '\n 🐘 Crash the current process...\n' ; sleep 1

select pg_backend_pid() as pid
\gset 
\setenv pid :pid
\! kill -9 $pid

\! sleep 30

\q

SQL

Here is the output. I've deliberately set it to run in an ephemeral Docker container because I don't want you to do the same on an existing database.

The container started, psql connected, and pgbench started:


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

postgres=#
postgres=# \! sleep 1 ; echo '\n 🐘 Start pgbench in the background...\n' ; sleep 1

 🐘 Start pgbench in the background...

postgres=#
postgres=# \! pgbench -i -U postgres postgres
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
vacuuming...
creating primary keys...
done in 0.21 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 0.14 s, vacuum 0.03 s, primary keys 0.03 s).
postgres=# \! pgbench -c 5 -T 60 -P 1 -U postgres postgres & sleep 10
pgbench (18.4 (Debian 18.4-1.pgdg13+1))
starting vacuum...end.
progress: 1.0 s, 504.0 tps, lat 9.719 ms stddev 7.949, 0 failed
progress: 2.0 s, 338.0 tps, lat 14.826 ms stddev 9.621, 0 failed
progress: 3.0 s, 348.0 tps, lat 14.335 ms stddev 9.640, 0 failed
progress: 4.0 s, 346.0 tps, lat 14.456 ms stddev 9.288, 0 failed
progress: 5.0 s, 357.0 tps, lat 13.976 ms stddev 9.178, 0 failed
progress: 6.0 s, 349.0 tps, lat 14.371 ms stddev 10.674, 0 failed
progress: 7.0 s, 357.0 tps, lat 14.010 ms stddev 9.035, 0 failed
progress: 8.0 s, 350.0 tps, lat 14.235 ms stddev 9.087, 0 failed
progress: 9.0 s, 341.0 tps, lat 14.788 ms stddev 9.204, 0 failed
progress: 10.0 s, 277.0 tps, lat 18.002 ms stddev 9.095, 0 failed

Displaying the PostgreSQL log file while pgbench is still running:


postgres=# \! sleep 1 ; echo '\n 🐘 Tail the logfile...\n' ; sleep 1

 🐘 Tail the logfile...

progress: 11.0 s, 353.0 tps, lat 14.131 ms stddev 10.032, 0 failed
postgres=#
postgres=# select current_setting('data_directory')||'/'||pg_current_logfile() log
postgres-# \gset
postgres=# \setenv log :log
postgres=# \! tail -f "$log" | sed -e "s/^/📜 /" &
postgres=#
📜 2026-07-06 17:06:18.098 UTC [1] LOG:  starting PostgreSQL 18.4 (Debian 18.4-1.pgdg13+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
📜 2026-07-06 17:06:18.099 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
📜 2026-07-06 17:06:18.099 UTC [1] LOG:  listening on IPv6 address "::", port 5432
📜 2026-07-06 17:06:18.101 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
📜 2026-07-06 17:06:18.106 UTC [75] LOG:  database system was shut down at 2026-07-06 17:06:17 UTC
📜 2026-07-06 17:06:18.109 UTC [1] LOG:  database system is ready to accept connections
progress: 12.0 s, 332.0 tps, lat 15.064 ms stddev 10.867, 0 failed

Crashing the current backend with kill -9 while pgbench is still running and the log file is being tailed:


postgres=# \! sleep 1 ; echo '\n 🐘 Crash the current process...\n' ; sleep 1


 🐘 Crash the current process...

progress: 13.0 s, 357.0 tps, lat 13.976 ms stddev 9.172, 0 failed
postgres=#
postgres=# select pg_backend_pid() as pid
postgres-# \gset
postgres=# \setenv pid :pid
postgres=# \! kill -9 $pid
postgres=#
postgres=# \! sleep 30
📜 2026-07-06 17:06:36.241 UTC [1] LOG:  client backend (PID 85) was terminated by signal 9: Killed
📜 2026-07-06 17:06:36.241 UTC [1] DETAIL:  Failed process was running: select pg_backend_pid() as pid
📜 2026-07-06 17:06:36.241 UTC [1] LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
pgbench: error: client 0 aborted in command 10 (SQL) of script 0; perhaps the backend died while processing
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
pgbench: error: client 2 aborted in command 8 (SQL) of script 0; perhaps the backend died while processing
pgbench: error: client 3 aborted in command 8 (SQL) of script 0; perhaps the backend died while processing
📜 2026-07-06 17:06:36.243 UTC [1] LOG:  all server processes terminated; reinitializing
pgbench: error: client 4 aborted in command 8 (SQL) of script 0; perhaps the backend died while processing
pgbench: error: client 1 aborted in command 7 (SQL) of script 0; perhaps the backend died while processing
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 5
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 4948
number of failed transactions: 0 (0.000%)
latency average = 14.106 ms
latency stddev = 9.783 ms
initial connection time = 6.526 ms
tps = 353.948496 (without initial connection time)
pgbench: error: Run was aborted; the above results are incomplete.
📜 2026-07-06 17:06:36.250 UTC [117] LOG:  database system was interrupted; last known up at 2026-07-06 17:06:18 UTC
📜 2026-07-06 17:06:36.390 UTC [117] LOG:  database system was not properly shut down; automatic recovery in progress
📜 2026-07-06 17:06:36.392 UTC [117] LOG:  redo starts at 0/175F960
📜 2026-07-06 17:06:36.433 UTC [117] LOG:  invalid record length at 0/2687F00: expected at least 24, got 0
📜 2026-07-06 17:06:36.433 UTC [117] LOG:  redo done at 0/2687ED8 system usage: CPU: user: 0.03 s, system: 0.00 s, elapsed: 0.04 s
📜 2026-07-06 17:06:36.435 UTC [118] LOG:  checkpoint starting: end-of-recovery immediate wait
📜 2026-07-06 17:06:37.205 UTC [118] LOG:  checkpoint complete: wrote 2054 buffers (12.5%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 1 recycled; write=0.625 s, sync=0.138 s, total=0.771 s; sync files=51, longest=0.108 s, average=0.003 s; distance=15521 kB, estimate=15521 kB; lsn=0/2687F00, redo lsn=0/2687F00
📜 2026-07-06 17:06:37.207 UTC [1] LOG:  database system is ready to accept connections


postgres=#
postgres=# \q

This indicates that PostgreSQL intentionally terminated all other connections (as seen in the error messages from the five pgbench connections) when it detected a crash signal, and that it completed recovery before allowing new connections:

The logfile indicates the cause:

LOG:  client backend (PID 85) was terminated by signal 9: Killed
LOG:  terminating any other active server processes

The application encountered the error:

WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.

Unlike database engines that keep sessions separate, PostgreSQL backends share key memory areas like shared buffers, lock tables, and transaction status data. If a backend crashes unexpectedly, PostgreSQL cannot ensure these structures stay consistent. As a result, it adopts a cautious strategy: shutting down all backends and restarting from a reliable, known state.

This behavior is deliberate and is a fundamental safety feature of PostgreSQL. When a backend crashes, it is considered a possible sign of shared-memory corruption. To prevent serving inconsistent data, PostgreSQL shuts down all sessions, replays WAL during recovery, and only resumes accepting connections afterward.

PostgreSQL query planner parameters and prepared statements

Franck Pachot — Sun, 05 Jul 2026 18:46:50 +0000

PostgreSQL provides several planner configuration parameters, such as enable_seqscan and enable_indexscan, that influence how execution plans are generated. These settings affect planning, not the execution of an already-generated plan. With prepared statements, an interesting question arises. Should planner settings be applied before PREPARE, before EXECUTE, or both?

Let's look at a simple example: a "tasks" table with a due date and a "done" status:

\c

drop   table if exists tasks;

-- a table of tasks with status (done or not) and due date
create table tasks (
 id   bigint generated always as identity primary key,
 due  timestamptz,
 done boolean
);

-- insert 500 tasks, with 1% not done
insert into tasks (due, done)
 select
  now()+interval '1 day'*n,
  42 != n%100
 from generate_series(1,500) n
;

-- index the todo (partial index)
create index on tasks(due,id)
 where done = false;

vacuum analyze tasks;

With a partial index, I indexed only the tasks that are not yet done (done = false) because that's my most frequent query pattern:

postgres=# explain
            select id, due, done from tasks 
             where done = false and id>0
             order by due limit 1
;

                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Limit  (cost=0.13..3.60 rows=1 width=17)
   ->  Index Scan using tasks_due_id_idx1 on tasks  (cost=0.13..17.47 rows=5 width=17)
         Index Cond: (id > 0)
(3 rows)

With partial indexes, the condition covered by the index is not even visible in the execution plan because the index itself enforces the condition.

Prepared statement

I decided to use a prepared statement with all values as parameters. It is probably not a good idea in this case. When a parameter can have only a few different values, and you expect different cardinalities for each, you should probably define one query per value, using literals. I'm doing this to illustrate what can happen, with a simple, extreme example:

postgres=# prepare c(boolean, int) as
            select id, due, done from tasks 
             where done = $1 and id>0
             order by due limit $2
;

PREPARE

postgres=# explain (analyze, settings) 
            execute c (false,  1)
;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.13..3.60 rows=1 width=17) (actual time=0.087..0.088 rows=1.00 loops=1)
   Buffers: shared hit=4 read=1
   ->  Index Scan using tasks_due_id_idx on tasks  (cost=0.13..17.47 rows=5 width=17) (actual time=0.087..0.087 rows=1.00 loops=1)
         Index Cond: (id > 0)
         Index Searches: 1
         Buffers: shared hit=4 read=1
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.148 ms
 Execution Time: 0.099 ms
(10 rows)

With the same prepared statement, I disabled the Index Scan before the next execution:

postgres=# set enable_indexscan to off;
SET

postgres=# explain (analyze, settings) 
            execute c (false,  1)
;

                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.28..10.28 rows=1 width=17) (actual time=0.043..0.043 rows=1.00 loops=1)
   Buffers: shared hit=4
   ->  Sort  (cost=10.28..10.29 rows=5 width=17) (actual time=0.042..0.042 rows=1.00 loops=1)
         Sort Key: due
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=4
         ->  Seq Scan on tasks  (cost=0.00..10.25 rows=5 width=17) (actual time=0.012..0.037 rows=5.00 loops=1)
               Filter: ((NOT done) AND (id > 0))
               Rows Removed by Filter: 495
               Buffers: shared hit=4
 Settings: enable_indexscan = 'off'
 Planning:
   Buffers: shared hit=12
 Planning Time: 0.174 ms
 Execution Time: 0.055 ms
(15 rows)

I was still using the same prepared statement, yet the execution plan had changed. PostgreSQL generated a new custom plan for this execution, so it used the planner setting active at EXECUTE time (enable_indexscan = 'off') rather than any setting active when the statement was prepared.

With plan_cache_mode set to the default auto, each execution of the prepared statement goes through a fresh planning phase, similar to an ad hoc query, but with the actual parameter values available to the planner as constants for at least the first five executions. The query planner parameters at EXECUTE time drive the planning, resulting in a custom plan. After five executions, it may switch to a generic plan if the optimizer estimates it's worth it, comparing the generic plan's cost with the average execution cost of the previous custom plans (including planning overhead).

Because this decision depends on cost estimates, previous execution history, and the generic-versus-custom plan heuristic, plan selection may be less predictable than when plan_cache_mode is explicitly set. I recommend not relying on the auto behavior; instead, decide whether a prepared statement should be generic or custom by setting plan_cache_mode accordingly. Ideally, you should use parameters only when a generic plan is acceptable, and use different prepared statements with literals when the value matters for choosing the optimal access path.

Generic plan

You might expect a generic plan to permanently preserve the planner environment in effect when the plan was created. Here is the same example with plan_cache_mode set to force_generic_plan:


--- reset the session 

postgres=# \c
You are now connected to database "postgres" as user "postgres".

postgres=# \dconfig enable*scan*

List of configuration parameters

      Parameter       | Value
----------------------+-------
 enable_bitmapscan    | on
 enable_indexonlyscan | on
 enable_indexscan     | on
 enable_seqscan       | on
 enable_tidscan       | on
(5 rows)

-- disable auto plan cache mode and set it to generic

postgres=# set plan_cache_mode=force_generic_plan;

SET

postgres=# prepare c(boolean, int) as
            select id, due, done from tasks
             where done = $1 and id > 0
             order by due limit $2
;

PREPARE

postgres=# explain (analyze, settings)
            execute c (false,  1)
;

                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=21.46..21.52 rows=25 width=17) (actual time=0.057..0.057 rows=1.00 loops=1)
   Buffers: shared hit=7
   ->  Sort  (cost=21.46..22.08 rows=250 width=17) (actual time=0.055..0.055 rows=1.00 loops=1)
         Sort Key: due
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=7
         ->  Seq Scan on tasks  (cost=0.00..11.50 rows=250 width=17) (actual time=0.010..0.040 rows=5.00 loops=1)
               Filter: ((id > 0) AND (done = $1))
               Rows Removed by Filter: 495
               Buffers: shared hit=4
 Settings: plan_cache_mode = 'force_generic_plan'
 Planning:
   Buffers: shared hit=122
 Planning Time: 0.453 ms
 Execution Time: 0.073 ms
(15 rows)

With the predicate on "done" that can take any value, a generic plan cannot use the partial index that contains entries only for the false value, so the query planner falls back to a Seq Scan.

I disable Seq Scan for the next execution:


postgres=# set enable_seqscan to off;

SET

postgres=# explain (analyze, settings)
            execute c (false,  1)
;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=21.46..21.52 rows=25 width=17) (actual time=0.059..0.060 rows=1.00 loops=1)
   Buffers: shared hit=4
   ->  Sort  (cost=21.46..22.08 rows=250 width=17) (actual time=0.057..0.057 rows=1.00 loops=1)
         Sort Key: due
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=4
         ->  Seq Scan on tasks  (cost=0.00..11.50 rows=250 width=17) (actual time=0.018..0.051 rows=5.00 loops=1)
               Filter: ((id > 0) AND (done = $1))
               Rows Removed by Filter: 495
               Buffers: shared hit=4
 Settings: plan_cache_mode = 'force_generic_plan', enable_seqscan = 'off'
 Planning Time: 0.013 ms
 Execution Time: 0.080 ms
(13 rows)

The statement was not re-planned. While there's no direct proof, several clues suggest it:

Seq Scan persisted even when disabled, even though an alternative access method, such as using the primary key index, is available and would respect the directive, even if not optimal.
The Planning section was absent, as seen in the initial EXECUTE after PREPARE, which showed Buffers: shared hit related to catalog lookups.
The Planning Time was brief, only covering the time to retrieve the plan from cache.
There was no Disabled: true indicator or a very high cost noted in earlier PostgreSQL versions for Seq Scan, indicating that enable_seqscan = 'off' was ineffective in this case.

A potential source of confusion is that enable_seqscan = 'off' appears in the Settings section, even though it was not used to produce the displayed plan. The Settings section shows planner-related GUC values active during EXPLAIN execution, which may differ from those active when a cached generic plan was created.

DDL invalidation

Prepared statements continue to use cached plans when query parameters change, but generic plans might be re-created if invalidated. DDL statements, such as adding a column, invalidate cached plans regardless of whether they depended on that column:

postgres=# alter table tasks add column description text;

ALTER TABLE

postgres=# explain (analyze, settings)
            execute c (false,  1)
;

                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=41.54..41.60 rows=25 width=17) (actual time=0.113..0.114 rows=1.00 loops=1)
   Buffers: shared hit=10
   ->  Sort  (cost=41.54..42.17 rows=250 width=17) (actual time=0.111..0.112 rows=1.00 loops=1)
         Sort Key: due
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=10
         ->  Bitmap Heap Scan on tasks  (cost=20.09..31.59 rows=250 width=17) (actual time=0.069..0.105 rows=5.00 loops=1)
               Recheck Cond: (id > 0)
               Filter: (done = $1)
               Rows Removed by Filter: 495
               Heap Blocks: exact=4
               Buffers: shared hit=10
               ->  Bitmap Index Scan on tasks_pkey  (cost=0.00..20.02 rows=500 width=0) (actual time=0.050..0.050 rows=500.00 loops=1)
                     Index Cond: (id > 0)
                     Index Searches: 1
                     Buffers: shared hit=6
 Settings: plan_cache_mode = 'force_generic_plan', enable_seqscan = 'off'
 Planning:
   Buffers: shared hit=23
 Planning Time: 0.300 ms
 Execution Time: 0.130 ms
(21 rows)

This time, enable_seqscan = 'off' was used because the prepared statement was re-planned, which effectively skipped Seq Scan in favor of a Bitmap Heap Scan.

Because the plan is generic, PostgreSQL cannot assume that $1 will always meet the partial index's predicate. Therefore, the partial index cannot be used, but the primary key index "tasks_pkey" contains entries for all rows and can be used when sequential scan is disabled.

Disabled: true (PostgreSQL 18)

I've run another DDL to remove the primary key and, consequently, the index, and the prepared statement is re-planned:

postgres=# alter table tasks drop constraint tasks_pkey;

ALTER TABLE

postgres=# explain (analyze, settings)
            execute c (false,  1)
;

                                                                                                        QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=21.46..21.52 rows=25 width=17) (actual time=0.054..0.055 rows=1.00 loops=1)
   Buffers: shared hit=4
   ->  Sort  (cost=21.46..22.08 rows=250 width=17) (actual time=0.052..0.052 rows=1.00 loops=1)
         Sort Key: due
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=4
         ->  Seq Scan on tasks  (cost=0.00..11.50 rows=250 width=17) (actual time=0.014..0.046 rows=5.00 loops=1)
               Disabled: true
               Filter: ((id > 0) AND (done = $1))
               Rows Removed by Filter: 495
               Buffers: shared hit=4
 Settings: plan_cache_mode = 'force_generic_plan', enable_seqscan = 'off'
 Planning:
   Buffers: shared hit=16 dirtied=2
 Planning Time: 0.251 ms
 Execution Time: 0.069 ms
(16 rows)

The mention of Disabled: true indicates that the disabled node was still in use because no alternative was available. The only index on this table is a partial index, which can be used only with a custom plan when parameter $1 is false, not with a standard plan.

In PostgreSQL 17, instead of Disabled: true, you would see an extremely high cost, indicating that disabled scans are deprioritized:

                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10000000021.46..10000000021.52 rows=25 width=17) (actual time=0.144..0.145 rows=1 loops=1)
   ->  Sort  (cost=10000000021.46..10000000022.08 rows=250 width=17) (actual time=0.143..0.143 rows=1 loops=1)
         Sort Key: due
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on tasks  (cost=10000000000.00..10000000011.50 rows=250 width=17) (actual time=0.098..0.136 rows=5 loops=1)
               Filter: ((id > 0) AND (done = $1))
               Rows Removed by Filter: 495
 Settings: plan_cache_mode = 'force_generic_plan', enable_seqscan = 'off', jit = 'off'
 Planning Time: 0.014 ms
 Execution Time: 0.167 ms
(10 rows)

It's important to determine if the EXECUTE-time parameters were applied to the plan, as the Settings section can be misleading. It displays the parameters set during the execution explanation, but they only affect the plan if re-planning occurs. EXPLAIN does not reveal the parameters that were used to establish the cached plan.

In an upcoming blog post about pg_plan_advice, I'll share a different approach to guide PostgreSQL 19's query planner. We will see that changing pg_plan_advice.advice keeps behavior consistent because it doesn't invalidate cached plans, as we've observed with enable_seqscan. However, using EXPLAIN (plan_advice) shows the hints used during planning, which differ slightly from the settings output.

EXECUTE is doing the planning

These examples clearly show that the important planner settings are those active when PostgreSQL creates a plan, not necessarily when the statement is prepared. If a prepared statement does not have a cached plan to reuse, planning happens during EXECUTE.

The initial EXECUTE might set a generic plan that is reused in later executions until the cache entry expires. To verify this, I modify the parameters before the first EXECUTE:

postgres=# \c

You are now connected to database "postgres" as user "postgres".

postgres=# set plan_cache_mode=force_generic_plan;

SET

postgres=# prepare c(boolean, int) as
            select id, due, done from tasks
             where done = $1 and id > 0
             order by due limit $2
;

PREPARE

postgres=# set enable_seqscan to off;

SET

postgres=# explain (analyze, settings)
            execute c (false,  1)
;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=21.46..21.52 rows=25 width=17) (actual time=0.058..0.059 rows=1.00 loops=1)
   Buffers: shared hit=7
   ->  Sort  (cost=21.46..22.08 rows=250 width=17) (actual time=0.057..0.057 rows=1.00 loops=1)
         Sort Key: due
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=7
         ->  Seq Scan on tasks  (cost=0.00..11.50 rows=250 width=17) (actual time=0.011..0.043 rows=5.00 loops=1)
               Disabled: true
               Filter: ((id > 0) AND (done = $1))
               Rows Removed by Filter: 495
               Buffers: shared hit=4
 Settings: plan_cache_mode = 'force_generic_plan', enable_seqscan = 'off'
 Planning:
   Buffers: shared hit=92
 Planning Time: 0.358 ms
 Execution Time: 0.074 ms
(16 rows)

The mention Disabled: true results from setting enable_seqscan = 'off'. This parameter does not trigger a re-plan, so if a plan was available after PREPARE, it would have been used— but it wasn't. The EXECUTE command generated a plan because no cached plan existed.

Re-planning triggers

In short, a plan can be set and cached by the first EXECUTE that allows a generic plan (forced by plan cache mode or in auto after some executions) and reused by subsequent EXECUTE statements until the cached plan is invalidated. The next EXECUTE after invalidation will then set and cache a new plan, based on the query planner settings at that time.

Here are the events that trigger an automatic invalidation, causing the next EXECUTE to re-plan the prepared statement:

Condition	Re-planning	Internals (plancache.c)
DDL changes on referenced objects	Yes	Relcache/syscache inval callbacks
Statistics updates	Yes	Relcache inval callbacks
search_path change	Yes	Explicit check in `RevalidateCachedQuery`
Role change (with RLS)	Yes	Explicit check in `RevalidateCachedQuery`
row_security change (with RLS)	Yes	Explicit check in `RevalidateCachedQuery`
TransactionXmin advancement (transient plans)	Yes	Check in `CheckCachedPlan`
plan_cache_mode = force_custom_plan	Yes	Decision in `choose_custom_plan`
First 5 executions (auto mode)	Yes	Decision in `choose_custom_plan`

Note that when choosing between a generic or custom plan in auto, the entire cost comparison history remains intact across invalidations, and the 5-executions counter is not reset.

Conclusion

Planner configuration parameters affect plan creation, not execution. When using prepared statements, the crucial question isn't whether a setting was changed before PREPARE or before EXECUTE, but whether PostgreSQL produced a new plan or reused an existing plan from the cache.

For custom plans, the planner settings active during execution are used, since the statement is replanned each time. For generic plans, PostgreSQL might reuse a previously generated plan, rendering any subsequent planner changes ineffective until the cached plan is invalidated and rebuilt.

This means the Settings section of EXPLAIN can sometimes be misleading. It shows the current session settings for planner-related GUCs, but not necessarily the values that were used when the plan was originally generated.

You might wonder why the planning-time settings aren't shown explicitly. They are displayed when executing EXPLAIN, but the plan could have been created earlier with an EXECUTE without EXPLAIN. Storing this information for each EXECUTE would add unnecessary overhead, for the rare case that an EXPLAIN is called later.

When working with prepared statements, check whether PostgreSQL reused an existing plan or generated a new one. If specific query planner settings are required, set them before each execution, since the cache may contain a generic plan from earlier settings, or replan using the current parameters. To ensure consistent and reliable results, it's best to keep the settings uniform. Remember to reset the parameters afterward to prevent them from unintentionally affecting subsequent queries. In short, changing query planner parameters is useful for troubleshooting query performance, but it is not practical to use in applications.

Another approach is to use pg_hint_plan, which allows binding query planner parameters directly to a query with hints (/*+ Set(enable_seqscan off) */), rather than to a specific EXECUTE statement context. With hints, the settings travel with the query and are used consistently for all subsequent EXECUTE calls.

DEV Community: Franck Pachot

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

Multiple updates

Duplicate key violations

Heap-Only Tuples

Deferred unique constraint

Garbage collection

Conclusion

What happens when a PostgreSQL backend crashes?

PostgreSQL query planner parameters and prepared statements

Prepared statement

Generic plan

DDL invalidation

Disabled: true (PostgreSQL 18)

EXECUTE is doing the planning

Re-planning triggers

Conclusion

Graph nodes (vertices `()` )

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