DEV Community: Franck Pachot

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

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

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

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

PostgreSQL and RUM

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


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

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

Indexes:

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

The RUM index supports filtering and ordering by distance:


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

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

(5 rows)

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

(12 rows)

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

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

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


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

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

(5 rows)

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

(20 rows)

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

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

PostgreSQL and B-tree

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

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

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

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

(13 rows)

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

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

DocumentDB collection

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


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

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

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

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

Here is a similar query and its execution plan:


postgres=# SET documentdb_core.bsonUseEJson to true;

SET

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

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

(5 rows)

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

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

(9 rows)

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

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

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

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

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

(15 rows)

The plan shows:

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

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

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

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

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

Index Only Scan

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

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

postgres=# VACUUM;

VACUUM

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

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

(12 rows)

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

Extended RUM index

Here's an overview of the internal process:

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

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


postgres=# \d documentdb_data.documents_28

                  Table "documentdb_data.documents_28"

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

Indexes:

    "collection_pk_28" PRIMARY KEY, btree (shard_key_value, object_id)
    "documents_rum_index_69" documentdb_extended_rum (document documentdb_extended_rum_catalog.bson_extended_rum_composite_path_ops (pathspec='[ "words", "category", { "published" : -1 } ]', tl='2691'))
    "documents_rum_index_70" documentdb_extended_rum (document documentdb_extended_rum_catalog.bson_extended_rum_composite_path_ops (pathspec='[ "category", "published" ]', tl='2691'))

Check constraints:

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

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

MongoDB-compatible multi-key index performance

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

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

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

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

test> use db;

switched to db db

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

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

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

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

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

Indexing flexible documents

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

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

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

Testing other MongoDB emulations

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

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

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

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

Conclusion

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

kernel asynchronous reads in PostgreSQL 19 (io_uring)

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

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

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

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

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


postgres=# \dconfig io_*

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

(8 rows)

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

Conclusion

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

The difference is in how waiting is organized.

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

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

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

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

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

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

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

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

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

IO combining and prefetch

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

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

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

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

PostgreSQL 19 (beta)

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

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

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

AIO configuration

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


postgres=# \dconfig io_*

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

(8 rows)

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

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

Seq Scan on small rows table (inline)

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

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

CREATE TABLE

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

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

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

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

(1 row)

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

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

(1 row)

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


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

(18 rows)

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

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

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

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

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

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

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

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

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

Seq Scan on oversized rows table (TOASTed)

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

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

CREATE TABLE

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

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

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

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

(1 row)

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

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

(1 row)

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

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

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

(8 rows)

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

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

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

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

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

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

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

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

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

(6 rows)

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

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

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

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

eBPF (block layer)

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

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

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

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

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

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

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

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

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

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

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

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

Conclusion

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

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

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

Hybrid Search (Full-Text and Vector Similarity) in HorizonDB

Franck Pachot — Mon, 29 Jun 2026 18:17:02 +0000

You may have read about Hybrid search for Azure HorizonDB. It is presented as combining BM25 full‑text and vector similarity in a single query. But how are they actually combined? The execution plan answers that.

In this post, I use a small synthetic product catalog to ensure the entire demo is reproducible. The text is sufficiently realistic for BM25 queries, and the embeddings are deterministic synthetic vectors, allowing you to run the full script without needing an embedding model. If you have azure_openai.create_embeddings() configured, you can substitute the synthetic embedding function with actual embeddings.

I will test two separate queries, followed by three methods of combining BM25 with vector retrieval:

cascade (BM25 → vector)
cascade (vector → BM25)
hybrid (parallel + fusion)

These are not interchangeable: they represent distinct trade-offs between recall and performance.

Setup

This example uses:

pg_textsearch for BM25 full-text search
pgvector for the vector type and distance operator
pg_diskann for the vector index, when available

The extensions must be listed in azure.extensions to enable CREATE EXTENSION.

In addition, pg_textsearch must be loaded on startup:

Here is my HorizonDB configuration:

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

postgres=> show azure.extensions;

             azure.extensions
------------------------------------------
 pg_diskann,vector,pg_textsearch,azure_ai

postgres=> show shared_preload_libraries;

                               shared_preload_libraries
---------------------------------------------------------------------------------------
 pg_textsearch,azure,orion_storage,pg_availability,pg_qs,pgms_stats,pgms_wait_sampling

I've set up the extension features, functions, data types, and operators using CREATE EXTENSIONS and created a schema for this demo:


create extension if not exists vector;
create extension if not exists pg_textsearch;
create extension if not exists pg_diskann;

drop schema if exists hybrid_demo cascade;
create schema hybrid_demo;

set search_path = hybrid_demo, public, pgfts;

For the demo: a deterministic embedding function

I should use azure_openai.create_embeddings(), as I mentioned in the previous blog post. However, for this demo, I opted to create a fake embedding function that maps text to a simple vector(16) based on product keywords. This isn't an embedding model, but it ensures that execution plans are reproducible without relying on a model. It also simplifies the concept of embeddings with a basic, small-dimension LIKE-style semantic vector. The purpose is to demonstrate a search query using both a real text search and a vector-based semantic search.

create or replace function demo_embedding(txt text)
returns vector(16)
language sql
immutable
parallel safe
as $$
select (
  '[' ||
  concat_ws(',',
    case when txt ~* '(mid|century)'       then '1.0' else '0.0' end,
    case when txt ~* '(modern|minimalist)' then '1.0' else '0.0' end,
    case when txt ~* '(chair|seat)'        then '1.0' else '0.0' end,
    case when txt ~* '(table|desk)'        then '1.0' else '0.0' end,
    case when txt ~* '(sofa|couch)'        then '1.0' else '0.0' end,
    case when txt ~* '(lamp|light)'        then '1.0' else '0.0' end,
    case when txt ~* '(wood|walnut|oak)'   then '1.0' else '0.0' end,
    case when txt ~* '(metal|steel)'       then '1.0' else '0.0' end,
    case when txt ~* '(leather)'           then '1.0' else '0.0' end,
    case when txt ~* '(fabric|linen)'      then '1.0' else '0.0' end,
    case when txt ~* '(industrial)'        then '1.0' else '0.0' end,
    case when txt ~* '(scandinavian)'      then '1.0' else '0.0' end,
    case when txt ~* '(office)'            then '1.0' else '0.0' end,
    case when txt ~* '(dining)'            then '1.0' else '0.0' end,
    case when txt ~* '(classic|vintage)'   then '1.0' else '0.0' end,
    case when txt ~* '(outdoor|garden)'    then '1.0' else '0.0' end
  ) ||
  ']'
)::vector;
$$;

Using a straightforward CASE that searches for specific words, I create the semantic vector for a text. This method employs vector similarity solely for demonstration, without utilizing a model. You can envision AI models doing the same but with thousands of dimensions driven by large language models (LLMs) rather than keywords.

Table with text and embeddings

I created a product catalog table with structured fields, text, and a single vector column:

create table products (
  product_id bigint primary key,
  category   text not null,
  brand      text not null,
  price      numeric(10,2) not null,
  title      text not null,
  description text not null,
  search_text text generated always as (
    title || ' ' || description
  ) stored,
  embedding vector(16) not null
);

I loaded a few hundred thousand rows. The data is synthetic but intentionally patterned: products have categories, materials, styles, and terms that are useful for both BM25 and vector search:

insert into products ( product_id, category, brand, price, title, description, embedding)
with generated as (
  select
    g as product_id,
    (array[ 'chair', 'table', 'sofa', 'lamp', 'desk', 'shelf' ])[1 + (g % 6)] as category,
    (array[ 'Contoso', 'Fabrikam', 'Northwind', 'AdventureWorks', 'Wingtip', 'Tailspin' ])[1 + (g % 6)] as brand,
    (array[ 'mid-century modern', 'industrial', 'scandinavian', 'classic', 'minimalist', 'outdoor' ])[1 + (g % 6)] as style,
    (array[ 'walnut wood', 'black metal', 'oak wood', 'leather', 'fabric', 'brushed steel' ])[1 + ((g / 7) % 6)] as material, (25 + (g % 500))::numeric(10,2) as price
  from generate_series(1, 200000) as g
)
select product_id, category, brand, price,
  initcap(style || ' ' || material || ' ' || category) as title,
  concat( style, ' ', category, ' by ', brand, ' with ', material, '. Designed for ',
    case
      when category in ('chair', 'sofa') then 'living room seating'
      when category in ('table', 'desk') then 'home office and dining'
      when category = 'lamp' then 'warm interior lighting'
      else 'storage and display'
    end,
    '. Product code ', product_id, '.'
  ) as description,
  demo_embedding(
    concat_ws(' ', style, material, category, brand)
  ) as embedding
from generated;

I checked a few rows:

postgres=>
 select product_id, title, category, brand, price, search_text, embedding
from products where price >= 42
order by product_id
limit 10;

 product_id |               title               | category |     brand      | price |                                                               search_text                                                               |             embedding
------------+-----------------------------------+----------+----------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------
         17 | Outdoor Oak Wood Shelf            | shelf    | Tailspin       | 42.00 | Outdoor Oak Wood Shelf outdoor shelf by Tailspin with oak wood. Designed for storage and display. Product code 17.                      | [0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,1]
         18 | Mid-Century Modern Oak Wood Chair | chair    | Contoso        | 43.00 | Mid-Century Modern Oak Wood Chair mid-century modern chair by Contoso with oak wood. Designed for living room seating. Product code 18. | [1,1,1,0,0,0,1,0,0,0,0,0,0,0,0,0]
         19 | Industrial Oak Wood Table         | table    | Fabrikam       | 44.00 | Industrial Oak Wood Table industrial table by Fabrikam with oak wood. Designed for home office and dining. Product code 19.             | [0,0,0,1,0,0,1,0,0,0,1,0,0,0,0,0]
         20 | Scandinavian Oak Wood Sofa        | sofa     | Northwind      | 45.00 | Scandinavian Oak Wood Sofa scandinavian sofa by Northwind with oak wood. Designed for living room seating. Product code 20.             | [0,0,0,0,1,0,1,0,0,0,0,1,0,0,0,0]
         21 | Classic Leather Lamp              | lamp     | AdventureWorks | 46.00 | Classic Leather Lamp classic lamp by AdventureWorks with leather. Designed for warm interior lighting. Product code 21.                 | [0,0,0,0,0,1,0,0,1,0,0,0,0,0,1,0]
         22 | Minimalist Leather Desk           | desk     | Wingtip        | 47.00 | Minimalist Leather Desk minimalist desk by Wingtip with leather. Designed for home office and dining. Product code 22.                  | [0,1,0,1,0,0,0,0,1,0,0,0,0,0,0,0]
         23 | Outdoor Leather Shelf             | shelf    | Tailspin       | 48.00 | Outdoor Leather Shelf outdoor shelf by Tailspin with leather. Designed for storage and display. Product code 23.                        | [0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,1]
         24 | Mid-Century Modern Leather Chair  | chair    | Contoso        | 49.00 | Mid-Century Modern Leather Chair mid-century modern chair by Contoso with leather. Designed for living room seating. Product code 24.   | [1,1,1,0,0,0,0,0,1,0,0,0,0,0,0,0]
         25 | Industrial Leather Table          | table    | Fabrikam       | 50.00 | Industrial Leather Table industrial table by Fabrikam with leather. Designed for home office and dining. Product code 25.               | [0,0,0,1,0,0,0,0,1,0,1,0,0,0,0,0]
         26 | Scandinavian Leather Sofa         | sofa     | Northwind      | 51.00 | Scandinavian Leather Sofa scandinavian sofa by Northwind with leather. Designed for living room seating. Product code 26.               | [0,0,0,0,1,0,0,0,1,0,0,1,0,0,0,0]


(10 rows)

I tested some searches on search_text and embedding and started indexing those columns.

Create the indexes (BM25 and DiskANN)

First, I created the BM25 full-text index. HorizonDB’s BM25 full-text search brings BM25 ranking into PostgreSQL without a separate Elasticsearch/OpenSearch Search service. It uses the open-source extension pg_textsearch:


postgres=> create index products_bm25_idx
            on products
            using bm25 (search_text)
            with (text_config = 'english')
;

NOTICE:  BM25 index build started for relation products_bm25_idx
NOTICE:  Using text search configuration: english
NOTICE:  Using index options: k1=1.20, b=0.75
NOTICE:  parallel index build: launched 2 of 2 requested workers

NOTICE:  BM25 index build completed: 200000 documents, avg_length=16.17

CREATE INDEX

Then I created the vector index using DiskANN and cosine similarity:


postgres=> create index products_embedding_diskann_idx
            on products
            using diskann (embedding vector_cosine_ops)
;

CREATE INDEX

I gathered the statistics:


postgres=> vacuum analyze products;

ANALYZE

My data set is ready for queries.

Query 1: BM25 only

I started with a keyword search, using to_bm25query() to define the BM25 query. Ranking uses BM25 and is performed with the <@> operator. Top-k queries use this operator in ORDER BY ... LIMIT.

BM25 is implemented as an index-backed operator that must be bound to a specific index. This is why prepared statements require explicitly naming the index:


postgres=> prepare query1 (text, int) as
select
  p.product_id,
  p.title,
  p.category,
  p.brand,
  p.price
from products p
order by p.search_text <@> to_bm25query( $1 , 'products_bm25_idx' )
limit $2;

postgres=> explain (analyze, buffers, verbose, costs off) 
           execute query1 ('mid century modern wooden chair', 10)
;

                                                                 QUERY PLAN                                                          
---------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.268..0.442 rows=10 loops=1)
   Output: product_id, title, category, brand, price, ((search_text <@> 'products_bm25_idx:mid century modern wooden chair'::bm25query))
   Buffers: shared hit=582
   ->  Index Scan using products_bm25_idx on hybrid_demo.products p (actual time=0.267..0.440 rows=10 loops=1)
         Output: product_id, title, category, brand, price, (search_text <@> 'products_bm25_idx:mid century modern wooden chair'::bm25query)
         Order By: (p.search_text <@> 'products_bm25_idx:mid century modern wooden chair'::bm25query)
         Buffers: shared hit=582
 Query Identifier: -4837396746836655100
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.116 ms
 Execution Time: 0.460 ms

(12 rows)

The Index Scan returns the Top-10 result ('rows=10') directly in ranking order (Order By).

Lexical retrieval is good for exact words, rare terms, product codes, and anything where the user expects the same token to appear in the document.

Query 2: ANN only

I further explored the semantic aspect by performing similarity search using the cosine distance operator (<=>) for vectors:

postgres=> prepare query2 (text, int) as
select
  p.product_id,
  p.title,
  p.category,
  p.brand,
  p.price,
  p.embedding <=> demo_embedding($1) as distance
from products p
order by p.embedding <=> demo_embedding($1)
limit $2;

postgres=> explain (analyze, buffers, verbose, costs off)
execute query2 ('modern wooden chair', 10);

                                                         QUERY PLAN                                                     
----------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.374..0.399 rows=10 loops=1)
   Output: product_id, title, category, brand, price, ((embedding <=> '[0,1,1,0,0,0,1,0,0,0,0,0,0,0,0,0]'::vector))
   Buffers: shared hit=562
   ->  Index Scan using products_embedding_diskann_idx on hybrid_demo.products p (actual time=0.374..0.397 rows=10 loops=1)
         Output: product_id, title, category, brand, price, (embedding <=> '[0,1,1,0,0,0,1,0,0,0,0,0,0,0,0,0]'::vector)
         Order By: (p.embedding <=> '[0,1,1,0,0,0,1,0,0,0,0,0,0,0,0,0]'::vector)
         Buffers: shared hit=562
 Query Identifier: 2112846290524187050
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.275 ms
 Execution Time: 0.414 ms

(12 rows)

The Index Scan returns the Top-10 result (rows=10) directly in ranking order (Order By).

The key distinction is that it doesn't search for words. Instead, it finds rows near the query vector. Using the synthetic vector function, this refers to rows that share similar dimensions. With actual embeddings, it translates to model similarity.

Query 3: BM25 first, then vector search

A straightforward approach is to use BM25 as a preliminary filter before vector search. Here, the vector index is bypassed, and an accurate nearest neighbors search is performed on the candidates selected by BM25:


postgres=> prepare query3 (text, text, int, int) as
with
bm25_candidates as (
  select *
  from products p
  order by p.search_text <@> to_bm25query($1, 'products_bm25_idx')
  limit $3
)
select
  p.product_id, p.title, p.category, p.brand, p.price
from bm25_candidates p
order by p.embedding <=> demo_embedding($2)
limit $4;

postgres=> explain (analyze, buffers, verbose, costs off)
execute query3 (
  'mid century modern wooden chair',
  'modern wooden chair',
  5000,
  10
);

                                                                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=97.529..97.531 rows=10 loops=1)
   Output: p.product_id, p.title, p.category, p.brand, p.price, ((p.embedding <=> '[0,1,1,0,0,0,1,0,0,0,0,0,0,0,0,0]'::vector))
   Buffers: shared hit=29482
   ->  Sort (actual time=97.528..97.529 rows=10 loops=1)
         Output: p.product_id, p.title, p.category, p.brand, p.price, ((p.embedding <=> '[0,1,1,0,0,0,1,0,0,0,0,0,0,0,0,0]'::vector))
         Sort Key: ((p.embedding <=> '[0,1,1,0,0,0,1,0,0,0,0,0,0,0,0,0]'::vector))
         Sort Method: top-N heapsort  Memory: 26kB
         Buffers: shared hit=29482
         ->  Subquery Scan on p (actual time=3.312..96.508 rows=5000 loops=1)
               Output: p.product_id, p.title, p.category, p.brand, p.price, (p.embedding <=> '[0,1,1,0,0,0,1,0,0,0,0,0,0,0,0,0]'::vector)
               Buffers: shared hit=29482
               ->  Limit (actual time=3.310..95.687 rows=5000 loops=1)
                     Output: p_1.product_id, p_1.category, p_1.brand, p_1.price, p_1.title, NULL::text, NULL::text, p_1.embedding, ((p_1.search_text <@> 'products_bm25_idx:mid century modern wooden chair'::bm25query))
                     Buffers: shared hit=29482
                     ->  Index Scan using products_bm25_idx on hybrid_demo.products p_1 (actual time=3.309..95.361 rows=5000 loops=1)
                           Output: p_1.product_id, p_1.category, p_1.brand, p_1.price, p_1.title, NULL::text, NULL::text, p_1.embedding, (p_1.search_text <@> 'products_bm25_idx:mid century modern wooden chair'::bm25query)
                           Order By: (p_1.search_text <@> 'products_bm25_idx:mid century modern wooden chair'::bm25query)
                           Buffers: shared hit=29482
 Query Identifier: 2456399702791328288
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.328 ms
 Execution Time: 97.560 ms
(23 rows)

The execution plan features a two-phase pipeline:

Phase 1 (BM25): An index scan on products_bm25_idx retrieves the top 5000 candidates sorted by BM25 score (~95 ms).
Phase 2 (vector): A top‑N heapsort calculates exact vector distances on these 5000 rows (~2 ms).

This method uses BM25 to generate candidates, followed by vector ranking. It performs well when the query includes strong keywords, but it is not a hybrid search: BM25 sets recall, and the vector step only reranks.

Therefore, a sufficiently large candidate set (LIMIT $3) is necessary to avoid missing relevant results. Increasing this limit boosts recall but also raises latency, as most of the time is spent in the BM25 top‑K retrieval.

Query 4: ANN first, then text search

An alternative approach is to utilize the vector index (ANN) followed by text search as a post-filter. Here, the BM25 index is not used, and text ranking is only applied to the candidates identified by the vector search:


postgres=> prepare query4 (text, text, int, int) as
with
ann_candidates as (
  select *
  from products p
  order by p.embedding <=> demo_embedding($1)
  limit $3
)
select
  p.product_id, p.title, p.category, p.brand, p.price
from ann_candidates p
order by p.search_text <@> to_bm25query($2, 'products_bm25_idx')
limit $4;

postgres=> explain (analyze, buffers, verbose, costs off)
execute query4 (
  'modern wooden chair',
  'mid century modern wooden chair',
  5000,
  10
);

                                                                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=250.982..250.984 rows=10 loops=1)
   Output: p.product_id, p.title, p.category, p.brand, p.price, ((p.search_text <@> 'products_bm25_idx:mid century modern wooden chair'::bm25query))
   Buffers: shared hit=74975
   ->  Sort (actual time=250.981..250.982 rows=10 loops=1)
         Output: p.product_id, p.title, p.category, p.brand, p.price, ((p.search_text <@> 'products_bm25_idx:mid century modern wooden chair'::bm25query))
         Sort Key: ((p.search_text <@> 'products_bm25_idx:mid century modern wooden chair'::bm25query))
         Sort Method: top-N heapsort  Memory: 26kB
         Buffers: shared hit=74975
         ->  Subquery Scan on p (actual time=26.789..249.873 rows=5000 loops=1)
               Output: p.product_id, p.title, p.category, p.brand, p.price, (p.search_text <@> 'products_bm25_idx:mid century modern wooden chair'::bm25query)
               Buffers: shared hit=74975
               ->  Limit (actual time=26.681..153.452 rows=5000 loops=1)
                     Output: p_1.product_id, p_1.category, p_1.brand, p_1.price, p_1.title, NULL::text, p_1.search_text, NULL::vector(16), ((p_1.embedding <=> '[0,1,1,0,0,0,1,0,0,0,0,0,0,0,0,0]'::vector))
                     Buffers: shared hit=59825
                     ->  Index Scan using products_embedding_diskann_idx on hybrid_demo.products p_1 (actual time=26.681..153.072 rows=5000 loops=1)
                           Output: p_1.product_id, p_1.category, p_1.brand, p_1.price, p_1.title, NULL::text, p_1.search_text, NULL::vector(16), (p_1.embedding <=> '[0,1,1,0,0,0,1,0,0,0,0,0,0,0,0,0]'::vector)
                           Order By: (p_1.embedding <=> '[0,1,1,0,0,0,1,0,0,0,0,0,0,0,0,0]'::vector)
                           Buffers: shared hit=59825
 Query Identifier: 778851517376612713
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.311 ms
 Execution Time: 251.015 ms

(23 rows)

The execution plan displays the same two-phase pipeline but in reverse order:

Phase 1 (vector ANN): The vector index (DiskANN) retrieves the 5000 nearest neighbors based on embedding similarity, which is quick and index-driven.
- Phase 2 (BM25): A top‑N heapsort calculates BM25 scores for those 5000 entries. Since ranking occurs after the CTE materialization, no BM25 index is used.

This setup is not a hybrid search. Instead, the vector phase defines recall, and BM25 only fine-tunes the ranking.

Query 5: hybrid search with RRF

A hybrid search merges candidates from both search types. I run a query that performs these steps:

Executes a text search and retains the top 50 results.
Performs a vector search and keeps the top 50.
Ranks the small candidate lists.
Merges the candidate sets.
Uses Reciprocal Rank Fusion (RRF).
Outputs a single ranked result set.

The full query defines each step as a CTE in a WITH clause. While the query planner typically inlines these by default, I added MATERIALIZE for the demo to visualize each search and ranking stage separately:


postgres=> prepare query5 (text, text, int, int) as
with
bm25 as (
  select
    p.product_id,
    p.search_text <@> to_bm25query($1, 'products_bm25_idx') as score
  from products p
  order by p.search_text <@> to_bm25query($1, 'products_bm25_idx')
  limit $3
),
bm25_ranked as (
  select
    product_id,
    row_number() over (order by score) as bm25_rank
  from bm25
),
vec as (
  select
    p.product_id,
    p.embedding <=> demo_embedding($2) as distance
  from products p
  order by p.embedding <=> demo_embedding($2)
  limit $3
),
vec_ranked as (
  select
    product_id,
    row_number() over (order by distance) as vec_rank
  from vec
),
candidates as (
  select product_id from bm25_ranked
  union
  select product_id from vec_ranked
)
select
  p.product_id,
  p.title,
  p.category,
  p.brand,
  p.price,
  b.bm25_rank,
  v.vec_rank,
  (
    1.0 / (60 + coalesce(b.bm25_rank, 1000))
    +
    1.0 / (60 + coalesce(v.vec_rank, 1000))
  ) as rrf_score
from candidates c
join products p using (product_id)
left join bm25_ranked b using (product_id)
left join vec_ranked  v using (product_id)
order by rrf_score desc
limit $4;

postgres=> explain (analyze, buffers, verbose, costs off)
execute query5 (
  'mid century modern wooden chair',
  'modern wooden chair',
  50,
  10
);

                                                                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=84.994..85.001 rows=10 loops=1)
   Output: p.product_id, p.title, p.category, p.brand, p.price, b.bm25_rank, v.vec_rank, (((1.0 / ((60 + COALESCE(b.bm25_rank, '1000'::bigint)))::numeric) + (1.0 / ((60 + COALESCE(v.vec_rank, '1000'::bigint)))::numeric)))
   Buffers: shared hit=34010
   CTE bm25
     ->  Limit (actual time=0.260..1.180 rows=50 loops=1)
           Output: p_1.product_id, ((p_1.search_text <@> 'products_bm25_idx:mid century modern wooden chair'::bm25query))
           Buffers: shared hit=803
           ->  Index Scan using products_bm25_idx on hybrid_demo.products p_1 (actual time=0.260..1.176 rows=50 loops=1)
                 Output: p_1.product_id, (p_1.search_text <@> 'products_bm25_idx:mid century modern wooden chair'::bm25query)
                 Order By: (p_1.search_text <@> 'products_bm25_idx:mid century modern wooden chair'::bm25query)
                 Buffers: shared hit=803
   CTE bm25_ranked
     ->  WindowAgg (actual time=0.263..1.202 rows=50 loops=1)
           Output: bm25.product_id, row_number() OVER (?), bm25.score
           Buffers: shared hit=803
           ->  CTE Scan on bm25 (actual time=0.261..1.189 rows=50 loops=1)
                 Output: bm25.score, bm25.product_id
                 Buffers: shared hit=803
   CTE vec
     ->  Limit (actual time=26.758..83.367 rows=50 loops=1)
           Output: p_2.product_id, ((p_2.embedding <=> '[0,1,1,0,0,0,1,0,0,0,0,0,0,0,0,0]'::vector))
           Buffers: shared hit=32807
           ->  Index Scan using products_embedding_diskann_idx on hybrid_demo.products p_2 (actual time=26.757..83.360 rows=50 loops=1)
                 Output: p_2.product_id, (p_2.embedding <=> '[0,1,1,0,0,0,1,0,0,0,0,0,0,0,0,0]'::vector)
                 Order By: (p_2.embedding <=> '[0,1,1,0,0,0,1,0,0,0,0,0,0,0,0,0]'::vector)
                 Buffers: shared hit=32807
   CTE vec_ranked
     ->  WindowAgg (actual time=26.764..83.400 rows=50 loops=1)
           Output: vec.product_id, row_number() OVER (?), vec.distance
           Buffers: shared hit=32807
           ->  CTE Scan on vec (actual time=26.760..83.382 rows=50 loops=1)
                 Output: vec.distance, vec.product_id
                 Buffers: shared hit=32807
   ->  Sort (actual time=84.993..84.996 rows=10 loops=1)
         Output: p.product_id, p.title, p.category, p.brand, p.price, b.bm25_rank, v.vec_rank, (((1.0 / ((60 + COALESCE(b.bm25_rank, '1000'::bigint)))::numeric) + (1.0 / ((60 + COALESCE(v.vec_rank, '1000'::bigint)))::numeric)))
         Sort Key: (((1.0 / ((60 + COALESCE(b.bm25_rank, '1000'::bigint)))::numeric) + (1.0 / ((60 + COALESCE(v.vec_rank, '1000'::bigint)))::numeric))) DESC
         Sort Method: top-N heapsort  Memory: 27kB
         Buffers: shared hit=34010
         ->  Hash Left Join (actual time=84.714..84.944 rows=100 loops=1)
               Output: p.product_id, p.title, p.category, p.brand, p.price, b.bm25_rank, v.vec_rank, ((1.0 / ((60 + COALESCE(b.bm25_rank, '1000'::bigint)))::numeric) + (1.0 / ((60 + COALESCE(v.vec_rank, '1000'::bigint)))::numeric))
               Hash Cond: (bm25_ranked.product_id = v.product_id)
               Buffers: shared hit=34010
               ->  Hash Left Join (actual time=84.698..84.887 rows=100 loops=1)
                     Output: bm25_ranked.product_id, p.product_id, p.title, p.category, p.brand, p.price, b.bm25_rank
                     Hash Cond: (bm25_ranked.product_id = b.product_id)
                     Buffers: shared hit=34010
                     ->  Nested Loop (actual time=84.679..84.855 rows=100 loops=1)
                           Output: bm25_ranked.product_id, p.product_id, p.title, p.category, p.brand, p.price
                           Inner Unique: true
                           Buffers: shared hit=34010
                           ->  HashAggregate (actual time=84.659..84.668 rows=100 loops=1)
                                 Output: bm25_ranked.product_id
                                 Group Key: bm25_ranked.product_id
                                 Batches: 1  Memory Usage: 24kB
                                 Buffers: shared hit=33610
                                 ->  Append (actual time=0.265..84.634 rows=100 loops=1)
                                       Buffers: shared hit=33610
                                       ->  CTE Scan on bm25_ranked (actual time=0.265..1.212 rows=50 loops=1)
                                             Output: bm25_ranked.product_id
                                             Buffers: shared hit=803
                                       ->  CTE Scan on vec_ranked (actual time=26.765..83.413 rows=50 loops=1)
                                             Output: vec_ranked.product_id
                                             Buffers: shared hit=32807
                           ->  Index Scan using products_pkey on hybrid_demo.products p (actual time=0.002..0.002 rows=1 loops=100)
                                 Output: p.product_id, p.category, p.brand, p.price, p.title, p.description, p.search_text, p.embedding
                                 Index Cond: (p.product_id = bm25_ranked.product_id)
                                 Buffers: shared hit=400
                     ->  Hash (actual time=0.011..0.011 rows=50 loops=1)
                           Output: b.bm25_rank, b.product_id
                           Buckets: 1024  Batches: 1  Memory Usage: 11kB
                           ->  CTE Scan on bm25_ranked b (actual time=0.001..0.006 rows=50 loops=1)
                                 Output: b.bm25_rank, b.product_id
               ->  Hash (actual time=0.009..0.009 rows=50 loops=1)
                     Output: v.vec_rank, v.product_id
                     Buckets: 1024  Batches: 1  Memory Usage: 11kB
                     ->  CTE Scan on vec_ranked v (actual time=0.000..0.004 rows=50 loops=1)
                           Output: v.vec_rank, v.product_id
 Query Identifier: 6796811579565214126
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.515 ms
 Execution Time: 85.087 ms
(82 rows)

Here is how the hybrid search with PostgreSQL extensions (pg_textsearch for BM25, pg_diskann for vectors) works in this query:

BM25 branch: Index Scan using products_bm25_idx → 50 rows in ~1 ms
Vector branch: Index Scan using products_embedding_diskann_idx → 50 rows in ~80 ms
Fusion layer: 100 candidate IDs from the two branches (Append), looked up by primary key (Index Scan products_pkey via Nested Loop), joined with their ranks (Hash Join), then a final top‑N sort on the RRF score.

RRF, or Reciprocal Rank Fusion, is a straightforward method for combining multiple rankings. It uses a constant (usually 60) to smooth out rank differences, ensuring that an item must perform well across several lists rather than just one to stand out. Since RRF operates on ranks rather than raw scores, it sidesteps the issue of comparing different scoring systems, such as BM25 and vector scores, which are on different scales.

Without MATERIALIZE, the query shape is:

 Limit
   CTE bm25_ranked
     ->  WindowAgg
           ->  Subquery Scan on bm25
                 ->  Limit
                       ->  Index Scan using products_bm25_idx on products p_1
                             Order By: (search_text <@> 'products_bm25_idx:mid century modern wooden chair'::bm25query)
   CTE vec_ranked
     ->  WindowAgg
           ->  Subquery Scan on vec
                 ->  Limit
                       ->  Index Scan using products_embedding_diskann_idx on products p_2
                             Order By: (embedding <=> '[0,1,1,0,0,0,1,0,0,0,0,0,0,0,0,0]'::vector)
   ->  Sort
         Sort Key: (((1.0 / ((60 + COALESCE(b.bm25_rank, '1000'::bigint)))::numeric) + (1.0 / ((60 + COALESCE(v.vec_rank, '1000'::bigint)))::numeric))) DESC
         ->  Hash Left Join
               Hash Cond: (bm25_ranked.product_id = v.product_id)
               ->  Hash Left Join
                     Hash Cond: (bm25_ranked.product_id = b.product_id)
                     ->  Nested Loop
                           ->  HashAggregate
                                 Group Key: bm25_ranked.product_id
                                 ->  Append
                                       ->  CTE Scan on bm25_ranked
                                       ->  CTE Scan on vec_ranked
                           ->  Index Scan using products_pkey on products p
                                 Index Cond: (product_id = bm25_ranked.product_id)
                     ->  Hash
                           ->  CTE Scan on bm25_ranked b
               ->  Hash
                     ->  CTE Scan on vec_ranked v

This is a hybrid search method in HorizonDB that involves two top‑k retrievals and a ranking layer expressed in SQL. No single hybrid index scan is used. PostgreSQL employs different access methods for text and vector search, with fusion occurring above these scans.

This approach offers a database‑friendly way to implement hybrid search that is explicit and easy to debug: you can see both branches in the plan (CTE), adjust the candidate size (LIMIT 50), balance BM25 versus vector scores (rrf_score), which can be swapped with a reranker, and inspect each candidate list separately, including the number of rows and buffers read.

In HorizonDB, hybrid search isn't about merging indexes into a single search engine. It involves correctly combining multiple top‑k retrievals within a SQL statement.

Conclusion

Hybrid search in HorizonDB isn't a new index type or access method. It's a query pattern created with PostgreSQL extensions.

Execution plans clarify this: BM25 and vector search operate as separate index scans, each with its own ranking system. PostgreSQL doesn’t combine them into a single operator. Instead, it performs independent top‑k retrievals and merges the results within SQL.

From the examples, three patterns emerge:

BM25 → vector: lexical search defines recall, vector search reranks
Vector → BM25: semantic search defines recall, text search reranks
BM25 + vector (RRF): both enhance recall, and their results are fused

Of these, only the last is truly hybrid. The first two are cascades in which one stage generates candidates, and the other refines them.

This distinction affects both relevance and performance. In cascade queries, recall is constrained by the first stage: anything not retrieved at that stage is lost. In hybrid queries, both branches contribute, boosting recall but requiring two index scans.

Plan analysis also shows where time is spent. In my examples, nearly all the cost is in the top‑k retrieval (BM25 or ANN), while reranking is less expensive. The primary parameter to tune is candidate size, not the scoring method.

A practical point: RRF uses ranks instead of raw scores to avoid normalization issues between BM25 and vector metrics.

Hybrid search in PostgreSQL is explicit and modular: separate retrievals combined within SQL. This makes execution plans clear and trade-offs transparent.

Azure AI on HorizonDB (Microsoft Foundry Azure OpenAI from SQL)

Franck Pachot — Sat, 27 Jun 2026 23:08:39 +0000

The azure_ai extension on HorizonDB adds generative AI functions to PostgreSQL, allowing users to utilize Azure AI's generation, ranking, and embedding models. Here's a four-step example that demonstrates how to define default models, set up endpoints, register them, and use them in SQL queries.

1. Allow and install the extension

The azure_ai extension must be set in azure.extensions from the parameter group:

Once enabled, you can CREATE EXTENSION:

postgres=> SHOW azure.extensions;

             azure.extensions
------------------------------------------
 pg_diskann,vector,pg_textsearch,azure_ai

(1 row)

postgres=> CREATE EXTENSION IF NOT EXISTS azure_ai;

CREATE EXTENSION

The functions are available, but I don't have access to an AI model yet:

postgres=> SELECT azure_ai.generate(
            'Hello'
           );

ERROR:  Endpoint not found.
DETAIL:  Please set/register the model.

An AI Model Management feature is coming to HorizonDB, currently in private preview, which is basically a zero-setup mode for azure_ai, but for the moment, I will do it manually.

2. Deploy a model in Azure

I go to the Microsoft Foundry | Azure OpenAI, hit "Create" and select "Azure OpenAI":

I set my resource group and region:

I use the default network setting that allows all networks, including the internet, to access this resource.

Once created, the next step is to "go to resource" in order to deploy a model:

In the model catalog, I select the chat models, for LLM tasks and generation purposes:

I hit "Uset this model" and deploy gpt-4o-mini:

The URL and API key is displayed in the Home section:

The details for the available models is in the Deployments section:

I can also get the parameters from the Python sample:

endpoint = "https://frankpachot-ai.openai.azure.com/"
model_name = "gpt-4o-mini"
deployment = "gpt-4o-mini"

subscription_key = "<your-api-key>"
api_version = "2024-12-01-preview"

client = AzureOpenAI(
    api_version=api_version,
    azure_endpoint=endpoint,
    api_key=subscription_key,

This information will be used to register the model from PostgreSQL.

I'll use them directly, but in production, keys should be stored in a secrets management system instead of being hardcoded in SQL.

3. Register the model in PostgreSQL

In HorizonDB, I can add AI models with model_registry.model_add() which takes the following parameters:

parameter	meaning
alias	SQL name
endpoint	where to call the model
deployment	which model instance
model_name	metadata / capability
api_version	protocol version
auth_type	how to authenticate
key	credential

The deployment name must exactly match the one defined in Azure. This is not the model name but the deployment identifier.

Here is the registration with the information from the chat model I deployed:

postgres=> SELECT model_registry.model_add(
    'default-chat',                                 -- alias
    'https://frankpachot-ai.openai.azure.com/',     -- azure_endpoint
    'gpt-4o-mini',                                  -- deployment name
    'gpt-4o-mini',                                      -- model name
    '2024-12-01-preview',                           -- api_version
    'subscription-key',                             -- auth type
    'FR3Xcz5VXiHSbz8Eqeo5qXsyKqgxrFeYCSuqOv...'     -- api_key
);

                       model_add
--------------------------------------------------------
 Model 'default-chat' (gpt-4o-mini) added successfully.

(1 row)

PostgreSQL can now invoke the Azure OpenAI deployment.

With this solution, the database itself doesn’t host the model. It only contains the information needed to call it, such as the endpoint, deployment, and key, but the calls to Azure OpenAI are transparent to the users.

4. Use the model from SQL queries

Generate

Now, azure_ai.generate() can use the registered model for generative AI:

postgres=> SELECT azure_ai.generate(
    'Who is Slonik and how does he look like? context:'|| version()
  , 'default-chat'
);

generate     
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Slonik is a mascot for PostgreSQL, often depicted as a friendly, cartoonish elephant. He typically has a blue-gray body, large floppy ears, and a cheerful expression. Slonik embodies the PostgreSQL community's spirit and is often used in promotional materials and events related to PostgreSQL.
(1 row)

postgres=>

The model name default-chat is the default for this function. I can omit it.

Validate

Instead of employing the model for text generation, I can utilize it to validate my text with azure_ai.is_true().

Let's verify the correct names for our favorite database:

postgres=> select azure_ai.is_true( format (
           '%s is the right name for Slonik''s database', unnest
            )), string_agg(unnest,',') from unnest(ARRAY[
            'PostgreSQL','Postgres','PG','pgsql','postgresql',
            'POSTGRES','pgdb','postgres-db','SQL','psql',
            'postmaster','postgré','postgrès','posgress',
            'posgresql','postgrasql','postgray','postgrest',
            'postgrezql','postgrex','postgresesql','postgresequel',
            'Post-Ingres',' Post-Gres-Q-L','Postgres95','pg-sql',
            'pgserver','pg-database','HorizonDB',
            'slonik-db','elephant-db','the elephant'
             ]) group by 1 order by 1 desc;

agg
---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 t       | PostgreSQL,Postgres,postgresql,POSTGRES,the elephant
 f       | PG,pgsql,pgdb,postgres-db,SQL,psql,postmaster,postgré,postgrès,posgress,posgresql,postgrasql,postgray,postgrest,postgrezql,postgrex,postgresesql,postgresequel,Post-Ingres, Post-Gres-Q-L,Postgres95,pg-sql,pgserver,pg-database,HorizonDB,slonik-db,elephant-db

(2 rows)

Ok, according to gpt-4o-mini, "The Elephant" is a valid name for PostgreSQL.

Extract

The chat model can also be used to extract structured information from unstructured text. For example, from the version() banner I'm interested in the PostgreSQL compatibility version, and the name of the managed service:

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=> SELECT azure_ai.extract(
             version() , -- text from which to extract
             ARRAY['PostgreSQL compatibility','Cloud service name']
            );

                                    extract
-------------------------------------------------------------------------------
 {"Cloud service name": "Azure HorizonDB", "PostgreSQL compatibility": "17.9"}

(1 row)

Search

In order to generate embeddings, I need to deploy a dedicated model:

With the same method I used for chat models, I got the endpoint, name, and key to access this embedding model:

postgres=> SELECT model_registry.model_add(
    'default-embedding',                            -- alias
    'https://franckpachot-ai.openai.azure.com/',     -- azure_endpoint
    'text-embedding-3-small',                       -- deployment name
    'text-embedding-3-small',                       -- model name
    '2024-12-01-preview',                           -- api_version
    'subscription-key',                             -- auth type
    'FR3Xcz5VXiHSbz8Eqeo5qXsyKqgxrFeYCSuqOv...'     -- api_key
);

                              model_add
---------------------------------------------------------------------
 Model 'default-embedding' (text-embedding-3-small) added successfully.

(1 row)

Using the alias default-embedding, which I defined when registering the model deployment, I can generate embeddings for specific text:

postgres=> SELECT azure_openai.create_embeddings(
    'default-embedding',
    'hello world'
);
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 create_embeddings                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
----------------------------------------------------------------
{-0.006729126,-0.03918457,0.03414917,0.028747559,-0.024841309,-0.041992188,-0.030288696,0.049316406,-0.013969421,-0.017669678,0.015396118,-0.026992798,-0.020980835,-0.027801514,0.008583069,0.03567505,-0.053619385,-0.0023059845,0.008773804,0.048034668,0.037078857,-0.009239197,-0.008781433,0.011428833,0.0140686035,-0.002161026,-0.037597656,0.04547119,0.0112838745,-0.03967285,0.0234375,-0.050628662,0.011985779,...}
(1 row)

postgres=>

Note that the azure_ai extension deploys functions in different namespaces. High‑level semantic operations, independent of the model vendor, such as generate(), is_true(), extract(), rank() are in the azure_ai schema. In contrast, create_embeddings, which returns vectors and is tied to the OpenAI API, is in the azure_openai schema.

I have also enabled pg_vector to use the vector data type and operators for generated embeddings. Here is an example using a prompt to find PostgreSQL settings related to shared buffer cache memory:

postgres=> CREATE EXTENSION vector;

CREATE EXTENSION

postgres=> WITH settings AS (
  SELECT name, short_desc, azure_openai.create_embeddings('default-embedding',
      row_to_json(pg_settings)::text
  )::vector AS embedding FROM pg_settings
) SELECT
  name, short_desc FROM settings
  ORDER BY embedding <=> azure_openai.create_embeddings('default-embedding',
    'Buffer cache shared memory allocated by PostgreSQL'
  )::vector LIMIT 5;

            name            |                                       short_desc
----------------------------+----------------------------------------------------------------------------------------
 effective_cache_size       | Sets the planner's assumption about the total size of the data caches.
 shared_memory_type         | Selects the shared memory implementation used for the main shared memory region.
 shared_buffers             | Sets the number of shared memory buffers used by the server.
 shared_memory_size         | Shows the size of the server's main shared memory area (rounded up to the nearest MB).
 dynamic_shared_memory_type | Selects the dynamic shared memory implementation used.

(5 rows)

I haven't set an index or stored embeddings here. This example simply demonstrates how it works in a stateless demo. The similarity search uses the cosine distance operator <=> to compare data from pg_settings with my prompt, returning the Top-5 matches. In a real application, you would generate the embeddings within an AI pipeline, then store and index them using pg_vector or DiskANN, rather than calling azure_openai.create_embeddings() on each query, except for the prompt.

Rank

AzureAI also provides functions for re-ranking the results of a similarity search. Ideally, a dedicated ranking model should be used. Here, I didn't deploy one and used the chat model for demonstration.:

postgres=> WITH
 settings AS (
  SELECT name, short_desc, row_to_json(pg_settings)::text AS doc,
      azure_openai.create_embeddings( 'default-embedding',
        row_to_json(pg_settings)::text
      )::vector AS embedding FROM pg_settings),
 candidates as (
  SELECT name, short_desc, doc FROM settings
  ORDER BY embedding <=> azure_openai.create_embeddings( 'default-embedding',
    'Buffer cache shared memory allocated by PostgreSQL'
  )::vector LIMIT 20 ),
 reranked AS ( SELECT * FROM azure_ai.rank(
    'Setting the buffer cache shared memory allocated by PostgreSQL',
    ARRAY (SELECT doc FROM candidates),  -- text to rank
    ARRAY (SELECT name FROM candidates), -- id of the item
    'default-chat'
  )
)
SELECT r.*, s.short_desc
 FROM settings s JOIN reranked r ON r.id = s.name
 ORDER BY r.rank LIMIT 5
;

             id             | rank | score |                                       short_desc
----------------------------+------+-------+----------------------------------------------------------------------------------------
 shared_buffers             |    1 |   0.9 | Sets the number of shared memory buffers used by the server.
 effective_cache_size       |    2 |   0.8 | Sets the planner's assumption about the total size of the data caches.
 shared_memory_type         |    3 |   0.7 | Selects the shared memory implementation used for the main shared memory region.
 shared_memory_size         |    4 |   0.5 | Shows the size of the server's main shared memory area (rounded up to the nearest MB).
 dynamic_shared_memory_type |    5 |   0.4 | Selects the dynamic shared memory implementation used.
(5 rows)

Models

I registered two models for this demonstration:

postgres=> SELECT * FROM model_registry.model_list_all();

       alias       |       model_name       |        registry_type        | model_creator | model_users |   status   |          created_at           |          updated_at           |                 endpoint                 |    auth_type     |    api_version     |    deployment_name
-------------------+------------------------+-----------------------------+---------------+-------------+------------+-------------------------------+-------------------------------+------------------------------------------+------------------+--------------------+------------------------
 default-chat      | gpt-4o-mini            | Bring Your Own Model (BYOM) | franck        | public      | registered | 2026-06-26 15:53:44.045341+00 | 2026-06-26 15:53:44.045341+00 | https://frankpachot-ai.openai.azure.com/ | subscription-key | 2024-12-01-preview | gpt-4o-mini
 default-embedding | text-embedding-3-small | Bring Your Own Model (BYOM) | franck        | public      | registered | 2026-06-26 16:16:00.059347+00 | 2026-06-26 16:16:00.059347+00 | https://frankpachot-ai.openai.azure.com/ | subscription-key | 2024-12-01-preview | text-embedding-3-small

(2 rows)

postgres=>

When embeddings are stored and indexed, the embedding search is the cheap first pass: it finds settings that are close to the question in the vector space. The reranker is the precise second pass: it looks again at the selected candidates and sorts them by relevance to the question. This is useful because vector similarity gives good candidates, but not always the best order. In this example, I even re-rank them with a more precise question.

Here is a final example, where I store the content of this blog post into a PostgreSQL table and call azure_ai to generate a pitch:

postgres=> CREATE TEMP TABLE blog(raw text);

CREATE TABLE

postgres=> \copy blog FROM PROGRAM ' curl -s "https://dev.to/franckpachot/azure-ai-on-horizondb-fbk" | tr -d "\r" | tr "\n" " " ' WITH (format text);

COPY 1

postgres=> SELECT azure_ai.generate(
           'Generate a short LinkedIn post to pitch (tech, not marketing) this article, in 5 small bullet points:' || raw
           ) FROM blog;

                                                                    generate
------------------------------------------------------------------------------------------------------------------------------------------------
 🚀 Exciting news for tech enthusiasts! Check out this article on integrating Azure AI with HorizonDB:                                         +
                                                                                                                                               +
 - **Generative AI Functions**: The `azure_ai` extension brings powerful generative AI capabilities directly to PostgreSQL.                    +
 - **Seamless Integration**: Easily install and configure the extension without altering your database architecture.                           +
 - **Model Management**: Learn how to deploy and register AI models in Azure with straightforward steps.                                       +
 - **Efficient Data Processing**: Utilize external AI models for tasks like text generation while keeping your data local.                     +
 - **Future of Databases**: This extension showcases how AI can enhance traditional database systems, merging functionality with SQL integrity.+
                                                                                                                                               +
 🔗 Read more here: [Azure AI on HorizonDB](https://dev.to/franckpachot/azure-ai-on-horizondb-fbk)
(1 row)

Conclusion

The azure_ai extension on HorizonDB (currently in preview) exposes generative AI functions directly in PostgreSQL. It does not run models locally: it stores connection details and calls external Azure AI endpoints from SQL.

This extension is only available in HorizonDB and not in upstream PostgreSQL or Azure Database for PostgreSQL. The feature depends on HorizonDB-specific integration with Azure AI services and the model registry.

The examples here show direct calls from SQL, but this is mostly for demonstration. In practice, embeddings are generated once, in workflows defined with pg_durable functions, stored with pg_vector datatypes, and indexed with HSNW or DiskANN. Queries generate embeddings for the parameters.

$lookup join strategies: understanding the trade-offs with flexible documents

Franck Pachot — Fri, 26 Jun 2026 05:37:11 +0000

In a previous post, I explored how MongoDB chooses between nested loop, indexed loop, and hash join strategies for $lookup. Here, I examine what occurs when $lookup runs on DocumentDB for PostgreSQL—an open-source extension implementing the MongoDB API on PostgreSQL.

The document model minimizes the need for joins by embedding related data directly within documents. However, when a join is necessary — such as for reference data that updates independently, many-to-many relationships, or dimensional lookups — the flexibility of embedding can complicate join optimization.

The goal isn't just to identify "which database is faster"—it's to understand why their behaviors differ, the trade-offs involved, and the options when join performance matters.

Relational databases tend to perform more joins because normalized schemas require them, but they also optimize joins more effectively thanks to scalar, well-typed columns. In contrast, document databases perform fewer joins thanks to embedding, but when they do, flexible field semantics—such as arrays—restrict the available join algorithms.

I've run all tests in Docker containers with default settings on the same machine. The timings are indicative, not benchmarks — they illustrate the relative cost of different approaches, not absolute performance under production conditions (caching, concurrency, hardware, and tuning would all change the numbers).

An example: fact and reference table

In a document database, you'd typically embed related data to avoid joins. But some data doesn't embed well:

Exchange rates change continuously. If you embed rate_to_usd inside each portfolio document, you'd need to update millions of documents every time a rate moves.
Portfolios reference a currency, and you want to compute USD valuations by looking up the current rate at query time.

This is a classic case where a $lookup join makes sense: a large fact collection (portfolios) joined to a small, frequently-updated reference collection (fxRates). The document model can't avoid this join without accepting stale embedded rates.

Schema and data generation

I created two collections:

portfolios: 5 million documents with a currency field (5 distinct values)
fxRates: 5 documents mapping each currency to its USD exchange rate

I used mongosh to create and load the collection with the following commands:

db.portfolios.drop();
db.fxRates.drop();

const currencies = ["USD", "EUR", "CHF", "GBP", "JPY"];
currencies.forEach(cur => {
  db.fxRates.insertOne({
    currency: cur,
    rate_to_usd: Math.random() * (1.5 - 0.5) + 0.5,
    last_updated: new Date()
  });
});

const totalPortfolios = 5e6;
let bulk = [];
for (let i = 1; i <= totalPortfolios; i++) {
  const currency = currencies[Math.floor(Math.random() * currencies.length)];
  bulk.push({
    portfolioId: i,
    clientId: Math.floor(Math.random() * 10000),
    valuation: Math.round(Math.random() * 1_000_000),
    currency: currency,
    asOfDate: new Date()
  });
  if (bulk.length === 10000) {
    db.portfolios.insertMany(bulk);
    bulk = [];
  }
}
if (bulk.length > 0) db.portfolios.insertMany(bulk);

db.fxRates.createIndex({ currency: 1 }, { unique: true });

The index on a five-document collection is not strictly necessary, but it's good practice and protects my lookup table from duplicates.

The Query with `$lookup`

This query fetches all portfolios, retrieves the foreign exchange rate for each currency, and converts the valuation to USD.


db.portfolios.aggregate([
  {$lookup: {
    from: "fxRates",
    localField: "currency",
    foreignField: "currency",
    as: "fx"
  }},
  {$unwind: "$fx"},
  {$project: {
    portfolioId: 1, valuation: 1, currency: 1,
    rate_to_usd: "$fx.rate_to_usd",
    valuation_usd: {$multiply: ["$valuation", "$fx.rate_to_usd"]}
  }}
])

MongoDB's $lookup combined with $unwind behaves like a LEFT OUTER JOIN followed by filtering out non-matching rows.

Why flexible documents make joins hard

In a relational database, portfolios.currency is a VARCHAR column. The optimizer knows it's a single scalar value per row. It can extract it, hash it, sort it, or probe an index with it — all with well-defined operators.

In a document database, currency might be:

A string: "USD"
An array: ["USD", "EUR"]
Missing entirely
A nested document

MongoDB's $lookup compatibility requires the following behavior:

If localField is an array ["USD", "EUR"], it matches any foreign document where foreignField equals "USD" OR "EUR" (or contains either, if it's also an array).
It's effectively an "any element matches any element" semantic.

This means that the join condition is not always a simple equality a = b, but may involve “any element matches” semantics evaluated at runtime. Instead, the matching logic must evaluate each document's field at runtime, determine whether it's a scalar or an array, and match accordingly.

The safest general approach is a lateral join — executing the inner query for each outer document and passing the current document's field value into the matching function. This is what both MongoDB and DocumentDB for PostgreSQL do.

What happens under the hood (DocumentDB for PostgreSQL)

I use the DocumentDB API in a SQL query rather than the MongoDB-compatible endpoint to view the PostgreSQL execution plan.

EXPLAIN (ANALYZE ON, BUFFERS ON, COSTS ON, VERBOSE ON)
SELECT document
FROM bson_aggregation_pipeline('test',
'{
  "aggregate": "portfolios",
  "pipeline": [
    {
      "$lookup": {
        "from": "fxRates",
        "localField": "currency",
        "foreignField": "currency",
        "as": "fx"
      }
    },
    {
      "$unwind": "$fx"
    },
    {
      "$project": {
        "portfolioId": 1,
        "valuation": 1,
        "currency": 1,
        "rate_to_usd": "$fx.rate_to_usd",
        "valuation_usd": {
          "$multiply": [
            "$valuation",
            "$fx.rate_to_usd"
          ]
        }
      }
    }
  ],
  "cursor": {}
}');

Since I joined a large collection with a small one and require all documents from both, I would anticipate a hash join. Instead, it uses a nested loop join:

Nested Loop  (actual time=579..64792 rows=5000000 loops=1)
  ->  Seq Scan on documents_11 collection  (rows=5000000 loops=1)
  ->  Seq Scan on documents_10 collection_0_1  (rows=1 loops=5000000)
        Filter: bson_dollar_lookup_join_filter(...)
        Rows Removed by Filter: 4
Execution Time: 87750 ms

The fxRates table (5 rows, fitting in a single 8kB block) is scanned 5 million times. PostgreSQL's cost-based optimizer knows the table is tiny and fits in cache, so a sequential scan is the right choice over an index scan — but the scan is still executed 5 million times because of the LATERAL pattern. The filter function bson_dollar_lookup_join_filter is evaluated 25 million times. This function handles array semantics — it extracts the field from the outer document, determines whether it's scalar or an array, and checks for matches in the inner document.

Because the inner side is marked as LATERAL, it depends on the current outer row. This prevents PostgreSQL from evaluating both sides independently, which is required for hash or merge joins. As a result, only a nested loop strategy is possible.

In MongoDB, the equivalent behavior is the IndexedLoopJoin strategy: for each outer document, probe the index on the foreign field. The algorithm and per-document cost are the same.

A note on MongoDB's Hash Join

MongoDB 8.0 can use hash join for $lookup when allowDiskUse: true, no compatible index on the foreign field, the foreign collection is small, and the SBE engine is active. Under these conditions, MongoDB builds an in-memory hash table from the foreign collection, correctly handling array semantics by storing per-element entries.

In tests with 5M portfolios and 5 fxRates, MongoDB's native HashJoin finished in ~14 seconds — the fastest of my tests. Without tweaks, it took 170 seconds — the worst.

To achieve 14 seconds, I dropped the index on the foreign field, enabled allowDiskUse, and set internalQueryFrameworkControl to trySbeEngine. The default trySbeRestricted mode doesn't push the $lookup and $unwind to SBE, since the optimization depends on feature flags that aren't enabled in this mode. With trySbeEngine, SBE handles the pipeline, using HashJoin:

// Setup for hash join
db.adminCommand({setParameter: 1, internalQueryFrameworkControl: "trySbeEngine"});
db.fxRates.dropIndex("currency_1");

// The query (same as all other tests)
db.portfolios.aggregate([
  {$lookup: {from: "fxRates", localField: "currency", foreignField: "currency", as: "fx"}},
  {$unwind: "$fx"},
  {$project: {portfolioId: 1, valuation: 1, currency: 1, rate_to_usd: "$fx.rate_to_usd", valuation_usd: {$multiply: ["$valuation", "$fx.rate_to_usd"]}}}
], {allowDiskUse: true}).explain("executionStats");

// Restore
db.fxRates.createIndex({currency: 1}, {unique: true});
db.adminCommand({setParameter: 1, internalQueryFrameworkControl: "trySbeRestricted"});

DocumentDB for PostgreSQL doesn't currently implement this optimization — it relies on PostgreSQL's native join strategies, which don't understand BSON array semantics. Under normal conditions, both MongoDB and DocumentDB use a Nested Loop join.

Attempting alternatives via the MongoDB API

Using `_id` as Join Key (~71s)

The documentDB extension has a special case when foreignField is _id — it uses direct object_id equality:

// Reshape fxRates to use currency as _id
db.fxRates.drop();
currencies.forEach(cur => {
  db.fxRates.insertOne({
    _id: cur,
    rate_to_usd: Math.random() * (1.5 - 0.5) + 0.5,
    last_updated: new Date()
  });
});

db.portfolios.aggregate([
  {$lookup: {from: "fxRates", localField: "currency", foreignField: "_id", as: "fx"}},
  {$unwind: "$fx"},
  {$project: {portfolioId:1, valuation:1, currency:1,
              rate_to_usd:"$fx.rate_to_usd",
              valuation_usd:{$multiply:["$valuation","$fx.rate_to_usd"]}}}
])

It uses an index scan with the join condition applied as an Index Cond, which is more efficient than a sequential scan with a Filter. It's slightly faster, taking 71 seconds instead of 88 seconds, yet it remains a nested loop with 5 million iterations:

Nested Loop  (actual time=17..48170 rows=5000000 loops=1)
  ->  Seq Scan on documents_11 collection  (rows=5000000 loops=1)
  ->  Index Scan using _id_ on documents_12  (rows=1 loops=5000000)
        Index Cond: (object_id = ANY (bson_dollar_lookup_extract_filter_array(...)))
Execution Time: 70578 ms

This is the same as MongoDB's IndexedLoopJoin — the _id field is guaranteed to be scalar, so the extension can use a direct equality lookup on the primary key. However, it doesn't change the join strategy.

Uncorrelated `$lookup` + `$filter` (~68s)

A minor enhancement involves reading all fxRates at once, using an empty pipeline and no join condition, attaching the data as an array, and then filtering locally:

db.portfolios.aggregate([
  {$lookup: {from: "fxRates", pipeline: [], as: "allFx"}},
  {$addFields: {
    fx: {$arrayElemAt: [{$filter: {
      input: "$allFx", as: "r",
      cond: {$eq: ["$$r.currency", "$currency"]}
    }}, 0]}
  }},
  {$project: {portfolioId:1, valuation:1, currency:1,
              rate_to_usd:"$fx.rate_to_usd",
              valuation_usd:{$multiply:["$valuation","$fx.rate_to_usd"]}}}
])

The execution plan shows a Nested Loop with a single loop:

Nested Loop  (actual time=17..20177 rows=5000000 loops=1)
  ->  Aggregate  (rows=1 loops=1)          -- reads fxRates ONCE
  ->  Seq Scan on documents_11  (rows=5000000 loops=1)
Execution Time: 67905 ms  (of which ~48s is $addFields+$project)

The join itself is fast — fxRates are aggregated once into a single array. But the per-document $filter + $arrayElemAt evaluates BSON expressions 5 million times. We traded "nested loop probe" for "per-row array scan in BSON space".

This is conceptually similar to the "nested loop with materialization" approach from the previous MongoDB article — reading the lookup collection once, but matching per-document in the projection.

Pipeline-Based `$lookup` — No Help

Using $lookup with pipeline and let doesn't enhance performance:

  {$lookup: {
    from: "fxRates",
    let: { cur: "$currency" },
    pipeline: [
      {$match: {$expr: {$eq: ["$currency", "$$cur"]}}}
    ],
    as: "fx"
  }},
  {$unwind: "$fx"},

The extension still creates a LATERAL join (all code paths set rightTree->lateral = true), and it introduces additional overhead due to variable resolution.

With the MongoDB-compatible API, no solution significantly improves the efficiency of the join. But on DocumentDB, the power of SQL opens new possibilities.

The PostgreSQL escape hatch: SQL with Hash Join

Since DocumentDB stores data in standard PostgreSQL tables, we can query the same collections with SQL—within the same transaction and with full ACID guarantees. The trade-off is that we lose flexible-document join semantics and assume scalar join keys.

Prerequisite: enabling Hash Join for the BSON type (a hack)

The bson type has a hash operator class (bson_hash_ops) used for GROUP BY and DISTINCT. But the = operator doesn't declare hash join support — it's missing HASHES and MERGES properties. This is likely intentional, since bson = bson comparison on full documents has different semantics than field-level equality. But for my investigation (comparing extracted scalar field values), it would work:

-- Requires superuser — this is a hack, not a supported configuration
-- If DocumentDB enables this in the future, it will be part of the extension
ALTER OPERATOR documentdb_core.= (documentdb_core.bson, documentdb_core.bson)
  SET (COMMUTATOR = OPERATOR(documentdb_core.=), HASHES, MERGES);

Without this, PostgreSQL cannot execute hash join for bson = bson conditions, even in custom SQL. However, note that the SQL hash join method, enabled by this hack, does not replicate MongoDB's "any element matches" behavior when joined fields include arrays.

The Query: CTE with Hash Join (~39s)

To utilize a SQL join, I first query the two collections within two common table expressions in the WITH clause, then join them in the main query:

WITH portfolios AS (
  SELECT document FROM documentdb_api.collection('test', 'portfolios')
),
fxRates AS (
  SELECT document FROM documentdb_api.collection('test', 'fxRates')
)
SELECT documentdb_api_internal.bson_dollar_project(
  documentdb_api_internal.bson_dollar_merge_documents_at_path(
    p.document, f.document, 'fx'),
  '{ "portfolioId" : 1, "valuation" : 1, "currency" : 1,
     "rate_to_usd" : "$fx.rate_to_usd",
     "valuation_usd" : { "$multiply" : ["$valuation", "$fx.rate_to_usd"] } }'::bson,
  '{}'::bson
)
FROM portfolios p
JOIN fxRates f
  ON documentdb_api_catalog.bson_expression_get(
       p.document, '{"": "$currency"}'::bson, true)
   = documentdb_api_catalog.bson_expression_get(
       f.document, '{"": "$currency"}'::bson, true);

With this query and the operator tweak enabling hash join, I have the following execution plan:

Hash Join  (actual time=7.4..34018 rows=5000000 loops=1)
  Hash Cond: (bson_expression_get(documents_11.document, '{"":"$currency"}'...)
            = bson_expression_get(documents_10.document, '{"":"$currency"}'...))
  ->  Seq Scan on documents_11  (rows=5000000 loops=1)
  ->  Hash  (rows=5 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Seq Scan on documents_10  (rows=5 loops=1)
Execution Time: 38664 ms

PostgreSQL creates a small 5-row hash table (9 kB) and probes it once per portfolio. It makes a single pass over both collections. Most of the remaining time is spent calling bson_expression_get 5 million times to retrieve the join key, along with bson_dollar_merge_documents_at_path and bson_dollar_project to generate the final output.

In the end, this query is only about twice as fast. It requires a complex workaround, breaks document semantics, and still spends most of its time evaluating BSON expressions.

Results Summary

I started Docker containers as:

# Start MongoDB (minimal setup for ACID transactions)
docker exec -it $(
 docker run -d -p 27017:27017 mongo --replSet rs0
) mongosh --eval "rs.initiate()"

# Start DocumentDB (PostgreSQL and MongoDB-compatible endpoints)
docker run -d -p 9712:9712 -p 10260:10260 \
 ghcr.io/documentdb/documentdb/documentdb-local:latest \
 --username "franck" --password "franck" --start-pg

Below is a summary of my experiments, run in Docker containers with default configurations, involving 5 million portfolios, 5 fxRates, and a unique index on fxRates.currency:

Approach	MongoDB	DocumentDB	Strategy
`$lookup` localField/foreignField	~170s	~88s	Nested Loop (lateral index/filter)
`$lookup` with `foreignField: "_id"`	~155s	~71s	Nested Loop (index probe)
Uncorrelated `$lookup` + `$filter`	~22s	~68s	Materialize once + per-doc filter
SQL CTE + Hash Join (operator tweak)	—	~39s	Hash Join (forced)
`HashJoin` (SBE, internal tweak)	~14s	—	Hash Join (forced)

MongoDB's native HashJoin via the Slot-Based Execution engine is fastest, handling hash table build/probe natively with per-element array support and avoiding BSON field extraction overhead, but will not be used without configuration tweaks. The DocumentDB SQL escape hatch uses PostgreSQL's optimizer for the same join strategy but incurs overhead with bson_expression_get on each row.

The other solutions are compatible with standard configurations and use appropriate data models and query code. Remember that the time here reflects reading five million documents, and the difference may be insignificant on small datasets.

The Trade-off: Flexibility vs. Optimization

These experiments show the trade-off clearly. Relational systems rely on joins due to normalization, but they can optimize them effectively thanks to typed scalar columns. Document databases avoid many joins, but when joins are needed, flexible semantics—like arrays—limit the available algorithms.

DocumentDB for PostgreSQL sits in the middle. It relies on PostgreSQL storage and execution while preserving MongoDB semantics. As a result, $lookup uses only a subset of SQL's join capabilities to preserve this flexibility. The SQL workaround shows that performance improves when you enforce scalar semantics, but this runs counter to the expectations of a document model, where any field in one document can be an array in another.

So the real question is not which system is faster, but which trade-off you choose: flexibility with embedded arrays or optimization for scalar values.

This was tested on MongoDB 8.0 and DocumentDB 0.112 on PostgreSQL 17.10, and both can improve in the future. Optimization is possible when the field is a known scalar. But if you have a fixed schema, do you still want a document database or switch to SQL? PostgreSQL can also gain optimizations that benefit DocumentDB queries. For example, the lateral join could be memoized in a future version.

If you're thinking about using DocumentDB for PostgreSQL — whether you're migrating from MongoDB or starting fresh — don't stop at the first slow query. Look into the causes, since the trade-off between speed and flexibility can differ. Check execution plans, and file an issue or start a discussion. More feedback from real workloads helps the contributors improve the extension. That's a major advantage of open source.

Oracle FDW on Azure Database for PostgreSQL

Franck Pachot — Thu, 25 Jun 2026 06:33:37 +0000

When migrating from Oracle Database to PostgreSQL, the Oracle Foreign Data Wrapper enables PostgreSQL to connect to Oracle and import data as if it were a local table. This extension is not available on all managed PostgreSQL services. For Azure Database for PostgreSQL, setup is straightforward since it runs community PostgreSQL. The only requirement is that extensions that you want to use (with CREATE EXTENSION) must first be enabled via the azure-specific parameter azure.extensions so that they are installed and loaded in the managed service:

Oracle_FDW extension in PostgreSQL

I can create the extension and check the version:

postgres=> \du
                                List of roles

   Role name    |                         Attributes
----------------+------------------------------------------------------------
 azure_pg_admin | Cannot login
 azuresu        | Superuser, Create role, Create DB, Replication, Bypass RLS
 franck         | Create role, Create DB, Bypass RLS
 replication    | Replication

postgres=> \connect - franck

I am now connected to the database "postgres" as user "franck".

postgres=> create extension oracle_fdw;

CREATE EXTENSION

postgres=> select oracle_diag();
                                                oracle_diag
------------------------------------------------------------------------------------------------------------
 oracle_fdw 2.8.0, PostgreSQL 18.4, Oracle client 23.26.0.0.0, ORACLE_HOME=/opt/oracle/oracle_instantclient

It's important to understand how PostgreSQL will connect to the Oracle Database: oracle_fdw is installed with the Oracle Instant Client libraries.

An Oracle Database in a lab

I exposed an Oracle database to the internet for lab purposes, so I did not need to configure the network. I set up using the Docker image.


# Start Oracle and expose port 1521 to all interfaces

sudo docker run -d --name ora -p 1521:1521 -e ORACLE_PASSWORD=franck gvenzl/oracle-free:slim

# Open port 1521 in the firewall (you may have to add a rule in the inbound security rules)

sudo firewall-cmd --zone=public --permanent --add-port=1521/tcp
sudo firewall-cmd --reload

# Starting Oracle Database takes time, wait for it to be up

echo "Waiting for Oracle" ; until docker logs ora | 
 grep "Completed: Pluggable database FREEPDB1 opened read write"
do echo -n "." ; sleep 1 ; done

# Load the SCOTT/TIGER schema example

docker exec -it ora bash -c '
TWO_TASK=//localhost:1521/FREEPDB1 sqlplus system/franck @?/rdbms/admin/utlsampl.sql
'

# Allow SCOTT to EXPLAIN PLAN

docker exec -it ora bash -c '
 echo "GRANT SELECT_CATALOG_ROLE TO SCOTT;" |  
 TWO_TASK=//localhost:1521/FREEPDB1 sqlplus system/franck
'

# Show the connection string from internet

echo "You can connect as: SCOTT/TIGER@//$(curl -s ifconfig.me):1521/FREEPDB1"

Foreign Data Wrapper server and user

Now, back to Azure Database for PostgreSQL, I can specify the Oracle Database server via the connection string, as well as the user and password I use for connection:


postgres=> create server oracle_server
  foreign data wrapper oracle_fdw
  options (dbserver '//141.145.212.208:1521/FREEPDB1');

CREATE SERVER

postgres=> create user mapping for current_user
  server oracle_server
  options (user 'SCOTT', password 'TIGER');

CREATE USER MAPPING

I import the tables I need to query:


postgres=> IMPORT FOREIGN SCHEMA "SCOTT" 
           FROM SERVER oracle_server INTO public
;

postgres=> \d

             List of relations

 Schema |   Name   |     Type      | Owner
--------+----------+---------------+--------
 public | bonus    | foreign table | franck
 public | dept     | foreign table | franck
 public | emp      | foreign table | franck
 public | salgrade | foreign table | franck

(4 rows)

postgres=> select * from dept;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON

(4 rows)

I can access the tables as if they were local, my PostgreSQL session being connected to the remote Oracle Database.

Performance and execution plan

The execution plan indicates whether the join has been pushed down, a decision made by the query planner:

postgres=> explain (verbose off)
           select * from dept join emp using(deptno)
;
                                                                                QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=30012.50..40190.00 rows=5000 width=206)
   Hash Cond: (dept.deptno = emp.deptno)
   ->  Foreign Scan on dept  (cost=10000.00..20000.00 rows=1000 width=92)
         Oracle query: SELECT /*62b5e8b0d88d7cfb*/ r1."DEPTNO", r1."DNAME", r1."LOC" FROM "SCOTT"."DEPT" r1
   ->  Hash  (cost=20000.00..20000.00 rows=1000 width=116)
         ->  Foreign Scan on emp  (cost=10000.00..20000.00 rows=1000 width=116)
               Oracle query: SELECT /*d6c3774a98386064*/ r2."EMPNO", r2."ENAME", r2."JOB", r2."MGR", r2."HIREDATE", r2."SAL", r2."COMM", r2."DEPTNO" FROM "SCOTT"."EMP" r2

(7 rows)

postgres=> explain (verbose off)
           select * from dept join emp using(deptno)
           where ename='KING'
;

                                                                                                                                 QUERY PLAN                 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=10000.00..10250.00 rows=25 width=206)
   Oracle query: SELECT /*7a68726e19f9c7b7*/ r1."DEPTNO", r1."DNAME", r1."LOC", r2."EMPNO", r2."ENAME", r2."JOB", r2."MGR", r2."HIREDATE", r2."SAL", r2."COMM" FROM ("SCOTT"."DEPT" r1 INNER JOIN "SCOTT"."EMP" r2 ON (r1."DEPTNO" = r2."DEPTNO") AND (r2."ENAME" = 'KING'))
(2 rows)

When using the VERBOSE option, EXPLAIN executes an EXPLAIN PLAN on the remote server. (I granted SELECT_CATALOG_ROLE to prevent the no SELECT privilege on V$SQL error.)

postgres=> explain (verbose on, analyze, costs off)
           select * from dept join emp using(deptno)
           where ename='KING'
;
                                                                                                                                                                                                                                                                                                                                                  QUERY PLAN                 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan (actual time=11.621..11.632 rows=1.00 loops=1)
   Output: dept.deptno, dept.dname, dept.loc, emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal, emp.comm
   Oracle query: SELECT /*7a68726e19f9c7b7*/ r1."DEPTNO", r1."DNAME", r1."LOC", r2."EMPNO", r2."ENAME", r2."JOB", r2."MGR", r2."HIREDATE", r2."SAL", r2."COMM" FROM ("SCOTT"."DEPT" r1 INNER JOIN "SCOTT"."EMP" r2 ON (r1."DEPTNO" = r2."DEPTNO") AND (r2."ENAME" = 'KING'))
   Oracle plan: SELECT STATEMENT
   Oracle plan:   HASH JOIN   (condition "R1"."DEPTNO"="R2"."DEPTNO")
   Oracle plan:     NESTED LOOPS
   Oracle plan:       NESTED LOOPS
   Oracle plan:         STATISTICS COLLECTOR
   Oracle plan:           TABLE ACCESS FULL EMP  (filter "R2"."ENAME"='KING')
   Oracle plan:         INDEX UNIQUE SCAN PK_DEPT (condition "R1"."DEPTNO"="R2"."DEPTNO")
   Oracle plan:       TABLE ACCESS BY INDEX ROWID DEPT
   Oracle plan:     TABLE ACCESS FULL DEPT
 Query Identifier: 3032555283609452836
 Planning Time: 35.288 ms
 Execution Time: 11.704 ms

(15 rows)

An EXPLAIN ANALYZE displays the timing of remote calls, which here is approximately 12 milliseconds:

This 12ms latency is expected because my PostgreSQL database is located in Amsterdam (Azure West Europe), while the Oracle Database is in Paris (OCI eu-paris-1).

Network troubleshooting

I can verify the location using the TCP/IP information in the Oracle Database listener.

docker exec -it ora bash -c 'curl -s ipinfo.io ; echo ; echo ; grep "PROGRAM=postgres" /opt/oracle/diag/tnslsnr/*/listener/trace/listener.log | tail -1'

{
  "ip": "141.145.212.208",
  "city": "Paris",
  "region": "Île-de-France",
  "country": "FR",
  "loc": "48.8534,2.3488",
  "org": "AS31898 Oracle Corporation",
  "postal": "75000",
  "timezone": "Europe/Paris",
  "readme": "https://ipinfo.io/missingauth"
}

24-JUN-2026 21:28:23:647 * (CONNECT_DATA=(SERVICE_NAME=FREEPDB1)(CID=(PROGRAM=postgres:?franck?postgres?152.67.90.237?35830??SELECT)(HOST=fea1c65ae6cf)(USER=azuredb))(CONNECTION_ID=VQdZFJxbFlngYwQBIQob2g==))(TARGET_LOCAL_INSTANCE=FREE) * (ADDRESS=(PROTOCOL=tcp)(HOST=51.124.207.22)(PORT=1024)) * establish * FREEPDB1 * 0

The IP address embedded in PROGRAM reflects the PostgreSQL client process name, not Oracle network metadata (I used psql in Zurich). The IP in HOST indicates the PostgreSQL client connecting to the Oracle listener, while the first IP from ipinfo.io shows the location of the Oracle Database host.

A latency of 12ms is expected between these two cloud regions:

If I need to troubleshoot, I can run tcpdump -i any port 1521 -nn on the Oracle Database host:

sudo tcpdump -i any port 1521 -nn -tttt -vvv

dropped privs to tcpdump
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on any, link-type LINUX_SLL (Linux cooked v1), capture size 262144 bytes

2026-06-24 22:01:44.583852 IP 51.124.207.22.1024 > 10.0.0.59.1521: Flags [S], seq 987172167, win 65535, options [mss 1440,sackOK,TS val 2030652947 ecr 0,nop,wscale 9], length 0
2026-06-24 22:01:44.583952 IP 51.124.207.22.1024 > 172.17.0.2.1521: Flags [S], seq 987172167, win 65535, options [mss 1440,sackOK,TS val 2030652947 ecr 0,nop,wscale 9], length 0
2026-06-24 22:01:44.583960 IP 51.124.207.22.1024 > 172.17.0.2.1521: Flags [S], seq 987172167, win 65535, options [mss 1440,sackOK,TS val 2030652947 ecr 0,nop,wscale 9], length 0
2026-06-24 22:01:44.583988 IP 172.17.0.2.1521 > 51.124.207.22.1024: Flags [S.], seq 944233141, ack 987172168, win 65160, options [mss 1460,sackOK,TS val 2168564814 ecr 2030652947,nop,wscale 7], length 0
2026-06-24 22:01:44.583996 IP 172.17.0.2.1521 > 51.124.207.22.1024: Flags [S.], seq 944233141, ack 987172168, win 65160, options [mss 1460,sackOK,TS val 2168564814 ecr 2030652947,nop,wscale 7], length 0
2026-06-24 22:01:44.584048 IP 10.0.0.59.1521 > 51.124.207.22.1024: Flags [S.], seq 944233141, ack 987172168, win 65160, options [mss 1460,sackOK,TS val 2168564814 ecr 2030652947,nop,wscale 7], length 0
2026-06-24 22:01:44.602184 IP 51.124.207.22.1024 > 10.0.0.59.1521: Flags [.], ack 1, win 128, options [nop,nop,TS val 2030652960 ecr 2168564814], length 0
2026-06-24 22:01:44.602200 IP 51.124.207.22.1024 > 10.0.0.59.1521: Flags [P.], seq 1:75, ack 1, win 128, options [nop,nop,TS val 2030652960 ecr 2168564814], length 74
2026-06-24 22:01:44.602201 IP 51.124.207.22.1024 > 10.0.0.59.1521: Flags [P.], seq 75:336, ack 1, win 128, options [nop,nop,TS val 2030652960 ecr 2168564814], length 261
2026-06-24 22:01:44.602241 IP 51.124.207.22.1024 > 172.17.0.2.1521: Flags [.], ack 1, win 128, options [nop,nop,TS val 2030652960 ecr 2168564814], length 0
2026-06-24 22:01:44.602249 IP 51.124.207.22.1024 > 172.17.0.2.1521: Flags [.], ack 1, win 128, options [nop,nop,TS val 2030652960 ecr 2168564814], length 0
2026-06-24 22:01:44.602252 IP 51.124.207.22.1024 > 172.17.0.2.1521: Flags [P.], seq 1:75, ack 1, win 128, options [nop,nop,TS val 2030652960 ecr 2168564814], length 74
2026-06-24 22:01:44.602253 IP 51.124.207.22.1024 > 172.17.0.2.1521: Flags [P.], seq 1:75, ack 1, win 128, options [nop,nop,TS val 2030652960 ecr 2168564814], length 74
2026-06-24 22:01:44.602254 IP 51.124.207.22.1024 > 172.17.0.2.1521: Flags [P.], seq 75:336, ack 1, win 128, options [nop,nop,TS val 2030652960 ecr 2168564814], length 261
2026-06-24 22:01:44.602255 IP 51.124.207.22.1024 > 172.17.0.2.1521: Flags [P.], seq 75:336, ack 1, win 128, options [nop,nop,TS val 2030652960 ecr 2168564814], length 261
2026-06-24 22:01:44.602295 IP 172.17.0.2.1521 > 51.124.207.22.1024: Flags [.], ack 75, win 509, options [nop,nop,TS val 2168564833 ecr 2030652960], length 0
2026-06-24 22:01:44.602303 IP 172.17.0.2.1521 > 51.124.207.22.1024: Flags [.], ack 75, win 509, options [nop,nop,TS val 2168564833 ecr 2030652960], length 0
2026-06-24 22:01:44.602312 IP 10.0.0.59.1521 > 51.124.207.22.1024: Flags [.], ack 75, win 509, options [nop,nop,TS val 2168564833 ecr 2030652960], length 0
2026-06-24 22:01:44.602435 IP 172.17.0.2.1521 > 51.124.207.22.1024: Flags [.], ack 336, win 507, options [nop,nop,TS val 2168564833 ecr 2030652960], length 0
2026-06-24 22:01:44.602446 IP 172.17.0.2.1521 > 51.124.207.22.1024: Flags [.], ack 336, win 507, options [nop,nop,TS val 2168564833 ecr 2030652960], length 0
2026-06-24 22:01:44.602462 IP 10.0.0.59.1521 > 51.124.207.22.1024: Flags [.], ack 336, win 507, options [nop,nop,TS val 2168564833 ecr 2030652960], length 0
2026-06-24 22:01:44.619442 IP 172.17.0.2.1521 > 51.124.207.22.1024: Flags [P.], seq 1:62, ack 336, win 507, options [nop,nop,TS val 2168564850 ecr 2030652960], length 61
2026-06-24 22:01:44.619470 IP 172.17.0.2.1521 > 51.124.207.22.1024: Flags [P.], seq 1:62, ack 336, win 507, options [nop,nop,TS val 2168564850 ecr 2030652960], length 61
2026-06-24 22:01:44.619493 IP 10.0.0.59.1521 > 51.124.207.22.1024: Flags [P.], seq 1:62, ack 336, win 507, options [nop,nop,TS val 2168564850 ecr 2030652960], length 61
2026-06-24 22:01:44.630863 IP 51.124.207.22.1024 > 10.0.0.59.1521: Flags [.], ack 62, win 128, options [nop,nop,TS val 2030652994 ecr 2168564850], length 0

Duplicate packets originating from 172.17.0.2 are from Docker networking and are captured on both the host and container bridges. This marks the start of a TCP connection from Azure Database for PostgreSQL to Oracle on port 1521, beginning with the TCP handshake. PostgreSQL then sends two TNS packets to Oracle, which acknowledges both. Oracle responds with a 61-byte payload. TCP flags include [S] (connection setup), [S.] (SYN+ACK), [.] (ACK), and [P.] (PSH + ACK). The absence of the U flag indicates Oracle Net isn't using TCP urgent data, resolving previous OOB issues with older clients.

Production connection string

Running the Oracle Database in a lab helps with troubleshooting, but the database you want to migrate is not publicly accessible, and the connection string may be a bit more complex. As the Oracle client is on a managed service, you cannot add files such as tnsnames.ora or an mTLS wallet. I tested with an Oracle Autonomous database.

You need to enable One-Way TLS (encryption without a wallet) by setting Mutual TLS (mTLS) authentication to not required, and add the IP address of the PostgreSQL database to the allow list:

I got this address from the listener log of my previous test.

Then I got the TLS connection string for the Oracle Autonomous database and used it to define the foreign data wrapper server:

create server oracle_autonomous
  foreign data wrapper oracle_fdw
  options (dbserver '(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.eu-madrid-1.oraclecloud.com))(connect_data=(service_name=g230b6cc64a62e6_mad_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))');

create user mapping for current_user
  server oracle_autonomous
  options (user 'ADMIN', password '4ut0n0m0u5_Password');

After specifying the connection string and credentials, you can read or import tables from Oracle Autonomous into Azure Database for PostgreSQL.

Conclusion

Oracle FDW allows querying an Oracle database from PostgreSQL with minimal setup. On Azure Database for PostgreSQL, the only extra step is to enable the extension. Otherwise, it functions as standard PostgreSQL. This feature is useful for migrations to import or compare data between the two databases. Queries are straightforward, and the planner decides what runs remotely. Performance depends on pushdown to Oracle and network latency. Filters and joins may run remotely or locally, and if pushdown isn’t possible, large result sets may be transferred and joined locally. All this is visible from the execution plan.

Connecting to Oracle can sometimes be challenging. Make sure to run oracle_diag() and verify that both oracle_fdw and the Oracle client are current and compatible with your Oracle database, that the port is opened on the network between the two databases, and that you can connect with a connection string without additional files.

Vector Search with Filters: pgvector vs DiskANN on HorizonDB

Franck Pachot — Mon, 22 Jun 2026 09:04:45 +0000

In a previous post, I explained that using filtering with pgvector can decrease recall in approximate nearest neighbor (ANN) searches.

This post repeats the same experiment and dataset but compares two methods:

pgvector with HNSW, a popular PostgreSQL extension
HorizonDB with DiskANN, Microsoft’s vector index

The goal is to understand what happens when similarity search is combined with filtering.

Both setups were tested on a HorizonDB instance with 2 vCores and 16 GiB RAM in Azure public preview, where I activated the extensions by adding them to azure.extensions.

In real applications, SQL statements without a WHERE clause are rare, and typical queries combine a filter alongside a similarity search:

SELECT *
 FROM embeddings
 WHERE tenant_id = 42
 ORDER BY embedding <=> :vector_query
 LIMIT 10;

You don’t search the entire dataset. Instead, you focus on a specific subset, such as one tenant, recent data, or a category.

Experiment Setup

As in the previous post, I inserted 2 million rows of 512‑dimensional vectors with a simple metadata column (color, evenly distributed):

CREATE TABLE embeddings_table (
  id bigserial PRIMARY KEY,
  color text,
  embedding vector(512)
);

I generated random vectors to insert the embeddings, and three values for color:


create function random_embedding(dimensions int) returns vector as
$$
select
    array(
        select random()::real
        from generate_series(1, dimensions)
    )::vector
$$ language sql;

insert into embeddings_table (embedding,color)
 select random_embedding(512)
  ,(array['red', 'green', 'blue'])[generate_series(1, 2000000)%3+1]
;

This uniform distribution is intentional: it isolates the effect of filtering.

pgvector (HNSW)

Here's the HNSW index that I created in my previous post:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE INDEX ON embeddings_table 
 USING hnsw (embedding vector_cosine_ops)
;

It can take some time. Here is the query I use to monitor progress:

SELECT
  command,
  phase,
  CASE WHEN blocks_total > 0 THEN round(100.0 * blocks_done / blocks_total, 1) ELSE 0 END AS blocks_pct,
  CASE WHEN tuples_total > 0 THEN round(100.0 * tuples_done / tuples_total, 1) ELSE 0 END AS tuples_pct,
  relid::regclass AS table_name,
  index_relid::regclass AS index_name
FROM pg_stat_progress_create_index
;

I might have raised the maintenance_work_mem as indicated by the NOTICE after index creation — it took more than one hour.


NOTICE: hnsw graph no longer fits into maintenance_work_mem after 195123 tuples

CREATE INDEX

Total execution time: 01:20:03.861

I generate another embedding for my query, which I'll store as a psql variable with \gset:

select random_embedding(512) as query
\gset

Without index - Seq Scan and Exact Nearest Neighbor Search

I checked the behavior before the index is created, without using a vector index:

--EXPLAIN (ANALYZE, BUFFERS, VERBOSE off, COSTS off)
SELECT id , color, embedding <=> :'query' nn_cosine
 FROM embeddings_table 
 WHERE color = 'red'
 ORDER BY embedding <=> :'query'
 LIMIT 15
;

Without a vector index, pgvector performs an exact nearest neighbors (ENN) search:

postgres=> SELECT id , color, embedding <=> :'query' nn_cosine
 FROM embeddings_table
 WHERE color = 'red'
 ORDER BY embedding <=> :'query'
 LIMIT 15
;

   id    | color |      nn_cosine
---------+-------+---------------------
 1370302 | red   |  0.2639440793770883
 1048027 | red   | 0.23483715071295053
  804529 | red   | 0.24173628441472828
 1013860 | red   | 0.25945607630683887
 1011571 | red   | 0.23552944123421315
 1514713 | red   |  0.2324248794732996
 1411876 | red   | 0.21627272961247324
 1743091 | red   | 0.25835400937319386
  494461 | red   | 0.23541082932461943
 2128600 | red   |  0.2531862056756401
 2118649 | red   | 0.27055299026445434
 2151097 | red   | 0.25835806753276225
 1988278 | red   |  0.2341769026974998
 1259950 | red   | 0.21806901991480443
 1553203 | red   | 0.24358174125683563
(15 rows)

The execution plan indicates that the color = 'red' filter was applied to the scan result, and the distance was computed from the filtered data to sort and return the top-15.

 Limit (actual time=4088.254..4164.226 rows=15 loops=1)
   Buffers: shared hit=5348519
   ->  Gather Merge (actual time=4088.252..4164.222 rows=15 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=5348519
         ->  Sort (actual time=4058.895..4058.897 rows=11 loops=3)
               Sort Key: ((embedding <=> '[0.51098114,0.8720485,0.8568168,...,0.110820755]'::vector))
               Sort Method: top-N heapsort  Memory: 26kB
               Buffers: shared hit=5348519
               Worker 0:  Sort Method: top-N heapsort  Memory: 26kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 26kB
               ->  Parallel Seq Scan on embeddings_table (actual time=0.127..3949.246 rows=222222 loops=3)
                     Filter: (color = 'red'::text)
                     Rows Removed by Filter: 444445
                     Buffers: shared hit=5348445
 Planning Time: 0.078 ms
 Execution Time: 4164.252 ms
(18 rows)

This approach works when the filter is sufficiently selective to enable distance calculations and sorting on a small set of rows. However, with larger or expanding data sets, a quick neighbor search requires an index that can efficiently find neighbors.

With HNSW index - Approximate Nearest Neighbor Index Scan

Once the index is created, the query runs faster, but the result, compared to the previous execution, is incomplete:

postgres=> SELECT id , color, embedding <=> :'query' nn_cosine
 FROM embeddings_table
 WHERE color = 'red'
 ORDER BY embedding <=> :'query'
 LIMIT 15
;
   id    | color |      nn_cosine
---------+-------+---------------------
 1259950 | red   | 0.21806901991480443
  744973 | red   | 0.21565428393281894
  550210 | red   |  0.2085433809181183
 1860280 | red   | 0.24081963623331726
 1794061 | red   | 0.22253304456085543
 1056052 | red   |   0.232418043420476
 1247836 | red   | 0.22905966001837208
 1128376 | red   | 0.24222950580938118
  847906 | red   | 0.24276414827559645
 1782067 | red   |   0.233356563767256
 2023984 | red   | 0.24263831933162294
 1438981 | red   | 0.21360709574533177
(12 rows)

This is what happened:

The HNSW index retrieves a fixed number of candidates
PostgreSQL applies the WHERE filter afterward
Only matching rows are returned, which can result in fewer rows than requested

For example, if the index examines about 40 candidates (ef_search = 40, the default) and the filter matches one third of the rows, we would expect about 13 matching rows on average. This is approximate because the index examines a local region of the vector space, where the metadata distribution may differ from the global distribution.

In this execution plan, 40 candidates were visited (12 returned, 28 filtered out), but only 12 matched the predicate, falling short of the query's LIMIT 15. These candidates are not necessarily the closest neighbors that satisfy the filter. The true nearest neighbors with color = 'red' might lie outside the explored region and were therefore not considered, because the region included other colors.

In this controlled case, where vectors are uniformly distributed, the ANN result is not only smaller but also deviates significantly from the ENN baseline, with very little overlap between the returned rows and the true nearest neighbors in the filtered subset.

In the execution plan, this is evident as the condition on color appears as a Filter applied after the index scan:

 Limit (actual time=2.352..2.496 rows=12 loops=1)
   Buffers: shared hit=1775
   ->  Index Scan using i_hnsw on embeddings_table (actual time=2.351..2.493 rows=12 loops=1)
         Order By: (embedding <=> '[0.51098114,0.8720485,0.8568168,...,0.110820755]'::vector)
         Filter: (color = 'red'::text)
         Rows Removed by Filter: 28
         Buffers: shared hit=1775
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.054 ms
 Execution

The execution plan details show what happened: 12 returned + 28 filtered = 40 candidates, which is ef_search.

HNSW applies filtering after traversal, meaning the graph is searched globally before rows are discarded. This breaks the assumption that the closest neighbors are among the candidates returned by the index, especially when using selective filters, as they often are not.

The mitigations include:

Increasing ef_search. For instance, based on our data distribution, setting hnsw.ef_search = 200 could provide enough candidates so that at least 15 remain after filtering.
Using partial indexes when filtering on a small set of discrete, known values, so each is indexing a specific subset.
Enabling iterative scans to expand the search scope when filtering removes too many candidates. This improves recall but requires multiple scans.

These measures improve recall, though they come with higher costs and require manual calibration because filtering is handled separately from the ANN search.

DiskANN on HorizonDB

Now I create a DiskANN index, designed for filtering during the search:

postgres=> CREATE EXTENSION IF NOT EXISTS pg_diskann;

CREATE EXTENSION

postgres=> CREATE INDEX i_diskann ON embeddings_table
           USING diskann (embedding vector_cosine_ops)
;

CREATE INDEX

Total execution time: 00:42:40.800

In HorizonDB documentation, it is explained that DiskANN enables advanced filtering for combined vector and metadata queries. Depending on selectivity and planner estimates, it can apply filters during traversal or fall back to post-filtering

With DiskANN index - Approximate Nearest Neighbor Index Scan

After creating the index, I run the same query:

postgres=> SELECT id , color, embedding <=> :'query' nn_cosine
 FROM embeddings_table
 WHERE color = 'red'
 ORDER BY embedding <=> :'query'
 LIMIT 15
;

NOTICE:  pg_diskann: Filter selectivity too high (0.3325), skipping filtered vector scan

   id    | color |      nn_cosine
---------+-------+---------------------
 1968337 | red   | 0.24740531343440575
  545257 | red   | 0.22958854707242105
  828484 | red   | 0.24054936837179086
  744973 | red   | 0.21565428393281894
  307819 | red   | 0.23955190885002464
  557251 | red   |  0.2158213914722017
 1860280 | red   | 0.24081963623331726
 1794061 | red   | 0.22253304456085543
 1048324 | red   | 0.24138402291179228
  494461 | red   | 0.23541082932461943
  851149 | red   | 0.26081572380948004
  832813 | red   |  0.2240000332741019
  745516 | red   | 0.24130828891293765
  233791 | red   |  0.2324244758968873
 1247836 | red   | 0.22905966001837208
(15 rows)

The note is interesting: pre-filtering was not used. Still, the result is complete because DiskANN dynamically adapts the number of candidates explored.

This means the filter was not applied during traversal, so the query falls back to post-filtering, as with pgvector. However, unlike HNSW, the number of candidates is not fixed: the engine explores more vectors until enough rows are returned.

In the execution plan, we see that 42 rows were filtered out and 15 were returned, meaning about 57 candidates were explored:

 Limit (actual time=6.607..10.821 rows=15 loops=1)
   Buffers: shared hit=5772
   ->  Index Scan using i_diskann on embeddings_table (actual time=6.606..10.817 rows=15 loops=1)
         Order By: (embedding <=> '[0.51098114,0.8720485,0.8568168,0.3119626,...,0.110820755]'::vector)
         Filter: (color = 'red'::text)
         Rows Removed by Filter: 42
         Buffers: shared hit=5772
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.104 ms
 Execution Time: 10.893 ms
(11 rows)

By varying the LIMIT, we can see that even without pre-filtering, the results are complete and remain fast for common top-n queries:

LIMIT	Rows Removed by Filter	rows=	Buffers	Execution Time
1	1	1	3779	4.215 ms
2	1	2	3790	6.431 ms
5	22	5	4613	8.153 ms
10	29	10	5310	9.841 ms
15	42	15	5722	10.893 ms
100	222	100	15364	39.989 ms
1000	1879	1000	91585	826.303 ms
10000	20202	10000	602407	27827.608 ms

This distinction is crucial: pgvector uses a fixed search budget (ef_search), whereas DiskANN adjusts the number of explored candidates. Even with post-filtering, it can provide enough rows without manual tuning and without incremental scans.

Highly selective query - Exact Nearest Neighbor

If the filter is highly selective, there is no need to use an approximate search:

postgres=> SELECT id , color, embedding <=> :'query' nn_cosine
 FROM embeddings_table
 WHERE color = 'red' and id = 494461
 ORDER BY embedding <=> :'query'
 LIMIT 15
;

NOTICE:  pg_diskann: Filter selectivity too low (0.0000), using filter-only scan

   id   | color |      nn_cosine
--------+-------+---------------------
 494461 | red   | 0.23541082932461943
(1 row)

The execution plan shows that it didn't use the vector index but instead used the B-tree index on id with an additional Sort for exact nearest-neighbor ordering:

 Limit (actual time=0.038..0.039 rows=1 loops=1)
   Buffers: shared hit=12
   ->  Sort (actual time=0.037..0.038 rows=1 loops=1)
         Sort Key: ((embedding <=> '[0.51098114,0.8720485,0.8568168,0.311962,...,0.110820755]'::vector))
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=12
         ->  Index Scan using embeddings_table_pkey on embeddings_table (actual time=0.033..0.034 rows=1 loops=1)
               Index Cond: (id = 494461)
               Filter: (color = 'red'::text)
               Buffers: shared hit=12
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.140 ms
 Execution Time: 0.053 ms
(14 rows)

DiskANN doesn’t even participate when the filtering is highly selective. The planner uses a B-tree index and bypasses ANN.

With DiskANN index - Pre-filtered Approximate Nearest Neighbor

When filters are selective enough to be applied during the scan but not enough to avoid ANN search, there's no NOTICE:

postgres=> SELECT id , color, embedding <=> :'query' nn_cosine
 FROM embeddings_table
 WHERE color = 'red' and id <= 494461
 ORDER BY embedding <=> :'query'
 LIMIT 15
;

   id   | color |      nn_cosine
--------+-------+---------------------
 418054 | red   | 0.21522295998337881
 233791 | red   |  0.2324244758968873
 377470 | red   | 0.23508242287803505
 196009 | red   |  0.2356772244494152
 225253 | red   | 0.24119501226879858
 233026 | red   | 0.23055577367239388
 308998 | red   | 0.21652541614338927
 291283 | red   |  0.2319492505072145
 459442 | red   |  0.2448930593940325
 296956 | red   | 0.21969539585363562
 223876 | red   | 0.24664282569002072
 385669 | red   | 0.21339531139470636
 390397 | red   |  0.2484855378229266
 296467 | red   | 0.22426404565684388
 220285 | red   |  0.2179154018451408
(15 rows)

This is where the execution plan displays a Custom Scan (DiskANNFilteredScan), enabled by PostgreSQL's extensibility. It performs pre-filtering (Filter(IndexScan)) and Approximate Nearest Neighbor search (Vector) to yield the precise number of candidates in the filtered set (Rows Retrieved: 15 count):

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Limit (actual time=25.842..28.876 rows=15 loops=1)
   Buffers: shared hit=11905
   ->  Custom Scan (DiskANNFilteredScan) (actual time=25.841..28.872 rows=15 loops=1)
         Strategy: Filter(IndexScan) -> Vector
         Rows Retrieved: 15 count
         TIDs Collected: 303062 count
         Buffers: shared hit=11905
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.091 ms
 Execution Time: 28.927 ms
(11 rows)

This plan is an ANN with pre-filtering, effective when both the column filter and the embedding filter are selective and need to be combined for efficient execution. The optimizer makes this decision dynamically.

TIDs Collected indicates the number of rows matching the indexable portion of the filter prior to the vector search. According to the cardinalities, the id condition was used for prefiltering:

postgres=> select count(*) FROM embeddings_table 
           WHERE id <= 494461;
 count
--------
 303063

This represents a selectivity of about 15% for the pre-filtered set. The remaining condition (color = 'red') is applied afterward, reducing the result further to about 5% of the table.

Why this matters

These experiments reveal a different execution model, not just performance differences. With pgvector and HNSW, search is driven by a fixed budget: exploring a set number of candidates (ef_search) with the filter applied afterward. If the filter removes many candidates, fewer rows are returned, lowering recall. Achieving correct results requires tuning (ef_search), enabling iterative scans, or creating dedicated partial indexes.

With DiskANN, there is no fixed number of candidates set. Instead, it adapts based on the query. When the filter isn't sufficiently selective, it even bypasses the filtered ANN, reverting to vector search and post-filtering. Unlike HNSW, the number of candidates explored isn't fixed, allowing the engine to return the desired number of rows.

When the filter is very selective, such as id = constant, the optimizer skips the ANN and instead uses a standard index scan, calculating distances on the smaller result set.

When the filter is moderately selective, the execution model changes. DiskANN incorporates the predicate into traversal with a filtered ANN scan (DiskANNFilteredScan). Search is limited to the filtered subset, retrieving exactly the requested neighbors without exploring irrelevant regions.

DiskANN chooses execution modes based on filter selectivity, adjusting the effort required to return accurate results. Here are the execution strategies observed in this example:

Predicate	Selectivity (approx.)	Strategy	What happens
`color = 'red'`	~33% (>12%)	ANN + post-filter	Filtered ANN is skipped. The engine explores more candidates (~57 to get 15) instead of a fixed budget and returns the expected rows.
`id = constant`	~0% (<1%)	Exact index scan	ANN is bypassed. The primary key index is used, and distances are computed on the small result set.
`color='red' AND id <= ...`	~5% (<12%)	Filtered ANN	The indexable predicate is used to collect a filtered candidate set (`DiskANNFilteredScan`), and ANN runs within that reduced set.

It is also interesting to note that, on the same dataset, the DiskANN index is not significantly larger than the HNSW index:

postgres=> SELECT indexrelid::regclass AS index_name, pg_size_pretty(pg_relation_size(indexrelid)) AS size, am.amname AS access_method
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid JOIN pg_am am ON c.relam = am.oid WHERE indrelid = 'embeddings_table'::regclass;
 index_name |  size   | access_method
------------+---------+---------------
 i_hnsw     | 5208 MB | hnsw
 i_diskann  | 5545 MB | diskann

The behavior observed in the execution plans is actually governed by configuration parameters:

postgres=> \dconfig diskann.*

           List of configuration parameters

              Parameter               |     Value
--------------------------------------+---------------
 diskann.cost_reduced_beta            | 0.8
 diskann.enable_filter_hook           | on
 diskann.enable_legacy_vector_compat  | on
 diskann.enable_neighbor_prefetch     | on
 diskann.filtering_beta               | 0.8
 diskann.iterative_search             | Relaxed_Order
 diskann.l_value_is                   | 100
 diskann.selectivity_min              | 0.01
 diskann.selectivity_threshold        | 0.12
 diskann.skip_prefetch_buffer_divisor | 2
 diskann.skip_prefetch_if_cached      | on
 diskann.verbose_explain              | off
(12 rows)

For example, diskann.selectivity_threshold = 0.12 explains why a filter that matches about one-third of the rows skips the filtered ANN, and the combined predicate (with an estimated selectivity around 5%) is below the threshold. selectivity_min = 0.01 explains why the condition on one row over two million bypasses the ANN entirely. Based on the estimated selectivity, values between those thresholds are eligible for a filtered scan (DiskANNFilteredScan).

Those parameter can be tuned, here is the documentation:

Scalable Vector Indexing with DiskANN - Azure HorizonDB | Microsoft Learn

Use the pg_diskann extension to enable scalable, high-performance vector indexing in Azure HorizonDB for efficient semantic similarity search in large datasets, with advanced filtering for combined vector and metadata queries.

learn.microsoft.com

Conclusion

HNSW requires tuning a parameter (ef_search) per query, and always uses post-filtering, which can lead to incomplete results. DiskANN hides much of this complexity behind the optimizer. It adapts the number of candidates explored, applies filtering during the search when beneficial, and avoids ANN altogether when unnecessary.

This difference matters because real-world applications rarely perform vector searches without filters. Multi-tenancy, time windows, geography, access control, status, language, or category filters all mean that nearest neighbors must be identified within a subset rather than across the entire dataset. Nobody wants to scan index entries belonging to others in multi-tenant environments.

DiskANN uses pgvector semantics—the same data types, operators (<=> for cosine distance, <-> for L2, <#> for inner product), and ORDER BY ... LIMIT syntax—so queries remain the same. Only the index changes to improve performance and recall in Azure database services.

HorizonDB cache hierarchy: RAM, NVMe SSD, and multi-AZ storage behind PostgreSQL

Franck Pachot — Wed, 17 Jun 2026 17:07:10 +0000

HorizonDB extends PostgreSQL with disaggregated storage, making it appear to developers like PostgreSQL while offering cloud-native high availability, elasticity, and performance. Performance metrics, especially I/O, can reveal differences due to additional components such as local caches and remote storage along the read/write paths.

Three aspects can expose these internal workings at the PostgreSQL layer:

Cache utilization, observable via EXPLAIN BUFFERS, comparing shared buffer hits with read calls to retrieve pages.
Average read times from EXPLAIN or metrics help infer the storage layer: less than a microsecond for memory, tens of microseconds for local NVMe SSD cache, and about a millisecond for remote persistent storage.
Wait events, as I/O operations handled outside PostgreSQL's main code path, in extensions, may show different patterns in pg_stat_activity compared to traditional PostgreSQL setups.

This approach is similar to tests I previously performed on OCI and AWS. I insert 100 MB of data (~12,800 rows) into a PostgreSQL table every two minutes and run full table scans with EXPLAIN ANALYZE:


\timing on
-- create the table
drop table if exists large;
create table large ( filler text ) with (fillfactor=10);
-- insert 100 MB
insert into large
 select string_agg( chr((32+random()*94)::int) ,'' )
 from generate_series(1,1000) c, generate_series(1,12800) r
 group by r
\; 
-- check the size
select 'size', pg_table_size('large') , pg_size_pretty( pg_table_size('large') ) , now()
\; 
-- query with full table scan
explain (costs off, analyze, buffers) select * from large
-- repeat every two minutes
\watch i=120 c=1000

I log the execution plans, including the elapsed time for the sequential scan and the number of buffers accessed (shared buffer hits and reads). Some metrics are gathered in Azure, and I also run the PostgreSQL for VS Code extension dashboard, which shows other metrics exposed by PostgreSQL.

It starts with 100% shared buffer hits

The storage grows by 100 MB every two minutes as new inserts are added:

The inserted rows remain constant, but as the table size grows, the read workload increases because more rows are scanned during each execution:

Since this involves a full table scan and the table size grows, it reads an increasing number of blocks, all resulting in shared buffer hits without reading from files, as the inserted pages remain in shared buffers:

The sequential scan in PostgreSQL employs a small ring buffer, but this occurs only on cache misses. Since the pages are already present in the shared buffer from prior insertions, this does not apply here.

This continues, and the table size reaches the shared buffer size.

The inserts fill up the shared buffer (11 GiB)

I run on a HorizonDB instance with 16 GiB RAM, provisioned like in the previous post, and 11 GiB are allocated to the shared buffer:

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

Until I added 11 GB, the sequential scan only involved shared hit buffers and did not perform any reads (1433600 x 8KB blocks equals 11,200 MB):


               Tue 16 Jun 2026 10:33:01 AM GMT (every 120s)

 ?column? | pg_table_size | pg_size_pretty |              now
----------+---------------+----------------+-------------------------------
 size     |   11747319808 | 11 GB          | 2026-06-16 10:33:01.555657+00
(1 row)

            Tue 16 Jun 2026 10:33:01 AM GMT (every 120s)

                             QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on large (actual time=0.005..507.198 rows=1433600 loops=1)
   Buffers: shared hit=1433600 dirtied=12800
 Planning Time: 0.013 ms
 Execution Time: 551.265 ms
(4 rows)

Time: 4724.370 ms (00:04.724)

The next run, with 100 MB more data, begins to show some buffers read (16071 x 8KB = 125MB):

               Tue 16 Jun 2026 10:35:01 AM GMT (every 120s)

 ?column? | pg_table_size | pg_size_pretty |              now
----------+---------------+----------------+-------------------------------
 size     |   11852201984 | 11 GB          | 2026-06-16 10:35:01.555654+00
(1 row)

This is consistent with the newly appended pages, 100 MB, displacing the same amount of older buffers once shared buffers were full.

            Tue 16 Jun 2026 10:35:01 AM GMT (every 120s)

                             QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on large (actual time=0.007..624.128 rows=1446400 loops=1)
   Buffers: shared hit=1430329 read=16071 dirtied=12800 written=753
 Planning Time: 0.022 ms
 Execution Time: 669.210 ms
(4 rows)

Time: 4667.335 ms (00:04.667)

In vanilla PostgreSQL, sequential scans use a bulk-read strategy with a small ring of shared buffers, typically 256KB, to avoid flooding the main shared-buffer cache. With buffered I/O, pages not found in shared buffers may still be served from the operating system page cache, which is the usual double-buffering behavior. In such an experiment, the read (cache misses) increase by 100 MB each time due to buffer evictions from newly inserted data. I observed that on other managed services, asymptotic throughput decayed while cache evictions increased:

In HorizonDB, where the caching layers are different, we do not observe the same gradual decay. After the table scan size exceeded the shared-buffer size, subsequent scans were mostly reported as reads by PostgreSQL, indicating that most older pages were no longer in shared buffers, while only the most recently inserted 100 MB remained as hits. The next run reads the full table via disk reads (1446401 x 8KB = 11300 MB), with only the last 100 MB served from the shared buffer (12799 x 8KB = 100 MB):

               Tue 16 Jun 2026 10:37:01 AM GMT (every 120s)

 ?column? | pg_table_size | pg_size_pretty |              now
----------+---------------+----------------+-------------------------------
 size     |   11957084160 | 11 GB          | 2026-06-16 10:37:01.556217+00
(1 row)

             Tue 16 Jun 2026 10:37:01 AM GMT (every 120s)

                              QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on large (actual time=2.825..82165.728 rows=1459200 loops=1)
   Buffers: shared hit=12799 read=1446401 dirtied=12949
 Planning Time: 0.019 ms
 Execution Time: 82219.965 ms
(4 rows)

Time: 86399.819 ms (01:26.400)

PostgreSQL BUFFERS only shows the shared-buffer view. A read is a miss from shared buffers, but in a disaggregated architecture, it may still be served by a local cache before reaching durable remote storage.

Let's do some maths to estimate the storage layer of these buffer reads, considering that most of the per-buffer processing time is dominated by access latency:

Shared buffer hits: reading 11 GB in 507ms results in roughly 0.00038 ms per I/O (~0.38 µs), indicative of RAM access.
Buffer reads: reading 11 GB in 1 minute and 26 seconds translates to 134 MB/s, approximately 16,000 IOPS with 8KB I/Os. Since the query is single-threaded and has no parallelism, the average I/O time is about 0.0568 ms (~57 µs). This reflects reading from the local NVMe SSD cache.

I've highlighted the execution plans at the key inflection point. We can review those metrics throughout the entire run using the VS Code dashboard.

Because this is the only table in the database, the total size increases by 100 MB every two minutes, reaching 11 GB:

As table scans increase, shared buffer hits or reads also rise. When the table size hits 11 GB, all reads are served by storage (read, no longer from shared buffers), replacing the earlier 100% cache hits.

The cache hit ratio shows when it falls below 100%:

The query execution time increases from 500ms with RAM to 1 minute with an SSD:

Here is another EXPLAIN showing when those metrics were taken:

               Tue 16 Jun 2026 10:55:01 AM GMT (every 120s)

 ?column? | pg_table_size | pg_size_pretty |              now
----------+---------------+----------------+-------------------------------
 size     |   12901031936 | 12 GB          | 2026-06-16 10:55:01.555546+00
(1 row)

             Tue 16 Jun 2026 10:55:01 AM GMT (every 120s)

                              QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on large (actual time=0.114..71725.275 rows=1574400 loops=1)
   Buffers: shared hit=12800 read=1561600 dirtied=12800
 Planning Time: 0.022 ms
 Execution Time: 71781.188 ms
(4 rows)

Time: 76089.679 ms (01:16.090)

Reading 12 GB in 1 minute and 12 seconds results in approximately 166 MB/s, corresponding to around 20,000 IOPS with 8 KB I/O operations and an average of 0.046 ms per I/O (~46 microseconds). This aligns with reads served from the local NVMe SSD cache rather than the remote durable storage path.

Even if these reads are not counted as cache hits by PostgreSQL metrics—which only track the shared buffers—they still constitute a highly effective local cache, achieving 10 GB reads in just one minute. This provides faster I/O without requiring larger compute instances.

Next cache level: local NVMe SSD

While the run was ongoing, I observed the query duration:

There were some peaks in response time that temporarily reduced throughput in inserted and fetched rows:

The same is visible from the block reads:

I gathered the execution plans from that period, showing a single run at 11:21 UTC (13:21 CET on the dashboard), with reduced throughput—reading 11 GB over 5 minutes instead of 1 minute.

             Tue 16 Jun 2026 11:17:01 AM GMT (every 120s)

                              QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on large (actual time=0.111..77440.938 rows=1715200 loops=1)
   Buffers: shared hit=14286 read=1700914 dirtied=12800 written=12555
 Planning Time: 0.022 ms
 Execution Time: 77503.429 ms
(4 rows)

Time: 81571.846 ms (01:21.572)

INSERT 0 12800

              Tue 16 Jun 2026 11:19:01 AM GMT (every 120s)

 ?column? | pg_table_size | pg_size_pretty |             now
----------+---------------+----------------+-----------------------------
 size     |   14159659008 | 13 GB          | 2026-06-16 11:19:01.5558+00
(1 row)

             Tue 16 Jun 2026 11:19:01 AM GMT (every 120s)

                              QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on large (actual time=0.109..62897.716 rows=1728000 loops=1)
   Buffers: shared hit=345599 read=1382401
 Planning Time: 0.022 ms
 Execution Time: 62958.979 ms
(4 rows)

Time: 66659.325 ms (01:06.659)

INSERT 0 12800

               Tue 16 Jun 2026 11:21:01 AM GMT (every 120s)

 ?column? | pg_table_size | pg_size_pretty |              now
----------+---------------+----------------+-------------------------------
 size     |   14264598528 | 13 GB          | 2026-06-16 11:21:01.555743+00
(1 row)

              Tue 16 Jun 2026 11:21:01 AM GMT (every 120s)

                               QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on large (actual time=4.852..288105.348 rows=1740800 loops=1)
   Buffers: shared hit=313478 read=1427322 dirtied=12800 written=22539
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.029 ms
 Execution Time: 288179.656 ms
(6 rows)

Time: 292242.942 ms (04:52.243)

INSERT 0 12800

               Tue 16 Jun 2026 11:25:53 AM GMT (every 120s)

 ?column? | pg_table_size | pg_size_pretty |              now
----------+---------------+----------------+-------------------------------
 size     |   14369480704 | 13 GB          | 2026-06-16 11:25:53.798728+00
(1 row)

              Tue 16 Jun 2026 11:25:53 AM GMT (every 120s)

                               QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on large (actual time=30.632..87584.215 rows=1753600 loops=1)
   Buffers: shared hit=14238 read=1739362 dirtied=12800 written=11000
 Planning Time: 0.015 ms
 Execution Time: 87647.389 ms
(4 rows)

Time: 91315.980 ms (01:31.316)

Doing the maths, 1,427,322 reads in 288,105 milliseconds yields an average of roughly 288,105 ms / 1,427,322 ≈ 0.202 ms per read. This indicates that the scan is no longer fully served by the fastest local SSD-cache path, and some reads fall through to a slower storage path.

I reviewed the HorizonDB compute instance metrics in the Azure portal. The inserted and returned tuples align with our observations. Variations are expected due to the bursty workload: the query executes for about a minute, then pauses for two minutes, causing the 10-second metric buckets to capture the active rate during execution and near-zero during idle times.

The pattern shifts when the query time matches the job's 2-minute wait time. Here is the query time:

The memory usage has been rising until the shared buffers reached their allocated size (Linux allocates lazily):

Finally, it is interesting to observe the network I/O from the compute instance, which relates to the read and write operations to the storage:

The slower run can be explained by some reads taking a lower cache tier or being delayed by contention/throttling. The correlation with network activity makes remote storage involvement plausible, possibly due to SSD cache misses.

Returning to the VS Code dashboard, we can also observe the statistics about vacuum, checkpoint, and WAL:

On the dashboard, the average active-session timeline is visible, along with wait-event names for reads. These suggest that reads are handled by an extension rather than the PostgreSQL core code, as they appear as HorizonDB_SSDCache_Read wait events under the Extension wait type.

Some observations of HorizonDB_Storage_Read indicate longer read times and increased network activity.

Conclusion

These experiments show that HorizonDB behaves like PostgreSQL at the SQL layer while exposing the effects of a multi-tier storage architecture through latency, throughput, and wait events.

With a monotonically increasing table size scanned, we have observed the following phases:

Phase	PostgreSQL view	Effective per 8 KiB block	HorizonDB reality
Fits in shared buffers	`shared hit`	~0.35–0.38 µs	PostgreSQL shared-buffer residency
After shared-buffer overflow, normal runs	mostly `read`	~46–57 µs	Local low-latency cache below PostgreSQL
“Slower” peaks	mostly `read`	~0.20 ms	Slower storage path, including remote-storage reads

The configuration (large shared_buffers and effective_cache_size equal to it) shows that HorizonDB minimizes reliance on the OS page cache and instead uses a multi-tier caching strategy, with a fast NVMe SSD in the stateless compute node, to reduce reads from durable remote storage.

From PostgreSQL’s point of view, the scan is still a normal sequential scan, and BUFFERS still reports only the PostgreSQL shared-buffer state. When the table fits in shared buffers, the scan is served as shared hits and completes in a few hundred milliseconds. Once the table grows beyond shared buffers, PostgreSQL reports mostly read buffers. In a disaggregated storage architecture, those reads do not necessarily mean cold remote storage. They may be served from a lower cache tier beneath PostgreSQL.

The effective per-block timings show three distinct regimes in this test: sub-microsecond effective access times when pages are already in shared buffers, tens of microseconds per PostgreSQL block during normal post-overflow scans, and occasional slower periods of a few tenths of a millisecond per block. These slower periods correlate with longer query times and increased network activity, which makes lower-tier reads or storage-path delays plausible, although this experiment alone cannot distinguish cache misses from contention, throttling, or other synchronization effects.

The important lesson is that PostgreSQL cache-hit ratio alone is not enough to understand performance in a disaggregated system. A drop from shared hit to read may look dramatic in PostgreSQL metrics, but it means “not in shared buffers”, not necessarily “read from remote durable storage” in HorizonDB. The observed latency and wait events indicate an additional cache tier below PostgreSQL, where normal reads are served from a fast local NVMe SSD cache, while slower peaks use a slower storage path.

HorizonDB is in preview on Azure, so those metrics may improve, and your feedback is welcome.

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.

DEV Community: Franck Pachot

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

PostgreSQL and RUM

PostgreSQL and B-tree

DocumentDB collection

Index Only Scan

Extended RUM index

MongoDB-compatible multi-key index performance

Indexing flexible documents

Testing other MongoDB emulations

Conclusion

kernel asynchronous reads in PostgreSQL 19 (io_uring)

Conclusion

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

IO combining and prefetch

PostgreSQL 19 (beta)

AIO configuration

Seq Scan on small rows table (inline)

Seq Scan on oversized rows table (TOASTed)

eBPF (block layer)

Conclusion

Hybrid Search (Full-Text and Vector Similarity) in HorizonDB

Setup

For the demo: a deterministic embedding function

Table with text and embeddings

Create the indexes (BM25 and DiskANN)

Query 1: BM25 only

Query 2: ANN only

Query 3: BM25 first, then vector search

Query 4: ANN first, then text search

Query 5: hybrid search with RRF

Conclusion

Azure AI on HorizonDB (Microsoft Foundry Azure OpenAI from SQL)

1. Allow and install the extension

2. Deploy a model in Azure

3. Register the model in PostgreSQL

4. Use the model from SQL queries

Generate

Validate

Extract

Search

Rank

Models

Conclusion

$lookup join strategies: understanding the trade-offs with flexible documents

An example: fact and reference table

Schema and data generation

The Query with $lookup

Why flexible documents make joins hard

What happens under the hood (DocumentDB for PostgreSQL)

A note on MongoDB's Hash Join

Attempting alternatives via the MongoDB API

Using _id as Join Key (~71s)

Uncorrelated $lookup + $filter (~68s)

Pipeline-Based $lookup — No Help

The PostgreSQL escape hatch: SQL with Hash Join

Prerequisite: enabling Hash Join for the BSON type (a hack)

The Query: CTE with Hash Join (~39s)

Results Summary

The Trade-off: Flexibility vs. Optimization

Oracle FDW on Azure Database for PostgreSQL

Oracle_FDW extension in PostgreSQL

An Oracle Database in a lab

Foreign Data Wrapper server and user

Performance and execution plan

Network troubleshooting

Production connection string

Conclusion

Vector Search with Filters: pgvector vs DiskANN on HorizonDB

Experiment Setup

pgvector (HNSW)

Without index - Seq Scan and Exact Nearest Neighbor Search

With HNSW index - Approximate Nearest Neighbor Index Scan

DiskANN on HorizonDB

With DiskANN index - Approximate Nearest Neighbor Index Scan

Highly selective query - Exact Nearest Neighbor

With DiskANN index - Pre-filtered Approximate Nearest Neighbor

Why this matters

Scalable Vector Indexing with DiskANN - Azure HorizonDB | Microsoft Learn

Conclusion

The Query with `$lookup`

Using `_id` as Join Key (~71s)

Uncorrelated `$lookup` + `$filter` (~68s)

Pipeline-Based `$lookup` — No Help