DEV Community: Franck Pachot

HorizonDB preview: automate a reproducible lab with ARM

Franck Pachot — Fri, 12 Jun 2026 20:47:11 +0000

Azure HorizonDB is a new database service on Azure. It is PostgreSQL-compatible from a developer perspective, but its storage layer differs to support built-in high availability and scalability.

Since it's currently in preview, APIs might evolve and defaults could change. This is a great opportunity to explore how well PostgreSQL works with your existing applications.

If you're setting up a lab for those tests, you don’t want to navigate the UI each time. You need something that can be recreated quickly and dismantled once finished.

Azure Resource Manager (ARM) is well suited for that.

Login to Azure

I installed the Azure CLI and logged in on my laptop:


az login

This opens the browser, lets me sign in interactively, and lets me select the tenant and subscription.

If I'm already logged in, I can get my subscription ID:


az account show --query id -o tsv

From there, everything can be automated via the CLI.

ARM template for HorizonDB preview

I created a parameterized template, in hdb-template.json:


{
    "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "apiVersion":         { "type": "string", "defaultValue": "2026-01-20-preview" },
        "clusterName":        { "type": "string" },
        "location":           { "type": "string" },
        "poolName":           { "defaultValue": "DefaultPool", "type": "string" },
        "administratorLogin": { "type": "string" },
        "administratorLoginPassword": { "type": "securestring" },
        "tags":               { "type": "object", "defaultValue": {} },
        "firewallRules":      { "type": "array", "defaultValue": [] },
        "guid":               { "type": "string", "defaultValue": "[newGuid()]" }
    },
    "resources": [
        {
            "type": "Microsoft.HorizonDB/clusters",
            "apiVersion": "[parameters('apiVersion')]",
            "name": "[parameters('clusterName')]",
            "location": "[parameters('location')]",
            "properties": {
                "createMode":                 "Default",
                "version":                    "17",
                "zonePlacementPolicy":        "BestEffort",
                "replicaCount":               2,
                "vCores":                     2,
                "administratorLogin":         "[parameters('administratorLogin')]",
                "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
                "network":                    { "publicNetworkAccess": "Enabled" },
                "aiModelManagement":          1
            },
            "tags":                           "[parameters('tags')]"
        },
        {
            "condition": "[greater(length(parameters('firewallRules')), 0)]",
            "type": "Microsoft.Resources/deployments",
            "apiVersion": "2019-08-01",
            "name": "[concat('firewallRules-', parameters('guid'), '-', copyIndex())]",
            "copy": {
                "count": "[if(greater(length(parameters('firewallRules')), 0), length(parameters('firewallRules')), 1)]",
                "mode": "Serial",
                "name": "firewallRulesIterator"
            },
            "dependsOn": [
                "[concat('Microsoft.HorizonDB/clusters/', parameters('clusterName'))]"
            ],
            "properties": {
                "mode": "Incremental",
                "template": {
                    "$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",
                    "contentVersion": "1.0.0.0",
                    "resources": [
                        {
                            "type": "Microsoft.HorizonDB/clusters/pools/firewallRules",
                            "name": "[concat(parameters('clusterName'),'/',parameters('poolName'),'/',parameters('firewallRules')[copyIndex()].name)]",
                            "apiVersion": "[parameters('apiVersion')]",
                            "properties": {
                                "Description": "[parameters('firewallRules')[copyIndex()].name]",
                                "StartIpAddress": "[parameters('firewallRules')[copyIndex()].startIpAddress]",
                                "EndIpAddress": "[parameters('firewallRules')[copyIndex()].endIpAddress]"
                            }
                        }
                    ]
                }
            }
        }
    ]
}

This template defines the HorizonDB cluster and its configuration. I generated it the first time I manually created a cluster via the UI portal. I include the firewall rules in a loop to define multiple client IPs.

Keeping firewall rules in the template makes the lab fully reproducible, including network access, without requiring additional CLI commands.

HorizonDB parameters

In hdb-parameters.json I put all my parameters used by the ARM templates:

{
  "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentParameters.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "clusterName":                { "value": "franckpachot-hdb" },
    "location":                   { "value": "swedencentral" },
    "administratorLogin":         { "value": "franck" },
    "tags":          { "value":   { "Owner": "franckpachot" } },
    "firewallRules": {
      "value": [
        { "name": "home",  "startIpAddress": "192.0.2.10",    "endIpAddress": "192.0.2.20" },
        { "name": "shell", "startIpAddress": "52.178.13.135", "endIpAddress": "52.178.13.135" }
      ]
    }
  }
}

This file contains all environment-specific values: cluster name, region, allowed IP ranges, and admin user. You must update them. The regions where HorizonDB preview is available are listed here: https://learn.microsoft.com/en-us/azure/horizondb/overview#azure-regions

I do not store the password in the parameters file and pass it at deployment time.

Resource group

I create a resource group that will contain everything:


az group create --name franckpachot-rg --location swedencentral

This makes it easy to identify and delete everything related to the lab.

You will change the location to the region you chose above, and the resource group name will be used in the next command.

Deploy the cluster

A single command deploys the cluster from the template:


az deployment group create --resource-group franckpachot-rg --template-file hdb-template.json --parameters @hdb-parameters.json --parameters administratorLoginPassword=PostgreSQL@Azure

The template defines the infrastructure. The parameters define the environment, and the CLI command combines both.

The deployment takes about 15 minutes.

Retrieve the endpoint and connect

The endpoint is visible in the Azure portal, but it can also be retrieved directly from the CLI:


az resource show --resource-type Microsoft.HorizonDB/clusters --name franckpachot-hdb --resource-group franckpachot-rg --query properties.fullyQualifiedDomainName -o tsv

I set my PostgreSQL connection variables using the credentials used when creating it, and the hostname of the endpoint:

export PGPORT=5432
export PGUSER=franck
export PGPASSWORD=PostgreSQL@Azure
export PGDATABASE=postgres
export PGHOST=

You can then connect with psql or any PostgreSQL client, including the PostgreSQL extension for Visual Studio Code

Let's check the version banner and a few parameters:


$ psql
psql (16.2, server 17.9 (Azure HorizonDB (70f3b593ec7)(release)))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=> select version();

                                                        version
-----------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.9 (Azure HorizonDB (70f3b593ec7)(release)) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 13.2.0, 64-bit

(1 row)

postgres=> \dconfig azure*

                List of configuration parameters

                 Parameter                  |       Value
--------------------------------------------+-------------------
 azure.accepted_password_auth_method        | md5,scram-sha-256
 azure.enable_temp_tablespaces_on_local_ssd | on
 azure.extensions                           |
 azure.fabric_mirror_enabled                | off
 azure.service_principal_id                 |
 azure.service_principal_tenant_id          |
(6 rows)

postgres=> \dconfig shared*

                                        List of configuration parameters

            Parameter             |                                    Value
----------------------------------+------------------------------------------------------------------------------
 shared_buffers                   | 11241MB
 shared_memory_size               | 11776MB
 shared_memory_size_in_huge_pages | 5888
 shared_memory_type               | mmap
 shared_preload_libraries         | azure, orion_storage, pg_availability, pg_qs, pgms_stats, pgms_wait_sampling

(5 rows)

postgres=> show effective_cache_size;
 effective_cache_size
----------------------
 11241MB
(1 row)

postgres=> show full_page_writes;
 full_page_writes
------------------
 off
(1 row)

An interesting aspect is the memory setup. On a 2 vCore, 16 GiB RAM instance, shared_buffers is allocated about 11 GB, which exceeds typical PostgreSQL recommendations.

Traditionally, PostgreSQL utilizes both its buffer cache and the OS filesystem cache. In HorizonDB with disaggregated storage, the PostgreSQL fork avoids double buffering, and shared_buffers and effective_cache_size are closely aligned.

Additionally, full_page_writes is disabled. This is atypical for PostgreSQL, where full page writes are essential for recovering from torn pages during WAL replay on traditional filesystems. However, this is justified in HorizonDB because the storage layer guarantees protection against partial page writes, and disabling it provides significant performance benefits.

To use extensions, create a parameter group and set azure.extensions. Managed services must control PostgreSQL extensibility because extensions can gain elevated system privileges.

Cleanup

The advantage of the resource group is that you can delete everything with a single command:


az group delete --name franckpachot-rg --yes

This removes the cluster and all associated resources.

Final thoughts

HorizonDB is currently in preview, and this is a perfect chance to test it!

With ARM and Azure CLI:

You can set up a lab in just 15 minutes
Test how PostgreSQL applications work with it
Explore various extensions and features
And easily clean everything up when you're done

This way, you can focus on what really matters: understanding how this PostgreSQL‑compatible platform performs with your applications and workloads. Please share your comments and feedback.

RUM—Storing More in the Index

Franck Pachot — Tue, 09 Jun 2026 06:12:39 +0000

This series of posts traces the evolution from GIN to RUM to Extended RUM, showing how a single architectural idea—store more in the index to do less work at query time—unlocks major performance improvements at each step.

RUM was created by Alexander Korotkov, Oleg Bartunov, and Teodor Sigaev at Postgres Professional. It started as a direct response to the GIN limitations that users kept hitting in production — particularly in full-text search scenarios where ranking and ordering dominate query time.

The name is a nod: GIN (the drink) → RUM (a stronger drink). The index is stronger, too.

RUM's innovation is exactly one architectural change: each entry in a posting list can carry an additional datum alongside the TID:

GIN posting list entry:  [TID]
RUM posting list entry:  [TID, addInfo]

A 2016 thread on pgsql-general captures the exact pain point. Andreas Krogh was building a web-based email system — millions of messages, users expecting millisecond response — and wanted a single index that could:

Match full-text search terms (fts_all @@ to_tsquery(...))
Filter by folder (folder_id = ANY(ARRAY[2,3]))
Sort by timestamp (ORDER BY sent DESC)
Stop early (LIMIT 101)

With GIN, steps 1 and 2 worked (via btree_gin), but step 3 always required a separate Sort node — the index couldn't deliver results in timestamp order. The planner had to scan all matching TIDs, fetch every heap tuple, and sort them. For a mailbox with 100K matches, that's unacceptable.

Oleg Bartunov's response was direct: "We are [working] hard on our internal version of rum." RUM was designed to solve exactly this class of problem.

RUM extends the GIN framework with an order_by_attach option:

CREATE INDEX idx ON documents USING rum (tsv rum_tsvector_addon_ops, created_at)
  WITH (attach = 'created_at', to = 'tsv', order_by_attach = true);

This tells RUM: "for every TID in the tsv posting list, also store the corresponding created_at value." The posting list entries become:


"postgresql" → [(0,1, 2024-01-15), (3,7, 2024-02-20), (5,2, 2024-03-01)]

Now the posting list is sorted by addInfo (the timestamp), not by the physical order of TIDs.

What RUM Enables Over GIN:

Ordered results without sorting. The index walks the posting list in addInfo order and stops after N entries. No Sort node, no full scan.
Distance-based ordering with <=>. RUM introduces distance operators (<=>, <=|, |=>). The <=> computes ABS(a - b) — nearest-first retrieval. The <=| and |=> variants restrict to one direction. For full-text ranking, RUM's <=> operator has a built-in ranking function that combines ts_rank and ts_rank_cd semantics and handles OR queries better than either function alone.
Depth-first traversal: first results immediately. Unlike GIN's bitmap approach (collect all TIDs, then access the heap), RUM performs a depth-first traversal. It can return first results immediately — critical for LIMIT queries.
Phrase search without recheck. RUM's rum_tsvector_ops stores word positions as addInfo. The index verifies phrase adjacency during the scan — no heap rechecks needed.

Here are the operator classes:

rum_tsvector_ops stores lexemes with positional information, supports ordering (<=>) by relevance
rum_tsvector_hash_ops stores hashed lexemes with positions, supports ordering (<=>) with no prefix search
rum_tsvector_addon_ops stores lexemes + any attached column, supports ordering (<=>) on attached column
rum_anyarray_ops stores array elements + array length, supports ordering (<=>) by similarity
rum_anyarray_addon_ops stores array elements + attached column, supports ordering (<=>) on attached column
rum_timestamp_ops stores scalar values, supports ordering (<=>)

Using the same articles table from the previous post, I add a RUM index that adds the published timestamp to tsv:

postgres=# CREATE EXTENSION IF NOT EXISTS rum;

postgres=# CREATE INDEX idx_rum_addon ON articles
    USING rum (tsv rum_tsvector_addon_ops, published)
    WITH (attach = 'published', to = 'tsv');

I searched for the five most recent articles by publication date using the same text query as in the previous post.

postgres=# EXPLAIN (COSTS OFF, ANALYZE, BUFFERS, VERBOSE)
SELECT id, title, published,
       published <=> '2020-06-01'::timestamp AS distance
FROM articles
WHERE tsv @@ to_tsquery('simple', 'postgresql & article')
ORDER BY published <=> '2020-06-01'::timestamp
LIMIT 5;

QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Limit (actual time=89.101..89.552 rows=5 loops=1)
   Output: id, title, published, ((published <=> '2020-06-01 00:00:00'::timestamp without time zone))
   Buffers: shared hit=1233 read=1514, temp read=1303 written=1303
   ->  Index Scan using idx_rum_addon on documentdb_core.articles (actual time=89.097..89.542 rows=5 loops=1)
         Output: id, title, published, (published <=> '2020-06-01 00:00:00'::timestamp without time zone)
         Index Cond: (articles.tsv @@ '''postgresql'' & ''article'''::tsquery)
         Order By: (articles.published <=> '2020-06-01 00:00:00'::timestamp without time zone)
         Buffers: shared hit=1233 read=1514, temp read=1303 written=1303
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.401 ms
 Execution Time: 89.747 ms
(12 rows)

No Sort node. No Bitmap. A true Index Scan with Order By pushed into the index. Stops after 5 results.

I add a RUM index on tsv that adds the position, for phrase search without recheck:

postgres=# CREATE INDEX idx_rum_pos ON articles USING rum (tsv rum_tsvector_ops);

postgres=# EXPLAIN (COSTS OFF, ANALYZE, BUFFERS, VERBOSE)
SELECT id, title
FROM articles
WHERE tsv @@ to_tsquery('simple', 'postgresql <-> article')
LIMIT 5;

QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Limit (actual time=116.196..116.198 rows=0 loops=1)
   Output: id, title
   Buffers: shared hit=661
   ->  Index Scan using idx_rum_pos on documentdb_core.articles (actual time=116.192..116.193 rows=0 loops=1)
         Output: id, title
         Index Cond: (articles.tsv @@ '''postgresql'' <-> ''article'''::tsquery)
         Buffers: shared hit=661
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.223 ms
 Execution Time: 117.838 ms
(11 rows)

No "Rows Removed by Index Recheck". Positions stored in the index enable direct verification of adjacency. The same idea applies beyond full‑text search, for example, with JSONB, where RUM can store element positions to reduce rechecks and improve performance.

The same index is used for relevance ranking:

postgres=# EXPLAIN (COSTS OFF, ANALYZE, BUFFERS, VERBOSE)
SELECT id, title,
       tsv <=> to_tsquery('english', 'postgresql | optimization') AS rank
FROM articles
WHERE tsv @@ to_tsquery('simple', 'postgresql | article')
ORDER BY tsv <=> to_tsquery('english', 'postgresql | optimization')
LIMIT 10;

QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Limit (actual time=543.958..544.006 rows=10 loops=1)
   Output: id, title, ((tsv <=> '''postgresql'' | ''optim'''::tsquery))
   Buffers: shared hit=783, temp read=13289 written=15244
   ->  Index Scan using idx_rum_pos on documentdb_core.articles (actual time=543.954..543.977 rows=10 loops=1)
         Output: id, title, (tsv <=> '''postgresql'' | ''optim'''::tsquery)
         Index Cond: (articles.tsv @@ '''postgresql'' | ''article'''::tsquery)
         Order By: (articles.tsv <=> '''postgresql'' | ''optim'''::tsquery)
         Buffers: shared hit=783, temp read=13289 written=15244
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.198 ms
 Execution Time: 548.884 ms
(12 rows)

Ranking is computed directly during the index scan. No Sort node, no heap access for computing the score, enabling early termination with LIMIT. This contrasts with GIN, where ranking is computed after fetching all matching rows from the heap and sorting them.

Early versions of RUM allowed multi-column indexes, but only the main inverted column participated in the ordered scan (2017 thread). Additional columns were applied as post-filters, which limited the benefit of LIMIT queries. Currently filter columns can participate in the index scan itself, allowing pruning during traversal of the ordered posting list — but ordering is still driven by a single attached column. Here is an example:

postgres=# CREATE INDEX idx_rum_multi ON articles
    USING rum (tsv rum_tsvector_addon_ops, category, published)
    WITH (attach = 'published', to = 'tsv');

postgres=# EXPLAIN (COSTS OFF, ANALYZE, BUFFERS, VERBOSE)
SELECT id, title, published
FROM articles
WHERE tsv @@ to_tsquery('english', 'postgresql')
  AND category = 'tech'
ORDER BY published <=> '2020-06-01'::timestamp
LIMIT 5;

QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Limit (actual time=50.212..51.269 rows=5 loops=1)
   Output: id, title, published, ((published <=> '2020-06-01 00:00:00'::timestamp without time zone))
   Buffers: shared hit=117 read=112, temp read=550 written=550
   ->  Index Scan using idx_rum_multi on documentdb_core.articles (actual time=50.209..51.258 rows=5 loops=1)
         Output: id, title, published, (published <=> '2020-06-01 00:00:00'::timestamp without time zone)
         Index Cond: ((articles.tsv @@ '''postgresql'''::tsquery) AND (articles.category = 'tech'::text))
         Order By: (articles.published <=> '2020-06-01 00:00:00'::timestamp without time zone)
         Buffers: shared hit=117 read=112, temp read=550 written=550
 Planning:
   Buffers: shared hit=37 read=9 dirtied=3
 Planning Time: 7.805 ms
 Execution Time: 51.667 ms
(12 rows)

Here, category = 'tech' is in Index Cond, not a Filter, so pre-filtering on multiple columns is possible. RUM’s <=> operator uses a ranking model (ts_score) that combines ts_rank and ts_rank_cd, addressing their respective limitations (logical operators and OR queries).

The RUM Data Structure includes addInfo and may be sorted on it instead of TID:

RUM Posting List (leaf page):
┌─────────────────────────────────────────────┐
│ [TID₁, addInfo₁] [TID₂, addInfo₂] ...       │
│ sorted by addInfo (when order_by_attach=on) │
│ or by TID (when order_by_attach=off)        │
└─────────────────────────────────────────────┘

One drawback of the RUM index is that it has slower build and insert times than the GIN index. This is because RUM stores additional information alongside keys and utilizes generic Write-Ahead Logging (WAL) records, which can significantly increase WAL volume compared to GIN. It fits workloads where data is written once, such as append-only or event-driven use cases, and is queried many times later. Unlike GIN, RUM does not implement a pending list for fast updates, which contributes to its higher write and build costs. RUM performs best when dealing with highly repetitive keys, such as those found in natural language text or denormalized documents. However, for high-cardinality unique keys, like UUIDs, the inverted index structure offers little advantage over a B-tree.

The next post will cover Extended RUM, which enables additional indexing and ordering capabilities in Microsoft's contribution to DocumentDB.

GIN: Understanding PostgreSQL's Inverted Index and Its Limitations

Franck Pachot — Tue, 09 Jun 2026 05:44:32 +0000

This series of posts traces the evolution from GIN to RUM to Extended RUM, showing how a single architectural idea—to store more in the index to do less work at query time—unlocks major performance improvements at each step.

Generalized Inverted Index (GIN) is PostgreSQL's answer to the question "how do I index composite values?" — including arrays, JSONB documents, tsvector, and any data type that decomposes into multiple keys per row.

The structure is conceptually simple:

Entry Tree (B-tree of keys)
├── "alice"  → Posting List: [(0,1), (3,7), (5,2)]
├── "bob"    → Posting List: [(0,2), (4,1)]
├── "carol"  → Posting Tree (when list becomes too large for in-line storage)"
│             └── B-tree of ItemPointers
└── ...

For each indexed value, GIN calls extractValue to decompose it into keys. Each key gets an entry in the B-tree. Each entry points to a posting list — a sorted array of heap TIDs (tuple identifiers) that contain that key.

At query time, GIN calls extractQuery to decompose the search condition into keys, then intersects or unions the posting lists using a consistent function.

What GIN Gets Right:

Space efficiency: posting lists are compressed (variable-byte encoding), and duplicate keys are stored once.
Fast containment queries: @>, <@, @@ — any "does this value contain these keys?" pattern is natural.
Extensible operator classes: anyone can plug in new data types via extractValue/extractQuery/consistent.

Limitations of GIN:

Posting lists lack ordering info. GIN's lists only contain TIDs sorted by physical location, with no space for extra data.
Cannot perform ordered scans. GIN doesn’t support ORDER BY pushdown, and queries with sorting require an explicit sort after the index scan.
No extra payload per TID. Posting lists hold only 6-byte TIDs, without timestamps, scores, or additional info.
Only bitmap scans are supported, not true index scans. GIN generates and processes bitmaps of matching TIDs.

Implications:

Ranking needs heap access. For ranked searches like ts_rank(), GIN finds TIDs but lacks term frequency or position info, so it must fetch and sort matching heap tuples.
Phrase searches need reprocessing. GIN can locate tuples containing specific words, but it cannot confirm if these words are adjacent based only on the index. It identifies possible matches and then rechecks each heap tuple to ensure the words are in sequence. As clarified by Artur Zakirov (Postgres Professional), "GIN supports it, but it requires additional bitmap heap scan and so it is slower."
No LIMIT pushdown — must scan the entire posting list even for a small number of results.
Cursor-based iteration isn't supported.

I created the following tables that I'll use for the examples in this blog post and the next ones:

CREATE EXTENSION IF NOT EXISTS btree_gin;

DROP TABLE IF EXISTS articles;
CREATE TABLE articles (
    id          serial PRIMARY KEY,
    title       text NOT NULL,
    body        text NOT NULL,
    category    text NOT NULL,
    published   timestamp NOT NULL,
    score       int NOT NULL,
    tsv         tsvector GENERATED ALWAYS AS (
                    to_tsvector('simple', title || ' ' || body)
                ) STORED
);

INSERT INTO articles (title, body, category, published, score)
SELECT
    'Article about ' || words[1 + (i % 10)] || ' and ' || words[1 + ((i*7) % 10)],
    'This is the body discussing ' || words[1 + ((i*3) % 10)] ||
    ' in the context of ' || words[1 + ((i*11) % 10)] ||
    '. We also mention ' || words[1 + ((i*13) % 10)] || ' here.',
    categories[1 + (i % 4)],
    '2020-01-01'::timestamp + (i || ' hours')::interval,
    (i * 17) % 100
FROM generate_series(1, 1e6) AS s(i),
LATERAL (SELECT ARRAY['postgresql','indexing','performance','database',
                       'query','optimization','storage','replication',
                       'vacuum','analytics'] AS words) w,
LATERAL (SELECT ARRAY['tech','science','blog','news'] AS categories) c;

ANALYZE articles;

CREATE INDEX idx_gin_tsv ON articles USING gin (tsv);

Here is a Full-Text Search — Bitmap Only:

postgres=# EXPLAIN (COSTS OFF, ANALYZE, BUFFERS)
SELECT id, title, score
FROM articles
WHERE tsv @@ to_tsquery('simple', 'postgresql & article')
LIMIT 10
;

QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Limit (actual time=24.049..24.104 rows=10 loops=1)
   Buffers: shared hit=299 read=6
   ->  Bitmap Heap Scan on articles (actual time=24.045..24.087 rows=10 loops=1)
         Recheck Cond: (tsv @@ '''postgresql'' & ''article'''::tsquery)
         Heap Blocks: exact=6
         Buffers: shared hit=299 read=6
         ->  Bitmap Index Scan on idx_gin_tsv (actual time=16.165..16.165 rows=100000 loops=1)
               Index Cond: (tsv @@ '''postgresql'' & ''article'''::tsquery)
               Buffers: shared hit=299
 Planning:
   Buffers: shared hit=33 read=2 dirtied=7
 Planning Time: 0.314 ms
 Execution Time: 24.138 ms
(13 rows)

GIN uses Bitmap Index Scan → Bitmap Heap Scan. It never produces a true Index Scan. Even with LIMIT 10, it builds the full bitmap first on one million rows before discarding most of them to return only ten.

An ORDER BY requires a Sort operation:

postgres=# EXPLAIN (COSTS OFF, ANALYZE)
SELECT id, title, published
FROM articles
WHERE tsv @@ to_tsquery('simple', 'postgresql & article')
ORDER BY published DESC
LIMIT 5;

QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Limit (actual time=1838.940..1853.064 rows=5 loops=1)
   ->  Gather Merge (actual time=1838.937..1853.054 rows=5 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort (actual time=1812.430..1812.435 rows=4 loops=3)
               Sort Key: published DESC
               Sort Method: top-N heapsort  Memory: 26kB
               Worker 0:  Sort Method: top-N heapsort  Memory: 26kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 26kB
               ->  Parallel Bitmap Heap Scan on articles (actual time=113.247..1784.517 rows=33333 loops=3)
                     Recheck Cond: (tsv @@ '''postgresql'' & ''article'''::tsquery)
                     Heap Blocks: exact=19968
                     ->  Bitmap Index Scan on idx_gin_tsv (actual time=127.531..127.532 rows=100000 loops=1)
                           Index Cond: (tsv @@ '''postgresql'' & ''article'''::tsquery)
 Planning Time: 1.290 ms
 Execution Time: 1853.445 ms
(16 rows)

The Sort node is unavoidable. GIN collected all matching TIDs, accessed the heap, sorted them, and retained the top 5.

Phrase search with distance — Recheck Required:

postgres=# EXPLAIN (COSTS OFF, ANALYZE)
SELECT id, title
FROM articles
WHERE tsv @@ to_tsquery('simple', 'postgresql <-> article')
LIMIT 5;

QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Limit (actual time=357.799..357.803 rows=0 loops=1)
   ->  Bitmap Heap Scan on articles (actual time=357.795..357.797 rows=0 loops=1)
         Recheck Cond: (tsv @@ '''postgresql'' <-> ''article'''::tsquery)
         Rows Removed by Index Recheck: 100000
         Heap Blocks: exact=55556
         ->  Bitmap Index Scan on idx_gin_tsv (actual time=48.745..48.746 rows=100000 loops=1)
               Index Cond: (tsv @@ '''postgresql'' <-> ''article'''::tsquery)
 Planning Time: 0.561 ms
 Execution Time: 357.873 ms
(9 rows)

With Rows Removed by Index Recheck, GIN found candidates containing both words but had to access the heap to verify adjacency. No positional information in the index.

What's Next

These three limitations — no ordering, no positions, bitmap-only scans — are exactly what RUM was designed to solve. In the next post of this series, we'll see how a single architectural change (adding addInfo to each posting list entry) eliminates all three.

Extended RUM in DocumentDB extension for PostgreSQL: Efficient ESR (Equality, Sort, Range) Queries

Franck Pachot — Sun, 07 Jun 2026 15:37:15 +0000

Last year, I examined RUM indexes within this series on multi-key indexing, demonstrating that they cannot substitute MongoDB's compound indexes for sorted queries. A year later, Microsoft has fixed this in the DocumentDB extension for PostgreSQL with an Extended RUM index that preserves the ordering of the keys, allowing an ordered scan rather than a bitmap scan. Let's revisit our pagination query to see how it performs now.

I start a container with the latest DocumentDB (version v0.112-0 from May 26, 2026):

docker run -d --name documentdb-local -p 10260:10260 -p 9712:9712 ghcr.io/documentdb/documentdb/documentdb-local:latest  --username franck --password franck --start-pg

I can connect to PostgreSQL on port 9712, where many extensions are installed, including the extended RUM index:

docker exec -it documentdb-local psql -p 9712 postgres

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

postgres=# \dx
                                        List of installed extensions
Name           | Version |   Schema   |                        Description                         
-------------------------+---------+------------+------------------------------------------------------------
 documentdb              | 0.112-0 | public     | API surface for DocumentDB for PostgreSQL
 documentdb_core         | 0.112-0 | public     | Core API surface for DocumentDB on PostgreSQL
 documentdb_extended_rum | 0.112-0 | public     | DocumentDB Extended RUM index access method
 pg_cron                 | 1.6     | pg_catalog | Job scheduler for PostgreSQL
 plpgsql                 | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis                 | 3.6.3   | public     | PostGIS geometry and geography spatial types and functions
 tsm_system_rows         | 1.0     | public     | TABLESAMPLE method which accepts number of rows as a limit
 vector                  | 0.8.2   | public     | vector data type and ivfflat and hnsw access methods
(8 rows)

postgres=#

I can also connect to the MongoDB-compatible API:

docker exec -it documentdb-local mongosh -u franck -p franck 'mongodb://localhost:10260/?tls=true&tlsAllowInvalidCertificates=true'

Current Mongosh Log ID: 6a0b3b537d2a1c3471d1a7ba
Connecting to:          mongodb://<credentials>@localhost:10260/?tls=true&tlsAllowInvalidCertificates=true&directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.8.3
Using MongoDB:          7.0.0
Using Mongosh:          2.8.3

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

[direct: mongos] test>

Like in the previous post, I created a simple collection with 10,000 documents:

[direct: mongos] test>
 for (let i = 0; i < 10000; i++) {
  db.demo.insertOne({
    a: 1,
    b: Math.random(),
    ts: new Date()
  });
}

I create a compound index that follows the MongoDB Equality, Sort, Range rule—designed for queries with an equality filter on a and a sort on ts:

[direct: mongos] test>
 db.demo.createIndex({ "a": 1, "ts": -1 });

I run the same query as in the previous post, which, with the standard RUM indexes, produced a Bitmap Index Scan followed by a Sort of all documents matching a: 1 before returning the top 10:

[direct: mongos] test>
 db.demo.find(
 { a: 1 }
).sort(
 { ts: -1 }
).limit(10).explain("executionStats");

The good surprise is that with the current version of DocumentDB, the execution plan looks like MongoDB's native IXSCAN with no additional sort step:

[direct: mongos] test> db.demo.find(
  { a: 1 }).sort({ts:-1}).limit(10).explain("executionStats")
;

{
  explainVersion: 2,
...
  executionStats: {
    nReturned: Long('10'),
    executionTimeMillis: 0.286,
    executionStartAtTimeMillis: 0.256,
    totalDocsExamined: Long('10'),
    totalKeysExamined: Long('10'),
    executionStages: {
      stage: 'LIMIT',
      nReturned: Long('10'),
      executionTimeMillis: 0.286,
      executionStartAtTimeMillis: 0.256,
      totalDocsExamined: 10,
      totalKeysExamined: 10,
      numBlocksFromCache: 25,
      inputStage: {
        stage: 'FETCH',
        nReturned: Long('10'),
        executionTimeMillis: 0.267,
        executionStartAtTimeMillis: 0.253,
        totalKeysExamined: 10,
        numBlocksFromCache: 25,
        inputStage: {
          stage: 'IXSCAN',
          nReturned: Long('10'),
          executionTimeMillis: 0.267,
          executionStartAtTimeMillis: 0.253,
          indexName: 'a_1_ts_-1',
          totalKeysExamined: 10,
          numBlocksFromCache: 25
        }
      }
    }
  },
  ok: 1
}

It read only the minimum necessary to get the result: ten index entries (totalKeysExamined: 10) in the expected order and fetched only ten documents (totalDocsExamined: 10). This is the most efficient execution plan.

Comparing the Two RUM Index Definitions

I connect to PostgreSQL to describe the table that stores my collection documents (you will see later how I obtained the name):

postgres=# \d documentdb_data.documents_7

            Table "documentdb_data.documents_7"

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

Indexes:

    "collection_pk_7" PRIMARY KEY, btree (shard_key_value, object_id)

    "documents_rum_index_25" documentdb_extended_rum (document documentdb_extended_rum_catalog.bson_extended_rum_composite_path_ops (pathspec='[ "a", { "ts" : -1 } ]', tl='2691'))

Check constraints:

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

postgres=#

What was a standard RUM index in the previous post is now an extended RUM index:

Attribute	Previous post	Current test
Index Type	`documentdb_rum`	`documentdb_extended_rum`
Operator Class	two `bson_rum_single_path_ops`	one `bson_extended_rum_composite_path_ops`
Fields	`a` (asc), `ts`	`a` (asc), `ts` (desc)
Sort Direction on `ts`	Not specified	Explicitly `-1` (descending)
Path Encoding	Two separate `path=` entries	Single JSON `pathspec` array

The extended RUM index acts as a sort-order-aware composite index, embedding the descending direction directly into the pathspec. Unlike the previous approach, which stored each path independently, this approach encodes all indexed fields as a single composite pathspec and generates a single composite index entry per document, preserving the relative ordering between fields. An index scan (RumOrderedScan) efficiently covers both filtering and sorting, eliminating the need for a separate Sort node in the PostgreSQL execution plan. This benefit is evident when executing the same query via the DocumentDB API in PostgreSQL:

postgres=# explain (analyze, buffers, verbose, costs off)
select document from bson_aggregation_find(
  'test',
  '{
    "find": "demo",
    "filter": { "a": 1 },
    "sort":   { "ts": -1 },
    "limit": 10
  }'::documentdb_core.bson
);
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.108..0.138 rows=10 loops=1)
   Output: document, (bson_orderby(document, '{ "ts" : { "$numberInt" : "-1" } }'::bson))
   Buffers: shared hit=4
   ->  Index Scan using "a_1_ts_-1" on documentdb_data.documents_7 collection (actual time=0.104..0.117 rows=10 loops=1)
         Output: document, bson_orderby(document, '{ "ts" : { "$numberInt" : "-1" } }'::bson)
         Index Cond: (collection.document @= '{ "a" : { "$numberInt" : "1" } }'::bson)
         Order By: (collection.document |-<> '{ "ts" : { "$numberInt" : "-1" } }'::bson)
         Buffers: shared hit=4
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.453 ms
 Execution Time: 0.192 ms
(12 rows)

postgres=#

Note: I got the name of the internal table I described above from this execution plan, which uses the collection name in the query. The MongoDB API's explain() shows a MongoDB-compatible execution plan, and EXPLAIN in PostgreSQL shows the PostgreSQL version of it.

Comparison of Execution Plans

Here is how the new behavior with an ordered index scan compares to the previous bitmap scan.

Feature	Ordered Index Scan	Bitmap Index Scan
PostgreSQL Node	`Index Scan`	`Bitmap Index Scan`
Ordering	Handled by sort direction in index	Lost—requires a `Sort` node
Scan Type	`scanType: RumOrderedScan`	`scanType: RumFastScan / RumRegularScan`
Efficiency	Supports early termination (`LIMIT`)	Must scan all matching TIDs into bitmap
RUM Entry Point	`rumgettuple()`	`rumgetbitmap()`
Sort Step	None — `useSimpleScan = true`	`rum_tuplesort_performsort()` required
Memory Usage	Low—one tuple at a time	High—full `TIDBitmap` + sort state
Index Structure Used	B-tree walk via `orderStack`	Posting list / posting tree dump
Filter Evaluation	Inline via `ValidateIndexEntry()`	Post-collection in `keyGetItem()`
Seek Optimization	Yes—advances `queryKey` as entries exhaust	No
Multi-column Support	Multi-column via composite pathspec	Multi-column via separate entries
`LIMIT` benefit	✅ Full — stops after N rows	❌ None — bitmap built before `LIMIT` applies
Recheck Behavior	`xs_recheckorderby` per tuple	`xs_recheck` on bitmap result
Trigger Condition	`RumEnableOrderedOperatorScans` + `willSort` + `norderbys > 0`	Default path

Index Only Scan

Other improvements are coming to Extended RUM, like Index Only Scan, currently supported for COUNT:

postgres=# explain (analyze, buffers, verbose, costs off)  
SELECT document FROM bson_aggregation_count(  
  'test',  
  '{  
    "count": "demo",  
    "query": { "a": 1 }  
  }'::documentdb_core.bson  
);
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Aggregate (actual time=22.760..22.763 rows=1 loops=1)
   Output: documentdb_api_internal.bsoncommandcount(1)
   Buffers: shared hit=109
   ->  Index Only Scan using "a_1_ts_-1" on documentdb_data.documents_7 collection (actual time=0.089..14.292 rows=10000 loops=1)
         Output: collection.document
         Index Cond: (collection.document @= '{ "a" : { "$numberInt" : "1" } }'::bson)
         Heap Fetches: 0
         Buffers: shared hit=109
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.441 ms
 Execution Time: 22.883 ms
(12 rows)

Index Only Scan will will support covering projections in the future (see IsQueryValidForIndexOnlyScan)

Conclusion

A year ago, DocumentDB's RUM indexes had a significant limitation for pagination queries: even with the right compound index, the planner would fall back to a Bitmap Index Scan followed by a full Sort, meaning every matching document had to be collected and sorted before the first result could be returned. A LIMIT 10 query on 10,000 documents would examine all 10,000—defeating the purpose of the compound index.

With v0.112-0, this is fixed. The new documentdb_extended_rum index type, combined with the RumOrderedScan execution path, reduces the gap with native MongoDB behavior:

The index encodes sort direction directly in the pathspec ({ "ts": -1 })
The planner chooses an Index Scan instead of a Bitmap Index Scan
No Sort node appears in the plan
LIMIT 10 examines exactly 10 index entries and 10 documents

This is more than just a cosmetic change. In time-series queries—such as filtering on a low-cardinality field, sorting by timestamp descending, and retrieving the first page—the difference between the two plans ranges from O(result) to O(total size). For OLTP systems, pagination queries are common and need to be quick, scalable, and predictable, since they show results to the user before she selects, refines filters, or moves to the next page.

This ordered scan is also essential for TTL indexes to efficiently identify expiration candidates.

The key ingredients that make this work together are visible from the DocumentDB open-source code:

documentdb_extended_rum—composite pathspec with explicit sort direction
bson_extended_rum_composite_path_ops — single operator class covering all fields
RumOrderedScan — B-tree walk in index order via orderStack, bypassing rumgetbitmap()
useSimpleScan — returns one tuple at a time, enabling true LIMIT pushdown
RumAllowOrderByRawKeys — the GUC that enables this path, now on by default

This behavior is enabled by default but not forced (it is a planner decision based on the query characteristics, with runtime adaptation to optimize the scan type):

postgres=# \dconfig *rum*order*

           List of configuration parameters
                  Parameter                   | Value
----------------------------------------------+-------
 documentdb_rum.enable_ordered_operator_scans | on
 documentdb_rum.forceRumOrderedIndexScan      | off
(2 rows)

In under a year, DocumentDB evolved from "RUM instead of GIN, but with the same pagination limitations" to "RUM with ordered scan, aligning more with MongoDB's IXSCAN behavior for ESR-pattern indexes". For developers implementing cursor-based pagination or queries with a selective filter and sorting on a time or sequence field, this marks the version at which it begins to function as expected. It also improves TTL indexes maintenance.

Getting Started with pg_durable: Workflows Inside PostgreSQL

Franck Pachot — Fri, 05 Jun 2026 16:31:54 +0000

Modern applications demand long‑lasting workflows that can withstand crashes, restarts, and failures, often relying on external platforms. pg_durable integrates durable workflow orchestration directly into PostgreSQL, enabling workflows to be created in SQL and reliably executed, monitored, inspected, and recovered from disruptions.

This tool is particularly useful for ETL processes, data pipelines, background tasks, scheduled jobs, long‑running business procedures, and internal workflow management.

To truly grasp its capabilities, the best approach is to try it yourself. I did so by building and running it locally, which provided practical insight.

Installing pg_durable

I installed Rust to compile the extension:

curl https://sh.rustup.rs -sSf | sh

. "$HOME/.cargo/env"

I installed the PostgreSQL build dependencies.

sudo dnf install -y openssl-devel pkg-config libicu-devel readline-devel flex

I installed pgrx for PostgreSQL 17:

cargo install cargo-pgrx --version 0.16.1 --locked

cargo pgrx init --pg17=download

export PG_VERSION=pg17

I enabled the extension preload library:

sed -e "/shared_preload_libraries/s/^.*$/shared_preload_libraries='pg_durable'/" \
    -i ~/.pgrx/data-17/postgresql.conf

I declared the superuser that I'll create to run the background worker:

cat >> ~/.pgrx/data-17/postgresql.conf <<<"pg_durable.worker_role='superworker'"

I cloned pg_durable from the Microsoft repository:

git clone https://github.com/microsoft/pg_durable.git

cd pg_durable

I built the extension, started PostgreSQL, and got a psql prompt:


cargo pgrx run

I checked the pg_durable configuration, connected to the pg_durable.database, created the pg_durable.worker_role user, and installed the extension:

pg_durable=# \dconfig pg_durable.*

          List of configuration parameters
               Parameter               |    Value
---------------------------------------+-------------
 pg_durable.database                   | postgres
 pg_durable.enable_superuser_instances | off
 pg_durable.execution_acquire_timeout  | 30
 pg_durable.max_duroxide_connections   | 10
 pg_durable.max_management_connections | 6
 pg_durable.max_user_connections       | 10
 pg_durable.worker_role                | superworker
(7 rows)

\c postgres

CREATE ROLE superworker SUPERUSER LOGIN;

CREATE EXTENSION pg_durable;

My PostgreSQL instance is now ready to use durable functions.

Hello World

I create a user that will execute workflows and grant access to pg_durable:

CREATE USER franck;

GRANT CREATE ON DATABASE postgres TO franck;

SELECT df.grant_usage('franck');

I reconnect as that user:

postgres=# \c postgres franck

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

postgres=>

I start a durable workflow:

postgres=> SELECT df.start(
           $sql$ SELECT 'Hello, durable world!' AS message $sql$
          );

  start
----------
 393f09e0
(1 row)

As the SQL statements can contain quotes, I used PostgreSQL dollar quoting, that can be $$ with an optional tag inside.

One second later, it is completed:

postgres=> SELECT df.status('393f09e0');

  status
-----------
 completed

(1 row)

The result can be fetched as a document with a list of rows:

postgres=> SELECT df.result('393f09e0');

                              result
------------------------------------------------------------------
 {"rows": [{"message": "Hello, durable world!"}], "row_count": 1}

(1 row)

I used the same "Hello, durable world!" example as in the HorizonDB durable functions documentation, because this is where pg_durable comes from: Microsoft provides it to PostgreSQL as an open-source extension.

Here is more information about this execution:

postgres=> \x
Expanded display is on.

postgres=> SELECT * FROM df.list_instances();

-[ RECORD 1 ]---+-------------------------------------------------------------
instance_id     | 393f09e0
label           |
function_name   | pg_durable::orchestration::execute-function-graph
status          | completed
execution_count | 1
output          | {"rows":[{"message":"Hello, durable world!"}],"row_count":1}

postgres=> \x
Expanded display is off.
postgres=>

The execution graph is basic, as there's a single operation:

postgres=> SELECT df.explain('393f09e0');

                                explain
------------------------------------------------------------------------
 Instance: 393f09e0                                                    +
 Status:   ✓ Completed                                                 +
 Output:   {"rows":[{"message":"Hello, durable world!"}],"row_count":1}+
                                                                       +
 SQL: SELECT 'Hello, durable world!' AS message ✓
(1 row)

There is a single node:

postgres=> \x
Expanded display is on.

postgres=> SELECT * from df.instance_nodes('393f09e0');

-[ RECORD 1 ]+-----------------------------------------------------------------
execution_id | 1
node_id      | 60e433bb
node_type    | SQL
query        | SELECT 'Hello, durable world!' AS message
result_name  |
left_node    |
right_node   |
status       | completed
result       | {"rows": [{"message": "Hello, durable world!"}], "row_count": 1}
updated_at   | 2026-06-04 16:36:38.594906+00

postgres=> \x
Expanded display is off.

It was executed once:


postgres=> SELECT * from df.instance_executions('393f09e0');

 execution_id |  status   | event_count | duration_ms |                            output
--------------+-----------+-------------+-------------+--------------------------------------------------------------
            1 | Completed |          14 |         692 | {"rows":[{"message":"Hello, durable world!"}],"row_count":1}
(1 row)

Here are the global metrics:

postgres=> SELECT * FROM df.metrics();

 total_instances | running_instances | completed_instances | failed_instances | total_executions | total_events
-----------------+-------------------+---------------------+------------------+------------------+--------------
               1 |                 0 |                   1 |                0 |                1 |           14
(1 row)

This simply looks like ADBA—Asynchronous Database Access, but it can do more. We will see that it can define complex workflows with a declarative syntax. In the next examples I'll use \gset in PSQL to get the ID into a variable.

Beyond a Single Query

While a simple SQL statement demonstrates the basics, pg_durable becomes much more powerful when orchestrating multiple operations.

The SQL workflow DSL supports:

Variable capture: =>)
Sequential execution: ~>
Parallel execution and wait: & - Parallel execution and race: |
Conditional branching: ?> and !>
Infinite loops: @>

Here are simple table-free examples demonstrating pg_durable's key features. Let's start with a sequential execution (~>):

postgres=> SELECT df.start(
       'SELECT now() as step1' |=> 't1'
    ~> 'SELECT pg_sleep(5)'
    ~> 'SELECT now() as step2' |=> 't2'
    ~> 'SELECT now() as step3' |=> 't3',
    'sequential-timing'
) as i \gset

-- Check the results after five seconds
postgres=> SELECT df.result(:'i');

                                  result
---------------------------------------------------------------------------
 {"rows": [{"step3": "2026-06-04T21:33:59.939785+00:00"}], "row_count": 1}

(1 row)

postgres=> SELECT node_type, result_name, result, node_id, left_node, right_node
 FROM df.instance_nodes(:'i');

 node_type | result_name |                                  result                                   | node_id  | left_node | right_node
-----------+-------------+---------------------------------------------------------------------------+----------+-----------+------------
 THEN      | t3          | {"rows": [{"step3": "2026-06-04T21:33:59.939785+00:00"}], "row_count": 1} | 49c9c8b4 | 48fcec43  | 9f818758
 THEN      | t2          | {"rows": [{"step2": "2026-06-04T21:33:59.507283+00:00"}], "row_count": 1} | 48fcec43 | 348c1948  | c78f2200
 THEN      |             | {"rows": [{"pg_sleep": null}], "row_count": 1}                            | 348c1948 | c7317ea8  | 200f8a52
 SQL       | t1          | {"rows": [{"step1": "2026-06-04T21:33:53.746030+00:00"}], "row_count": 1} | c7317ea8 |           |
 SQL       |             | {"rows": [{"pg_sleep": null}], "row_count": 1}                            | 200f8a52 |           |
 SQL       |             | {"rows": [{"step2": "2026-06-04T21:33:59.507283+00:00"}], "row_count": 1} | c78f2200 |           |
 SQL       |             | {"rows": [{"step3": "2026-06-04T21:33:59.939785+00:00"}], "row_count": 1} | 9f818758 |           |

(7 rows)

I displayed the timestamps to show that steps 2 and 3 occur 5 seconds after step 1 because I used pg_sleep(5) in the sequential execution.

The node_type column indicates the type of operation each node represents in the function graph. Here, THEN denotes sequential execution: running the left child, then the right child, and SQL is the leaf node with no children, executing the SQL query.

The complete list of valid node types in pg_durable is:

node_type	Description	Operator/Function
SQL	Execute SQL query	Plain string or `df.sql()`
THEN	Sequential execution	`~>` operator or `df.seq()`
IF	Conditional branch	`?>` `!>` operators or `df.if()`
JOIN	Parallel execution (wait all)	`&` operator or `df.join()`
RACE	Parallel execution (first wins)	`\` operator or `df.race()`
LOOP	Infinite loop	`@>` operator or `df.loop()`
BREAK	Exit loop	`df.break()`
SLEEP	Pause execution	`df.sleep()`
WAIT_SCHEDULE	Wait for cron schedule	`df.wait_for_schedule()`
HTTP	Make HTTP request	`df.http()`
SIGNAL	Wait for external signal	`df.wait_for_signal()`

We can execute steps in parallel (&):

postgres=> SELECT df.start(
    ('SELECT now() as branch1' |=> 'b1' ~> 'SELECT pg_sleep(20)')
    &
    ('SELECT now() as branch2' |=> 'b2' ~> 'SELECT pg_sleep(10)')
    ~> 'SELECT now() as after_join' |=> 'final',
    'parallel-sleep'
) as i \gset

postgres=> SELECT node_type, result_name, result, node_id, left_node, right_node
 FROM df.instance_nodes(:'i');

 node_type | result_name |                                              result                                              | node_id  | left_node | right_node
-----------+-------------+--------------------------------------------------------------------------------------------------+----------+-----------+------------
 SQL       | b1          | {"rows": [{"branch1": "2026-06-04T21:53:47.870539+00:00"}], "row_count": 1}                      | 9d479c96 |           |
 SQL       | b2          | {"rows": [{"branch2": "2026-06-04T21:53:47.870606+00:00"}], "row_count": 1}                      | cb844cd8 |           |
 SQL       |             | {"rows": [{"pg_sleep": null}], "row_count": 1}                                                   | ad8ec143 |           |
 THEN      |             | {"rows": [{"pg_sleep": null}], "row_count": 1}                                                   | 1ff3adc8 | cb844cd8  | ad8ec143
 SQL       |             | {"rows": [{"pg_sleep": null}], "row_count": 1}                                                   | 9db57c15 |           |
 THEN      |             | {"rows": [{"pg_sleep": null}], "row_count": 1}                                                   | 5e4d8069 | 9d479c96  | 9db57c15
 JOIN      |             | [{"rows": [{"pg_sleep": null}], "row_count": 1}, {"rows": [{"pg_sleep": null}], "row_count": 1}] | 6f95c84d | 5e4d8069  | 1ff3adc8
 SQL       |             | {"rows": [{"after_join": "2026-06-04T21:54:08.752247+00:00"}], "row_count": 1}                   | d4be28af |           |
 THEN      | final       | {"rows": [{"after_join": "2026-06-04T21:54:08.752247+00:00"}], "row_count": 1}                   | df59e250 | 6f95c84d  | d4be28af

(9 rows)

Here, b1 and b2 started at the same time, in parallel. One branch completed after 20 seconds and the other after 10 seconds, so the final step started after 20 seconds, and the total duration was 20 seconds—it would have been more than 30 seconds if executed serially:

postgres=> SELECT * from df.instance_executions(:'i');

 execution_id |  status   | event_count | duration_ms |                                   output
--------------+-----------+-------------+-------------+----------------------------------------------------------------------------
            1 | Completed |          26 |       22107 | {"rows":[{"after_join":"2026-06-04T21:54:08.752247+00:00"}],"row_count":1}

(1 row)

Parallel nodes are used when we need the result from all operations. The next step awaits all. If we want to continue as soon as one branch gets a result, we can run a race, where the first one wins (|):

postgres=> SELECT df.start(
    'SELECT pg_sleep(30)' | 'SELECT pg_sleep(90)' ~> 'SELECT ''fast'' as winner',
    'race-test'
) as i \gset

postgres=> select usename,backend_start,query_start,wait_event,state,query,application_name
 from pg_stat_activity where usename=user and pid!=pg_backend_pid(
)
\watch 10
                                              Fri 05 Jun 2026 04:08:30 AM GMT (every 10s)

 usename |         backend_start         |          query_start          | wait_event | state  |        query        | application_name
---------+-------------------------------+-------------------------------+------------+--------+---------------------+------------------
 franck  | 2026-06-05 04:08:29.873785+00 | 2026-06-05 04:08:29.877368+00 | PgSleep    | active | SELECT pg_sleep(30) |
 franck  | 2026-06-05 04:08:29.875738+00 | 2026-06-05 04:08:29.879296+00 | PgSleep    | active | SELECT pg_sleep(90) |
(2 rows)

                                              Fri 05 Jun 2026 04:08:40 AM GMT (every 10s)

 usename |         backend_start         |          query_start          | wait_event | state  |        query        | application_name
---------+-------------------------------+-------------------------------+------------+--------+---------------------+------------------
 franck  | 2026-06-05 04:08:29.873785+00 | 2026-06-05 04:08:29.877368+00 | PgSleep    | active | SELECT pg_sleep(30) |
 franck  | 2026-06-05 04:08:29.875738+00 | 2026-06-05 04:08:29.879296+00 | PgSleep    | active | SELECT pg_sleep(90) |
(2 rows)

                                              Fri 05 Jun 2026 04:08:50 AM GMT (every 10s)

 usename |         backend_start         |          query_start          | wait_event | state  |        query        | application_name
---------+-------------------------------+-------------------------------+------------+--------+---------------------+------------------
 franck  | 2026-06-05 04:08:29.873785+00 | 2026-06-05 04:08:29.877368+00 | PgSleep    | active | SELECT pg_sleep(30) |
 franck  | 2026-06-05 04:08:29.875738+00 | 2026-06-05 04:08:29.879296+00 | PgSleep    | active | SELECT pg_sleep(90) |
(2 rows)

                                              Fri 05 Jun 2026 04:09:00 AM GMT (every 10s)

 usename |         backend_start         |          query_start          | wait_event | state  |        query        | application_name
---------+-------------------------------+-------------------------------+------------+--------+---------------------+------------------
 franck  | 2026-06-05 04:08:29.875738+00 | 2026-06-05 04:08:29.879296+00 | PgSleep    | active | SELECT pg_sleep(90) |
(1 row)

                                              Fri 05 Jun 2026 04:09:10 AM GMT (every 10s)

 usename |         backend_start         |          query_start          | wait_event | state  |        query        | application_name
---------+-------------------------------+-------------------------------+------------+--------+---------------------+------------------
 franck  | 2026-06-05 04:08:29.875738+00 | 2026-06-05 04:08:29.879296+00 | PgSleep    | active | SELECT pg_sleep(90) |
(1 row)

                                              Fri 05 Jun 2026 04:09:20 AM GMT (every 10s)

 usename |         backend_start         |          query_start          | wait_event | state  |        query        | application_name
---------+-------------------------------+-------------------------------+------------+--------+---------------------+------------------
 franck  | 2026-06-05 04:08:29.875738+00 | 2026-06-05 04:08:29.879296+00 | PgSleep    | active | SELECT pg_sleep(90) |
(1 row)

                                              Fri 05 Jun 2026 04:09:30 AM GMT (every 10s)

 usename |         backend_start         |          query_start          | wait_event | state  |        query        | application_name
---------+-------------------------------+-------------------------------+------------+--------+---------------------+------------------
 franck  | 2026-06-05 04:08:29.875738+00 | 2026-06-05 04:08:29.879296+00 | PgSleep    | active | SELECT pg_sleep(90) |
(1 row)

                                              Fri 05 Jun 2026 04:09:40 AM GMT (every 10s)

 usename |         backend_start         |          query_start          | wait_event | state  |        query        | application_name
---------+-------------------------------+-------------------------------+------------+--------+---------------------+------------------
 franck  | 2026-06-05 04:08:29.875738+00 | 2026-06-05 04:08:29.879296+00 | PgSleep    | active | SELECT pg_sleep(90) |
(1 row)

                                              Fri 05 Jun 2026 04:09:50 AM GMT (every 10s)

 usename |         backend_start         |          query_start          | wait_event | state  |        query        | application_name
---------+-------------------------------+-------------------------------+------------+--------+---------------------+------------------
 franck  | 2026-06-05 04:08:29.875738+00 | 2026-06-05 04:08:29.879296+00 | PgSleep    | active | SELECT pg_sleep(90) |
(1 row)

                      Fri 05 Jun 2026 04:10:00 AM GMT (every 10s)

 usename | backend_start | query_start | wait_event | state | query | application_name
---------+---------------+-------------+------------+-------+-------+------------------
(0 rows)

                      Fri 05 Jun 2026 04:10:10 AM GMT (every 10s)

 usename | backend_start | query_start | wait_event | state | query | application_name
---------+---------------+-------------+------------+-------+-------+------------------
(0 rows)

 execution_id |  status   | event_count | duration_ms |                   output
--------------+-----------+-------------+-------------+--------------------------------------------
            1 | Completed |          27 |       31414 | {"rows":[{"winner":"fast"}],"row_count":1}
(1 row)

Both operations run, but we use the result from the fastest one. The trade-off is that both branches consume resources, so it's best suited for lightweight operations where responsiveness outweighs the cost. It can be used for timeout scenarios, competing API calls, or waiting for either a signal or a deadline.

We can add conditional branches with if...then (?>) and else (!>):

postgres=> SELECT df.start(
    'SELECT 42 = 6*7 as condition' |=> 'cond'
    ?> 'SELECT ''Branch A'' as result'
    !> 'SELECT ''Branch B'' as result',
    'conditional-test'
) as i \gset

postgres=> SELECT df.result(:'i');

                       result
----------------------------------------------------
 {"rows": [{"result": "Branch A"}], "row_count": 1}
(1 row)

postgres=> SELECT node_type, result_name, result, node_id, left_node, right_node
 FROM df.instance_nodes(:'i');

 node_type | result_name |                       result                       | node_id  | left_node | right_node
-----------+-------------+----------------------------------------------------+----------+-----------+------------
 SQL       | cond        | {"rows": [{"condition": true}], "row_count": 1}    | 03784caa |           |
 SQL       |             | {"rows": [{"result": "Branch A"}], "row_count": 1} | d99e0e6c |           |
 IF        |             | {"rows": [{"result": "Branch A"}], "row_count": 1} | a764d565 | d99e0e6c  | 72edfb05
 SQL       |             |                                                    | 72edfb05 |           |

(4 rows)

The result can be captured into a variable (|=>) for reuse ($):

postgres=> SELECT df.start(
    'SELECT 42 as answer' |=> 'x'
    ~> 'SELECT $x * 2 as doubled'
    ~> 'SELECT $x + 10 as plus_ten',
    'variable-test'
) as i \gset

postgres=> SELECT df.result(:'i');

                    result
----------------------------------------------
 {"rows": [{"plus_ten": 52}], "row_count": 1}
(1 row)

postgres=> SELECT node_type, result_name, result, node_id, left_node, right_node
 FROM df.instance_nodes(:'i');

 node_type | result_name |                    result                    | node_id  | left_node | right_node
-----------+-------------+----------------------------------------------+----------+-----------+------------
 SQL       |             | {"rows": [{"doubled": 84}], "row_count": 1}  | 4afc6779 |           |
 SQL       |             | {"rows": [{"plus_ten": 52}], "row_count": 1} | 4d2146fe |           |
 THEN      |             | {"rows": [{"plus_ten": 52}], "row_count": 1} | 4dd00824 | 63f0a5eb  | 4d2146fe
 THEN      |             | {"rows": [{"doubled": 84}], "row_count": 1}  | 63f0a5eb | c2dbd3d3  | 4afc6779
 SQL       | x           | {"rows": [{"answer": 42}], "row_count": 1}   | c2dbd3d3 |           |

(5 rows)

The result is the final result after the sequential execution but each step shows the intermediate result.

It's also possible to loop (@>) and wait for a signal or a specific time (using a cron syntax):

SELECT df.start(  
    @> (  
        'SELECT datname, usename, application_name, wait_event_type, wait_event, state, query FROM pg_stat_activity'  
        ~> df.wait_for_schedule('*/1 * * * *')  
    ),  
    'pg-stat-activity-sampler'  
)  as i \gset

This reads pg_stat_activity every minute.

I can query the last 30 executions:

SELECT count(*) AS sessions, r.query, r.wait_event, COUNT(DISTINCT execution_id) AS samples
FROM df.instance_nodes(:'i',30) n
CROSS JOIN LATERAL jsonb_to_recordset(n.result::jsonb -> 'rows') AS r ( datname text, usename text, application_name text, wait_event_type text, wait_event text, state text, query text)
WHERE n.node_type = 'SQL' AND r.query IS NOT NULL
GROUP BY r.query, r.wait_event
ORDER BY count(*)/COUNT(DISTINCT execution_id) DESC, r.query, r.wait_event;

I can schedule a purge at midnight to keep only one week:

SELECT df.start(  
    @> (  
        df.wait_for_schedule('0 0 * * *')  
        ~> 'SELECT * FROM df.prune_executions(  
               instance_id => ''pg-stat-activity-sampler'',  
               completed_before => NOW() - INTERVAL ''7 days''  
           )'  
    ),  
    'pg-stat-activity-pruner'  
);

With a simple command, I have implemented an active session history monitoring.

Here is a final test to show that each step runs in its own transaction:

postgres=> SELECT df.start(
    -- Sequential execution showing different txid/session_id
    'SELECT user, pg_backend_pid() as session_id, txid_current() as txid, ''step1'' as step' |=> 's1'
    ~> 'SELECT user, pg_backend_pid() as session_id, txid_current() as txid, ''step2'' as step'
    ~>
    -- Race showing parallel execution with different connections
    (
        'SELECT user, pg_backend_pid() as session_id, txid_current() as txid, ''race_branch1'' as branch'
        |
        'SELECT user, pg_backend_pid() as session_id, txid_current() as txid, ''race_branch2'' as branch'
    )
    ~> 'SELECT user, pg_backend_pid() as session_id, txid_current() as txid, ''final'' as step',
    'txid-demo'
) as i \gset

postgres=> SELECT node_type, result_name, result FROM
 df.instance_nodes(:'i') WHERE node_type='SQL';

 node_type | result_name |                                                     result
-----------+-------------+----------------------------------------------------------------------------------------------------------------
 SQL       | s1          | {"rows": [{"step": "step1", "txid": 25769, "user": "franck", "session_id": 1840182}], "row_count": 1}
 SQL       |             | {"rows": [{"step": "step2", "txid": 25784, "user": "franck", "session_id": 1840183}], "row_count": 1}
 SQL       |             | {"rows": [{"txid": 25819, "user": "franck", "branch": "race_branch1", "session_id": 1840185}], "row_count": 1}
 SQL       |             | {"rows": [{"txid": 25820, "user": "franck", "branch": "race_branch2", "session_id": 1840186}], "row_count": 1}
 SQL       |             | {"rows": [{"step": "final", "txid": 25859, "user": "franck", "session_id": 1840187}], "row_count": 1}

(5 rows)

The main purpose of durable functions is to execute steps as if they were independent operations, while adding orchestration, persistence, and resilience. They run with the credentials of the user who defines the workflow, with a new session, and transaction, per operation.

Conclusion

With pg_durable, workflows become a natural extension of PostgreSQL—another “just use PostgreSQL” use case—defined in SQL, executed close to the data, with the database's resilience and persistence, and managed with the same tools you already use.

What makes it compelling is that this isn’t a prototype—it comes from real production needs—HorizonDB durable functions, now open-sourced and contributed back to PostgreSQL. It’s a clear example of Microsoft investing in the ecosystem and sharing what was built for its managed services.

If your applications already rely on PostgreSQL, this approach lets you add durable orchestration without introducing new infrastructure—just more capability where your data already lives. Try it, experiment with it, and if it clicks, give the project a star and help it grow.

Filter on Children, Sort by Parent: One-to-Many Compound Index Strategies in PostgreSQL

Franck Pachot — Mon, 01 Jun 2026 11:59:58 +0000

Before looking at PostgreSQL, I'll first introduce the problem by showing how MongoDB's document model and multi-key indexes handle compound indexes across a one-to-many relationship. Then we will see how to approximate this in PostgreSQL by denormalizing on the "many" side or the "one" side, how to maintain consistency with cascade foreign keys or triggers, and how to accelerate filtering with sorted pagination using B-tree, GIN, or RUM indexes.

Multi-key indexes in MongoDB allow compound indexes on one-to-many relationships. For example, the following index covers fields from both the children (child_value) and the parent (parent_value) when children are embedded as an array (in a children field) within the parent document:

db.parent.createIndex(
 { "children.child_value": 1, parent_value: 1 }
);

Such an index can efficiently support an equality filter on child_value combined with a sort and pagination on parent_value. For instance, finding the top 10 parents, ordered by parent_value, where at least one child has a child_value of 0.9:

db.parent.find(
 { "children.child_value": 0.9 }
).sort({ "parent_value": 1 }).limit(10);

It is important to understand the semantics of predicates on embedded arrays. This query does not return the top 10 children with this value — a parent may have multiple matching children, but we get one result per parent. This differs from a relational join (or $unwind in a MongoDB aggregation pipeline), which would produce one row per child. Here, the predicate tests only for the existence of at least one child with the specified value. The result set contains distinct parents, not (parent, child) pairs.

A multi-key index stores multiple index entries per parent document — one for each distinct child_value in the array — paired with the parent's parent_value. The number of index entries per parent equals the number of distinct child values, which can be less than the total number of children when multiple children share the same value. For example, given these documents:

{
  _id: 24,
  parent_value: "Y",
  children: [
    { child_num: 1, child_value: 0.9 },
    { child_num: 2, child_value: 0.8 }
  ]
},
{
  _id: 42,
  parent_value: "X",
  children: [
    { child_num: 1, child_value: 0.5 },
    { child_num: 2, child_value: 0.9 },
    { child_num: 3, child_value: 0.9 },
    { child_num: 4, child_value: 0.7 }
  ]
}

The index contains three entries for the second document, because the two children with child_value: 0.9 produce only one index entry:

  (0.5, "X", 42) → record ID of { _id: 42 }
  (0.7, "X", 42) → record ID of { _id: 42 }
  (0.8, "Y", 24) → record ID of { _id: 24 }
  (0.9, "X", 42) → record ID of { _id: 42 }
  (0.9, "Y", 24) → record ID of { _id: 24 }

The index scan for {child_value: 0.9} finds two entries, already ordered by the second field of the compound key, parent_value. MongoDB can read them in order — "X" before "Y" — and fetch the corresponding documents without an additional sort. With a .limit(10), the scan stops after 10 distinct parents are found, making the query efficient regardless of how many total documents exist in the collection.

There is no direct equivalent of this in PostgreSQL. If the one-to-many relationship is stored as JSONB, GIN indexes can locate documents containing {child_value: 0.9} but do not preserve ordering on other fields. The query must fetch all matching documents, sort them on parent_value, and only then return the top 10. If the one-to-many relationship is normalized into two tables, no single index can span columns from both tables — a join is required first, and the sort optimization depends on the join strategy chosen by the planner.

Normalized One-to-Many

Now that the problem is defined, let's see how we can work around this limitation in a normalized model:

drop table if exists parent, child cascade;

create table parent (
 parent_id    bigserial primary key,
 parent_value float
);

create table child (
 child_id     bigserial primary key,
 parent_id    int references parent (parent_id),
 child_value  float
);

insert into parent (parent_value)
 select random() from generate_series(1, 1000)
;

insert into child (parent_id, child_value)
 select parent_id, round(random()::numeric, 4) from parent, generate_series(1, 1000)
;

create index on parent (parent_value);
create index on child (child_value);

vacuum analyze parent, child;

The following query finds the top 10 parents, by parent_value, that have at least one child with a child_value of 0.9999:

-- explain (analyze, buffers, verbose, costs off)
select *
from parent p
where exists (
  select 1 from child c
  where c.parent_id = p.parent_id
  and c.child_value = 0.9999
)
order by parent_value
limit 10;

 parent_id |     parent_value
-----------+----------------------
       956 | 0.013518349069161273
       789 | 0.022813950892476287
       307 | 0.024740344416860793
       833 | 0.041118650516225985
       493 |  0.07587347477845374
       402 |  0.08400127026866477
       256 |   0.0980166832909124
       342 |  0.11175505348300807
       846 |  0.12047992766758941
       590 |  0.16840580135419425
(10 rows)

The execution plan shows that:

The index on child_value was scanned to find all index entries with the expected value — here rows=99.0 — which are then used to locate the corresponding blocks in the child table (this is a Bitmap Index Scan).
The Bitmap Heap Scan on the child table returned all children with child_value = 0.9999 (there are 95 if we count the actual matches — a bitmap scan can have some false positives). The results were stored in memory (Materialize).
The index on parent_value was scanned to retrieve parents in the desired order, checking for each whether its parent_id appears in the materialized buffer. It had to read rows=169.00 parents before finding rows=10.00 that satisfied the condition, with lookups on the materialized result (loops=169).

                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.366..2.272 rows=10.00 loops=1)
   Output: p.parent_id, p.parent_value
   Buffers: shared hit=245
   ->  Nested Loop Semi Join (actual time=0.365..2.270 rows=10.00 loops=1)
         Output: p.parent_id, p.parent_value
         Join Filter: (p.parent_id = c.parent_id)
         Rows Removed by Join Filter: 16246
         Buffers: shared hit=245
         ->  Index Scan using parent_parent_value_idx on public.parent p (actual time=0.012..0.080 rows=169.00 loops=1)
               Output: p.parent_id, p.parent_value
               Index Searches: 1
               Buffers: shared hit=142
         ->  Materialize (actual time=0.000..0.006 rows=96.19 loops=169)
               Output: c.parent_id
               Storage: Memory  Maximum Storage: 20kB
               Buffers: shared hit=103
               ->  Bitmap Heap Scan on public.child c (actual time=0.032..0.151 rows=99.00 loops=1)
                     Output: c.parent_id
                     Recheck Cond: (c.child_value = '0.9999'::double precision)
                     Heap Blocks: exact=99
                     Buffers: shared hit=103
                     ->  Bitmap Index Scan on child_child_value_idx (actual time=0.016..0.016 rows=99.00 loops=1)
                           Index Cond: (c.child_value = '0.9999'::double precision)
                           Index Searches: 1
                           Buffers: shared hit=4

The other index on parent_value can serve the sort but not the filter on child_value. In both cases, many more rows must be read to combine the two conditions after a Nested Loop Join. That is the price of normalization.

In a SQL database, if we want a single index to cover both fields, they must exist in the same table.

Denormalize into the "Many" Side (Children)

A common solution is to duplicate the parent value into the child table and create a compound index:

alter table child add parent_value float;

update child
  set parent_value = parent.parent_value
  from parent
  where child.parent_id = parent.parent_id;

alter table child alter parent_value set not null;

create index on child (child_value, parent_value);

The query can now operate on the child table only:

-- explain (analyze, buffers, verbose, costs off)
select distinct parent_id, parent_value
 from child
 where child_value = 0.9999
 order by parent_value
 limit 10;

 parent_id |     parent_value
-----------+----------------------
       956 | 0.013518349069161273
       789 | 0.022813950892476287
       307 | 0.024740344416860793
       833 | 0.041118650516225985
       493 |  0.07587347477845374
       402 |  0.08400127026866477
       256 |   0.0980166832909124
       342 |  0.11175505348300807
       846 |  0.12047992766758941
       590 |  0.16840580135419425
(10 rows)

The execution plan shows a single index scan which covers:

the filter: Index Cond: child_value = 0.9999
the order: Presorted Key: child.parent_value

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.067..0.072 rows=10.00 loops=1)
   Output: parent_id, parent_value
   Buffers: shared hit=36
   ->  Unique (actual time=0.066..0.070 rows=10.00 loops=1)
         Output: parent_id, parent_value
         Buffers: shared hit=36
         ->  Incremental Sort (actual time=0.066..0.066 rows=11.00 loops=1)
               Output: parent_id, parent_value
               Sort Key: child.parent_value, child.parent_id
               Presorted Key: child.parent_value
               Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB
               Buffers: shared hit=36
               ->  Index Scan using child_child_value_parent_value_idx on public.child (actual time=0.014..0.054 rows=33.00 loops=1)
                     Output: parent_id, parent_value
                     Index Cond: (child.child_value = '0.9999'::double precision)
                     Index Searches: 1
                     Buffers: shared hit=36
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.132 ms
 Execution Time: 0.088 ms

There is an additional sort because the result may contain duplicate parents when they have multiple children with the same value, but it is an incremental sort so it does not have to read all children — here only rows=33.00 before producing rows=10.00. This gets the top ten parents by parent_value without reading more child rows than necessary.

This is nearly optimal. The new index resembles a multi-key index, except that it may contain duplicate entries for the same parent — unnecessary for this query, but potentially useful for others that need the detail of individual children. Like the multi-key index, it must be maintained when the parent value changes. We have two solutions: referential integrity constraint and triggers.

Update with Cascade Constraint (Recommended)

One way to keep this column automatically updated is declarative. We add an additional foreign key that enforces that the parent_value in the child table matches the one in the parent, and declare it on update cascade:

alter table parent add unique (parent_id, parent_value);

alter table child
 add constraint fk_child_parent
 foreign key (parent_id, parent_value)
 references parent (parent_id, parent_value)
 on update cascade;

Unfortunately, PostgreSQL cannot use the same index for two unique constraints that share a prefix, so two indexes are maintained on the parent table. This solution is intended for cases where updates are not frequent.

I test the performance overhead when updating one parent:

\timing on
explain (analyze, buffers, costs off, verbose on)
 update parent
 set parent_value = parent_value - 0.000000000000000042
 where parent_id = 789
;

                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Update on public.parent (actual time=0.063..0.064 rows=0.00 loops=1)
   Buffers: shared hit=16
   ->  Index Scan using parent_parent_id_parent_value_key on public.parent (actual time=0.022..0.025 rows=1.00 loops=1)
         Output: (parent_value - '4.2e-17'::double precision), ctid
         Index Cond: (parent.parent_id = 789)
         Index Searches: 1
         Buffers: shared hit=5
 Planning Time: 0.080 ms
 Trigger RI_ConstraintTrigger_a_18059 for constraint fk_child_parent on parent: time=37.950 calls=1
 Trigger RI_ConstraintTrigger_c_18061 for constraint fk_child_parent on child: time=6.767 calls=1000
 Execution Time: 44.874 ms

There is an overhead: 37.9 milliseconds to cascade the update to a thousand of children (parent action), and 6.7 milliseconds to validate each _c_hild row (child check).

This is not problematic for occasional updates. Of course, if we update all parents, the row-by-row cascading constraints can be slow:

\timing on
explain (analyze, buffers, costs off, verbose on)
 update parent
 set parent_value = parent_value - 0.000000000000000042
;
vacuum parent, child;

                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Update on public.parent (actual time=5.944..5.945 rows=0.00 loops=1)
   Buffers: shared hit=10975 dirtied=1
   ->  Seq Scan on public.parent (actual time=0.010..0.156 rows=1000.00 loops=1)
         Output: (parent_value - '4.2e-17'::double precision), ctid
         Buffers: shared hit=11
 Planning:
   Buffers: shared hit=86
 Planning Time: 0.235 ms
 Trigger RI_ConstraintTrigger_a_18059 for constraint fk_child_parent on parent: time=42659.655 calls=506
 Trigger RI_ConstraintTrigger_c_18061 for constraint fk_child_parent on child: time=3398.155 calls=506000
 Execution Time: 46103.577 ms
(11 rows)

Time: 46105.778 ms (00:46.106)

Obviously this is not a solution for frequently updated columns. However, the parent side of a one-to-many relationship often consists of static references, slowly changing dimensions, or immutable event headers, making this overhead acceptable. I also tested with the constraint set as DEFERRABLE. The elapsed time is similar, but you do not see it in the EXPLAIN output because the validation occurs at commit time.

Update with Trigger (Not Recommended)

The cascade constraint has the advantage of being declarative, but it executes internally as a trigger. We can instead create a custom trigger that cascades the update without re-validating the foreign key — assuming that nobody updates the denormalized column directly, and that it is set correctly on insert.

-- function to update the parent value in the children
create or replace function sync_parent_value() returns trigger as $$
begin
  update child set parent_value = new.parent_value
  where parent_id = new.parent_id;
  return new;
end;
$$ language plpgsql;

-- trigger raised for each update
create trigger trg_sync_parent_value
after update of parent_value on parent
for each row
when (old.parent_value is distinct from new.parent_value)
execute function sync_parent_value();

-- drop the previous constraint
alter table child drop constraint fk_child_parent;
alter table parent drop constraint parent_parent_id_parent_value_key;

I test the update of all parents:

\timing on
explain (analyze, buffers, costs off, verbose on)
 update parent
 set parent_value = parent_value - 0.000000000000000042
;
vacuum parent, child;

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Update on public.parent (actual time=4.547..4.547 rows=0.00 loops=1)
   Buffers: shared hit=8453 dirtied=26
   ->  Seq Scan on public.parent (actual time=0.009..0.124 rows=1000.00 loops=1)
         Output: (parent_value - '4.2e-17'::double precision), ctid
         Buffers: shared hit=11
 Planning:
   Buffers: shared hit=8 dirtied=2
 Planning Time: 0.108 ms
 Trigger trg_sync_parent_value: time=42719.154 calls=506
 Execution Time: 42724.042 ms
(10 rows)

Time: 42729.150 ms (00:42.729)

postgres=# vacuum parent, child;

VACUUM
Time: 176.585 ms

The performance advantage over the cascade constraint is minimal, and the risk is higher because parent_value in the child table could be updated independently without any check that it still matches the parent. We would also need an insert trigger to set the correct value, and an update trigger to prevent direct modifications. Ultimately, the cascade constraint solution is strongly preferable.

Denormalize into the "One" Side (Parent)

If we want to more closely mimic a MongoDB multi-key index, denormalizing into the child table is not an exact equivalent because there is no deduplication. It has its advantages — it can serve queries that need individual child rows — but a structure analogous to a multi-key index should be stored on the parent side, where each parent holds a summary of its children's values.

One approach is to add an array of child values to the parent table so that we can filter on it:

-- add array column
alter table parent add child_values float[];

-- populate from child
update parent p set child_values = (
  select array_agg(distinct child_value)
  from child where parent_id = p.parent_id
);

A trigger must maintain the array when children change:

create or replace function sync_child_values_array() returns trigger as $$
begin
  update parent set child_values = (
    select array_agg(distinct child_value)
    from child where parent_id = coalesce(new.parent_id, old.parent_id)
  ) where parent_id = coalesce(new.parent_id, old.parent_id);
  return new;
end;
$$ language plpgsql;

create trigger trg_sync_child_values_array
after insert or update or delete on child
for each row
execute function sync_child_values_array();

The triggers are examples, but you must cover all possible cases in your environment. For example, here, we do not expect children moved to another parent by updating parent_id, which should update both parents.

Now that we have the child values in the parent, we can create a compound index. But we cannot use regular B-Tree on fields within an array, and need an inverted index: GIN or RUM.

GIN Index (and `btree_gin` Extension)

As an array is involved, we need an inverted index. PostgreSQL provides GIN indexes, and they can be combined with scalar columns using the btree_gin extension:

-- required extension
create extension if not exists btree_gin;

-- compound GIN index on the array and the parent value
create index on parent using gin (parent_value, child_values);

Now the query gives the same result, but the compound GIN index cannot avoid a sort:

explain (analyze, buffers, verbose, costs off)
select parent_id, parent_value
 from parent
 where child_values @> array[0.9999::float]
 order by parent_value
 limit 10;

                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.079..0.081 rows=10.00 loops=1)
   Output: parent_id, parent_value
   Buffers: shared hit=14
   ->  Sort (actual time=0.079..0.080 rows=10.00 loops=1)
         Output: parent_id, parent_value
         Sort Key: parent.parent_value
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=14
         ->  Bitmap Heap Scan on public.parent (actual time=0.031..0.060 rows=93.00 loops=1)
               Output: parent_id, parent_value
               Recheck Cond: (parent.child_values @> '{0.9999}'::double precision[])
               Heap Blocks: exact=10
               Buffers: shared hit=14
               ->  Bitmap Index Scan on parent_parent_value_child_values_idx (actual time=0.018..0.018 rows=93.00 loops=1)
                     Index Cond: (parent.child_values @> '{0.9999}'::double precision[])
                     Index Searches: 1
                     Buffers: shared hit=4

Because GIN requires a bitmap scan, preserving the ordering on parent_value is not possible. Such an index can be used for queries with predicates on both fields, but our pagination query uses it less efficiently. It remains acceptable here (reading 93 rows to return 10) because the predicate is selective enough that few rows need sorting.

Partial B-tree Index for known predicates

If the predicate is not very selective—because the value being filtered on is popular—the previous index is not very efficient. If you have frequent filtering on a few specific values, you can create partial indexes:

create index on parent (parent_value)
 where child_values @> array[0.9999::float]
;

Then the execution that uses this exact predicate is optimal, reading only 10 index entries to produce the 10 result rows:

explain (analyze, buffers, verbose, costs off)
select parent_id, parent_value
 from parent
 where child_values @> array[0.9999::float]
 order by parent_value
 limit 10;

                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.013..0.024 rows=10.00 loops=1)
   Output: parent_id, parent_value
   Buffers: shared hit=10 read=1
   ->  Index Scan using parent_parent_value_idx on public.parent (actual time=0.012..0.022 rows=10.00 loops=1)
         Output: parent_id, parent_value
         Index Searches: 1
         Buffers: shared hit=10 read=1
 Planning:
   Buffers: shared hit=15 read=1
 Planning Time: 0.314 ms
 Execution Time: 0.034 ms

This is the fastest possible execution for a known predicate. However, it requires creating a separate partial index for each distinct filter value, which is impractical when the set of possible predicates is large or unpredictable.

DocumentDB Multi-Key Index (PostgreSQL with extended RUM)

While standard PostgreSQL GIN indexes cannot return ordered results, the DocumentDB extension for PostgreSQL provides a true multi-key index implementation. DocumentDB stores documents as BSON and uses an enhanced RUM access method (documentdb_extended_rum) that maintains multi-key index entries—just like MongoDB does natively. This enables the query engine to push both filter and sort into a single index scan with early termination via LIMIT.

I start a DocumentDB container and connect with psql:

docker run -d --name documentdb-local \
  -p 10260:10260 \
  -p 9712:9712 \
  ghcr.io/documentdb/documentdb/documentdb-local:latest \
  --username postgres \
  --password Iapetus \
  --start-pg
docker exec -it documentdb-local psql -p 9712 postgres

After creating the same relational tables as before, I create a DocumentDB collection and its compound index:

-- create the index first (on empty collection)
select documentdb_api_internal.create_indexes_non_concurrently(
  'mydb',
  '{
    "createIndexes": "parents",
    "indexes": [{
      "key": { "children.child_value": 1, "parent_value": 1 },
      "name": "child_value_parent_value_idx"
    }]
  }'::documentdb_core.bson
);

-- populate from the relational tables, embedding children as an array
select documentdb_api.insert_one('mydb', 'parents',
  format('{"parent_id": %s, "parent_value": %s, "children": %s}',
    p.parent_id, p.parent_value,
    (select coalesce(json_agg(json_build_object('child_value', c.child_value)), '[]')
     from child c where c.parent_id = p.parent_id)
  )::documentdb_core.bson, null
) from parent p;

Now, I run the equivalent query. The filter and sort match the MongoDB query from the beginning of this article:

explain (analyze, buffers, costs off)
select document from bson_aggregation_find(
  'mydb',
  '{
    "find": "parents",
    "filter": { "children.child_value": 0.9999 },
    "sort": { "parent_value": 1 },
    "limit": 10,
    "projection": { "parent_id": 1, "parent_value": 1, "_id": 0 }
  }'::documentdb_core.bson
);

                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.179..0.890 rows=10 loops=1)
   Buffers: shared hit=95
   ->  Index Scan using child_value_parent_value_idx on documents_10 collection (actual time=0.178..0.888 rows=10 loops=1)
         Index Cond: (document @= '{ "children.child_value" : { "$numberDouble" : "0.99990000000000001101" } }'::bson)
         Order By: (document |-<> '{ "parent_value" : { "$numberInt" : "1" } }'::bson)
         Buffers: shared hit=95
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.274 ms
 Execution Time: 0.916 ms
(10 rows)

The plan shows a single index scan—no sort node, no bitmap scan. The index provides both the filter (Index Cond) and the ordering (Order By) simultaneously. With LIMIT 10, the executor stops after reading 10 matching documents from the index, regardless of how many total parents contain the value.

Looking at the underlying storage, we can see the extended RUM index that makes this possible:

\d documentdb_data.documents_10

postgres=# \d documentdb_data.documents_10
           Table "documentdb_data.documents_10"
     Column      |  Type  | Collation | Nullable | Default
-----------------+--------+-----------+----------+---------
 shard_key_value | bigint |           | not null |
 object_id       | bson   |           | not null |
 document        | bson   |           | not null |
Indexes:
    "collection_pk_10" PRIMARY KEY, btree (shard_key_value, object_id)
    "documents_rum_index_29" documentdb_extended_rum (document documentdb_extended_rum_catalog.bson_extended_rum_composite_path_ops (pathspec='[ "children.child_value", "parent_value" ]', tl='2691'))
Check constraints:
    "shard_key_value_check" CHECK (shard_key_value = '10'::bigint)

The documentdb_extended_rum index stores BSON paths as composite keys, exploding array elements into multiple index entries—the same structure as a MongoDB multi-key index, implemented in PostgreSQL's extensible index infrastructure.

As I used this collection to duplicate data, we need to keep it in sync with the relational tables and add triggers:

-- sync child changes to the DocumentDB collection  
create or replace function sync_child_to_documentdb() returns trigger as $$  
begin  
  -- rebuild the entire children array for the affected parent  
  perform documentdb_api.update(  
    'mydb',  
    format('{  
      "update": "parents",  
      "updates": [{  
        "q": { "parent_id": %s },  
        "u": { "$set": { "children": %s } }  
      }]  
    }',  
      coalesce(new.parent_id, old.parent_id),  
      (select coalesce(json_agg(json_build_object('child_value', c.child_value)), '[]')  
       from child c where c.parent_id = coalesce(new.parent_id, old.parent_id))  
    )::documentdb_core.bson  
  );  
  return new;  
end;  
$$ language plpgsql;  

create trigger trg_sync_child_to_documentdb  
after insert or update or delete on child  
for each row  
execute function sync_child_to_documentdb();  

-- sync parent_value updates to the DocumentDB collection  
create or replace function sync_parent_to_documentdb() returns trigger as $$  
begin  
  perform documentdb_api.update(  
    'mydb',  
    format('{  
      "update": "parents",  
      "updates": [{  
        "q": { "parent_id": %s },  
        "u": { "$set": { "parent_value": %s } }  
      }]  
    }', new.parent_id, new.parent_value)::documentdb_core.bson  
  );  
  return new;  
end;  
$$ language plpgsql;  

create trigger trg_sync_parent_to_documentdb  
after update of parent_value on parent  
for each row  
when (old.parent_value is distinct from new.parent_value)  
execute function sync_parent_to_documentdb();

Of course, if the DocumentDB collection is the primary data store rather than a synchronized copy, these triggers are unnecessary—you would simply use the DocumentDB API functions (or MongoDB wire protocol on port 10260) for all reads and writes, and the multi-key index is maintained automatically.

Conclusion

MongoDB's multi-key index is a B-tree that explodes array entries, creating one index entry per distinct array element per document. This allows a single index to efficiently serve equality filters on child values combined with sorts on parent values, with early termination via limit. PostgreSQL has no native equivalent: GIN indexes handle array containment but cannot produce ordered output, and B-tree indexes cannot span multiple tables. However, it is extensible.

We explored several strategies to approximate this behavior, filter with pagination, in PostgreSQL:

Approach	Read Performance	Write Cost	Complexity
Normalized with `EXISTS`	Moderate: two index scans, semi-join	Low	Low
Denormalized into child + cascade FK	Near-optimal (single index, incremental sort for dedup)	High (all children updated on parent change)	Moderate
Array on parent + GIN	Low (sort after bitmap scan)	Moderate (one parent row per child change)	Moderate (triggers)
Array on parent + partial B-tree index	Optimal (index scan, no sort, stops at 10)	Low (one parent row per child change)	Moderate (one index per predicate)
DocumentDB extended RUM	Optimal (ordered index scan, no sort, stops at 10)	Low (one document per parent change)	Low (native multi-key semantics)

The best choice depends on the workload:

If parent_value rarely changes and queries need individual child rows, denormalizing into the child table with a cascade constraint is simple and effective. The compound B-tree index on the child table provides near-optimal read performance, and the cascade foreign key maintains consistency declaratively.
If the query only needs parents and the child predicate is known in advance, a partial B-tree index on the parent table with an array column delivers optimal read performance with minimal write overhead—only one parent row is updated when a child is inserted or deleted. The limitation is that a separate partial index is needed for each predicate value.
If the predicates are varied and unpredictable but a sort after filtering is acceptable, the GIN index provides a general-purpose solution that works with any array containment predicate, at the cost of a post-filter sort.
If the predicates are varied and unpredictable and you need optimal read performance without sorting, the DocumentDB extension provides the closest general-purpose equivalent to MongoDB's multi-key index, supporting array filtering with ordered output from a single index scan—with no write amplification on parent value updates since children are embedded in the same document.

In all cases, the standard PostgreSQL solution requires explicit denormalization and maintenance logic—whether through cascade constraints, triggers, or application code—to achieve what MongoDB's document model and multi-key indexes provide natively. The DocumentDB extension bridges this gap by bringing the document model directly into PostgreSQL, aligning the data structure and the index structure by design and eliminating the need for synchronization entirely.

BSON and OSON: documents are designed to be nested, not flat

Franck Pachot — Sat, 30 May 2026 08:23:05 +0000

I like vendor benchmarks as they are often good illustrations of worst practice. Rather than focusing on real implementation trade-offs, they pick an extreme case that favors their own implementation by chance but is not optimized in the competitor's — because it is simply not how that technology is meant to be used. For example, Oracle published "2x" benchmark results using YCSB, a key-value benchmark (slide 14 here), and a "529x" test misusing the raw BSON purpose (here). Both compare a relational database against a document database by using neither technology as it was designed to be used — no normalized tables, no nested documents, just flat key-value fields. The latter test is even worse: it uses 1,000 top-level fields in a single document. Don't do that!

While having thousands of columns in an SQL table is usually undesirable, it's acceptable to have hundreds or even thousands of fields in a document, as they represent multiple entities and value objects. However, nobody creates a flat structure with 1,000 top-level fields. The advantage of JSON is its ability to organize entities into nested sub-documents. For instance, instead of storing first_name and last_name as separate fields, you can have a name field containing a sub-object with first and last. During queries, using dot notation to reference name.first and name.last makes no difference from first_name or last_name. It simplifies reading and displaying the document, and, as that is how it should be used, the binary JSON formats are optimized for it.

To illustrate this, I created two collections, flat with one thousand fields at the top level, and nest with ten top-level fields, each containing a sub-object with ten sub-objects.


{
echo field{0..9}{0..9}{0..9}
for i in {1..100000} ; do echo {0..9}{0..9}{0..9} ; done
} | sed 's/ /,/g' |
 mongoimport --collection "flat" --type=csv --headerline --drop

{
echo field{0..9}.sub{0..9}.sub{0..9}
for i in {1..100000} ; do echo {0..9}{0..9}{0..9} ; done
} | sed 's/ /,/g' |
 mongoimport --collection "nest" --type=csv --headerline --drop

The flat documents are like:

  { _id: ObjectId('6a1a1ed41d55219677a79c22'),
  field000: 0, field001: 1, field002: 2, field003: 3, field004: 4, field005: 5, field006: 6, field007: 7, field008: 8, field009: 9,
  field010: 10, field011: 11, field012: 12, field013: 13, field014: 14, field015: 15, field016: 16, field017: 17, field018: 18, field019: 19,
  field020: 20, field021: 21, field022: 22, field023: 23, field024: 24, field025: 25, field026: 26, field027: 27, field028: 28, field029: 29,
  field030: 30, field031: 31, field032: 32, field033: 33, field034: 34, field035: 35, field036: 36, field037: 37, field038: 38,
...

The nested documents are like:

  {                                                                                                                                                                                               
    _id: ObjectId('6a1a160041c5538a7e93a9c5'),
    field0: {
      sub0: { sub0: 0, sub1: 1, sub2: 2, sub3: 3, sub4: 4, sub5: 5, sub6: 6, sub7: 7, sub8: 8, sub9: 9 },
      sub1: { sub0: 10, sub1: 11, sub2: 12, sub3: 13, sub4: 14, sub5: 15, sub6: 16, sub7: 17, sub8: 18, sub9: 19 },
      sub2: { sub0: 20, sub1: 21, sub2: 22, sub3: 23, sub4: 24, sub5: 25, sub6: 26, sub7: 27, sub8: 28, sub9: 29 },
      sub3: { sub0: 30, sub1: 31, sub2: 32, sub3: 33, sub4: 34, sub5: 35,
...

They hold the same values, with a different field structure.

BSON serialization format optimization

BSON is designed for efficient sequential streaming through network or disk access. Each field is stored one after another, with a small header containing the field type and name, followed by the value. Because every value type has either a fixed size (like a 64-bit integer or a double) or an explicit length prefix (like a string or a binary blob), the parser can skip over any field it doesn't care about without reading its value at all — it simply jumps forward by the declared length.

Nested sub-documents and arrays are stored the same way: the type is object or array, followed by the total byte length of the entire nested structure, followed by the nested fields themselves (for arrays, the field names are simply "0", "1", "2", and so on). That length prefix is the key optimization: if the parser is looking for a top-level field and the current field is a sub-document that doesn't match, it can skip the entire sub-document in one jump — all its nested fields, however deep — without reading a single byte inside it.

When you query a field using dot notation, such as "name.first", the parser works level by level. It scans the top-level fields looking for name. Any top-level field that doesn't match name is skipped, including any sub-documents, in a single jump. Once name is found and confirmed to be of type object, the parser steps into that sub-document and begins scanning its fields for first, again skipping anything that doesn't match. It never needs to read the sibling sub-documents of name at all.

The worst case is a field that doesn't exist: the parser must scan every field at the relevant nesting level before concluding it isn't there. But even then, sub-documents and arrays that are clearly irrelevant — because their parent name doesn't match — are each skipped in a single operation regardless of how large or deeply nested they are.

This is precisely why organizing many fields into a nested hierarchy is not just a cosmetic preference. A flat document with 1,000 top-level fields forces the parser to read 1,000 field names in the worst case. A document with 10 top-level fields, each containing 10 sub-fields, each containing 10 values, holds the same 1,000 values but the parser only ever reads at most 10 + 10 + 10 = 30 field names to locate any one of them. The BSON length prefix turns the nested structure into an implicit index, making the format genuinely faster to navigate at scale.

With the collection flat that has one thousand top-level fields from "field000" to "field999", looking for the last field "field999" takes more than one second:


db.flat.find(
 { "field999" : {$lt:42} }
).explain("executionStats").executionStats
;

{
  executionSuccess: true,
  nReturned: 0,
  executionTimeMillis: 1081,
  totalKeysExamined: 0,
  totalDocsExamined: 100000,
  executionStages: {
    isCached: false,
    stage: 'COLLSCAN',
    filter: { field999: { '$lt': 42 } },
    nReturned: 0,
    executionTimeMillisEstimate: 1070,
    works: 100001,
    advanced: 0,
    needTime: 100000,
    needYield: 0,
    saveState: 64,
    restoreState: 64,
    isEOF: 1,
    direction: 'forward',
    docsExamined: 100000
  }
}

With the collection nest that has ten top-level fields from "field0" to "field9", and two levels of ten sub-objects from "sub0" to "sub9", looking for the last field "field9.sub9.sub9" is twice as fast:


db.nest.find(
 { "field9.sub9.sub9" : {$lt:42} }
).explain("executionStats").executionStats
;

{
  executionSuccess: true,
  nReturned: 0,
  executionTimeMillis: 424,
  totalKeysExamined: 0,
  totalDocsExamined: 100000,
  executionStages: {
    isCached: false,
    stage: 'COLLSCAN',
    filter: { 'field9.sub9.sub9': { '$lt': 42 } },
    nReturned: 0,
    executionTimeMillisEstimate: 420,
    works: 100001,
    advanced: 0,
    needTime: 100000,
    needYield: 0,
    saveState: 21,
    restoreState: 21,
    isEOF: 1,
    direction: 'forward',
    docsExamined: 100000
  }
}

A major part of the time is fetching the document from storage. Navigating into it is a smaller part, but the difference is still clearly visible: 1,081 ms versus 424 ms for 100,000 documents, with the same number of values, and the same worst-case field to find: the last one. The only difference is how those fields are arranged. Nesting them three levels deep, with ten branches at each level, makes the collection scan faster — with no index, no schema change, and no query rewrite beyond the dot notation that you would use anyway.

This is not a micro-optimization or an edge case. Any collection scan, whether triggered by a missing index, a low-selectivity filter, or a background analytics query, pays this cost on every document it reads. The deeper and wider your documents are, the more the BSON length-prefix trick pays off, because the parser can leap over entire branches of the document tree in a single bounds check.

What about Oracle's OSON format?

Oracle Database stores JSON documents in its proprietary binary format called OSON. At first glance, OSON appears to take a fundamentally different approach: rather than storing field names inline with each value as BSON does, OSON builds a field name dictionary at the beginning of the document. Think of it like a mini-datastore with its catalog and indexes, rather than a protocol buffer. Each field in the document body then refers to its name by a short numeric ID rather than repeating the full string. This makes individual field names cheaper to compare and reduces document size when the same field name appears many times.

Given that design, you might expect nesting to make no difference in Oracle: if every field is just a numeric ID anyway, scanning 1,000 flat fields should cost the same as scanning 10 + 10 + 10 fields spread across three levels. The dictionary lookup cost is the same either way.

In practice, however, the same experiment on Oracle Database shows a very similar result to MongoDB.

Querying field999 on the flat table takes about 1.6 seconds:


set autotrace traceonly

select /*+ gather_plan_statistics */ "DATA" from "ORA"."flat"
 where JSON_EXISTS("DATA",'$?(@.field999.numberOnly() < $B0)'
 passing 42 as "B0" type(strict))
;

PLAN_TABLE_OUTPUT
________________________________________________________________________________________________
SQL_ID  1h98k751w7dws, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ "DATA" from "ORA"."flat"  where
JSON_EXISTS("DATA",'$?(@.field999.numberOnly() < $B0)'  passing 42 as
"B0" type(strict))

Plan hash value: 4073748891
---------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      0 |00:00:01.60 |     301K|    739 |
|*  1 |  TABLE ACCESS FULL| flat |      1 |     12 |      0 |00:00:01.60 |     301K|    739 |
---------------------------------------------------------------------------------------------

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

   1 - filter(JSON_EXISTS2( /*+ QJSNMD_USE_SHR_XCTX */ "DATA" /*+ LOB_BY_VALUE */
              FORMAT OSON , '$?(@.field999.numberOnly() < $B0)' /* json_path_str
              $?(@.field999.numberOnly() < $B0)  */  PASSING 42 AS "B0" FALSE ON ERROR
              TYPE(STRICT) )=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
-----------------------------------------------------------
             117  CPU used by this session
             117  CPU used when call started
             160  DB time
         1174164  RM usage by this session
               3  Requests to/from client
             738  Session total flash IO requests
         6053888  cell physical IO interconnect bytes
          301074  consistent gets
          301074  consistent gets from cache
          301074  consistent gets pin
            1068  consistent gets pin (fastpath)
             739  gcs data block access records
               1  messages sent
             747  non-idle wait count
              48  non-idle wait time
               2  opened cursors cumulative
               1  opened cursors current
             738  physical read total IO requests
         6053888  physical read total bytes
               2  process last non-idle time
          301074  session logical reads
              48  user I/O wait time
               4  user calls

Querying field9.sub9.sub9 on the nested table takes about 1.0 second:


select /*+ gather_plan_statistics */ "DATA" from "ORA"."nest"
 where JSON_EXISTS("DATA",'$?(@.field9.sub9.sub9.numberOnly() < $B0)'
 passing 42 as "B0" type(strict))
;

PLAN_TABLE_OUTPUT
__________________________________________________________________________________________________
SQL_ID  7yuwrup6rscrc, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ "DATA" from "ORA"."nest"   where
JSON_EXISTS("DATA",'$?(@.field9.sub9.sub9.numberOnly() < $B0)'
passing 42 as "B0" type(strict))

Plan hash value: 3225864993
---------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      0 |00:00:01.04 |     100K|   1143 |
|*  1 |  TABLE ACCESS FULL| nest |      1 |   1000 |      0 |00:00:01.04 |     100K|   1143 |
---------------------------------------------------------------------------------------------

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

   1 - filter(JSON_EXISTS2( /*+ QJSNMD_USE_SHR_XCTX */ "DATA" /*+ LOB_BY_VALUE */
              FORMAT OSON , '$?(@.field9.sub9.sub9.numberOnly() < $B0)' /* json_path_str
              $?(@.field9.sub9.sub9.numberOnly() < $B0)  */  PASSING 42 AS "B0" FALSE ON ERROR
              TYPE(STRICT) )=1)


Statistics
-----------------------------------------------------------
              40  CPU used by this session
              40  CPU used when call started
             106  DB time
          404934  RM usage by this session
               4  Requests to/from client
            1125  Session total flash IO requests
         9363456  cell physical IO interconnect bytes
          100221  consistent gets
          100221  consistent gets from cache
          100221  consistent gets pin
           99096  consistent gets pin (fastpath)
            1142  gcs data block access records
             158  global enqueue gets sync
             158  global enqueue releases
            1134  non-idle wait count
              71  non-idle wait time
               2  opened cursors cumulative
               1  opened cursors current
            1125  physical read total IO requests
         9363456  physical read total bytes
              61  process last non-idle time
          100221  session logical reads
              71  user I/O wait time
               4  user calls

The most telling number is not the execution time but the Buffers column: 301,074 consistent gets for the flat table versus 100,221 for the nested table. Oracle is reading three times as many data blocks for the flat documents. To understand why, I check the actual segment sizes:


with
table_seg (owner,table_name, table_blocks) as ( select owner, segment_name, sum(blocks) from dba_segments group by owner,segment_name),
lob_seg   (owner,table_name,   lob_blocks) as ( select owner, table_name,   sum(blocks) from dba_lobs join dba_segments using (owner, segment_name) group by owner,table_name),
tab_stats (owner,table_name,  avg_row_len) as ( select owner, table_name,   avg_row_len from dba_tables)
select owner, table_name, table_blocks, nvl(lob_blocks, 0) as lob_blocks, table_blocks + nvl(lob_blocks, 0) as total_blocks, avg_row_len
 from tab_stats natural join table_seg natural left join lob_seg
 where owner = 'ORA' and table_name in ('flat', 'nest')
;

OWNER    TABLE_NAME       TABLE_BLOCKS    LOB_BLOCKS    TOTAL_BLOCKS    AVG_ROW_LEN
________ _____________ _______________ _____________ _______________ ______________
ORA      flat                    1,152       317,608         318,760            166
ORA      nest                  100,992            32         101,024          7,724

The segment query reveals the full story. The nested documents have an average row length of 7,724 bytes and are stored entirely inline in the table segment's blocks, with almost no LOB blocks. The flat documents have an average row length of only 166 bytes in the table segment, but they spill into 317,608 LOB blocks. That tiny average row length for flat is not a sign of small documents — it is the sign of a pointer because large documents do not fit in fixed-sized table blocks. The actual OSON bytes have been pushed out of the row into a separate LOB segment managed by Oracle's securefile infrastructure, and every document access requires an additional pointer dereference to fetch them from there.

The root cause is the OSON dictionary. OSON stores each distinct field name only once in a per-document dictionary and replaces every occurrence in the document body with a short numeric ID. The dictionary itself is the mechanism that compresses the field names. A flat document with 1,000 entirely unique field names like field000 through field999 requires a dictionary with 1,000 entries, one per distinct string, with no repetition to exploit. The resulting OSON document is large enough to exceed Oracle's block size and gets stored out of row as a LOB. A nested document whose entire structure uses only 20 distinct names — field0 through field9 and sub0 through sub9 — has a dictionary with just 20 short entries. Furthermore, objects that share the same field structure can reuse each other's field ID array entirely rather than repeating it. The resulting document is compact enough to be stored inline, directly in the table block, with no LOB indirection at all.

The consequence is dramatic here. Scanning the flat collection means fetching a pointer from the table block, then chasing it to a LOB block, for every single one of the 100,000 documents. Scanning the nested collection means reading the document directly from the table block. That is the difference between 301,074 consistent gets and 100,221.

So the two formats reach the same conclusion by different paths. BSON benefits from nesting because the length-prefix on sub-documents lets the parser skip entire branches with a single jump, reducing the number of field names it reads. OSON benefits from nesting because the dictionary compresses repeated field names, shrinking the documents enough to keep them inline in the table and avoiding the cost of LOB storage entirely.

JSONB and BSON on PostgreSQL

JSONB takes a different approach from BSON. Rather than storing fields in insertion order with length-prefix skipping, JSONB sorts keys at each nesting level and adds a header describing the offset and type of every value at that level, enabling binary search instead of sequential scanning. Because this structure is built independently per level, a nested document gets a short sorted list at each level rather than one long list at the top. The benefit of nesting is the same as with BSON, just through a different mechanism.

I imported the same data into PostgreSQL:

CREATE TABLE flat (data JSONB);  
CREATE TABLE nest (data JSONB);
\copy flat (data) FROM PROGRAM 'mongoexport --collection flat'  
\copy nest (data) FROM PROGRAM 'mongoexport --collection nest' 

VACUUM ANALYZE flat, nest
;

The query on the flat structure takes more than two seconds:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, COSTS OFF)  
SELECT * FROM flat  
WHERE (data->>'field999')::int < 42;  

                                 QUERY PLAN
----------------------------------------------------------------------------
 Seq Scan on public.flat (actual time=2138.111..2138.111 rows=0.00 loops=1)
   Output: data
   Filter: (((flat.data ->> 'field999'::text))::integer < 42)
   Rows Removed by Filter: 100000
   Buffers: shared hit=400637
 Planning Time: 0.043 ms
 Execution Time: 2138.126 ms
(7 rows)

The query on the nested structure is twice as fast:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, COSTS OFF)  
SELECT * FROM nest  
WHERE (data->'field9'->'sub9'->>'sub9')::int < 42; 

                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on public.nest (actual time=1292.003..1292.003 rows=0.00 loops=1)
   Output: data
   Filter: (((((nest.data -> 'field9'::text) -> 'sub9'::text) ->> 'sub9'::text))::integer < 42)
   Rows Removed by Filter: 100000
   Buffers: shared hit=400637
 Planning Time: 0.041 ms
 Execution Time: 1292.017 ms
(7 rows)

2,138 ms versus 1,292 ms, same documents, same values, same worst-case field. The difference is purely the cost of navigating the JSONB structure. The nested document wins because each level presents the parser with a short sorted list of at most 10 keys rather than a single list of 1,000. It is interesting to see that both tables have the same values but different field names, due to nesting, finally have exactly the same number of pages (shared hit=400637) in JSONB/TOAST.

PostgreSQL can also store documents in BSON natively via the DocumentDB extension, which uses the same BSON library as MongoDB. I loaded the same data there to confirm the result holds.

DocumentDB is a PostgreSQL database, so I ran the JSONB queries above on the same instance (the PostgreSQL endpoint port is 9712 in DocumentDB docker container by default). Then I used the DocumentDB extension API to import the same data from JSONB tables to DocumentDB collections:

postgres=# \dx documentdb*
                                List of installed extensions
          Name           | Version | Schema |                  Description
-------------------------+---------+--------+-----------------------------------------------
 documentdb              | 0.111-0 | public | API surface for DocumentDB for PostgreSQL
 documentdb_core         | 0.111-0 | public | Core API surface for DocumentDB on PostgreSQL
 documentdb_extended_rum | 0.111-0 | public | DocumentDB Extended RUM index access method
(3 rows)

-- Insert from the PostgreSQL JSONB tables into DocumentDB collections

SELECT documentdb_api.insert_one('db', 'flat', document)
FROM (SELECT data::text::documentdb_core.bson AS document FROM flat) t;

SELECT documentdb_api.insert_one('db', 'nest', document)
FROM (SELECT data::text::documentdb_core.bson AS document FROM nest) t;

I connected to the same DocumentDB database with the MongoDB-compatible API (the MongoDB endpoint port is 10260 in DocumentDB docker container by default).

The query on the flat structure with one thousand fields took 3 seconds:

db.flat.find(
 { "field999" : {$lt:42} }
).explain("executionStats").executionStats
;

{
  nReturned: Long('0'),
  executionTimeMillis: 3285.758,
  executionStartAtTimeMillis: 3285.758,
  totalDocsExamined: Long('100000'),
  totalKeysExamined: Long('0'),
  executionStages: {
    stage: 'COLLSCAN',
    nReturned: Long('0'),
    executionTimeMillis: 3285.758,
    executionStartAtTimeMillis: 3285.758,
    totalDocsExamined: 100000,
    totalKeysExamined: 0,
    totalDocsRemovedByRuntimeFilter: 100000,
    numBlocksFromCache: 374177,
    numBlocksFromDisk: 76758
  }
}

The query on the nested structure is twice as fast:

db.nest.find(
 { "field9.sub9.sub9" : {$lt:42} }
).explain("executionStats").executionStats
;

{
  nReturned: Long('0'),
  executionTimeMillis: 1401.24,
  executionStartAtTimeMillis: 1401.24,
  totalDocsExamined: Long('100000'),
  totalKeysExamined: Long('0'),
  executionStages: {
    stage: 'COLLSCAN',
    nReturned: Long('0'),
    executionTimeMillis: 1401.24,
    executionStartAtTimeMillis: 1401.24,
    totalDocsExamined: 100000,
    totalKeysExamined: 0,
    totalDocsRemovedByRuntimeFilter: 100000,
    numBlocksFromCache: 349453,
    numBlocksFromDisk: 51482
  }
}

3,286 ms versus 1,401 ms — consistent with the MongoDB result and explained by the same BSON length-prefix mechanism. DocumentDB stores documents in BSON on top of PostgreSQL storage, so the nesting optimization is identical to native MongoDB when looking at a field value.

Conclusion

Format	Navigation mechanism	Why nesting helps
BSON	Sequential scan with length-prefix skip	Skips entire sub-documents in one jump
JSONB	Binary search on sorted keys per level	Keeps each sorted list short
OSON	Dictionary lookup	Reduces dictionary size

The underlying mechanisms differ, but the guidance is the same: documents are nested structures that aggregate multiple entities and value objects. Don’t chase performance with documents containing 1,000 top-level fields — fix the data model first.

Model your documents the way you naturally think about the data — as a hierarchy of related objects — and the binary JSON format your database uses will reward you for it. Whether that is BSON in MongoDB, BSON via the DocumentDB extension for PostgreSQL, JSONB, or OSON, flat documents with hundreds of top-level fields are not just harder to read — they are measurably slower to query and heavier on storage. Nesting is not just good document design. It is how these formats are engineered to be used.

$exists and non-sparse indexes in MongoDB and in other DocumentDB

Franck Pachot — Fri, 29 May 2026 19:41:42 +0000

In SQL databases, NULL represents an unknown value — not the absence of a value. When a value is simply non-applicable for a given entity, the correct relational modeling approach is normalization: the entity gets no row in the relevant table at all, rather than a NULL in a column. This distinction becomes tricky with OUTER JOIN results, where the absence of a row is surfaced as NULL across all columns of the unmatched side, including key columns — making it easy to confuse "unknown value" with "no row existed."

MongoDB has its own subtlety: a field can be explicitly set to null or simply not exist in the document at all. In the BSON representation, these are distinct — one is a key with a null-typed value, the other is the absence of the key entirely. The schema is flexible: you can define a field or not. But in indexes, this distinction disappears. Except for partial indexes, indexes must have a key value for every document it covers. For documents where the field is missing, MongoDB uses null as a stand-in — the same key value used for explicit nulls. This means an index scan cannot distinguish between the two states, and resolving null vs. missing requires fetching the full document to apply a residual filter.

Consequently, a standard index scan with a filter on null or $exists is inexact: the query planner performs an index scan on the null key and then fetches the full document to verify whether the field is truly null or simply absent.

An example: `{ $exists: true }` filter

When you query with { num: { $exists: true } }, you expect MongoDB to use an index on num. Let's test it on MongoDB, as well as some emulations: Oracle Database, Amazon DocumentDB (AWS), and DocumentDB extension on PostgreSQL (Microsoft).

Here is my test collection:

db.test.insertMany([
  { _id: 1, num: 42   },
  { _id: 2, num: 7    },
  { _id: 3, num: null },
  { _id: 4            },
  { _id: 5, num: 99   },
  { _id: 6            },
  { _id: 7, num: null },
  { _id: 8, num: 15   }
])

I have inserted eight documents:

four with real values (_id 1, 2, 5, 8),
two with the field explicitly set to null (_id 3, 7), and
two where the field is entirely absent (_id 4, 6).

The query { num: { $exists: true } } should return six documents — everything except _id 4 and 6.

Before touching indexes, notice that $exists is not the same as a null check:

db.test.find({ num: null })

[
  { _id: 3, num: null },
  { _id: 4 },
  { _id: 6 },
  { _id: 7, num: null }
]

db.test.find({ num: { $exists: false } })

[
  { _id: 4 },
  { _id: 6 }
]

db.test.find({ num: { $exists: true } })

[
  { _id: 1, num: 42 },
  { _id: 2, num: 7 },
  { _id: 3, num: null },
  { _id: 5, num: 99 },
  { _id: 7, num: null },
  { _id: 8, num: 15 }
]

A field set to null exists. A field not written into the document does not. This distinction is perfectly clear at the document level. At the index level, it is not.

Null in the index key is ambiguous

When MongoDB builds a B-tree index on num, it must create an entry for every document. For documents with no num field, the index key exists with a null value. For documents where num is explicitly set to null, it also stores null. Both cases produce the same index key.

Here is how the non-sparse index looks:

Non-sparse index on { num: 1 }:

  null  →  _id:3  { num: null }    explicit null
  null  →  _id:4  { }              missing field
  null  →  _id:6  { }              missing field
  null  →  _id:7  { num: null }    explicit null
  7     →  _id:2
  15    →  _id:8
  42    →  _id:1
  99    →  _id:5

The four entries under the null key are indistinguishable from the index alone. To evaluate $exists, the engine must read the actual document. This is called a residual predicate — a filter condition the index cannot resolve, deferred to a later fetch stage.

Another way to look at it: the document schema is flexible, with no structure declared upfront and fields that may or may not exist, whereas indexes are different—their schema is declared, and the key fields always exist.

MongoDB with a non-sparse index

I create a regular index, which is by default non-sparse and has one index entry per document (or more for multi-key indexes).

db.test.createIndex({ num: 1 })

db.test.find({ num: { $exists: true } }).explain("executionStats")

The execution plan shows what happens across the IXSCAN and FETCH stages:

executionStats: {
  nReturned: 6,
  totalKeysExamined: 8,
  totalDocsExamined: 8,
  executionStages: {
    stage: 'FETCH',
    filter: { num: { '$exists': true } },
    nReturned: 6,
    docsExamined: 8,
    inputStage: {
      stage: 'IXSCAN',
      nReturned: 8,
      isSparse: false,
      indexBounds: { num: [ '[MinKey, MaxKey]' ] },
      keysExamined: 8
    }
  }
}

The IXSCAN returns all 8 index entries across the full [MinKey, MaxKey] range. The FETCH stage then reads all 8 documents and applies filter: { num: { $exists: true } } as a residual predicate, discarding _id 4 and 6. Notice docsExamined: 8 but nReturned: 6 — two fetches were wasted. The index was used, but the null bucket forced unnecessary work.

MongoDB with a sparse index

A sparse index excludes documents where the indexed field is entirely absent. It does not exclude explicit null values. Documents _id 3 and 7 have num: null and are still indexed.

db.test.createIndex({ num: 1 }, { sparse: true })

db.test.find({ num: { $exists: true } }).explain("executionStats")

As I have no projection, there is still a FETCH, but only for the documents in the final result:

executionStats: {
  nReturned: 6,
  totalKeysExamined: 6,
  totalDocsExamined: 6,
  executionStages: {
    stage: 'FETCH',
    nReturned: 6,
    docsExamined: 6,
    inputStage: {
      stage: 'IXSCAN',
      nReturned: 6,
      isSparse: true,
      indexBounds: { num: [ '[MinKey, MaxKey]' ] },
      keysExamined: 6
    }
  }
}

keysExamined dropped from 8 to 6 — the two missing-field documents are not in the index. More importantly, the FETCH stage has no filter. There is no residual predicate. Every document pointed to by the sparse index either has a real value or has an explicit null — both satisfy $exists: true. The index structure itself proves the condition. The FETCH still happens because find() needs to return the documents, but it is doing useful work only, not wasted disambiguation.

Here is how the sparse index looks:

Sparse index on { num: 1 }:

  null  →  _id:3  { num: null }    explicit null — indexed
  null  →  _id:7  { num: null }    explicit null — indexed
  7     →  _id:2
  15    →  _id:8
  42    →  _id:1
  99    →  _id:5

  _id:4  { }  — not indexed
  _id:6  { }  — not indexed

The null bucket still exists in a sparse index, but it contains only explicit nulls. The ambiguity is gone.

Oracle Database

I reproduced the same on Oracle Database with the MongoDB emulation:

ora> db.test.createIndex({ num: 1 })
num_1
ora> db.test.find({ num: { $exists: true } }).explain("executionStats")
{
  queryPlanner: {
    namespace: 'ora.test',
    parsedQuery: { num: { '$exists': true } },
    rewrittenQuery: { num: { '$exists': true } },
    generatedSql: `select "DATA",rawtohex("RESID"),"ETAG" from "ORA"."test" where JSON_EXISTS("DATA",'$?(exists(@.num)) ' type(strict))`,
    winningPlan: ' Plan Hash Value  : 3552627291 \n' +
      '\n' +
      '--------------------------------------------------------------------------------------------------\n' +
      '| Id  | Operation                             | Name            | Rows | Bytes | Cost | Time     |\n' +
      '--------------------------------------------------------------------------------------------------\n' +
      '|   0 | SELECT STATEMENT                      |                 |    1 | 24501 |    2 | 00:00:01 |\n' +
      '|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | test            |    1 | 24501 |    2 | 00:00:01 |\n' +
      '|   2 |    HASH UNIQUE                        |                 |    1 | 24501 |      |          |\n' +
      '| * 3 |     INDEX RANGE SCAN (MULTI VALUE)    | $ora:test.num_1 |    1 |       |    1 | 00:00:01 |\n' +
      '--------------------------------------------------------------------------------------------------\n' +
      '\n' +
      'Predicate Information (identified by operation id):\n' +
      '------------------------------------------\n' +
      `* 3 - access(JSON_QUERY("DATA" /*+ LOB_BY_VALUE */ FORMAT OSON , '$."num"[*]' RETURNING ANY ORA_RAWCOMPARE ASIS WITHOUT ARRAY WRAPPER ERROR ON ERROR PRESENT ON EMPTY NULL ON MISMATCH TYPE(LAX)\n` +
      "  MULTIVALUE)>HEXTORAW('01'))\n" +
      '\n' +
      '\n' +
      'Notes\n' +
      '-----\n' +
      '- Dynamic sampling used for this statement ( level = 2 )\n' +
      '\n'
  },
  serverInfo: { host: 'localhost', port: 27017, version: '7.0.22' },
  ok: 1
}
ora>

It doesn't display the execution statistics, but I can get it from the SQL endpoint:

sql> select /*+ gather_plan_statistics */ "DATA",rawtohex("RESID"),"ETAG" from "ORA"."test" where JSON_EXISTS("DATA",'$?(exists(@.num)) ' type(strict));

DATA                    RAWTOHEX("RESID")    ETAG
_______________________ ____________________ ___________________________________
{"_id":3,"num":null}    03C104               523160F3D2777CB2E0637B5B000A71CD
{"_id":7,"num":null}    03C108               523160F3D27F7CB2E0637B5B000A71CD
{"_id":2,"num":7}       03C103               523160F3D2757CB2E0637B5B000A71CD
{"_id":8,"num":15}      03C109               523160F3D2817CB2E0637B5B000A71CD
{"_id":1,"num":42}      03C102               523160F3D2737CB2E0637B5B000A71CD
{"_id":5,"num":99}      03C106               523160F3D27B7CB2E0637B5B000A71CD

6 rows selected.

sql> select * from dbms_xplan.display_cursor(format=>'allstats last');

PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________
SQL_ID  c08vsvqpn75vw, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ "DATA",rawtohex("RESID"),"ETAG"
from "ORA"."test" where JSON_EXISTS("DATA",'$?(exists(@.num)) '
type(strict))

Plan hash value: 3552627291

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |      1 |        |      6 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| test            |      1 |      1 |      6 |00:00:00.01 |       2 |
|   2 |   HASH UNIQUE                       |                 |      1 |      1 |      6 |00:00:00.01 |       1 |
|*  3 |    INDEX RANGE SCAN (MULTI VALUE)   | $ora:test.num_1 |      1 |      1 |      6 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------------

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

   3 - access("test"."SYS_NC00005$">HEXTORAW('01'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


26 rows selected.

The index range scan returned 6 entries, as if it were a sparse index. We cannot create a sparse index on Oracle Database:

ora> db.test.dropIndex({ num: 1 })
{ nIndexesWas: 2, ok: 1 }

ora> db.test.createIndex({ num: 1 } , { sparse: 1 })
MongoServerError[MONGO-67]: Unsupported index option: sparse

Amazon DocumentDB (AWS)

AWS DocumentDB speaks the MongoDB wire protocol but is built on a completely different architecture. The storage layer is distributed like Aurora, replicated across three availability zones. The query planner and storage engine are specific to Amazon DocumentDB and deliver performance characteristics that differ from both MongoDB and standard PostgreSQL.

Non-sparse index on Amazon DocumentDB

The { num: { $exists: true } } query does not use the non-sparse index (created as db.test.createIndex({ num: 1 })) on Amazon DocumentDB (tested on version 8, planner version 3):

queryPlanner: {
  plannerVersion: 3,
  winningPlan: { stage: 'COLLSCAN', filter: { num: { '$exists': true } } }
},
executionStats: {
  nReturned: '6',
  executionTimeMillis: '14.121',
  planningTimeMillis: '14.019',
  executionStages: {
    stage: 'COLLSCAN',
    nReturned: '6',
    executionTimeMillisEstimate: '0.025'
  }
}

The index is completely abandoned. The planner chose a full collection scan.

Sparse index on Amazon DocumentDB

With the index created as db.test.createIndex({ num: 1 }, { sparse: true }), the index is used:

queryPlanner: {
  plannerVersion: 3,
  winningPlan: { stage: 'IXSCAN', indexName: 'num_1', direction: 'forward' }
},
executionStats: {
  nReturned: '6',
  executionTimeMillis: '10.034',
  planningTimeMillis: '8.128',
  executionStages: {
    stage: 'IXSCAN',
    nReturned: '6',
    executionTimeMillisEstimate: '1.842',
    indexName: 'num_1',
    direction: 'forward'
  }
}

Every entry in the sparse index provably satisfies $exists: true. It scans 6 index entries and returns 6 documents. While a sparse index is optional in MongoDB, it is mandatory in Amazon DocumentDB to use an index for this query at all.

Microsoft DocumentDB on PostgreSQL

Microsoft DocumentDB is implemented as an open-source PostgreSQL extension, accessed via the MongoDB wire protocol through a compatible endpoint.

With DocumentDB on PostgreSQL, a sparse index is not required for an optimal access path. I created the index as db.test.createIndex({ num: 1 }) and used a hint to force the index, since on a small collection the cost-based planner would otherwise prefer a sequential scan:

db.test.find(
  { num: { $exists: true } }
).hint("num_1").explain("executionStats")

(Be careful when using a hint with MongoDB queries, as it may change the result, limiting the scan to what is indexed)

The execution plan reads only the necessary entries from the index:

executionStats: {
  nReturned: Long('6'),
  executionTimeMillis: 0.093,
  executionStartAtTimeMillis: 0.089,
  totalDocsExamined: Long('6'),
  totalKeysExamined: Long('6'),
  executionStages: {
    stage: 'FETCH',
    nReturned: Long('6'),
    executionTimeMillis: 0.093,
    executionStartAtTimeMillis: 0.089,
    totalKeysExamined: 6,
    numBlocksFromCache: 24,
    inputStage: {
      stage: 'IXSCAN',
      nReturned: Long('6'),
      executionTimeMillis: 0.093,
      executionStartAtTimeMillis: 0.089,
      indexName: 'num_1',
      totalKeysExamined: 6,
      numBlocksFromCache: 24
    }
  }
}

It shows the same count for index entries (totalKeysExamined: 6) and documents fetched (totalDocsExamined: 6).

Here we can go further: we can bypass the MongoDB layer entirely and query PostgreSQL directly, seeing exactly what the database engine sees.

To understand why, we can look at the underlying implementation from the PostgreSQL catalog:

\d documentdb_data.documents_15

           Table "documentdb_data.documents_15"
     Column      |  Type  | Collation | Nullable | Default
-----------------+--------+-----------+----------+---------
 shard_key_value | bigint |           | not null |
 object_id       | bson   |           | not null |
 document        | bson   |           | not null |
Indexes:
    "collection_pk_15" PRIMARY KEY, btree (shard_key_value, object_id)
    "documents_rum_index_47" documentdb_extended_rum
        (document bson_extended_rum_composite_path_ops
         (pathspec='[ "num" ]', tl='2691'))

There are no individual columns for num, name, or any other document field. The entire document is stored as a single bson blob in the document column. PostgreSQL has no native knowledge of what is inside it. The collection name test maps to documents_15, where 15 is the collection's internal identifier.

The index is not a standard PostgreSQL B-tree. It is an Extended RUM index — documentdb_extended_rum — with a custom operator class: bson_extended_rum_composite_path_ops. RUM is an extension of GIN (Generalized Inverted Index) that adds support for ordering, range scans, and additional per-entry metadata. The operator class is the critical piece: it knows how to extract the num field from the opaque BSON blob and store it in a structure PostgreSQL can search. pathspec='[ "num" ]' tells it which field to index.

We can obtain the PostgreSQL execution plan directly using the DocumentDB API. I disabled sequential scans to override the cost-based planner's preference on this small table:

postgres=# set enable_seqscan to off;
           explain (analyze, buffers, verbose, costs off)
select document from bson_aggregation_find(
  'test',
  '{
    "find": "test",
    "filter": { "num": { "$exists": true } }
  }'::documentdb_core.bson
);
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Index Scan using num_1 on documentdb_data.documents_15 collection (actual time=0.036..0.040 rows=6 loops=1)
   Output: document
   Index Cond: (collection.document @>= '{ "num" : { "$minKey" : 1 } }'::bson)
   Buffers: shared hit=3
 Planning:
   Buffers: shared hit=26
 Planning Time: 0.416 ms
 Execution Time: 0.052 ms
(8 rows)

The $exists: true predicate has been translated into a PostgreSQL index condition: document @>= '{ "num": { "$minKey": 1 } }'::bson. This uses a custom BSON operator @>= meaning "document has field num with a value greater than or equal to MinKey."

MinKey is a special BSON sentinel value that sits below every other BSON value in the type ordering. The condition @>= MinKey therefore means "field num exists and has any BSON value at all" — which is exactly $exists: true. Existence becomes a range scan from the minimum possible value: an elegant encoding.

The RUM index is path-based and only creates entries for paths that actually exist in documents. However, documents where num is absent also have their index entry, that are scanned by the opposite filter { num: {$exists": false} }:

postgres=# explain (analyze, buffers, verbose, costs off)
select document from bson_aggregation_find(
  'test',
  '{
    "find": "test",
    "filter": { "num": { "$exists": false } }
  }'::documentdb_core.bson
);
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Index Scan using num_1 on documentdb_data.documents_15 collection (actual time=0.030..0.033 rows=2 loops=1)
   Output: document
   Index Cond: (collection.document @? '{ "num" : false }'::bson)
   Buffers: shared hit=3
 Planning:
   Buffers: shared hit=80
 Planning Time: 0.282 ms
 Execution Time: 0.070 ms
(8 rows)

This has read the two rows (rows=2) without the num field.

The complete picture

Here is the summary for { $exists: true } queries on the example above:

	Non-sparse index	Sparse index
MongoDB	FETCH ← IXSCAN, 8 keys, 8 docs, residual filter, 2 wasted fetches	FETCH ← IXSCAN, 6 keys, 6 docs, no residual filter
Amazon DocumentDB (AWS)	COLLSCAN, no index, 8 docs	IXSCAN, 6 keys
DocumentDB on PostgreSQL (Microsoft)	FETCH ← IXSCAN, 6 keys, 6 docs, no residual filter	FETCH ← IXSCAN, 6 keys, 6 docs, no residual filter
Oracle Database (ORDS)	TABLE ACCESS BY INDEX ROWID BATCHED ← HASH UNIQUE ← INDEX RANGE SCAN (MULTI VALUE), 6 keys, 6 docs	sparse index not supported

What this means in practice

🟠 The safe pattern for $exists: true queries in Amazon DocumentDB is to use a sparse index. Without it, you get a collection scan regardless of selectivity. With it, you get a direct index scan without any fetch overhead.

🟢 In MongoDB, the benefit is real but more modest: a sparse index saves the wasted fetch-and-discard work for missing-field documents and removes the residual predicate. The difference scales with how many documents are missing the field entirely.

🔴 In Oracle Database where traditionally indexes do not store null keys, indexes act like they are sparse.

🔵 In DocumentDB on PostgreSQL, the execution is optimal with a standard non-sparse RUM index. In case of doubt, verify plans with explain() from the MongoDB API (port 10260) and EXPLAIN from the PostgreSQL API (port 9712).

The Autovacuum Scale Factor Problem at Scale - Know Your Defaults

Franck Pachot — Tue, 26 May 2026 10:45:53 +0000

In PostgreSQL, autovacuum and autoanalyze exist to clean up dead tuples (old versions of updated/deleted rows) and update query planner statistics, respectively. The challenge is running them frequently enough so that query plans and execution do not degrade after data modifications, but not so frequently as to cause excessive I/O overhead.

Databases often maintain a counter of the number of modifications to trigger these background jobs. Oracle Database and MySQL use a stale percentage (the ratio of modifications to total rows) for statistics gathering. SQL Server uses a dynamically decreasing percentage to ensure statistics do not remain stale for too long on massive tables. PostgreSQL uses a hybrid approach: a fixed base threshold combined with a scale factor (a percentage) that grows proportionally with the table size.

This hybrid approach hits the sweet spot for most workloads, but it often requires tuning based on your specific data. The key factor to watch is the amount of static, "cold" data in your tables. Because the scale factor is calculated against the total table size, a large volume of cold data will significantly inflate the threshold. This can delay maintenance on the active working set—the "hot" data actually used by your queries—leaving it vulnerable to stale statistics or bloat.

Here are the default base thresholds:

postgres=# \dconfig *autovacuum*threshold
        List of configuration parameters
             Parameter              |   Value
------------------------------------+-----------
 autovacuum_analyze_threshold       | 50
 autovacuum_vacuum_insert_threshold | 1000
 autovacuum_vacuum_max_threshold    | 100000000
 autovacuum_vacuum_threshold        | 50
(4 rows)

At first glance, this suggests tables are analyzed when 50 rows are modified, and vacuumed when 50 dead tuples accumulate (from deletes or updates) or 1,000 rows are inserted. But this is only true without the scale factor—10% for statistics, 20% for vacuum:

postgres=# \dconfig *autovacuum*scale_factor
       List of configuration parameters
               Parameter               | Value
---------------------------------------+-------
 autovacuum_analyze_scale_factor       | 0.1
 autovacuum_vacuum_insert_scale_factor | 0.2
 autovacuum_vacuum_scale_factor        | 0.2
(3 rows)

Because of the scale factor, the actual trigger thresholds increase with the size of the table. For the default settings, the formulas are:

Analyze when inserts or modifications > 10% table row count plus 50 rows
Vacuum when dead tuples (from DELETE or UPDATE) > 20% table row count plus 50 rows
Vacuum when inserts > 20% table row count plus 1000 rows

As these formulas show, a larger table requires a much larger accumulation of changes before maintenance fires. This is perfectly acceptable if data churn is uniformly distributed, as small changes across a massive dataset will not drastically impact query cost estimations.

However, data distribution is rarely uniform and evolves over time (e.g., seasonal sales spikes, market expanding to new countries). Because static data inflates the table row count in the formulas above, your database waits too long to trigger maintenance on the active working set.

This is the core problem with default autovacuum settings at scale: a table with 5 million rows can accumulate half a million stale modifications before the planner statistics are refreshed, and over a million dead tuples before bloat is cleaned up. The larger the table grows, the longer it waits, and the worse the situation becomes:

Query planner statistics become increasingly stale between analyzes.
The visibility map is stale for longer and index-only scans become less efficient.
Dead tuple bloat accumulates more between cleanups, wasting storage and slowing scans.

To demonstrate this, I have run the following script to simulate this kind of activity, constantly inserting 100 rows and then updating them. We delete nothing because we want to keep the history, but queries operate on those recent rows. Think of it like orders being entered, then processed, and remaining stored:

\c
\o tmp.log

-- run autovacuum frequently for the demo
alter system set autovacuum_naptime = '1s';
select pg_reload_conf();

-- create a table
drop table demo;
create table demo (
 id bigserial primary key, n int default 0
);
vacuum analyze demo
;

-- show the vacuum and analyze statistics,
-- insert 100 rows and update them
-- run that in a loop every 5 seconds
select relname, n_tup_ins, n_tup_upd , n_mod_since_analyze, n_ins_since_vacuum
 , autovacuum_count,  last_autovacuum  --, vacuum_count,  last_vacuum
 , autoanalyze_count, last_autoanalyze --, analyze_count, last_analyze
from pg_stat_user_tables where relid='demo'::regclass
\;
insert into demo select from generate_series(1,100)
\;
update demo set n=n+1 where id in (
select id from demo order by id desc limit 100
)
\watch i=5 c=100000

For each iteration, the total number of rows inserted (n_tup_ins) and updated (n_tup_upd), as visible in pg_stat_user_tables, increases by 100. It is the X-axis on this diagram (n_tup):

The Y-axis shows the staleness of statistics (n_mod_since_analyze) and the accumulation of dead tuples (n_mod_since_vacuum) until the auto vacuum/analyze kicks in.

With 5 million rows, the last million inserted rows accumulated dead tuples. That is 20% of the total table, as defined by the default vacuum scale factor, but it likely represents 100% of the data actively read by your queries (for example, if the application processes the last year or less of a 5-year history). Furthermore, the last 500,000 rows have completely stale statistics, the 10% default analyze scale factor, and the past months may not have the same data distribution as the previous years.

Think about the impact this has on the maximum value for an ID sequence or a created_at timestamp. It also completely skews the query planner's understanding of your data distribution (such as querying by country or day of the week). I have seen this cause severe performance issues in the real world: a retail company where shops only open on Sundays during the summer, or a trading platform suddenly processing entirely new market trends. Because the statistics are stale, the planner assumes your new, active data looks exactly like your old, historical data.

As the table grows, the impact of this bloat and staleness compounds, and performance will no longer scale. Eventually, your execution plans will flip—not because the queries changed, but simply because the estimations of the query planner are completely wrong.

For very large tables where the total size increases but the active working set is a small, predictable number of rows, you can effectively disable the scale factor and rely almost entirely on the fixed threshold:

ALTER TABLE demo SET (  
    autovacuum_analyze_scale_factor = 0.001,  
    autovacuum_analyze_threshold    = 10000,  
    autovacuum_vacuum_scale_factor  = 0.001,  
    autovacuum_vacuum_threshold     = 10000  
);

This sets a nearly flat threshold that does not grow with the table size. The right threshold value depends on how many rows your active working set changes per hour and how much staleness you can tolerate. However, you must monitor the consequences of running autovacuum frequently on a growing table to ensure it does not cause localized I/O spikes.

Here is how the same run starts with the new table settings:

Auto analyze never left more than ~10,000 modified rows without refreshing statistics. This threshold grows slightly with the table (at 10 million rows it doubles to 20,000), but remains vastly better than the default. Auto vacuum follows the same pattern for dead tuples, but runs more frequently here because the insert-specific vacuum trigger was left at its default (1,000 rows + 20% scale factor), which only triggers the analyze threshold beyond 45,000 rows.

To address this unbounded growth natively, recent PostgreSQL versions introduced autovacuum_vacuum_max_threshold (with a default of 100 million—which is too high for my example). Rather than letting the scale factor dictate an endlessly growing target, this parameter imposes a hard ceiling on the vacuum threshold calculation. This means that even on a massive 1-billion-row table, autovacuum will forcibly trigger once dead tuples reach the configured maximum, serving as a built-in safety net. You can even adjust this globally via a simple config reload, or set it as a per-table storage parameter to enforce a strict upper limit on staleness without micromanaging scale factors across your entire schema.

Naturally, enforcing stricter thresholds—whether through these new maximum caps or manual table-level tuning—means autovacuum will run more frequently, which demands more background worker capacity. Historically, increasing autovacuum_max_workers to handle this extra load required a full database restart. However, PostgreSQL now thoughtfully splits this architecture: autovacuum_worker_slots reserves the hard upper bound of backend slots at postmaster startup, while autovacuum_max_workers dictates how many of those slots are actively used. This allows you to dynamically scale up your active workers on the fly (ALTER SYSTEM SET autovacuum_max_workers = 8; SELECT pg_reload_conf();) to absorb heavy maintenance workloads without incurring any downtime.

Alternatively, if your table has a clear time-based or categorical boundary between hot and cold data, partitioning is worth considering. Autovacuum operates per partition, so a current_year partition with 100,000 rows will trigger maintenance far sooner than a monolithic 5-million-row table, meaning the default scale factor will naturally behave exactly as intended.

Avoiding range overlaps in PostgreSQL with EXCLUDE constraint, better than serializable or assertions

Franck Pachot — Fri, 15 May 2026 18:21:25 +0000

Two groups of friends try to book seats ranges 5–8 and 7–9 in the same row, for the same show, at the same time. One of them must fail and retry. An ACID database can help enforce this rule. The question is: how much collateral damage does your concurrency control cause for everyone else?

In modern development, we tend to put the business logic in the application first. Still, enforcing business invariants in the database with integrity constraints may provide better performance when it avoids the need for the serializable isolation level required when the invariant concerns multiple rows. This is the case for foreign keys and unique constraints, and here is another example.

In a previous post of this series, we saw how Oracle SQL Assertions can work around the limitations of Oracle's Snapshot Isolation (improperly called SERIALIZABLE). We explored the rule: "each shift must always have at least one doctor on call", which is an example of a table-level invariant — a constraint that requires at least one row satisfying a given condition to exist at all times. This goes beyond what traditional row-pair constraints can express.

Most databases provide only the unique constraint, which enforces that no two rows share the same value(s) for a set of columns. PostgreSQL extends this with exclusion constraints, which generalize the concept: rather than just preventing duplicate values, they prevent any two rows from satisfying a specified pairwise condition — for example, preventing two time ranges from overlapping.

Here is an example of such a business rule: we manage a movie theater where customers can book consecutive seats in a row for themselves and their friends.

Oracle Database: assertion

The booking table identifies the seats for a session (SHOWING_ID) by the row (ROW_NUMBER) and the seat number range (SEAT_START, SEAT_END):


Name           Null?       Type
______________ ___________ ______________________________
BOOKING_ID     NOT NULL    RAW(16 BYTE)
SHOWING_ID     NOT NULL    RAW(16 BYTE)
ROW_NUMBER     NOT NULL    NUMBER(38)
SEAT_START     NOT NULL    NUMBER(38)
SEAT_END       NOT NULL    NUMBER(38)
CUSTOMER_ID    NOT NULL    RAW(16 BYTE)
CREATED_AT                 TIMESTAMP(6) WITH TIME ZONE

Typically, customers query look at the free seats in their preferred row, and book an available range that is free and can fit the number of seats they need. A serializable transaction would be needed to be sure that what is read is not booked by another in the meantime, but Oracle's SERIALIZABLE isolation level does not provide true serializability — it implements Snapshot Isolation, which permits write skew anomalies and doesn't prevent a new booking from being committed that overlaps what was read. With SQL assertions, you can take another approach: create an integrity constraint that raises an error in case of overlap:


CREATE ASSERTION no_overlapping_seats
CHECK (
    NOT EXISTS (
        SELECT 1 
        FROM bookings b1
        WHERE EXISTS (
            SELECT 1 
            FROM bookings b2
            WHERE b1.showing_id = b2.showing_id
              AND b1.row_number = b2.row_number
              AND b1.booking_id <> b2.booking_id
              -- Intersection logic:
              AND b1.seat_start < b2.seat_end
              AND b2.seat_start < b1.seat_end
        )
    )
);

The rule says that there cannot exist two bookings within the same show for the same row with overlapping start/end seat ranges.

The assertion uses the existential negation form (NOT EXISTS). The universal quantifier version, which may be easier to understand, says that for any two different bookings on the same show and row, the seat ranges must not overlap (one ends before the other starts):

∀ b1, b2 ∈ Bookings : (
    b1.show = b2.show ∧ b1.row = b2.row ∧ b1.id ≠ b2.id
) ⇒ (
    b1.start ≥ b2.end ∨ b2.start ≥ b1.end
)

This is equivalent, by De Morgan's law (x ∨ y ≡ ¬(¬x ∧ ¬y)), to saying it cannot be the case that both ranges extend into each other:

∀ b1, b2 ∈ Bookings : (
    b1.show = b2.show ∧ b1.row = b2.row ∧ b1.id ≠ b2.id
) ⇒ ¬ (
    b1.start < b2.end ∧ b2.start < b1.end
)

Now, applying quantifier duality (∀x : P ⇒ ¬Q ≡ ¬∃x : P ∧ Q), it is easy to understand the existential negation version that maps directly to the NOT EXISTS assertion:

¬∃ b1, b2 ∈ Bookings : (
    b1.show = b2.show ∧ b1.row = b2.row ∧ b1.id ≠ b2.id
  ∧
    b1.start < b2.end ∧ b2.start < b1.end
)

With this, any concurrent inserts for the same show and same row will wait, and after the wait, the seat ranges will be checked and ORA-08601: SQL assertion (NO_OVERLAPPING_SEATS) violated will be raised if they overlap. This works but is not ideal because it will wait even if the seats do not overlap. However, two non-overlapping bookings in the same show and row will still block before being accepted becaise assertions lock on exact column values (here, the combination of showing_id and row_number), not on ranges within those values.

It is possible to get better concurrency for that rule with PostgreSQL exclusion constraints, but first, let's check with serializable as PostgreSQL supports true Serializable Snapshot Isolation (SSI).

PostgreSQL: serializable

To get a fully reproducible example, I create all necessary tables for the theater's screens, movies, showings, and bookings:


CREATE TABLE screens (
    screen_id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    rows INT NOT NULL, 
    seats_per_row INT NOT NULL 
);

INSERT INTO screens (screen_id, name, rows, seats_per_row) VALUES
('a1b2c3d4-0001-4000-8000-000000000001', 'Alpha Station', 25, 36),
('a1b2c3d4-0001-4000-8000-000000000002', 'Big Market', 18, 28),
('a1b2c3d4-0001-4000-8000-000000000003', 'Mül Paradise', 12, 20),
('a1b2c3d4-0001-4000-8000-000000000004', 'Kirian Corridor', 15, 24);

CREATE TABLE movies (
    movie_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    year INT NOT NULL,
    duration_minutes INT NOT NULL,
    director TEXT NOT NULL DEFAULT 'Luc Besson'
);

INSERT INTO movies (movie_id, title, year, duration_minutes) VALUES
('b0b0b0b0-0001-4000-8000-000000000001', 'Subway', 1985, 104),
('b0b0b0b0-0001-4000-8000-000000000002', 'The Big Blue', 1988, 168),
('b0b0b0b0-0001-4000-8000-000000000003', 'Nikita', 1990, 117),
('b0b0b0b0-0001-4000-8000-000000000004', 'Léon: The Professional', 1994, 110),
('b0b0b0b0-0001-4000-8000-000000000005', 'The Fifth Element', 1997, 126),
('b0b0b0b0-0001-4000-8000-000000000006', 'Angel-A', 2005, 90),
('b0b0b0b0-0001-4000-8000-000000000007', 'Lucy', 2014, 89),
('b0b0b0b0-0001-4000-8000-000000000008', 'Valerian and the City of a Thousand Planets', 2017, 137),
('b0b0b0b0-0001-4000-8000-000000000009', 'Anna', 2019, 119),
('b0b0b0b0-0001-4000-8000-000000000010', 'Dogman', 2023, 114);

CREATE TABLE showings (
    showing_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    movie_id UUID NOT NULL,
    screen_id UUID NOT NULL,
    start_time TIMESTAMPTZ NOT NULL
);

INSERT INTO showings (showing_id, movie_id, screen_id, start_time) VALUES
-- Friday
('c0c0c0c0-0001-4000-8000-000000000001', 'b0b0b0b0-0001-4000-8000-000000000005', 'a1b2c3d4-0001-4000-8000-000000000001', '2025-01-24 19:00:00+01'),
('c0c0c0c0-0001-4000-8000-000000000002', 'b0b0b0b0-0001-4000-8000-000000000004', 'a1b2c3d4-0001-4000-8000-000000000003', '2025-01-24 20:00:00+01'),
('c0c0c0c0-0001-4000-8000-000000000003', 'b0b0b0b0-0001-4000-8000-000000000008', 'a1b2c3d4-0001-4000-8000-000000000002', '2025-01-24 21:00:00+01'),
('c0c0c0c0-0001-4000-8000-000000000004', 'b0b0b0b0-0001-4000-8000-000000000007', 'a1b2c3d4-0001-4000-8000-000000000004', '2025-01-24 22:00:00+01')
;

CREATE TABLE bookings (
    booking_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    showing_id UUID NOT NULL REFERENCES showings(showing_id),
    row_number INT NOT NULL,
    seat_range INT4RANGE NOT NULL,  -- e.g., [5,9) means seats 5,6,7,8
    customer_id UUID NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO bookings (booking_id, showing_id, row_number, seat_range, customer_id) VALUES
('d0d0d0d0-0001-4000-8000-000000000001', 'c0c0c0c0-0001-4000-8000-000000000001', 12, '[15,19)'::int4range, 'e0e0e0e0-0001-4000-8000-000000000001'),
('d0d0d0d0-0001-4000-8000-000000000002', 'c0c0c0c0-0001-4000-8000-000000000001', 12, '[19,21)'::int4range, 'e0e0e0e0-0001-4000-8000-000000000002'),
('d0d0d0d0-0001-4000-8000-000000000003', 'c0c0c0c0-0001-4000-8000-000000000001', 13, '[10,16)'::int4range, 'e0e0e0e0-0001-4000-8000-000000000003'),
('d0d0d0d0-0001-4000-8000-000000000004', 'c0c0c0c0-0001-4000-8000-000000000001', 14, '[1,5)'::int4range, 'e0e0e0e0-0001-4000-8000-000000000004'),
('d0d0d0d0-0001-4000-8000-000000000005', 'c0c0c0c0-0001-4000-8000-000000000001', 14, '[30,36)'::int4range, 'e0e0e0e0-0001-4000-8000-000000000005');

INSERT INTO bookings (
 showing_id,
 row_number,
 seat_range,
 customer_id
) VALUES (
 'c0c0c0c0-0001-4000-8000-000000000002',
  12,
  '[ 1, 3)'::int4range,
  gen_random_uuid()
)
;

PostgreSQL has a built-in range datatype. A range like [5,8) represents a half-open interval — it includes 5, 6, and 7 but not 8. The [ means inclusive, ) means exclusive. This is the standard representation for discrete ranges in PostgreSQL, and it means I can store the seat range in a single column instead of two columns like in Oracle Database. PostgreSQL also provides operators on ranges, notably && (overlaps) which returns true when two ranges share any points in common.

Typically a service that checks the available seats for a show and books a range of seats will use a serializable transaction:


BEGIN ISOLATION LEVEL SERIALIZABLE;

-- query the show's booked seats
SELECT row_number,seat_range,now() 
 FROM bookings 
 WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002';

-- find a range available and book it 
INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)  
 VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[ 5, 8)'::int4range, gen_random_uuid())
 RETURNING customer_id, booking_id, created_at;

-- wait time added to hold the transaction open long enough for others 
-- to start and attempt to commit concurrently
select pg_sleep(10); 

-- commit the transaction or fail if not serializable
COMMIT;

For this show and row, only seats in range [ 1, 3) are currently booked. I run my transactions in parallel for ranges [ 5, 8), [25,28), [ 7,10) so I expect only one of [ 5, 8) and [ 7,10) to succeed, in addition to [ 5, 8) which do not overlap with anyone:



\! psql -ec '\timing on' -c "begin isolation level serializable; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)  VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[ 5, 8)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;"   & sleep 1

\! psql -ec '\timing on' -c "begin isolation level serializable; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)  VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[25,28)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;"   & sleep 1

\! psql -ec '\timing on' -c "begin isolation level serializable; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)  VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[ 7,10)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;"   & sleep 1

Note: the \! commands launch background shell processes with &, staggered by 1-second sleeps, to simulate concurrent users hitting the database at nearly the same time.

As it was first, [5,8) succeeded:



postgres=# SELECT row_number,seat_range,created_at
 FROM bookings
 WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002' 
 ORDER BY created_at
;
 row_number | seat_range |          created_at
------------+------------+-------------------------------
         12 | [1,3)      | 2026-05-14 10:12:58.322202+00
         12 | [5,8)      | 2026-05-14 10:15:26.063663+00
(2 rows)

Both [25,28) and [ 7,10) have waited for the first one to commit and have failed, not because of overlap, as both viewed the seats free from their query (the first transaction wasn't committed yet), but because of serialization:

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.
Time: 10004.080 ms (00:10.004)

Serializable Snapshot Isolation (SSI) uses predicate locks to track what each transaction has read. Here, all three transactions read the same set of rows for the showing — SSI records a predicate lock on the showing_id filter condition. When the first transaction commits its insert, the other transactions have a read-write dependency: they read a state that no longer exists (the showing now has a new booking). SSI detects this (a "pivot" in the dependency graph) and aborts the transactions that cannot be guaranteed to produce a serializable outcome. The granularity of SSI predicate locks means the entire showing's bookings are effectively locked — not just the specific seat range. This is why [25,28), which doesn't overlap with anything, still fails.

For better concurrency, we need to lower the isolation level and enforce the overlap prevention as an in-database integrity constraint. In short, we enforce the logic using the C instead of I from ACID properties.

PostgreSQL: EXCLUDE constraint

PostgreSQL's exclusion constraints generalize unique constraints: instead of checking only equality between rows, they can check any combination of operators. The constraint is backed by an index (typically GiST) that efficiently finds conflicting rows at insert or update time. The key insight is that the constraint acts as a materialized conflict check — it evaluates against the current committed state of the table at the moment of the insert, independent of what the transaction previously read. This is why we can safely drop down to Read Committed isolation.

Exclusion constraints require a GiST index on the range for the overlap operator &&. Since I also have equality operators = on non-range columns ("showing_id", "row_number"), I need to enable the btree_gist extension — it's shipped with PostgreSQL and adds GiST operator classes for scalar types like UUID and integer, allowing them to be mixed with range types in a single GiST index. Then I can declare the exclusion constraint that will raise an error on seat range overlap for the same show and row:

-- Need the btree_gist extension for mixing = and && operators
CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookings ADD
    CONSTRAINT no_overlapping_seats
        EXCLUDE USING GIST (
            showing_id WITH =,
            row_number WITH =,
            seat_range WITH &&      -- && = "overlaps" operator
        )
;

The EXCLUDE USING GIST declaration reads: "no two rows may exist where showing_id equals, row_number equals, AND seat_range overlaps." The GiST index makes this check efficient — PostgreSQL doesn't scan the whole table, it uses the index to find only potentially conflicting rows.

If you forget to create the btree_gist extension, you'll encounter the following error:

ERROR:  data type uuid has no default operator class for access method "gist"  
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

The reason is that GiST indexes natively support only geometric and range types (with operators like &&, @>, <<). Scalar types like uuid, integer, or text have no built-in GiST operator class — they normally live in B-tree indexes. The btree_gist extension adds GiST operator classes for these scalar types, enabling them to participate in exclusion constraints alongside range operators. The error is misleading because it doesn't say "you need btree_gist" but says "this is not supported by GiST alone".

Here are some inserts to test it:


-- This should FAIL (overlaps with seats [15,19) in row 12, showing ...0001)
INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)
VALUES ('c0c0c0c0-0001-4000-8000-000000000001', 12, '[17,22)'::int4range, gen_random_uuid());

-- This should SUCCEED (adjacent, seats [21,25) in row 12)
INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)
VALUES ('c0c0c0c0-0001-4000-8000-000000000001', 12, '[21,25)'::int4range, gen_random_uuid());

-- This should SUCCEED (same row 12 but different showing)
INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)
VALUES ('c0c0c0c0-0001-4000-8000-000000000004', 12, '[15,19)'::int4range, gen_random_uuid());

You can validate that overlapping ranges of seats are not permitted for the same row in the same show. However, this doesn't show the concurrency advantages of it like a parallel testing.

I delete the booking for [5,8) that succeeded before, so that I can run my parallel test again:


DELETE FROM bookings 
 WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002' 
 AND row_number=12 AND seat_range='[ 5, 8)'::int4range
;

Now, I can run my transactions in Read Committed isolation level because the constraint prevents the overlap, regardless of what was read initially. The exclusion constraint checks for conflicts against committed data at insert time — even if the transaction's earlier SELECT didn't see the conflicting row:



\! psql -ec '\timing on' -c "begin isolation level read committed; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)  VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[ 5, 8)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;"   & sleep 1

\! psql -ec '\timing on' -c "begin isolation level read committed; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)  VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[25,28)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;"   & sleep 1

\! psql -ec '\timing on' -c "begin isolation level read committed; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)  VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[ 7,10)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;"   & sleep 1

Two bookings have succeeded, [5,8) and [25,28), and from the creation time you can see that they didn't have to wait on each other:


postgres=# SELECT row_number,seat_range,created_at
 FROM bookings
 WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002' 
 ORDER BY created_at
;
 row_number | seat_range |          created_at
------------+------------+-------------------------------
         12 | [1,3)      | 2026-05-14 10:12:58.322202+00
         12 | [5,8)      | 2026-05-14 10:33:25.935444+00
         12 | [25,28)    | 2026-05-14 10:33:26.936793+00
(3 rows)

Only the conflicting range [7,10) waited on the first transaction to commit and then detected the overlap:


ERROR:  conflicting key value violates exclusion constraint "no_overlapping_seats"
DETAIL:  Key (showing_id, row_number, seat_range)=(c0c0c0c0-0001-4000-8000-000000000002, 12, [7,10)) conflicts with existing key (showing_id, row_number, seat_range)=(c0c0c0c0-000
1-4000-8000-000000000002, 12, [5,8)).
Time: 8009.347 ms (00:08.009)

Note that the wait time (~8 seconds instead of ~10) corresponds to the 1-second stagger between the sessions: the third transaction started 2 seconds after the first, so it waited the remaining duration until the first committed. During this wait, PostgreSQL holds a lightweight lock on the potentially conflicting index entry — only transactions inserting an actually overlapping range will block.

Indexes are often used to avoid broader table locks. For example, unique constraints in most databases rely on an index to detect conflicts, and in Oracle, creating an index on a foreign key helps avoid locks for referential integrity checks. PostgreSQL does not need that because it can use share row locks, whereas Oracle implements only exclusive row locks.

The application must interpret this error (SQLSTATE 23P01 — exclusion_violation) as: "those seats are not available" and it should start again the transaction with a fresher seat map. This is similar to how applications handle serialization failures (SQLSTATE 40001) with SSI, but with an important difference: with the exclusion constraint, only actual overlaps trigger the error, so retries are far less frequent.

Summary

Approach	Blocking Behavior	Concurrency	Constraint Expressiveness
Oracle 26ai Assertion	Blocks all inserts for same show+row, even non-overlapping	Medium (wait even non-conflicting seats)	Very high (arbitrary SQL predicates)
PostgreSQL Serializable (SSI)	Aborts transactions that read the same predicate range, even non-overlapping inserts	Low (requires retries)	N/A (application logic)
PostgreSQL EXCLUDE constraint	Blocks only actually overlapping ranges	High (fails only on conflict)	Pairwise operators supported by an index

The exclusion constraint with GiST index provides the best concurrency: non-conflicting inserts proceed in parallel without waiting or aborting, while true overlaps are precisely detected and rejected. Serializable isolation is more conservative — it must protect against any possible anomaly from the read set, not just overlaps. Oracle assertions are the most expressive (they can enforce arbitrary multi-row predicates) but operate at the granularity of exact value locks, which causes unnecessary blocking when the actual conflict depends on range overlap.

The tradeoff is clear: if your invariant can be expressed as a pairwise exclusion condition with indexable operators, PostgreSQL's EXCLUDE constraint gives you the best of both worlds — declarative integrity with fine-grained concurrency.

Do you need foreign keys and surrogate keys because you broke your relationships ?

Franck Pachot — Sat, 09 May 2026 20:47:56 +0000

Many developers believe they need a relational database because their data has relationships. The reasoning usually goes: "My entities are related, therefore I need foreign keys, therefore I need an RDBMS."

But the causality is actually reversed. Normalization creates the need for foreign keys—not the other way around.

This misunderstanding persists when the builders are agents, because LLMs operate on high-dimensional similarity (vectors), which may catch correlation better than causality.

Normalization and relations

When you normalize your domain model into Normal Forms, you decompose aggregates—cohesive groups of entities that form a natural consistency boundary—into separate, independent tables. Each table represents a single relation in the mathematical sense: a set of tuples (rows) constrained by functional dependencies on a key. That is what "relational" means in "relational model"—it refers to these mathematical relations (tables), not to the relationships between entities.

Actually, this normalization is a decomposition that removes the structural associations between entities. Co-location, direct references, and pointers that once made related data physically and logically cohesive are eliminated. The connections between entities exist only through shared attribute values, and are reconstructed at query time via joins on those columns. Normalization eliminates those relationships from the data structure, keeping only direct, functional dependency of an attribute on its key.

In other words, relational databases are not meant to strengthen relationships—they dissolve them from the schema and defer their reconstruction to the query, in order to have an application-agnostic representation of data.

An example: the Order aggregate

Consider a typical e-commerce domain. An Order is a natural aggregate: it contains line items, a shipping address, and payment details. In your domain model, these are not independent things—a line item has no meaning outside its order, a shipping address belongs to that specific purchase, and the payment is authorized for that exact total.

In a document database, this aggregate stays whole:

{
  "_id": ObjectId("order_123"),
  "customer": "Alice",
  "status": "confirmed",
  "shippingAddress": {
    "street": "123 Main St",
    "city": "Springfield",
    "zip": "62704"
  },
  "items": [
    { "sku": "WIDGET-A", "name": "Blue Widget", "qty": 2, "price": 9.99 },
    { "sku": "GADGET-B", "name": "Red Gadget", "qty": 1, "price": 24.99 }
  ],
  "payment": {
    "method": "credit_card",
    "last4": "4242",
    "authorized": 44.97
  }
}

The consistency boundary is the document. A single write replaces or updates the entire order atomically. No line item can be orphaned. No shipping address can drift out of sync. The structure is the integrity.

Now normalize this into Third Normal Form:

orders (order_id PK, customer, status)
order_items (item_id PK, order_id FK, sku, name, qty, price)
shipping_addresses (address_id PK, order_id FK, street, city, zip)
payments (payment_id PK, order_id FK, method, last4, authorized)

Notice what happened:

The aggregate was shattered into four independent tables with no structural cohesion.
Foreign keys had to be added (order_id FK) to re-express the parent-child relationship that was previously implicit through embedding.
Surrogate keys had to be invented (address_id) because value objects don't have a natural identity—they were identified by their values.
Joins are now required to reconstitute what was a single read—SELECT ... FROM orders JOIN order_items JOIN shipping_addresses JOIN payments.
Referential integrity constraints must be declared to prevent orphan rows—a problem that could not exist when the data was embedded.
Transaction scope must be explicitly managed because inserting an order now requires manipulating data in four tables. You need BEGIN/COMMIT to ensure atomicity that was previously guaranteed by the single-document write.

Surrogate keys: identity invented for decomposition

This point deserves emphasis. In Domain-Driven Design, a Value Object has no identity—it is defined entirely by its attributes. A shipping address is a value object. A line item, depending on your domain, may be a dependent entity whose identity is meaningful only within its parent aggregate.

But a relational table requires every row to be uniquely identifiable by a primary key. When you extract a value object or dependent entity into its own table, you must assign it an artificial surrogate key—an auto-incremented integer or UUID that has no meaning in your domain. This surrogate exists solely because normalization required the value object to live independently.

The surrogate key is not modeling your domain. It is compensating for a storage decision.

This normalization has consequences for consistency and concurrency

In most RDBMS implementations, the physical model mirrors the logical one: the default unit of atomicity and locking is the row, not the original multi-entity aggregate from your domain model. Since normalization has scattered what was once a single aggregate across multiple tables, the database must provide additional mechanisms to restore consistency across those rows:

Referential integrity constraints (foreign keys) exist to reenforce cross-row consistency that was inherent before decomposition, when related entities were embedded under a common root (the aggregate root).
Isolation levels and explicit locks exist because the natural consistency boundary (the aggregate, in Domain-Driven Design terms) no longer maps to a single lockable unit. The database must escalate from row-level locks to approximate the transactional boundary of the original aggregate root. This is implicit with serializable isolation level (often implemented with range or predicate locks) or explicit in lower isolation levels (SELECT FOR UPDATE on the aggregate root).

What normalization costs you—and what it buys

To be fair, normalization is not without benefits. It eliminates data redundancy, which simplifies updates to shared reference data and reduces storage when the same entity participates in many aggregates (e.g., a product referenced by thousands of order lines). It provides a flexible query model in which any column can serve as a join path, enabling ad hoc analytics that weren't anticipated at design time. The normalized model is also the best choice when you don't know your use cases or data distribution, because it is an abstraction that doesn't depend on access patterns or cardinalities.

But these benefits come at a structural cost:

What normalization removes	What must be added to compensate
Embedding (co-location)	Joins at query time
Structural association	Foreign key constraints
Aggregate-level atomicity	Explicit transactions across tables
Identity through containment	Surrogate keys
Single-unit locking	Isolation levels and lock escalation

Many foreign keys in your schema are evidence of a relationship that was structurally present and had to be re-declared as a constraint after decomposition.

The relational model as a middle ground

This application-agnostic quality is not accidental—it is the relational model's primary design goal. And it positions the relational model as an intermediate representation between two extremes:

Document model (full business aggregates): Optimized for OLTP, where the unit of read and write aligns with the domain's consistency boundary. You read what you need in one operation, and you write what belongs together atomically. The structure serves the application.
Columnar model (individual attributes): Optimized for analytics and ad-hoc queries, where any combination of columns across millions of rows can be scanned, filtered, and aggregated without regard to entity boundaries. The structure serves the query engine.

The relational model sits between these two. It doesn't fully optimize for either transactional aggregates or analytical scans, but it provides a general-purpose representation that can serve both reasonably well when the database engines adds physical optimizations. Rows are decomposed enough to avoid redundancy and enable flexible joins, but not so decomposed that individual attributes lose their tuple context. This is why the relational model became the default: when you don't know your access patterns or future use cases, and need a single schema to serve multiple applications, normalization into relations is the safest bet.

But "safest bet" is not the same as "best fit." When you do know your domain, your access patterns, and your consistency boundaries, that middle-ground positioning becomes over-engineering for your transactional workload and under-optimization for your analytical one.

Beyond the aggregates

Everything above describes referential integrity within a domain aggregate—the consistency boundary where entities and value objects are tightly coupled under a single root. But there are relationships between aggregates too, and this is where your software architecture determines whether the database should enforce them:

Domain-Driven Design decouples aggregates so that each team can operate independently within a bounded context. You may reference another aggregate by its root identity, but enforcement is asynchronous—through application events between services—always routing consistency through the aggregate root. In this architecture, cross-aggregate foreign keys in the database would create the tight coupling that DDD explicitly avoids.
Monolithic data models keep all entities tightly coupled. Foreign keys can be defined across the entire schema, and any application module can update any entity. In this world, in-database integrity constraints are the sole guarantor of business invariants, because no architectural boundary prevents inconsistent writes.

The choice is not purely technical—it reflects whether you trust your architecture or your database to enforce boundaries between aggregates.

When the application evolves

This architectural distinction becomes critical when new use cases emerge—and they always do.

In a relational model, evolution means adding columns, adding tables, and adding joins. The normalized schema was over-engineered for the first use case, but that over-engineering pays off as flexibility: a new query path is just a new join condition. Schema changes to existing data require migration scripts—adding columns, backfilling values, updating constraints—but the work is well understood, and the tooling is mature. New use cases accumulate within the same schema without requiring organizational change: no new services to deploy, no event streams to operate, no eventual consistency to reason about. A single team can absorb new requirements by extending the model, and each evolution is a small, incremental step.

An ORM can significantly lower the initial barrier. Tools like Hibernate, ActiveRecord, or SQLAlchemy let developers define entities in application code and automatically generate a normalized schema—making prototyping and MVPs feel almost as frictionless as with a schema-flexible database. But the ORM is an abstraction over decomposition, not an elimination of it. The joins, the transactions, the N+1 query problems are still there—just hidden until load reveals them. An ORM that isn't tuned (lazy loading in loops, missing fetch strategies, implicit transaction boundaries) will generate SQL that defeats the relational model's strengths. The ease of starting must be followed by the discipline of tuning.

However, as the schema grows—more tables, more joins, more constraints, more cross-cutting migrations—the monolithic model becomes harder to reason about, harder to change safely, and harder to split later when organizational growth demands it. Every new use case widens the blast radius of a schema change.

In a document model, evolution follows the bounded context. When a new use case aligns with an existing aggregate, you add fields to the document—and because documents are schema-flexible, existing data doesn't require migration. Old documents simply lack the new field, which the application handles gracefully. But when a new use case crosses aggregate boundaries or introduces a different access pattern—say, analytics across all orders, or a recommendation engine that needs to correlate items across customers—you don't contort your existing domain model to accommodate it. Instead, you create a new bounded context with its own collection and schema optimized for its access pattern, and you feed it through event streaming (change streams, CDC) from the source aggregates.

This comes at a cost, too: you need the infrastructure for event streaming, you must reason about eventual consistency between contexts, and you need organizational maturity to define clean boundaries. For a small team building an MVP, spinning up event pipelines, or managing change stream consumers may be premature architecture.

This is a fundamental difference in how complexity grows:

The monolithic model absorbs complexity within a single schema. New use cases add joins and constraints. The schema becomes harder to reason about, harder to migrate, and harder to split later—but each evolution is a small step that doesn't require organizational change.
The domain model distributes complexity across bounded contexts. Each context remains simple—optimized for its specific use case—and the integration between them is through events. You need to recognize when a new use case doesn't fit the existing bounded context, and spawn a new collection with its own stream, rather than adding multi-document transactions or cross-collection lookups that would compromise the aggregate's integrity.

In practice, this means a document-oriented architecture scales with organizational complexity. Each team owns its bounded context, its schema, and its data. A new analytical use case doesn't require the order service to change—it subscribes to order events and materializes its own read-optimized view. The order aggregate stays clean, stays fast, and stays owned by a single team. Meanwhile, the relational model scales with functional complexity within a single team—allowing a small group serve many use cases from a single schema, provided they can manage its growing weight.

Dimension	Relational model	Document model
Getting started	More upfront schema work	Fast to prototype
Adding use cases	Extend the schema, add joins	Add fields or spawn new bounded contexts
Complexity growth	Accumulates within one schema	Distributes across contexts
Organizational impact	Larger team synchronization	Requires team boundaries
Risk at scale	Monolithic schema becomes rigid	Must continue in event-driven design

The frequency of releases reflects the difference. Monthly migration windows are often considered undesirable in relational databases. With schema-flexible documents, schema evolution happens at the pace of code deployment: weekly or even daily iterations, without migration scripts or downtime windows.

The takeaway

Relationships exist in your domain model regardless of your database choice. A document database preserves them—a document maps to an aggregate where entities and value objects are embedded, with transactions and locks scoped to that boundary. A relational database decomposes them through normalization and reconstructs them through joins, foreign keys, and transaction isolation.

The relational model earns its place as a general-purpose middle ground that scales with functional complexity—enabling a single team to serve many use cases from a single schema without organizational change. The document model scales with organizational complexity—each team owns its bounded context, its aggregate stays clean, and new use cases spawn new contexts connected by events rather than widening a monolithic schema.

The question is not "Does my data have relationships?" (it always does). The question is: "Do I want my database to preserve my aggregate boundaries or decompose them? And when my application evolves, do I want complexity to accumulate in my schema, or to distribute across my architecture?".

Codd's Connection Trap and Oracle's JOIN TO ONE

Franck Pachot — Sat, 02 May 2026 21:52:47 +0000

In a previous post, I explored Codd's connection trap in PostgreSQL and MongoDB — the classic pitfall where joining two independent many-to-many relationships through a shared attribute produces spurious combinations that look like facts but aren't.

The example followed Codd's 1970 suppliers–parts–projects model: we know which suppliers supply which parts, and which projects use which parts, but joining through parts to derive supplier–project relationships is a relational composition — it tells us what could be true, not what is true.

Oracle Database 26ai introduces JOIN TO ONE, a SQL extension that structurally prevents this class of errors. In this post, I'll reproduce Codd's connection trap in Oracle, show how JOIN TO ONE catches it, and demonstrate the correct solutions.

Why This Matters: A Gap in SQL joins

When developers build joins at the application level — fetching a parent row for a given foreign key in PL/SQL or application code — they naturally get safety checks: TOO_MANY_ROWS tells them a lookup that should have been unique returned multiple rows, and NO_DATA_FOUND tells them the expected parent doesn't exist. These exceptions act as guardrails, catching data or logic errors immediately.

But when the same logic moves into a SQL JOIN, those guardrails disappear. A join that silently matches multiple rows simply multiplies the result set — no error, no warning, just quietly wrong numbers. A join that finds no match either drops the row (inner join) or pads it with NULLs (outer join), but never raises an alarm about violated assumptions.

JOIN TO ONE bridges this gap. It brings the equivalent of TOO_MANY_ROWS protection into SQL joins: if a join that you declared as "to one" ever reaches a second row, Oracle raises a runtime error instead of silently corrupting your results. The default outer-join behavior handles the "zero matches" case gracefully (like a NO_DATA_FOUND that returns NULL columns instead of erroring), and you can override it to INNER JOIN TO ONE when the absence of a match should eliminate the row.

A note on naming: JOIN TO ONE is semantically JOIN TO ZERO OR ONE AND ONLY ONE (for the default outer case) or JOIN TO ONE AND ONLY ONE (for the inner case). SQL has never been shy about verbosity, so a more precise name might have been warranted.

Schema & Sample Data

Following Codd's example, and the previous blog post, we have suppliers, parts, projects, and two independent many-to-many relationships — now with quantities to make the consequences of the trap concrete:

CREATE TABLE suppliers (
    supplier_id VARCHAR2(10) PRIMARY KEY
);

CREATE TABLE parts (
    part_id VARCHAR2(10) PRIMARY KEY
);

CREATE TABLE projects (
    project_id VARCHAR2(10) PRIMARY KEY
);

-- Supplier supplies parts
CREATE TABLE supplier_part (
    supplier_id   VARCHAR2(10) REFERENCES suppliers,
    part_id       VARCHAR2(10) REFERENCES parts,
    qty_available INT NOT NULL,
    PRIMARY KEY (supplier_id, part_id)
);

-- Project uses parts
CREATE TABLE project_part (
    project_id VARCHAR2(10) REFERENCES projects,
    part_id    VARCHAR2(10) REFERENCES parts,
    qty_needed INT NOT NULL,
    PRIMARY KEY (project_id, part_id)
);

-- Reference data
INSERT INTO suppliers VALUES ('S1');
INSERT INTO suppliers VALUES ('S2');

INSERT INTO parts VALUES ('P1');
INSERT INTO parts VALUES ('P2');
INSERT INTO parts VALUES ('P3');

INSERT INTO projects VALUES ('Alpha');
INSERT INTO projects VALUES ('Beta'); 

-- S1 supplies P1 (100 units) and P2 (200 units)
-- S2 supplies P2 (150 units) and P3 (300 units)
INSERT INTO supplier_part VALUES ('S1', 'P1', 100);
INSERT INTO supplier_part VALUES ('S1', 'P2', 200);
INSERT INTO supplier_part VALUES ('S2', 'P2', 150);
INSERT INTO supplier_part VALUES ('S2', 'P3', 300);

-- Alpha uses P1 (50 units) and P2 (75 units)
INSERT INTO project_part VALUES ('Alpha', 'P1', 50);
INSERT INTO project_part VALUES ('Alpha', 'P2', 75);

-- Alpha uses P1 (50 units) and P2 (75 units)  
-- Beta  uses P2 (60 units) and P3 (90 units)  
INSERT INTO project_part VALUES ('Alpha', 'P1', 50);  
INSERT INTO project_part VALUES ('Alpha', 'P2', 75);  
INSERT INTO project_part VALUES ('Beta',  'P2', 60);  
INSERT INTO project_part VALUES ('Beta',  'P3', 90); 

COMMIT;

The Connection Trap in Action

A developer wants to know which suppliers are connected to which projects and executes the following query:

SELECT sp.supplier_id,
       pp.project_id,
       sp.part_id,
       sp.qty_available,
       pp.qty_needed
FROM   supplier_part sp
JOIN   project_part pp ON sp.part_id = pp.part_id
ORDER  BY sp.supplier_id, sp.part_id
;

SUPPLIER_ID    PROJECT_ID    PART_ID       QTY_AVAILABLE    QTY_NEEDED
______________ _____________ __________ ________________ _____________
S1             Alpha         P1                      100            50
S1             Alpha         P2                      200            75
S1             Beta          P2                      200            60
S2             Alpha         P2                      150            75
S2             Beta          P2                      150            60
S2             Beta          P3                      300            90

6 rows selected.

6 rows from only 4 supplier-part rows and 4 project-part rows. The query asserts, for example, "S2 supplies P2 to Alpha" — but our data only says S2 can supply P2 and Alpha needs P2. The join inferred relationships through the shared attribute part_id that were never recorded as facts.

As Codd warned in his 1970 paper, this is exactly the connection trap: deriving relationships that were never asserted.

The Damage with Aggregates

Now the developer summarizes:

SELECT sp.supplier_id,
       SUM(sp.qty_available) AS total_available,
       SUM(pp.qty_needed)    AS total_needed
FROM   supplier_part sp
JOIN   project_part pp ON sp.part_id = pp.part_id
GROUP  BY sp.supplier_id
ORDER  BY sp.supplier_id
;

SUPPLIER_ID       TOTAL_AVAILABLE    TOTAL_NEEDED
______________ __________________ _______________
S1                            500             185
S2                            600             225

Compare with the actual totals from each table independently:

SELECT supplier_id, SUM(qty_available) AS total_available  
FROM   supplier_part  
GROUP  BY supplier_id  
ORDER  BY supplier_id;  

SUPPLIER_ID       TOTAL_AVAILABLE
______________ __________________
S1                            300
S2                            450

SELECT project_id, SUM(qty_needed) AS total_needed  
FROM   project_part  
GROUP  BY project_id  
ORDER  BY project_id;  

PROJECT_ID       TOTAL_NEEDED
_____________ _______________
Alpha                     125
Beta                      150

The connection trap inflated both sides:

S1's availability jumped from 300 to 500: P2's 200 was counted twice (once for Alpha, once for Beta)
S2's availability jumped from 450 to 600: P2's 150 was counted twice (once for Alpha, once for Beta)
Needs were scrambled: the 225 attributed to S2 mixes Alpha's and Beta's needs, double-counting P2's demand

The trap corrupts aggregates in whichever direction the data happens to push — inflation, deflation, or both at once — and it does so silently. In application code, a lookup-by-key that returns two rows would raise TOO_MANY_ROWS. In a SQL join, the same situation just silently multiplies your totals.

This explains why, in a data warehouse, we denormalize into a dimensional model, or star schema, with a single fact table and dimension tables. Normalization makes the relational schema unsafe for users who see only the SQL schema, without the details of the domain model or the safeguards provided by the application.

The Join Graph: Why There Is No RWT

Oracle's JOIN TO ONE documentation introduces the concept of a Row-Widened Table (RWT) — a table from which all other tables can be reached through unique (many-to-one) joins, ensuring the query result maps one-to-one to the RWT rows. A query where such an RWT exists is a Row Widening Only Query (RWOQ), and it's almost always what you need for correct results.

Here's the join graph of our broken query:

   supplier_part ──→ parts ←── project_part
         (FK)        (PK)         (FK)

The "parts" table is a parent node reached from two sibling child nodes. This is the chasm trap:

Starting from "supplier_part": the path to "project_part" via "parts" goes many-to-one then one-to-many — not unique
Starting from "project_part': same problem in reverse
Starting from "parts": both children fan out

No table qualifies as a RWT. This is not a RWOQ. The output rows don't map one-to-one to any table's rows.

How JOIN TO ONE Catches the Trap

With Oracle 26ai's JOIN TO ONE, attempting to write this query produces an error:

-- THIS FAILS — and that's exactly what we want  
SELECT sp.supplier_id,  
       pp.project_id,  
       sp.qty_available,  
       pp.qty_needed  
FROM   supplier_part sp  
JOIN TO ONE (parts p, project_part pp); 

Error at Command Line : 6 Column : 23
Error report -
SQL Error: ORA-18641: No join key found for "PROJECT_PART"

JOIN TO ONE requires every table inside the parentheses to be reachable from the leading RWT through a chain of unique joins. The path supplier_part → parts is many-to-one (valid), but parts → project_part is one-to-many (invalid). Oracle detects that part_id alone is not unique in project_part (the PK is (project_id, part_id)) and blocks the query.

Even forcing an explicit ON clause doesn't help:

SELECT sp.supplier_id,  
       pp.project_id,  
       sp.qty_available,  
       pp.qty_needed 
FROM supplier_part sp
JOIN TO ONE (
    parts p,
    project_part pp ON p.part_id = pp.part_id
);

Error at Command Line : 8 Column : 5
Error report -
SQL Error: ORA-18640: JOIN TO ONE reached multiple rows joining to "PP", resulting in a non-unique join

https://docs.oracle.com/error-help/db/ora-18640/

Oracle either rejects at parse time or raises a runtime error the moment a part matches multiple project_part rows — the SQL equivalent of the TOO_MANY_ROWS exception that application developers rely on. Instead of silently producing wrong numbers for months or years, you get an immediate, clear signal: this query structure doesn't support the one-to-one mapping you're claiming.

The Correct Solutions: two Separate RWOQs

Since the schema doesn't record the three-way relationship, we must run two separate queries, and that's exactly what JOIN TO ONE forces us to do:

-- RWOQ 1: "What can each supplier supply?"  
-- RWT = supplier_part → unique joins to suppliers and parts  
SELECT sp.supplier_id,  
       sp.part_id,  
       sp.qty_available  
FROM   supplier_part sp  
JOIN TO ONE (suppliers s, parts p)  
ORDER  BY sp.supplier_id, sp.part_id;  

SUPPLIER_ID    PART_ID       QTY_AVAILABLE
______________ __________ ________________
S1             P1                      100
S1             P2                      200
S2             P2                      150
S2             P3                      300

-- RWOQ 2: "What does each project need?"  
-- RWT = project_part → unique joins to projects and parts  
SELECT pp.project_id,  
       pp.part_id,  
       pp.qty_needed  
FROM   project_part pp  
JOIN TO ONE (projects j, parts p)  
ORDER  BY pp.project_id, pp.part_id;  

PROJECT_ID    PART_ID       QTY_NEEDED
_____________ __________ _____________
Alpha         P1                    50
Alpha         P2                    75
Beta          P2                    60
Beta          P3                    90

Both are valid RWOQs. Clean star-shaped join graphs. No spurious combinations. Aggregates on qty_available or qty_needed are guaranteed correct.

Conclusion

Codd identified the connection trap in 1970: inferring relationships from shared attributes produces combinations that could be true, not combinations that are true. Over fifty years later, this trap remains one of the most common sources of silently wrong SQL — aggregates that are "slightly off," duplicates masked by DISTINCT, totals that nobody questions because they look plausible.

Application developers have long relied on TOO_MANY_ROWS and NO_DATA_FOUND exceptions to catch violated uniqueness assumptions in procedural lookups. But the moment those lookups become SQL joins, the safety net vanishes — a many-to-one assumption that silently becomes many-to-many just multiplies rows without complaint.

Oracle's JOIN TO ONE in Database 26ai brings that safety net back into SQL:

	Traditional JOIN	JOIN TO ONE
Connection trap	⚠️ Silently produces wrong results	⛔️ Blocked at parse/runtime
Row multiplication	⚠️ Cartesian per shared parent	⛔️ Prevented by RWOQ enforcement
Aggregates	⚠️ Inflated or deflated silently	✅ Guaranteed by one-to-one mapping
Equivalent of `TOO_MANY_ROWS`	❌ Not available in joins	🛑 Runtime error on violated uniqueness
Developer awareness	⚠️ Can go unnoticed for years	🛑 Immediate error

The rule is the same whether you use normalized relations, star schemas, or document models: if a relationship is a fact, it must be stored as one — not derived through joins. JOIN TO ONE ensures that when you do join, the result stays faithful to the facts your schema actually records, or the query fails.

If you think SQL databases, normalization, and referential integrity automatically protect data consistency better than denormalized models, this is proof that they do not. A document model can preserve business invariants by storing them consistently, whereas normalization can break them across multiple tables to be joined. The relational model is an abstraction that simplifies data relationships and can hide business invariants that exist in the domain model and the application. Applications must then compensate by writing safer queries, often at a performance cost by running multiple queries. The new JOIN TO ONE syntax helps SQL users find the right balance by declaring their intent: to look up additional columns from dimensions without changing the number of fact rows.

If you're used to MongoDB, this RWOQ (Row Widening Only Query) is simply a $lookup. It doesn't change the cardinality, but adds an array that can be:

an empty array (similar to NO_DATA_FOUND and LEFT OUTER JOIN)
a single-item array (as expected with JOIN TO ONE)
a multi-item array (equivalent to TOO_MANY_ROWS if unexpected) There's no risk of silent row multiplication, because when that expansion is expected, it must be made explicit with $unwind.

DEV Community: Franck Pachot

HorizonDB preview: automate a reproducible lab with ARM

Login to Azure

ARM template for HorizonDB preview

HorizonDB parameters

Resource group

Deploy the cluster

Retrieve the endpoint and connect

Cleanup

Final thoughts

RUM—Storing More in the Index

GIN: Understanding PostgreSQL's Inverted Index and Its Limitations

What's Next

Extended RUM in DocumentDB extension for PostgreSQL: Efficient ESR (Equality, Sort, Range) Queries

Comparing the Two RUM Index Definitions

Comparison of Execution Plans

Index Only Scan

Conclusion

Getting Started with pg_durable: Workflows Inside PostgreSQL

Installing pg_durable

Hello World

Beyond a Single Query

Conclusion

Filter on Children, Sort by Parent: One-to-Many Compound Index Strategies in PostgreSQL

Normalized One-to-Many

Denormalize into the "Many" Side (Children)

Update with Cascade Constraint (Recommended)

Update with Trigger (Not Recommended)

Denormalize into the "One" Side (Parent)

GIN Index (and btree_gin Extension)

Partial B-tree Index for known predicates

DocumentDB Multi-Key Index (PostgreSQL with extended RUM)

Conclusion

BSON and OSON: documents are designed to be nested, not flat

BSON serialization format optimization

What about Oracle's OSON format?

JSONB and BSON on PostgreSQL

Conclusion

$exists and non-sparse indexes in MongoDB and in other DocumentDB

An example: { $exists: true } filter

Null in the index key is ambiguous

MongoDB with a non-sparse index

MongoDB with a sparse index

Oracle Database

Amazon DocumentDB (AWS)

Non-sparse index on Amazon DocumentDB

Sparse index on Amazon DocumentDB

Microsoft DocumentDB on PostgreSQL

The complete picture

What this means in practice

The Autovacuum Scale Factor Problem at Scale - Know Your Defaults

Avoiding range overlaps in PostgreSQL with EXCLUDE constraint, better than serializable or assertions

Oracle Database: assertion

PostgreSQL: serializable

PostgreSQL: EXCLUDE constraint

Summary

Do you need foreign keys and surrogate keys because you broke your relationships ?

Normalization and relations

An example: the Order aggregate

Surrogate keys: identity invented for decomposition

This normalization has consequences for consistency and concurrency

What normalization costs you—and what it buys

The relational model as a middle ground

Beyond the aggregates

When the application evolves

The takeaway

Codd's Connection Trap and Oracle's JOIN TO ONE

Why This Matters: A Gap in SQL joins

Schema & Sample Data

The Connection Trap in Action

The Damage with Aggregates

The Join Graph: Why There Is No RWT

How JOIN TO ONE Catches the Trap

The Correct Solutions: two Separate RWOQs

Conclusion

GIN Index (and `btree_gin` Extension)

An example: `{ $exists: true }` filter