DEV Community: Franck Pachot

The FETCH FIRST story: from ROW_NUMBER() in 12c back to ROWNUM in 23ai, and a LIMIT equivalent for PostgreSQL

Franck Pachot — Sun, 04 Oct 2026 22:36:44 +0000

The FETCH FIRST ... ROWS ONLY clause arrived in the SQL standard with SQL:2008, and Oracle Database implemented it in 12cR1, released in June 2013.

Before that, there were two common ways to write a Top-N query, both requiring a subquery:

Order the rows first, then apply ROWNUM outside:

SELECT *
FROM (
  SELECT ...
  FROM ...
  ORDER BY ...
)
WHERE ROWNUM <= 42;

Calculate an analytic ROW_NUMBER(), then filter its result outside:

SELECT *
FROM (
  SELECT ...,
         ROW_NUMBER() OVER (ORDER BY ...) AS rn
  FROM ...
)
WHERE rn <= 42;

The first subquery is necessary because the ordering must happen before the ROWNUM filter. The second is necessary because the analytic function cannot be evaluated in the same query block’s WHERE clause.

With FETCH FIRST, we can express the intention directly:

SELECT ...
FROM ...
ORDER BY ...
FETCH FIRST 42 ROWS ONLY;

However, a simpler SQL statement does not necessarily mean a simpler implementation.

First, a transformation to ROW_NUMBER

Like many additions to Oracle’s SQL syntax, FETCH FIRST was implemented through a transformation to existing constructs. Oracle initially chose the analytic ROW_NUMBER() solution.

This was the familiar rewrite in 12c, 18c, 19c, 21c, and early 23c releases. In the recent 23ai and 26ai releases tested here, the simple FETCH FIRST n ROWS ONLY case is rewritten using ROWNUM.

“AI” is not responsible for the change. Oracle 18c and 19c belong to the 12cR2 release family, with names reflecting the release year. Similarly, 26ai is still in the 23 release family, and “ai” replaced “c” with the 23.4 release.

Beyond the marketing names, the relevant change is fix control 35915968:

SQL> SELECT bugno, description, optimizer_feature_enable
     FROM v$system_fix_control
     WHERE bugno = 35915968;

     BUGNO DESCRIPTION                                           OPTIMIZER_FEATURE_ENABLE
---------- ----------------------------------------------------- ------------------------
  35915968 fetch first transformation using rownum                23.1.0

The OPTIMIZER_FEATURE_ENABLE column tells us the optimizer compatibility setting associated with the control.

There is another clue in ORACLE_HOME. In the 26ai installation used for this investigation, rdbms/admin/bundlefcp_DBBP.xml lists this control under the 23.4.0.0.0 bundle:

<bug id="35915968">
  <fix_control default_value="1">35915968</fix_control>
</bug>

This is more useful than simply saying “23ai changed it.”

The first problem was costing

Choosing ROW_NUMBER() rather than ROWNUM already had a consequence in 12cR1: the optimizer did not apply the same first-k-row optimization.

I blogged about this in 2014: ROWNUM vs ROW_NUMBER() and 12c fetch first.

With ROWNUM <= 10, Oracle knew that it needed only the first ten rows and could cost an ordered index access accordingly. With the analytic rewrite, it could cost the access as if many more rows were needed, making a full scan and sort look preferable.

My recommendation was to add FIRST_ROWS(n), not the old FIRST_ROWS hint.

This was improved in 19c:

SQL> SELECT bugno, description, optimizer_feature_enable
     FROM v$system_fix_control
     WHERE bugno = 22174392;

     BUGNO DESCRIPTION                                                      OPTIMIZER_FEATURE_ENABLE
---------- ---------------------------------------------------------------- ------------------------
  22174392 first k row optimization for window function rownum predicate    19.1.0

I blogged about it in 2020: 19c: scalable Top-N queries without further hints to the query planner.

The improvement is visible in the execution plan as WINDOW NOSORT STOPKEY, the stopkey replacing the simple analytic filter WINDOW SORT PUSHED RANK, so that Oracle could cost the analytic Top-N query with the first-k-row objective and choose that access without the additional hint.

When an index supplies the required order, Oracle can stop early. Without an ordered access path, a sort may still be necessary.

Now, back to ROWNUM

The first-k-row is not the only difference between ROWNUM and the analytic function. ROWNUM was also acts as a non-mergeable view barrier, forcing the optimizer to treat the query block as an isolated inline view.

For the simple row limit tested here, Oracle 26ai now goes back to the other implementation:

SQL> EXPLAIN PLAN FOR
     SELECT *
     FROM dual
     ORDER BY dummy
     FETCH FIRST 42 ROWS ONLY;

SQL> SELECT *
     FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC +PREDICATE'));

------------------------------------
| Id  | Operation           | Name |
------------------------------------
|   0 | SELECT STATEMENT    |      |
|*  1 |  COUNT STOPKEY      |      |
|   2 |   VIEW              |      |
|   3 |    TABLE ACCESS FULL| DUAL |
------------------------------------

Predicate Information:
----------------------

   1 - filter(ROWNUM<=42)

The transformation is visible with DBMS_UTILITY.EXPAND_SQL_TEXT:

WITH
  FUNCTION expand_my_sql(p_sql IN VARCHAR2) RETURN CLOB IS
    v_output CLOB;
  BEGIN
    DBMS_UTILITY.EXPAND_SQL_TEXT(
      input_sql_text  => p_sql,
      output_sql_text => v_output
    );
    RETURN v_output;
  END;
SELECT expand_my_sql(
  'SELECT * FROM DUAL ORDER BY DUMMY FETCH FIRST 42 ROWS ONLY'
) AS expanded_sql
/

Formatted for readability, the output is:

SELECT "A1"."DUMMY" "DUMMY"
FROM (
  SELECT "A2"."DUMMY" "DUMMY"
  FROM "SYS"."DUAL" "A2"
  ORDER BY "A2"."DUMMY"
) "A1"
WHERE ROWNUM <= 42

We can restore the earlier transformation on the same database by disabling the change:

ALTER SESSION SET "_fix_control" = '35915968:0';

The expanded SQL becomes:

SELECT "A1"."DUMMY" "DUMMY"
FROM (
  SELECT "A2"."DUMMY" "DUMMY",
         "A2"."DUMMY" "rowlimit_$_0",
         ROW_NUMBER() OVER (
           ORDER BY "A2"."DUMMY"
         ) "rowlimit_$$_rownumber"
  FROM "SYS"."DUAL" "A2"
) "A1"
WHERE "A1"."rowlimit_$$_rownumber" <= 42
ORDER BY "A1"."rowlimit_$_0"

And the plan uses the analytic operation again:

---------------------------------------
| Id  | Operation             | Name |
---------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  VIEW                 |      |
|*  2 |   WINDOW NOSORT STOPKEY|      |
|   3 |    TABLE ACCESS FULL  | DUAL |
---------------------------------------

Predicate Information:
----------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=42)
   2 - filter(ROW_NUMBER() OVER (ORDER BY "DUAL"."DUMMY")<=42)

More than a decade after introducing FETCH FIRST, Oracle is using the legacy ROWNUM solution for this case. It benefits from the existing first-k-row optimization and gives the optimizer a well-established row-limit boundary.

This is not a claim that all row-limiting clauses now use ROWNUM. OFFSET, percentages, and WITH TIES have additional semantics. In the same 26ai binary, another control, 35969400, can also select a native ROW LIMIT operation.

But for the case discussed here, going back to ROWNUM also avoids a wrong-results bug.

EXISTS and NOT EXISTS returning a row

A Stack Overflow question showed Oracle returning a row for this query:

SELECT 1 AS one_row
FROM dual
WHERE EXISTS (SELECT * FROM view_abcd)
  AND NOT EXISTS (SELECT * FROM view_abcd)
;

This is a logical contradiction. EXISTS returns true or false depending on whether its subquery returns any rows, regardless of the values in those rows. If one predicate is true, the other must be false.

The view combines OUTER APPLY, an inner ANSI JOIN, and a correlated FETCH FIRST 1 ROW ONLY. The question reports the problem on 19c and 21c.

On Oracle 23ai Free 23.9.0.25.07, the default result is correct. Disabling 35915968 makes it return the wrong row: db<>fiddle.

I also reproduced it on 26ai Free 23.26.3.0.0 with:

ALTER SESSION SET optimizer_features_enable = '21.1.0';

That was a starting point, not the solution. I wanted a finer setting than changing the whole optimizer compatibility level.

Copilot, guided by experience

I used Copilot for this investigation. Not just to ask “what is the answer?”, but to run the experiments I would normally run myself.

Here are some of my prompts:

“Please find the bug published by Oracle.”

“Is it ANSI join? There’s a long history of ANSI joins implemented as transformations and not working.”

“Reproduce and use Pathfinder to find the exact parameter or bug control to avoid it.”

“Maybe you have run without the latest patches.”

“I’m able to reproduce the bug on 26ai with optimizer_features_enable='21.1.0', but I would like a finer setting.”

The agent ran Mauro Pagano’s Pathfinder with OFE 21.1 as the baseline, testing 2,811 cases. I described this method in the past (I ‘fixed’ execution plan regression with optimizer_features_enable, what to do next?).

Two controls avoided the wrong result: 35915968:1 and 35969400:1. The description of the first immediately connected the result to the FETCH FIRST rewrite.

Then I prompted Copilot for the next step:

“Look at the execution plans.”

“Is it a combination of FETCH FIRST transformation, ANSI join, and merged views?”

This is where AI assistance is useful to me. My experience guides the investigation. The agent handles the repetitive experiments, collects the output, and helps compare it. The results still have to explain the behavior. A plausible answer is not enough.

What the trace adds

The failing execution plan is reduced to an unconditional FAST DUAL. The correct plan retains the filter, the outer-join branches, and the COUNT STOPKEY operations.

The expanded SQL and execution plans show the difference. The paired 10053 traces add something more interesting: the analytic and ROWNUM rewrites lead to different view-merging decisions.

In the analytic path, Oracle keeps the window-function query block:

SVM:     SVM bypassed on view SEL$4(#0): Window functions in this view.

It also retains an additional correlated lateral-view layer:

SVM:     SVM bypassed on view SEL$F23444D6(#0): Lateral view with left correlation.

In the ROWNUM path, Oracle merges the simple inner query into the block containing the row limit:

CVM:   Merging SPJ view SEL$4 (#0) into SEL$6 (#0)

The resulting scalar subquery is simpler:

SELECT "D"."DUMMY"
FROM "SYS"."DUAL" "D"
WHERE ROWNUM <= 1
  AND "D"."DUMMY" = "A"."DUMMY"

It also merges a layer that remained separate in the analytic path:

CVM:   Merging SPJ view SEL$F23444D6 (#0) into SEL$3 (#0)

So the explanation is not simply “ROWNUM prevents view merging.” The correct path actually performs more merges at these points. It removes unnecessary wrapper blocks while retaining the scalar subquery’s row-limit boundary.

Both paths still have restrictions around the null-augmented outer-joined lateral view. The analytic path adds another combination of boundaries and correlations, and that combination ends in the wrong executable plan.

This identifies the problematic path. It does not identify the exact line of Oracle code responsible, nor prove that 35915968 was originally created to fix this particular report. What we have demonstrated is that enabling this transformation avoids the wrong result.

New syntax, old machinery

I like standard SQL syntax. FETCH FIRST expresses the intention more clearly than a manually nested ROWNUM query. I've seen too many of those queries with the wrong placement of ORDER BY.

But syntax support and native implementation are different things.

Oracle’s ANSI joins are translated into internal structures, including lateral views and legacy outer-join representations. The row-limit clause was translated into an analytic query. Each transformation may be reasonable on its own. Their combinations are where the complexity grows.

There is a long history of ANSI-related wrong-results bugs. Jonathan Lewis has documented examples involving ANSI joins and NATURAL JOIN. I've see it striking again with materialized views and assertions.

Transformations are essential to query optimization. The problem is not that they exist. It is that adding a feature through several layers of rewriting also adds interactions that must preserve the original semantics.

Here, the legacy ROWNUM implementation has two advantages: first-k-row optimization and a row-limit boundary that Oracle already knows how to handle. Finally, the newer syntax stayed but the machinery underneath returned to the simpler implementation.

And AI can help us investigate that machinery. Not by replacing database knowledge with a confident explanation, but by making it easier to run the experiments that turn an explanation into evidence. You don't know to download Pathfinder and run it yourself when an AI agent can reproduce everything on a docker container.

What about PostgreSQL?

You may wonder how PostgreSQL dealt with the addition of FETCH FIRST to the SQL standard. Unlike Oracle’s initial implementation, PostgreSQL did not rewrite it into an analytic ROW_NUMBER() query. FETCH FIRST was implemented as an alternative syntax for LIMIT/OFFSET which already existed, and EXPLAIN shows a Limit node. The planner also knows the requested row count when costing paths: if an index supplies the required order, it can choose that path and stop once enough rows have been produced. Otherwise, it may still need to scan or sort more data.

At the top level, FETCH FIRST does not add a query block. Inside a subquery, though, the row limit prevents that subquery from being pulled up, since moving the limit could change which rows it returns. So PostgreSQL has a native stop-after-N boundary, conceptually like Oracle’s COUNT STOPKEY—not the same operator, but the same basic idea. The engines converge on that approach. PostgreSQL used it from the start, while Oracle initially implemented FETCH FIRST through ROW_NUMBER() analytic filter and later changed the rewrite for the simple row-limit case.

DocumentDB 0.117: scalar $group index pushdown

Franck Pachot — Thu, 01 Oct 2026 21:02:22 +0000

Here's a detailed look at scalar $group index pushdown, an optimization introduced with DocumentDB 0.117-0 on September 10, 2026. It also highlights an advantage over MongoDB: the PostgreSQL query planner ensures optimal performance without requiring additional hints in the query.

DocumentDB is a fully open-source PostgreSQL extension that brings the MongoDB API to the SQL table. It offers MongoDB users an alternative by integrating with PostgreSQL, transforming MongoDB operators into efficient PostgreSQL access paths. Microsoft leads the development, continuously enhancing the extension based on valuable feedback from enterprise customers on Azure DocumentDB.

To demonstrate the optimization, I load 50,000 documents with 100 distinct a values and 200-byte payloads. I create an index on {a: 1} then calculate {$group: {_id: null, total: {$sum: "$a"}}}. The query has no hint, so the optimizer must decide whether it can answer the accumulator from the a_1 index.

Controlled method

MongoDB, DocumentDB 0.116, and DocumentDB 0.117 process the same documents, indexes, and unhinted pipelines. Each DocumentDB gateway execution is divided into setup, VACUUM (ANALYZE), and query phases. I compare rows, heap fetches, and buffers rather than elapsed time across different containers.

The feature is present in 0.117 but disabled by default. I enable only documentdb.enable_scalar_aggregate_index_pushdown. All other settings stay the same.

MongoDB 8.0 reference

I initially perform the unhinted aggregation on MongoDB Atlas 8.0:

db = db.getSiblingDB("perf117");
db.scalar_group.drop();
db.scalar_group.insertMany(Array.from({length: 50000}, (_, index) => ({
  _id: index + 1,
  a: (index + 1) % 100,
  payload: "x".repeat(200)
})));
db.scalar_group.createIndex({a: 1});

const pipeline = [
  {$group: {_id: null, total: {$sum: "$a"}}}
];
print(EJSON.stringify({
  result: db.scalar_group.aggregate(pipeline).toArray(),
  explain: db.scalar_group.explain("executionStats").aggregate(pipeline)
}, null, 2));

{
  "result": [
    {
      "_id": null,
      "total": 2475000
    }
  ],
  "explain": {
    "explainVersion": "2",
    "queryPlanner": {
      "namespace": "perf117.scalar_group",
      "parsedQuery": {},
      "indexFilterSet": false,
      "queryHash": "7234A6DE",
      "planCacheShapeHash": "7234A6DE",
      "planCacheKey": "2A6F009E",
      "optimizationTimeMillis": 0,
      "optimizedPipeline": true,
      "maxIndexedOrSolutionsReached": false,
      "maxIndexedAndSolutionsReached": false,
      "maxScansToExplodeReached": false,
      "prunedSimilarIndexes": false,
      "winningPlan": {
        "isCached": false,
        "queryPlan": {
          "stage": "GROUP",
          "planNodeId": 3,
          "inputStage": {
            "stage": "COLLSCAN",
            "planNodeId": 1,
            "filter": {},
            "direction": "forward"
          }
        },
        "slotBasedPlan": {
          "slots": "$$RESULT=s8 env: {  }",
          "stages": "[3] project [s8 = newBsonObj(\"_id\", s6, \"total\", s7)] \n[3] project [s6 = null, s7 = doubleDoubleSumFinalize(s5)] \n[3] group [] [s5 = aggDoubleDoubleSum(s1)] spillSlots[s4] mergingExprs[aggMergeDoubleDoubleSums(s4)] \n[1] scan s2 s3 none none none none none none lowPriority [s1 = a] @\"a05943ac-772f-4c76-b128-e6e3bfdd0158\" true false "
        }
      },
      "rejectedPlans": []
    },
    "executionStats": {
      "executionSuccess": true,
      "nReturned": 1,
      "executionTimeMillis": 12,
      "totalKeysExamined": 0,
      "totalDocsExamined": 50000,
      "executionStages": {
        "stage": "project",
        "planNodeId": 3,
        "nReturned": 1,
        "executionTimeMillisEstimate": 4,
        "opens": 1,
        "closes": 1,
        "saveState": 0,
        "restoreState": 0,
        "isEOF": 1,
        "projections": {
          "8": "newBsonObj(\"_id\", s6, \"total\", s7) "
        },
        "inputStage": {
          "stage": "project",
          "planNodeId": 3,
          "nReturned": 1,
          "executionTimeMillisEstimate": 4,
          "opens": 1,
          "closes": 1,
          "saveState": 0,
          "restoreState": 0,
          "isEOF": 1,
          "projections": {
            "6": "null ",
            "7": "doubleDoubleSumFinalize(s5) "
          },
          "inputStage": {
            "stage": "group",
            "planNodeId": 3,
            "nReturned": 1,
            "executionTimeMillisEstimate": 4,
            "opens": 1,
            "closes": 1,
            "saveState": 0,
            "restoreState": 0,
            "isEOF": 1,
            "groupBySlots": [],
            "expressions": {
              "5": "aggDoubleDoubleSum(s1) ",
              "initExprs": {
                "5": null
              }
            },
            "mergingExprs": {
              "4": "aggMergeDoubleDoubleSums(s4) "
            },
            "usedDisk": false,
            "spills": 0,
            "spilledBytes": 0,
            "spilledRecords": 0,
            "spilledDataStorageSize": 0,
            "inputStage": {
              "stage": "scan",
              "planNodeId": 1,
              "nReturned": 50000,
              "executionTimeMillisEstimate": 4,
              "opens": 1,
              "closes": 1,
              "saveState": 0,
              "restoreState": 0,
              "isEOF": 1,
              "numReads": 50000,
              "recordSlot": 2,
              "recordIdSlot": 3,
              "scanFieldNames": [
                "a"
              ],
              "scanFieldSlots": [
                1
              ]
            }
          }
        }
      }
    },
    "queryShapeHash": "F1620CAE8E90491891C0FB6F23ECD9556823F83467253A897C540C2A88AC66E5",
    "command": {
      "aggregate": "scalar_group",
      "pipeline": [
        {
          "$group": {
            "_id": null,
            "total": {
              "$sum": "$a"
            }
          }
        }
      ],
      "cursor": {},
      "$db": "perf117"
    },
    "serverInfo": {
      "host": "a004f7434a57",
      "port": 27017,
      "version": "8.0.28",
      "gitVersion": "cd6fc9b3b7cf87ff2bbca0af67382ac407fc682a"
    },
    "serverParameters": {
      "internalQueryFacetBufferSizeBytes": 104857600,
      "internalQueryFacetMaxOutputDocSizeBytes": 104857600,
      "internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
      "internalDocumentSourceGroupMaxMemoryBytes": 104857600,
      "internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
      "internalQueryProhibitBlockingMergeOnMongoS": 0,
      "internalQueryMaxAddToSetBytes": 104857600,
      "internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600,
      "internalQueryFrameworkControl": "trySbeRestricted",
      "internalQueryPlannerIgnoreIndexWithCollationForRegex": 1
    },
    "ok": 1
  }
}

MongoDB performs a COLLSCAN for this unhinted scalar aggregation, scanning all 50,000 documents without using any index keys, resulting in one group. The aggregation does not spill, but the documents containing payload data are still read.

MongoDB creates index-access plans only when the index helps with filtering, sorting, or a DISTINCT_SCAN rewrite, which isn't the case here. To force an IXSCAN for this query, you need to specify the index explicitly using { hint: { a: 1 } } (this bypasses the query planner so you must ensure that the result remains unaffected, especially if using a partial index):

db.scalar_group.explain(
   "executionStats"
 ).aggregate(
   pipeline, { hint: { a: 1 } } 
 ).queryPlanner.winningPlan
;

{
  isCached: false,
  queryPlan: {
    stage: 'GROUP',
    planNodeId: 3,
    inputStage: {
      stage: 'PROJECTION_COVERED',
      planNodeId: 2,
      transformBy: { a: true, _id: false },
      inputStage: {
        stage: 'IXSCAN',
        planNodeId: 1,
        keyPattern: { a: 1 },
        indexName: 'a_1',
        isMultiKey: false,
        multiKeyPaths: { a: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: { a: [ '[MinKey, MaxKey]' ] }
      }
    }
  },
  slotBasedPlan: {
    slots: '$$RESULT=s7 env: {  }',
    stages: '[3] project [s7 = newBsonObj("_id", s5, "total", s6)] \n' +
      '[3] project [s5 = null, s6 = doubleDoubleSumFinalize(s4)] \n' +
      '[3] group [] [s4 = aggDoubleDoubleSum(s1)] spillSlots[s3] mergingExprs[aggMergeDoubleDoubleSums(s3)] \n' +
      '[1] ixseek KS(0A0104) KS(F0FE04) none s2 none none lowPriority [s1 = 0] @"10eeeb21-169c-4c72-bbb4-4a674cbf37db" @"a_1" true '
  }
}

With the hint, MongoDB can use an index-only scan (IXSCAN + PROJECTION_COVERED), reading only 50,000 index entries.

DocumentDB 0.116 (before this optimization)

I load and index the same data through the DocumentDB 0.116 gateway (MongoDB-compatible endpoint):

db = db.getSiblingDB("perf117");
db.scalar_group.drop();
db.scalar_group.insertMany(Array.from({length: 50000}, (_, index) => ({
  _id: index + 1,
  a: (index + 1) % 100,
  payload: "x".repeat(200)
})));
db.scalar_group.createIndex(
  {a: 1},
  {storageEngine: {enableOrderedIndex: true}}
);
print(EJSON.stringify({
  insertedDocuments: db.scalar_group.countDocuments(),
  indexes: db.scalar_group.getIndexes().map(index => index.name)
}, null, 2));

{
  "insertedDocuments": 50000,
  "indexes": [
    "_id_",
    "a_1"
  ]
}

I run VACUUM (ANALYZE) in PostgreSQL to simulate the auto-vacuum job in a reproducible way:

VACUUM (ANALYZE)
;

Then I run the unchanged scalar aggregation:

db = db.getSiblingDB("perf117");
const pipeline = [
  {$group: {_id: null, total: {$sum: "$a"}}}
];
print(EJSON.stringify({
  result: db.scalar_group.aggregate(pipeline).toArray(),
  explain: db.scalar_group.explain("executionStats").aggregate(pipeline)
}, null, 2));

{
  "result": [
    {
      "_id": null,
      "total": 2475000
    }
  ],
  "explain": {
    "explainVersion": 2,
    "command": "db.runCommand({explain: { 'aggregate': 'scalar_group', 'pipeline': [{ '$group': { '_id': null, 'total': { '$sum': '$a' } } }], 'cursor': {} }})",
    "explainCommandPlanningTimeMillis": 4.189,
    "explainCommandExecTimeMillis": 159.291,
    "stages": [
      {
        "$cursor": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "COLLSCAN",
              "startupCost": 0,
              "totalCost": 2477,
              "estimatedTotalKeysExamined": 50000
            }
          },
          "executionStats": {
            "nReturned": 50000,
            "executionTimeMillis": 67.959,
            "executionStartAtTimeMillis": 0.004,
            "totalDocsExamined": 50000,
            "totalKeysExamined": 50000,
            "executionStages": {
              "stage": "COLLSCAN",
              "nReturned": 50000,
              "executionTimeMillis": 67.959,
              "executionStartAtTimeMillis": 0.004,
              "totalDocsExamined": 50000,
              "totalKeysExamined": 50000,
              "numBlocksFromCache": 1852
            }
          }
        }
      },
      {
        "$root": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "GENERIC_AGGREGATE",
              "startupCost": 0,
              "totalCost": 2602.02,
              "aggStrategy": "Sorted",
              "estimatedTotalKeysExamined": 1
            }
          },
          "executionStats": {
            "nReturned": 1,
            "executionTimeMillis": 159.235,
            "executionStartAtTimeMillis": 159.231,
            "totalDocsExamined": 1,
            "totalKeysExamined": 1,
            "executionStages": {
              "stage": "GENERIC_AGGREGATE",
              "nReturned": 1,
              "executionTimeMillis": 159.235,
              "executionStartAtTimeMillis": 159.231,
              "totalDocsExamined": 1,
              "totalKeysExamined": 1,
              "numBlocksFromCache": 1852
            }
          }
        }
      }
    ],
    "ok": 1
  }
}

DocumentDB 0.116 also performs a collection scan through the gateway. It examines 50,000 documents and reports 1,852 shared-buffer hits.

I run the same setup and pipeline through the native DocumentDB API in the PostgreSQL endpoint, but explicitly disable Seq Scan to show why the Index Scan is more expensive:

\pset pager off
\set ON_ERROR_STOP on
SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog,
  documentdb_api_internal, public;

SELECT extversion AS documentdb_version
FROM pg_extension
WHERE extname = 'documentdb';

SELECT documentdb_api.drop_collection('perf117', 'scalar_group');
SELECT count(documentdb_api.insert_one(
  'perf117',
  'scalar_group',
  format(
    '{"_id":%s,"a":%s,"payload":"%s"}',
    i, i % 100, repeat('x', 200)
  )::documentdb_core.bson,
  NULL
))
FROM generate_series(1, 50000) i;

SELECT documentdb_api_internal.create_indexes_non_concurrently(
  'perf117',
  '{"createIndexes":"scalar_group","indexes":[{"key":{"a":1},"storageEngine":{"enableOrderedIndex":true},"name":"a_1"}]}',
  true
);

SELECT collection_id
FROM documentdb_api_catalog.collections
WHERE database_name = 'perf117' AND collection_name = 'scalar_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;

SET enable_seqscan TO off;
SET enable_bitmapscan TO off;

EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document
FROM bson_aggregation_pipeline(
  'perf117',
  '{"aggregate":"scalar_group","pipeline":[{"$group":{"_id":null,"total":{"$sum":"$a"}}}],"cursor":{}}'
);

...
                                                                                                                             QUERY PLAN                                                                                                                             
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate (actual rows=1 loops=1)
   Output: bson_repath_and_build('_id'::text, 'BSONHEX070000000a0000'::bson, 'total'::text, bsonsumwithexpr(document, 'BSONHEX0e00000002000300000024610000'::bson, 'BSONHEX12000000096e6f7700876814cda001000000'::bson, NULL::text)), 'BSONHEX070000000a0000'::bson
   Buffers: shared hit=2101
   ->  Custom Scan (DocumentDBApiExplainQueryScan) (actual rows=50000 loops=1)
         Output: document
         namespaceName: perf117.scalar_group
         indexName: _id_
         _id_: (startup cost=0.415, total cost=1379.415, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=50000, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)
         Buffers: shared hit=2101
         ->  Index Scan using _id_ on documentdb_data.documents_4 collection (actual rows=50000 loops=1)
               Output: document
               Index Cond: (collection.shard_key_value = '4'::bigint)
               Buffers: shared hit=2101
 Planning:
   Buffers: shared hit=254
(15 rows)

Without scalar pushdown, DocumentDB 0.116 scans the _id_ index and visits the table for all 50,000 rows. The plan reports 2,101 shared-buffer hits. The a_1 index cannot yet supply the accumulator directly.

In this case, the Index Scan was not selected by default because it was estimated to be more costly—I had to disable the Seq Scan to reveal it.

DocumentDB 0.117 (with the optimization)

Since this feature is off by default in 0.117, where it was introduced, I enable it on the server running the MongoDB gateway and restart the local container to ensure new gateway sessions adopt the setting.

ALTER SYSTEM SET documentdb.enable_scalar_aggregate_index_pushdown TO 'on';
SELECT pg_reload_conf();

The setting used for the captured run is:

 documentdb.enable_scalar_aggregate_index_pushdown 
---------------------------------------------------
 on
(1 row)

The source code indicates that, like many features, it will be enabled in a later version: /* Added in v117, Pending stabilization, enable in v119 */. Since then, v0.118 has been renamed to v1.0 and v0.119 to v1.1 as DocumentDB prepares for a 1.0 release.

I repeat the same setup from the MongoDB endpoint:

db = db.getSiblingDB("perf117");
db.scalar_group.drop();
db.scalar_group.insertMany(Array.from({length: 50000}, (_, index) => ({
  _id: index + 1,
  a: (index + 1) % 100,
  payload: "x".repeat(200)
})));
db.scalar_group.createIndex(
  {a: 1},
  {storageEngine: {enableOrderedIndex: true}}
);
print(EJSON.stringify({
  insertedDocuments: db.scalar_group.countDocuments(),
  indexes: db.scalar_group.getIndexes().map(index => index.name)
}, null, 2));

{
  "insertedDocuments": 50000,
  "indexes": [
    "_id_",
    "a_1"
  ]
}

The same maintenance step makes the heap all-visible:

VACUUM (ANALYZE);

The query is unchanged and still has no hint:

db = db.getSiblingDB("perf117");
const pipeline = [
  {$group: {_id: null, total: {$sum: "$a"}}}
];
print(EJSON.stringify({
  result: db.scalar_group.aggregate(pipeline).toArray(),
  explain: db.scalar_group.explain("executionStats").aggregate(pipeline)
}, null, 2));

{
  "result": [
    {
      "_id": null,
      "total": 2475000
    }
  ],
  "explain": {
    "explainVersion": 2,
    "command": "db.runCommand({explain: { 'aggregate': 'scalar_group', 'pipeline': [{ '$group': { '_id': null, 'total': { '$sum': '$a' } } }], 'cursor': {} }})",
    "explainCommandPlanningTimeMillis": 1.54,
    "explainCommandExecTimeMillis": 414.061,
    "stages": [
      {
        "$cursor": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "IXSCAN",
              "ns": "perf117.scalar_group",
              "indexName": "a_1",
              "direction": "Forward",
              "isIndexOnlyScan": true,
              "indexUsage": {
                "indexKeyString": "{\"a\": 1}",
                "isMultiKey": false,
                "bounds": [
                  "[\"a\": (MinKey, MaxKey)]"
                ]
              },
              "startupCost": 0,
              "totalCost": 4.01,
              "indexFilterSet": [
                {
                  "a": {
                    "$range": {
                      "fullScan": true
                    }
                  }
                }
              ],
              "estimatedTotalKeysExamined": 16667
            },
            "indexCosts": [
              {
                "namespace": "perf117.scalar_group",
                "costs": [
                  {
                    "indexName": "_id_",
                    "indexKeyString": "{\"_id\": 1}",
                    "startupCost": 0.415,
                    "totalCost": 1379.415,
                    "selectivity": 1,
                    "correlation": 0.75,
                    "estimatedPercentIndexPagesLoaded": 100,
                    "estimatedTotalIndexEntries": 50000,
                    "boundarySelectivity": 1
                  }
                ]
              }
            ]
          },
          "executionStats": {
            "nReturned": 50000,
            "executionTimeMillis": 98.976,
            "executionStartAtTimeMillis": 0.084,
            "totalDocsExamined": 50000,
            "totalKeysExamined": 50000,
            "executionStages": {
              "stage": "IXSCAN",
              "nReturned": 50000,
              "executionTimeMillis": 98.976,
              "executionStartAtTimeMillis": 0.084,
              "indexName": "a_1",
              "totalDocsAnalyzed": 0,
              "indexUsage": {
                "scanLoops": 100,
                "scanType": "ordered",
                "scanKeys": [
                  "key 1: [(isInequality: true, estimatedEntryCount: 50000)]"
                ]
              },
              "totalKeysExamined": 50000,
              "numBlocksFromCache": 24
            }
          }
        }
      },
      {
        "$root": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "GENERIC_AGGREGATE",
              "startupCost": 0,
              "totalCost": 45.7,
              "aggStrategy": "Sorted",
              "estimatedTotalKeysExamined": 1
            }
          },
          "executionStats": {
            "nReturned": 1,
            "executionTimeMillis": 413.955,
            "executionStartAtTimeMillis": 413.95,
            "totalDocsExamined": 1,
            "totalKeysExamined": 1,
            "executionStages": {
              "stage": "GENERIC_AGGREGATE",
              "nReturned": 1,
              "executionTimeMillis": 413.955,
              "executionStartAtTimeMillis": 413.95,
              "totalDocsExamined": 1,
              "totalKeysExamined": 1,
              "numBlocksFromCache": 24
            }
          }
        }
      }
    ],
    "ok": 1
  }
}

The 0.117 gateway now performs an IXSCAN on a_1 with isIndexOnlyScan: true. It scans 50,000 index entries, reports totalDocsAnalyzed: 0, and makes 24 shared-buffer hits. The 200-byte payload remains unread.

The native query differs from the 0.116 script only in enabling the released feature:

\pset pager off
\set ON_ERROR_STOP on
SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog,
  documentdb_api_internal, public;

SELECT extversion AS documentdb_version
FROM pg_extension
WHERE extname = 'documentdb';

SELECT documentdb_api.drop_collection('perf117', 'scalar_group');
SELECT count(documentdb_api.insert_one(
  'perf117',
  'scalar_group',
  format(
    '{"_id":%s,"a":%s,"payload":"%s"}',
    i, i % 100, repeat('x', 200)
  )::documentdb_core.bson,
  NULL
))
FROM generate_series(1, 50000) i;

SELECT documentdb_api_internal.create_indexes_non_concurrently(
  'perf117',
  '{"createIndexes":"scalar_group","indexes":[{"key":{"a":1},"storageEngine":{"enableOrderedIndex":true},"name":"a_1"}]}',
  true
);

SELECT collection_id
FROM documentdb_api_catalog.collections
WHERE database_name = 'perf117' AND collection_name = 'scalar_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;

SET documentdb.enable_scalar_aggregate_index_pushdown TO on;

EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document
FROM bson_aggregation_pipeline(
  'perf117',
  '{"aggregate":"scalar_group","pipeline":[{"$group":{"_id":null,"total":{"$sum":"$a"}}}],"cursor":{}}'
);
                                                                                                                                QUERY PLAN                                                                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate (actual rows=1 loops=1)
   Output: bson_repath_and_build('_id'::text, 'BSONHEX070000000a0000'::bson, 'total'::text, bsonsumwithexpr(document, 'BSONHEX0e00000002000300000024610000'::bson, 'BSONHEX12000000096e6f77006d7a14cda001000000'::bson, NULL::text)), 'BSONHEX070000000a0000'::bson
   Buffers: shared hit=24
   ->  Custom Scan (DocumentDBApiExplainQueryScan) (actual rows=50000 loops=1)
         Output: document
         namespaceName: perf117.scalar_group
         indexName: a_1
         indexKey: {"a": 1}
         isMultiKey: false
         indexBounds: ["a": (MinKey, MaxKey)]
         innerScanLoops: 100 loops
         scanType: ordered
         scanKeyDetails: key 1: [(isInequality: true, estimatedEntryCount: 50000)]
         _id_: (indexKey='{"_id": 1}', startup cost=0.415, total cost=1379.415, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=50000, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)
         Buffers: shared hit=24
         ->  Index Only Scan using a_1 on documentdb_data.documents_3 collection (actual rows=50000 loops=1)
               Output: document
               Index Cond: (collection.document @<> 'BSONHEX18000000036100100000000866756c6c5363616e00010000'::bson)
               Heap Fetches: 0
               Buffers: shared hit=24
 Planning:
   Buffers: shared hit=328
(22 rows)

PostgreSQL shows the same change as Index Only Scan using a_1, with Heap Fetches: 0 and 24 shared-buffer hits. This is the access path underlying the MongoDB-compatible IXSCAN stage.

DocumentDB and PostgreSQL two-step optimization

Before this change, scalar $group index-path generation considered the group key but not the fields referenced by its accumulators. With a constant key such as _id: null, it could overlook a covering index on an accumulator field. The optimization collects simple "$field" accumulator paths and adds a fullScan qual for each. In this example, the native plan shows document @<> '{"a":{"fullScan":true}}', while the gateway explain shows the marker in indexFilterSet. The marker expands to inclusive MinKey–MaxKey bounds, so it matches all documents rather than filtering the result.

This exposes an access path. It does not force the planner to choose it.

PostgreSQL chooses the path: it costs the available alternatives and, in this run, selects the a_1 Index Only Scan. The reported access-path cost is 4.01, versus 1,379.415 for the _id_ index alternative. Those are planner estimates, not execution times. VACUUM (ANALYZE) supplies statistics and marks heap pages all-visible, helping the scan avoid heap fetches, but the injected condition does not guarantee that PostgreSQL will choose it.

In comparison, MongoDB’s hinted plan also scans a_1 from MinKey to MaxKey—shown explicitly as indexBounds: {a: ['[MinKey, MaxKey]']} but obtaining that covered path required the user’s {hint: {a: 1}} option. That hint directs MongoDB to use the index, whereas DocumentDB’s fullScan marker leaves the final access-path choice to PostgreSQL’s cost-based planner.

Conclusion

This is a measurable optimization improvement, not just a new plan-node name. DocumentDB 0.117 replaces full-document access with a covered scan of the accumulator field.

Here is a summary of the experiment:

Engine and API	Access path	Index entries	Heap/document reads	Shared-buffer hits
MongoDB 8.0	`COLLSCAN`	0	50,000 documents	not reported
MongoDB 8.0 with `hint()`	`IXSCAN`	50,000	0	not reported
DocumentDB 0.116 gateway (MongoDB API)	`COLLSCAN`	not applicable	50,000 documents	1,852
DocumentDB 0.116 native (SQL function) with Seq Scan disabled	`_id_` `Index Scan`	50,000	50,000 heap rows	2,101
DocumentDB 0.117 gateway (MongoDB API), feature on	index-only `IXSCAN` on `a_1`	50,000	0 (`totalDocsAnalyzed`)	24
DocumentDB 0.117 native (SQL function), feature on	`Index Only Scan` on `a_1`	50,000	0 (`Heap Fetches`)	24

Without needing the developer to manually suggest index usage, DocumentDB 0.117 naturally chooses a more efficient access path compared to un-hinted MongoDB 8.0 in the captured plans. MongoDB reads 50,000 documents, while DocumentDB accesses only the covered index. This extension uniquely combines the best of both worlds: MongoDB's emulation with BSON-based access paths and extended RUM indexes, plus PostgreSQL's smart query planner and cost-based optimizer. Note: It relies on accurate planner statistics for optimization and a fresh visibility map for execution. I've run VACUUM ANALYZE manually here to create a consistent test case without delay, but statistics gathering also runs smoothly in the background through PostgreSQL’s auto-vacuum.

DocumentDB 0.109: $match $sort $limit $project covered by index scan

Franck Pachot — Tue, 29 Sep 2026 22:05:28 +0000

This should have been the first post of this series. A few months ago, I demonstrated (video) what I see as one of the key advantages of a document model: a compound index can support filtering, sorting, and pagination across data embedded in a one-to-many relationship.

In a normalized relational model, that relationship typically spans multiple tables. Indexes belong to individual tables, so answering the same query may require to join more rows before sorting and filtering.

That demo used MongoDB. Some MongoDB emulations on SQL databases pretend compatibility but lack performance it they don't implement MongoDB-style indexing or query plans. That's generally because the emulation is built on top of RDBMS indexes which were not designed for non-1NF schemas.

Azure DocumentDB takes a different approach: it runs as a PostgreSQL extension which, thanks to PostgreSQL’s extensibility, can define native indexing for non-relational datatypes. The DocumentDB extension uses Extended RUM indexes.

In this post, I’ll run the same as I did on MongoDB to show that DocumentDB on PostgreSQL provides the same performance with similar execution plan. You can also play with it on db<>fiddle https://dbfiddle.uk/Ft_2L34U:

This starts by creating, though the MongoDB-compatible endpoint, the collection and index with accounts and operations:

//
// Create same data as https://youtu.be/Hq9CFhxSqgw?si=g6CnDuv_PqPHRquc
//

db.accounts.createIndex({
  category: 1,
  "operations.date": -1,
});

function insert(num) {
  const ops = [];
  for (let i = 0; i < num; i++) {
    const account = Math.floor(Math.random() * 10_000) + 1;
    const category = Math.floor(Math.random() * 3);
    const operation = {
      date: new Date(),
      amount: Math.floor(Math.random() * 1_000) + 1,
    };
    ops.push({
      updateOne: {
        filter: { _id: account },
        update: {
          $set: { category: category },
          $push: { operations: operation },
        },
        upsert: true,
      },
    });
  }
  db.accounts.bulkWrite(ops);
}

insert(1_000); insert(1_000); insert(1_000); insert(1_000); insert(1_000);
insert(1_000); insert(1_000); insert(1_000); insert(1_000); insert(1_000);

As the account operations are embedded as an array for each account, a single index can serve filtering on account's attributes, like "category", and operation's attributes, like "date".

A simple query asks Which Category 1 account had the most recent activity? and this involves a filter on category and operation date:


db.accounts.find(
    { "category": 1 },
    { "operations.amount": 1, "operations.date": 1 }
  ).sort({ "operations.date": -1 }).limit(1);

This is typical of pagination, filtering a specific number of documents from an ordered result:

[
  {
    _id: 6115,
    operations: [
      { date: ISODate('2026-09-25T22:34:03.218Z'), amount: 320 },
      { date: ISODate('2026-09-25T22:34:05.739Z'), amount: 847 }
    ]
  }
]

The MongoDB-compatible execution plan shows that it didn't require a sort operation as the index scan provides the documents in the expected order:

db.accounts.find(
    { "category": 1 },
    { "operations.amount": 1, "operations.date": 1 }
  ).sort({ "operations.date": -1 }).limit(1).explain().queryPlanner.winningPlan

{
  stage: 'LIMIT',
  startupCost: 0,
  totalCost: 0.2,
  estimatedTotalKeysExamined: 1,
  inputStage: {
    stage: 'PROJECT',
    startupCost: 0,
    totalCost: 0.2,
    estimatedTotalKeysExamined: 1,
    inputStage: {
      stage: 'FETCH',
      ns: 'test.accounts',
      startupCost: 0,
      totalCost: 208.79,
      estimatedTotalKeysExamined: 1055,
      inputStage: {
        stage: 'IXSCAN',
        ns: 'test.accounts',
        indexName: 'category_1_operations.date_-1',
        direction: 'Forward',
        indexUsage: {
          indexKeyString: '{"category": 1,"operations.date": -1}',
          isMultiKey: true,
          bounds: [
            '["category": [1, 1], "operations.date": DESC(MinKey, MaxKey)]'
          ]
        },
        startupCost: 0,
        totalCost: 208.79,
        hasOrderBy: true,
        indexFilterSet: [ { category: { '$eq': 1 } } ],
        estimatedTotalKeysExamined: 1055
      }
    }
  }
}

I can run the same query, as an aggregation pipeline, on the PostgreSQL endpoint though DocumentDB functions:

EXPLAIN (COSTS OFF, ANALYZE ON, BUFFERS ON, VERBOSE ON)
SELECT document FROM documentdb_api_catalog.bson_aggregation_pipeline(
    'test',
    '{"aggregate": "accounts", "pipeline": [
  {"$match": {"category": 1}},
  {"$sort": {"operations.date": -1}},
  {"$limit": 1},
  {"$project": {"operations.amount": 1, "operations.date": 1}}
    ], "cursor": {}}'::documentdb_core.bson
);

                                                   QUERY PLAN
--------------------------------------------------------------------------
Subquery Scan on agg_stage_3 (actual time=0.111..0.112 rows=1.00 loops=1)
  Output: documentdb_api_internal.bson_dollar_project(agg_stage_3.document, 'BSONHEX31000000106f7065726174696f6e732e616d6f756e740001000000106f7065726174696f6e732e64617465000100000000'::documentdb_core.bson, 'BSONHEX12000000096e6f7700d660b4daa001000000'::documentdb_core.bson)
  Buffers: shared hit=5
  ->  Limit (actual time=0.101..0.101 rows=1.00 loops=1)
        Output: collection.document, (documentdb_api_catalog.bson_orderby(collection.document, 'BSONHEX1a000000106f7065726174696f6e732e6461746500ffffffff00'::documentdb_core.bson))
        Buffers: shared hit=5
        ->  Custom Scan (DocumentDBApiExplainQueryScan) (actual time=0.100..0.100 rows=1.00 loops=1)
              Output: collection.document, documentdb_api_catalog.bson_orderby(collection.document, 'BSONHEX1a000000106f7065726174696f6e732e6461746500ffffffff00'::documentdb_core.bson)
              namespaceName: test.accounts
              indexName: category_1_operations.date_-1
              indexKey: {"category": 1,"operations.date": -1}
              isMultiKey: true
              indexBounds: ["category": [1, 1], "operations.date": DESC(MinKey, MaxKey)]
              innerScanLoops: 1 loops
              scanType: ordered
              scanKeyDetails: key 1: [(isInequality: true, estimatedEntryCount: 116)]
              _id_: (startup cost=0.282, total cost=287.743, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=6328, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)
              Buffers: shared hit=5
              ->  Index Scan using "category_1_operations.date_-1" on documentdb_data.documents_2 collection (actual time=0.055..0.055 rows=1.00 loops=1)
                    Output: collection.document
                    Index Cond: (collection.document OPERATOR(documentdb_api_catalog.@=) 'BSONHEX130000001063617465676f7279000100000000'::documentdb_core.bson)
                    Order By: (collection.document OPERATOR(documentdb_api_catalog.|-<>) 'BSONHEX1a000000106f7065726174696f6e732e6461746500ffffffff00'::documentdb_core.bson)
                    Index Searches: 0
                    Buffers: shared hit=5
Planning:
  Buffers: shared hit=518
Planning Time: 1.605 ms
Execution Time: 0.202 ms

The Index Scan covered the $match filter with Index Cond, the $sort with Order By, the $limit: 1 with rows=1.00 and the $projection with Output. There no sort or filter above it. The Custom Scan is a purely decorative wrapper that DocumentDB injects around the real index access path so that EXPLAIN can expose MongoDB-style index metadata, like isMultiKey: true and the indexBounds.

I got the remark that the planning time is huge here Planning Time: 1.605 ms. I've ran it on db<>fiddle which use micro-VM for fast ephemeral instances, so don't compare time. But still, Buffers: shared hit=518 as a lot and this is due to the first query in a PostgreSQL session that reads from the catalog. If I had run the query with explain first to check the result (https://dbfiddle.uk/eFaEQ0Gq), the next query would have had much faster planning:

Planning:
  Buffers: shared hit=2
Planning Time: 0.126 ms
Execution Time: 0.104 ms

The video demonstrated the main advantage of a document model with multi-key indexes on MongoDB. The same exists open-source on PostgreSQL with the DocumentDB extension. Extended RUM was added in v0.106 (August 29, 2025), ordered indexes/scans were enabled by default in v0.109 (March 09, 2026) - be sure that documentdb_extended_rum is listed in shared_preload_libraries - and later releases improved edge cases of it (e.g. collation support across 0.111–0.113, multikey fixes in 0.116).

DocumentDB 0.116: $sort $group prefix pushdown

Franck Pachot — Tue, 29 Sep 2026 21:11:04 +0000

DocumentDB 0.116-0, released August 20, 2026, introduced prefix sort pushdown for $group accumulators. It lets the grouping stage consume rows in the existing sort order, avoiding a separate blocking sort or hash aggregate.

The previous article in this series covered a different optimization: a distinct scan that skips duplicate index entries when a group returns one row per distinct key. Prefix sort pushdown does not skip entries. It removes the top-level sort. The query pattern is also related to the first-per-group example in First/Last per Group: PostgreSQL DISTINCT ON and MongoDB DISTINCT_SCAN Performance, which shows MongoDB using DISTINCT_SCAN.

DocumentDB implements the MongoDB API as a fully open-source PostgreSQL extension. It offers an open alternative for MongoDB applications and exposes aggregation execution through both MongoDB-like and PostgreSQL plans, providing insight into sorts, buffers, and temporary I/O. Microsoft is the main contributor, with improvements coming from enterprise experience with Azure DocumentDB.

To demonstrate prefix sort pushdown, I load 50,000 documents across 100 groups, add a 200-byte payload, and create an ordered index on {a: 1}. The pipeline sorts by a, groups by the same key, and returns the first name in each group. Because the group key is a prefix of the sort keys, the accumulator can use the existing index order instead of sorting all 50,000 rows again.

Controlled method

MongoDB, DocumentDB 0.114, and DocumentDB 0.116 receive the same data, ordered index, hint, and pipeline. Both DocumentDB versions get the same VACUUM (ANALYZE), and both native runs use the same planner settings. The documentdb.enableSortPushToAccumulatorWithPrefix setting exists in 0.114 but is disabled by default. It is enabled by default in 0.116. I compare explicit sort nodes, temporary I/O, rows, and buffers rather than total elapsed time. The only timing I cite is when the gateway's group stage starts producing rows (executionStartAtTimeMillis), which shows whether grouping blocks or streams. In the native (SQL function) runs, I set enable_hashagg=off (and enable_seqscan / enable_bitmapscan) to force the sorted plan, but the gateway (MongoDB-compatible endpoint) runs use default settings.

MongoDB 8.0 reference

I first run the pipeline on MongoDB Atlas 8.0:

db = db.getSiblingDB("perf116");
db.sort_group.drop();
db.sort_group.insertMany(Array.from({length: 50000}, (_, index) => ({
  _id: index + 1,
  a: (index + 1) % 100,
  b: (50000 - index - 1) % 1000,
  name: `name_${index + 1}`,
  payload: "x".repeat(200)
})));
db.sort_group.createIndex({a: 1});

const pipeline = [
  {$sort: {a: 1}},
  {$group: {_id: "$a", firstVal: {$first: "$name"}}}
];
print(EJSON.stringify({
  resultCount: db.sort_group.aggregate(
    pipeline,
    {hint: "a_1"}
  ).toArray().length,
  explain: db.sort_group.explain("executionStats").aggregate(
    pipeline,
    {hint: "a_1"}
  )
}, null, 2));

It shows a DISTINCT_SCAN:

{
  "resultCount": 100,
  "explain": {
    "explainVersion": "1",
    "stages": [
      {
        "$cursor": {
          "queryPlanner": {
            "namespace": "perf116.sort_group",
            "parsedQuery": {},
            "indexFilterSet": false,
            "queryHash": "DC5C6196",
            "planCacheShapeHash": "DC5C6196",
            "planCacheKey": "979F6906",
            "optimizationTimeMillis": 0,
            "maxIndexedOrSolutionsReached": false,
            "maxIndexedAndSolutionsReached": false,
            "maxScansToExplodeReached": false,
            "prunedSimilarIndexes": false,
            "winningPlan": {
              "isCached": false,
              "stage": "FETCH",
              "inputStage": {
                "stage": "DISTINCT_SCAN",
                "keyPattern": {
                  "a": 1
                },
                "indexName": "a_1",
                "isMultiKey": false,
                "multiKeyPaths": {
                  "a": []
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 2,
                "direction": "forward",
                "indexBounds": {
                  "a": [
                    "[MinKey, MaxKey]"
                  ]
                }
              }
            },
            "rejectedPlans": []
          },
          "executionStats": {
            "executionSuccess": true,
            "nReturned": 100,
            "executionTimeMillis": 2,
            "totalKeysExamined": 100,
            "totalDocsExamined": 100,
            "executionStages": {
              "isCached": false,
              "stage": "FETCH",
              "nReturned": 100,
              "executionTimeMillisEstimate": 0,
              "works": 101,
              "advanced": 100,
              "needTime": 0,
              "needYield": 0,
              "saveState": 3,
              "restoreState": 3,
              "isEOF": 1,
              "docsExamined": 100,
              "alreadyHasObj": 0,
              "inputStage": {
                "stage": "DISTINCT_SCAN",
                "nReturned": 100,
                "executionTimeMillisEstimate": 0,
                "works": 101,
                "advanced": 100,
                "needTime": 0,
                "needYield": 0,
                "saveState": 3,
                "restoreState": 3,
                "isEOF": 1,
                "keyPattern": {
                  "a": 1
                },
                "indexName": "a_1",
                "isMultiKey": false,
                "multiKeyPaths": {
                  "a": []
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 2,
                "direction": "forward",
                "indexBounds": {
                  "a": [
                    "[MinKey, MaxKey]"
                  ]
                },
                "keysExamined": 100
              }
            }
          }
        },
        "nReturned": 100,
        "executionTimeMillisEstimate": 3
      },
      {
        "$groupByDistinctScan": {
          "newRoot": {
            "_id": "$a",
            "firstVal": "$name"
          }
        },
        "nReturned": 100,
        "executionTimeMillisEstimate": 3
      }
    ],
    "queryShapeHash": "0822C17532FC5F85649D595163EF78A0BAEF5CDA8811F1689D203E1AD79FD2F7",
    "serverInfo": {
      "host": "a004f7434a57",
      "port": 27017,
      "version": "8.0.28",
      "gitVersion": "cd6fc9b3b7cf87ff2bbca0af67382ac407fc682a"
    },
    "serverParameters": {
      "internalQueryFacetBufferSizeBytes": 104857600,
      "internalQueryFacetMaxOutputDocSizeBytes": 104857600,
      "internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
      "internalDocumentSourceGroupMaxMemoryBytes": 104857600,
      "internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
      "internalQueryProhibitBlockingMergeOnMongoS": 0,
      "internalQueryMaxAddToSetBytes": 104857600,
      "internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600,
      "internalQueryFrameworkControl": "trySbeRestricted",
      "internalQueryPlannerIgnoreIndexWithCollationForRegex": 1
    },
    "command": {
      "aggregate": "sort_group",
      "pipeline": [
        {
          "$sort": {
            "a": 1
          }
        },
        {
          "$group": {
            "_id": "$a",
            "firstVal": {
              "$first": "$name"
            }
          }
        }
      ],
      "hint": "a_1",
      "cursor": {},
      "$db": "perf116"
    },
    "ok": 1
  }
}

MongoDB recognizes the first-per-group pattern. It uses DISTINCT_SCAN on a_1, examines 100 index keys, fetches 100 documents for name, and returns 100 groups. There is no blocking sort.

DocumentDB 0.114 (before this optimization)

I load and index the same data through the DocumentDB 0.114 gateway (MongoDB-compatible endpoint):

db = db.getSiblingDB("perf116");
db.sort_group.drop();
db.sort_group.insertMany(Array.from({length: 50000}, (_, index) => ({
  _id: index + 1,
  a: (index + 1) % 100,
  b: (50000 - index - 1) % 1000,
  name: `name_${index + 1}`,
  payload: "x".repeat(200)
})));
db.sort_group.createIndex(
  {a: 1},
  {storageEngine: {enableOrderedIndex: true}}
);
print(EJSON.stringify({
  insertedDocuments: db.sort_group.countDocuments(),
  indexes: db.sort_group.getIndexes().map(index => index.name)
}, null, 2));


{
  "insertedDocuments": 50000,
  "indexes": [
    "_id_",
    "a_1"
  ]
}

Connected to the PostgreSQL endpoint, I make the table statistics and visibility state deterministic:

\pset pager off
SET search_path TO documentdb_api_catalog, public;
SELECT extversion AS documentdb_version
FROM pg_extension
WHERE extname = 'documentdb';
SELECT collection_id
FROM collections
WHERE database_name = 'perf116' AND collection_name = 'sort_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;
SELECT :'collection_id' AS vacuumed_collection_id;

 documentdb_version 
--------------------
 0.114-0
(1 row)

 vacuumed_collection_id 
------------------------
 2
(1 row)

Then I run the same hinted pipeline on the MongoSH:

db = db.getSiblingDB("perf116");
const pipeline = [
  {$sort: {a: 1}},
  {$group: {_id: "$a", firstVal: {$first: "$name"}}}
];
print(EJSON.stringify({
  resultCount: db.sort_group.aggregate(
    pipeline,
    {hint: "a_1"}
  ).toArray().length,
  explain: db.sort_group.explain("executionStats").aggregate(
    pipeline,
    {hint: "a_1"}
  )
}, null, 2));


{
  "resultCount": 100,
  "explain": {
    "explainVersion": 2,
    "command": "db.runCommand({explain: { 'aggregate': 'sort_group', 'pipeline': [{ '$sort': { 'a': 1 } }, { '$group': { '_id': '$a', 'firstVal': { '$first': '$name' } } }], 'hint': 'a_1', 'cursor': {} }})",
    "explainCommandPlanningTimeMillis": 2.649,
    "explainCommandExecTimeMillis": 349.424,
    "stages": [
      {
        "$cursor": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "FETCH",
              "startupCost": 0,
              "totalCost": 13.89,
              "estimatedTotalKeysExamined": 5556,
              "inputStage": {
                "stage": "IXSCAN",
                "indexName": "a_1",
                "direction": "Forward",
                "startupCost": 0,
                "totalCost": 13.89,
                "hasOrderBy": true,
                "indexFilterSet": [
                  {
                    "a": {
                      "$range": {
                        "orderByScan": 1
                      }
                    }
                  }
                ],
                "estimatedTotalKeysExamined": 5556
              }
            }
          },
          "executionStats": {
            "nReturned": 50000,
            "executionTimeMillis": 125.407,
            "executionStartAtTimeMillis": 0.036,
            "totalDocsExamined": 50000,
            "totalKeysExamined": 50000,
            "executionStages": {
              "stage": "FETCH",
              "nReturned": 50000,
              "executionTimeMillis": 125.407,
              "executionStartAtTimeMillis": 0.036,
              "totalKeysExamined": 50000,
              "numBlocksFromCache": 50023,
              "inputStage": {
                "stage": "IXSCAN",
                "nReturned": 50000,
                "executionTimeMillis": 125.407,
                "executionStartAtTimeMillis": 0.036,
                "indexName": "a_1",
                "totalKeysExamined": 50000,
                "numBlocksFromCache": 50023
              }
            }
          }
        }
      },
      {
        "$group": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "GROUP",
              "startupCost": 111.12,
              "totalCost": 236.13,
              "aggStrategy": "Hashed",
              "estimatedTotalKeysExamined": 5556,
              "inputStage": {
                "stage": "PROJECTION_DEFAULT",
                "startupCost": 0,
                "totalCost": 83.34,
                "estimatedTotalKeysExamined": 5556
              }
            }
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 349.126,
            "executionStartAtTimeMillis": 348.869,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "GROUP",
              "nReturned": 100,
              "executionTimeMillis": 349.126,
              "executionStartAtTimeMillis": 348.869,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "numBlocksFromCache": 50023,
              "inputStage": {
                "stage": "PROJECTION_DEFAULT",
                "nReturned": 50000,
                "executionTimeMillis": 255.141,
                "executionStartAtTimeMillis": 0.041,
                "totalDocsExamined": 50000,
                "totalKeysExamined": 50000,
                "numBlocksFromCache": 50023
              }
            }
          }
        }
      }
    ],
    "ok": 1
  }
}

With default settings, the gateway plan uses a hash aggregate (aggStrategy: "Hashed"), which cannot return any group until it reads all 50,000 rows. The group stage starts at 348.9 ms. The MongoDB-compatible explain doesn't show buffers or temporary I/O, so I inspect the native plan from the PostgreSQL endpoint. I disable hash aggregation there to see the sort-based plan that the new optimization targets:

\pset pager off
\set ON_ERROR_STOP on
SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog,
  documentdb_api_internal, public;

SELECT extversion AS documentdb_version
FROM pg_extension
WHERE extname = 'documentdb';

SELECT documentdb_api.drop_collection('perf116', 'sort_group');
SELECT count(documentdb_api.insert_one(
  'perf116',
  'sort_group',
  format(
    '{"_id":%s,"a":%s,"b":%s,"name":"name_%s","payload":"%s"}',
    i, i % 100, (50000 - i) % 1000, i, repeat('x', 200)
  )::documentdb_core.bson,
  NULL
))
FROM generate_series(1, 50000) i;

SELECT documentdb_api_internal.create_indexes_non_concurrently(
  'perf116',
  '{"createIndexes":"sort_group","indexes":[{"key":{"a":1},"storageEngine":{"enableOrderedIndex":true},"name":"a_1"}]}',
  true
);

SELECT collection_id
FROM documentdb_api_catalog.collections
WHERE database_name = 'perf116' AND collection_name = 'sort_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;

SET enable_seqscan TO off;
SET enable_bitmapscan TO off;
SET enable_hashagg TO off;
SET documentdb.enableGroupByCompoundIdIndexPushdown TO off;

\echo === DocumentDB 0.114 ===
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document
FROM bson_aggregation_pipeline(
  'perf116',
  '{"aggregate":"sort_group","pipeline":[{"$sort":{"a":1}},{"$group":{"_id":"$a","firstVal":{"$first":"$name"}}}],"cursor":{},"hint":"a_1"}'
);

Here is the output:

 documentdb_version 
--------------------
 0.114-0
(1 row)

 drop_collection 
-----------------
 f
(1 row)

NOTICE:  creating collection
 count 
-------
 50000
(1 row)

                                                                                                                   create_indexes_non_concurrently                                                                                                                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 BSONHEX7e00000003726177006c0000000364656661756c7453686172640059000000106e756d496e64657865734265666f72650001000000106e756d496e6465786573416674657200020000000863726561746564436f6c6c656374696f6e4175746f6d61746963616c6c790000106f6b00010000000000106f6b000100000000
(1 row)

=== DocumentDB 0.114 ===
                                                                                                                                                                                                                                                                                        QUERY PLAN                                                                                                                                                                                                                                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate (actual rows=100 loops=1)
   Output: bson_repath_and_build('_id'::text, (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700210816cda001000000'::bson)), 'firstVal'::text, bson_expression_get(bsonfirst(agg_stage_0.document, '{BSONHEX0c0000001061000100000000}'::bson[]), 'BSONHEX11000000020006000000246e616d650000'::bson, true, 'BSONHEX12000000096e6f7700210816cda001000000'::bson)), (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700210816cda001000000'::bson))
   Group Key: (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700210816cda001000000'::bson))
   Buffers: shared hit=50023, temp read=1768 written=1771
   ->  Sort (actual rows=50000 loops=1)
         Output: (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700210816cda001000000'::bson)), agg_stage_0.document
         Sort Key: (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700210816cda001000000'::bson))
         Sort Method: external merge  Disk: 14144kB
         Buffers: shared hit=50023, temp read=1768 written=1771
         ->  Subquery Scan on agg_stage_0 (actual rows=50000 loops=1)
               Output: bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700210816cda001000000'::bson), agg_stage_0.document
               Buffers: shared hit=50023
               ->  Index Scan using a_1 on documentdb_data.documents_2 collection (actual rows=50000 loops=1)
                     Output: collection.document, bson_orderby(collection.document, 'BSONHEX0c0000001061000100000000'::bson)
                     Index Cond: (collection.document @<> 'BSONHEX1e00000003610016000000106f7264657242795363616e00010000000000'::bson)
                     Order By: (collection.document |-<> 'BSONHEX0c0000001061000100000000'::bson)
                     Buffers: shared hit=50023
 Planning:
   Buffers: shared hit=548
(19 rows)

With hash aggregation disabled, DocumentDB 0.114 reads the ordered a_1 index but still sorts all 50,000 rows before GroupAggregate. The sort spills to disk as a 14,144 kB external merge and reports 1,768 temporary blocks read and 1,771 written.

DocumentDB 0.116 (after this optimization)

I repeat the same:

db = db.getSiblingDB("perf116");
db.sort_group.drop();
db.sort_group.insertMany(Array.from({length: 50000}, (_, index) => ({
  _id: index + 1,
  a: (index + 1) % 100,
  b: (50000 - index - 1) % 1000,
  name: `name_${index + 1}`,
  payload: "x".repeat(200)
})));
db.sort_group.createIndex(
  {a: 1},
  {storageEngine: {enableOrderedIndex: true}}
);
print(EJSON.stringify({
  insertedDocuments: db.sort_group.countDocuments(),
  indexes: db.sort_group.getIndexes().map(index => index.name)
}, null, 2));


{
  "insertedDocuments": 50000,
  "indexes": [
    "_id_",
    "a_1"
  ]
}

The same maintenance step is applied:

\pset pager off
SET search_path TO documentdb_api_catalog, public;
SELECT extversion AS documentdb_version
FROM pg_extension
WHERE extname = 'documentdb';
SELECT collection_id
FROM collections
WHERE database_name = 'perf116' AND collection_name = 'sort_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;
SELECT :'collection_id' AS vacuumed_collection_id;


 documentdb_version 
--------------------
 0.116-0
(1 row)

 vacuumed_collection_id 
------------------------
 7
(1 row)

The query and hint are unchanged:

db = db.getSiblingDB("perf116");
const pipeline = [
  {$sort: {a: 1}},
  {$group: {_id: "$a", firstVal: {$first: "$name"}}}
];
print(EJSON.stringify({
  resultCount: db.sort_group.aggregate(
    pipeline,
    {hint: "a_1"}
  ).toArray().length,
  explain: db.sort_group.explain("executionStats").aggregate(
    pipeline,
    {hint: "a_1"}
  )
}, null, 2));


{
  "resultCount": 100,
  "explain": {
    "explainVersion": 2,
    "command": "db.runCommand({explain: { 'aggregate': 'sort_group', 'pipeline': [{ '$sort': { 'a': 1 } }, { '$group': { '_id': '$a', 'firstVal': { '$first': '$name' } } }], 'hint': 'a_1', 'cursor': {} }})",
    "explainCommandPlanningTimeMillis": 1.094,
    "explainCommandExecTimeMillis": 280.012,
    "stages": [
      {
        "$cursor": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "FETCH",
              "ns": "perf116.sort_group",
              "startupCost": 0,
              "totalCost": 0,
              "estimatedTotalKeysExamined": 5556,
              "inputStage": {
                "stage": "IXSCAN",
                "ns": "perf116.sort_group",
                "indexName": "a_1",
                "direction": "Forward",
                "indexUsage": {
                  "indexKeyString": "{\"a\": 1}",
                  "isMultiKey": false,
                  "bounds": [
                    "[\"a\": (MinKey, MaxKey)]"
                  ]
                },
                "startupCost": 0,
                "totalCost": 0,
                "hasOrderBy": true,
                "indexFilterSet": [
                  {
                    "a": {
                      "$range": {
                        "orderByScan": 1
                      }
                    }
                  }
                ],
                "estimatedTotalKeysExamined": 5556
              }
            },
            "indexCosts": [
              {
                "namespace": "perf116.sort_group",
                "costs": [
                  {
                    "indexName": "_id_",
                    "startupCost": 0.415,
                    "totalCost": 1379.415,
                    "selectivity": 1,
                    "correlation": 0.75,
                    "estimatedPercentIndexPagesLoaded": 100,
                    "estimatedTotalIndexEntries": 50000,
                    "boundarySelectivity": 1
                  }
                ]
              }
            ]
          },
          "executionStats": {
            "nReturned": 50000,
            "executionTimeMillis": 92.674,
            "executionStartAtTimeMillis": 0.11,
            "totalDocsExamined": 50000,
            "totalKeysExamined": 50000,
            "executionStages": {
              "stage": "FETCH",
              "nReturned": 50000,
              "executionTimeMillis": 92.674,
              "executionStartAtTimeMillis": 0.11,
              "totalKeysExamined": 50000,
              "numBlocksFromCache": 50044,
              "inputStage": {
                "stage": "IXSCAN",
                "nReturned": 50000,
                "executionTimeMillis": 92.674,
                "executionStartAtTimeMillis": 0.11,
                "indexName": "a_1",
                "indexUsage": {
                  "scanLoops": 100,
                  "scanType": "ordered",
                  "scanKeys": [
                    "key 1: [(isInequality: true, estimatedEntryCount: 50000)]"
                  ]
                },
                "totalKeysExamined": 50000,
                "numBlocksFromCache": 50044
              }
            }
          }
        }
      },
      {
        "$group": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "GROUP",
              "startupCost": 0,
              "totalCost": 152.79,
              "aggStrategy": "Sorted",
              "estimatedTotalKeysExamined": 5556
            }
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 279.817,
            "executionStartAtTimeMillis": 3.311,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "GROUP",
              "nReturned": 100,
              "executionTimeMillis": 279.817,
              "executionStartAtTimeMillis": 3.311,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "numBlocksFromCache": 50044
            }
          }
        }
      }
    ],
    "ok": 1
  }
}

The group stage now reports aggStrategy: "Sorted" instead of 0.114's "Hashed": with the $sort pushed down, $group consumes the ordered index scan directly.

The gateway still reports 50,000 index entries and documents. DocumentDB's distinct scan for $first-only groups (documentdb.enableDistinctScanForGroupFirst, a sibling of the enableGroupByDistinctScan setting from the previous article) is disabled by default in 0.116, so all 50,000 keys are examined. I left it disabled to isolate the sort elimination, which is visible in the PostgreSQL execution plan (you can try all variations in this db<>fiddle):

\pset pager off
\set ON_ERROR_STOP on
SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog,
  documentdb_api_internal, public;

SELECT extversion AS documentdb_version
FROM pg_extension
WHERE extname = 'documentdb';

SELECT documentdb_api.drop_collection('perf116', 'sort_group');
SELECT count(documentdb_api.insert_one(
  'perf116',
  'sort_group',
  format(
    '{"_id":%s,"a":%s,"b":%s,"name":"name_%s","payload":"%s"}',
    i, i % 100, (50000 - i) % 1000, i, repeat('x', 200)
  )::documentdb_core.bson,
  NULL
))
FROM generate_series(1, 50000) i;

SELECT documentdb_api_internal.create_indexes_non_concurrently(
  'perf116',
  '{"createIndexes":"sort_group","indexes":[{"key":{"a":1},"storageEngine":{"enableOrderedIndex":true},"name":"a_1"}]}',
  true
);

SELECT collection_id
FROM documentdb_api_catalog.collections
WHERE database_name = 'perf116' AND collection_name = 'sort_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;

SET enable_seqscan TO off;
SET enable_bitmapscan TO off;
SET enable_hashagg TO off;
SET documentdb.enableGroupByCompoundIdIndexPushdown TO off;

SET documentdb.enableSortPushToAccumulatorWithPrefix TO on;
\echo === DocumentDB 0.116 feature ON ===
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document
FROM bson_aggregation_pipeline(
  'perf116',
  '{"aggregate":"sort_group","pipeline":[{"$sort":{"a":1}},{"$group":{"_id":"$a","firstVal":{"$first":"$name"}}}],"cursor":{},"hint":"a_1"}'
);

Here is the output:

 documentdb_version 
--------------------
 0.116-0
(1 row)

 drop_collection 
-----------------
 t
(1 row)

NOTICE:  creating collection
 count 
-------
 50000
(1 row)

                                                                                                                   create_indexes_non_concurrently                                                                                                                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 BSONHEX7e00000003726177006c0000000364656661756c7453686172640059000000106e756d496e64657865734265666f72650001000000106e756d496e6465786573416674657200020000000863726561746564436f6c6c656374696f6e4175746f6d61746963616c6c790000106f6b00010000000000106f6b000100000000
(1 row)

=== DocumentDB 0.116 feature ON ===
                                                                                                                                                                                                                                            QUERY PLAN                                                                                                                                                                                                                                             
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate (actual rows=100 loops=1)
   Output: bson_repath_and_build('_id'::text, (bson_expression_get(document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700d2f01acda001000000'::bson)), 'firstVal'::text, bsonfirstwithexpr(document, 'BSONHEX11000000020006000000246e616d650000'::bson, 'BSONHEX12000000096e6f7700d2f01acda001000000'::bson, NULL::text)), (bson_expression_get(document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700d2f01acda001000000'::bson))
   Group Key: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700d2f01acda001000000'::bson)
   Buffers: shared hit=50023
   ->  Custom Scan (DocumentDBApiExplainQueryScan) (actual rows=50000 loops=1)
         Output: bson_expression_get(document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700d2f01acda001000000'::bson), document
         namespaceName: perf116.sort_group
         indexName: a_1
         indexKey: {"a": 1}
         isMultiKey: false
         indexBounds: ["a": (MinKey, MaxKey)]
         innerScanLoops: 100 loops
         scanType: ordered
         scanKeyDetails: key 1: [(isInequality: true, estimatedEntryCount: 50000)]
         _id_: (startup cost=0.415, total cost=1379.415, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=50000, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)
         Buffers: shared hit=50023
         ->  Index Scan using a_1 on documentdb_data.documents_8 collection (actual rows=50000 loops=1)
               Output: document
               Index Cond: (collection.document @<> 'BSONHEX1e00000003610016000000106f7264657242795363616e00010000000000'::bson)
               Order By: (collection.document |-<> 'BSONHEX0c0000001061000100000000'::bson)
               Buffers: shared hit=50023
 Planning:
   Buffers: shared hit=387
(23 rows)

In 0.116, GroupAggregate consumes the ordered index scan directly. The Sort node, 14 MB spill, and all temporary reads and writes are gone. The scan work is unchanged, which isolates the improvement to sort elimination.

Conclusion

DocumentDB 0.116 turns a blocking $group into a streaming one: groups come directly from the ordered index scan, with no hash table or sort in between.

Here is a summary of the experiment:

Engine and API	Index entries	Documents fetched	Blocking step	Temporary I/O
MongoDB 8.0	100	100	none, `DISTINCT_SCAN`	not reported
DocumentDB 0.114 gateway (MongoDB API)	50,000	50,000	hash aggregate (first group at ~349 ms)	not exposed
DocumentDB 0.114 native (SQL function)	50,000	50,000	external merge sort, 14,144 kB (hash aggregation off)	1,768 blocks read, 1,771 written
DocumentDB 0.116 gateway (MongoDB API)	50,000	50,000	no, sorted streaming (first group at ~3 ms)	not exposed
DocumentDB 0.116 native (SQL function)	50,000	50,000	none	none
DocumentDB 0.116 native (SQL function), `enableDistinctScanForGroupFirst` on (default in 0.117)	100	100	none	none

By default, 0.114 uses hash aggregation and blocks until it reads all 50,000 rows (first group around 349 ms). When hash aggregation is turned off, it sorts and spills 14 MB. Version 0.116 streams sorted groups directly from the index (first group in about 3 ms), avoiding sorting and temporary I/O. With default 0.116 settings, DocumentDB does not use the distinct-scan access path for this pipeline, so it still reads all 50,000 entries, but grouping is no longer blocking. Enabling enableDistinctScanForGroupFirst (the default in 0.117) brings it to MongoDB's profile: 100 keys and 100 documents.

Note that the official description of this optimization is "pushing suffix sort keys into the accumulator in $sortGroup", but $sortGroup is not a user-facing MongoDB aggregation operator. It's an internal stage name in DocumentDB's codebase: when a pipeline has a $sort immediately followed by a $group, the two are fused into a single internal stage.

DocumentDB 0.116: $group distinct scan

Franck Pachot — Thu, 24 Sep 2026 17:52:53 +0000

I previously covered MongoDB’s DISTINCT_SCAN for first/last-per-group queries. DocumentDB 0.116 (August 20, 2026) extends the same loose-index-scan principle to $group queries that return one row per distinct grouping key, combining it with the index-only access path described in the previous post of this series.

DocumentDB implements the MongoDB API as a fully open source PostgreSQL extension. This makes it possible to run MongoDB applications on the most popular open source relational database without reducing the MongoDB language to simple document filtering. In my opinion, DocumentDB is the only MongoDB emulation that translates MongoDB operators into native SQL access paths. Microsoft is the main contributor, improving the extension from en enterprise customer feedback on Azure DocumentDB.

To demonstrate this optimization, I create 50,000 documents with only 100 distinct values in a, and an ordered a_1 index. The aggregation groups by a without an accumulator:

[
  {$group: {_id: "$a"}},
  {$sort: {_id: 1}}
]

The result contains 100 groups. A normal index scan reads all 50,000 index entries and lets the aggregate remove duplicates. A distinct scan can jump from one value to the next and read only 100 index entries.

I use MongoDB 8.0.28 as the reference, DocumentDB 0.114-0 as the previous version, and DocumentDB 0.116-0 with enableGroupByDistinctScan explicitly enabled. VACUUM (ANALYZE) is run on both DocumentDB versions so the visibility state is identical.

MongoDB 8.0 reference

This script creates the collection, builds the index, returns the number of groups, and runs the complete explain("executionStats"):

db = db.getSiblingDB("distinct116");
db.distinct_group.drop();

const batch = [];
for (let i = 0; i < 50000; i++) {
  batch.push({_id: i, a: i % 100, payload: "x".repeat(200)});
}
db.distinct_group.insertMany(batch);
db.distinct_group.createIndex({a: 1}, {name: "a_1"});

const pipeline = [
  {$group: {_id: "$a"}},
  {$sort: {_id: 1}}
];

print(EJSON.stringify({
  resultCount: db.distinct_group.aggregate(
    pipeline,
    {hint: "a_1"}
  ).toArray().length,
  explain: db.distinct_group.explain("executionStats").aggregate(
    pipeline,
    {hint: "a_1"}
  )
}, null, 2));

Here is the full execution plan:

{
  "resultCount": 100,
  "explain": {
    "explainVersion": "1",
    "stages": [
      {
        "$cursor": {
          "queryPlanner": {
            "namespace": "distinct116.distinct_group",
            "parsedQuery": {},
            "indexFilterSet": false,
            "queryHash": "C46B0559",
            "planCacheShapeHash": "C46B0559",
            "planCacheKey": "C66CA7DD",
            "optimizationTimeMillis": 0,
            "maxIndexedOrSolutionsReached": false,
            "maxIndexedAndSolutionsReached": false,
            "maxScansToExplodeReached": false,
            "prunedSimilarIndexes": false,
            "winningPlan": {
              "isCached": false,
              "stage": "PROJECTION_COVERED",
              "transformBy": {
                "a": 1,
                "_id": 0
              },
              "inputStage": {
                "stage": "DISTINCT_SCAN",
                "keyPattern": {
                  "a": 1
                },
                "indexName": "a_1",
                "isMultiKey": false,
                "multiKeyPaths": {
                  "a": []
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 2,
                "direction": "forward",
                "indexBounds": {
                  "a": [
                    "[MinKey, MaxKey]"
                  ]
                }
              }
            },
            "rejectedPlans": []
          },
          "executionStats": {
            "executionSuccess": true,
            "nReturned": 100,
            "executionTimeMillis": 2,
            "totalKeysExamined": 100,
            "totalDocsExamined": 0,
            "executionStages": {
              "isCached": false,
              "stage": "PROJECTION_COVERED",
              "nReturned": 100,
              "executionTimeMillisEstimate": 0,
              "works": 101,
              "advanced": 100,
              "needTime": 0,
              "needYield": 0,
              "saveState": 3,
              "restoreState": 3,
              "isEOF": 1,
              "transformBy": {
                "a": 1,
                "_id": 0
              },
              "inputStage": {
                "stage": "DISTINCT_SCAN",
                "nReturned": 100,
                "executionTimeMillisEstimate": 0,
                "works": 101,
                "advanced": 100,
                "needTime": 0,
                "needYield": 0,
                "saveState": 3,
                "restoreState": 3,
                "isEOF": 1,
                "keyPattern": {
                  "a": 1
                },
                "indexName": "a_1",
                "isMultiKey": false,
                "multiKeyPaths": {
                  "a": []
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 2,
                "direction": "forward",
                "indexBounds": {
                  "a": [
                    "[MinKey, MaxKey]"
                  ]
                },
                "keysExamined": 100
              }
            }
          }
        },
        "nReturned": 100,
        "executionTimeMillisEstimate": 1
      },
      {
        "$groupByDistinctScan": {
          "newRoot": {
            "_id": "$a"
          }
        },
        "nReturned": 100,
        "executionTimeMillisEstimate": 1
      },
      {
        "$sort": {
          "sortKey": {
            "_id": 1
          }
        },
        "totalDataSizeSortedBytesEstimate": 22100,
        "usedDisk": false,
        "spills": 0,
        "spilledDataStorageSize": 0,
        "nReturned": 100,
        "executionTimeMillisEstimate": 1
      }
    ],

MongoDB recognizes that the pipeline needs only one result per distinct indexed value. The winning plan is a covered DISTINCT_SCAN. It examines 100 keys, reads no documents, and returns 100 values to $groupByDistinctScan. This is the ideal access path for this query.

DocumentDB 0.114-0 (before this optimization)

I load exactly the same 50,000 documents in DocumentDB 0.114-0 on PostgreSQL through the MongoDB-compatible gateway:

db = db.getSiblingDB("distinct116");
db.distinct_group.drop();

for (let start = 0; start < 50000; start += 1000) {
  const batch = [];
  for (let i = start; i < start + 1000; i++) {
    batch.push({_id: i, a: i % 100, payload: "x".repeat(200)});
  }
  db.distinct_group.insertMany(batch);
}

db.distinct_group.createIndex({a: 1}, {name: "a_1"});

Before running the plans, I vacuum the physical PostgreSQL table, from psql:

\pset pager off
SET search_path TO documentdb_api_catalog, public;
SELECT extversion AS documentdb_version
FROM pg_extension
WHERE extname = 'documentdb';
SELECT collection_id
FROM collections
WHERE database_name = 'distinct116' AND collection_name = 'distinct_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;
SELECT :'collection_id' AS vacuumed_collection_id;

In real life, this runs automatically by the background auto-vacuum of PostgreSQL but I don't want to wait and prefer a deterministic test.

The 0.114-0 output identifies the extension and the collection that was vacuumed:

Pager usage is off.
SET
 documentdb_version 
--------------------
 0.114-0
(1 row)

VACUUM
 vacuumed_collection_id 
------------------------
 4
(1 row)

I then run the same aggregation through the MongoDB API:

db = db.getSiblingDB("distinct116");

const pipeline = [
  {$group: {_id: "$a"}},
  {$sort: {_id: 1}}
];

print(EJSON.stringify({
  resultCount: db.distinct_group.aggregate(
    pipeline,
    {hint: "a_1"}
  ).toArray().length,
  explain: db.distinct_group.explain("executionStats").aggregate(
    pipeline,
    {hint: "a_1"}
  )
}, null, 2));

Here is the full execution plan:

{
  "resultCount": 100,
  "explain": {
    "explainVersion": 2,
    "command": "db.runCommand({explain: { 'aggregate': 'distinct_group', 'pipeline': [{ '$group': { '_id': '$a' } }, { '$sort': { '_id': 1 } }], 'hint': 'a_1', 'cursor': {} }})",
    "explainCommandPlanningTimeMillis": 3.197,
    "explainCommandExecTimeMillis": 179.281,
    "stages": [
      {
        "$cursor": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "IXSCAN",
              "indexName": "a_1",
              "direction": "Forward",
              "isIndexOnlyScan": true,
              "startupCost": 0,
              "totalCost": 13.89,
              "indexFilterSet": [
                {
                  "a": {
                    "$range": {
                      "orderByScan": 1
                    }
                  }
                }
              ],
              "estimatedTotalKeysExamined": 5556
            }
          },
          "executionStats": {

            "nReturned": 50000,
            "executionTimeMillis": 109.417,
            "executionStartAtTimeMillis": 0.09,
            "totalDocsExamined": 50000,
            "totalKeysExamined": 50000,
            "executionStages": {
              "stage": "IXSCAN",
              "nReturned": 50000,
              "executionTimeMillis": 109.417,
              "executionStartAtTimeMillis": 0.09,
              "indexName": "a_1",
              "totalDocsAnalyzed": 0,
              "totalKeysExamined": 50000,
              "numBlocksFromCache": 24
            }
          }
        }
      },
      {
        "$group": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "GROUP",
              "startupCost": 0,
              "totalCost": 125.01,
              "aggStrategy": "Sorted",
              "estimatedTotalKeysExamined": 5556
            }
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 176.999,
            "executionStartAtTimeMillis": 1.335,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "GROUP",
              "nReturned": 100,
              "executionTimeMillis": 176.999,
              "executionStartAtTimeMillis": 1.335,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "numBlocksFromCache": 24
            }
          }
        }
      },
      {
        "$sort": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "SORT",
              "startupCost": 540.04,
              "totalCost": 553.93,
              "sortKeysCount": 1,
              "sortKey": [
                {
                  "_id": 1
                }
              ],
              "estimatedTotalKeysExamined": 5556,
              "inputStage": {
                "stage": "PROJECTION_DEFAULT",
                "startupCost": 0,
                "totalCost": 194.46,
                "estimatedTotalKeysExamined": 5556
              }
            }
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 179.079,
            "executionStartAtTimeMillis": 178.996,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "SORT",
              "nReturned": 100,
              "executionTimeMillis": 179.079,
              "executionStartAtTimeMillis": 178.996,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "sortMethod": "quicksort",
              "totalDataSizeSortedBytesEstimate": 29,
              "numBlocksFromCache": 32,
              "inputStage": {
                "stage": "PROJECTION_DEFAULT",
                "nReturned": 100,
                "executionTimeMillis": 178.606,
                "executionStartAtTimeMillis": 1.343,
                "totalDocsExamined": 100,
                "totalKeysExamined": 100,
                "numBlocksFromCache": 24
              }
            }
          }
        }
      }
    ],
    "ok": 1
  }
}

The gateway reports an index-only IXSCAN, but its cursor returns all 50,000 index entries. totalDocsAnalyzed: 0 confirms that the table is not visited, while totalKeysExamined: 50000 shows the remaining work. The GROUP stage reduces these entries to 100 groups.

The native PostgreSQL call uses the same database, collection, pipeline, hint, planner settings, and post-vacuum state:

\set ON_ERROR_STOP on
\pset pager off

SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog, documentdb_api_internal, public;
SET enable_seqscan TO off;
SET enable_bitmapscan TO off;
SET enable_hashagg TO off;

SELECT document FROM bson_aggregation_pipeline(
  'distinct116',
  '{"aggregate":"distinct_group","pipeline":[{"$group":{"_id":"$a"}},{"$sort":{"_id":1}}],"cursor":{},"hint":"a_1"}'
);

EXPLAIN (ANALYZE ON, COSTS OFF, BUFFERS ON, SUMMARY OFF, TIMING OFF, VERBOSE ON)
SELECT document FROM bson_aggregation_pipeline(
  'distinct116',
  '{"aggregate":"distinct_group","pipeline":[{"$group":{"_id":"$a"}},{"$sort":{"_id":1}}],"cursor":{},"hint":"a_1"}'
);

The PostgreSQL execution plan is:

                                                                                                                                                                     QUERY PLAN                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort (actual rows=100 loops=1)
   Output: agg_stage_1.document, (bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson))
   Sort Key: (bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson)) NULLS FIRST
   Sort Method: quicksort  Memory: 29kB
   Buffers: shared hit=24
   ->  Subquery Scan on agg_stage_1 (actual rows=100 loops=1)
         Output: agg_stage_1.document, bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson)
         Buffers: shared hit=24
         ->  GroupAggregate (actual rows=100 loops=1)
               Output: bson_repath_and_build('_id'::text, (bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77002cec22cda001000000'::bson))), (bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77002cec22cda001000000'::bson))
               Group Key: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77002cec22cda001000000'::bson)
               Buffers: shared hit=24
               ->  Index Only Scan using a_1 on documentdb_data.documents_4 collection (actual rows=50000 loops=1)
                     Output: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77002cec22cda001000000'::bson), collection.document
                     Index Cond: (collection.document @<> 'BSONHEX1e00000003610016000000106f7264657242795363616e00010000000000'::bson)
                     Order By: (collection.document |-<> 'BSONHEX0c0000001061000100000000'::bson)
                     Heap Fetches: 0
                     Buffers: shared hit=24
 Planning:
   Buffers: shared hit=18
(20 rows)

The PostgreSQL plan makes the old behavior explicit. Index Only Scan using a_1 reads 50,000 rows before GroupAggregate returns 100. Heap Fetches: 0 is good, but the executor still visits every duplicate index entry.

DocumentDB 0.116-0 (after this optimization)

The new distinct-scan path is controlled by documentdb.enableGroupByDistinctScan. It is disabled by default in this image, so I enable it explicitly. ALTER SYSTEM is used from psql because the MongoDB gateway opens independent PostgreSQL sessions:

\pset pager off
ALTER SYSTEM SET documentdb.enableGroupByDistinctScan TO 'on';
SELECT pg_reload_conf();
SHOW documentdb.enableGroupByDistinctScan;

After restarting the local DocumentDB container, I run the same gateway setup script, I vacuum the new physical collection with the same script, and run the query unchanged. Here is the complete MongoDB-compatible gateway output from DocumentDB 0.116-0:

{
  "resultCount": 100,
  "explain": {
    "explainVersion": 2,
    "command": "db.runCommand({explain: { 'aggregate': 'distinct_group', 'pipeline': [{ '$group': { '_id': '$a' } }, { '$sort': { '_id': 1 } }], 'hint': 'a_1', 'cursor': {} }})",
    "explainCommandPlanningTimeMillis": 1.482,
    "explainCommandExecTimeMillis": 1.631,
    "stages": [
      {
        "$cursor": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "DISTINCT_SCAN",
              "ns": "distinct116.distinct_group",
              "startupCost": 0,
              "totalCost": 0,
              "estimatedTotalKeysExamined": 5556,
              "inputStage": {
                "stage": "IXSCAN",
                "indexName": "a_1",
                "direction": "Forward",
                "isIndexOnlyScan": true,
                "indexUsage": {
                  "indexKeyString": "{\"a\": 1}",
                  "isMultiKey": false,
                  "bounds": [
                    "[\"a\": (MinKey, MaxKey)]"
                  ]
                },
                "startupCost": 0,
                "totalCost": 0,
                "indexFilterSet": [
                  {
                    "a": {
                      "$range": {
                        "orderByScan": 1
                      }
                    }
                  }
                ],
                "estimatedTotalKeysExamined": 5556
              }
            },
            "indexCosts": [
              {
                "namespace": "distinct116.distinct_group",
                "costs": [
                  {
                    "indexName": "_id_",
                    "startupCost": 0.415,
                    "totalCost": 1379.415,
                    "selectivity": 1,
                    "correlation": 0.75,
                    "estimatedPercentIndexPagesLoaded": 100,
                    "estimatedTotalIndexEntries": 50000,
                    "boundarySelectivity": 1
                  }
                ]
              }
            ]
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 0.696,
            "executionStartAtTimeMillis": 0.042,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "DISTINCT_SCAN",
              "nReturned": 100,
              "executionTimeMillis": 0.696,
              "executionStartAtTimeMillis": 0.042,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "numBlocksFromCache": 24,
              "inputStage": {
                "stage": "IXSCAN",
                "nReturned": 100,
                "executionTimeMillis": 0.531,
                "executionStartAtTimeMillis": 0.038,
                "indexName": "a_1",
                "totalDocsAnalyzed": 0,
                "indexUsage": {
                  "scanLoops": 100,
                  "scanType": "ordered",
                  "scanKeys": [
                    "key 1: [(isInequality: true, estimatedEntryCount: 50000)]"
                  ]
                },
                "totalKeysExamined": 100,
                "numBlocksFromCache": 24
              }
            }
          }
        }
      },
      {
        "$group": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "GROUP",
              "startupCost": 0,
              "totalCost": 125.01,
              "aggStrategy": "Sorted",
              "estimatedTotalKeysExamined": 5556
            }
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 1.085,
            "executionStartAtTimeMillis": 0.07,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "GROUP",
              "nReturned": 100,
              "executionTimeMillis": 1.085,
              "executionStartAtTimeMillis": 0.07,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "numBlocksFromCache": 24
            }
          }
        }
      },
      {
        "$sort": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "SORT",
              "startupCost": 540.04,
              "totalCost": 553.93,
              "sortKeysCount": 1,
              "sortKey": [
                {
                  "_id": 1
                }
              ],
              "estimatedTotalKeysExamined": 5556,
              "inputStage": {
                "stage": "PROJECTION_DEFAULT",
                "startupCost": 0,
                "totalCost": 194.46,
                "estimatedTotalKeysExamined": 5556
              }
            }
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 1.475,
            "executionStartAtTimeMillis": 1.39,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "SORT",
              "nReturned": 100,
              "executionTimeMillis": 1.475,
              "executionStartAtTimeMillis": 1.39,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "sortMethod": "quicksort",
              "totalDataSizeSortedBytesEstimate": 29,
              "numBlocksFromCache": 29,
              "inputStage": {
                "stage": "PROJECTION_DEFAULT",
                "nReturned": 100,
                "executionTimeMillis": 1.273,
                "executionStartAtTimeMillis": 0.074,
                "totalDocsExamined": 100,
                "totalKeysExamined": 100,
                "numBlocksFromCache": 24
              }
            }
          }
        }
      }
    ],
    "ok": 1
  }
}

The winning cursor stage is now DISTINCT_SCAN over the index-only IXSCAN. It returns 100 entries instead of 50,000. The gateway reports totalKeysExamined: 100, totalDocsAnalyzed: 0, and 100 scan loops: one ordered index probe per distinct value.

The native PostgreSQL script verifies the physical operator:

\set ON_ERROR_STOP on
\pset pager off

SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog, documentdb_api_internal, public;
SET enable_seqscan TO off;
SET enable_bitmapscan TO off;
SET enable_hashagg TO off;
SET documentdb.enableGroupByDistinctScan TO on;

SHOW documentdb.enableGroupByDistinctScan;

SELECT document FROM bson_aggregation_pipeline(
  'distinct116',
  '{"aggregate":"distinct_group","pipeline":[{"$group":{"_id":"$a"}},{"$sort":{"_id":1}}],"cursor":{},"hint":"a_1"}'
);

EXPLAIN (ANALYZE ON, COSTS OFF, BUFFERS ON, SUMMARY OFF, TIMING OFF, VERBOSE ON)
SELECT document FROM bson_aggregation_pipeline(
  'distinct116',
  '{"aggregate":"distinct_group","pipeline":[{"$group":{"_id":"$a"}},{"$sort":{"_id":1}}],"cursor":{},"hint":"a_1"}'
);

Here is the PostgreSQL execution plan:

QUERY PLAN                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort (actual rows=100 loops=1)
   Output: agg_stage_1.document, (bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson))
   Sort Key: (bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson)) NULLS FIRST
   Sort Method: quicksort  Memory: 29kB
   Buffers: shared hit=24
   ->  Subquery Scan on agg_stage_1 (actual rows=100 loops=1)
         Output: agg_stage_1.document, bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson)
         Buffers: shared hit=24
         ->  GroupAggregate (actual rows=100 loops=1)
               Output: bson_repath_and_build('_id'::text, (bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f770033f022cda001000000'::bson))), (bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f770033f022cda001000000'::bson))
               Group Key: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f770033f022cda001000000'::bson)
               Buffers: shared hit=24
               ->  Custom Scan (DocumentDBApiExplainQueryScan) (actual rows=100 loops=1)
                     Output: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f770033f022cda001000000'::bson), collection.document
                     namespaceName: distinct116.distinct_group
                     indexName: a_1
                     indexKey: {"a": 1}
                     isMultiKey: false
                     indexBounds: ["a": (MinKey, MaxKey)]
                     innerScanLoops: 100 loops
                     scanType: ordered
                     scanKeyDetails: key 1: [(isInequality: true, estimatedEntryCount: 50000)]
                     _id_: (startup cost=0.415, total cost=1379.415, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=50000, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)
                     Buffers: shared hit=24
                     ->  Custom Scan (DocumentDBApiDistinctQueryScan) (actual rows=100 loops=1)
                           Output: collection.document
                           Buffers: shared hit=24
                           ->  Index Only Scan using a_1 on documentdb_data.documents_10 collection (actual rows=100 loops=1)
                                 Output: collection.document
                                 Index Cond: (collection.document @<> 'BSONHEX1e00000003610016000000106f7264657242795363616e00010000000000'::bson)
                                 Order By: (collection.document |-<> 'BSONHEX0c0000001061000100000000'::bson)
                                 Heap Fetches: 0
                                 Buffers: shared hit=24
 Planning:
   Buffers: shared hit=18
(35 rows)

DocumentDBApiDistinctQueryScan wraps the index-only scan. The inner Index Only Scan now reports actual rows=100 instead of 50,000, with Heap Fetches: 0. This is not only a different plan name: 49,900 duplicate index entries no longer pass through the aggregate.

Conclusion

Here is a summary of the result on this 50,000-document example:

Engine	Access path	Index entries read	Heap/document reads	Groups
MongoDB 8.0.28	covered `DISTINCT_SCAN`	100	0	100
DocumentDB 0.114-0 gateway (MongoDB API)	index-only `IXSCAN`	50,000	0 analyzed	100
DocumentDB 0.114-0 native (SQL function)	`Index Only Scan`	50,000	0 heap fetches	100
DocumentDB 0.116-0 gateway (MongoDB API), feature on	`DISTINCT_SCAN` over index-only `IXSCAN`	100	0 analyzed	100
DocumentDB 0.116-0 native (SQL function), feature on	`DocumentDBApiDistinctQueryScan` over `Index Only Scan`	100	0 heap fetches	100

On this query, DocumentDB 0.116-0 reaches the same physical efficiency as MongoDB: both read one covered index entry per group and no documents. Compared with DocumentDB 0.114-0, the new access path reduces index entries flowing into the aggregate from 50,000 to 100, a 500-fold reduction. This is the kind of MongoDB compatibility that matters for performance: the same query is translated into the same efficient access strategy.

DocumentDB 0.113: $group covering index

Franck Pachot — Thu, 24 Sep 2026 17:51:57 +0000

This builds on my previous article, Covering Index for $group/$sum in MongoDB Aggregation, which showed that a hinted covering index can make hash-based grouping read index entries instead of documents. DocumentDB 0.113 (June 22, 2026) adds the corresponding index-only access path to its PostgreSQL execution engine.

DocumentDB is a fully open source PostgreSQL extension that implements the MongoDB API, giving MongoDB applications an open alternative on PostgreSQL. What matters to me is that it goes beyond parsing MongoDB syntax: operators become real PostgreSQL access paths and executor operations. Microsoft is the main contributor, improving the extension from the enterprise customer feedback on Azure DocumentDB.

To demonstrate the covered aggregate, I load 10,000 sales with ten categories, amount modulo 17, and a 200-byte payload, then group by category and sum the amount. Those fields are covered by an index on { category: 1, amount: 1 }.

I compare DocumentDB 0.112 with 0.113. Both runs load the same rows, create the same explicitly ordered index, run the same VACUUM (ANALYZE), disable the same PostgreSQL scan alternatives, and execute the same pipeline. The release is the only variable.

MongoDB 8.0 reference

I've run the following on MongoDB Atlas:

use test;
db.sales.drop();
db.sales.insertMany(Array.from({ length: 10000 }, (_, i) => ({
  _id: i + 1,
  category: (i + 1) % 10,
  amount: (i + 1) % 17,
  payload: "x".repeat(200)
})));

db.sales.createIndex(
    { category: 1, amount: 1 }
);

const pipeline = [
  { $group: { _id: "$category", total: { $sum: "$amount" } } }
];

db.sales.aggregate(pipeline, {
  hint: "category_1_amount_1"
});

db.sales.explain("executionStats").aggregate(pipeline, {
  hint: "category_1_amount_1"
});

Here is the queryPlanner.winningPlan:

{
  isCached: false,
  queryPlan: {
    stage: 'GROUP',
    planNodeId: 3,
    inputStage: {
      stage: 'PROJECTION_COVERED',
      planNodeId: 2,
      transformBy: { amount: true, category: true, _id: false },
      inputStage: {
        stage: 'IXSCAN',
        planNodeId: 1,
        keyPattern: { category: 1, amount: 1 },
        indexName: 'category_1_amount_1',
        isMultiKey: false,
        multiKeyPaths: { category: [], amount: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          category: [ '[MinKey, MaxKey]' ],
          amount: [ '[MinKey, MaxKey]' ]
        }
      }
    }
  },
  slotBasedPlan: {
    slots: '$$RESULT=s8 env: {  }',
    stages: '[3] project [s8 = newBsonObj("_id", s5, "total", s7)] \n' +
      '[3] project [s7 = doubleDoubleSumFinalize(s6)] \n' +
      '[3] group [s5] [s6 = aggDoubleDoubleSum(s2)] spillSlots[s4] mergingExprs[aggMergeDoubleDoubleSums(s4)] \n' +
      '[3] project [s5 = (s1 ?: null)] \n' +
      '[1] ixseek KS(0A0A0104) KS(F0F0FE04) none s3 none none lowPriority [s1 = 0, s2 = 1] @"574c1bf9-12e8-4b16-8bb7-e5c60d523460" @"category_1_amount_1" true '
  }
}

Here is the executionStats:

  executionStats: {
    executionSuccess: true,
    nReturned: 10,
    executionTimeMillis: 6,
    totalKeysExamined: 10000,
    totalDocsExamined: 0,
    executionStages: {
...
          inputStage: {
            stage: 'project',
            planNodeId: 3,
            nReturned: 10000,
            executionTimeMillisEstimate: 2,
            opens: 1,
            closes: 1,
            saveState: 0,
            restoreState: 0,
            isEOF: 1,
            projections: { '5': '(s1 ?: null) ' },
            inputStage: {
              stage: 'ixseek',
              planNodeId: 1,
              nReturned: 10000,
              executionTimeMillisEstimate: 2,
              opens: 1,
              closes: 1,
              saveState: 0,
              restoreState: 0,
              isEOF: 1,
              indexName: 'category_1_amount_1',
              keysExamined: 10000,
              seeks: 1,
              numReads: 10001,
              recordIdSlot: 3,
              outputSlots: [ Long('1'), Long('2') ],
              indexKeysToInclude: '00000000000000000000000000000011',
              seekKeyLow: 'KS(0A0A0104) ',
              seekKeyHigh: 'KS(F0F0FE04) '
            }
          }
        }
      }
    }
  },

MongoDB uses PROJECTION_COVERED over IXSCAN: it examines 10,000 index keys (totalKeysExamined: 10000), fetches zero documents (totalDocsExamined: 0), and produces the ten groups (nReturned: 10). That is the useful reference behavior because the aggregate is answered from index values without reading collection documents (no stage: 'FETCH').

DocumentDB 0.112 (before this optimization)

When running the same on DocumentDB 0.112 we observe an IXSCAN but a FETCH above it, reading all documents - the aggregation is not visible in this execution plan:

        "executionStats": {
          "nReturned": 10000,
          "executionTimeMillis": 36.895,
          "executionStartAtTimeMillis": 0.048,
          "totalDocsExamined": 10000,
          "totalKeysExamined": 10000,
          "executionStages": {
            "stage": "FETCH",
            "nReturned": 10000,
            "executionTimeMillis": 36.895,
            "executionStartAtTimeMillis": 0.048,
            "totalKeysExamined": 10000,
            "numBlocksFromCache": 10008,
            "inputStage": {
              "stage": "IXSCAN",
              "nReturned": 10000,
              "executionTimeMillis": 36.895,
              "executionStartAtTimeMillis": 0.048,
              "indexName": "category_1_amount_1",
              "totalKeysExamined": 10000,
              "numBlocksFromCache": 10008
            }
          }

Obviously, the aggregation was not pushed down to the MongoDB-compatible scan. I run the same on PostgreSQL with the DocumentDB API functions to understand the full execution:

\pset pager off
SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog,
  documentdb_api_internal, public;

EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document
FROM bson_aggregation_pipeline(
  'test',
  '{"aggregate":"sales","hint":"category_1_amount_1","pipeline":[{"$group":{"_id":"$category","total":{"$sum":"$amount"}}}],"cursor":{}}'
);

QUERY PLAN                                                                                                                                                                                                                                                              
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate (actual rows=10 loops=1)
   Output: bson_repath_and_build('_id'::text, (bson_expression_get(document, 'BSONHEX1500000002000a0000002463617465676f72790000'::bson, true, 'BSONHEX12000000096e6f77001e9afecaa001000000'::bson)), 'total'::text, bsonsum(bson_expression_get(document, 'BSONHEX1300000002000800000024616d6f756e740000'::bson, true, 'BSONHEX12000000096e6f77001e9afecaa001000000'::bson))), (bson_expression_get(document, 'BSONHEX1500000002000a0000002463617465676f72790000'::bson, true, 'BSONHEX12000000096e6f77001e9afecaa001000000'::bson))
   Group Key: bson_expression_get(collection.document, 'BSONHEX1500000002000a0000002463617465676f72790000'::bson, true, 'BSONHEX12000000096e6f77001e9afecaa001000000'::bson)
   Buffers: shared hit=10008
   ->  Index Scan using category_1_amount_1 on documentdb_data.documents_6 collection (actual rows=10000 loops=1)
         Output: bson_expression_get(document, 'BSONHEX1500000002000a0000002463617465676f72790000'::bson, true, 'BSONHEX12000000096e6f77001e9afecaa001000000'::bson), document
         Index Cond: (collection.document @<> 'BSONHEX250000000363617465676f72790016000000106f7264657242795363616e00010000000000'::bson)
         Order By: (collection.document |-<> 'BSONHEX130000001063617465676f7279000100000000'::bson)
         Buffers: shared hit=10008
 Planning:
   Buffers: shared hit=430
(11 rows)

With VERBOSE, the GroupAggregate output shows the category expression used for _id and the amount expression passed to bsonsum. The scan below outputs the category expression and document, but in 0.112 it is a regular Index Scan: PostgreSQL still visits the table and reports 10,008 shared-buffer hits.

DocumentDB 0.113 (after this optimization)

I run the same with DocumentDB 0.113 and the MongoDB-compatible execution plan shows no FETCH stage:

        "queryPlanner": {
          "winningPlan": {
            "stage": "IXSCAN",
            "indexName": "category_1_amount_1",
            "direction": "Forward",
            "isIndexOnlyScan": true,
            "startupCost": 0,
            "totalCost": 0.01,
            "indexFilterSet": [
              {
                "$range": {
                  "category": {
                    "orderByScan": 1
                  }
                }
              }
            ],
            "estimatedTotalKeysExamined": 6
          }
        },

The execution statistics show "totalDocsAnalyzed": 0

        "executionStats": {
          "nReturned": 10000,
          "executionTimeMillis": 12.757,
          "executionStartAtTimeMillis": 0.065,
          "totalDocsExamined": 10000,
          "totalKeysExamined": 10000,
          "executionStages": {
            "stage": "IXSCAN",
            "nReturned": 10000,
            "executionTimeMillis": 12.757,
            "executionStartAtTimeMillis": 0.065,
            "indexName": "category_1_amount_1",
            "totalDocsAnalyzed": 0,
            "totalKeysExamined": 10000,
            "numBlocksFromCache": 9
          }
        }

In the MongoDB-compatible execution plan, IXSCAN is still the stage name. What distinguishes an index only scan, in addition to the absence of FETCH above, is totalDocsAnalyzed which is the number of heap fetches analyzed for MVCC visibility, even if data is not needed because all fields are covered in the index. Here "totalDocsAnalyzed": 0 means that the PostgreSQL visibility map was fresh enough to avoid checking the heap.

I run it from PostgreSQL to get the familiar execution plan where the same is exposed as Heap Fetches: 0:

\pset pager off
SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog,
  documentdb_api_internal, public;

EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document
FROM bson_aggregation_pipeline(
  'test',
  '{"aggregate":"sales","hint":"category_1_amount_1","pipeline":[{"$group":{"_id":"$category","total":{"$sum":"$amount"}}}],"cursor":{}}'
);
                                                                                                                                                                                                                                                             QUERY PLAN                                                                                                                                                                                                                                                              
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate (actual rows=10 loops=1)
   Output: bson_repath_and_build('_id'::text, (bson_expression_get(document, 'BSONHEX1500000002000a0000002463617465676f72790000'::bson, true, 'BSONHEX12000000096e6f7700fd9efecaa001000000'::bson)), 'total'::text, bsonsum(bson_expression_get(document, 'BSONHEX1300000002000800000024616d6f756e740000'::bson, true, 'BSONHEX12000000096e6f7700fd9efecaa001000000'::bson))), (bson_expression_get(document, 'BSONHEX1500000002000a0000002463617465676f72790000'::bson, true, 'BSONHEX12000000096e6f7700fd9efecaa001000000'::bson))
   Group Key: bson_expression_get(collection.document, 'BSONHEX1500000002000a0000002463617465676f72790000'::bson, true, 'BSONHEX12000000096e6f7700fd9efecaa001000000'::bson)
   Buffers: shared hit=9
   ->  Index Only Scan using category_1_amount_1 on documentdb_data.documents_2 collection (actual rows=10000 loops=1)
         Output: bson_expression_get(document, 'BSONHEX1500000002000a0000002463617465676f72790000'::bson, true, 'BSONHEX12000000096e6f7700fd9efecaa001000000'::bson), document
         Index Cond: (collection.document @<> 'BSONHEX250000000363617465676f72790016000000106f7264657242795363616e00010000000000'::bson)
         Order By: (collection.document |-<> 'BSONHEX130000001063617465676f7279000100000000'::bson)
         Heap Fetches: 0
         Buffers: shared hit=9
 Planning:
   Buffers: shared hit=500
(12 rows)

The verbose expressions are equivalent in 0.113, so the aggregate has not been simplified into a different calculation. The access path supplying them has changed to Index Only Scan. Although PostgreSQL labels one output value document, Heap Fetches: 0 and the low number of Buffers: shared hit=9 prove that it is supplied without visiting the table heap.

Conclusion

The improvement is visible on this test case: DocumentDB 0.113 shows Index Only Scan and Heap Fetches: 0 (9 shared hits), versus 10,008 shared hits in DocumentDB 0.112. With this improvement, the DocumentDB access performance is the same as MongoDB.

Here is a summary of the experiments:

Engine and API	Access path	Heap evidence	Shared-buffer hits
MongoDB 8.0	`PROJECTION_COVERED` over `IXSCAN`	`totalDocsExamined: 0`	not reported
DocumentDB 0.112 gateway (MongoDB API)	`FETCH` over `IXSCAN`	10,000 rows fetched	10,008
DocumentDB 0.112 native (SQL function)	`Index Scan`	regular heap access	10,008
DocumentDB 0.113 gateway (MongoDB API)	index-only `IXSCAN`	`totalDocsAnalyzed: 0`	9
DocumentDB 0.113 native (SQL function)	`Index Only Scan`	`Heap Fetches: 0`	9

This feature brings the same behavior as MongoDB where the $group fields can benefit from a covering index. Unlike MongoDB, PostgreSQL does not generally require a hint. When the index-only path is cost-effective—and the visibility map is sufficiently current—the planner can select Index Only Scan automatically. In this experiment, planner settings and the DocumentDB hint were used only to make the comparison deterministic.

NOT IN can be executed as an Anti-Join (NOT EXISTS) in PG19

Franck Pachot — Thu, 24 Sep 2026 12:45:57 +0000

Most databases transform a NOT IN query to NOT EXISTS when possible, because the semantic is the same with NOT NULL resultsets (if they are not, see NOT IN vs. NOT EXISTS: often a data modeling issue). PostgreSQL doesn't and this leads to performance issues (see Recovering TPS After a Cross-Database Migration by Vinay Kumar Dumpa).

PostgreSQL 19 will fix that and transform NOT IN to NOT EXISTS, when NOT NULL is guaranteed, so that a SubPlan or hashed SubPlan becomes an anti-join that can benefit from all join methods: Nested Loop, Merge Join or Hash Join.

I'll demonstrate that at PostgreSQL Conference Europe 2026 (Postgres 19, 20, & Beyond: Live Demos of New Features & Tools) with the following example:

postgres=# explain (analyze OFF, buffers, verbose, costs ON)
 select count(*) from demo
  where   key  not in (
   select key  from demo
);
                                                       QUERY PLAN

------------------------------------------------------------------------  Aggregate  (cost=18693858641.69..18693858641.70 rows=1 width=8)
   Output: count(*)
   ->  Seq Scan on public.demo  
       (cost=0.42..18693857391.67 rows=500005 width=0)
         Output: demo.key, demo.value
         Filter: (NOT (ANY (demo.key = (SubPlan 1).col1)))
         SubPlan 1
           ->  Materialize  (cost=0.42..34887.62 rows=1000010 width=8)
                 Output: demo_1.key
                 ->  Index Only Scan using demo_pkey on public.demo         
                     (cost=0.42..25980.58 rows=1000010 width=8)
                       Output: demo_1.key

This was in PostgreSQL 18 and the EXPLAIN (ANALYZE ON) is still running.

The same in PostgreSQL 19 beta 3 runs in three seconds:

postgres=# explain (analyze on, buffers, verbose, costs off)
 select count(*) from demo
  where   key  not in (
   select key  from demo
);

                                                       QUERY PLAN

--------------------------------------------------------------------------
Aggregate (actual time=3258.030..3258.037 rows=1.00 loops=1)
   Output: count(*)
   Buffers: shared hit=5474
   ->  Merge Anti Join (actual time=3258.022..3258.026 rows=0.00 loops=1)
         Inner Unique: true
         Merge Cond: (demo.key = demo_1.key)
         Buffers: shared hit=5474
         ->  Index Only Scan using demo_pkey on public.demo 
            (actual time=0.052..819.263 rows=1000000.00 loops=1)
               Output: demo.key
               Heap Fetches: 0
               Index Searches: 1
               Buffers: shared hit=2737
         ->  Index Only Scan using demo_pkey on public.demo demo_1 
             (actual time=0.030..815.955 rows=1000000.00 loops=1)
               Output: demo_1.key
               Heap Fetches: 0
               Index Searches: 1
               Buffers: shared hit=2737

I also reproduced the examples from Vinay blog post and got the following:

Version	Index state	Query shape	Main plan node	Execution time
PG18	no FK index	NOT IN	SubPlan 1 re-scan	17444.689 ms
PG18	no FK index	NOT EXISTS	Hash Right Anti Join	2905.400 ms
PG18	no FK index	LEFT JOIN IS NULL	Hash Right Anti Join	3008.620 ms
PG18	FK index	NOT IN	still SubPlan 1	17199.510 ms
PG18	FK index	NOT EXISTS	Nested Loop Anti Join + Index Only Scan, Heap Fetches: 0	4.327 ms
PG18	FK index	LEFT JOIN IS NULL	Nested Loop Anti Join + Index Only Scan, Heap Fetches: 0	2.207 ms
PG19 beta	no FK index	NOT IN	Hash Right Anti Join	2862.253 ms
PG19 beta	no FK index	NOT EXISTS	Hash Right Anti Join	2876.597 ms
PG19 beta	no FK index	LEFT JOIN IS NULL	Hash Right Anti Join	3002.127 ms
PG19 beta	FK index	NOT IN	Nested Loop Anti Join + Index Only Scan, Heap Fetches: 0	3.246 ms
PG19 beta	FK index	NOT EXISTS	Nested Loop Anti Join + Index Only Scan, Heap Fetches: 0	2.459 ms
PG19 beta	FK index	LEFT JOIN IS NULL	Nested Loop Anti Join + Index Only Scan, Heap Fetches: 0	2.297 ms

Here is the best plan for NOT IN in PostgreSQL 18:

QUERY PLAN                                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=5323187.53..5323187.54 rows=2 width=69) (actual time=17180.752..17180.795 rows=3.00 loops=1)
   Buffers: shared hit=26390, temp read=12785 written=3360
   ->  Sort  (cost=5323187.53..5323187.54 rows=2 width=69) (actual time=17003.001..17003.023 rows=3.00 loops=1)
         Sort Key: o.order_date DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=26390, temp read=12785 written=3360
         ->  Nested Loop  (cost=27625.18..5323187.52 rows=2 width=69) (actual time=16548.945..17002.862 rows=3.00 loops=1)
               Join Filter: (p.product_id = o.product_id)
               Rows Removed by Join Filter: 149997
               Buffers: shared hit=26387, temp read=12785 written=3360
               ->  Seq Scan on products p  (cost=0.00..902.00 rows=50000 width=27) (actual time=0.028..53.334 rows=50000.00 loops=1)
                     Buffers: shared hit=402
               ->  Materialize  (cost=27625.18..5320785.53 rows=2 width=50) (actual time=0.118..0.334 rows=3.00 loops=50000)
                     Storage: Memory  Maximum Storage: 17kB
                     Buffers: shared hit=25985, temp read=12785 written=3360
                     ->  Nested Loop  (cost=27625.18..5320785.52 rows=2 width=50) (actual time=5865.203..16536.107 rows=3.00 loops=1)
                           Buffers: shared hit=25985, temp read=12785 written=3360
                           ->  Index Scan using customers_pkey on customers c  (cost=0.29..8.30 rows=1 width=26) (actual time=0.020..0.032 rows=1.00 loops=1)
                                 Index Cond: (customer_id = 42)
                                 Index Searches: 1
                                 Buffers: shared hit=3
                           ->  Bitmap Heap Scan on orders o  (cost=27624.90..5320777.19 rows=2 width=32) (actual time=5865.020..16535.894 rows=3.00 loops=1)
                                 Recheck Cond: ((customer_id = 42) AND (order_id <= 1500000))
                                 Filter: ((order_status <> 'CANCELLED'::text) AND (product_id >= 1000) AND (product_id <= 3000) AND (NOT (ANY (order_id = (SubPlan 1).col1))))
                                 Rows Removed by Filter: 162
                                 Heap Blocks: exact=164
                                 Buffers: shared hit=25982, temp read=12785 written=3360
                                 ->  BitmapAnd  (cost=27624.90..27624.90 rows=149 width=0) (actual time=58.263..58.267 rows=0.00 loops=1)
                                       Buffers: shared hit=4104
                                       ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..6.67 rows=299 width=0) (actual time=0.026..0.027 rows=308.00 loops=1)
                                             Index Cond: (customer_id = 42)
                                             Index Searches: 1
                                             Buffers: shared hit=3
                                       ->  Bitmap Index Scan on orders_pkey  (cost=0.00..27617.97 rows=1495139 width=0) (actual time=58.200..58.200 rows=1500000.00 loops=1)
                                             Index Cond: (order_id <= 1500000)
                                             Index Searches: 1
                                             Buffers: shared hit=4101
                                 SubPlan 1
                                   ->  Materialize  (cost=0.00..67214.78 rows=1530659 width=8) (actual time=0.033..1095.621 rows=816222.33 loops=9)
                                         Storage: Disk  Maximum Storage: 26875kB
                                         Buffers: shared hit=21714, temp read=12785 written=3360
                                         ->  Seq Scan on order_validations v  (cost=0.00..53581.49 rows=1530659 width=8) (actual time=0.032..1653.223 rows=1528882.00 loops=1)
                                               Filter: (validation_state = 'PASSED'::text)
                                               Rows Removed by Filter: 1020517
                                               Buffers: shared hit=21714
 Planning:
   Buffers: shared hit=326 read=1
 Planning Time: 1.935 ms
 JIT:
   Functions: 22
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 0.832 ms (Deform 0.434 ms), Inlining 60.858 ms, Optimization 60.745 ms, Emission 56.440 ms, Total 178.876 ms
 Execution Time: 17199.510 ms

Here is the best plan for NOT IN in PostgreSQL 19:

QUERY PLAN                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1152.11..1152.12 rows=2 width=68) (actual time=3.136..3.156 rows=2.00 loops=1)
   Buffers: shared hit=327 read=8
   ->  Sort  (cost=1152.11..1152.12 rows=2 width=68) (actual time=3.133..3.148 rows=2.00 loops=1)
         Sort Key: o.order_date DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=327 read=8
         ->  Nested Loop  (cost=7.68..1152.10 rows=2 width=68) (actual time=0.535..3.119 rows=2.00 loops=1)
               Buffers: shared hit=324 read=8
               ->  Nested Loop  (cost=7.39..1135.49 rows=2 width=50) (actual time=0.518..3.091 rows=2.00 loops=1)
                     Buffers: shared hit=318 read=8
                     ->  Index Scan using customers_pkey on customers c  (cost=0.29..8.30 rows=1 width=26) (actual time=0.020..0.023 rows=1.00 loops=1)
                           Index Cond: (customer_id = 42)
                           Index Searches: 1
                           Buffers: shared hit=3
                     ->  Nested Loop Anti Join  (cost=7.10..1127.16 rows=2 width=32) (actual time=0.494..3.058 rows=2.00 loops=1)
                           Buffers: shared hit=315 read=8
                           ->  Bitmap Heap Scan on orders o  (cost=6.67..1109.34 rows=4 width=32) (actual time=0.174..2.008 rows=4.00 loops=1)
                                 Recheck Cond: (customer_id = 42)
                                 Filter: ((order_status <> 'CANCELLED'::text) AND (product_id >= 1000) AND (product_id <= 3000) AND (order_id <= 1500000))
                                 Rows Removed by Filter: 305
                                 Heap Blocks: exact=307
                                 Buffers: shared hit=310
                                 ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..6.67 rows=299 width=0) (actual time=0.042..0.042 rows=309.00 loops=1)
                                       Index Cond: (customer_id = 42)
                                       Index Searches: 1
                                       Buffers: shared hit=3
                           ->  Index Only Scan using idx_order_validations_order_id on order_validations v  (cost=0.43..4.45 rows=1 width=8) (actual time=0.257..0.257 rows=0.50 loops=4)
                                 Index Cond: ((order_id = o.order_id) AND (validation_state = 'PASSED'::text))
                                 Heap Fetches: 0
                                 Index Searches: 4
                                 Buffers: shared hit=5 read=8
               ->  Index Scan using products_pkey on products p  (cost=0.29..8.31 rows=1 width=26) (actual time=0.008..0.009 rows=1.00 loops=2)
                     Index Cond: (product_id = o.product_id)
                     Index Searches: 2
                     Buffers: shared hit=6
 Planning:
   Buffers: shared hit=434 read=6
 Planning Time: 2.824 ms
 Execution Time: 3.246 ms

Conclusion

This reproduction confirms the performance issue is mainly a plan-shape problem, not just a missing index. On PostgreSQL 18, the NOT IN form is a materialized SubPlan that repeatedly scans the validation result. Adding a foreign-key index doesn't significantly improve this. Rewriting as NOT EXISTS or LEFT JOIN ... IS NULL allows the optimizer to optimize the anti-join. With a covering index, PostgreSQL switches to a Nested Loop Anti Join with index-only lookup, reducing execution time from seconds to milliseconds.

PostgreSQL 19 beta results differ: it transforms the NOT IN predicate into an anti-join in this workload, enabling effective FK index use. Behavior varies by version, so verify with EXPLAIN (ANALYZE, BUFFERS, SETTINGS) on the target release instead of relying on PostgreSQL 18 behavior.

Microsoft supports many Azure Database for PostgreSQL migrations, including large, critical databases. Feedback from these workloads not only guides customer improvements but also contributes to PostgreSQL core development. In PostgreSQL 19, Richard Guo's proposed patch resolves the NOT IN issue present in PostgreSQL 18. His work shows how feedback from enterprise workload migrations helps improve PostgreSQL optimizer's transformations in future versions

Planned for PostgreSQL 19:

Proposed for PostgreSQL 20

Online enabling checksums in PG ̶1̶9̶ 20 (was 19 beta but reverted)

Franck Pachot — Wed, 23 Sep 2026 21:01:18 +0000

Note: This feature was removed from PostgreSQL 19. We hope it will be included in PostgreSQL 20.

Enabling data checksums is strongly recommended to detect corruption originating in the storage or I/O layer, which can silently lead to incorrect query results. Although PostgreSQL performs basic sanity checks on the page header without checksums, it does not cryptographically verify page contents. As a result, many types of silent corruption could go unnoticed and produce inaccurate query outcomes.

In PostgreSQL 18, data pages across database clusters include a checksum by default, which is verified each time a page is read from disk and recalculated when written. Since checksums are enabled for all databases in a cluster, this applies broadly. However, if you've upgraded from earlier versions, your databases likely lack checksums. You can add checksums using pg_checksums, but the database must be shut down, and it can be a time-consuming process.

PostgreSQL 19 will support enabling checksums online, allowing them to operate in the background while the application remains active, possibly with throttling to lessen workload impact. Here's an example.

Corruption with checksums

To set up a demonstration database without checksums, I initialized it using the --no-data-checksums option with initdb:

podman run -d --replace                        \
 -e POSTGRES_PASSWORD=xxxxxxx                  \
 -e POSTGRES_INITDB_ARGS="--no-data-checksums" \
postgres:19beta3

I created a table with one row containing the 'Hello World!' text:

postgres=# create table hackme as select 'Hello World!' as value
;
CREATE TABLE

postgres=# select distinct value from hackme
;
    value
--------------
 Hello World!

(1 row)

I ensure the dirty page is written to and flushed from the shared buffers:

postgres=# checkpoint
;
CHECKPOINT

postgres=# create extension if not exists pg_buffercache
;
CREATE EXTENSION

postgres=# select * from pg_buffercache_evict_all()
;
 buffers_evicted | buffers_flushed | buffers_skipped
-----------------+-----------------+-----------------
            1200 |               0 |               0

(1 row)

There's no encryption in PostgreSQL, so the data is visible in the file:

postgres=# select current_setting('data_directory')||'/'||pg_relation_filepath('hackme'::regclass) as file
;
                    file
--------------------------------------------
 /var/lib/postgresql/20/docker/base/5/16454

(1 row)

postgres=# \gset

postgres=# \setenv file :file

postgres=# \! cat -v $file | tail -c 42

@^@^@^@^A^@^A^@^B       ^X^@^[Hello World!^@^@^@

postgres=#

With filesystem access, I can modify the data to simulate storage corruption:


postgres=# \! LC_ALL=C sed 's/World!/Hacker/g' $file > /tmp/corrupted.file && cat /tmp/corrupted.file > $file

postgres=# \! cat -v $file | tail -c 42

@^@^@^@^A^@^A^@^B       ^X^@^[Hello Hacker^@^@^@

postgres=#

When PostgreSQL reads the file again, it doesn't detect that the page was modified outside the instance and displays corrupted data:

postgres=# select distinct value from hackme
;
    value
--------------
 Hello Hacker

(1 row)

This is a major problem. Data can be corrupted at any layer below the PostgreSQL instance, and this corruption goes undetected.

Enabling checksums online

Without stopping the instance, I enable checksums:


postgres=# show data_checksums
;
 data_checksums
----------------
 off

(1 row)

postgres=# select pg_enable_data_checksums()
;
 pg_enable_data_checksums
--------------------------

(1 row)

The checksum process is in progress and you can track the updates:

postgres=# select * from pg_stat_progress_data_checksums
;
 pid | datid | datname |  phase   | databases_total | databases_done | relations_total | relations_done | blocks_total | blocks_done
-----+-------+---------+----------+-----------------+----------------+-----------------+----------------+--------------+-------------
 101 |     0 |         | enabling |                 |                |                 |                |              |

(1 row)

postgres=# show data_checksums
;
 data_checksums
----------------
 inprogress-on

(1 row)

Over time, the database is protected by checksums:

postgres=# select * from pg_stat_progress_data_checksums
;
 pid | datid | datname | phase | databases_total | databases_done | relations_total | relations_done | blocks_total | blocks_done
-----+-------+---------+-------+-----------------+----------------+-----------------+----------------+--------------+-------------

(0 rows)

postgres=# show data_checksums
;
 data_checksums
----------------
 on

(1 row)

Checksums are enabled without any downtime.

Corruption with checksums

I do the same as before, flushing the shared buffers and modifying the file directly:


postgres=# select * from pg_buffercache_evict_all()
;
 buffers_evicted | buffers_flushed | buffers_skipped
-----------------+-----------------+-----------------
             991 |               0 |               0

(1 row)

postgres=# \! cat -v $file | tail -c 42

@^@^@^@^A^@^A^@^B       ^X^@^[Hello Hacker^@^@^@

postgres=# \! LC_ALL=C sed 's/Hacker/Franck/g' $file > /tmp/corrupted.file && cat /tmp/corrupted.file > $file

postgres=# \! cat -v $file | tail -c 42

@^@^@^@^A^@^A^@^B       ^X^@^[Hello Franck^@^@^@

Now that each page has a checksum, a read detects any corruption:

postgres=# select distinct value from hackme
;
ERROR:  invalid page in block 0 of relation "base/5/16454"

You can now switch to a standby node or restore from a backup.

Detecting corruption early is crucial for successful recovery. pg_basebackup performs checksum verification:

\! pg_basebackup  -zFtar -D  /var/tmp/backup

WARNING:  checksum verification failed in file "./base/5/16388", block 0: calculated 764A but expected 163F
WARNING:  file "./base/5/16388" has a total of 1 checksum verification failure
WARNING:  1 total checksum verification failure
pg_basebackup: error: checksum error occurred

If you back up your database with a tool that doesn't check backups, validate the restore test with pg_checksums --check.

Conclusion

Data checksums are vital for PostgreSQL, transforming silent storage issues into detectable errors for investigation and recovery. Without them, corrupted data may go unnoticed, as page-header checks can't detect arbitrary page changes.

Enable checksums during cluster setup. For existing clusters, online activation avoids maintenance but isn't zero-impact:

pg_enable_data_checksums() and pg_disable_data_checksums() need superuser access and must run on the primary. Changes are propagated to standbys via WAL.
The operation uses two background-worker slots. Ensure max_worker_processes has enough headroom.
It waits for open transactions and temporary tables in all databases. Long sessions or tables can delay indefinitely.
Checksums are applied when enabling, but reads verify them only after final transition to on.
A crash or restart during inprogress-on requires restarting from scratch.
Standbys may need forced restart points, blocking WAL replay and causing lag, possibly stalling a primary. Reducing max_wal_size beforehand can mitigate this.

Checksums are best seen as part of a larger corruption-detection strategy: enable early, monitor transition, validate backups and replicas, and plan around transaction lifetimes and workload.

EXPLAIN (ANALYZE, IO) in PostgreSQL 19

Franck Pachot — Mon, 21 Sep 2026 17:34:44 +0000

I'll be showcasing some exciting PostgreSQL work, featuring contributions from Microsoft engineers at pgconf.eu: Postgres 19, 20, & Beyond: Live Demos of New Features & Tools. During the demos, we'll explore many execution plans, including a new PostgreSQL 19 feature—the IO option for EXPLAIN.

EXPLAIN (ANALYZE) executes the query and reports runtime statistics. BUFFERS shows logical buffer activity: cache hits and reads. IO goes further, reporting how the read stream behaved: how far ahead PostgreSQL was able to prefetch, how many physical I/O requests were issued, their sizes, the level of concurrency, and how often the consumer had to wait.

For a long time, PostgreSQL relied primarily on the operating system and filesystem for read-ahead. PostgreSQL 17 introduced read streams, giving the executor its own streaming read-ahead mechanism. PostgreSQL 18 added asynchronous I/O infrastructure, including io_method implementations such as worker-based AIO and Linux io_uring. PostgreSQL 19 exposes this activity in EXPLAIN (ANALYZE, IO).

When PostgreSQL knows it will read multiple table blocks, for example during a Seq Scan, Bitmap Heap Scan, or Tid Range Scan, it can use a read stream. The stream looks ahead, combines nearby blocks into larger I/O requests, and keeps buffers pinned ahead of the consumer. That distance is reported in the Prefetch line.

Every time a buffer is handed to the scan node, whether it was already cached or had to be read from storage, PostgreSQL samples the current prefetch depth. This is why the Prefetch numbers are scoped to buffer consumption, not just to physical reads.

Physical I/O requests are reported separately on the I/O line. PostgreSQL records the number of I/O requests issued, the average number of blocks read per request, the number of other I/Os already in progress when a request was submitted, and how often the consumer encountered an I/O that had not yet completed.

Here is an example:

postgres=# explain (analyze, buffers, IO, verbose off, costs off)
postgres-# select * from demo
 ;
                               QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on demo (actual time=0.703..1096.397 rows=1000000.00 loops=1)
   Prefetch: avg=34.56 max=68 capacity=71
   I/O: count=1825 waits=7 size=15.97 in-progress=3.70
   Buffers: shared hit=16310 read=29145
 Planning Time: 0.068 ms
 Execution Time: 1924.887 ms
(6 rows)

The scan touched 16310 + 29145 = 45,455 shared buffers. With the default 8 KB block size, that is about 355 MiB of table data. Of those buffers, 16310 were already in shared buffers, and 29145 had to be read.

The Prefetch line indicates how far ahead the read stream was able to stay:

capacity=71 is the maximum number of buffers this stream was allowed to pin ahead of the consumer.
max=68 indicates the stream reached a peak depth of 68 pinned buffers, close to the limit.
avg=34.56 indicates that, across all 45,455 buffer hand-offs to the scan node, the stream had about 35 buffers pinned ahead on average.

The I/O line reports the physical reads:

count=1825 indicates that PostgreSQL issued 1,825 distinct I/O requests.
size=15.97 indicates that each request read about 16 blocks on average: 29145 / 1825.
in-progress=3.70 indicates that, when a new I/O was submitted, about 3.7 other I/Os were already in progress on average.
waits=7 indicates that only 7 of the 1,825 I/O requests had not completed by the time the consumer reached their first buffer.

That last point is important: waits means that, of the 1,825 I/O requests, only 7 were still unfinished when the scan needed them. The other 1,818 completed early enough that prefetching and asynchronous execution hid their latency.

Let's analyze these further. Little's Law states that the average number of items in a stable system (L) equals the product of the average arrival rate (λ) and the average time each item spends in the system (W).

Two counters are directly reported: in-progress=3.70, indicating the average number of concurrent I/O requests (L), and count=1825, representing completed requests over the total scan time of 1096.397 ms. Assuming I/O requests were issued and completed steadily during this period, the request completion rate λ = count / time = 1825 / 1.096397 s equals approximately 1664.5 requests per second. Applying Little's Law (L = λ · W), we find the average time a request spends in the system from submission to completion as W = L / λ = 3.70 / 1664.5, which is roughly 0.002223 seconds or 2.22 milliseconds.

This is a derived average, not an actual reported value. Using it as a per-request estimate, the reported waits=7—the number of requests the consumer reached before completing—sets an upper limit on total blocked time of 7 × 2.22 ms = 15.56 ms. This represents approximately 15.56 / 1096.397 = 1.42% of the scan's total elapsed time. Keep in mind, this is an upper bound, not an exact measurement, because waits counts events rather than durations. A wait only adds to the remaining latency of a request already in progress, which is at most its average latency of 2.22 ms.

Don't mistake this 1.42% for the total I/O. It's a Seq Scan with most of the work involving I/O, but this isn't visible in the foreground process because most I/O occurred in the background through a read stream that held about 35 buffers pinned ahead on average (avg=34.56). Multiple requests were usually active at once (in-progress=3.70). The I/O operations and row processing largely overlapped for nearly the entire 1096.397 ms. The 1.42% is an upper estimate—based on waits=7 and average I/O latency, not a direct measurement—representing the brief moments when the overlap broke: when the scan ran out of its prefetched buffers and had to pause for a specific request to finish.

In one sentence, this EXPLAIN output tells us that PostgreSQL kept roughly 35 buffers prefetched ahead of the sequential scan, combined 29,145 block reads into 1,825 larger I/O requests of about 16 blocks each, maintained about four concurrent reads on average, and stalled for at most ~1.42% of the scan's time waiting on the 7 I/O requests that weren't ready in time.

Learn PostgreSQL extensibility through a gloriously bad idea: MM/DD/YYYY native datatype and GiST index

Franck Pachot — Wed, 16 Sep 2026 20:02:06 +0000

This project teaches four of PostgreSQL's most powerful features by building something no sane person would ship:

extensions — how you add new capabilities to PostgreSQL in C,
expression indexes — how you index a computed value, not a stored one,
custom operators — how you teach PostgreSQL new verbs like <@ and <->,
specialized indexed types — how you invent a data type and the index that makes it fast.

The bad idea that ties them together: store every date as the literal ten characters MM/DD/YYYY and then make that terrible choice searchable.

⚠️ Do not do this in production. PostgreSQL already has a perfectly good
date type. We are torturing a string on purpose, because a bad-but-simple
example is the fastest way to see what each PostgreSQL feature is really
for. Every section below tells you the sensible thing to do first, then keeps
going for the lesson.

FranckPachot / pg-mm-dd-yyyy

An academic PostgreSQL extension lab for month-first dates, B-tree, and GiST operator classes

pg-mm-dd-yyyy: learn PostgreSQL extensions through a gloriously bad idea

This project teaches four of PostgreSQL's most powerful features by building something no sane person would ship:

extensions — how you add new capabilities to PostgreSQL in C,
expression indexes — how you index a computed value, not a stored one,
custom operators — how you teach PostgreSQL new verbs like <@ and <->,
specialized indexed types — how you invent a data type and the index that makes it fast.

The bad idea that ties them together: store every date as the literal ten characters MM/DD/YYYY and then make that terrible choice searchable.

⚠️ Do not do this in production. PostgreSQL already has a perfectly good date type. We are torturing a string on purpose, because a bad-but-simple example is the fastest way to see what each PostgreSQL feature is really for. Every section below tells you the sensible…

View on GitHub

The story

Imagine you inherit an application from a team that stored all its dates as US text: 09/15/2026, 12/31/2025, and so on. You cannot change the column. The new feature request is deceptively small:

Find me everything that happened in September, any year.

That one sentence walks us straight through all four features. By the end you will have:

reached for an expression index (the correct, boring first answer),
discovered its limits and built a custom type with its own operators,
taught PostgreSQL a containment operator <@ for partial dates like 09/*/*,
added a similarity operator <-> and a GiST index that answers "nearest birthdays" without scanning the whole table.

Why is MM/DD/YYYY such a fun villain? Because it is not sorted on anything a computer likes. Sorting the text groups all the Januaries together, then all the Februaries, while the years jump around inside each group. It is, famously, used by almost nobody outside the United States:

But here is the twist that makes it worth studying: it is ordered on something. It is ordered month first, then day, then year. And it turns out that is exactly the order you want for a surprising number of real questions.

1. The sensible answer first: an expression index

You do not need any of this project to answer "everything in September." You need an expression index, and it is worth understanding why, because it is the tool you should actually reach for 95% of the time.

Here is the inherited table you are not allowed to change:

CREATE TABLE events_text (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    happened_on text NOT NULL   -- e.g. '09/15/2026'
);

An index normally indexes a column. An expression index indexes the result of a function applied to each row. So we write one small, strict, immutable function that turns the text into a real date, and index that:

CREATE FUNCTION us_text_to_date(value text)
RETURNS date
LANGUAGE sql
IMMUTABLE STRICT PARALLEL SAFE
AS $$
    SELECT make_date(
        substring(value FROM 7 FOR 4)::integer,  -- YYYY
        substring(value FROM 1 FOR 2)::integer,  -- MM
        substring(value FROM 4 FOR 2)::integer   -- DD
    )
$$;

CREATE INDEX events_text_chronology_idx
ON events_text (us_text_to_date(happened_on));

make_date even rejects impossible dates like 02/30/2026 for free. Now queries that use the same expression hit the index:

SELECT *
FROM events_text
WHERE us_text_to_date(happened_on) >= date '2026-09-01'
  AND us_text_to_date(happened_on) <  date '2026-10-01';

The row on disk is still the ugly text 09/15/2026; the B-tree quietly stores the derived date. That is the whole point of an expression index: you keep your data as-is and index a better view of it.

There is one sharp edge: the query has to spell the expression exactly the same way as the index, or PostgreSQL won't use it. A cleaner habit is to give the derived value a name with a generated column, so every query refers to one plain column instead of repeating the function:

ALTER TABLE events_text
    ADD COLUMN happened_date date
    GENERATED ALWAYS AS (us_text_to_date(happened_on)) STORED;

CREATE INDEX events_text_chronology_idx
ON events_text (happened_date);

SELECT *
FROM events_text
WHERE happened_date >= date '2026-09-01'
  AND happened_date <  date '2026-10-01';

GENERATED ALWAYS AS ... STORED computes happened_date from happened_on on every insert or update and keeps it in sync automatically — you can't write to it directly, so it can't drift. Now the column, the index, and every query all speak the same simple name, and there is no way to accidentally miss the index by phrasing the expression differently. (The function must be IMMUTABLE, which ours is.) This is still just an expression under the hood; the generated column only gives it a stable, hard-to-misuse name. Verified on PostgreSQL 18, the query above plans as an Index Scan using events_text_chronology_idx with an Index Cond on happened_date.

Why STORED and not VIRTUAL? PostgreSQL 18 added VIRTUAL generated columns (computed on read, no storage) and made them the default. They sound like the perfect fit here, but two rules rule them out for this case:

a VIRTUAL column's expression cannot call a user-defined function, and us_text_to_date is exactly that — PostgreSQL 18 rejects it with "Virtual generated columns that make use of user-defined functions are not yet supported";
you cannot build an index directly on a VIRTUAL column at all ("indexes on virtual generated columns are not supported").

So STORED is the right tool: it pays a little disk to give us a real, indexable column. If your derived value used only built-in functions, VIRTUAL would be viable — but you would still index the underlying expression, not the virtual column itself.

For the pure "which month" question, you can even index the text directly, because every value has the same fixed width:

CREATE INDEX events_text_month_first_idx
ON events_text (happened_on text_pattern_ops);

SELECT * FROM events_text WHERE happened_on LIKE '09/%';

For production, stop here. Or better, store a real date and format it for display. Everything after this point exists to teach you what PostgreSQL lets you build when the boring answer is not enough — and to make the tradeoffs visible.

2. Why keep going? Because month-first is a real question shape

The joke is that US dates are "ordered on nothing." The deeper truth is that they are ordered on month, then day, then year, and some questions genuinely want that order.

A vineyard asks which grape varieties get harvested latest in the season? The month and day matter first; the year just names the vintage.
Who shares a birthday? Month and day matter; the age is often deliberately ignored.
Anniversaries, holidays, seasonal maintenance — all care about where in the year something falls, not its position on a global timeline.

But notice the moment you take "where in the year" seriously, plain ordering stops being enough. Sorting says January comes before February comes before December, so on that line December looks as far from January as possible. Seasonally that is nonsense: a December holiday and a January one are practically neighbours. The question is no longer "what comes before what" but "how close are these two dates in the year?" — and closeness wraps around the calendar.

That is the real lesson hiding inside this silly format: advanced indexes are not only about linear sorting; they are also about distance. A B-tree is a sorting machine and can only ever put values on one line. To rank dates by seasonal nearness — with December next to January — we need an index that understands distance, which is exactly what GiST gives us. Keep that circular-month idea in the back of your mind; it is what motivates the KNN operator later.

So we will take the month-first order seriously and ask: what if MM/DD/YYYY were a native PostgreSQL type, with its own operators and its own index? This is not a claim that Americans invented the format for database search — the W3C notes it is a US convention and 03/04/02 is ambiguous across locales, and the international standard is ISO 8601 YYYY-MM-DD. It is our own playful reinterpretation, turned into a working search policy.

3. Feature: a specialized type (a PostgreSQL extension in C)

This is where the extension comes in. PostgreSQL was built to be extended: you can add a brand-new data type, written in C, that stores and indexes itself natively instead of piggy-backing on text.

The extension adds a type called mmddyyyy whose physical value is exactly the ten displayed bytes:

SELECT '09/15/2026'::mmddyyyy AS value,
       pg_column_size('09/15/2026'::mmddyyyy) AS bytes,
       '09/15/2026'::mmddyyyy::date AS native_date;

   value    | bytes | native_date
------------+-------+-------------
 09/15/2026 |    10 | 2026-09-15

Input is validated strictly: correct separators, leading zeros, real Gregorian month lengths, leap years, and years 0001..9999. The extension also gives you casts to and from date, accessors for month/day/year, comparison operators, and a default B-tree operator class. The pieces of an extension:

mmddyyyy.control and sql/mmddyyyy--0.1.0.sql register the type, functions, casts, and operators with PostgreSQL.
src/mmddyyyy.c implements the behavior in C.
The Makefile compiles it against the PostgreSQL server headers.

Feature: custom operators (starting with sort order)

Here is the first genuinely surprising thing. A B-tree does not know how to compare your values by itself. It asks the type's operator class. So even though the bytes start with the month, we can tell the B-tree to sort them chronologically.

Plain text sorts left to right, so it gets this wrong:

SELECT '12/31/2025'::text < '01/01/2026'::text;  -- false (1 sorts after 0)

But mmddyyyy ships a comparison function, mmddyyyy_cmp, that reads out the year, month, and day and compares them in year, month, day order by calling compare_mmddyyyy:

if (left_year  != right_year)  return left_year  < right_year  ? -1 : 1;
if (left_month != right_month) return left_month < right_month ? -1 : 1;
if (left_day   != right_day)   return left_day   < right_day   ? -1 : 1;
return 0;

We register it as the type's default B-tree operator class, so the same visible spelling now sorts chronologically:

SELECT '12/31/2025'::mmddyyyy < '01/01/2026'::mmddyyyy;  -- true

Create the index with completely ordinary SQL — PostgreSQL picks the default operator class automatically:

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    happened_on mmddyyyy NOT NULL
);

CREATE INDEX events_date_btree ON events (happened_on);

SELECT * FROM events
WHERE happened_on >= '09/01/2026' AND happened_on < '10/01/2026';

The lesson: "ordinary SQL" hides a custom operator. The syntax is normal; the ordering is something we defined in C.

4. Feature: a custom operator for a new question (`<@`)

The B-tree comparison operators compare two complete dates. They are great for "before September 1" or "between two dates." They cannot express the question we started with: does this date belong to September, regardless of day and year?

So we invent a second type, mmddyyyy_pattern, where any component can be * ("I don't care"), and a new operator <@ that reads as "the date is contained in the set the pattern describes."

Pattern	Set of dates it describes
`09//`	Every September date, any day, any year
`/15/`	The 15th of every month, every year
`09/15/*`	Every September 15
`09/*/2026`	Every day in September 2026
`//2026`	Every date in 2026
`09/15/2026`	Exactly one date

SELECT '09/15/2026'::mmddyyyy <@ '09/*/*'::mmddyyyy_pattern;  -- true
SELECT '09/15/2026'::mmddyyyy <@ '*/15/*'::mmddyyyy_pattern;  -- true
SELECT '09/15/2026'::mmddyyyy <@ '10/*/*'::mmddyyyy_pattern;  -- false

This is component equality with wildcards. It is not LIKE, not a text prefix, not a range. The * becomes a bitmask inside the pattern, not a SQL wildcard.

5. Feature: a specialized index (GiST) to make `<@` fast

We can now ask the question, but answering it quickly is a new problem. A normal compound B-tree on (month, day, year) is fast for month = 9 (the leading column) but useless for day = 15 alone, because matching rows are scattered across all twelve month ranges.

The reason is fundamental: a B-tree squeezes three columns onto one ordered line.

(01,01,0001), (01,01,0002), ..., (01,02,0001), ..., (12,31,9999)

month = 9 is one contiguous slice of that line. day = 15 is a little piece inside every month's slice — not contiguous, so the B-tree cannot jump straight to it.

GiST (Generalized Search Tree) is a different kind of index. Instead of one global order, every internal node stores a summary box that bounds its children in each dimension independently:

month {8,9,10}   day [1,31]   year [1980,2030]

That box promises: every date below me has a month in {8,9,10}, a day in [1,31], and a year in [1980,2030] — all at once, and independently. So any specified component can rule the whole subtree out:

09/*/* → month 9 is in the set → maybe, descend.
02/*/* → month 2 is not in the set → skip this entire subtree.
*/15/2050 → year 2050 is outside [1980,2030] → skip, even though the month was a wildcard.

One GiST index handles month-only, day-only, year-only, and any mix:

CREATE INDEX events_date_gist ON events USING gist (happened_on);

SELECT * FROM events WHERE happened_on <@ '09/*/*';
SELECT * FROM events WHERE happened_on <@ '*/15/*';
SELECT * FROM events WHERE happened_on <@ '09/*/2026';

That flexibility is the whole reason GiST exists, and it is why we needed a custom type: a specialized index and a specialized type are designed together.

6. Feature: an ordering operator (`<->`) for "nearest" search

<@ gives a yes/no answer. The next natural question has no yes/no answer: which dates are most similar to September 15, 2026? Should an old September date rank ahead of August 15 the same year? That is a policy, and we make it explicit with a distance operator <->:

SELECT happened_on, happened_on <-> '09/15/2026' AS distance
FROM events
ORDER BY happened_on <-> '09/15/2026'
LIMIT 10;

ORDER BY distance LIMIT k is k-nearest-neighbor (KNN) search. The distance we chose makes month dominate day, and day dominate year, with the month measured around the calendar circle so December and January are neighbours:

The weights create strict tiers: any month difference costs at least 32; any day difference at least 1; every possible year difference stays under 1. So this is seasonal similarity, not elapsed time. Wildcards contribute zero — distance from 09/*/* only measures how far your month is from September. Our GiST operator class registers <-> as an ordering operator and supplies a lower-bound distance for internal boxes, so PostgreSQL can walk the tree in distance order and stop after k rows instead of scoring every row.

7. How the GiST index actually works

The public value and the GiST key are deliberately different things:

flowchart LR
  Input["input 09/15/2026"] --> Parse["mmddyyyy_in validates"]
  Parse --> Heap["heap: 10 text bytes"]
  Heap --> Compress["GiST compress"]
  Compress --> Leaf["leaf point: month {9}, day 15, year 2026"]
  Leaf --> Union["union child keys"]
  Union --> Internal["internal M/D/Y summary box"]
  Pattern["pattern 09/*/*"] --> Consistent["consistent"]
  Internal --> Consistent
  Consistent --> Decision["descend or prune"]

The heap keeps the ten text bytes. The GiST stores a separate 8-byte summary key per entry, because pruning a subtree needs a compact bound. The three fixed-size C structures are:

Structure	Size	Contents
`MmDdYyyy`	10 bytes	The characters `MM/DD/YYYY`, no trailing NUL
`MmDdYyyyPattern`	6 bytes	`int16` year, byte month/day, and a present-field mask
`MmDdYyyyGistKey`	8 bytes	Year range, a 12-bit month bitmap, and a day range

Compile-time assertions (StaticAssertDecl) keep those layouts locked to the INTERNALLENGTH values declared in mmddyyyy--0.1.0.sql, so the C and SQL sides can never drift apart silently.

Months are a circle, not a line

This is the most interesting design detail, and one worth understanding.

Day and year are ordinary ranges: [min, max]. But months wrap around — a subtree holding only December and January dates is a tight seasonal cluster, yet a naive range would record it as [1, 12], the "any month" box that can never prune anything.

So the month component of the GiST key is stored as a 12-bit bitmap (month_mask): bit m−1 is set when some date below has month m. That makes the box for a December/January cluster print as {1,12} — two months — instead of the useless span [1,12]:

({9},[15,15],[2026,2026])     -- a leaf: exactly 09/15/2026
({6,7},[1,31],[1,748])        -- an internal box: June & July, days 1-31, years 1-748
({12,1},[1,31],[2000,2001])   -- a tight winter cluster, NOT "every month"

The bitmap also makes two GiST operations clean:

union (combining child boxes) is a bitwise OR — exact and order-independent, exactly what GiST wants.
distance looks only at months actually present, and measures each one around the circle, so the Dec/Jan box is genuinely near January.

On disk the layout stays 8 bytes: two int16 year bounds, one uint16 month bitmap, two uint8 day bounds.

The operator-class callbacks

A GiST operator class is a set of C functions PostgreSQL calls at the right moments. Ours:

Callback	What it does here
`compress`	Turns a 10-byte leaf date into an 8-byte summary (one month bit, point day/year)
`union`	Combines child summaries: OR the month bitmaps, widen day/year ranges
`consistent`	Given a summary and a pattern, decides if any descendant could match
`penalty`	Scores how much a box must grow to absorb a new entry (month counts most)
`picksplit`	Splits a full page into two groups, preferring tight months
`same`	Tests whether two summaries are identical
`distance`	Exact distance at a leaf; a never-too-large lower bound for a box (for KNN)
`fetch`	Rebuilds the exact `MM/DD/YYYY` text for index-only scans

PostgreSQL owns the hard parts — page layout, tree height, locking, WAL, crash recovery, concurrent splits, and the scan machinery. The operator class only supplies the domain logic. That division of labor is what makes GiST reusable for R-trees, full-text search, ranges, and this calendar experiment alike.

Reading a real GiST page

The benchmark uses the pageinspect extension to read block 0 (the root) directly, so you can see the summary boxes PostgreSQL built:

child_page  (1404,65535)
key         (happened_on)=("({6,7},[1,31],[1,748])")

The block number is the downlink to a child page; offset 65535 (0xFFFF) marks it as an internal downlink rather than a heap row. The key is that child's summary box in the ({months},[day_lo,day_hi],[year_lo,year_hi]) form produced by the type's output function.

8. See the difference: B-tree vs GiST on the full calendar

The heavy lab in lab/compare-indexes.sql generates every representable day from 01/01/0001 through 12/31/9999 — 3,652,059 rows — and builds one compound B-tree and one GiST over the same components:

CREATE INDEX calendar_days_mmddyyyy_btree
ON calendar_days (
    mmddyyyy_month(happened_on),
    mmddyyyy_day(happened_on),
    mmddyyyy_year(happened_on)
)
INCLUDE (happened_on);

CREATE INDEX calendar_days_mmddyyyy_gist
ON calendar_days USING gist (happened_on);

With sequential/bitmap scans and parallelism disabled to expose what each index must visit, one run showed the pattern clearly (exact numbers vary by hardware and build history — rerun the lab to regenerate them):

Predicate	Rows	B-tree buffers	GiST buffers	Lesson
month = 9	299,970	1,481	1,562	Leading B-tree equality is excellent; GiST ties
day = 15	119,988	17,994	2,080	B-tree crosses every month; GiST prunes by day
day = 15, year = 2026	12	17,994	122	B-tree can't narrow; two GiST dimensions prune together
exact 09/15/2026	1	4	10	Fully constrained B-tree descent is leanest

The honest summary: GiST's strength is flexibility, not raw speed. One GiST index answers many shapes of question. In exchange you pay with more storage, overlapping summary boxes, slower builds, and less efficient exact lookups. For a leading-column or fully-specified query, the B-tree still wins. Choosing the right index is about matching the tool to the questions you actually ask.

9. Run it yourself

Docker is the only prerequisite. Everything builds PostgreSQL 17 with the extension compiled under -Wall -Wextra -Werror, so a build failure is a real defect.

Fast correctness suite (73,049 dates, 1900–2099; verifies types, casts, B-tree plans, <@ counts, multi-page GiST, index-only KNN, and KNN vs a forced sequential scan):

bash ./test.sh

powershell.exe -NoProfile -ExecutionPolicy Bypass -File .\test.ps1

Narrated demo (walks from the expression index up to GiST and KNN plans):

docker compose build
docker compose up -d --wait
MSYS_NO_PATHCONV=1 docker compose exec -T postgres \
  psql -X -U postgres -d mmddyyyy_lab -f /project/lab/demo.sql
docker compose down -v

Full 3.65M-row comparison (allow ~1 GiB of Docker disk headroom):

bash ./benchmark.sh

powershell.exe -NoProfile -ExecutionPolicy Bypass -File .\benchmark.ps1

The same suite runs automatically in CI on every push (.github/workflows/ci.yml).

What this project deliberately leaves out

To keep the source readable, some real-world concerns are out of scope:

Input must be canonical MM/DD/YYYY; one-digit fields are rejected.
No BC dates, infinite dates, time zones, or years outside 0001..9999.
<@ uses generic selectivity estimates.
No binary send/receive functions or extension upgrade scripts.
The GiST split heuristic favors clarity over benchmark-tuned packing.
The container builds PostgreSQL 17 only.

None of these change the lessons; they just keep the code small enough to read in one sitting.

Where to go next

PostgreSQL docs: GiST and pageinspect.
Try a vector approach: a cube-based GiST index over (cos θ, sin θ, scaled_year) is a great comparison exercise — see why raw Euclidean distance does not give the strict month > day > year tiers this project enforces.
Read the source: src/mmddyyyy.c

Background on the format

ISO 8601: the international YYYY-MM-DD standard.
W3C date formats: locale ambiguity and US MM/DD/YY.
Unicode CLDR patterns: locale-sensitive date patterns.
MIT on the US date format: the tentative British-inheritance hypothesis.

Final thoughts

Comparing PostgreSQL with other databases while ignoring its extensibility framework misses what makes it different. PostgreSQL was built as an extensible system: you can register a new base type that becomes native, with its own input/output functions and operators backed by C functions, and teach an existing access method how to index that type through an operator class and its support functions. That is exactly what this project does — mmddyyyy is a base type, <@and <->are operators, and mmddyyyy_gist_ops is a GiST operator class. This is far more than a hook for supplying a sort or comparison callback.

The leverage is in the division of responsibilities. The whole extension is on the order of a few hundred lines of C, and none of it touches the hard parts of a database engine. I never wrote a line dealing with MVCC visibility, tuple locking, buffer management, WAL, or crash recovery. My code is responsible only for the domain logic — how a date is parsed, compared, bounded into a GiST key, and scored for distance. Everything that makes an index safe and fast under concurrency and failure — durability, transactional consistency, page-level locking, and recovery — stays in the PostgreSQL core and the GiST access method. You extend the semantics; PostgreSQL keeps the guarantees.

When Did This Transaction Happen? PostgreSQL Snapshots, LSNs, Oracle SCNs, and More

Franck Pachot — Tue, 15 Sep 2026 17:08:27 +0000

When did a database change happen?

This isn't just academic. Incremental replication must order committed changes and resume exactly without locking writers. Migration validation checks if source and target are the same state, even as both change. Audit and incident analysis reconstruct who changed data, when, and when others could see it. Application-log correlation links request timestamps with database transactions and resulting commits.

It has business meaning. An editor changes a document at 10:00🕐, saves at 10:04🕐 to publish it, and gets confirmation at 10:05🕐. Other sessions can't see the uncommitted change at 10:00🕐, but later services might see this timestamp. Which moment should an updated_at field report?

If correlating with an application log, the request or statement time may be correct. If describing the user's experience, it may be wrong: users distinguish "I edited", "I saved", and "I published". These are separate business events. Commit visibility, durability, client acknowledgment, and downstream publication are separate system events.

In a nonblocking MVCC database, there is no universal updated_at. A row version can be created, stay private, become visible at commit, reach replicas, and be shown to users later. The correct timestamp depends on the application's question.

That sounds like one question, but it is at least five:

When did the transaction begin?
Which committed state did a statement read?
When was a new row version created?
When did the transaction become committed and visible?
Which durable log position protects that commit?

PostgreSQL presents different coordinates: xmin:xmax:xip_list for transaction visibility, an LSN for write-ahead log position, and a transaction ID for tuple version changes. None are wall-clock timestamps.

Oracle appears more unified, using the System Change Number (SCN) for read consistency, transaction commits, checkpoints, and recovery. However, claiming "Oracle has only an SCN" is inaccurate, as it also requires a transaction ID, undo address, and redo position. YugabyteDB employs HybridTime as the MVCC read and commit coordinator, but provisional writes initially have different timestamps. SQL Server, MySQL/InnoDB, and MongoDB/WiredTiger split these responsibilities.

The key comparison isn't "which database has the best clock?" but rather: "which coordinator answers which ordering question?"

AI disclaimer: I wrote this with a lot of help from GitHub Copilot. I used it to make the comparison more thorough and to check each equivalence against the original documentation and code. Any interpretation and remaining errors are my own.

One transaction has several times

Consider this deliberately generic sequence:


BEGIN
 -> choose read point
 -> UPDATE
 -> COMMIT (some databases may run the following in different order)
      -> make log durable (disk or replicas)
      -> make changes visible
 -> reply (successful commit)

Some of these events can coincide in one implementation, but they remain different promises:

Moment	Question it answers
Transaction start	When did this unit of work begin?
Read point	Which other transactions are visible to this statement?
Version creation	Which transaction produced this physical row state?
Commit point	From which logical point may new readers see the work?
Durable log point	How far must recovery or replication progress to include it?
Client reply	When did this particular client learn the outcome?

The logical visibility rule is similar in all MVCC systems. Here, v is a candidate row or document version, and q is the query evaluating whether it can see that version:


visible (v,q) = 
  ownTransaction(v,q)
  OR
  ( committed(v) AND ( commitPoint(v) <= readPoint(q) ) )

This is a model, not a specific product implementation. PostgreSQL and InnoDB don't store commitPoint as a per-row scalar. They determine it from transaction ID, status, and active transactions in a snapshot. Oracle and YugabyteDB clarify the logical commit process, but all engines require the ownTransaction exception for uncommitted changes.

Why the commit coordinator is usually somewhere else

This separation exists for a physical reason. The final commit coordinate does not exist when the transaction modifies its first row. By commit time, one transaction may have changed millions of rows, and many dirty pages may already have left memory. Revisiting all of them would make commit latency a function of transaction size and would undermine write-ahead logging's no-force rule: commit should make the log durable, not force every data page.

The common answer is indirection. A row version records a transaction marker or provisional time. Commit publishes the outcome and final coordinate in transaction or log metadata. Readers resolve the marker through that metadata; cleanup later may copy enough info into blocks or final versions to avoid lookup.

The implementations differ, but the pressure is the same:

PostgreSQL tuples retain xmin/xmax. Commit status lives in pg_xact, and the optional pg_commit_ts side data maps XID to wall-clock commit time.
Oracle rows refer through an ITL entry and XID to transaction-table metadata where commit records the SCN. Block cleanout can happen later.
YugabyteDB first writes provisional intents. The status tablet records one final commit HybridTime, and asynchronous apply later creates regular records at that time.
InnoDB rows retain DB_TRX_ID and DB_ROLL_PTR. An internal transaction serialization number is assigned near commit for purge ordering, but it is not copied into each row version.

This explains retention limits. If an engine keeps the XID-to-commit-time mapping as auxiliary metadata, it can age independently of the business row. Recovering an exact commit time years later differs from deciding visibility while the version history is still live.

PostgreSQL: pg_current_snapshot() is a visibility boundary

PostgreSQL documents the text representation of a pg_snapshot as xmin:xmax:xip_list. For example:

select pg_current_snapshot();

 pg_current_snapshot
---------------------
 10:20:10,14,15

The three components have precise meanings:

xmin is the lowest transaction ID that was still active. Lower IDs have completed, either by committing or rolling back.
xmax is one past the highest transaction ID that had completed. IDs at or above it had not completed at snapshot time and are invisible to this snapshot.
xip_list contains the top-level transactions that were still in progress between the two horizons. It does not list subtransaction IDs.

An ID between xmin and xmax that is absent from xip_list has completed. Its commit status then says whether it is visible or dead. This is why the snapshot is a visibility boundary over transaction identities, not a timestamp and not a list of all committed transactions.

There is also an unfortunate name collision. Snapshot xmin and xmax are horizons. Tuple xmin and xmax are transaction IDs in a row-version header: the inserting transaction and, normally, the deleting or superseding transaction. The visibility algorithm relates them, but they are not the same field.

XID order is first-write order, not commit order

A PostgreSQL transaction initially has a virtual transaction ID. A normal 32-bit XID is allocated from a cluster-wide counter when the transaction first writes to the database. A read-only transaction may never get one. Calling pg_current_xact_id() forces allocation; pg_current_xact_id_if_assigned() does not.

The documentation makes the ordering guarantee narrow: a lower XID started writing before a higher XID. It may have started the SQL transaction later, and it may commit much later.

This schedule is possible:

T1 first write   ->   XID 100    ->   remains open
T2 first write   ->   XID 101    ->   commits
Reader snapshot  ->   100:102:100

The reader can see committed work from 101 while 100 is still invisible. A single high-water mark could not describe that state; the exception list is the important part.

This ordering also explains PostgreSQL's famous transaction ID wraparound problem. The XID stored in tuple headers is only 32 bits. Normal XIDs are compared with modulo-2³² arithmetic, so any XID has about two billion values considered older and two billion considered newer. VACUUM must freeze sufficiently old tuple versions before they cross that half-range and appear to come from the future. PostgreSQL's public xid8 adds an epoch for observation, but ordinary heap tuple headers still carry the compact 32-bit XID.

Read time

At READ COMMITTED, each command starts with a new snapshot. Two SELECT statements in one transaction can therefore see different commits. At REPEATABLE READ and SERIALIZABLE, the transaction keeps the snapshot chosen for its first non-transaction-control statement. In all cases, the current transaction's earlier commands require additional self-visibility and command ID rules that are not serialized in the public xmin:xmax:xip_list string.

PostgreSQL can export this read point with pg_export_snapshot() and import it in another transaction with SET TRANSACTION SNAPSHOT. The token remains valid only while the exporting transaction stays open. Parallel pg_dump uses synchronized snapshots so all workers see identical contents, and pg_dump --snapshot can align a dump with another session or a logical replication slot. This is often the right coordinate for comparing a source and target during migration: first agree on the state being compared, then compare the rows.

Update time

An UPDATE normally marks the old tuple with the updater's XID in xmax and creates a replacement tuple with that XID in xmin. The transaction also emits WAL records for WAL-logged storage. At this point, another transaction cannot infer a commit time from the tuple. It sees an XID whose status may still be in progress, committed, or aborted.

Commit time and WAL time

PostgreSQL's pg_lsn is a 64-bit byte position in the WAL stream. WAL records are appended, and their insert positions increase monotonically. The following three positions are deliberately distinct:

select pg_current_wal_insert_lsn(),
             pg_current_wal_lsn(),
             pg_current_wal_flush_lsn();

The insert LSN is the logical end after records have been inserted into shared WAL buffers.
The write LSN is how far those buffers have been written out.
The flush LSN is how far PostgreSQL knows the WAL is on durable storage.

An LSN sampled after an UPDATE does not identify the visibility of that update. Other backends write to the same WAL stream, so their records can be between this transaction's records. The tuple itself does not store its WAL LSN.

There is an important qualification to the slogan "an LSN is only a byte position." For a write transaction, the position of its commit record determines its order among other records in the WAL stream. PostgreSQL's logical decoding API provides a commit_lsn, and the documentation states that concurrent transactions are decoded in commit order.

So these are both true:

A generic current LSN is not a transaction snapshot or a commit time.
The LSN of a specific commit record is a useful order for committed change streams.

That order still does not say which client received its success response first. Group commit can flush several commit records together, and process or network scheduling can reorder the replies. With synchronous_commit set to off, PostgreSQL can report success before that commit record reaches durable storage. Logical decoding waits until the transaction has safely been flushed.

PostgreSQL marks the XID committed in pg_xact. If track_commit_timestamp is on, which is not the default, it also retains a wall-clock commit timestamp that can be queried with pg_xact_commit_timestamp(). The mapping is stored separately under pg_commit_ts and is WAL-logged for recovery and physical replication. It is not added to tuple headers, and vacuum routinely removes old entries once their XIDs are no longer needed. This is optional historical metadata, not the MVCC snapshot coordinate and not a permanent audit trail.

Replication adds more positions, not a global clock

Physical streaming replication turns one WAL position into a pipeline. On the primary, it inserts, writes, and flushes a record. A standby then receives, writes, flushes, and replays it. pg_stat_replication exposes the standby's write_lsn, flush_lsn, and replay_lsn as reported to the sender.

flowchart LR
    I[Primary insert] --> W[Primary write]
    W --> F[Primary flush]
    F --> R[Standby receive]
    R --> SW[Standby write]
    SW --> SF[Standby flush]
    SF --> A[Standby replay]
    A --> V[Visible to standby queries]

The synchronous_commit mode selects which boundary a committing session must wait for. In the usual synchronous-standby configuration:

Mode	Commit may return after
`off`	the local commit record is inserted, with no durability wait; flush can lag by up to three times `wal_writer_delay`
`local`	local durable flush, without waiting for a synchronous standby
`remote_write`	a synchronous standby has written WAL to its operating system
`on`	a synchronous standby has durably flushed WAL
`remote_apply`	a synchronous standby has replayed the commit so queries can see it

These modes change acknowledgment and durability, not the transaction's MVCC snapshot. They also explain why "committed" needs a subject: committed in the primary's transaction state, durable locally, durable remotely, and visible on a standby are distinct observations.

PostgreSQL 19, still in beta as I write this, makes those boundaries directly waitable:

WAIT FOR LSN '0/306EE20';
WAIT FOR LSN '0/306EE20' WITH (MODE 'standby_flush', TIMEOUT '5s');

The default standby_replay mode is useful for read-your-writes on an asynchronous replica. Other modes wait for standby write, standby flush, or primary flush. This does not turn the LSN into an MVCC snapshot: the client must capture the relevant primary LSN, and WAIT FOR compares its numeric value without identifying the timeline. Promotion therefore requires the application to reconsider whether the token still belongs to the expected history.

After failover, an LSN alone is not a universal history identifier. PostgreSQL creates a new timeline when recovery diverges; positions before the fork share history, while post-fork records are identified by their timeline and LSN. Logical replication has another namespace: replication origins can remember a source LSN and source timestamp for replayed transactions, but those values remain coordinates of that source, not a new global commit clock.

Two-phase commit does not create one either. PREPARE TRANSACTION preserves a local XID under a caller-supplied global transaction identifier (GID); its changes remain invisible until COMMIT PREPARED. An external coordinator can use matching GIDs at several databases to obtain an atomic outcome, but each PostgreSQL cluster still has its own XIDs, WAL timelines, LSNs, and clocks.

Finally, now() is not commit time either. It is transaction_timestamp(), fixed at transaction start. statement_timestamp() marks receipt of the current command, and clock_timestamp() reads the changing wall clock. A default such as updated_at default now() therefore records neither the physical update instant nor the commit instant of a long transaction.

Oracle: one SCN family, but not one coordinate

Oracle's SCN is much closer to the single logical clock people often look for. Oracle defines it as a monotonically increasing logical timestamp that orders database events. The same concept appears in several places:

a query SCN identifies the consistent point a statement must read;
a transaction has a start SCN and change SCNs;
commit generates and records a commit SCN;
block and data-file checkpoint SCNs bound recovery work;
Flashback and point-in-time recovery accept SCNs.

Those are related SCN values, not one value assigned at BEGIN and reused for every purpose.

Read time

At Oracle's default READ COMMITTED, a query is consistent to the SCN at which the statement opens. At SERIALIZABLE or READ ONLY, queries use the transaction's read point. If a current block contains changes that are too new, Oracle copies the block and applies undo to build a consistent-read clone.

The SCN still cannot be the entire visibility rule. A session must see its own uncommitted update and exclude another session's uncommitted update, even if both are reading with the same query SCN.

The useful difference is that Oracle exposes the SCN as a historical read coordinate. AS OF SCN asks for the committed state at one point, while VERSIONS BETWEEN SCN returns committed row versions over an interval:

select * from orders as of scn :read_scn;

select versions_startscn, versions_endscn, versions_xid, status
from orders versions between scn :scn_a and :scn_b;

DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER can set the same read point for ordinary queries in a session. These features depend on retained undo, or on Flashback Archive when configured for longer history.

Update time

Oracle allocates a transaction ID at the first DML statement, when it assigns an undo segment and a transaction-table slot. The XID encodes:

undo segment number : slot number : sequence number

An update stores the old values in undo and records transaction information in the data block's interested transaction list (ITL). Rows changed by that transaction refer to its ITL entry. The ITL points through the XID and undo block address (UBA) to the transaction table and undo chain. The number of ITL entries is limited per block. Applying undo to restore a consistent read snapshot also restores previous ITLs, so the list virtually covers undo retention.

Commit time

At commit, Oracle generates a commit SCN and records the committed state in the undo segment's transaction table. LGWR writes the remaining redo and the transaction SCN to the online redo log. By default, the client waits for that redo to be durable; asynchronous commit options can weaken that coupling. Data blocks do not all have to be written at commit.

Oracle may clean transaction information from modified blocks during commit. If it does not clean a block, a later reader finds the XID in the ITL, checks the undo segment header for the transaction's status and commit SCN, and performs delayed block cleanout.

We can summarize it as: The Oracle transaction ID identifies the transaction-table entry from which a reader can discover commit status and commit SCN.

But "just an identifier" hides useful work. The XID also locates the undo segment and slot, distinguishes slot reuse through its sequence number, identifies row-lock ownership, and lets a transaction recognize its own changes. Once the transaction is committed, the commit SCN supplies the logical ordering test.

Putting a commit SCN in a table is a special operation

This indirection is what keeps Oracle commit fast. The database can publish the outcome in the undo-segment transaction table and make the redo durable without visiting every changed row or forcing every dirty block. Transaction-table slots and undo are reusable, and delayed cleanout is visibility machinery, not an indefinite audit history of exact row commit times.

Oracle does have a fascinating exception: USERENV('COMMITSCN'). It is absent from the general USERENV parameter list, but Oracle's current error reference documents two unusually strict rules:

it may be invoked only once in a transaction (ORA-01721);
it must be a top-level expression in an INSERT ... VALUES clause or the right-hand side of an UPDATE assignment (ORA-01725).

While COMMIT_SCN was used for trigger-based logical replication before Oracle acquired redo-based GoldenGate, Oracle Database 23.26.2's DBVERIFY executable is still a concrete consumer:

create table SYS_DBV<pid>$ (myscn number);
insert into SYS_DBV<pid>$ values (userenv('COMMITSCN'));
-- OCITransCommit
select myscn from SYS_DBV<pid>$;
drop table SYS_DBV<pid>$;

It reads the committed value back as an Oracle NUMBER, converts it to a packed SCN, and puts it in the verification context used by block checks. A live SQL trace and TKPROF run confirmed the lifecycle. Between the insert and commit, the server recursively executed UPDATE SYS_DBV<pid>$ SET MYSCN=:1 WHERE ROWID=:2, affecting the inserted row; DBVERIFY then selected the value, locked the table exclusively, and dropped it. This is an operational SCN boundary, not a feature self-test.

The 23.26.2 server binary also names a precommit ... commit scn patch callback. Together, those clues describe something very different from evaluating SYSDATE: one selected stored value is patched with the SCN known on the commit path. The restrictions are also the point. Oracle can arrange this for one explicit target; doing it implicitly to every ordinary row a transaction touched would destroy the fast-commit design.

Commit-SCN materialized-view logs apply the same idea at a system-maintained boundary. CREATE MATERIALIZED VIEW LOG ... WITH COMMIT SCN chooses them over timestamp-based logs. Current server strings show log rows carrying XID$$ and refresh SQL joining that XID to SYS.SNAP_XCMT$, whose observed columns map XID to COMMIT_SCN:

many MLOG$ change rows --XID$$--> one XID / COMMIT_SCN mapping

That is a scalable commit-time join, not a rewrite of the base-table rows. The mapping is maintained and purged for materialized-view refresh. It should not be treated as a permanent application audit table.

This is also separate from XStream and GoldenGate-style capture. XStream was introduced in 11g Release 2, and mines redo into logical change records delivered in committed transaction order. Commit-SCN materialized-view logs were added in 12c Release 1 for fast refresh. One did not simply replace the other: they are different consumers of commit ordering through different capture paths.

Oracle also has redo addresses

SCN is not a byte address in redo. V$LOGMNR_CONTENTS exposes the separation particularly well. For one mined change, it can report:

SCN, START_SCN, and COMMIT_SCN for logical database time;
XIDUSN, XIDSLT, and XIDSQN for transaction identity;
UBAFIL, UBABLK, and UBAREC for the undo record;
RBASQN, RBABLK, and RBABYTE for the redo byte address (RBA).

Oracle's term "log sequence number" names the generation of a redo log file. The RBA adds a block and byte offset to locate an individual redo record. In this sense, PostgreSQL's pg_lsn is closer to an Oracle RBA than to an Oracle SCN.

An SCN is also not an exact wall-clock timestamp. SCN_TO_TIMESTAMP() returns an approximation, usually with three-second precision, and the database retains the mapping for a limited period. ORA_ROWSCN is another trap: it is block-level unless the table was created with ROWDEPENDENCIES; only then does Oracle maintain row-level dependency information. Even in that fine-grained mode, ORA_ROWSCN is only a conservative value greater than or equal to the last modifying transaction's commit SCN, not necessarily that exact SCN. Flashback Version Query uses its own VERSIONS_* pseudocolumns instead.

RAC and distributed databases solve different clock problems

Oracle RAC has several instances opening one database. They must coordinate one database SCN domain across the cluster interconnect. Oracle has changed the implementation over time: older releases exposed MAX_COMMIT_PROPAGATION_DELAY, while current binary strings still name a "broadcast-on-commit SCN mode."

Database links connect independent databases, and the documented guarantee is weaker. Oracle says each system has its own SCN. The systems synchronize their SCNs at the end of each remote SQL statement and at the start and end of each transaction, but cannot keep them absolutely synchronized. A gap can therefore produce a remote read that is consistent yet slightly out of date. A dummy remote query or a transaction boundary forces another synchronization point.

Distributed two-phase commit adds common identity and outcome, not a permanent global clock for all work at all sites. The global transaction ID is the same across participants, and a commit-point site records the decisive outcome. For an in-doubt transaction, DBA_2PC_PENDING.COMMIT# exposes what the documentation calls its global commit number; COMMIT FORCE can even reuse the SCN observed at a site that already committed. That synchronizes the resolution of one distributed transaction. It does not give unrelated transactions in independent databases a total order.

A monotonic coordinate still has a finite representation

There is also a naming fossil. Modern Oracle documentation expands SCN as System Change Number, but the 23.26.2 Instant Client still carries the old ORA-08209 explanation: "The System Commit Number has not yet been initialized." This directly shows that System Commit Number existed in Oracle's terminology and survived in old error text. It is not enough to date a formal rename or to claim that early SCNs represented commits only.

Monotonic does not mean infinite. Oracle 12.2 increased the SCN capability: the documented ORA-24442 is raised when a newer database tries to transfer an SCN that exceeds what a pre-12.2 database or client can represent. Current binaries call this BigSCN, track SCN headroom, and include compatibility rollover paths. This differs from PostgreSQL XID wraparound because the SCN is a forward-moving logical time rather than a circular tuple identity. But RAC coordination and database-link synchronization can propagate higher observed SCNs, so capacity and compatibility are distributed-system concerns, not merely local counters.

YugabyteDB: commit HybridTime becomes the MVCC time

I include YugabyteDB because I have also worked with it, and its more modern distributed architecture makes time part of the consistency protocol, not just a diagnostic label. DocDB stores versions in an LSM tree whose key ends in a HybridTime. A hybrid logical clock (HLC) has a physical and a logical component. It follows causal order and is monotonic on each node, but its physical component should not be confused with a perfectly synchronized wall clock.

Read time

A distributed read chooses a hybrid time ht_read. Each tablet waits until that point is safe to read and normally includes a version when ht_record <= ht_read. Clock uncertainty can reveal a record that might have preceded the request even though its HybridTime is above the first read point; YugabyteDB then advances the read time and restarts the read. This is why the read protocol also carries safe time and local/global limits.

YSQL can also synchronize read points across sessions with the PostgreSQL-compatible pg_export_snapshot() and SET TRANSACTION SNAPSHOT syntax. The exporting transaction must remain open, and current YugabyteDB documentation limits export/import to REPEATABLE READ. This shares one distributed snapshot; it is not arbitrary historical time travel.

Update time: provisional HybridTimes

A distributed transaction does not put uncommitted values directly beside regular visible values. It writes provisional records to a separate RocksDB instance named IntentsDB. The documented primary-intent shape is:

DocumentKey, SubKeys..., LockType, ProvisionalRecordHybridTime
        -> TxnId, Value

The provisional HybridTime is not the commit time. Different intents in one transaction generally have different provisional HybridTimes. The transaction UUID ties them to one status record and lets the transaction see its own intents; other readers do not treat pending intents as committed values.

Commit time: one final HybridTime

The transaction manager asks a transaction status tablet to commit. That tablet chooses the current HybridTime while appending the committed status to its Raft log. Once the status change is replicated, the transaction has one commit HybridTime and all its provisional records become logically visible.

A reader that encounters a not-yet-cleaned intent asks the status tablet. If the transaction committed, the reader treats the intent as if it were already a regular record at the final commit HybridTime.

Cleanup is asynchronous. Each participant later Raft-replicates an apply record containing the transaction ID and commit HybridTime, removes the provisional records, and writes regular records to RegularDB with that final HybridTime. We can summarize it as: YugabyteDB stores a timestamp in both provisional and regular records, but the provisional write timestamp and the final commit timestamp are different.

Raft log positions remain a separate coordinate. Each tablet, including the status tablet, has its own Raft log and operation order. No single cluster-wide Raft byte position exists, analogous to a PostgreSQL WAL LSN. HybridTime provides the cross-tablet MVCC coordinate, while Raft provides replicated order and durability inside each tablet group.

HybridTime orders causality, not an omniscient wall clock

The HLC guarantee is precise. Events on one server receive increasing HybridTimes. If event A sends an RPC that leads to event B on another server, the clock value travels with the message, and B receives a greater HybridTime. That covers causal chains.

The implementation packs HybridTime into an unsigned 64-bit value: physical microseconds occupy the high bits, and 12 low bits hold the logical component. If the logical component fills, YugabyteDB carries it into the physical part. The source notes microsecond accuracy through 2100 and beyond. The 64-bit width provides headroom, but the clock algorithm is what guarantees monotonicity: when the physical clock goes backward, the last physical component is retained and the logical component advances.

Two nodes that have exchanged no relevant messages can still have physical clock skew. Their HLC values are comparable as tuples, but that numeric order does not prove a causal relationship or exact wall-clock order between the independent events. Once they communicate, the lower clock advances to the higher observed value.

This is why the read protocol cannot simply sample any node's HLC and declare the result complete. It calculates a global_limit from physical time plus the configured maximum clock skew, waits for tablet safe time, and restarts when it encounters a possibly earlier event above the chosen read point. Together, the timestamp and the uncertainty protocol provide the guarantee.

SQL Server: XSN, LSN, and `rowversion`

SQL Server exposes almost every possible source of naming confusion.

First, its traditional READ COMMITTED uses locks. Statement snapshots appear when READ_COMMITTED_SNAPSHOT (RCSI) is enabled, and transaction snapshots appear at SNAPSHOT isolation.

For row versioning, SQL Server assigns a transaction sequence number (XSN) when a participating transaction first accesses the version store. For a SNAPSHOT transaction, the engine also records the transactions active at the snapshot. It follows a row's version chain to the newest version whose XSN is below the reader's sequence and was not in that active set. This is conceptually close to PostgreSQL's horizons plus in-progress transactions.

RCSI chooses a new sequence point for each statement. SNAPSHOT keeps the transaction-level view. On update, SQL Server stores the previously committed row image in the version store and links the current row to it. Row-version metadata includes a transaction sequence number and a version pointer. Writers still use write locks, or transaction-ID locks with optimized locking.

SQL Server keeps these identifiers separate:

transaction_id primarily identifies the transaction for locking and is unique only within an instance;
transaction_sequence_num (XSN) identifies a transaction in row versioning and participates in snapshot visibility tests;
LSN identifies a record in one database's transaction log.

Every new log record has a higher LSN than the preceding record. The DMV sys.dm_tran_database_transactions exposes begin, latest, savepoint, and commit LSNs for a database transaction. Change Data Capture makes the meaning especially explicit: __$start_lsn is the commit LSN, groups changes from one transaction, and orders transactions; __$seqval orders changes inside it. CDC stores a separate mapping from commit LSN to commit wall-clock time.

This is a more directly exposed version of the PostgreSQL qualification: an LSN is a log position, and the commit record's position can be used as commit order. It is still not the XSN snapshot boundary or a wall-clock time. A fully durable commit flushes the log before completion; delayed durability can return before it hardens the log.

Finally, the SQL Server rowversion data type is unrelated to all of this. It is an eight-byte database counter placed in rows that declare such a column. It advances on INSERT or UPDATE, even when values are unchanged. It is useful as an optimistic concurrency token, but it is neither a timestamp nor a commit sequence, and the deprecated synonym timestamp makes the name worse.

MySQL/InnoDB: a read view, an undo chain, and two logs

"MySQL" can use different storage engines, so this comparison focuses on InnoDB.

An InnoDB consistent read sees changes committed before its read point, excludes later and uncommitted transactions, and includes its own earlier statements. At the default REPEATABLE READ, the first consistent read establishes the transaction's snapshot. At READ COMMITTED, each consistent read gets a fresh snapshot.

Internally, the read view records transaction-ID limits and the active write transactions. It is the same broad strategy as PostgreSQL and SQL Server snapshot versioning: transaction assignment order plus an active exception set, not a commit timestamp in each row.

InnoDB updates clustered-index records in place. Each record has:

DB_TRX_ID, the six-byte ID of the last transaction to insert or update it;
DB_ROLL_PTR, a seven-byte pointer to the undo record from which an older version can be reconstructed.

At update time, the writer has an InnoDB transaction ID, creates undo, generates redo, changes the current record, and holds the conflicting lock. The ID does not become a commit timestamp when the transaction commits. Commit changes its status and makes the version eligible for new read views.

There is a second internal number, but it is easy to over-translate it. MySQL 8.4 source defines trx->no as a transaction serialization number, initially TRX_ID_MAX, assigned shortly before the transaction moves to COMMITTED_IN_MEMORY. InnoDB puts update undo into history in this order, and a read view's m_low_limit_no tells purge which older transaction histories no view still needs.

This is a commit-near ordering horizon, not a miniature Oracle SCN. It is not stored in clustered records, exposed as a stable application token, or used as wall-clock time. The source even notes that transaction numbers need not follow commit LSN order exactly when transactions use different rollback segments, although causal visibility still preserves the necessary order.

InnoDB's redo log has an ever-increasing LSN. MySQL 8.4 exposes current, flushed-to-disk, and checkpoint LSNs. As in PostgreSQL, redo from concurrent transactions can interleave. The LSN tracks recovery progress, not the read view or a row's commit time.

MySQL then adds a second log at the server layer. The binary log is used for replication and point-in-time recovery. MySQL caches a transactional workload and writes it there as a unit at commit. With the default binlog_order_commits=ON, storage-engine commits are serialized in binary-log order. If it is disabled, transactions in one group may commit in an order different from their binary-log positions.

When GTIDs are enabled, a binary-logged client transaction receives a value of the form:

source_uuid:sequence_number

The sequence number follows commit order on that source. It is an excellent replication identity and ordering coordinate, but it is not DB_TRX_ID, is not stored in each InnoDB row version, and does not define one scalar order across unrelated source UUIDs. original_commit_timestamp is separate wall-clock metadata propagated by replication.

The MySQL server coordinates its binary log and InnoDB through internal two-phase commit. Durability therefore depends on both sides, notably sync_binlog and innodb_flush_log_at_trx_commit, rather than on the MVCC transaction ID.

MongoDB/WiredTiger: three time domains in one stack

MongoDB belongs in this comparison because it was designed around replication, while WiredTiger offers a distinct OLTP storage choice underneath it. The database server and its storage engine do not expose the same time abstraction. At least three time domains coexist:

$clusterTime and operationTime are logical causal tokens returned to clients;
oplog OpTime orders replicated operations within a replica-set history;
WiredTiger transaction IDs and timestamps determine storage-engine visibility and history.

They often carry related BSON Timestamp values, but their roles are not interchangeable.

Read and update time

MongoDB's logical clock is Lamport-like. Servers and drivers gossip $clusterTime, advancing it when they observe a later value. Its BSON Timestamp contains seconds plus an ordinal, but it is an ordering token, not an elapsed-time measurement. operationTime lets a client carry the logical time of an acknowledged operation into a causally dependent one.

A read with read concern "snapshot" uses one atClusterTime. Outside a multi-document transaction, a client may supply it; otherwise, mongos, or a single-member replica set, selects a recent majority-committed snapshot. The storage engine implements that point using a WiredTiger read timestamp and a transaction snapshot.

WiredTiger first gives a writing transaction an internal transaction ID and puts each modification on an in-memory update chain. Snapshot visibility checks both that ID and, for timestamped data, the update's commit timestamp. An ordinary update is initially uncommitted, not automatically prepared. Prepare timestamp and durable timestamp are additional states used only when a transaction actually enters the prepared protocol.

For ordinary unprepared transactions, WiredTiger is no-steal at the transaction level: writes first live in memory and are not written to disk before the whole transaction commits. Rollback can mark those in-memory updates aborted instead of physically undoing pages. The tradeoff is a hard cache constraint. MongoDB aborts an uncommitted transaction that creates excessive WiredTiger cache pressure, and returns TransactionTooLargeForCache for a transaction too large to ever fit. Prepared transactions are a separate protocol with additional persistence rules; they should not be used to describe every ordinary update.

The visible BSON document contains none of this metadata. It has no automatic transaction ID, read timestamp, or commit timestamp field. An ObjectId may encode approximate client-side creation time, and an application may add updatedAt, but neither is database commit time.

Commit, oplog, and durability

On a replica set, the oplog is the ordered history of logical writes. Its ts field is a BSON Timestamp; MongoDB guarantees oplog timestamp uniqueness within one mongod. An OpTime pairs that timestamp with the election term:

OpTime = { ts: Timestamp(seconds, ordinal), t: election_term }

MongoDB supplies logical timestamps from this domain to WiredTiger as commit timestamps for replicated changes. WiredTiger then installs the timestamp on the transaction's internal updates; reconciliation can persist it in an on-disk time window. Multi-document transactions may package many changes into applyOps records, so an oplog entry is not necessarily one BSON document change.

A transaction spanning shards adds distributed prepare and commit coordination. Participants can prepare at different timestamps, and the coordinator chooses one commit timestamp that makes the transaction visible across its participants. Each shard still has its own replica-set oplog; no single byte position covers the whole sharded cluster.

Replica-set status makes the pipeline visible through distinct applied, written, durable, and majority-committed OpTime values. WiredTiger also has a journal LSN for local crash recovery. That LSN is not the oplog token used by replication or change streams. The oplog's separate wall dates and status fields such as lastCommittedWallTime are wall-clock observations, not substitutes for OpTime.

What survives later?

WiredTiger does retain timestamp metadata internally while versions need it. In-memory updates have transaction and timestamp fields. The current on-disk value can carry a time window, and the history store keys older values by B-tree, record key, start timestamp, and a uniqueness counter; its value also carries stop and durable timestamps. None of that becomes a queryable field in the BSON document.

The retention boundaries have different names:

the oldest timestamp is the earliest point at which the application may start a new timestamped read;
the pinned timestamp also accounts for already-running readers and is the real garbage-collection floor;
the stable timestamp is the upper boundary of the state considered stable. Rollback to stable removes updates beyond it after rollback or recovery.

History-store versions disappear when no supported read can need them. Oplog entries disappear when the capped oplog rolls past its retention window. A regular document can therefore outlive every system-maintained path from that document to its original commit OpTime. Long-term audit still requires an application field or a separately retained change history.

The same questions, side by side

Engine	Read coordinate	Update/version marker	Commit coordinate	Durable/log coordinate
PostgreSQL	`pg_snapshot` XID horizons plus active XIDs	Tuple `xmin`/`xmax`; WAL records	Commit-record LSN for decoded logged changes; no commit scalar in ordinary tuples	WAL insert/write/flush LSN
Oracle	Query or transaction SCN plus own-XID rules	ITL XID, UBA, change SCN	Commit SCN	Redo RBA and redo-log sequence; checkpoint SCN
YugabyteDB	Read HybridTime plus safe-time limits	Transaction UUID and provisional HybridTime in `IntentsDB`	Final commit HybridTime	Per-tablet Raft log/OpId; committed status replication
SQL Server	Locks, or XSN plus active set for RCSI/`SNAPSHOT`	XSN and version-chain pointer; transaction ID for locks	Commit-record LSN for logged transactions	Per-database transaction-log LSN
MySQL/InnoDB	Read view over transaction IDs and active writers	`DB_TRX_ID` plus `DB_ROLL_PTR`	Internal `trx->no` for history/purge; GTID/binlog order when enabled	InnoDB redo LSN plus binary-log file/position
MongoDB/WiredTiger	`atClusterTime`; WiredTiger read timestamp plus transaction snapshot	Internal transaction ID and timestamped update chain; no BSON marker	Replica-set oplog timestamp; coordinated commit timestamp for distributed transactions	Oplog `OpTime` and majority point; WiredTiger journal LSN/checkpoint

The table deliberately avoids forcing one-to-one equivalence. Oracle's commit SCN and YugabyteDB's commit HybridTime participate directly in MVCC time. PostgreSQL's commit-record LSN, SQL Server's CDC LSN, and MySQL's GTID are useful for change streams, but they don't make the snapshot stored by a reader.

Can a regular row tell me its exact commit coordinate later?

Usually not. "The engine used this metadata" and "the application can recover it forever from the current row" are very different statements.

Engine	In the ordinary row or document?	Where the exact coordinate may still exist
PostgreSQL	No; tuples store XIDs, not commit LSN or commit timestamp	Commit record in retained WAL; optional `pg_commit_ts` until vacuum removes the XID mapping
Oracle	Generally no; `ORA_ROWSCN` need not be the exact commit SCN	Reusable transaction metadata, retained redo/LogMiner data, or an explicit `USERENV('COMMITSCN')` or commit-SCN MV-log target
YugabyteDB	Not as an ordinary SQL column	The internal regular DocDB key carries final HybridTime while that version survives garbage collection
SQL Server	No application column unless one is designed	Retained transaction log or CDC tables and their LSN-to-time mapping
MySQL/InnoDB	No; `DB_TRX_ID` is a version creator, and `trx->no` is not stored there	Retained undo, binary log/GTID metadata, or other configured change history
MongoDB/WiredTiger	No field in the BSON payload	Internal time windows/history store while retained, or the rolling oplog/change-stream history

The "later" in that question matters. MVCC metadata is retained to serve active or supported historical reads; logs are retained to satisfy recovery and replication policy. Neither lifetime automatically matches an audit requirement.

Wall-clock time is another coordinate

Wall-clock time is useful for audit and diagnosis, but it is a poor substitute for transaction order:

PostgreSQL now() is transaction start; optional commit timestamps are separate and retained only for a limited transaction-ID horizon.
Oracle SCN-to-timestamp conversion is approximate and retained for a limited time.
YugabyteDB HybridTime embeds a physical component but also a logical counter and clock-uncertainty protocol.
SQL Server CDC maps commit LSN to tran_end_time rather than pretending the LSN is a date.
MySQL propagates an original commit timestamp separately from GTID, binlog position, InnoDB transaction ID, and redo LSN.
MongoDB exposes wall-clock dates beside oplog and replica-status OpTime values; the BSON timestamp's seconds-plus-ordinal representation remains a logical replication coordinate.

Two wall-clock readings can be equal, and clocks can be corrected. A client can receive commit responses in an order different from the log's commit records. An updated_at value is normally evaluated while the statement runs, before commit. If an application needs both explanation and deterministic processing, store the timestamp for the intended business event and consume changes with the engine's transactional ordering coordinate.

The practical rule

Before comparing two database numbers, name the promise you need:

For repeatable visibility, keep or export a database snapshot.
For change-stream order and restart, keep a commit LSN, binlog position/GTID, or the database's CDC token.
For durability, wait for the relevant WAL, redo, or Raft flush/apply position required by the configured policy.
For optimistic application updates, use an explicit version token and do not call it commit time.
For human audit time, store a timestamp, but keep a transactional token as the tie-breaker when order matters.
For ordering across independent systems, use a protocol that propagates source identity and causality. Do not compare unrelated XIDs, LSNs, SCNs, oplog positions, or wall clocks as if they shared a namespace.

PostgreSQL's snapshot and WAL LSN are kept separate because visibility and recovery are two distinct processes. This isn’t just about missing metadata. It’s fundamental to how PostgreSQL is designed. WAL recovery updates the physical database by reapplying logged page changes, while MVCC visibility is determined afterward based on heap tuple transaction markers, transaction status, and the reader's snapshot. Importantly, recovery doesn’t need to process every unfinished transaction or undo heap changes before the database can show a consistent state.

That separation benefits the conservative recovery approach. It ensures recovery happens only after a system failure, making a smaller contract more valuable—especially in an open-source database that runs across various operating systems, filesystems, storage solutions, extensions, and support models. It also reduces dependencies for extensions. Usually, a new data type or operator class can reuse existing heap MVCC and index access methods without creating new transaction visibility or crash recovery mechanisms. PostgreSQL indexes typically point to heap tuples and do not contain visibility data themselves. Instead, index-only scans refer to the heap's visibility map.

The boundary is not magic. A genuinely new table or index access method may need its own WAL and visibility work. PostgreSQL provides generic WAL records and custom WAL resource managers for that purpose. The extensibility benefit is that these responsibilities are explicit and localized, not that recovery is free.

Oracle handles many logical ordering challenges in the SCN domain, but XID, undo, and redo addresses are still important. YugabyteDB keeps a final temporal coordinate with committed versions because distributed MVCC requires it, while Raft order stays local to each tablet. SQL Server and InnoDB demonstrate even more valid combinations of these elements. MongoDB/WiredTiger presents them all together in a single stack: logical cluster time, replication OpTime, internal MVCC timestamps, and a separate journal position.

A transaction does not happen at one time. It crosses several boundaries, and each database gives those boundaries different names.

References

This article has been heavily reviewed by AI from the following sources.

PostgreSQL

Oracle Database

YugabyteDB

SQL Server

MySQL/InnoDB

MongoDB and WiredTiger

My level of knowledge is much higher for Oracle Database, PostgreSQL, YugabyteDB and MongoDB than for Microsoft SQL Server and MySQL InnoDB, so AI review was crucial to avoid biases. Please comment if you see anything not correct or where explanation can be improved. This is not an academic exercise, I've seen many queries falling into the trap of thinking that an updated_at column can easily provide incremental changes. I've left the best reference for this at the end: the Ask Tom Question Selecting rows that have changed via timestamp problematic with non-blocking reads with Tom Kyte's explanations.

PostgreSQL MVCC: Why Bloat Doesn't Automatically Mean Expensive Reads

Franck Pachot — Thu, 10 Sep 2026 18:08:08 +0000

One of the most persistent misconceptions about PostgreSQL MVCC is that old row versions accumulate in a chain until VACUUM removes them, making reads increasingly expensive as updates pile up.

That's not how PostgreSQL works. Space amplification is common in MVCC databases because they need to access multiple versions over time, but this doesn't necessarily lead to read amplification. Databases are built to read only the data they need from a larger dataset.

In this article, I'll demonstrate three important facts:

Scans don't walk version chains across pages — Seq Scans examine heap tuples directly and skip invisible ones; Index Scans follow the HOT chain only within a single page; Bitmap Scans behave like one or the other depending on bitmap losiness.
Making space reusable in heap and indexes doesn't wait for vacuum — normal reads perform maintenance with hint bits and opportunistic heap pruning, even with autovacuum disabled.
Index scans pay the visibility cost once, and mark dead entries LP_DEAD to skip future heap visits.

As a result, PostgreSQL can build up dead tuples and index entries, but read amplification doesn't increase proportionally, and space can be reused over time.

Setup

PostgreSQL MVCC is known for space amplification (called bloat), but it doesn't accumulate old versions forever. Some garbage collection (called vacuum) happens in the background. However, this article focuses on read amplification before vacuum. For the purpose of the demo, I created a table and disabled auto-vacuum:

drop table if exists mvcc_demo;

create table mvcc_demo (
 id int,
 a int,
 b int,
 filler text default repeat('x',1000)
);

alter table mvcc_demo set (autovacuum_enabled = off);

create index on mvcc_demo ( a );
create index on mvcc_demo ( b );

insert into mvcc_demo
select n,n,n
from generate_series(1,8) n;

vacuum analyze;

To inspect heap pages, I prepare a helper query that lists all line pointers in a page except those with length zero, which are only small stubs:

create extension if not exists pageinspect;

prepare show_tuples(int,int) as
select page, lp, t_xmin, t_xmax, t_ctid,
  regexp_replace(t_data::text,'^(\\x)(.{8})(.{8})(.{8})(.{8}).*$','id=\\x\2 a=\\x\3 b=\\x\4 filler=\\x\5...'),
  t_infomask, t_infomask2
 from generate_series($1,$2) page, lateral (
  select * from heap_page_items(get_raw_page('mvcc_demo', page)) where lp_len>0
) order by page, lp
;

execute show_tuples(0,1)
;

The initial state shows eight tuples:

 page | lp | t_xmin | t_xmax | t_ctid | t_infomask |                        regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
    0 |  1 |    697 |      0 | (0,1)  |       2306 | id=\x01000000 a=\x01000000 b=\x01000000 filler=\xb00f0000...
    0 |  2 |    697 |      0 | (0,2)  |       2306 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
    0 |  3 |    697 |      0 | (0,3)  |       2306 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
    0 |  4 |    697 |      0 | (0,4)  |       2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
    0 |  5 |    697 |      0 | (0,5)  |       2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
    0 |  6 |    697 |      0 | (0,6)  |       2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
    0 |  7 |    697 |      0 | (0,7)  |       2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
    1 |  1 |    697 |      0 | (1,1)  |       2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
(8 rows)

The value 0 of t_xmax and the t_ctid pointing to itself indicate that the tuple is the current version of the row.

Creating a version chain

I'll update one row several times with the following statement:

postgres=# update mvcc_demo
           set a=a+1
           where id=1
;
UPDATE 1

The first update created a new row version on another page because there was no space on the same page. The original tuple is updated with t_ctid storing the address of the next version, and receives its end of visibility in xmax:

 page | lp | t_xmin | t_xmax | t_ctid | t_infomask |                        regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
    0 |  1 |    697 |    740 | (1,2)  |        258 | id=\x01000000 a=\x01000000 b=\x01000000 filler=\xb00f0000...
    0 |  2 |    697 |      0 | (0,2)  |       2306 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
    0 |  3 |    697 |      0 | (0,3)  |       2306 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
    0 |  4 |    697 |      0 | (0,4)  |       2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
    0 |  5 |    697 |      0 | (0,5)  |       2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
    0 |  6 |    697 |      0 | (0,6)  |       2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
    0 |  7 |    697 |      0 | (0,7)  |       2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
    1 |  1 |    697 |      0 | (1,1)  |       2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
    1 |  2 |    740 |      0 | (1,2)  |      10242 | id=\x01000000 a=\x02000000 b=\x01000000 filler=\xb00f0000...
(9 rows)

This t_ctid is what makes people think every read must follow a growing chain of versions. Ordinary visibility checks don't work that way because:

if the query's read snapshot is between xmin and xmax, this is the right row, and there's no need to get another one
if the tuple is not visible to the snapshot, it is skipped. The scan continues normally and may encounter another version of the same logical row elsewhere in the heap.

Here, the row with id=1 (\x01000000) has two versions in two pages - it's not a HOT (heap-only tuple) update. The new version was inserted on a page with free space.

After a second update, the same happens, but there is free space on the same page, so the new version is inserted there, with two versions of id=1 (\x01000000) on page 1:

 page | lp | t_xmin | t_xmax | t_ctid | t_infomask |                        regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
    0 |  2 |    697 |      0 | (0,2)  |       2306 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
    0 |  3 |    697 |      0 | (0,3)  |       2306 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
    0 |  4 |    697 |      0 | (0,4)  |       2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
    0 |  5 |    697 |      0 | (0,5)  |       2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
    0 |  6 |    697 |      0 | (0,6)  |       2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
    0 |  7 |    697 |      0 | (0,7)  |       2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
    1 |  1 |    697 |      0 | (1,1)  |       2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
    1 |  2 |    740 |    741 | (1,3)  |       8450 | id=\x01000000 a=\x02000000 b=\x01000000 filler=\xb00f0000...
    1 |  3 |    741 |      0 | (1,3)  |      10242 | id=\x01000000 a=\x03000000 b=\x01000000 filler=\xb00f0000...
(9 rows)

Actually, all versions of id=1 (\x01000000) are on the same page because the initial version has disappeared from the first page even without vacuum, proof that free space is released even before vacuum runs. What happened is that the update has read the first page and did some cleanup while the buffer was pinned.

This is proof that garbage collection can happen without vacuum, simply when UPDATE, DELETE, or SELECT reads after the update. It is called opportunistic pruning: pruning is attempted whenever a page's free space heuristically looks low, or a page previously failed to fit an updated tuple. Space reclamation happens during tuple retrieval when the page is full or nearly full (<10% free or fillfactor target) and a buffer cleanup lock can be acquired.

Additionally, when the UPDATE has to move a new version to a different page because there isn't room, it flags the old page as full. Here, there was space to place the new version on the same page.

Here is a third update that adds another version:

 page | lp | t_xmin | t_xmax | t_ctid | t_infomask |                        regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
    0 |  2 |    697 |      0 | (0,2)  |       2306 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
    0 |  3 |    697 |      0 | (0,3)  |       2306 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
    0 |  4 |    697 |      0 | (0,4)  |       2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
    0 |  5 |    697 |      0 | (0,5)  |       2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
    0 |  6 |    697 |      0 | (0,6)  |       2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
    0 |  7 |    697 |      0 | (0,7)  |       2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
    1 |  1 |    697 |      0 | (1,1)  |       2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
    1 |  2 |    740 |    741 | (1,3)  |       9474 | id=\x01000000 a=\x02000000 b=\x01000000 filler=\xb00f0000...
    1 |  3 |    741 |    742 | (1,4)  |       8450 | id=\x01000000 a=\x03000000 b=\x01000000 filler=\xb00f0000...
    1 |  4 |    742 |      0 | (1,4)  |      10242 | id=\x01000000 a=\x04000000 b=\x01000000 filler=\xb00f0000...
(10 rows)

The row id=1 (\x01000000) started with a=1 (\x01000000), then updated to a=2 (\x02000000), a=3 (\x03000000), and a=4 (\x04000000). Because no open transactions need to read those old values, Postgres cleans them up when possible to free space on the page.

In this example, I update an indexed column, so even if the new version lands on the same page, this isn't a HOT update—a separate index entry is created. Because the old version might still be referenced by an index entry, ordinary read-triggered pruning cannot fully discard its line pointer. It is still there with a length of zero, which I filter out with lp_len>0 - so that id=1 (\x01000000), a=1 (\x01000000) disappeared.

However, page defragmentation reclaimed the tuple storage even though the line pointer is retained as a stub, and this space can be reused before any VACUUM runs and removes the line pointer. I update another row, id=2 (\x02000000) in the first page, and the new version fits there in a new line pointer of the same page:

postgres=# update mvcc_demo
           set a=a+1
           where id=2
;
UPDATE 1
postgres=# execute show_tuples(0,1)
;
 page | lp | t_xmin | t_xmax | t_ctid | t_infomask |                        regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
    0 |  2 |    697 |    743 | (0,8)  |        258 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
    0 |  3 |    697 |      0 | (0,3)  |       2306 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
    0 |  4 |    697 |      0 | (0,4)  |       2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
    0 |  5 |    697 |      0 | (0,5)  |       2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
    0 |  6 |    697 |      0 | (0,6)  |       2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
    0 |  7 |    697 |      0 | (0,7)  |       2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
    0 |  8 |    743 |      0 | (0,8)  |      10242 | id=\x02000000 a=\x03000000 b=\x02000000 filler=\xb00f0000...
    1 |  1 |    697 |      0 | (1,1)  |       2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
    1 |  2 |    740 |    741 | (1,3)  |       9474 | id=\x01000000 a=\x02000000 b=\x01000000 filler=\xb00f0000...
    1 |  3 |    741 |    742 | (1,4)  |       9474 | id=\x01000000 a=\x03000000 b=\x01000000 filler=\xb00f0000...
    1 |  4 |    742 |      0 | (1,4)  |      10498 | id=\x01000000 a=\x04000000 b=\x01000000 filler=\xb00f0000...
(11 rows)

The two versions of id=2 (\x02000000) are on the same page. This page is now full again, and another update, on id =3 (\ x03000000), will need to insert its new version on another page:

postgres=# update mvcc_demo
           set a=a+1
           where id=3
;
UPDATE 1
postgres=# execute show_tuples(0,1)
;
 page | lp | t_xmin | t_xmax | t_ctid | t_infomask |                        regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
    0 |  2 |    697 |    743 | (0,8)  |       1282 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
    0 |  3 |    697 |    744 | (1,5)  |        258 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
    0 |  4 |    697 |      0 | (0,4)  |       2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
    0 |  5 |    697 |      0 | (0,5)  |       2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
    0 |  6 |    697 |      0 | (0,6)  |       2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
    0 |  7 |    697 |      0 | (0,7)  |       2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
    0 |  8 |    743 |      0 | (0,8)  |      10498 | id=\x02000000 a=\x03000000 b=\x02000000 filler=\xb00f0000...
    1 |  1 |    697 |      0 | (1,1)  |       2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
    1 |  2 |    740 |    741 | (1,3)  |       9474 | id=\x01000000 a=\x02000000 b=\x01000000 filler=\xb00f0000...
    1 |  3 |    741 |    742 | (1,4)  |       9474 | id=\x01000000 a=\x03000000 b=\x01000000 filler=\xb00f0000...
    1 |  4 |    742 |      0 | (1,4)  |      10498 | id=\x01000000 a=\x04000000 b=\x01000000 filler=\xb00f0000...
    1 |  5 |    744 |      0 | (1,5)  |      10242 | id=\x03000000 a=\x04000000 b=\x03000000 filler=\xb00f0000...
(12 rows)

This is about storage amplification. PostgreSQL uses more space than undo-based MVCC because it copies the whole row. Undo-based MVCC databases typically reconstruct an older row version from smaller undo information during visibility checks. PostgreSQL can evaluate visibility directly against heap tuples and transaction status information rather than reconstructing an earlier page image from undo records.

For now, this tells us nothing about read amplification. Let's run a SELECT and count the number of pages visited.

Sequential scans do not follow CTID chains

Let's run a simple table scan:

explain (analyze, buffers, wal, costs off, summary off)
select ctid, xmin, xmax, *
from mvcc_demo;

                             QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on mvcc_demo (actual time=0.032..0.038 rows=8.00 loops=1)
   Buffers: shared hit=3 dirtied=2
   WAL: records=3 fpi=2 bytes=12736 fpi bytes=12580
 Planning:
   Buffers: shared hit=19
(5 rows)

                             QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on mvcc_demo (actual time=0.015..0.018 rows=8.00 loops=1)
   Buffers: shared hit=2
(2 rows)

The first read did some cleanup, and this opportunistic pruning generated some WAL records, but the next sequential scans read each page only once. With a Seq Scan, the versions invisible to the read snapshot are ignored without following any chain.

A sequential scan iterates through line pointers on the page and checks visibility (HeapTupleSatisfiesVisibility, HeapTupleSatisfiesMVCC, or the batched HeapTupleSatisfiesMVCCBatch in heapam.c). Old/dead versions are simply other heap tuples on the page (or other pages) that fail the visibility check and get skipped. The scan doesn't have to build another version of the page or follow a chain of versions, and is as simple as:

tuple visible? -> return it and continue on the same page
tuple invisible? -> skip it and continue on the same page

The same applies to the HOT updates chain: Sequential scans don't need to pay attention to HOT links because they scan every line pointer on the page anyway. The cost is driven primarily by pages read, not by versions count, so there's no version-chain read amplification.

Reads perform cleanup without VACUUM

I executed show_tuples(0,1) to observe that after the SELECT, some old versions were cleaned, and some infomasks changed:

 page | lp | t_xmin | t_xmax | t_ctid |                        regexp_replace                        | t_infomask | t_infomask2
------+----+--------+--------+--------+--------------------------------------------------------------+------------+-------------
    0 |  4 |    697 |      0 | (0,4)  | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000... |       2306 |           4
    0 |  5 |    697 |      0 | (0,5)  | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000... |       2306 |           4
    0 |  6 |    697 |      0 | (0,6)  | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000... |       2306 |           4
    0 |  7 |    697 |      0 | (0,7)  | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000... |       2306 |           4
    0 |  8 |    743 |      0 | (0,8)  | id=\x02000000 a=\x03000000 b=\x02000000 filler=\xb00f0000... |      10498 |           4
    1 |  1 |    697 |      0 | (1,1)  | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000... |       2306 |           4
    1 |  2 |    740 |    741 | (1,3)  | id=\x01000000 a=\x02000000 b=\x01000000 filler=\xb00f0000... |       9474 |           4
    1 |  3 |    741 |    742 | (1,4)  | id=\x01000000 a=\x03000000 b=\x01000000 filler=\xb00f0000... |       9474 |           4
    1 |  4 |    742 |      0 | (1,4)  | id=\x01000000 a=\x04000000 b=\x01000000 filler=\xb00f0000... |      10498 |           4
    1 |  5 |    744 |      0 | (1,5)  | id=\x03000000 a=\x04000000 b=\x03000000 filler=\xb00f0000... |      10498 |           4
(10 rows)

I displayed t_infomask2 to confirm that these updates are not HOT updates even when successor versions happen to be stored on the same page. The low value 4 corresponds only to the number of attributes in the tuple. None of the HOT-related flags (HEAP_HOT_UPDATED or HEAP_ONLY_TUPLE) are present.

No VACUUM was executed. The SELECT itself performed maintenance. The pruning operation is opportunistic:

only if the page looks worth pruning
only if a cleanup lock can be obtained immediately
only if tuples appear globally removable

Ordinary reads can do lots of cleanup before vacuum:

set hint bits / discover committed or aborted transactions. Except when checksums are enabled, this generates no WAL by default, since wal_log_hints=off)
prune dead tuples on the page — whether part of a HOT chain or a standalone dead tuple — which includes redirecting the root line pointer (LP_REDIRECT) past removed intermediate members, or marking a fully-dead tuple/chain LP_DEAD/LP_UNUSED. This is WAL-logged regardless of wal_log_hints.

VACUUM is the primary cleanup mechanism, but readers continuously perform lightweight housekeeping. PostgreSQL isn't the only database that does cleanup on read and generates WAL for it, because visibility changes after COMMIT, and long transactions cannot revisit all updated pages during a commit.

Index scans must check visibility from heap at least once

The table is too small for the planner to choose an index scan, but I force it with pg_plan_advice (introduced in PostgreSQL 19 - I'm using the beta version while writing this article):

load 'pg_plan_advice';

set pg_plan_advice.advice =
'INDEX_SCAN(mvcc_demo mvcc_demo_a_idx)
 NO_GATHER(mvcc_demo)';

I query for the value a=1 \x01000000 which has no rows after my updates:

postgres=# explain (analyze, buffers, wal, costs off, summary off)
           select ctid, xmin, xmax, * from mvcc_demo where a=1
;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using mvcc_demo_a_idx on mvcc_demo (actual time=0.011..0.012 rows=0.00 loops=1)
   Index Cond: (a = 1)
   Index Searches: 1
   Buffers: shared hit=3 dirtied=1
   WAL: records=1 fpi=1 bytes=349 fpi bytes=300
 Planning:
   Buffers: shared hit=6
 Supplied Plan Advice:
   INDEX_SCAN(mvcc_demo mvcc_demo_a_idx) /* matched */
   NO_GATHER(mvcc_demo) /* matched */
(10 rows)

The index scan has read three pages (Buffers: shared hit=3). The index is small and fits in a single page:

postgres=# select *
from bt_page_items('mvcc_demo_a_idx', 1)
;
 itemoffset | ctid  | itemlen | nulls | vars |          data           | dead | htid  | tids
------------+-------+---------+-------+------+-------------------------+------+-------+------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00 | t    | (0,1) |
          2 | (0,2) |      16 | f     | f    | 02 00 00 00 00 00 00 00 | f    | (0,2) |
          3 | (1,2) |      16 | f     | f    | 02 00 00 00 00 00 00 00 | f    | (1,2) |
          4 | (0,3) |      16 | f     | f    | 03 00 00 00 00 00 00 00 | f    | (0,3) |
          5 | (0,8) |      16 | f     | f    | 03 00 00 00 00 00 00 00 | f    | (0,8) |
          6 | (1,3) |      16 | f     | f    | 03 00 00 00 00 00 00 00 | f    | (1,3) |
          7 | (0,4) |      16 | f     | f    | 04 00 00 00 00 00 00 00 | f    | (0,4) |
          8 | (1,4) |      16 | f     | f    | 04 00 00 00 00 00 00 00 | f    | (1,4) |
          9 | (1,5) |      16 | f     | f    | 04 00 00 00 00 00 00 00 | f    | (1,5) |
         10 | (0,5) |      16 | f     | f    | 05 00 00 00 00 00 00 00 | f    | (0,5) |
         11 | (0,6) |      16 | f     | f    | 06 00 00 00 00 00 00 00 | f    | (0,6) |
         12 | (0,7) |      16 | f     | f    | 07 00 00 00 00 00 00 00 | f    | (0,7) |
         13 | (1,1) |      16 | f     | f    | 08 00 00 00 00 00 00 00 | f    | (1,1) |
(13 rows)

Index entries for the previous versions exist, and the heap pages must be read to determine whether they are visible, so both pages have been read. However, some have been marked as "dead".

Again, even without VACUUM, the read did some housekeeping. I ran the same query, and it reads only one page (Buffers: shared hit=1):

postgres=# explain (analyze, buffers, wal, costs off, summary off)
           select ctid, xmin, xmax, * from mvcc_demo where a=1
;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using mvcc_demo_a_idx on mvcc_demo (actual time=0.013..0.014 rows=0.00 loops=1)
   Index Cond: (a = 1)
   Index Searches: 1
   Buffers: shared hit=1
 Supplied Plan Advice:
   INDEX_SCAN(mvcc_demo mvcc_demo_a_idx) /* matched */
   NO_GATHER(mvcc_demo) /* matched */
(7 rows)

When the first index scan finds that an index entry points to a heap tuple known to be dead to all transactions, it can mark the index item LP_DEAD. Future index scans can then skip the corresponding heap fetch.

When an UPDATE is not a HOT update, PostgreSQL inserts new index entries because index tuples store a heap TID (block, offset). The new tuple version has a different TID, so all indexes must point to the new location. This allows index scans to jump directly to the current row version rather than following a chain across pages.

Some databases skip index updates for unchanged columns but follow chains for row pieces stored elsewhere, like migrated rows, overflow pages, or undo data. PostgreSQL favors adding new index entries for each non-HOT update, accepting write overhead to prevent chain-following during reads and reduce read overhead.

This write amplification is reduced further when HOT updates are possible, since no new index entries are created.

A little read amplification can still happen on the first read that reaches a now-dead old tuple through a stale index entry — the visibility check requires a heap visit — but that entry is then marked LP_DEAD, so subsequent index scans skip the heap visit entirely.

Generating more dead versions to see how it scales

To increase the amount of dead data, I update all rows fifty times:

postgres=# update mvcc_demo set a=a+1
\watch c=50 i=0.01

Every row gets fifty new versions. Many obsolete heap tuples and index entries now exist. No VACUUM has run.

I look up rows whose current value is a=54 after fifty update cycles. The initial a=4 is now a=54. I query it two times:

postgres=# explain (analyze, buffers, wal, costs off, summary off)
           select ctid, xmin, xmax, * from mvcc_demo where a=54
;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using mvcc_demo_a_idx on mvcc_demo (actual time=0.052..0.071 rows=3.00 loops=1)
   Index Cond: (a = 54)
   Index Searches: 1
   Buffers: shared hit=9 dirtied=5
   WAL: records=5 fpi=5 bytes=28721 fpi bytes=28476
 Supplied Plan Advice:
   INDEX_SCAN(mvcc_demo mvcc_demo_a_idx) /* matched */
   NO_GATHER(mvcc_demo) /* matched */
(8 rows)

postgres=# explain (analyze, buffers, wal, costs off, summary off)
           select ctid, xmin, xmax, * from mvcc_demo where a=54
;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using mvcc_demo_a_idx on mvcc_demo (actual time=0.020..0.039 rows=3.00 loops=1)
   Index Cond: (a = 54)
   Index Searches: 1
   Buffers: shared hit=4
 Supplied Plan Advice:
   INDEX_SCAN(mvcc_demo mvcc_demo_a_idx) /* matched */
   NO_GATHER(mvcc_demo) /* matched */
(7 rows)

The first read experienced some read amplification (shared hit=9 to get one row), but the next read had enough information in the index to avoid reading more than necessary (Buffers: shared hit=4). Notice that hint-bit updates and index dead-marking can generate WAL, especially when checksums or WAL logging of hints is enabled. In my case, the first lookup generated WAL: records=5.

If MVCC versions accumulated, as many think when they hear about bloat, we would have expected fifty versions for each id, but we observe much less:

postgres=# select id, a, b from mvcc_demo
;
postgres=# select id, a, b, 'x'||to_hex(a) a_hex  from mvcc_demo
;
 id | a  | b | a_hex
----+----+---+-------
  4 | 54 | 4 | x36
  6 | 56 | 6 | x38
  2 | 53 | 2 | x35
  5 | 55 | 5 | x37
  7 | 57 | 7 | x39
  1 | 54 | 1 | x36
  8 | 58 | 8 | x3a
  3 | 54 | 3 | x36
(8 rows)

postgres=# execute show_tuples(0,17)
;
 page | lp | t_xmin | t_xmax | t_ctid  |                        regexp_replace                        | t_infomask | t_infomask2
------+----+--------+--------+---------+--------------------------------------------------------------+------------+-------------
    0 | 57 |    790 |    791 | (0,58)  | id=\x04000000 a=\x32000000 b=\x04000000 filler=\xb00f0000... |       9474 |           4
    0 | 58 |    791 |    792 | (0,59)  | id=\x04000000 a=\x33000000 b=\x04000000 filler=\xb00f0000... |       9474 |           4
    0 | 59 |    792 |    793 | (0,60)  | id=\x04000000 a=\x34000000 b=\x04000000 filler=\xb00f0000... |       9474 |           4
    0 | 60 |    793 |    794 | (0,61)  | id=\x04000000 a=\x35000000 b=\x04000000 filler=\xb00f0000... |       8450 |           4
    0 | 61 |    794 |      0 | (0,61)  | id=\x04000000 a=\x36000000 b=\x04000000 filler=\xb00f0000... |      10498 |           4
    1 | 54 |    790 |    791 | (1,55)  | id=\x06000000 a=\x34000000 b=\x06000000 filler=\xb00f0000... |       9474 |           4
    1 | 55 |    791 |    792 | (1,56)  | id=\x06000000 a=\x35000000 b=\x06000000 filler=\xb00f0000... |       9474 |           4
    1 | 56 |    792 |    793 | (1,57)  | id=\x06000000 a=\x36000000 b=\x06000000 filler=\xb00f0000... |       9474 |           4
    1 | 57 |    793 |    794 | (1,58)  | id=\x06000000 a=\x37000000 b=\x06000000 filler=\xb00f0000... |       8450 |           4
    1 | 58 |    794 |      0 | (1,58)  | id=\x06000000 a=\x38000000 b=\x06000000 filler=\xb00f0000... |      10242 |           4
    2 | 53 |    790 |    791 | (2,54)  | id=\x02000000 a=\x31000000 b=\x02000000 filler=\xb00f0000... |       9474 |           4
    2 | 54 |    791 |    792 | (2,55)  | id=\x02000000 a=\x32000000 b=\x02000000 filler=\xb00f0000... |       9474 |           4
    2 | 55 |    792 |    793 | (2,56)  | id=\x02000000 a=\x33000000 b=\x02000000 filler=\xb00f0000... |       9474 |           4
    2 | 56 |    793 |    794 | (2,57)  | id=\x02000000 a=\x34000000 b=\x02000000 filler=\xb00f0000... |       8450 |           4
    2 | 57 |    794 |      0 | (2,57)  | id=\x02000000 a=\x35000000 b=\x02000000 filler=\xb00f0000... |      10242 |           4
    4 | 48 |    789 |    790 | (4,49)  | id=\x05000000 a=\x32000000 b=\x05000000 filler=\xb00f0000... |       9474 |           4
    4 | 49 |    790 |    791 | (4,50)  | id=\x05000000 a=\x33000000 b=\x05000000 filler=\xb00f0000... |       9474 |           4
    4 | 50 |    791 |    792 | (4,51)  | id=\x05000000 a=\x34000000 b=\x05000000 filler=\xb00f0000... |       9474 |           4
    4 | 51 |    792 |    793 | (4,52)  | id=\x05000000 a=\x35000000 b=\x05000000 filler=\xb00f0000... |       9474 |           4
    4 | 52 |    793 |    794 | (4,53)  | id=\x05000000 a=\x36000000 b=\x05000000 filler=\xb00f0000... |       9474 |           4
    4 | 53 |    794 |      0 | (4,53)  | id=\x05000000 a=\x37000000 b=\x05000000 filler=\xb00f0000... |      10242 |           4
    6 | 48 |    793 |    794 | (6,49)  | id=\x07000000 a=\x38000000 b=\x07000000 filler=\xb00f0000... |       8450 |           4
    6 | 49 |    794 |      0 | (6,49)  | id=\x07000000 a=\x39000000 b=\x07000000 filler=\xb00f0000... |      10242 |           4
   16 |  7 |    791 |    792 | (16,8)  | id=\x01000000 a=\x33000000 b=\x01000000 filler=\xb00f0000... |       9474 |           4
   16 |  8 |    792 |    793 | (16,9)  | id=\x01000000 a=\x34000000 b=\x01000000 filler=\xb00f0000... |       9474 |           4
   16 |  9 |    793 |    794 | (16,10) | id=\x01000000 a=\x35000000 b=\x01000000 filler=\xb00f0000... |       8450 |           4
   16 | 10 |    794 |      0 | (16,10) | id=\x01000000 a=\x36000000 b=\x01000000 filler=\xb00f0000... |      10498 |           4
   17 |  1 |    791 |    792 | (17,2)  | id=\x03000000 a=\x33000000 b=\x03000000 filler=\xb00f0000... |       9474 |           4
   17 |  2 |    792 |    793 | (17,4)  | id=\x03000000 a=\x34000000 b=\x03000000 filler=\xb00f0000... |       9474 |           4
   17 |  3 |    793 |    794 | (17,5)  | id=\x08000000 a=\x39000000 b=\x08000000 filler=\xb00f0000... |       8450 |           4
   17 |  4 |    793 |    794 | (17,6)  | id=\x03000000 a=\x35000000 b=\x03000000 filler=\xb00f0000... |       8450 |           4
   17 |  5 |    794 |      0 | (17,5)  | id=\x08000000 a=\x3a000000 b=\x08000000 filler=\xb00f0000... |      10242 |           4
   17 |  6 |    794 |      0 | (17,6)  | id=\x03000000 a=\x36000000 b=\x03000000 filler=\xb00f0000... |      10498 |           4
(33 rows)

Only the most recent versions are stored here, and most of them are clustered on the same page. This greatly reduces the space, even before autovacuum runs, by pruning unnecessary data.

VACUUM

Note that I disabled VACUUM to show the lightweight housekeeping that happens during reads, but VACUUM remains important to reduce space amplification and avoid XID wraparound:

reclaiming heap space
returning space to the free space map
removing dead index tuples
freezing old transaction IDs
advancing relfrozenxid
preventing transaction ID wraparound

Finally, I run VACUUM to show what happens when auto-vacuum is enabled:

postgres=# vacuum mvcc_demo
;
VACUUM

postgres=# execute show_tuples(0,17)
;
 page | lp | t_xmin | t_xmax | t_ctid  |                        regexp_replace                        | t_infomask | t_infomask2
------+----+--------+--------+---------+--------------------------------------------------------------+------------+-------------
    0 | 61 |    794 |      0 | (0,61)  | id=\x04000000 a=\x36000000 b=\x04000000 filler=\xb00f0000... |      10498 |           4
    1 | 58 |    794 |      0 | (1,58)  | id=\x06000000 a=\x38000000 b=\x06000000 filler=\xb00f0000... |      11010 |           4
    2 | 57 |    794 |      0 | (2,57)  | id=\x02000000 a=\x35000000 b=\x02000000 filler=\xb00f0000... |      11010 |           4
    4 | 53 |    794 |      0 | (4,53)  | id=\x05000000 a=\x37000000 b=\x05000000 filler=\xb00f0000... |      11010 |           4
    6 | 49 |    794 |      0 | (6,49)  | id=\x07000000 a=\x39000000 b=\x07000000 filler=\xb00f0000... |      11010 |           4
   16 | 10 |    794 |      0 | (16,10) | id=\x01000000 a=\x36000000 b=\x01000000 filler=\xb00f0000... |      11010 |           4
   17 |  5 |    794 |      0 | (17,5)  | id=\x08000000 a=\x3a000000 b=\x08000000 filler=\xb00f0000... |      11010 |           4
   17 |  6 |    794 |      0 | (17,6)  | id=\x03000000 a=\x36000000 b=\x03000000 filler=\xb00f0000... |      11010 |           4
(8 rows)

The table has 8 rows across 7 blocks, leaving ample free space. PostgreSQL will reuse this space. The free space was created during previous updates and remains allocated for similar future activity. If an unusual workload generated the free space, you might consider reclaiming it with VACUUM FULL (offline) or REPACK, and set the FILLFACTOR according to future activity.

When PostgreSQL does follow a chain

You may wonder why PostgreSQL stores the CTID of the next version, or may want to understand a specific situation where PostgreSQL has to follow the chain. The claims above are about ordinary MVCC-snapshot reads: plain SELECTs (seq scan, index scan, bitmap scan) never need to walk a version chain across pages. But PostgreSQL does follow CTID links across pages in a few specific situations, all related to write/lock conflict resolution rather than plain visibility checks:

Concurrent UPDATE/DELETE/MERGE in read-committed semantics: If the row being updated or deleted was concurrently modified by another transaction, PostgreSQL must locate the latest version to re-check the query's predicate against it (EvalPlanQual). This walks the version chain, no matter how many pages it spans.
SELECT ... FOR UPDATE/SHARE and other row locking: Locking a row whose key might be referenced by a future version requires locking every descendant in the chain, which can span multiple pages.

Here is an example:

-- start a READ COMMITTED transaction
begin;

-- UPDATE a row one thousand times
update mvcc_demo set a = a+1 where id=4
\watch c=1000 i=0.01

-- in another session, UPDATE the same row
\! psql -c "explain (analyze) update mvcc_demo set a = a+1 where id=4" & sleep 1

-- the concurrent session waits until we commit
commit;

Once my main session commits the thousands of updates, the concurrent update can continue. It has read the old version and followed the chain to the current version, showing a thousand of page reads:

-------------------------------------------------------------------------------------------------------
 Update on mvcc_demo (cost=0.00..6.60 rows=0 width=0) (actual time=202.644..202.644 rows=0.00 loops=1)
   Buffers: shared hit=1012
   ->  Seq Scan on mvcc_demo (cost=0.00..6.60 rows=1 width=10) (actual time=0.015..0.031 rows=1.00 loops=1)
         Filter: (id = 4)
         Rows Removed by Filter: 7
         Buffers: shared hit=6
 Planning:
   Buffers: shared hit=30
 Planning Time: 0.229 ms
 Execution Time: 202.752 ms

These are inherent to correctly implementing snapshot isolation semantics under concurrent writes, not a consequence of accumulated bloat: the chain is only as long as the number of concurrent update attempts on that specific row, not the table's total historical version count.

Deletes

Unlike undo-based databases, an update doesn't need to remove the old index entry. It stays there until VACUUM and is marked LP_DEAD when encountered. The same applies to DELETE, which is like an UPDATE with no new version. In that sense, PostgreSQL sits between undo-based databases, like MySQL, which make all changes in place, and LSM-based databases, like YugabyteDB, which only append new versions or tombstones.

PostgreSQL physically follows what SQL does logically: before commit, the changes are not effective, just intents, where existing rows are tagged by the transaction that is modifying them. Only after commit, and when old versions are no longer needed, do changes become effective and cleanup can occur.

DELETE operations are costly in undo-based databases because they require copying old values, updating indexes, and restoring data if the statement fails or the system crashes. In PostgreSQL, however, DELETE simply records the delete transaction in the tuple header as a tombstone. If the statement or transaction rolls back, it doesn't need to physically restore row contents or index entries. Once the transaction commits and old versions are obsolete, reads can mark the entries with LP_DEAD, and vacuum cleanup removes the entry.

Here is an example where deleting one row generates only one WAL record, showing that PostgreSQL does not physically remove index entries during DELETE execution:

postgres=# explain (analyze, buffers, wal, costs off, summary off)
           delete from mvcc_demo where a=58 -- 0x3a
;

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Delete on mvcc_demo (actual time=0.082..0.082 rows=0.00 loops=1)
   Buffers: shared hit=4 dirtied=2
   WAL: records=1 fpi=2 bytes=10392 fpi bytes=10320
   ->  Bitmap Heap Scan on mvcc_demo (actual time=0.021..0.021 rows=1.00 loops=1)
         Recheck Cond: (a = 58)
         Heap Blocks: exact=1
         Buffers: shared hit=2
         ->  Bitmap Index Scan on mvcc_demo_a_idx (actual time=0.012..0.012 rows=1.00 loops=1)
               Index Cond: (a = 58)
               Index Searches: 1
               Buffers: shared hit=1
(11 rows)

postgres=# execute show_tuples(0,17)
;

 page | lp | t_xmin | t_xmax | t_ctid  |                        regexp_replace                        | t_infomask | t_infomask2
------+----+--------+--------+---------+--------------------------------------------------------------+------------+-------------
    0 | 61 |    794 |      0 | (0,61)  | id=\x04000000 a=\x36000000 b=\x04000000 filler=\xb00f0000... |      10498 |           4
    1 | 58 |    794 |      0 | (1,58)  | id=\x06000000 a=\x38000000 b=\x06000000 filler=\xb00f0000... |      11010 |           4
    2 | 57 |    794 |      0 | (2,57)  | id=\x02000000 a=\x35000000 b=\x02000000 filler=\xb00f0000... |      11010 |           4
    4 | 53 |    794 |      0 | (4,53)  | id=\x05000000 a=\x37000000 b=\x05000000 filler=\xb00f0000... |      11010 |           4
    6 | 49 |    794 |      0 | (6,49)  | id=\x07000000 a=\x39000000 b=\x07000000 filler=\xb00f0000... |      11010 |           4
   16 | 10 |    794 |      0 | (16,10) | id=\x01000000 a=\x36000000 b=\x01000000 filler=\xb00f0000... |      11010 |           4
   17 |  5 |    794 |  20923 | (17,5)  | id=\x08000000 a=\x3a000000 b=\x08000000 filler=\xb00f0000... |       8962 |        8196
   17 |  6 |    794 |      0 | (17,6)  | id=\x03000000 a=\x36000000 b=\x03000000 filler=\xb00f0000... |      11010 |           4
(8 rows)

As the index has not been updated, a SELECT must go to the heap (Heap Fetches: 1) to check visibility, even if no columns are needed from it:



postgres=# explain (analyze, buffers, wal, costs off, summary off)
           select from mvcc_demo where a=58
;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Index Only Scan using mvcc_demo_a_idx on mvcc_demo (actual time=0.036..0.036 rows=0.00 loops=1)
   Index Cond: (a = 58)
   Heap Fetches: 1
   Index Searches: 1
   Buffers: shared hit=3 dirtied=1
   WAL: records=1 fpi=1 bytes=249 fpi bytes=200
(6 rows)

postgres=# explain (analyze, buffers, wal, costs off, summary off)
           select from mvcc_demo where a=58
;

Another WAL record was generated here because the index scan marks the index item's line pointer LP_DEAD after confirming the referenced heap tuple is globally dead. Subsequent reads of this entry do not require reading the heap again (Heap Fetches: 0) if it's an index-only scan:

                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Index Only Scan using mvcc_demo_a_idx on mvcc_demo (actual time=0.012..0.013 rows=0.00 loops=1)
   Index Cond: (a = 58)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared hit=1
(5 rows)

In PostgreSQL, the real work of a DELETE is delayed until after COMMIT and the visibility horizon, when subsequent reads can see the deletion as durable and visible to all transactions. In the meantime, space is used, but there's no version chain to follow or undo to apply.

What this demo actually proves

This demonstration shows that PostgreSQL MVCC is designed around visibility checks and opportunistic maintenance, not repeated traversal of historical versions or change vectors. Dead tuple accumulation does not imply that every query must revisit every old version:

Sequential scans visit every heap line pointer directly and skip invisible tuples, without following version chains.
Ordinary reads opportunistically prune dead tuples (HOT or not) and set hint bits, even without VACUUM running.
Index scans may need to follow a HOT chain within a single pinned buffer, but once a chain is found fully dead, the index entry can be marked LP_DEAD.
Future index scans skip the heap visit entirely for entries already marked LP_DEAD.
The chain is followed either within a page (by index scans, via HOT links) or across pages by DML/row-locking that must recheck predicates against a newly committed version.
Deletes are logical until they are confirmed by COMMIT, in order to avoid unpredictable rollback or recovery time.
VACUUM is still required for space reclamation and index cleanup, but not for every visibility decision.

The practical implication is that PostgreSQL, despite its reputation for "bloat," can handle significant MVCC history while maintaining relatively low read amplification. Some patterns can increase the effect of a particular MVCC implementation, but these are generally more complex than straightforward updates from short OLTP transactions. FILLFACTOR is important to consider to leave space for active versions.

This was tested on Azure Database for PostgreSQL Flexible Server 18 with the pageinspect and pgstattuple extensions enabled, and autovacuum disabled for the test table.

DEV Community: Franck Pachot

The FETCH FIRST story: from ROW_NUMBER() in 12c back to ROWNUM in 23ai, and a LIMIT equivalent for PostgreSQL

First, a transformation to ROW_NUMBER

The first problem was costing

Now, back to ROWNUM

EXISTS and NOT EXISTS returning a row

Copilot, guided by experience

What the trace adds

New syntax, old machinery

What about PostgreSQL?

DocumentDB 0.117: scalar $group index pushdown

Controlled method

MongoDB 8.0 reference

DocumentDB 0.116 (before this optimization)

DocumentDB 0.117 (with the optimization)

DocumentDB and PostgreSQL two-step optimization

Conclusion

DocumentDB 0.109: $match $sort $limit $project covered by index scan

DocumentDB 0.116: $sort $group prefix pushdown

Controlled method

MongoDB 8.0 reference

DocumentDB 0.114 (before this optimization)

DocumentDB 0.116 (after this optimization)

Conclusion

DocumentDB 0.116: $group distinct scan

MongoDB 8.0 reference

DocumentDB 0.114-0 (before this optimization)

DocumentDB 0.116-0 (after this optimization)

Conclusion

DocumentDB 0.113: $group covering index

MongoDB 8.0 reference

DocumentDB 0.112 (before this optimization)

DocumentDB 0.113 (after this optimization)

Conclusion

NOT IN can be executed as an Anti-Join (NOT EXISTS) in PG19

Conclusion

Online enabling checksums in PG ̶1̶9̶ 20 (was 19 beta but reverted)

Corruption with checksums

Enabling checksums online

Corruption with checksums

Conclusion

EXPLAIN (ANALYZE, IO) in PostgreSQL 19

Learn PostgreSQL extensibility through a gloriously bad idea: MM/DD/YYYY native datatype and GiST index

FranckPachot / pg-mm-dd-yyyy

An academic PostgreSQL extension lab for month-first dates, B-tree, and GiST operator classes

pg-mm-dd-yyyy: learn PostgreSQL extensions through a gloriously bad idea

The story

1. The sensible answer first: an expression index

2. Why keep going? Because month-first is a real question shape

3. Feature: a specialized type (a PostgreSQL extension in C)

Feature: custom operators (starting with sort order)

4. Feature: a custom operator for a new question (<@)

5. Feature: a specialized index (GiST) to make <@ fast

6. Feature: an ordering operator (<->) for "nearest" search

7. How the GiST index actually works

Months are a circle, not a line

The operator-class callbacks

Reading a real GiST page

8. See the difference: B-tree vs GiST on the full calendar

9. Run it yourself

What this project deliberately leaves out

Where to go next

Background on the format

Final thoughts

When Did This Transaction Happen? PostgreSQL Snapshots, LSNs, Oracle SCNs, and More

One transaction has several times

Why the commit coordinator is usually somewhere else

PostgreSQL: pg_current_snapshot() is a visibility boundary

XID order is first-write order, not commit order

Read time

Update time

Commit time and WAL time

Replication adds more positions, not a global clock

Oracle: one SCN family, but not one coordinate

Read time

Update time

Commit time

Putting a commit SCN in a table is a special operation

Oracle also has redo addresses

RAC and distributed databases solve different clock problems

4. Feature: a custom operator for a new question (`<@`)

5. Feature: a specialized index (GiST) to make `<@` fast

6. Feature: an ordering operator (`<->`) for "nearest" search

SQL Server: XSN, LSN, and `rowversion`