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.

$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.

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

$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

GIN Index (and `btree_gin` Extension)

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