DEV Community: Franck Pachot

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.

Extended RUM in DocumentDB: B-tree-like ordered scans for flexible BSON in PostgreSQL

Franck Pachot — Fri, 03 Jul 2026 10:35:08 +0000

The main challenge in document database indexing is the flexibility of fields: the same path can be a scalar, an array, nested, or missing. Despite this, an index must specify what it covers and the order in which rows can be produced. B-tree indexes work well for fixed-scalar columns, enabling prefix filtering and returning sorted rows. GIN and RUM inverted indexes support flexible, repeated values, but traditional GIN doesn't support range queries or sorting, and RUM ordering relies on distance operators on attached values rather than standard document-style ORDER BY field LIMIT n.

DocumentDB's Extended RUM closes that gap. It extends the RUM access method for compound document indexes by generating composite index terms from the indexed paths and applying an ordering transform during the scan. The result is an inverted, multikey-style index that can filter, sort, and stop at LIMIT in a single Index Scan, while preserving document semantics for arrays and missing fields.

PostgreSQL and RUM

Here is the table I created for my previous blog post, RUM—Storing More in the Index:


postgres=# \d articles
                                                                   Table "public.articles"

  Column   |            Type             | Collation | Nullable |                                           Default                                         
-----------+-----------------------------+-----------+----------+---------------------------------------------------------------------------------------------
 id        | integer                     |           | not null | nextval('articles_id_seq'::regclass)
 title     | text                        |           | not null |
 body      | text                        |           | not null |
 category  | text                        |           | not null |
 published | timestamp without time zone |           | not null |
 score     | integer                     |           | not null |
 tsv       | tsvector                    |           |          | generated always as (to_tsvector('simple'::regconfig, (title || ' '::text) || body)) stored

Indexes:

    "articles_pkey" PRIMARY KEY, btree (id)
    "idx_gin_tsv" gin (tsv)
    "idx_rum_multi" rum (tsv rum_tsvector_addon_ops, category, published) WITH (attach=published, "to"=tsv)

The RUM index supports filtering and ordering by distance:


--EXPLAIN (COSTS OFF, ANALYZE ON, BUFFERS ON, VERBOSE ON)  
SELECT id, published
 , published <=> '1970-01-01'::timestamp as " <=> 1970"
 , extract ( epoch from published )      as " epoch "
FROM articles
WHERE tsv @@ to_tsquery('english', 'postgresql')
  AND category = 'tech'
ORDER BY published <=> '1970-06-01'::timestamp
LIMIT 5
;

 id  |      published      |  <=> 1970  |       epoch
-----+---------------------+------------+-------------------
  20 | 2020-01-01 20:00:00 | 1577908800 | 1577908800.000000
  40 | 2020-01-02 16:00:00 | 1577980800 | 1577980800.000000
  60 | 2020-01-03 12:00:00 | 1578052800 | 1578052800.000000
  80 | 2020-01-04 08:00:00 | 1578124800 | 1578124800.000000
 100 | 2020-01-05 04:00:00 | 1578196800 | 1578196800.000000

(5 rows)

                                                                                                                                                                                                                                                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=70.167..70.177 rows=5 loops=1)
   Output: id, published, ((published <=> '1970-01-01 00:00:00'::timestamp without time zone)), (EXTRACT(epoch FROM published)), ((published <=> '1970-06-01 00:00:00'::timestamp without time zone))
   Buffers: shared hit=223, temp read=550 written=550
   ->  Index Scan using idx_rum_multi on public.articles (actual time=70.165..70.175 rows=5 loops=1)
         Output: id, published, (published <=> '1970-01-01 00:00:00'::timestamp without time zone), EXTRACT(epoch FROM published), (published <=> '1970-06-01 00:00:00'::timestamp without time zone)
         Index Cond: ((articles.tsv @@ '''postgresql'''::tsquery) AND (articles.category = 'tech'::text))
         Order By: (articles.published <=> '1970-06-01 00:00:00'::timestamp without time zone)
         Buffers: shared hit=223, temp read=550 written=550
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.124 ms
 Execution Time: 70.698 ms

(12 rows)

Although Order By is integrated into the Index Scan, temp read reveals that it isn't a straightforward ordered index traversal, unlike a B-tree. Internal RUM scan processes spilled over to temporary storage. The key point is that there's no PostgreSQL Sort node involved. However, this is still distance-based ordering rather than simple key ordering.

I used the distance operator <=> with a date earlier than any date in this table, so the query effectively retrieves the first five articles sorted by published date. RUM allows ordering by its distance operators on attached values, such as published <=> constant. This can resemble chronological ordering when the constant is outside the data range, but it isn't the same as a simple ORDER BY published.

If I use a basic ORDER BY in my query without applying the distance operator, I obtain the same result, but it takes longer to execute:


--EXPLAIN (COSTS OFF, ANALYZE ON, BUFFERS ON, VERBOSE ON)  
SELECT id, published
 , published <=> '1970-01-01'::timestamp as " <=> 1970"
 , extract ( epoch from published )      as " epoch "
FROM articles
WHERE tsv @@ to_tsquery('english', 'postgresql')
  AND category = 'tech'
ORDER BY published
LIMIT 5
;

 id  |      published      |  <=> 1970  |       epoch
-----+---------------------+------------+-------------------
  20 | 2020-01-01 20:00:00 | 1577908800 | 1577908800.000000
  40 | 2020-01-02 16:00:00 | 1577980800 | 1577980800.000000
  60 | 2020-01-03 12:00:00 | 1578052800 | 1578052800.000000
  80 | 2020-01-04 08:00:00 | 1578124800 | 1578124800.000000
 100 | 2020-01-05 04:00:00 | 1578196800 | 1578196800.000000

(5 rows)

                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=21870.366..21870.368 rows=5 loops=1)
   Output: id, published, ((published <=> '1970-01-01 00:00:00'::timestamp without time zone)), (EXTRACT(epoch FROM published))
   Buffers: shared hit=3 read=50215 written=1
   ->  Sort (actual time=21870.364..21870.366 rows=5 loops=1)
         Output: id, published, ((published <=> '1970-01-01 00:00:00'::timestamp without time zone)), (EXTRACT(epoch FROM published))
         Sort Key: articles.published
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=3 read=50215 written=1
         ->  Bitmap Heap Scan on public.articles (actual time=85.266..21852.624 rows=50000 loops=1)
               Output: id, published, (published <=> '1970-01-01 00:00:00'::timestamp without time zone), EXTRACT(epoch FROM published)
               Recheck Cond: ((articles.tsv @@ '''postgresql'''::tsquery) AND (articles.category = 'tech'::text))
               Heap Blocks: exact=50000
               Buffers: shared hit=3 read=50215 written=1
               ->  Bitmap Index Scan on idx_rum_multi (actual time=75.951..75.951 rows=50000 loops=1)
                     Index Cond: ((articles.tsv @@ '''postgresql'''::tsquery) AND (articles.category = 'tech'::text))
                     Buffers: shared hit=3 read=215
 Planning:
   Buffers: shared read=2
 Planning Time: 2.276 ms
 Execution Time: 21870.406 ms

(20 rows)

RUM's ordering mechanism uses the <=> distance operator, which measures distance from a reference point. This differs from simply using ORDER BY published. When you directly apply ORDER BY published, RUM defaults to a bitmap scan combined with sorting.

For a query involving 1 million articles filtered by words = 'postgresql' and category = 'tech' (which yields 50K matches here) and sorted by published, it reads all matching rows and sorts them, taking 21 seconds because it reads 50K heap blocks instead of stopping after finding just 5 rows like expected with ORDER BY ... LIMIT.

PostgreSQL and B-tree

In tables with a strict schema and no arrays, the solution is straightforward. B-tree indexes store entries in sorted order, enabling efficient filtering and retrieval. A composite B-tree index on (category, published) can filter for category='tech' and return results already ordered by published, eliminating the need for a separate sort step:

postgres=# CREATE INDEX idx_articles_category_published
           ON articles (category, published)
;

postgres=# EXPLAIN (COSTS OFF, ANALYZE ON, BUFFERS ON, VERBOSE ON)  
 SELECT id, published
  , published <=> '1970-01-01'::timestamp as " <=> 1970"
  , extract ( epoch from published )      as " epoch "
 FROM articles
 WHERE tsv @@ to_tsquery('english', 'postgresql')
   AND category = 'tech'
 ORDER BY published
 LIMIT 5
;

                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.036..0.050 rows=5 loops=1)
   Output: id, published, ((published <=> '1970-01-01 00:00:00'::timestamp without time zone)), (EXTRACT(epoch FROM published))
   Buffers: shared hit=9
   ->  Index Scan using idx_articles_category_published on public.articles (actual time=0.035..0.048 rows=5 loops=1)
         Output: id, published, (published <=> '1970-01-01 00:00:00'::timestamp without time zone), EXTRACT(epoch FROM published)
         Index Cond: (articles.category = 'tech'::text)
         Filter: (articles.tsv @@ '''postgresql'''::tsquery)
         Rows Removed by Filter: 20
         Buffers: shared hit=9
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.129 ms
 Execution Time: 0.065 ms

(13 rows)

This is fast because the (category, published) B-tree walks category='tech' entries in timestamp order, and the filter predicate happens to reject only 20 rows before finding 5 matches. If the text predicate were much more selective or poorly correlated with published, this plan could also scan many rows.

B-trees index only columns with well-typed scalar values. A B-tree can index extracted scalar expressions from JSONB, but it does not naturally support document-database multikey semantics, where the same path may be scalar, an array, nested, or absent. For that, you need an inverted/multikey-style index. Here, I skipped indexing "words" because I know it can contain an array, and it relies on the fact that "category" can contain only one value. This is true in SQL, where the schema is declared for the table, but not for a polymorphic document collection.

DocumentDB collection

To show the same access pattern with flexible documents, I use the DocumentDB extension for PostgreSQL. I create a collection to store the same data in a flexible schema:


SELECT documentdb_api.create_collection('db', 'articles');

SELECT documentdb_api.insert_one(
    'db',
    'articles',
    FORMAT(
        '{"_id": %s, "title": %s, "body": %s, "category": %s, "published": {"$date": {"$numberLong": "%s"}}, "score": %s, "words": %s}',
        to_json(id),
        to_json(title),
        to_json(body),
        to_json(category),
        (EXTRACT(EPOCH FROM published) * 1000)::bigint,
        to_json(score),
        to_json(tsvector_to_array(tsv))
    )::documentdb_core.bson
)
FROM articles;

SELECT documentdb_api_internal.create_indexes_non_concurrently('db',   
    '{ "createIndexes": "articles", "indexes": [ {   
        "key": { "words":1, "category": 1, "published": -1 },   
        "name": "idx_wrd_cat_pub"   
    } ] }',   
    true)
;

This is similar to the "articles" table and index, but in a collection where the data type and cardinality don't have to be declared in advance. The index definition doesn't need to know that "words" contains an array and that there's only one "category" per article.

Here is a similar query and its execution plan:


postgres=# SET documentdb_core.bsonUseEJson to true;

SET

postgres=# SELECT documentdb_api_catalog.bson_dollar_unwind(
 cursorpage, '$cursor.firstBatch'
) FROM documentdb_api.find_cursor_first_page(
    'db', '{
         "find": "articles",
         "filter": { "words": "postgresql", "category": "tech" },
         "sort": { "published": 1 },
         "limit": 5,
         "projection": { "_id": 1, "title": 1, "published": 1 }
}'::documentdb_core.bson
)
;
                                                                                                                                   bson_dollar_unwind

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------
 { "cursor" : { "id" : { "$numberLong" : "0" }, "ns" : "db.articles", "firstBatch" : { "_id" : { "$numberInt" : "20" }, "title" : "Article about postgresql and postgresql", "published" : { "$date" : { "$numberLong" : "1577908800000" } } } }, "ok" : { "$numberDouble" : "1.0" } }
 { "cursor" : { "id" : { "$numberLong" : "0" }, "ns" : "db.articles", "firstBatch" : { "_id" : { "$numberInt" : "40" }, "title" : "Article about postgresql and postgresql", "published" : { "$date" : { "$numberLong" : "1577980800000" } } } }, "ok" : { "$numberDouble" : "1.0" } }
 { "cursor" : { "id" : { "$numberLong" : "0" }, "ns" : "db.articles", "firstBatch" : { "_id" : { "$numberInt" : "60" }, "title" : "Article about postgresql and postgresql", "published" : { "$date" : { "$numberLong" : "1578052800000" } } } }, "ok" : { "$numberDouble" : "1.0" } }
 { "cursor" : { "id" : { "$numberLong" : "0" }, "ns" : "db.articles", "firstBatch" : { "_id" : { "$numberInt" : "80" }, "title" : "Article about postgresql and postgresql", "published" : { "$date" : { "$numberLong" : "1578124800000" } } } }, "ok" : { "$numberDouble" : "1.0" } }
 { "cursor" : { "id" : { "$numberLong" : "0" }, "ns" : "db.articles", "firstBatch" : { "_id" : { "$numberInt" : "100" }, "title" : "Article about postgresql and postgresql", "published" : { "$date" : { "$numberLong" : "1578196800000" } } } }, "ok" : { "$numberDouble" : "1.0" } }

(5 rows)

postgres=# EXPLAIN (COSTS OFF, ANALYZE ON, BUFFERS ON, VERBOSE ON)
SELECT documentdb_api_catalog.bson_dollar_unwind(
 cursorpage, '$cursor.firstBatch'
) FROM documentdb_api.find_cursor_first_page(
    'db', '{
         "find": "articles",
         "filter": { "words": "postgresql", "category": "tech" },
         "sort": { "published": 1 },
         "limit": 5,
         "projection": { "_id": 1, "title": 1, "published": 1 }
}'::documentdb_core.bson
)
;
                                                                                                                                                                                                                                                                                                                                                                                                     QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
 ProjectSet (actual time=0.294..0.295 rows=5 loops=1)
   Output: bson_dollar_unwind(cursorpage, '$cursor.firstBatch'::text)
   Buffers: shared hit=14
   ->  Function Scan on documentdb_api.find_cursor_first_page (actual time=0.284..0.284 rows=1 loops=1)
         Output: cursorpage, continuation, persistconnection, cursorid
         Function Call: documentdb_api.find_cursor_first_page('db'::text, '{ "find" : "articles", "filter" : { "words" : "postgresql", "category" : "tech" }, "sort" : { "published" : { "$numberInt" : "1" } }, "limit" : { "$numberInt" : "5" }, "projection" : { "_id" : {
"$numberInt" : "1" }, "title" : { "$numberInt" : "1" }, "published" : { "$numberInt" : "1" } } }'::bson, '0'::bigint)
         Buffers: shared hit=14
 Planning Time: 0.046 ms
 Execution Time: 0.308 ms

(9 rows)

This is extremely fast, reading only 14 buffers, including additional lookups inside find_cursor_first_page, and is as efficient as the B-tree index on the SQL table, but on a flexible document collection.

Extended RUM is an extension of the RUM access method that shares the same on-disk page layout but overrides the scan, ordering, and cost-estimation entry points. The key addition is the ordered composite index, which matches the features of a multi-key index in MongoDB.

The find_cursor_first_page function executes everything inside a C function, so PostgreSQL's EXPLAIN only sees it as a black-box Function Scan node. To see the internal plan, I use bson_aggregation_pipeline, which generates inline SQL that the planner can optimize and expose:

postgres=# EXPLAIN (COSTS OFF, ANALYZE ON, BUFFERS ON, VERBOSE ON)
SELECT document FROM documentdb_api_catalog.bson_aggregation_pipeline(
    'db',
    '{"aggregate": "articles", "pipeline": [
        {"$match": {  "words": "postgresql", "category": "tech" }},
        {"$sort": { "published": 1 }},
        {"$limit": 5},
        {"$project": { "_id": 1, "title": 1, "published": 1 }}
    ], "cursor": {}}'::documentdb_core.bson
);
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------
 Subquery Scan on agg_stage_3 (actual time=0.058..0.075 rows=5 loops=1)
   Output: documentdb_api_internal.bson_dollar_project(agg_stage_3.document, '{ "_id" : { "$numberInt" : "1" }, "title" : { "$numberInt" : "1" }, "published" : { "$numberInt" : "1" } }'::bson, '{ "now" : { "$date" : { "$numberLong" : "1783073064776" } } }'::bson)
   Buffers: shared hit=10
   ->  Limit (actual time=0.051..0.064 rows=5 loops=1)
         Output: collection.document, (bson_orderby(collection.document, '{ "published" : { "$numberInt" : "1" } }'::bson))
         Buffers: shared hit=10
         ->  Index Scan using idx_wrd_cat_pub on documentdb_data.documents_28 collection (actual time=0.050..0.062 rows=5 loops=1)
               Output: collection.document, bson_orderby(collection.document, '{ "published" : { "$numberInt" : "1" } }'::bson)
               Index Cond: ((collection.document @= '{ "words" : "postgresql" }'::bson) AND (collection.document @= '{ "category" : "tech" }'::bson))
               Order By: (collection.document OPERATOR(documentdb_api_internal.<>-|) '{ "published" : { "$numberInt" : "1" } }'::bson)
               Buffers: shared hit=10
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.305 ms
 Execution Time: 0.107 ms

(15 rows)

The plan shows:

Index Scan using idx_wrd_cat_pub, the DocumentDB Extended RUM compound index on pathspec='[ "words", "category", { "published": -1 } ]'
Index Cond filter on words: "postgresql" and category = "tech", which are in the index prefix and can be scalars or arrays
Order By using the ordering <>-| operator on published: 1, which pushes the ascending sort to the index by scanning the descending index in reverse
Limit that stops after 5 rows, with no extra work

The result is the desired access pattern: 5 matching rows are returned after examining only 10 cached blocks, with no sort and 0.1 ms execution time.

The sort key path is extracted from the index term at scan time, matching the sort field to the appropriate position in the compound index and returning a comparable value that PostgreSQL can use for ordered retrieval. The result is an Index Scan (not a Bitmap Index Scan) that returns rows in the requested order directly from the inverted index.

DocumentDB's extended RUM achieves B-tree-like performance while retaining the inverted index's ability to handle flexible document fields — values that can be scalars, arrays, missing, or nested objects.

Like B-tree indexes, it can be used for both forward and backward lookups. In this example, I've created the index in descending order ({ "words": 1, "category": 1, "published": -1 }) but it was scanned in ascending order ({"$sort": {"published": 1}}).

Index Only Scan

Like multi-key indexes in MongoDB, when a field contains an array, the index stores one entry per array element (not one entry per document). The index cannot reconstruct the full array from a single entry, so the document must be fetched. However, when the query doesn't need any data beyond what the index condition already validates, an Index Only Scan is possible:

postgres=# SELECT documentdb_api_internal.create_indexes_non_concurrently('db',
    '{ "createIndexes": "articles", "indexes": [ {
        "key": { "category": 1, "published": 1 },
        "name": "idx_cat_pub"
    } ] }',
    true);

postgres=# VACUUM;

VACUUM

postgres=# EXPLAIN (COSTS OFF, ANALYZE ON, BUFFERS ON, VERBOSE ON)
SELECT document
FROM documentdb_api_catalog.bson_aggregation_pipeline(
    'db',
    '{
        "aggregate": "articles",
        "pipeline": [
            { "$match": { "category": "tech" } },
            { "$count": "count" }
        ],
        "cursor": {}
    }'::documentdb_core.bson
);

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (actual time=165.000..165.001 rows=1 loops=1)
   Output: bson_repath_and_build('count'::text, documentdb_api_internal.bsoncount(1))
   Buffers: shared hit=3172
   ->  Index Only Scan using idx_cat_pub on documentdb_data.documents_28 collection (actual time=0.032..149.004 rows=250000 loops=1)
         Output: collection.document
         Index Cond: (collection.document @= '{ "category" : "tech" }'::bson)
         Heap Fetches: 0
         Buffers: shared hit=3172
 Planning:
   Buffers: shared hit=10
 Planning Time: 0.197 ms
 Execution Time: 165.025 ms

(12 rows)

The $count only needs to count matching entries, not return field values. The index can confirm that category = "tech" matches without fetching the heap row. Heap Fetches: 0 because the visibility map confirms all rows are visible, so no heap fetch is needed. This requires the table to have been vacuumed, which is usually done by autovacuum. Counting 250,000 documents out of one million takes 165 milliseconds with an Index Only Scan. It would take 30 seconds if it had to fetch all documents with a Bitmap Heap Scan.

Extended RUM index

Here's an overview of the internal process:

Index term composition: When a document is added, DocumentDB creates a combined index term that encodes all relevant paths (such as category and published date) into a single serialized bytea entry within the RUM entry tree.
Ordered scan: During query execution, if the sort order matches an indexed path, the RUM scan applies the ordering transform function to retrieve the sort key from each composite term. This allows PostgreSQL's Index Scan node to navigate the index in the specified order, reading only necessary entries to meet the LIMIT.
Index-only scan support: If the query can be answered solely using index entries, PostgreSQL can bypass heap fetches when the visibility map indicates all heap pages are visible.
Forward and reverse traversal: The index supports both ascending and descending orderings, aligning with MongoDB's 1 and -1 sort specifications.

The execution plan reveals the internal table name, and you can look at the definition:


postgres=# \d documentdb_data.documents_28

                  Table "documentdb_data.documents_28"

     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_28" PRIMARY KEY, btree (shard_key_value, object_id)
    "documents_rum_index_69" documentdb_extended_rum (document documentdb_extended_rum_catalog.bson_extended_rum_composite_path_ops (pathspec='[ "words", "category", { "published" : -1 } ]', tl='2691'))
    "documents_rum_index_70" documentdb_extended_rum (document documentdb_extended_rum_catalog.bson_extended_rum_composite_path_ops (pathspec='[ "category", "published" ]', tl='2691'))

Check constraints:

    "shard_key_value_check" CHECK (shard_key_value = '28'::bigint)

The indexes created by the DocumentDB API are Extended RUM indexes: documentdb_extended_rum, available with the DocumentDB extension for PostgreSQL. They index a pathspec that includes a sort direction. tl='2691' is the truncation limit because PostgreSQL index entries are limited to about one-third of an 8KB block. Truncated terms still support filtering, with a recheck against the heap.

MongoDB-compatible multi-key index performance

I used the DocumentDB API in PostgreSQL to view the internal execution plan, but I can also run the same query using the MongoDB-compatible endpoint:

$ mongosh 'mongodb://ddb:ddb@localhost:10260/?tls=true&tlsAllowInvalidCertificates=true'

Current Mongosh Log ID: 6a4780a124863da54dd4b0c1
Connecting to:          mongodb://<credentials>@localhost:10260/?tls=true&tlsAllowInvalidCertificates=true&directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.3.7
Using MongoDB:          7.0.0
Using Mongosh:          2.3.7
mongosh 2.9.2 is available for download: https://www.mongodb.com/try/download/shell

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

test> use db;

switched to db db

db> db.articles.find(
 { "words": "postgresql", "category": "tech"}
).sort(
 { "published": 1 }
).limit(5);

[
  {
    _id: 20,
    title: 'Article about postgresql and postgresql',
    body: 'This is the body discussing postgresql in the context of postgresql. We also mention postgresql here.',
    category: 'tech',
    published: ISODate('2020-01-01T20:00:00.000Z'),
    score: 40,
    words: [ 'about', 'also', 'and', 'article', 'body', 'context', 'discussing', 'here', 'in', 'is', 'mention', 'of', 'postgresql', 'the', 'this', 'we' ]
  },
  {
    _id: 40,
    title: 'Article about postgresql and postgresql',
    body: 'This is the body discussing postgresql in the context of postgresql. We also mention postgresql here.',
    category: 'tech',
    published: ISODate('2020-01-02T16:00:00.000Z'),
    score: 80,
    words: [ 'about', 'also', 'and', 'article', 'body', 'context', 'discussing', 'here', 'in', 'is', 'mention', 'of', 'postgresql', 'the', 'this', 'we' ]
  },
  {
    _id: 60,
    title: 'Article about postgresql and postgresql',
    body: 'This is the body discussing postgresql in the context of postgresql. We also mention postgresql here.',
    category: 'tech',
    published: ISODate('2020-01-03T12:00:00.000Z'),
    score: 20,
    words: [ 'about', 'also', 'and', 'article', 'body', 'context', 'discussing', 'here', 'in', 'is', 'mention', 'of', 'postgresql', 'the', 'this', 'we' ]
  },
  {
    _id: 80,
    title: 'Article about postgresql and postgresql',
    body: 'This is the body discussing postgresql in the context of postgresql. We also mention postgresql here.',
    category: 'tech',
    published: ISODate('2020-01-04T08:00:00.000Z'),
    score: 60,
    words: [ 'about', 'also', 'and', 'article', 'body', 'context', 'discussing', 'here', 'in', 'is', 'mention', 'of', 'postgresql', 'the', 'this', 'we' ]
  },
  {
    _id: 100,
    title: 'Article about postgresql and postgresql',
    body: 'This is the body discussing postgresql in the context of postgresql. We also mention postgresql here.',
    category: 'tech',
    published: ISODate('2020-01-05T04:00:00.000Z'),
    score: 0,
    words: [ 'about', 'also', 'and', 'article', 'body', 'context', 'discussing', 'here', 'in', 'is', 'mention', 'of', 'postgresql', 'the', 'this', 'we' ]
  }
]

db> db.articles.find(
 { "words": "postgresql", "category": "tech"}
).sort(
 { "published": 1 }
).limit(5).explain("executionStats").executionStats;

{
  nReturned: Long('5'),
  executionTimeMillis: 0.067,
  executionStartAtTimeMillis: 0.054,
  totalDocsExamined: Long('5'),
  totalKeysExamined: Long('5'),
  executionStages: {
    stage: 'LIMIT',
    nReturned: Long('5'),
    executionTimeMillis: 0.067,
    executionStartAtTimeMillis: 0.054,
    totalDocsExamined: 5,
    totalKeysExamined: 5,
    numBlocksFromCache: 10,
    inputStage: {
      stage: 'FETCH',
      nReturned: Long('5'),
      executionTimeMillis: 0.065,
      executionStartAtTimeMillis: 0.053,
      totalKeysExamined: 5,
      numBlocksFromCache: 10,
      inputStage: {
        stage: 'IXSCAN',
        nReturned: Long('5'),
        executionTimeMillis: 0.065,
        executionStartAtTimeMillis: 0.053,
        indexName: 'idx_wrd_cat_pub',
        totalKeysExamined: 5,
        numBlocksFromCache: 10
      }
    }
  }
}

This query is a good example of an ESR pattern: Equality, Sort, Range, common in OLTP applications using MongoDB-style indexes. The MongoDB-compatible plan shows that we get the result from IXSCAN directly (totalKeysExamined: 5, nReturned: Long('5'),) in an inexpensive way (numBlocksFromCache: 10, executionTimeMillis: 0.065) without an additional sort operation, like in MongoDB.

Indexing flexible documents

In DocumentDB, the index supports equality on words and category, whether they are scalar or array items, ordered retrieval on published, and early termination at LIMIT 5, all in a single Index Scan.

The important contrast is with traditional PostgreSQL inverted indexes. GIN and classic RUM are excellent for matching flexible predicates, but they do not naturally provide index-ordered retrieval for document-style queries such as ORDER BY field LIMIT n. Without index-supported ordering, PostgreSQL must read all matching rows and sort them, even when only the first few rows are needed.

Extended RUM fills this gap. It keeps the multikey behavior expected from a document index while adding B-tree-like ordered traversal for compound document paths. This allows flexible document queries to efficiently combine filtering, ordering, and early LIMIT termination.

Testing other MongoDB emulations

Among the MongoDB-compatible services I tested, I haven't yet found equivalent support for index-ordered retrieval with multikey predicates and sorting. For instance, Oracle multi-value indexes can't keep the order from the index because they need to deduplicate keys after scanning (see here). I also tested Amazon DocumentDB on AWS (see here), and Google Firestore on GCP (see here), and both require reading more and sorting afterward.

If you want to test by yourself, here is how you can insert the same data from MongoSH to any MongoDB-compatible database:

use demo;
db.articles.drop();
const words = [ 'postgresql','indexing','performance','database', 'query','optimization','storage','replication', 'vacuum','analytics' ];
const categories = ['tech','science','blog','news'];
const batch = []; const start = new Date('2020-01-01T00:00:00Z');
for (let i = 1; i <= 1000000; i++) {
  const title = `Article about ${words[i % 10]} and ${words[(i * 7) % 10]}`;
  const body = `This is the body discussing ${words[(i * 3) % 10]}` + ` in the context of ${words[(i * 11) % 10]}.` + ` We also mention ${words[(i * 13) % 10]} here.`;
  const published = new Date(start.getTime() + i * 3600 * 1000);
  batch.push({ _id: i, title, body, category: categories[i % 4], published, score: (i * 17) % 100, words: [ ...new Set( [ 'about', 'article', 'body', 'context', 'discussing', 'here', 'mention', 'postgresql', words[i % 10], words[(i * 7) % 10], words[(i * 3) % 10], words[(i * 11) % 10], words[(i * 13) % 10] ].sort()) ] });
  if (batch.length === 10000) { db.articles.insertMany(batch); batch.length = 0; }
} ; if (batch.length) { db.articles.insertMany(batch); }
db.articles.createIndex({ "words":1, "category": 1, "published": -1});
db.articles.find( { "words": "postgresql", "category": "tech"}).sort( { "published": 1 }).limit(5).explain("executionStats")

You can run this and compare with the execution plan above: do you read only 5 index entries without additional scan and sort?

Conclusion

The interesting part is that Extended RUM wasn't designed as an academic indexing experiment. It comes from the practical requirements of building a document database on PostgreSQL while preserving MongoDB-compatible semantics and performance characteristics. Document workloads expect multikey indexes, ordered retrieval, and early termination with .sort().limit(), even when fields can be arrays, nested objects, or missing entirely. Those requirements led Microsoft engineers to extend PostgreSQL's indexing framework rather than introduce a separate storage engine: Extended RUM originated in the technology behind Azure Cosmos DB and is now available as part of the open-source DocumentDB extension for PostgreSQL. The project is part of the Linux Foundation and available at documentdb.io.

kernel asynchronous reads in PostgreSQL 19 (io_uring)

Franck Pachot — Wed, 01 Jul 2026 20:25:06 +0000

In the previous post, I executed a query that benefits from Asynchronous Sequential Scan. Although the OS-level read calls remain synchronous (pread64(),preadv()), PostgreSQL's IO workers issue them and manage the asynchronous IO queues. Linux provides asynchronous buffered I/O that PostgreSQL can use directly via the io_uring system calls.

In this post, I run the same query using the io_uring IO method instead of the worker. Because I am running inside a Docker container where Secure Computing Mode (seccomp) disables io_uring system calls, I started a container with seccomp disabled:

docker run -d --name pg19 \
  -p 5432:5432 \
  -e POSTGRES_PASSWORD=xxx \
  --security-opt seccomp=unconfined \
  postgres:19beta1 \
  -c io_method=io_uring

I connected (PGUSER=postgres PGPASSWORD=xxx PGHOST=localhost psql) and checked the configuration:


postgres=# \dconfig io_*

  List of configuration parameters
         Parameter         |  Value
---------------------------+----------
 io_combine_limit          | 128kB
 io_max_combine_limit      | 128kB
 io_max_concurrency        | 64
 io_max_workers            | 8
 io_method                 | io_uring
 io_min_workers            | 2
 io_worker_idle_timeout    | 1min
 io_worker_launch_interval | 100ms

(8 rows)

This is similar to the previous post, but with a different io_method. I will execute the same query that benefits from io_combine, not the one involving large TOASTed documents:

postgres=# explain (analyze, buffers, io, costs off)
select count(*),avg(length(data)) from smalldocs;

                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual time=941.539..943.440 rows=1.00 loops=1)
   Buffers: shared hit=15019 read=131281 dirtied=801 written=432
   ->  Gather (actual time=941.398..943.428 rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=15019 read=131281 dirtied=801 written=432
         ->  Partial Aggregate (actual time=939.501..939.502 rows=1.00 loops=3)
               Buffers: shared hit=15019 read=131281 dirtied=801 written=432
               ->  Parallel Seq Scan on smalldocs (actual time=0.033..155.375 rows=341333.33 loops=3)
                     Prefetch: avg=74.32 max=91 capacity=94
                     I/O: count=8247 waits=54 size=15.92 in-progress=4.97
                     Buffers: shared hit=15019 read=131281 dirtied=801 written=432
                     Worker 0:  Prefetch: avg=74.41 max=91 capacity=94
                       I/O: count=2695 waits=30 size=15.93 in-progress=4.98
                     Worker 1:  Prefetch: avg=73.99 max=91 capacity=94
                       I/O: count=2760 waits=13 size=15.88 in-progress=4.95
 Planning:
   Buffers: shared hit=5
 Planning Time: 0.094 ms
 Execution Time: 943.470 ms
(20 rows)

This plan is similar to the previous one because io combine, visible as prefetch, works the same for both the worker and io_uring. The difference now is that I no longer see any postgres: io worker processes, since this is managed by the kernel.

I used strace on the PostgreSQL backend and on parallel workers:


# echo 3 | sudo tee /proc/sys/vm/drop_caches &&
  strace -fyye trace=io_uring_enter,io_uring_setup,io_uring_enter,io_uring_register -s 0 -qq \
         -p $(pgrep -fd, "postgres: ") -T -o /dev/stdout

2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000016>
2143284 io_uring_enter(4, 0, 1, IORING_ENTER_GETEVENTS, NULL, 8) = -1 EINTR (Interrupted system call) <0.000307>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000019>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000025>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000025>
2143284 io_uring_enter(4, 0, 1, IORING_ENTER_GETEVENTS, NULL, 8) = 0 <0.001068>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000034>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000041>
2143284 io_uring_enter(4, 0, 1, IORING_ENTER_GETEVENTS, NULL, 8) = 0 <0.000714>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000024>
2143284 io_uring_enter(4, 0, 1, IORING_ENTER_GETEVENTS, NULL, 8) = 0 <0.000720>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000028>
2143284 io_uring_enter(4, 0, 1, IORING_ENTER_GETEVENTS, NULL, 8) = 0 <0.000505>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000015>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000046>
2143284 io_uring_enter(4, 0, 1, IORING_ENTER_GETEVENTS, NULL, 8) = 0 <0.000406>
2143284 io_uring_enter(4, 2, 0, 0, NULL, 8) = 2 <0.000043>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000025>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000088>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000209>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000096>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000020>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000018>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000026>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000024>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000017>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000016>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000031>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000053>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000022>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000013>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000020>
2143284 io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 <0.000016>
2143284 io_uring_enter(4, 0, 1, IORING_ENTER_GETEVENTS, NULL, 8) = 0 <0.000560>
...

The syscall is io_uring_enter(fd, to_submit, min_complete, flags, sig, sigsz), so:

io_uring_enter(4, 1, 0, 0, NULL, 8) = 1 indicates: Kernel, here is one new I/O request from my submission queue. I do not want to wait. The kernel confirms that one submission was consumed.
io_uring_enter(4, 0, 1, IORING_ENTER_GETEVENTS, NULL, 8) = 0 indicates: I am not submitting anything. Wait until at least one completion is available. The return value is zero because no new submissions were made by this call. The short elapsed time shows that the completion was available quickly.

The io_uring trace reveals that PostgreSQL does not wait for individual read operations. Instead, the backend consistently submits requests via io_uring_enter(..., 1, 0, ...) and retrieves completed requests from the completion queue with io_uring_enter(..., 0, 1, IORING_ENTER_GETEVENTS, ...). Most completions occur immediately, suggesting that the read stream maintains sufficient I/O activity to make results available when needed. This behavior aligns with the EXPLAIN statistics, which show a deep prefetch queue, large combined reads, and minimal waiting despite thousands of I/O operations.

The difference from the worker implementation is not what PostgreSQL reads, but how those reads are submitted and completed:

io_mode=sync	io_mode=worker	io_mode=io_uring
postgres backend	postgres backend	postgres backend
↳ `pread64()` or `pread64()`	(async)↳ postgres: io worker	(async)↳ `io_uring_enter()`
↳ kernel	↳ `pread64()` or `pread64()`	↳ kernel
	↳ kernel

I have run the same with five parallel PostgreSQL query workers:

postgres=# \! echo 3 | sudo tee /proc/sys/vm/drop_caches
3
postgres=# set max_parallel_workers_per_gather = 5;
SET
postgres=# explain (analyze, buffers, io, settings, costs off)
select count(*),avg(length(data)) from smalldocs;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual time=48293.834..48295.976 rows=1.00 loops=1)
   Buffers: shared hit=9776 read=136512
   ->  Gather (actual time=48271.604..48295.958 rows=6.00 loops=1)
         Workers Planned: 5
         Workers Launched: 5
         Buffers: shared hit=9776 read=136512
         ->  Partial Aggregate (actual time=48237.365..48237.365 rows=1.00 loops=6)
               Buffers: shared hit=9776 read=136512
               ->  Parallel Seq Scan on smalldocs (actual time=1.863..47814.487 rows=170666.67 loops=6)
                     Prefetch: avg=80.29 max=92 capacity=94
                     I/O: count=8661 waits=8429 size=15.76 in-progress=4.97
                     Buffers: shared hit=9776 read=136512
                     Worker 0:  Prefetch: avg=82.96 max=91 capacity=94
                       I/O: count=1434 waits=1418 size=15.95 in-progress=4.93
                     Worker 1:  Prefetch: avg=80.43 max=91 capacity=94
                       I/O: count=1406 waits=1394 size=15.87 in-progress=4.89
                     Worker 2:  Prefetch: avg=77.46 max=92 capacity=94
                       I/O: count=1428 waits=1384 size=15.66 in-progress=4.91
                     Worker 3:  Prefetch: avg=81.88 max=91 capacity=94
                       I/O: count=1451 waits=1417 size=15.69 in-progress=5.02
                     Worker 4:  Prefetch: avg=76.47 max=91 capacity=94
                       I/O: count=1434 waits=1395 size=15.69 in-progress=5.03
 Settings: max_parallel_workers_per_gather = '5'
 Planning Time: 0.060 ms
 Execution Time: 48296.009 ms
(25 rows)

On average, each process has 5 I/O operations in progress, for a total of 30. As execution continues, the load average rises because uninterruptible waits are counted alongside runnable tasks:

top - 15:32:43 up 23 days, 40 min,  1 user,  load average: 25.11, 10.84, 4.17
Threads: 1031 total,   1 running, 1030 sleeping,   0 stopped,   0 zombie
%Cpu(s):  2.4 us,  1.5 sy,  0.0 ni,  0.0 id, 95.3 wa,  0.1 hi,  0.7 si,  0.0 st
GiB Mem :     23.6 total,     14.9 free,      5.9 used,      2.8 buff/cache
GiB Swap:      4.0 total,      3.8 free,      0.2 used.     14.0 avail Mem

    PID USER        VIRT S  %CPU  %MEM     TIME+ COMMAND                                                                                                                                                                       WCHAN
2187030 root        0.0m I   1.3   0.0   0:04.41 [kworker/u8:4-iscsi_q_1]                                                                                                                                                      -
2211481 opc       221.9m R   1.3   0.0   0:02.65 top                                                                                                                                                                           -
2143284 100998    251.3m S   1.0   0.7   0:47.66 postgres: postgres postgres 10.0.2.100(35862) EXPLAIN                                                                                                                         arm64_sys+
2212667 100998    247.1m S   1.0   0.2   0:00.38 postgres: parallel worker for PID 73                                                                                                                                          arm64_sys+
2212669 100998    247.1m S   1.0   0.1   0:00.39 postgres: parallel worker for PID 73                                                                                                                                          arm64_sys+
2212670 100998    247.1m S   1.0   0.1   0:00.39 postgres: parallel worker for PID 73                                                                                                                                          arm64_sys+
2212671 100998    247.1m S   1.0   0.2   0:00.39 postgres: parallel worker for PID 73                                                                                                                                          arm64_sys+
2212668 100998    247.1m S   0.7   0.2   0:00.40 postgres: parallel worker for PID 73                                                                                                                                          arm64_sys+
2212257 100998    251.3m D   0.3   0.7   0:00.07 postgres: postgres postgres 10.0.2.100(35862) EXPLAIN                                                                                                                         generic_f+
2210102 root        0.0m I   0.3   0.0   0:01.36 [kworker/u8:1-xfs-cil/sdb]                                                                                                                                                    -
2212682 100998    247.1m D   0.3   0.2   0:00.02 postgres: parallel worker for PID 73                                                                                                                                          generic_f+
2212694 100998    247.1m D   0.3   0.2   0:00.02 postgres: parallel worker for PID 73                                                                                                                                          generic_f+
2212794 100998    247.1m D   0.3   0.2   0:00.01 postgres: parallel worker for PID 73                                                                                                                                          generic_f+
4157944 opc        23.1m D   0.3   0.1  18:26.08 /usr/bin/fuse-overlayfs -o lowerdir=/data/opc/share/containers/storage/overlay/l/JU5OB2S2NJVEHXCBJJ2RUU7R4M:/data/opc/share/containers/storage/overlay/l/SCFJOZKVJCNPWIHHO5L+ wait_on_p+
      1 root      380.1m S   0.0   0.1   7:11.80 /usr/lib/systemd/systemd --switched-root --system --deserialize 18                                                                                                            -

The behavior with io_uring is subtler than with the worker method. With synchronous pread64() or preadv(), the calling process blocks until the read completes and may enter uninterruptible sleep (D state) during the I/O. Here is top when running io_method=worker and max_parallel_workers_per_gather = 5:

top - 18:06:57 up 23 days,  3:14,  1 user,  load average: 8.15, 8.05, 4.48
Threads: 1014 total,   1 running, 1013 sleeping,   0 stopped,   0 zombie
%Cpu(s):  2.3 us,  3.3 sy,  0.0 ni,  3.5 id, 89.8 wa,  0.2 hi,  0.9 si,  0.0 st
MiB Mem :  24132.3 total,  16070.1 free,   6064.6 used,   1997.6 buff/cache
MiB Swap:   4095.9 total,   3878.6 free,    217.3 used.  14712.0 avail Mem

    PID USER        VIRT  S  %CPU  %MEM     TIME+ COMMAND
2221405 opc       227264  R   1.3   0.0   1:57.53 top
2291054 100998    234816  S   1.3   0.1   0:00.23 postgres: parallel worker for PID 75
2291057 100998    234816  S   1.3   0.1   0:00.23 postgres: parallel worker for PID 75
2280024 root           0  I   1.0   0.0   0:05.86 [kworker/u8:1-iscsi_q_1]
2287675 100998    235968  S   1.0   0.7   0:06.82 postgres: postgres postgres 10.0.2.100(39936) EXPLAIN
1680775 opc        24896  D   0.7   0.1  59:06.99 /usr/bin/fuse-overlayfs -o lowerdir=/data/opc/share/containers/storage/overlay/l/JU5OB2S2NJVEHXCBJJ2RUU7R4M:/data/opc/share/containers/storage/overlay/l/SCFJOZKV+
2265340 root           0  I   0.7   0.0   0:09.13 [kworker/u8:0-iscsi_q_1]
2291053 100998    234816  D   0.7   0.1   0:00.23 postgres: parallel worker for PID 75
2291055 100998    234816  D   0.7   0.1   0:00.23 postgres: parallel worker for PID 75
2291056 100998    234816  D   0.7   0.1   0:00.23 postgres: parallel worker for PID 75
2287499 100998    231808  D   0.3   0.6   0:00.74 postgres: io worker 0
2287500 100998    231808  D   0.3   0.5   0:00.53 postgres: io worker 1
2288950 100998    231808  D   0.3   0.5   0:00.54 postgres: io worker 2
2288953 100998    231808  D   0.3   0.5   0:00.41 postgres: io worker 3
2288954 100998    231808  D   0.3   0.5   0:00.44 postgres: io worker 4
2288955 100998    231808  D   0.3   0.5   0:00.39 postgres: io worker 5

With io_uring, PostgreSQL submits requests via io_uring_enter() and can continue processing while those reads are in flight. It only waits when it needs completions that are not yet available.

When I increased the query to five parallel workers, the system's load average rose above 25, even though the CPUs were almost idle:


load average: 25.11, 10.84, 4.17
%Cpu(s): 2.4 us, 1.5 sy, 95.3 wa

If this load average were mostly due to runnable processes competing for the CPU, the CPUs would be busy. They are not: only about 4% of CPU time is spent running user or kernel code, while most time is spent waiting on I/O. On Linux, load average includes both runnable tasks (R) and tasks in uninterruptible sleep (D). This combination of a high load average, mostly idle CPUs, and dominant I/O wait suggests that the bottleneck is storage performance, not CPU capacity. Some waits appear as D-state tasks at the top, but others may be too short to capture in a snapshot yet still contribute to the scheduler's load accounting.

When systems start using io_uring, system administrators will need to keep an eye on things: a high load average without noticeable R or D states can be tricky to analyze.

At the PostgreSQL level, the IO wait class reflects different wait events for asynchronous IO. When using io_submit=worker, the backend waits on the io worker to complete with AioIoCompletion:

With io_submit=io_uring, the backend waits first for IO submission with AioIoSubmission, which is quick, and then for IO execution with AioIoCompletion:

To illustrate the traditional synchronous IO waits, DataFileRead, I executed the select on the TOASTed table from the previous blog post:

This serves as a reminder that asynchronous IO isn't always feasible.

Conclusion

PostgreSQL 19's asynchronous I/O is not about reading different table blocks. The same sequential scan is performed. For a large table, blocks are read from the buffer manager via the sequential-scan ring buffer, so the scan avoids flooding the entire shared buffer pool.

The difference is in how waiting is organized.

With io_method=worker, PostgreSQL backends delegate read requests to dedicated I/O worker processes. These workers issue synchronous pread64() calls, or preadv() when there's more than one contiguous size to read, and a worker process can block while the kernel completes each read.

With io_method=io_uring, PostgreSQL submits requests directly to the kernel via the io_uring submission queue. The kernel reports completed operations via the completion queue. PostgreSQL can therefore keep multiple reads in flight and usually consume completions as soon as they become available. If completions are not available when requested, the backend can still wait.

io_combine is independent of that choice. It still combines nearby block reads into larger I/O operations. The io_method determines how those operations are submitted and completed: either via PostgreSQL I/O workers using synchronous pread64() or preadv(), or via kernel-managed asynchronous I/O using io_uring.

The execution plans, traces, and system metrics tell the same story. PostgreSQL is not eliminating I/O waits. Instead, it hides much of that latency by combining reads, maintaining a deep prefetch pipeline, and keeping enough outstanding requests so that completions are often ready when the executor needs them. This approach works for operations where PostgreSQL can predict future page accesses, such as Sequential Scan, Bitmap Heap Scan, and Vacuum.

Multi-block buffered reads in PostgreSQL 19 (IO combine & prefetch)

Franck Pachot — Tue, 30 Jun 2026 21:00:32 +0000

PostgreSQL scans operate at the page level: the buffer manager fetches one 8 KB page (BLCKSZ) at a time, issuing one read per block (pread64), and waiting for the result (D state, which means uninterruptible sleep). The operating system may merge some of these requests through readahead, but PostgreSQL still generates many small I/O operations, leading to a high number of system calls on large scans. This is inefficient for heap table scans.

Operations like Seq Scan and Bitmap Heap Scan know which blocks they need in advance and can read them independently, unlike Index Scans, where each subsequent block depends on the previous one.

PostgreSQL 19 improves this with the new read stream layer. Instead of issuing one read per page, it groups adjacent blocks into larger I/O requests, up to io_combine_limit. The logical unit remains the 8 KB page, but physical I/O is no longer page-by-page. This limits the system call overhead by reducing context switches between user space and the kernel.

IO combining and prefetch

PostgreSQL 19 (currently in beta) introduces Asynchronous I/O (AIO), enabling non-blocking reads for operations involving multiple blocks. Instead of waiting for each read to finish before issuing the next, PostgreSQL can pipeline I/O requests using methods such as worker threads, io_uring, or a synchronous fallback. The AIO read pathway creates a look-ahead stream of block requests, grouping nearby blocks into larger I/O operations. This process attempts to coalesce adjacent blocks into a single request, subject to the io_combine_limit.

Prefetch or read-ahead still involves requesting blocks before they are needed, but with AIO, this is now integrated with asynchronous submission and batched reads, reducing reliance on implicit operating system read-ahead by issuing explicit asynchronous and batched reads.

We will look at asynchronous IO and io_uring in a future post. Meanwhile, you can read about it from the excellent The Internals of PostgreSQL by Hironobu Suzuki: https://www.interdb.jp/pg/pgsql08/05.html. In this post, I'll use the workers IO method, which still issues pread64() calls, but with variable size.

These improvements can be seen with EXPLAIN (ANALYZE, IO), which provides detailed I/O statistics.

PostgreSQL 19 (beta)

If you want to test the beta of PostgreSQL 19, here is how I start a container that exposes port 5432:

docker run -d --name pg19 \
 -d -p 5432:5432          \
 -e POSTGRES_PASSWORD=xxx \
 postgres:19beta1 postgres

If you read this later, use the release candidate or the final release. I'll update this post when PG19 is released if anything changes.

AIO configuration

I connect with PGUSER=postgres PGPASSWORD=xxx PGHOST=localhost psql and check the IO configuration:


postgres=# \dconfig io_*

  List of configuration parameters
         Parameter         | Value
---------------------------+--------
 io_combine_limit          | 128kB
 io_max_combine_limit      | 128kB
 io_max_concurrency        | 64
 io_max_workers            | 8
 io_method                 | worker
 io_min_workers            | 2
 io_worker_idle_timeout    | 1min
 io_worker_launch_interval | 100ms

(8 rows)

You may have heard about io_uring, a Linux I/O interface that provides true asynchronous I/O without requiring Direct I/O, unlike the legacy AIO interface. It’s not available everywhere, and I can’t use it from Docker here, as seccomp blocks it, but the worker method still enables concurrent reads and some I/O combining. It's the default in PG19.

With this configuration, between 2 and 8 workers can combine up to 128kB of IO reads, which is 16 blocks, since the block size is 8KB.

Seq Scan on small rows table (inline)

I create a "smalldocs" table and load it with 1,024,000 rows, each with a random 1KB text in the "data" column:

postgres=# create table "smalldocs" ( "id" bigserial, "data" text );

CREATE TABLE

postgres=# copy "smalldocs" ("data") from program $sh$
            base64 -w $((1024)) /dev/urandom | head -102400
            $sh$
           \watch c=10 i=0.01

COPY 102400
COPY 102400
COPY 102400
COPY 102400
COPY 102400
COPY 102400
COPY 102400
COPY 102400
COPY 102400
COPY 102400

postgres=# select count(*),avg(length(data)) from smalldocs;

  count  |          avg
---------+-----------------------
 1024000 | 1024.0000000000000000

(1 row)

postgres=# select pg_size_pretty(pg_total_relation_size('smalldocs'));

 pg_size_pretty
----------------
 1143 MB

(1 row)

I have a 1GB table. Reading it with a Seq Scan benefits from IO combine, and the IO format of EXPLAIN ANALYZE displays the prefetch statistics:


postgres=# explain (analyze, buffers, io, costs off)
select count(*),avg(length(data)) from smalldocs;
                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual time=990.149..992.613 rows=1.00 loops=1)
   Buffers: shared hit=282 read=146006
   ->  Gather (actual time=988.904..992.600 rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=282 read=146006
         ->  Partial Aggregate (actual time=986.997..986.998 rows=1.00 loops=3)
               Buffers: shared hit=282 read=146006
               ->  Parallel Seq Scan on smalldocs (actual time=0.103..135.652 rows=341333.33 loops=3)
                     Prefetch: avg=52.68 max=91 capacity=94
                     I/O: count=9208 waits=18 size=15.86 in-progress=3.61
                     Buffers: shared hit=282 read=146006
                     Worker 0:  Prefetch: avg=52.12 max=64 capacity=94
                       I/O: count=3522 waits=6 size=15.87 in-progress=3.78
                     Worker 1:  Prefetch: avg=73.13 max=91 capacity=94
                       I/O: count=3332 waits=7 size=15.87 in-progress=4.55
 Planning Time: 0.062 ms
 Execution Time: 992.647 ms

(18 rows)

This plan shows a parallel sequential scan efficiently scanning the 1GB table using PostgreSQL’s AIO read path. Each of the 3 parallel processes (leader + 2 workers) scans part of the table, and the read stream keeps a large look‑ahead, about 50 blocks on average (avg=52.68), so data is requested well before it is needed. Those blocks are grouped into larger I/O requests, around 16 blocks per read (size=15.86), so 128KB, which reduces overhead.

Because reads are submitted in advance, almost all I/O completes asynchronously: only 18 waits out of more than 8 thousand requests (count=9208 waits=18). At any time, a few I/Os are in flight (in-progress=3.61), keeping the storage busy.

In short, this is an ideal case for disk I/O: sequential access enables deep prefetching, combined reads, and very few stalls, so the scan runs close to I/O throughput limits rather than being blocked on individual reads.

Those reads can be traced with strace, they are pread64 and preadv calls from the postgres: io worker processes:

# strace -fyye trace=pread64,pwrite64,preadv \
  -p $(pgrep -fd, "postgres: io worker")  -s 0 -qq -o /dev/stdout

480027 pread64(14</var/lib/postgresql/19/docker/base/5/16385>, ""..., 8192, 0) = 8192
480027 pread64(14</var/lib/postgresql/19/docker/base/5/16385>, ""..., 16384, 8192) = 16384
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 2, 24576) = 32768
480027 pread64(14</var/lib/postgresql/19/docker/base/5/16385>, ""..., 65536, 57344) = 65536
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 6, 122880) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 5, 253952) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 4, 385024) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 4, 516096) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 6, 647168) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 5, 778240) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 5, 909312) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 6, 1040384) = 131072
480027 pread64(11</var/lib/postgresql/19/docker/base/5/2601>, ""..., 8192, 0) = 8192
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 3, 1171456) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 5, 1302528) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 6, 1433600) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 6, 1564672) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 4, 1695744) = 131072
480471 preadv(6</var/lib/postgresql/19/docker/base/5/16385>, [...], 7, 1826816) = 131072
480028 preadv(9</var/lib/postgresql/19/docker/base/5/16385>,  <unfinished ...>
480471 preadv(6</var/lib/postgresql/19/docker/base/5/16385>,  <unfinished ...>
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>,  <unfinished ...>
480028 <... preadv resumed>[...], 8, 2220032) = 131072
480471 <... preadv resumed>[...], 2, 1957888) = 131072
480027 <... preadv resumed>[...], 5, 2088960) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 5, 2351104) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 4, 2482176) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 7, 2613248) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 2, 2744320) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 4, 2875392) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>,  <unfinished ...>
480473 preadv(6</var/lib/postgresql/19/docker/base/5/16385>,  <unfinished ...>
480472 preadv(6</var/lib/postgresql/19/docker/base/5/16385>,  <unfinished ...>
480028 preadv(9</var/lib/postgresql/19/docker/base/5/16385>,  <unfinished ...>
480027 <... preadv resumed>[...], 7, 3006464) = 131072
480473 <... preadv resumed>[...], 5, 3137536) = 131072
480472 <... preadv resumed>[...], 5, 3268608) = 131072
480028 <... preadv resumed>[...], 8, 3399680) = 131072
480028 preadv(9</var/lib/postgresql/19/docker/base/5/16385>, [...], 2, 3530752) = 131072
480028 preadv(9</var/lib/postgresql/19/docker/base/5/16385>, [...], 5, 3661824) = 131072
480027 preadv(14</var/lib/postgresql/19/docker/base/5/16385>, [...], 7, 3792896) = 131072
...

Here, strace shows PostgreSQL I/O workers issuing pread64 and preadv calls on the table file, with most reads at 131072 bytes (128KB), corresponding to 16 PostgreSQL pages. This confirms that sequential scan uses I/O combining, grouping multiple 8KB blocks into larger reads. If they are all contiguous, it's a pread64 call. If they are not contiguous. it's a preadv call. Multiple calls are marked as and later resumed, showing that reads are in flight concurrently. This matches the asynchronous I/O model: requests are submitted ahead of time, and completion is picked up later, rather than waiting for each read. Occasional smaller reads (8KB, 16KB, 32KB) appear at boundaries or when combining is not possible, but the dominant pattern is large, aligned reads. Overall, the trace confirms what EXPLAIN reports:

reads are combined into larger I/O (about 128KB)
multiple I/Os are issued in parallel (pipelining)
backend rarely waits, as I/O completes asynchronously

This is a direct observation of PostgreSQL AIO read streams: look‑ahead + I/O combining + concurrent execution, achieving high throughput on sequential scans.

Seq Scan on oversized rows table (TOASTed)

I create another similar table, "largedocs", and load it with fewer and larger rows. My goal is to show what happens when TOAST kicks in with large extended data types. I load 1000 rows, each with a random 1MB text in the "data" column:

postgres=# create table "largedocs" ( "id" bigserial, "data" text );

CREATE TABLE

postgres=# copy "largedocs" ("data") from program $sh$
            base64 -w $((1024*1024)) /dev/urandom | head -100
            $sh$
           \watch c=10 i=0.01

COPY 100
COPY 100
COPY 100
COPY 100
COPY 100
COPY 100
COPY 100
COPY 100
COPY 100
COPY 100

postgres=# select count(*),avg(length(data)) from largedocs;

 count |         avg
-------+----------------------
  1000 | 1048576.000000000000

(1 row)

postgres=# select pg_size_pretty(pg_total_relation_size('largedocs'));

 pg_size_pretty
----------------
 1039 MB

(1 row)

I run the same query as before on this new table with fewer rows but TOASTed data:

postgres=# explain (analyze, buffers, io, costs off)
select count(*),avg(length(data)) from largedocs;

                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Aggregate (actual time=2856.953..2856.953 rows=1.00 loops=1)
   Buffers: shared hit=3432 read=132951
   ->  Seq Scan on largedocs (actual time=0.141..0.875 rows=1000.00 loops=1)
         Prefetch: avg=1.88 max=4 capacity=94
         I/O: count=4 waits=1 size=2.00 in-progress=1.00
         Buffers: shared read=8
 Planning Time: 0.053 ms
 Execution Time: 2856.978 ms

(8 rows)

Here, the sequential scan looks trivial, but most of the work is not in the main table. Only 1000 rows are scanned, and they are small (just TOAST pointers), so the Seq Scan itself does almost no I/O: only 8 blocks are read (shared read=8), with no parallelism and limited prefetching (avg=1.88).

However, execution time is much higher (2.8s) because each row requires fetching the TOASTed value to compute length(data). The access pattern is no longer sequential. Instead of scanning a contiguous stream of blocks, PostgreSQL performs a separate lookup into the TOAST table for each row.

Those reads are effectively random, so there is no opportunity for read‑ahead or I/O combining. The AIO read stream cannot build a pipeline, and PostgreSQL falls back to small reads driven by the executor, one TOAST value at a time.

I've left my strace running, and it shows only the four reads going to the IO workers (I used pgrep -f "postgres: io worker"), between 1 and 4 blocks (8kb and 32kb), which explains the average prefetching:

4021362 pread64(14</var/lib/postgresql/19/docker/base/5/16397>, ""..., 8192, 0) = 8192
4021362 pread64(14</var/lib/postgresql/19/docker/base/5/16397>, ""..., 16384, 8192) = 16384
4021362 pread64(14</var/lib/postgresql/19/docker/base/5/16397>, ""..., 32768, 24576) = 32768
4021362 pread64(14</var/lib/postgresql/19/docker/base/5/16397>, ""..., 8192, 57344) = 8192

The strace confirms this behavior. The I/O workers handle only a few reads on the main table, typically 1-4 blocks (8KB to 32KB), not the relation that holds the TOAST chunks.

I can check that the relation base/5/16397 is the table "largedocs":

postgres=# select c.oid, relkind, amname, relname 
            from pg_class c join pg_am a on c.relam = a.oid 
            where c.oid>='smalldocs'::regclass order by c.oid
;

  oid  | relkind | amname |       relname
-------+---------+--------+----------------------
 16389 | r       | heap   | smalldocs
 16394 | t       | heap   | pg_toast_16389
 16395 | i       | btree  | pg_toast_16389_index
 16397 | r       | heap   | largedocs
 16402 | t       | heap   | pg_toast_16397
 16403 | i       | btree  | pg_toast_16397_index

(6 rows)

When tracing all PostgreSQL backends (using pgrep -f "postgres: " processes), the actual workload appears: a large number of 8KB pread64 calls on the TOAST table (base/5/16402). These reads are small and not combined, even if apparently contiguous:

# strace -fyye trace=pread64,pwrite64,preadv -s 0 -qq  \
  -p $(pgrep -fd, "postgres: ") -o /dev/stdout

4004803 pread64(33</var/lib/postgresql/19/docker/base/5/16397>, ""..., 8192, 0) = 8192
4004969 pread64(84</var/lib/postgresql/19/docker/base/5/16403>,  <unfinished ...>
4004803 pread64(33</var/lib/postgresql/19/docker/base/5/16397>,  <unfinished ...>
4004969 <... pread64 resumed>""..., 8192, 24576) = 8192
4004803 <... pread64 resumed>""..., 16384, 8192) = 16384
4004969 pread64(84</var/lib/postgresql/19/docker/base/5/16403>, ""..., 8192, 8192) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 0) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 8192) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 16384) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 24576) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 32768) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 40960) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 49152) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 57344) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 65536) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 73728) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 81920) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 90112) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 98304) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 106496) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 114688) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 122880) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 131072) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 139264) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 147456) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 155648) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 163840) = 8192
4004969 pread64(83</var/lib/postgresql/19/docker/base/5/16402>, ""..., 8192, 172032) = 8192

This is the opposite of the previous example. With small rows, the sequential scan becomes a true streaming workload, where AIO can prefetch and combine I/O efficiently. With large TOASTed values, the same sequential scan degenerates into many small reads, in which PostgreSQL prefetching and I/O combining are not used.

eBPF (block layer)

At the syscall level, we saw how PostgreSQL issues fewer, larger reads, which reduce context switches. To see what actually reaches the storage device, we need to look at a lower layer.

To observe what actually reaches the storage device, I traced block I/O requests with eBPF. Because I use a block layer probe, block_rq_issue, it doesn’t show PostgreSQL logical reads, but it does show I/O requests after the filesystem, page cache, readahead, and request merging. First, I clear the cache to make sure reads hit the device, then I trace block requests and aggregate their sizes:

echo 3 | sudo tee /proc/sys/vm/drop_caches
sudo bpftrace -e '
tracepoint:block:block_rq_issue
{
  @bytes[args->bytes] = count();
}
interval:s:30
{
  exit();
}' |  sort -t: -k2 -rn | paste - - - - - -

On the sequential scan of "smalldocs", the distribution shows a wide range of request sizes. Large requests like 256KB, 512KB, or even 1MB appear frequently:

@bytes[1048576]: 782    @bytes[16384]: 672      @bytes[4096]: 547       @bytes[131072]: 277     @bytes[8192]: 170       @bytes[516096]: 130
@bytes[24576]: 93       @bytes[393216]: 78      @bytes[32768]: 77       @bytes[262144]: 52      @bytes[40960]: 46       @bytes[65536]: 40
@bytes[49152]: 40       @bytes[155648]: 34      @bytes[81920]: 32       @bytes[106496]: 32      @bytes[98304]: 31       @bytes[122880]: 31
@bytes[90112]: 30       @bytes[73728]: 30       @bytes[57344]: 28       @bytes[163840]: 27      @bytes[1044480]: 27     @bytes[237568]: 24
@bytes[139264]: 24      @bytes[114688]: 23      @bytes[196608]: 22      @bytes[188416]: 22      @bytes[147456]: 22      @bytes[180224]: 21
@bytes[172032]: 21      @bytes[229376]: 20      @bytes[524288]: 19      @bytes[253952]: 17      @bytes[212992]: 16      @bytes[204800]: 15
@bytes[12288]: 14       @bytes[221184]: 13      @bytes[303104]: 12      @bytes[245760]: 11      @bytes[69632]: 10       @bytes[376832]: 10
@bytes[360448]: 10      @bytes[270336]: 10      @bytes[786432]: 9       @bytes[655360]: 7       @bytes[294912]: 7       @bytes[278528]: 7
@bytes[638976]: 6       @bytes[401408]: 6       @bytes[20480]: 6        @bytes[917504]: 5       @bytes[53248]: 5        @bytes[425984]: 5
@bytes[327680]: 5       @bytes[319488]: 5       @bytes[258048]: 5       @bytes[1040384]: 5      @bytes[77824]: 4        @bytes[720896]: 4
@bytes[532480]: 4       @bytes[499712]: 4       @bytes[36864]: 4        @bytes[344064]: 4       @bytes[311296]: 4       @bytes[286720]: 4
@bytes[217088]: 4       @bytes[135168]: 4       @bytes[1032192]: 4      @bytes[991232]: 3       @bytes[983040]: 3       @bytes[925696]: 3
@bytes[86016]: 3        @bytes[851968]: 3       @bytes[802816]: 3       @bytes[770048]: 3       @bytes[745472]: 3       @bytes[61440]: 3
@bytes[573440]: 3       @bytes[540672]: 3       @bytes[512000]: 3       @bytes[507904]: 3       @bytes[491520]: 3       @bytes[458752]: 3
@bytes[450560]: 3       @bytes[434176]: 3       @bytes[405504]: 3       @bytes[385024]: 3       @bytes[380928]: 3       @bytes[368640]: 3
@bytes[352256]: 3       @bytes[335872]: 3       @bytes[274432]: 3       @bytes[266240]: 3       @bytes[249856]: 3       @bytes[208896]: 3
@bytes[184320]: 3       @bytes[159744]: 3       @bytes[999424]: 2       @bytes[966656]: 2       @bytes[884736]: 2       @bytes[876544]: 2
@bytes[819200]: 2       @bytes[782336]: 2       @bytes[761856]: 2       @bytes[757760]: 2       @bytes[753664]: 2       @bytes[729088]: 2
@bytes[724992]: 2       @bytes[647168]: 2       @bytes[643072]: 2       @bytes[626688]: 2       @bytes[585728]: 2       @bytes[557056]: 2
@bytes[45056]: 2        @bytes[442368]: 2       @bytes[421888]: 2       @bytes[339968]: 2       @bytes[323584]: 2       @bytes[307200]: 2
@bytes[282624]: 2       @bytes[167936]: 2       @bytes[118784]: 2       @bytes[110592]: 2       @bytes[1036288]: 2      @bytes[1028096]: 2
@bytes[1024000]: 2      @bytes[1007616]: 2      @bytes[970752]: 1       @bytes[958464]: 1       @bytes[950272]: 1       @bytes[94208]: 1
@bytes[942080]: 1       @bytes[933888]: 1       @bytes[909312]: 1       @bytes[905216]: 1       @bytes[901120]: 1       @bytes[892928]: 1
@bytes[860160]: 1       @bytes[843776]: 1       @bytes[839680]: 1       @bytes[835584]: 1       @bytes[827392]: 1       @bytes[811008]: 1
@bytes[806912]: 1       @bytes[798720]: 1       @bytes[794624]: 1       @bytes[765952]: 1       @bytes[749568]: 1       @bytes[733184]: 1
@bytes[712704]: 1       @bytes[692224]: 1       @bytes[671744]: 1       @bytes[659456]: 1       @bytes[630784]: 1       @bytes[622592]: 1
@bytes[614400]: 1       @bytes[602112]: 1       @bytes[593920]: 1       @bytes[589824]: 1       @bytes[565248]: 1       @bytes[552960]: 1
@bytes[548864]: 1       @bytes[544768]: 1       @bytes[528384]: 1       @bytes[503808]: 1       @bytes[487424]: 1       @bytes[483328]: 1
@bytes[475136]: 1       @bytes[466944]: 1       @bytes[462848]: 1       @bytes[454656]: 1       @bytes[446464]: 1       @bytes[430080]: 1
@bytes[417792]: 1       @bytes[413696]: 1       @bytes[409600]: 1       @bytes[397312]: 1       @bytes[389120]: 1       @bytes[372736]: 1
@bytes[356352]: 1       @bytes[331776]: 1       @bytes[290816]: 1       @bytes[28672]: 1        @bytes[200704]: 1       @bytes[143360]: 1
@bytes[102400]: 1       @bytes[1011712]: 1      Attaching 2 probes...

On "largedocs", with TOASTed values that are read by PostgreSQL with 8kB reads, smaller sizes are more visible, but surprisingly large requests still appear at block level:

@bytes[1048576]: 893    @bytes[16384]: 586      @bytes[4096]: 519       @bytes[8192]: 291       @bytes[516096]: 122     @bytes[24576]: 63
@bytes[65536]: 13       @bytes[57344]: 13       @bytes[32768]: 10       @bytes[163840]: 10      @bytes[131072]: 9       @bytes[122880]: 9
@bytes[73728]: 8        @bytes[40960]: 8        @bytes[327680]: 7       @bytes[262144]: 7       @bytes[303104]: 6       @bytes[221184]: 6
@bytes[212992]: 6       @bytes[172032]: 6       @bytes[1044480]: 6      @bytes[1007616]: 6      @bytes[90112]: 5        @bytes[671744]: 5
@bytes[61440]: 5        @bytes[532480]: 5       @bytes[524288]: 5       @bytes[49152]: 5        @bytes[237568]: 5       @bytes[188416]: 5
@bytes[155648]: 5       @bytes[114688]: 5       @bytes[98304]: 4        @bytes[901120]: 4       @bytes[868352]: 4       @bytes[786432]: 4
@bytes[69632]: 4        @bytes[647168]: 4       @bytes[557056]: 4       @bytes[475136]: 4       @bytes[450560]: 4       @bytes[401408]: 4
@bytes[368640]: 4       @bytes[352256]: 4       @bytes[286720]: 4       @bytes[270336]: 4       @bytes[245760]: 4       @bytes[20480]: 4
@bytes[12288]: 4        @bytes[1040384]: 4      @bytes[1015808]: 4      @bytes[991232]: 3       @bytes[876544]: 3       @bytes[86016]: 3
@bytes[835584]: 3       @bytes[761856]: 3       @bytes[737280]: 3       @bytes[720896]: 3       @bytes[696320]: 3       @bytes[688128]: 3
@bytes[679936]: 3       @bytes[614400]: 3       @bytes[581632]: 3       @bytes[53248]: 3        @bytes[466944]: 3       @bytes[442368]: 3
@bytes[360448]: 3       @bytes[335872]: 3       @bytes[294912]: 3       @bytes[278528]: 3       @bytes[229376]: 3       @bytes[180224]: 3
@bytes[147456]: 3       @bytes[135168]: 3       @bytes[118784]: 3       @bytes[106496]: 3       @bytes[1032192]: 3      @bytes[999424]: 2
@bytes[966656]: 2       @bytes[958464]: 2       @bytes[925696]: 2       @bytes[917504]: 2       @bytes[892928]: 2       @bytes[851968]: 2
@bytes[811008]: 2       @bytes[77824]: 2        @bytes[753664]: 2       @bytes[712704]: 2       @bytes[622592]: 2       @bytes[606208]: 2
@bytes[589824]: 2       @bytes[573440]: 2       @bytes[491520]: 2       @bytes[483328]: 2       @bytes[458752]: 2       @bytes[434176]: 2
@bytes[425984]: 2       @bytes[417792]: 2       @bytes[405504]: 2       @bytes[393216]: 2       @bytes[385024]: 2       @bytes[376832]: 2
@bytes[344064]: 2       @bytes[311296]: 2       @bytes[258048]: 2       @bytes[253952]: 2       @bytes[196608]: 2       @bytes[167936]: 2
@bytes[139264]: 2       @bytes[987136]: 1       @bytes[974848]: 1       @bytes[950272]: 1       @bytes[94208]: 1        @bytes[942080]: 1
@bytes[843776]: 1       @bytes[827392]: 1       @bytes[81920]: 1        @bytes[778240]: 1       @bytes[770048]: 1       @bytes[733184]: 1
@bytes[729088]: 1       @bytes[724992]: 1       @bytes[659456]: 1       @bytes[655360]: 1       @bytes[602112]: 1       @bytes[598016]: 1
@bytes[528384]: 1       @bytes[520192]: 1       @bytes[512000]: 1       @bytes[507904]: 1       @bytes[45056]: 1        @bytes[413696]: 1
@bytes[409600]: 1       @bytes[397312]: 1       @bytes[36864]: 1        @bytes[364544]: 1       @bytes[331776]: 1       @bytes[319488]: 1
@bytes[28672]: 1        @bytes[282624]: 1       @bytes[266240]: 1       @bytes[249856]: 1       @bytes[204800]: 1       @bytes[143360]: 1
@bytes[1024000]: 1      Attaching 2 probes...

This is because we are no longer looking at what PostgreSQL requests, but at what reaches the storage after the OS stack has optimized it, and because my TOAST chunks, inserted in bulk, are contiguous. The filesystem performs read-ahead, and the kernel can merge adjacent requests, producing larger I/O operations than those issued by PostgreSQL.

Importantly, this still uses buffered I/O through the filesystem cache. With Direct I/O, such merging would be much more limited, and request sizes would more closely reflect what the database issues.

This explains why both workloads can show similar block‑level patterns when the blocks read are contiguous.

Even when the block layer ends up issuing similar I/O sizes after merging, the syscall pattern still matters: fewer large reads mean fewer syscalls and fewer context switches, while many small reads increase CPU overhead.

In short, strace shows what PostgreSQL requests, while eBPF shows what actually reaches the device.

Conclusion

This highlights a simple rule: AIO helps when PostgreSQL can see and exploit a sequential access pattern. With rows stored inline, a Seq Scan becomes a streaming workload in which the read stream can prefetch ahead, combine blocks into larger I/O units, and pipeline requests efficiently.

However, with extended datatypes and large TOASTed values, the same scan turns into thousands of small lookups, similar to a nested loop with inner index access: no effective prefetching, no I/O combining, and almost no benefit from AIO. Only the lower-level storage layer can optimize this if the TOAST table is not too fragmented.

To understand what is happening at each layer, EXPLAIN shows intent, strace shows requests, and eBPF shows what actually reaches the device.

DEV Community: Franck Pachot

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

Extended RUM in DocumentDB: B-tree-like ordered scans for flexible BSON in PostgreSQL

PostgreSQL and RUM

PostgreSQL and B-tree

DocumentDB collection

Index Only Scan

Extended RUM index

MongoDB-compatible multi-key index performance

Indexing flexible documents

Testing other MongoDB emulations

Conclusion

kernel asynchronous reads in PostgreSQL 19 (io_uring)

Conclusion

Multi-block buffered reads in PostgreSQL 19 (IO combine & prefetch)

IO combining and prefetch

PostgreSQL 19 (beta)

AIO configuration

Seq Scan on small rows table (inline)

Seq Scan on oversized rows table (TOASTed)

eBPF (block layer)

Conclusion

Graph nodes (vertices `()` )

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