DEV Community: Franck Pachot

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.

PostgreSQL INCLUDE indexes: what problem are they trying to solve?

Franck Pachot — Mon, 31 Aug 2026 13:42:16 +0000

A common way to explain PostgreSQL's INCLUDE clause is that it helps with index-only scans. That's true, but it misses an important detail. Even before PostgreSQL 11, which introduced the INCLUDE clause, PostgreSQL was already capable of supporting covering indexes:

postgres=# create table demo ( id text primary key, value text)
;
CREATE TABLE

postgres=# insert into demo(id, value)
           select
             md5(g::text),       -- using text to see it with pageinspect
             md5((g%1000)::text) -- using text to see it with pageinspect
from generate_series(1,1000000) g
;
INSERT 0 1000000

postgres=# create index demo_idx1
           on demo(value, id)
;
CREATE INDEX

postgres=# vacuum analyze
;
VACUUM

postgres=# explain (verbose, analyze, buffers)
select id from demo
  where value <= '01'
;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using demo_idx1 on public.demo  (cost=0.55..566.29 rows=8785 width=33) (actual time=0.008..0.551 rows=4000.00 loops=1)
   Output: id
   Index Cond: (demo.value <= '01'::text)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared hit=50
 Query Identifier: -9119705436468283309
 Planning:
   Buffers: shared hit=5
 Planning Time: 0.118 ms
 Execution Time: 0.710 ms

The Index Only Scan covers all filtering (Index Cond: (demo.value <= '01'::text)) and projection (Output: id) without an INCLUDE clause.

Here is another example with output in PostgreSQL 8.4 and 9.3. Index-only scan for B-tree indexes was introduced in 9.2, and INCLUDE came later in 11.

This proves that you don't need an INCLUDE clause to create a covering index for a query. The same is true for other databases: Oracle users have built covering indexes that way for decades without an equivalent to the INCLUDE clause. Adding extra columns to the index can eliminate table access when the query needs those column values.

So why did PostgreSQL introduce a new syntax?

Let's create the covering index using an INCLUDE clause:

postgres=# create index demo_idx2
  on demo(value) include (id)
;
CREATE INDEX

The planner chooses it because the estimated cost is slightly lower, although the difference is too small to appear in the rounded cost shown by EXPLAIN:

postgres=# explain (verbose, analyze, buffers)
select id from demo
  where value <= '01'
;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using demo_idx2 on public.demo  (cost=0.55..566.29 rows=8785 width=33) (actual time=0.015..1.011 rows=4000.00 loops=1)
   Output: id
   Index Cond: (demo.value <= '01'::text)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared hit=50
 Query Identifier: -9119705436468283309
 Planning:
   Buffers: shared hit=5
 Planning Time: 0.132 ms
 Execution Time: 1.323 ms
(11 rows)

The two indexes have the same size in this example:

postgres=# select relname, pg_size_pretty(pg_relation_size(oid))
           from pg_class where relname in ('demo_idx1','demo_idx2')
;
  relname  | pg_size_pretty
-----------+----------------
 demo_idx2 | 91 MB
 demo_idx1 | 91 MB

Both indexes store value and id in their leaf tuples, so both can support index-only scans. The difference is in the B-tree key space. For demo_idx1, ordering is based on (value, id, TID), so both user columns participate in navigation. For demo_idx2, ordering is based on (value, TID), and id is stored as additional payload and is therefore absent from pivot tuples and other upper B-tree levels.

This is visible in the number of index key attributes:

postgres=# select indnatts, indnkeyatts from pg_index 
           where indexrelid='demo_idx1'::regclass
;
 indnatts | indnkeyatts
----------+-------------
        2 |           2


postgres=# select indnatts, indnkeyatts from pg_index 
           where indexrelid='demo_idx2'::regclass
;
 indnatts | indnkeyatts
----------+-------------
        2 |           1

Note (and see Vinh's comment below): the difference is only in the internal B-tree pages, not in the leaves. Since internal pages represent a small fraction of the index, the size difference is tiny (here 11620 vs. 11670 pages) and not visible in the rounded cost displayed by EXPLAIN. More importantly, this difference is generally not a reason to choose INCLUDE instead of adding the column to the key. The primary reasons are the different semantics and behavior: uniqueness enforcement, sort order, operator class requirements, deduplication, and update/maintenance characteristics.

Pageinspect can show more details

I check the index names and enable the extension:

postgres=# \d demo

              Table "public.demo"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 id     | text |           | not null |
 value  | text |           |          |
Indexes:
    "demo_pkey" PRIMARY KEY, btree (id)
    "demo_idx1" btree (value, id)
    "demo_idx2" btree (value) INCLUDE (id)

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

Here are some entries from an internal page of the B-tree with all columns in the key:

postgres=# select * from bt_multi_page_stats('demo_idx1', 1, -1)     
           where type='i' order by blkno desc limit 1
\gset
postgres=# select itemoffset, ctid, itemlen, nulls, vars, dead, htid, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape'), data
           from bt_page_items('demo_idx1', :blkno) order by itemoffset limit 4
;

 itemoffset |   ctid    | itemlen | nulls | vars | dead | htid |                                          encode                                           |                                                                                                          data
------------+-----------+---------+-------+------+------+------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          1 | (11666,0) |       8 | f     | f    |      |      |                                                                                           |
          2 | (11667,2) |      80 | f     | t    |      |      | ffeabd223de0d4eacb9a3e6e53e5448dCe370679455dd6faf22304e9943c1f2fa\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 65 33 37 30 36 37 39 34 35 35 64 64 36 66 61 66 32 32 33 30 34 65 39 39 34 33 63 31 66 32 66 61 00 00 00 00 00 00
          3 | (11668,2) |      80 | f     | t    |      |      | ffeabd223de0d4eacb9a3e6e53e5448dCf9ae9b3ef06aa649c69149c580697d44\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 66 39 61 65 39 62 33 65 66 30 36 61 61 36 34 39 63 36 39 31 34 39 63 35 38 30 36 39 37 64 34 34 00 00 00 00 00 00
(3 rows)

You can recognize the same value (ffeabd223de0d4eacb9a3e6e53e5448d) for two id (e370679455dd6faf22304e9943c1f2fa and f9ae9b3ef06aa649c69149c580697d44)

Here are entries from a leaf page of this index:

postgres=# select * from bt_multi_page_stats('demo_idx1', 1, -1)     
           where type='l' order by blkno desc limit 1
\gset
postgres=# select itemoffset, ctid, itemlen, nulls, vars, dead, htid, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape'), data
           from bt_page_items('demo_idx1', :blkno) order by itemoffset limit 4
;
 itemoffset |   ctid    | itemlen | nulls | vars | dead |   htid    |                                          encode                                           |                                                                                                          data
------------+-----------+---------+-------+------+------+-----------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          1 | (2574,81) |      80 | f     | t    | f    | (2574,81) | ffeabd223de0d4eacb9a3e6e53e5448dCf9ae9b3ef06aa649c69149c580697d44\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 66 39 61 65 39 62 33 65 66 30 36 61 61 36 34 39 63 36 39 31 34 39 63 35 38 30 36 39 37 64 34 34 00 00 00 00 00 00
          2 | (10612,3) |      80 | f     | t    | f    | (10612,3) | ffeabd223de0d4eacb9a3e6e53e5448dCfa133607fa7a0fc0aa0c4eb5af973b0e\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 66 61 31 33 33 36 30 37 66 61 37 61 30 66 63 30 61 61 30 63 34 65 62 35 61 66 39 37 33 62 30 65 00 00 00 00 00 00
          3 | (1291,4)  |      80 | f     | t    | f    | (1291,4)  | ffeabd223de0d4eacb9a3e6e53e5448dCfa684bc1a44ca31270e620437a302582\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 66 61 36 38 34 62 63 31 61 34 34 63 61 33 31 32 37 30 65 36 32 30 34 33 37 61 33 30 32 35 38 32 00 00 00 00 00 00
          4 | (2994,61) |      80 | f     | t    | f    | (2994,61) | ffeabd223de0d4eacb9a3e6e53e5448dCfa81207e0ef8bf7c472f22d1093f9d8c\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 66 61 38 31 32 30 37 65 30 65 66 38 62 66 37 63 34 37 32 66 32 32 64 31 30 39 33 66 39 64 38 63 00 00 00 00 00 00
(4 rows)

You can recognize similar data. The leaves simply add the TID, which is exposed here as htid (heap tuple identifier). The entries are ordered by (value, id):

 fe9fc289c3ff0af142b6d3bead98a923 c8ea21e50b29b5e7081dd060e311fc7f (2574,81)
 fe9fc289c3ff0af142b6d3bead98a923 b22ba7ef4b85c722a92da83a480dd63f (10612,3)
 fe9fc289c3ff0af142b6d3bead98a923 b24f3bf60138d0b322d72be638626170 (1291,4)
 fe9fc289c3ff0af142b6d3bead98a923 b35b3291bb326500fbf6237f593f56ff (2994,61)

Let's look at the other index where id is in INCLUDE rather than the key. The leaves have similar length and format:

postgres=# select * from bt_multi_page_stats('demo_idx2', 1, -1)     
           where type='l' order by blkno desc limit 1
\gset
postgres=# select itemoffset, ctid, itemlen, nulls, vars, dead, htid, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape'), data
           from bt_page_items('demo_idx2', :blkno) order by itemoffset limit 4
;
 itemoffset |    ctid    | itemlen | nulls | vars | dead |    htid    |                                          encode                                           |                                                                                                          data
------------+------------+---------+-------+------+------+------------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          1 | (12081,14) |      80 | f     | t    | f    | (12081,14) | ffeabd223de0d4eacb9a3e6e53e5448dCe54b7a956b88f1a26234f9666332f40f\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 65 35 34 62 37 61 39 35 36 62 38 38 66 31 61 32 36 32 33 34 66 39 36 36 36 33 33 32 66 34 30 66 00 00 00 00 00 00
          2 | (12093,42) |      80 | f     | t    | f    | (12093,42) | ffeabd223de0d4eacb9a3e6e53e5448dC65e6c0b6c51f50809d390bd6af75262f\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 36 35 65 36 63 30 62 36 63 35 31 66 35 30 38 30 39 64 33 39 30 62 64 36 61 66 37 35 32 36 32 66 00 00 00 00 00 00
          3 | (12105,70) |      80 | f     | t    | f    | (12105,70) | ffeabd223de0d4eacb9a3e6e53e5448dC0053cd459922f1a843b5af3e5b384c61\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 30 30 35 33 63 64 34 35 39 39 32 32 66 31 61 38 34 33 62 35 61 66 33 65 35 62 33 38 34 63 36 31 00 00 00 00 00 00
          4 | (12118,17) |      80 | f     | t    | f    | (12118,17) | ffeabd223de0d4eacb9a3e6e53e5448dC37b9ccdafd5e83b27cf23bb1e0089e63\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 33 37 62 39 63 63 64 61 66 64 35 65 38 33 62 32 37 63 66 32 33 62 62 31 65 30 30 38 39 65 36 33 00 00 00 00 00 00
(4 rows)

However, can see that the entries are ordered by (value, TID) even if id values are present in the index entries:

 ffeabd223de0d4eacb9a3e6e53e5448d (12081,14) e54b7a956b88f1a26234f9666332f40f
 ffeabd223de0d4eacb9a3e6e53e5448d (12093,42) 65e6c0b6c51f50809d390bd6af75262f
 ffeabd223de0d4eacb9a3e6e53e5448d (12105,70) 0053cd459922f1a843b5af3e5b384c61
 ffeabd223de0d4eacb9a3e6e53e5448d (12118,17) 37b9ccdafd5e83b27cf23bb1e0089e63

That's one physical difference between the two indexes: the sort order. The columns in INCLUDE are not part of the key. They are just additional payload. This has some pros and cons that we will cover later.

There's another difference in the B-tree internal pages (root and branches) which store key ranges:

postgres=# select * from bt_multi_page_stats('demo_idx2', 1, -1)     
           where type='i' order by blkno desc limit 1
\gset
postgres=# select itemoffset, ctid, itemlen, nulls, vars, dead, htid, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape'), data
           from bt_page_items('demo_idx2', :blkno) order by itemoffset limit 4
;
 itemoffset |     ctid     | itemlen | nulls | vars | dead |    htid    |                            encode                            |                                                          data          
------------+--------------+---------+-------+------+------+------------+--------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------
          1 | (11524,0)    |       8 | f     | f    |      |            |                                                              |
          2 | (11525,4097) |      56 | f     | t    |      | (10941,34) | fe131d7f5a6b38b23cc967316c13dae2\000\000\000\000\000\000\000 | 43 66 65 31 33 31 64 37 66 35 61 36 62 33 38 62 32 33 63 63 39 36 37 33 31 36 63 31 33 64 61 65 32 00 00 00 00 00 00 00
          3 | (11526,4097) |      56 | f     | t    |      | (12015,40) | fe131d7f5a6b38b23cc967316c13dae2\000\000\000\000\000\000\000 | 43 66 65 31 33 31 64 37 66 35 61 36 62 33 38 62 32 33 63 63 39 36 37 33 31 36 63 31 33 64 61 65 32 00 00 00 00 00 00 00
          4 | (11528,4097) |      56 | f     | t    |      | (744,66)   | fe73f687e5bc5280214e0486b273a5f9\000\000\000\000\000\000\000 | 43 66 65 37 33 66 36 38 37 65 35 62 63 35 32 38 30 32 31 34 65 30 34 38 36 62 32 37 33 61 35 66 39 00 00 00 00 00 00 00
(4 rows)

Only the key columns have values in branch pages, not the included columns, because the latter aren't needed to navigate to a key. This keeps upper-level tuples smaller. PostgreSQL can often achieve a similar effect through suffix truncation, which removes trailing key columns from upper B-tree levels when they are not needed to distinguish key ranges. However, defining a column as INCLUDE guarantees that it never participates in navigation and never appears in upper levels.

Columns in INCLUDE vs. in key

The real reason INCLUDE exists is not visible here. PostgreSQL enforces unique constraints and primary keys through unique indexes. You cannot add extra columns to the key without changing the uniqueness definition, so you must add them with INCLUDE so they don't participate in the B-tree key. This also explains why Oracle didn't need to implement an equivalent of INCLUDE: Oracle non-unique B-trees can enforce a unique constraint as long as the unique key is a prefix of the index key.

Another advantage of PostgreSQL's extensibility of data and index types is that, unlike key columns, included columns don't require an operator class for the access method, so you can include columns of types with no defined operator class at all.

INCLUDE columns also receive a maintenance-side benefit: updates that touch only the included column(s) can qualify for PostgreSQL's bottom-up index deletion optimization, which plain key-column updates cannot.

But there are also limitations where you may have to add the column to the key. For example, expressions can't be used as included columns. And if you want to cover the ORDER BY in addition to the WHERE and SELECT, the columns have to be in the key. B-tree deduplication is disabled entirely on any index with a non-key column. This means that an index with INCLUDE may be larger than a comparable plain B-tree even before accounting for the bytes needed to store the included column itself, because it also loses posting-list compression opportunities that would otherwise be available.

Index types

While all examples in this article use B-tree indexes, PostgreSQL also supports INCLUDE with GiST and SP-GiST indexes. This article's discussion of key space, pivot tuples, suffix truncation, deduplication, and uniqueness enforcement is specific to B-tree implementation details.

This was tested on Azure Database for PostgreSQL Flexible Server 18 with the pageinspect extension enabled.

Skip Scan vs. Loose Index Scan

Franck Pachot — Mon, 17 Aug 2026 19:39:02 +0000

Both optimizations avoid (skip) reading (scan) irrelevant leaf pages by repositioning (seek) via a fresh index descent instead of scanning sequentially. This surface similarity explains why people often used "skip scan" and "loose index scan" interchangeably before the distinction was clearly defined. For example, I wrote YugabyteDB Skip Scan aka Loose Index Scan on compound index in 2022 because the two concepts were not clearly distinguished in the PostgreSQL wiki at that time. PostgreSQL implemented neither of those features yet, and YugabyteDB implemented both with the same hybrid scan mechanism. However, today the distinction matters because PostgreSQL 18 has added Skip Scan but not Loose Index Scan.

What they have in common: repositioning an index scan between multiple sub-ranges

A plain range scan seeks once to the start of a range and then reads forward or backward until past the end. Both Skip Scan and Loose Index Scan can instead reposition to a new range, or "grouping" (a run of index entries sharing the same leading-column value), via a new index descent, rather than reading through a single range sequentially. This avoids reading irrelevant leaf pages.

What's different: what happens within each sub-range

Skip Scan avoids reading leaf pages entirely outside a relevant sub-range. PostgreSQL does not skip index entries that might match: within a grouping, it still examines the relevant entries and returns every match, exactly as a normal scan would within that sub-range. Separately, PostgreSQL can sometimes avoid rechecking a scan key on a page whose high key proves that all entries satisfy it (as a CPU optimization), but it does not avoid reading the page.

A Loose Index Scan, by contrast, deliberately reads only the first entry of each range and then jumps to the next range — it never reads the remaining matching rows because it isn't trying to satisfy a later-column predicate, only to enumerate the distinct values of a prefix.

In short:

	Skip Scan	Loose Index Scan
Skips	Leaf pages belonging to sub-ranges that can't satisfy the later-column predicate	Leaf pages/entries belonging to any range after its first entry
Within a matched range	Reads/checks every entry against the later-column	Reads one entry, then repositions to the next range
Purpose of the leading column	Vehicle for enumerating candidate ranges so a later-column predicate can be applied efficiently	The thing being enumerated (e.g., `DISTINCT`) — no later-column predicate needed

They benefit from similar data distributions

Both optimizations benefit when the leading index column has relatively few distinct values and many rows per value. Repeated descents are then cheaper than scanning large groups of entries sequentially.

Skip Scan may be rejected when the number of descents exceeds the cost of scanning the index normally. A Loose Index Scan has the same trade-off: if almost every row has a different prefix value, one descent per value is not worthwhile. Its advantage appears when each group contains enough duplicate entries to make skipping the rest of the group profitable.

The distinction is therefore not primarily the index definition or the data distribution. It is the query's objective: Skip Scan must return all matching rows, whereas Loose Index Scan deliberately returns only one representative per group.

Example

I created a table with an index on two columns:

CREATE EXTENSION IF NOT EXISTS pageinspect;

DROP TABLE IF EXISTS demo CASCADE;

CREATE TABLE demo (
    a       integer NOT NULL,
    b       text    NOT NULL,
    payload text    NOT NULL
);

INSERT INTO demo (a, b, payload)
SELECT
    a,
    repeat(md5(g::text), 28),       -- approximately 900 bytes
    repeat('payload-' || a || '-' || g, 20)
FROM generate_series(1, 8) AS a
CROSS JOIN generate_series(1, 80) AS g;

CREATE INDEX demo_ab_idx ON demo (a, b);

VACUUM ANALYZE demo;

Here is a query that shows the index entries in their logical order:

SELECT
    s.blkno AS index_block,
    i.itemoffset,
    d.a,
    left(d.b, 100) || '...' AS b,
    i.itemlen,
    i.htid,
    i.data as data 
FROM bt_multi_page_stats('demo_ab_idx', 1, -1) AS s
CROSS JOIN LATERAL bt_page_items('demo_ab_idx', s.blkno) AS i
JOIN demo AS d
  ON d.ctid = i.htid
WHERE s.type = 'l'
ORDER BY
    d.a,
    d.b;

My example has 640 rows:

A Skip Scan will scan all values of "a" but may skip the portions of each "a" grouping outside the "b" range, for example WHERE b LIKE '28%':

 index_block | itemoffset | a |                                                    b                                                    | itemlen |  htid   |                                                                                              data
-------------+------------+---+---------------------------------------------------------------------------------------------------------+---------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
           1 |         14 | 1 | 2838023a778dfaecdc212708f721b7882838023a778dfaecdc212708f721b7882838023a778dfaecdc212708f721b7882838... |      72 | (8,2)   | 01 00 00 00 da 00 00 00 80 03 00 40 ff 11 32 38 33 38 30 32 33 61 37 37 38 64 66 61 65 63 64 63 32 31 32 37 30 38 66 37 32 31 62 37 38 38 20 00 ff ff ff 4b 50 31 62 37 38 38 00 00 00 00 00 00
           1 |         15 | 1 | 28dd2c7955ce926456240b2ff0100bde28dd2c7955ce926456240b2ff0100bde28dd2c7955ce926456240b2ff0100bde28dd... |      72 | (12,4)  | 01 00 00 00 da 00 00 00 80 03 00 40 ff 11 32 38 64 64 32 63 37 39 35 35 63 65 39 32 36 34 35 36 32 34 30 62 32 66 66 30 31 30 30 62 64 65 20 00 ff ff ff 4b 50 30 30 62 64 65 00 00 00 00 00 00
           1 |         94 | 2 | 2838023a778dfaecdc212708f721b7882838023a778dfaecdc212708f721b7882838023a778dfaecdc212708f721b7882838... |      72 | (21,3)  | 02 00 00 00 da 00 00 00 80 03 00 40 ff 11 32 38 33 38 30 32 33 61 37 37 38 64 66 61 65 63 64 63 32 31 32 37 30 38 66 37 32 31 62 37 38 38 20 00 ff ff ff 4b 50 31 62 37 38 38 00 00 00 00 00 00
           1 |         95 | 2 | 28dd2c7955ce926456240b2ff0100bde28dd2c7955ce926456240b2ff0100bde28dd2c7955ce926456240b2ff0100bde28dd... |      72 | (25,5)  | 02 00 00 00 da 00 00 00 80 03 00 40 ff 11 32 38 64 64 32 63 37 39 35 35 63 65 39 32 36 34 35 36 32 34 30 62 32 66 66 30 31 30 30 62 64 65 20 00 ff ff ff 4b 50 30 30 62 64 65 00 00 00 00 00 00
           2 |         78 | 3 | 2838023a778dfaecdc212708f721b7882838023a778dfaecdc212708f721b7882838023a778dfaecdc212708f721b7882838... |      72 | (34,3)  | 03 00 00 00 da 00 00 00 80 03 00 40 ff 11 32 38 33 38 30 32 33 61 37 37 38 64 66 61 65 63 64 63 32 31 32 37 30 38 66 37 32 31 62 37 38 38 20 00 ff ff ff 4b 50 31 62 37 38 38 00 00 00 00 00 00
           2 |         79 | 3 | 28dd2c7955ce926456240b2ff0100bde28dd2c7955ce926456240b2ff0100bde28dd2c7955ce926456240b2ff0100bde28dd... |      72 | (38,5)  | 03 00 00 00 da 00 00 00 80 03 00 40 ff 11 32 38 64 64 32 63 37 39 35 35 63 65 39 32 36 34 35 36 32 34 30 62 32 66 66 30 31 30 30 62 64 65 20 00 ff ff ff 4b 50 30 30 62 64 65 00 00 00 00 00 00
           4 |         62 | 4 | 2838023a778dfaecdc212708f721b7882838023a778dfaecdc212708f721b7882838023a778dfaecdc212708f721b7882838... |      72 | (47,3)  | 04 00 00 00 da 00 00 00 80 03 00 40 ff 11 32 38 33 38 30 32 33 61 37 37 38 64 66 61 65 63 64 63 32 31 32 37 30 38 66 37 32 31 62 37 38 38 20 00 ff ff ff 4b 50 31 62 37 38 38 00 00 00 00 00 00
           4 |         63 | 4 | 28dd2c7955ce926456240b2ff0100bde28dd2c7955ce926456240b2ff0100bde28dd2c7955ce926456240b2ff0100bde28dd... |      72 | (51,5)  | 04 00 00 00 da 00 00 00 80 03 00 40 ff 11 32 38 64 64 32 63 37 39 35 35 63 65 39 32 36 34 35 36 32 34 30 62 32 66 66 30 31 30 30 62 64 65 20 00 ff ff ff 4b 50 30 30 62 64 65 00 00 00 00 00 00
           5 |         46 | 5 | 2838023a778dfaecdc212708f721b7882838023a778dfaecdc212708f721b7882838023a778dfaecdc212708f721b7882838... |      72 | (60,3)  | 05 00 00 00 da 00 00 00 80 03 00 40 ff 11 32 38 33 38 30 32 33 61 37 37 38 64 66 61 65 63 64 63 32 31 32 37 30 38 66 37 32 31 62 37 38 38 20 00 ff ff ff 4b 50 31 62 37 38 38 00 00 00 00 00 00
           5 |         47 | 5 | 28dd2c7955ce926456240b2ff0100bde28dd2c7955ce926456240b2ff0100bde28dd2c7955ce926456240b2ff0100bde28dd... |      72 | (64,5)  | 05 00 00 00 da 00 00 00 80 03 00 40 ff 11 32 38 64 64 32 63 37 39 35 35 63 65 39 32 36 34 35 36 32 34 30 62 32 66 66 30 31 30 30 62 64 65 20 00 ff ff ff 4b 50 30 30 62 64 65 00 00 00 00 00 00
           6 |         30 | 6 | 2838023a778dfaecdc212708f721b7882838023a778dfaecdc212708f721b7882838023a778dfaecdc212708f721b7882838... |      72 | (73,3)  | 06 00 00 00 da 00 00 00 80 03 00 40 ff 11 32 38 33 38 30 32 33 61 37 37 38 64 66 61 65 63 64 63 32 31 32 37 30 38 66 37 32 31 62 37 38 38 20 00 ff ff ff 4b 50 31 62 37 38 38 00 00 00 00 00 00
           6 |         31 | 6 | 28dd2c7955ce926456240b2ff0100bde28dd2c7955ce926456240b2ff0100bde28dd2c7955ce926456240b2ff0100bde28dd... |      72 | (77,5)  | 06 00 00 00 da 00 00 00 80 03 00 40 ff 11 32 38 64 64 32 63 37 39 35 35 63 65 39 32 36 34 35 36 32 34 30 62 32 66 66 30 31 30 30 62 64 65 20 00 ff ff ff 4b 50 30 30 62 64 65 00 00 00 00 00 00
           7 |         14 | 7 | 2838023a778dfaecdc212708f721b7882838023a778dfaecdc212708f721b7882838023a778dfaecdc212708f721b7882838... |      72 | (86,3)  | 07 00 00 00 da 00 00 00 80 03 00 40 ff 11 32 38 33 38 30 32 33 61 37 37 38 64 66 61 65 63 64 63 32 31 32 37 30 38 66 37 32 31 62 37 38 38 20 00 ff ff ff 4b 50 31 62 37 38 38 00 00 00 00 00 00
           7 |         15 | 7 | 28dd2c7955ce926456240b2ff0100bde28dd2c7955ce926456240b2ff0100bde28dd2c7955ce926456240b2ff0100bde28dd... |      72 | (90,5)  | 07 00 00 00 da 00 00 00 80 03 00 40 ff 11 32 38 64 64 32 63 37 39 35 35 63 65 39 32 36 34 35 36 32 34 30 62 32 66 66 30 31 30 30 62 64 65 20 00 ff ff ff 4b 50 30 30 62 64 65 00 00 00 00 00 00
           7 |         94 | 8 | 2838023a778dfaecdc212708f721b7882838023a778dfaecdc212708f721b7882838023a778dfaecdc212708f721b7882838... |      72 | (99,3)  | 08 00 00 00 da 00 00 00 80 03 00 40 ff 11 32 38 33 38 30 32 33 61 37 37 38 64 66 61 65 63 64 63 32 31 32 37 30 38 66 37 32 31 62 37 38 38 20 00 ff ff ff 4b 50 31 62 37 38 38 00 00 00 00 00 00
           7 |         95 | 8 | 28dd2c7955ce926456240b2ff0100bde28dd2c7955ce926456240b2ff0100bde28dd2c7955ce926456240b2ff0100bde28dd... |      72 | (103,5) | 08 00 00 00 da 00 00 00 80 03 00 40 ff 11 32 38 64 64 32 63 37 39 35 35 63 65 39 32 36 34 35 36 32 34 30 62 32 66 66 30 31 30 30 62 64 65 20 00 ff ff ff 4b 50 30 30 62 64 65 00 00 00 00 00 00

A Loose Index Scan will read only the first row for each value of "a", for example, in SELECT DISTINCT ON (a):

 index_block | itemoffset | a |                                                    b                                                    | itemlen |  htid  |                                                                                              data
-------------+------------+---+---------------------------------------------------------------------------------------------------------+---------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
           1 |          2 | 1 | 02e74f10e0327ad868d138f2b4fdd6f002e74f10e0327ad868d138f2b4fdd6f002e74f10e0327ad868d138f2b4fdd6f002e7... |      72 | (4,2)  | 01 00 00 00 da 00 00 00 80 03 00 40 ff 11 30 32 65 37 34 66 31 30 65 30 33 32 37 61 64 38 36 38 64 31 33 38 66 32 62 34 66 64 64 36 66 30 20 00 ff ff ff 4b 50 64 64 36 66 30 00 00 00 00 00 00
           1 |         82 | 2 | 02e74f10e0327ad868d138f2b4fdd6f002e74f10e0327ad868d138f2b4fdd6f002e74f10e0327ad868d138f2b4fdd6f002e7... |      72 | (17,3) | 02 00 00 00 da 00 00 00 80 03 00 40 ff 11 30 32 65 37 34 66 31 30 65 30 33 32 37 61 64 38 36 38 64 31 33 38 66 32 62 34 66 64 64 36 66 30 20 00 ff ff ff 4b 50 64 64 36 66 30 00 00 00 00 00 00
           2 |         66 | 3 | 02e74f10e0327ad868d138f2b4fdd6f002e74f10e0327ad868d138f2b4fdd6f002e74f10e0327ad868d138f2b4fdd6f002e7... |      72 | (30,3) | 03 00 00 00 da 00 00 00 80 03 00 40 ff 11 30 32 65 37 34 66 31 30 65 30 33 32 37 61 64 38 36 38 64 31 33 38 66 32 62 34 66 64 64 36 66 30 20 00 ff ff ff 4b 50 64 64 36 66 30 00 00 00 00 00 00
           4 |         50 | 4 | 02e74f10e0327ad868d138f2b4fdd6f002e74f10e0327ad868d138f2b4fdd6f002e74f10e0327ad868d138f2b4fdd6f002e7... |      72 | (43,3) | 04 00 00 00 da 00 00 00 80 03 00 40 ff 11 30 32 65 37 34 66 31 30 65 30 33 32 37 61 64 38 36 38 64 31 33 38 66 32 62 34 66 64 64 36 66 30 20 00 ff ff ff 4b 50 64 64 36 66 30 00 00 00 00 00 00
           5 |         34 | 5 | 02e74f10e0327ad868d138f2b4fdd6f002e74f10e0327ad868d138f2b4fdd6f002e74f10e0327ad868d138f2b4fdd6f002e7... |      72 | (56,3) | 05 00 00 00 da 00 00 00 80 03 00 40 ff 11 30 32 65 37 34 66 31 30 65 30 33 32 37 61 64 38 36 38 64 31 33 38 66 32 62 34 66 64 64 36 66 30 20 00 ff ff ff 4b 50 64 64 36 66 30 00 00 00 00 00 00
           6 |         18 | 6 | 02e74f10e0327ad868d138f2b4fdd6f002e74f10e0327ad868d138f2b4fdd6f002e74f10e0327ad868d138f2b4fdd6f002e7... |      72 | (69,3) | 06 00 00 00 da 00 00 00 80 03 00 40 ff 11 30 32 65 37 34 66 31 30 65 30 33 32 37 61 64 38 36 38 64 31 33 38 66 32 62 34 66 64 64 36 66 30 20 00 ff ff ff 4b 50 64 64 36 66 30 00 00 00 00 00 00
           7 |          2 | 7 | 02e74f10e0327ad868d138f2b4fdd6f002e74f10e0327ad868d138f2b4fdd6f002e74f10e0327ad868d138f2b4fdd6f002e7... |      72 | (82,3) | 07 00 00 00 da 00 00 00 80 03 00 40 ff 11 30 32 65 37 34 66 31 30 65 30 33 32 37 61 64 38 36 38 64 31 33 38 66 32 62 34 66 64 64 36 66 30 20 00 ff ff ff 4b 50 64 64 36 66 30 00 00 00 00 00 00
           7 |         82 | 8 | 02e74f10e0327ad868d138f2b4fdd6f002e74f10e0327ad868d138f2b4fdd6f002e74f10e0327ad868d138f2b4fdd6f002e7... |      72 | (95,3) | 08 00 00 00 da 00 00 00 80 03 00 40 ff 11 30 32 65 37 34 66 31 30 65 30 33 32 37 61 64 38 36 38 64 31 33 38 66 32 62 34 66 64 64 36 66 30 20 00 ff ff ff 4b 50 64 64 36 66 30 00 00 00 00 00 00

Here are some examples with the execution plans.

Skip Scan is visible in PostgreSQL 18. This query returns 16 rows from five of the eight a groups. PostgreSQL reports five Index Searches and 13 buffer hits:

explain (analyze, buffers)
SELECT a,b FROM demo WHERE b LIKE '28%';
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using demo_ab_idx on demo  (cost=0.28..13.17 rows=16 width=904) (actual time=0.031..0.087 rows=16.00 loops=1)
   Index Cond: ((b >= '28'::text) AND (b < '29'::text))
   Filter: (b ~~ '28%'::text)
   Heap Fetches: 0
   Index Searches: 5
   Buffers: shared hit=13
 Planning Time: 0.101 ms
 Execution Time: 0.101 ms
(8 rows)

The five searches do not mean that PostgreSQL blindly performs one fresh descent for every possible value of a. The executor can continue through nearby leaf pages when that is cheaper. It performs a new descent when repositioning is estimated to save work. The buffer count is cumulative, so pages reused by multiple searches can contribute multiple buffer hits.

PostgreSQL 18 does not implement Loose Index Scan natively. DISTINCT ON therefore performs one ordinary index scan, reads all 640 index entries, and then lets Unique retain the first row from each of the eight groups. The scan reports one Index Search and nine buffer hits:

postgres=# explain (analyze, buffers)
SELECT DISTINCT ON (a) b FROM demo
;
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=0.28..43.48 rows=8 width=904) (actual time=0.017..0.135 rows=8.00 loops=1)
   Buffers: shared hit=9
   ->  Index Only Scan using demo_ab_idx on demo  (cost=0.28..41.88 rows=640 width=904) (actual time=0.016..0.097 rows=640.00 loops=1)
         Heap Fetches: 0
         Index Searches: 1
         Buffers: shared hit=9
 Planning Time: 0.055 ms
 Execution Time: 0.150 ms
(8 rows)

We can simulate a Loose Index Scan with a recursive WITH clause that reads a single row from each scan with LIMIT 1:

postgres=# explain (analyze, buffers)
WITH RECURSIVE first_per_a AS (
    (
        SELECT a, b
        FROM demo
        ORDER BY a, b
        LIMIT 1
    )
    UNION ALL
    SELECT nxt.a, nxt.b
    FROM first_per_a cur
    CROSS JOIN LATERAL (
        SELECT a, b
        FROM demo
        WHERE a > cur.a
        ORDER BY a, b
        LIMIT 1
    ) AS nxt
)
SELECT b
FROM first_per_a
ORDER BY a;
                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=44.62..44.87 rows=101 width=36) (actual time=0.068..0.069 rows=8.00 loops=1)
   Sort Key: first_per_a.a
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=21
   CTE first_per_a
     ->  Recursive Union  (cost=0.28..39.23 rows=101 width=904) (actual time=0.018..0.058 rows=8.00 loops=1)
           Storage: Memory  Maximum Storage: 33kB
           Buffers: shared hit=21
           ->  Limit  (cost=0.28..0.34 rows=1 width=904) (actual time=0.017..0.018 rows=1.00 loops=1)
                 Buffers: shared hit=3
                 ->  Index Only Scan using demo_ab_idx on demo  (cost=0.28..41.88 rows=640 width=904) (actual time=0.016..0.017 rows=1.00 loops=1)
                       Heap Fetches: 0
                       Index Searches: 1
                       Buffers: shared hit=3
           ->  Nested Loop  (cost=0.28..3.79 rows=10 width=904) (actual time=0.004..0.005 rows=0.88 loops=8)
                 Buffers: shared hit=18
                 ->  WorkTable Scan on first_per_a cur  (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=1.00 loops=8)
                 ->  Limit  (cost=0.28..0.35 rows=1 width=904) (actual time=0.004..0.004 rows=0.88 loops=8)
                       Buffers: shared hit=18
                       ->  Index Only Scan using demo_ab_idx on demo demo_1  (cost=0.28..16.00 rows=213 width=904) (actual time=0.003..0.003 rows=0.88 loops=8)
                             Index Cond: (a > cur.a)
                             Heap Fetches: 0
                             Index Searches: 8
                             Buffers: shared hit=18
   ->  CTE Scan on first_per_a  (cost=0.00..2.02 rows=101 width=36) (actual time=0.021..0.062 rows=8.00 loops=1)
         Storage: Memory  Maximum Storage: 17kB
         Buffers: shared hit=21
 Planning Time: 0.133 ms
 Execution Time: 0.093 ms
(29 rows)

As with Skip Scan, we see multiple Index Searches under loop=8. Note that while actual time and rows are the average per loop, other statistics like Buffers, Index Searches, and Heap Fetches are cumulative across all loops. One initial index search finds the first group, followed by eight executions of the recursive subplan. Seven of those executions find the next value of a. The eighth searches for a > 8, finds nothing, and terminates the recursion. Thus, the recursive part has loops=8, returns an average of 0.88 rows per loop (7 rows in total), performs 8 total index searches, and accounts for 18 cumulative buffer hits.

While PostgreSQL 18 introduced the Index Searches display for Index Skip Scan, this is not an index skip scan. It is just the metric incremented over loops, and the number of buffers read is the same as in previous versions.

On my tiny 640-row example, the recursive CTE reads more buffers than the full scan because eight groups of eighty rows are too shallow to amortize the repeated descents. However, it scales with the number of groups rather than with the number of rows in those groups. With much larger groups, the ordinary scan grows with the total number of rows, while the loose-scan emulation still performs roughly one probe per group, plus the final probe that discovers the end.

Who implements what

Here is a quick comparison of database engines and the terminology they use. MySQL is the only mainstream engine that ships both under two clearly separate names:

Engine	Loose Index Scan	Skip Scan
PostgreSQL ≤ 17	❌ (recursive CTE workaround)	❌
PostgreSQL 18	❌ (recursive CTE workaround)	✅
MongoDB	✅ — `DISTINCT_SCAN`	❌ ($in with max 200 values workaround)
MySQL	✅ — Loose Index Scan (`GROUP BY`/`DISTINCT`/`MIN`/`MAX`)	✅ — Skip Scan Range Access
MariaDB	✅ — `LooseScan` (semi-join duplicate elimination)	❌
Oracle Database	❌	✅ — Index Skip Scan
Db2	❌	✅ — index skip scan / jump scan
SQLite	❌	✅ — skip-scan
CockroachDB	✅ , with limitations	❌
YugabyteDB	✅ — Hybrid Scan	✅
SQL Server	❌	❌

Even if implementations may be similar, the result sets make the distinction clear:

Skip Scan returns all matching rows after probing a leading-key range that was initially omitted
Loose Index Scan returns one row per distinct prefix value

Conclusion

Skip Scan and Loose Index Scan traverse the same B-Tree with the same physical move — abandon the sequential leaf walk, descend again, land further ahead — but they answer different questions, and that is the whole distinction:

Skip Scan asks: "which rows match my predicate on a later index column?" It returns every matching row. The leading column is a vehicle: it is enumerated only so the real predicate can be applied within each of its ranges.
Loose Index Scan asks: "what are the distinct values of this index prefix?" It returns one entry per value. The leading column is the answer itself, and everything after the first entry of each range is dead weight.

Both benefit when the leading column has few distinct values and many rows per value. That makes repositioning cheaper than reading every duplicate entry sequentially. The difference lies not in the preferred data distribution, but in what the query must return.

That symmetry is also why PostgreSQL 18 is easy to misread. Skip Scan is genuinely there, Index Searches makes it visible, and it is a real improvement for predicates on non-leading columns. But it does nothing for SELECT DISTINCT, because it never stops early — it reads and checks every entry in each range it visits.

The name in the documentation is not enough. Look at the result and the access pattern: if the scan returns every qualifying row after probing multiple prefix groups, it exhibits skip-scan behavior. If it returns one representative per distinct prefix value and skips duplicates, it exhibits loose-scan behavior.

This was tested on Azure Database for PostgreSQL Flexible Server 18 without additional extensions.

OrioleDB Multi-Version Concurrency Control

Franck Pachot — Sun, 16 Aug 2026 19:42:20 +0000

MVCC not only tracks row history but also records the search-key space. To better understand PostgreSQL MVCC, it's useful to look at some alternatives. One is zheap, which introduced out-of-place undo logging to rebuild historical row versions by storing old tuples in an undo log instead of the heap. This was done entirely at the table access method level, without modifying the index access method, but it was eventually abandoned. A more recent development is OrioleDB, which extends this approach to cover the search-key space with an MVCC index access method. Due to PostgreSQL's extensive ecosystem that supports multiple index types such as hash, GIN, BRIN, SPGiST, and others through extensions, OrioleDB uses a "bridge index" to ensure compatibility.

OrioleDB: Why MVCC Reaches Into the B-tree

OrioleDB is a PostgreSQL table access method that replaces the ordinary heap with index-organized, version-aware B-trees. Complete rows are stored in the primary B-tree. Native secondary indexes store the secondary key plus the primary key, allowing them to point back to the complete row.

That layout is easy to compare with InnoDB's clustered index, but the key difference is not clustering. It is how OrioleDB preserves old snapshots as rows, keys, and B-tree pages change. OrioleDB keeps two kinds of history:

row-level undo reconstructs an older tuple value
page-level undo reconstructs older B-tree leaf contents and key ranges

The second kind is what makes OrioleDB architecturally interesting. MVCC must preserve not only what a row used to contain but also where an old query would have found it.

The indexed-key problem

Suppose an old snapshot is open while another transaction modifies an order:

UPDATE orders
SET status = 'closed'
WHERE order_id = 42;

Before the update, a secondary index contains an entry conceptually like ('open', 42). Afterward, the current tree contains ('closed', 42).

Row undo can reconstruct the old value status = 'open' after row 42 has been found. It cannot scan the old open range to find row 42. The current secondary B-tree routes the row under closed.

An MVCC engine must therefore solve two distinct problems:

Problem	Question
Payload visibility	Which version of row 42 may this snapshot read?
Predicate reachability	Should a scan of `status = 'open'` enumerate row 42?

PostgreSQL solves reachability by retaining old heap tuples and old index entries until VACUUM can safely remove them. InnoDB retains old secondary records as delete-marked entries until purge. OrioleDB's native indexes instead use page-level undo to reconstruct the historical searchable key space.

How OrioleDB stores the current database

The primary B-tree stores complete rows. Its key is the table's primary key, or an internal key if no primary key is defined. A native secondary leaf stores the secondary key and primary key rather than a PostgreSQL heap TID.

A secondary lookup therefore follows this path from the secondary key:

search the secondary B-tree to find the primary key of the row
search the primary B-tree to find the row with all its values

Both trees participate in snapshot visibility. A secondary index is not merely a current-key candidate generator whose result can always be repaired at the primary row. It must first enumerate the keys that were part of the requested snapshot.

This tight integration has both benefits and costs. OrioleDB can make its native indexes understand its version model directly. However, PostgreSQL index access methods such as GiST, GIN, SP-GiST, BRIN, hash, and any new index type brought by a PostgreSQL extension do not understand OrioleDB primary keys or undo.

OrioleDB supports those methods through bridge indexes. An ordinary index stores a synthetic heap-shaped bridge_ctid. An internal OrioleDB bridge tree maps that identifier to the primary key. This preserves PostgreSQL's index-AM extensibility but adds an extra lookup and reintroduces stale index entries and a VACUUM cleanup cycle into the design. Native and bridged indexes therefore have different maintenance contracts.

A secondary lookup from such indexes now follows this path:

search the secondary index to find the bridge_ctid
search the bridge index to find the primary key of the row
search the primary B-tree to find the row

Why OrioleDB needs two undo histories

Row-level undo maintains a tuple's history. It records enough information to select or reconstruct the row version visible at a snapshot. The same undo machinery also supports transaction rollback.

Page-level undo records changes to B-tree leaves. It covers ordinary changes, such as insertions and deletions, as well as physical maintenance, such as compaction, splits, and merges. These operations can move keys or change page boundaries, even when their purpose is only to keep the current tree balanced.

That distinction matters during a range scan. A page split may move an old key onto a page that did not exist at the snapshot. A merge may erase the boundary that an old scan needs. OrioleDB can reconstruct a historical page image and, where necessary, merge historical and live items while preserving order and avoiding omissions or duplicates.

The conceptual read path for the example is:

reconstruct the old secondary range containing ('open', 42)
obtain primary key 42
find row 42 in the primary tree at the same snapshot
apply row undo if the current row is too new
return the old row

Page undo answers why the scan finds the row. Row undo answers what the row contained.

B-tree maintenance becomes MVCC maintenance

In PostgreSQL, a B-tree split is primarily a physical index operation. Historical membership persists because old index tuples still point to old heap tuples. The index does not need to preserve the old page topology for snapshot reads.

In OrioleDB, a split also changes the partitioning of the searchable key space. Compaction, merge, and page reuse carry the same additional obligation. A page cannot be treated as irrelevant merely because it is obsolete in the current tree. Retained snapshots may still need its earlier contents or boundaries.

This moves work rather than eliminating it:

native trees avoid PostgreSQL's heap-to-index vacuum cycle
row and page undo consume space while old snapshots remain relevant
scans may reconstruct historical images
split and merge code participates in visibility and retention
recycling history too early produces a snapshot too old boundary
checkpoints and recovery must preserve consistency across primary and secondary tree updates

A single logical update can modify the primary tree and several secondaries. Current OrioleDB tracks the short primary-applied, secondary-pending interval to prevent a checkpoint from permanently merging mismatched tree states. Recovery starts from a copy-on-write checkpoint, replays row-level WAL, repairs derived secondary work as needed, and rolls back incomplete transactions.

Comparison with other engines

The engines below often use similar words while assigning history to different structures:

Engine	Current organization	How old indexed membership survives	Principal deferred cost
PostgreSQL	Heap tuples plus separate indexes	Old index entries continue to reference old version TIDs	Heap and index `VACUUM`, freezing, and bloat control
zHeap Access Method	Tables with out-of-place undo, conventional PostgreSQL indexes	Old row versions reconstructed from undo, index AM remains PostgreSQL B-tree and relies on the existing index maintenance model	Undo retention plus index cleanup/VACUUM requirements
OrioleDB native	Complete rows in primary B-tree. Secondaries carry primary keys	Page undo reconstructs historical leaf items and ranges	Undo retention, reconstruction, and tree-aware reclamation
OrioleDB bridged	Native primary plus synthetic identities in ordinary index AMs	External entries plus a versioned bridge mapping	Extra lookups and bridge-aware `VACUUM`
Oracle Database	Heap blocks, indexes, undo, and consistent-read block images	Transactional index changes and undo/CR preserve logical visibility	Undo retention and optional physical coalesce/rebuild
MySQL InnoDB	Clustered primary plus secondary B-trees	Old secondary records remain delete-marked until safe purge	Undo history and purge lag
MongoDB WiredTiger	Per-key update chains and reconciled B-tree images	Older key values remain in update chains or the history-store B-tree	Reconciliation, eviction, cache pressure, and history cleanup

Oracle is similar in that it uses undo to construct a consistent view, but its block formats, index algorithms, and recovery machinery differ. It should not be described as rewinding an old root-to-leaf tree for every query.

WiredTiger is especially useful for comparison because it also keeps version history close to B-tree keys. Its in-memory update chains and history store optimize storage-engine keys. OrioleDB additionally ensures PostgreSQL table and secondary-index consistency across several trees.

Two research alternatives

Recent research shows that storing versions "in the B-tree" can mean different things.

MV-PBT writes changes to a current memory-resident partition, persists full partitions sequentially, and later merges immutable partitions. Version information in index records enables index-side visibility filtering. It trades OrioleDB-style page reconstruction for cross-partition search and a merge policy closer to LSM storage economics.

The cMVBT preprint represents historical trees explicitly as a partially persistent DAG. Snapshot scans traverse immutable committed nodes without latches, while writers perform proactive version and key splits and merges with optimistic latching. OrioleDB instead maintains a current topology and reconstructs historical leaves from page undo.

cMVBT is a valuable design comparison, not yet a production-engine comparison. Its reported implementation is in-memory, uses fixed-size keys and values, and supports single-operation write transactions. External storage, crash recovery, and arbitrary multi-operation ACID transactions remain outside that evaluation's scope.

What OrioleDB is betting on

OrioleDB's claim is not just that undo operations are less costly than keeping old heap tuples. Instead, it argues that row undo, page undo, version-aware native B-trees, copy-on-write checkpoints, and row-level WAL can work together more efficiently than PostgreSQL's heap, index, and vacuum system.

The most indicative tests go beyond current-key lookups. They include historical snapshots covering indexed-key modifications, long-range scans during split and merge processes, ongoing deletes and space reuse, checkpoint crashes within primary/secondary update windows, and bridged-index workloads with delayed vacuum.

OrioleDB is still a work-in-progress open-source project in beta and requires a patched version of PostgreSQL. Its source code reflects an ambitious and well-structured architecture rather than a stable core PostgreSQL implementation. Evaluate the design's effectiveness by how well it manages historical key ranges, maintenance, cleanup, and recovery under real-world mixed workloads.

The core concept is simple: an old row value is useful only if the old predicate can still locate it. OrioleDB incorporates this requirement directly into the B-tree and uses an additional bridge index to remain compatible with other PostgreSQL index types.

PostgreSQL Multi-Version: From Time Travel to Concurrency Control

Franck Pachot — Sun, 09 Aug 2026 21:04:53 +0000

A LinkedIn comment by Oleg Bartunov made me research this history. The comment explained that PostgreSQL MVCC (multiversion concurrency control) is not the forty-year-old design people often claim. Multiversion storage existed in Berkeley POSTGRES about forty years ago for time travel, but not for concurrency control. It was a different database with a different transaction and storage model. Vadim Mikheev added PostgreSQL's initial MVCC code in December 1998, and MVCC became a major feature of PostgreSQL 6.5 in June 1999. In 2026, that design pivot is about twenty-eight years old.

It nevertheless has an older storage heritage. Berkeley POSTGRES preserved old records for historical queries, archival storage, and recovery. PostgreSQL removed the user-facing Time Travel feature but retained a heap in which an update could create a new physical tuple and leave its predecessor for later reclamation. In 1998, that inherited property became raw material for a new purpose: consistent reads during concurrent writes.

The transition first appeared under the name LLL, Low-Level Locking proposals for LLL, part 1. The lock-manager work prepared the conflict modes needed by writers. MVCC added the other half of the application-visible result, nonblocking reads, in which an ordinary reader uses a visible tuple version instead of taking a data lock that blocks a writer. In the July 1998 LLL discussion, Mikheev separated WAL and non-overwriting storage and transaction system from locking and multiversion concurrency control. WAL determines how changes survive a crash. MVCC determines which changes a transaction can see. PostgreSQL eventually used both.

🤖 I created this piece with lots of help from GitHub Copilot, and I really value your feedback about this approach. I didn't rely on AI to save time, whether for me as a writer or for you as a reader: this article is actually longer and took more effort to get right than it would have if I had done without LLM reviews. What AI provided was a boost in quality. Honestly, I wouldn't have delved so deeply into research, review, and verification without its support.

1. POSTGRES began with a no-overwrite storage argument

Michael Stonebraker and Lawrence Rowe's 1987 paper, The Design of the POSTGRES Storage System, proposed a storage manager in which an update did not overwrite the old record in place. The current version remained on magnetic disk while historical versions could migrate to archival storage. A tuple carried temporal information and a link into its version history. Old values could be represented as differential records rather than complete copies.

This was not originally a concurrency-control design in the modern PostgreSQL sense. The paper's ambitions included historical queries, auditing, recovery, and support for optical archival media. Its conclusion named three design goals: instantaneous crash recovery, archival records on archival media, and asynchronous housekeeping. Past states were product data, not merely obsolete copies awaiting deletion.

The paper also described a background vacuum cleaner. Its job was to find obsolete historical records, move archival data from magnetic disk to tertiary storage, and reclaim expensive primary space. The name survives, but the continuity can be overstated. Modern PostgreSQL VACUUM does not transfer a queryable database history to write-once optical media. It determines which tuple and index versions are no longer needed by any relevant transaction, makes their space reusable, maintains visibility information, and freezes old transaction metadata. The family resemblance is real, but the contract is not the same.

The paper explicitly compared this design with conventional write-ahead logging. Its recovery argument was that committed old state had not been overwritten, so recovery could select valid versions instead of replaying a long log or performing long transaction rollbacks. That is the reason behind its claim of instantaneous crash recovery. In that 1987 design, no-overwrite was therefore proposed partly as an alternative recovery architecture to WAL, not just as Time Travel's storage format.

ARIES, published by C. Mohan and colleagues in 1992, later gave the database field a particularly influential WAL recovery algorithm built around repeating history during REDO, compensation log records, and fine-grained locking. It postdates the 1987 POSTGRES paper, so POSTGRES was not a reaction to ARIES. Both belong to a broader design space that was already comparing logging, shadowing/version retention, force policies, and recovery time.

No-overwrite storage and WAL still answer different questions:

no-overwrite asks where a new logical row state is placed
WAL asks what must reach durable log storage before a changed data page may be written, so crash recovery can redo or otherwise reconcile changes
multiversion concurrency control asks which row state a transaction may see.

The early recovery promise did not survive intact into pre-WAL PostgreSQL. The PostgreSQL 7.1 WAL documentation states plainly that earlier releases forced data changes to disk at commit and still could not guarantee consistency after a crash. Partially written pages and broken index-to-heap relationships remained possible. PostgreSQL 7.1 added WAL in 2001 to provide REDO recovery and reduce commit-time I/O. Its first WAL release explicitly did not implement WAL-based UNDO. Aborted heap tuples could remain physically present and be rejected using transaction status until reclaimed.

PostgreSQL thus did not adopt ARIES wholesale, nor did WAL replace heap versioning. It combined WAL for durability and crash recovery with tuple versions for transaction visibility. That sequence confirms Mikheev's 1998 point that recovery architecture and concurrency control were separable.

2. Time Travel exposed physical history, and then became a liability

Early POSTGRES made tuple history visible through Time Travel queries. In that world, retaining an old version was not bloat by definition. It could be an answer to a future historical query.

That idea fits relational data in an interesting but qualified way. A normalized schema avoids repeating a product description or customer address in every order, but a historical query must then reconstruct a mutually consistent past across all related tables. System-managed temporal versions can make such joins possible, but normalization does not make them automatic or cheap.

Applications still implement Time Travel selectively even after the database feature disappeared. Tables commonly carry created_at and updated_at, audit tables retain changes, and periodic snapshots preserve larger states. An order document may deliberately embed the product description, tax, and price shown at purchase time, perhaps as JSON, JSONB or BSON (with DocumentDB extension). That duplication is not failed normalization: it records a business fact that must not change when the product catalog changes. The old feature attempted to retain every table's past uniformly. Modern applications usually choose which history has business value.

That promise had a cost. The archived manual says that, as of PostgreSQL 6.2, Time Travel was no longer supported, citing its performance impact, storage cost, and a pg_time file that grew "toward infinite size in a short period of time." The release arrived in October 1997. A November 1997 source commit by Vadim Mikheev was titled "Good Bye, Time Travel!" Cleanup continued into 1998.

This is the first important reversal. PostgreSQL discarded indefinite, user-addressable history, but it did not turn the heap into an overwrite-in-place store. Updates still had the useful ability to leave an older physical version behind. Removing Time Travel changed how long versions had to remain meaningful and who was entitled to see them. It did not erase the storage system's multiversion nature.

Before MVCC, old versions served a narrower transactional purpose. PostgreSQL 6.4's update path already inserted a replacement tuple, set its xmin, and marked the old tuple with the updating transaction's xmax. Its HeapTupleSatisfiesNow() test used those transaction IDs and commit or abort status to reject an uncommitted or aborted replacement and to decide whether the predecessor was still current. The versions therefore supported transactional commit and rollback within the inherited non-overwriting heap, even though ordinary readers did not yet receive the consistent snapshots introduced in 6.5. Vacuum reclaimed versions once the transaction rules no longer needed them. They were short-lived transaction state, not retained historical data.

The distinction is visible in the later release notes. PostgreSQL 6.5 describes its new MVCC as taking advantage of PostgreSQL's "natural multiversion nature." That wording is unusually revealing: MVCC was new as concurrency control, while multiversion storage was already natural to the system.

3. Low-Level Locking turned storage versions into snapshots

In July 1998, a pgsql-hackers thread titled "proposals for LLL, part 1" recorded the design choices while the implementation was being planned.

Mikheev separated two choices:

WAL versus a non-overwriting storage/transaction system.
Locking versus multiversion concurrency and consistency control.

He emphasized: "These are quite different issues!" and used Oracle as the counterexample to any assumption that WAL implied locking: Oracle combined redo logging with multiversion reads. His practical proposal was to implement multiversion control first and switch PostgreSQL to WAL later. That is close to the sequence the project followed: MVCC in 6.5, WAL in 7.1.

The acronym is explicit in the source. Mikheev's August 1, 1998 commit was titled "Lmgr cleanup, new locking modes for LLL." The changed lock-manager files placed those modes behind the preprocessor symbol LowLevelLocking. In this development context, LLL meant Low-Level Locking. The work reorganized the lock manager and introduced the relation lock modes later used by SQL commands such as LOCK TABLE and SELECT FOR UPDATE. It was preparation for the nonblocking reads delivered by MVCC, not the complete visibility mechanism itself.

Chronology matters:

Date	Milestone	Result
1997	PostgreSQL 6.2 removes Time Travel	User-queryable tuple history was abandoned
July-August 1998	Low-Level Locking discussion and code	WAL/storage and concurrency control were separated, and lock modes were reorganized
October 1998	PostgreSQL 6.4	Lock-manager cleanup and new Low-Level Locking modes prepared the ground
November 27, 1998	New `HeapTuple` interface	The tuple API was reworked across heap, executor, index, vacuum, and catalog code
December 15, 1998	"Initial MVCC code"	Snapshot visibility entered the tree in a 65-file change by Vadim Mikheev
December 16-18, 1998	Serializable mode, isolation syntax, and lock modes	Transaction semantics became SQL-visible
January 29, 1999	Read Committed implemented as the default	Statement snapshots completed the principal 6.5 isolation behavior
March 28, 1999	Vacuum updated for MVCC	Cleanup learned the new visibility rules
May-June 1999	MVCC chapter and migration notes	The implementation acquired user-facing explanations before release
June 1999	PostgreSQL 6.5	MVCC, read committed, serializable mode, and transaction isolation shipped
April 2001	PostgreSQL 7.1	WAL supplied the modern crash-recovery foundation

So 1998 is the design and implementation pivot, not the date at which released PostgreSQL users first received MVCC. That date is June 9, 1999, with 6.5.

The contemporary milestone was concise. On December 16, 1998, Mikheev sent a pgsql-hackers message with the subject "MVCC works in serialized mode!" Its body began "CVS is just updated..." The matching source commit is titled "Serialized mode works!" This was the day after his 65-file initial MVCC commit, not a continuation of the 1987 Time Travel implementation under a new label. "Serialized" was the project's term at that point. It should not be read as the SSI-based Serializable semantics added in PostgreSQL 9.1. CVS was the code management system PostgreSQL used to store and manage the source code before switching to Git.

4. What changed between PostgreSQL 6.4 and 6.5

In PostgreSQL 6.4.2, the visibility core exposed two principal tuple tests, HeapTupleSatisfiesNow() and HeapTupleSatisfiesItself(). PostgreSQL 6.5 added:

HeapTupleSatisfiesSnapshot() for visibility against a transaction snapshot
HeapTupleSatisfiesDirty() for callers that must detect in-progress tuple state
HeapTupleSatisfiesUpdate() for deciding whether a tuple can be changed
GetSnapshotData(), implemented in the shared-memory process-state code, plus SetQuerySnapshot() and FreeXactSnapshot() for constructing and managing snapshot state

The corresponding header added QuerySnapshot, SerializableSnapshot, and a dirty snapshot object. Heap access, transaction management, the lock manager, and vacuum changed with them. This is important historically: MVCC was not a single visibility predicate dropped into an unchanged server. It required a new tuple interface, snapshot lifecycle, update-conflict rules, isolation-level behavior, and cleanup semantics across the executor and access methods.

Documentation developed alongside the code. On May 26, 1999, Thomas Lockhart integrated an MVCC chapter from material by Mikheev, followed by the wider 6.5 manual integration. Mikheev then added migration notes explaining the practical consequence of nonblocking reads: a row returned by SELECT was not thereby protected against a concurrent update or delete, so applications needing that guarantee had to use SELECT FOR UPDATE or an appropriate table lock. Lockhart gave those notes their own release-documentation subsection on June 3. In October 2000, Tom Lane substantially revised the MVCC chapter, sharpening the descriptions of isolation phenomena, Read Committed, serialization failures, and row locking. The documentation history shows the project turning a new storage behavior into an application contract.

5. A snapshot is not a single number

Oracle explanations often begin with a System Change Number: a query sees data as of a particular SCN, and undo is applied to reconstruct blocks consistent with it. PostgreSQL's ordinary MVCC snapshot is not represented by one global commit sequence number.

Conceptually, a PostgreSQL snapshot says:

transactions below xmin are old enough that none was still running when the snapshot was taken
transactions at or above xmax had not yet been assigned at that point and are therefore in the snapshot's future
transaction IDs in xip were in progress and must be treated accordingly
subtransaction state in subxip and its overflow handling refine that set
curcid distinguishes commands inside the transaction, allowing a transaction to observe its own work with SQL's command-order semantics.

The exact structure has evolved. Current SnapshotData includes more fields and special snapshot kinds than this summary, and transaction-ID wraparound makes comparisons more subtle than ordinary integer ordering. But the durable idea is a pair of horizons plus an exception set, rather than a database-wide commit timestamp.

Current GetSnapshotData() in procarray.c constructs this information from the ProcArray, shared state that tracks active backend transactions. Heap access then asks HeapTupleSatisfiesMVCC() whether an individual tuple is visible under that snapshot.

For an ordinary heap tuple, the decisive physical fields include:

t_xmin, the XID that inserted this tuple version
t_xmax, normally the XID that deleted or superseded it, though the field can also encode row-lock or multitransaction state
t_ctid, the physical location of this version or a newer version
t_infomask bits that cache facts and qualify how the transaction fields are to be interpreted.

A simplified visibility question is: did the inserting transaction commit in time for this snapshot, and did no deleting transaction commit in time to hide it? Real code must also handle the current transaction, command IDs, subtransactions, aborted transactions, multixacts, locks, and hint bits.

Hint bits are a small but characteristic optimization. Once PostgreSQL learns a transaction's durable status from the commit log, it can cache selected status facts in the tuple header. Future readers may avoid repeating the lookup. This is why a read can eventually dirty a heap page even though it changes no logical row data.

These costs have motivated proposals to reduce or replace PostgreSQL’s active-XID snapshot representation with a commit sequence number (CSN). In a 2013 pgsql-hackers proposal, Ants Aasma suggested assigning each committed transaction an ordered CSN so that a snapshot could be represented primarily by a cutoff value rather than by a list of concurrent XIDs. The proposal promised cheaper snapshot creation and potentially smaller snapshots, but required an XID-to-CSN mapping, retention rules for long-running snapshots, and new handling for subtransactions, crash recovery, hot standby, and asynchronous commits. Later discussion explored storing CSNs in a separate pg_csnlog, and prototype measurements showed substantial gains for some highly concurrent workloads but severe regressions when scans required many CSN lookups. As of 2026, this work remains experimental and has not been adopted by upstream PostgreSQL. It illustrates that XID-based visibility is not simply an obsolete design waiting to be replaced: alternatives exchange one set of costs for another, including commit-log lookups, cache pressure, storage overhead, and more complicated commit ordering.

This snapshot representation carries a real design trade-off:

Property	Benefit	Cost
`xmin`/`xmax` horizons plus an active-XID exception set	Visibility does not require assigning every commit a single global timestamp	Taking and retaining snapshots requires tracking concurrent transactions and copying or referencing their state
Tuple `xmin`/`xmax` plus transaction status	Commit and abort decisions stay compact and readers can evaluate versions independently	Visibility may require transaction-status lookups, hint bits, and careful wraparound rules
Statement or transaction snapshots	Readers do not block writers and repeated reads can receive a defined view	Old snapshots delay reclamation. Stronger isolation can require retries or SSI bookkeeping
No scalar commit sequence in an ordinary snapshot	Avoids making one scalar the complete visibility contract	"As of commit N" reasoning, global ordering, and distributed coordination need additional machinery
CSN-based snapshot proposal	Makes snapshots small and cheap to construct	Requires XID-to-CSN mapping, extra commit metadata, retention machinery, and careful handling of async commits and subtransactions

A scalar commit sequence number can make comparison and historical positioning simple: a version committed before the snapshot number is potentially visible. But the system must assign, publish, and often persist that order correctly. Oracle's SCN shows that this can be engineered at scale. PostgreSQL's horizon-and-exceptions snapshot chose a different set of costs. Neither representation removes the need to handle active, aborted, and self-visible transactions.

The lineage from 1998 is therefore conceptual and structural, not a claim that today's SnapshotData or visibility function is unchanged source code.

6. An UPDATE is a short-lived history

Suppose transaction 500 inserts a row. Its tuple version has xmin = 500. Later, transaction 620 updates the row. PostgreSQL marks the old version as superseded using xmax = 620 and writes a new tuple whose xmin = 620.

While 620 is uncommitted:

transaction 620 can see its own new version
another transaction's snapshot normally sees the old version
a concurrent writer of the same logical row waits on the row-level conflict.

After 620 commits, new snapshots see the new version. A snapshot that began earlier may still require the old one. Physical history has become a concurrency mechanism: several transactions can agree on different visible versions without a reader forcing the writer to wait.

This also explains why long transactions have a system-wide physical cost. Their old snapshot may keep a low visibility horizon, so versions that are dead to newer transactions cannot yet be removed. Replication slots, prepared transactions, and standby feedback can retain related horizons for different reasons. "Idle in transaction" is therefore not merely untidy client behavior. it can turn a local pause into heap and index retention elsewhere.

7. Indexes make versioning expensive, and HOT makes it tolerable

Ordinary PostgreSQL indexes point to heap item identifiers, not to a timeless logical row. A non-HOT update generally needs index entries for the new tuple version, including for indexes whose key values did not change. Old index entries cannot disappear while some snapshot might still follow them to a visible old heap version.

PostgreSQL 8.3, released in 2008, introduced Heap-Only Tuples (HOT). A HOT update is possible when:

the update does not change a column referenced by a non-summarizing index
the new tuple version fits on the same heap page.

The existing index entry can then continue to point to the root item identifier, and the heap carries an on-page chain to the appropriate version. Intermediate dead versions can be pruned during normal page access, including some SELECT operations. Their line pointers can be reused without waiting for a full-table vacuum pass.

Table fillfactor came first. It was added by PostgreSQL 8.2 in December 2006, in the same July 2006 patch that introduced index fillfactor. The 8.2 documentation already said that reserved space gave an update a chance to place its new row copy on the same page. HOT arrived in 8.3 and made that existing control more consequential because a same-page update that does not change indexed columns can also avoid new index entries. The trade-off is a larger base heap, potentially more I/O for scans, and wasted RAM, because the reserved free space is cached twice: once in shared_buffers and once in the OS page cache. A lower fillfactor is useful when observed update patterns justify the space, not as a universal setting.

HOT also corrects a common simplification: VACUUM is not the only code that removes dead tuple bodies. Page pruning can reclaim intra-page tuple space during ordinary operation. VACUUM remains necessary for broader heap cleanup, dead index entry removal, visibility-map maintenance, freezing, and space management.

Schema design changes how often HOT's conditions occur. Moving an order's frequently changing status into a narrow status table can avoid rewriting a very wide order row and improve cache locality. It also adds a relation, indexes, joins, and another consistency boundary. If the status column itself is indexed, changing it is not HOT-eligible. If status is an unindexed column in the order table and the page has room, the wide row can still receive a HOT update. Normalization is therefore a workload choice, not a HOT prerequisite.

8. VACUUM decides when history has stopped being evidence

A tuple is not removable merely because a newer version exists. It becomes reclaimable only when no transaction horizon relevant to cleanup can still need it. This turns VACUUM into the physical counterpart of snapshot semantics.

Routine vacuuming performs several distinct jobs:

identifies dead tuple versions and makes heap space reusable
removes or arranges removal of dead index entries
updates the free-space and visibility maps
marks pages all-visible, enabling index-only scans where the index has enough data to answer a query
marks pages all-frozen where possible, allowing future anti-wraparound work to skip them
when requested with ANALYZE, refreshes planner statistics, although VACUUM and ANALYZE are separate operations.

The visibility map keeps two conservative bits per heap page: all-visible and all-frozen. A write clears the relevant promise. Vacuum can set it again after proving the condition. The map is thus a compact summary of work that the heap's versioning design would otherwise force every index-only scan or anti-wraparound vacuum to repeat.

Freezing addresses the fact that normal PostgreSQL transaction IDs are 32-bit, as explained in the documentation on wraparound prevention. Age is interpreted in a moving, modulo-$2^{32}$ space, with roughly two billion XIDs on either side of the current point. If old tuple metadata were left untreated through wraparound, an ancient inserting XID could appear to be in the future and data could seem to disappear. Modern freezing records that an old inserting transaction is unconditionally in the past, commonly through a frozen status bit while preserving the original xmin value in supported page formats.

Autovacuum is consequently part of correctness, not an optional bloat-polishing service. Even a table configured to disable ordinary autovacuum can still receive anti-wraparound vacuuming.

9. MVCC did not by itself make SERIALIZABLE serializable

Another later correction is worth adding to the timeline. A stable snapshot prevents dirty, nonrepeatable, and phantom reads as those phenomena are commonly described, but snapshot isolation can still permit write-skew anomalies. Before PostgreSQL 9.1, the isolation level named SERIALIZABLE was essentially the behavior now called REPEATABLE READ. Those level names try to map to SQL standard definitions built on phenomena observed in non-MVCC, lock-based databases, but what PostgreSQL actually provided under either name was Snapshot Isolation.

PostgreSQL 9.1 added Serializable Snapshot Isolation (SSI), credited in the release notes to Kevin Grittner and Dan Ports. SSI retains MVCC snapshots and tracks read/write dependencies to detect dangerous structures. When concurrent transactions would produce a result inconsistent with every serial ordering, one is aborted and should be retried.

This is another example of layering a new rule over the same physical versions. Tuple versions make nonblocking snapshots possible. They do not alone prove serializability.

10. Undo-based alternatives revisit the storage choice

PostgreSQL's heap format is not the only way to provide PostgreSQL semantics. Three projects have explored moving older versions out of the main tuple stream.

Zheap was an EnterpriseDB prototype for an undo-based PostgreSQL table format. It aimed to update rows in place, keep transaction information in page slots, follow undo chains for older snapshots, reduce tuple and index bloat, and avoid table-wide vacuum for ordinary space reclamation. Its own documentation listed unfinished recovery, rollback, logical decoding, snapshot-too-old, and table-access-method integration work. The original repository's last main work dates from 2019 and the PostgreSQL wiki was last updated in 2021. Zheap was not merged upstream. It remains useful as a concrete design exploration, not a current PostgreSQL storage option.

OrioleDB is a newer index-organized table engine whose architecture uses PostgreSQL's table-access-method and extension interfaces, while currently requiring a patched PostgreSQL build. Its MVCC keeps the current tuple at the head of an undo chain and older row versions in an undo log. It combines that with B-tree primary storage, page merging, copy-on-write checkpoints, 64-bit transaction IDs, and a row-level WAL used for recovery and replication. This moves old-version pressure away from PostgreSQL-style heap chains and removes dedicated table vacuuming for OrioleDB tables, but introduces undo retention, rollback, checkpoint, index, and recovery machinery of its own. As of 2026 the project describes itself as a public beta recommended for experiments and benchmarking, not production use.

YugabyteDB takes another route. Tables and indexes are distributed to tablets, and each tablet is an LSM-based storage engine built on a customized RocksDB. Versions enter immutable SST files, and compaction later merges files and reclaims obsolete data. New state does not overwrite immutable files in place, and cleanup happens asynchronously, echoing the original POSTGRES argument. Compaction is not PostgreSQL VACUUM, though. It is the LSM mechanism that performs the analogous garbage-collection job after MVCC's history-retention rules say a version is obsolete.

These projects do not reject MVCC. They keep the concurrency model and revisit where versions live, how current pages are reclaimed, and how WAL and undo share recovery work. They make Mikheev's 1998 separation of storage/recovery from concurrency control visible again.

11. Oracle reaches consistent reads by a different physical route

Oracle is not an arbitrary comparison here. Mikheev's own 1998 argument, in section 3, used Oracle as its counterexample: proof that combining WAL with multiversion reads was already possible, because Oracle was already doing it. That is why Oracle, specifically, is worth tracing in this much detail.

Oracle's use of multiversion concurrency control goes back further than PostgreSQL 6.5, and further than commonly assumed. According to Oracle vice president Ken Jacobs, writing in Oracle Magazine and reproduced on the Oracle community forums, Oracle version 3, released in March 1983, already introduced nonblocking queries, using data saved in a before image file for both queries and transaction rollback, avoiding read locks, although overall throughput was still limited by table-level locking. Version 4, in 1984, named the resulting guarantee read consistency. Version 6, in 1988, was a further rewrite that replaced table-level locking with row-level locking for better scalability.

Interestingly, Oracle version 2 did not yet have before images, but it used after images for recovery, which were later replaced by redo logs. This reflects Oracle’s original block-oriented design: physical changes were protected using before and after images of database blocks rather than the more abstract logical redo and undo records.

In Oracle 5, before-image buffers are their own named memory area, separate from the general data buffers. Before images are tracked per object and used for a consistent-read "snapshot", and the "snapshot too old" error was already there. Oracle 5 already let a reader take an explicit SHARE lock without blocking other readers, but its default write behavior was still table-wide.

Oracle 6 shows the row-level rewrite directly, and complicates it. Its LOCK TABLE help topic adds two modes Oracle 5 never had, ROW SHARE and ROW EXCLUSIVE, described as allowing "concurrent use" and prohibiting only "entire table locks" rather than blocking the table outright. New DBA scripts shipped only with Oracle 6, blocking.sql and locktree.sql (Loaiza, November 1989), query the new v$lock view for row-level enqueue modes named Row-S, Row-X, Share, S/Row-X, and Exclusive, the same vocabulary Oracle still uses internally today. But row-level locking was not simply on by default in every Oracle 6 install: the shipped INIT.ORA sets row_locking = INTENT, and setting it to ALWAYS for full row-level locking required a separately licensed Transaction Processing Option (TPO).

By the time of Oracle7, released in 1992, the Concepts manual already used Multiversion Concurrency Control as a section heading, years before PostgreSQL 6.5 existed. It described rollback segments containing the old values changed by a transaction, called ordinary queries nonblocking, and stated the central result directly: readers do not block writers and writers do not block readers. Oracle9i later replaced manually managed rollback segments with automatic undo tablespaces, generalizing the older term rollback into undo. PostgreSQL and Oracle both provide multiversion read consistency, but "both use MVCC" should not conceal their opposite physical instincts.

PostgreSQL normally leaves old and new tuple versions in the table heap. Its indexes and cleanup machinery must live with those versions until they become globally irrelevant.

Oracle normally changes the current data block and stores its before-image information (as undo records) in what the older manuals called rollback segments and later manuals call undo. A query records an SCN. If it encounters a block changed after that SCN, Oracle applies those old values to construct a consistent-read copy. Oracle's own documentation explains why a reader can fail with "snapshot too old" when required rollback information has been reused. A PostgreSQL long-running snapshot more characteristically prevents cleanup and contributes to bloat. Both systems pay for old readers, but the pressure appears in different places.

The Oracle9i Flashback Query documentation exposed retained transaction history through AS OF SCN or AS OF TIMESTAMP. Oracle 10g added Flashback Version Query over an interval. Oracle 11g added Flashback Data Archive under the Total Recall name, with managed historical storage and a retention policy that avoids depending only on short-lived undo. The historical reversal is appealing. PostgreSQL abandoned database Time Travel and reused versions for concurrency. Oracle used versions for concurrency first, then exposed retained history as Flashback.

Oracle multi-versioning has always been at block level, but there is also a less-known way to version table rows. Oracle Workspace Manager (OWM) is a separate logical versioning layer. When a table is version-enabled, OWM renames the physical table with an _LT suffix and adds row columns beginning with WM_, including WM_VERSION, WM_NEXTVER, and WM_DELSTATUS. It then creates a view under the original table name with INSTEAD OF triggers. The view combines the current workspace metadata with those row-carried fields to expose only the versions relevant to the session's workspace.

Visibility information is attached to stored row versions and interpreted at read time, the same broad shape as PostgreSQL heap MVCC, but the two solve different problems. OWM supplies named workspaces, savepoints, refresh, merge, conflicts, and durable application-visible branches. Oracle undo supplies transaction rollback and consistent reads. Flashback supplies historical query and recovery facilities. These mechanisms can coexist precisely because none of them replaces the others.

12. Two heaps, seen directly

The physical difference is easiest to see by inserting one row and updating it twice, then asking each engine to show every version, the same way I inspected heap pages and B-tree entries directly in PostgreSQL resolves uniqueness through heap tuple visibility.

CREATE EXTENSION IF NOT EXISTS pageinspect;

DROP TABLE IF EXISTS mvcc_demo;
CREATE TABLE mvcc_demo (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   text NOT NULL,
  status text NOT NULL
) WITH (fillfactor = 50);
ALTER TABLE mvcc_demo SET (autovacuum_enabled = false);

INSERT INTO mvcc_demo (name, status) VALUES ('demo row', 'new');
UPDATE mvcc_demo SET status = 'active' WHERE name = 'demo row';
UPDATE mvcc_demo SET status = 'closed' WHERE name = 'demo row';

SELECT ctid, xmin, xmax, * FROM mvcc_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask
FROM heap_page_items(get_raw_page('mvcc_demo', 0))
ORDER BY lp;

An ordinary query sees one row:

 ctid  |   xmin   | xmax | id |   name   | status
-------+----------+------+----+----------+--------
 (0,3) | 46818350 |    0 |  1 | demo row | closed
(1 row)

The heap page still holds all three versions, chained by t_ctid:

 lp |  t_xmin  |  t_xmax  | t_ctid | t_infomask
----+----------+----------+--------+------------
  1 | 46818348 | 46818349 | (0,2)  |       1282
  2 | 46818349 | 46818350 | (0,3)  |       9474
  3 | 46818350 |        0 | (0,3)  |      10498
(3 rows)

EXPLAIN (ANALYZE, BUFFERS) shows what reading that page costs:

 Sort  (cost=59.83..62.33 rows=1000 width=20) (actual time=0.026..0.027 rows=3.00 loops=1)
   Sort Key: lp
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=1
   ->  Function Scan on heap_page_items  (cost=0.01..10.01 rows=1000 width=20) (actual time=0.021..0.021 rows=3.00 loops=1)
         Buffers: shared hit=1
 Planning Time: 0.025 ms
 Execution Time: 0.039 ms

Each version is a distinct physical tuple at a distinct ctid. t_xmax on one version is the same transaction as t_xmin on the next, so the chain records who superseded whom. Only the last line pointer, whose t_ctid points to itself, is the current version.

Oracle's Flashback Version Query shows the same idea from the other side. Insert one row and update it twice, each in its own committed transaction:

CREATE TABLE mvcc_demo (
  id     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   VARCHAR2(50) NOT NULL,
  status VARCHAR2(20) NOT NULL
);

INSERT INTO mvcc_demo (name, status) VALUES ('demo row', 'new');
COMMIT;
UPDATE mvcc_demo SET status = 'active' WHERE name = 'demo row';
COMMIT;
UPDATE mvcc_demo SET status = 'closed' WHERE name = 'demo row';
COMMIT;

Querying with the same name filter as the PostgreSQL query above, VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE reconstructs its whole history:

SET AUTOTRACE ON

SELECT VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID,
       VERSIONS_OPERATION, ROWID, name, status
FROM mvcc_demo
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE name = 'demo row'
ORDER BY VERSIONS_STARTSCN;

The ROWID is identical in every row, because Oracle updates it in place and reconstructs older states from undo rather than leaving old tuples behind:

   VERSIONS_STARTSCN      VERSIONS_ENDSCN VERSIONS_XID     V ROWID                NAME       STATUS
-------------------- -------------------- ---------------- - -------------------- ---------- ----------
      50016514928749       50016514945961 0B000900F9FD0200 I AABJ5aAAAAACABzAAA   demo row   new
      50016514945961       50016514962782 08001D00E4240300 U AABJ5aAAAAACABzAAA   demo row   active
      50016514962782                      0B001000F8FD0200 U AABJ5aAAAAACABzAAA   demo row   closed

AUTOTRACE shows what that reconstruction costs:

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    51 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY     |           |     1 |    51 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| MVCC_DEMO |     1 |    51 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  db block gets
         21  consistent gets
          0  physical reads
          3  rows processed

There is no index on name, so Oracle does a full table access and, for each row it scans, walks that row's undo chain to answer VERSIONS BETWEEN, turning one physical row into three logical rows at a cost of 21 consistent gets and zero physical reads, because the block was already cached. PostgreSQL's heap_page_items(get_raw_page(...)) above instead reads the one physical page directly, a single buffer hit, and every version is already sitting on it with nothing to reconstruct. Twenty-one buffer touches to rebuild three versions from undo, against one buffer touch to read three versions already stored in place, is the same trade-off as section 8, now expressed as measured numbers rather than as an architecture description: Oracle pays, on read, to reconstruct history that is not stored contiguously with the current row. PostgreSQL pays, on cleanup, to keep history stored contiguously until VACUUM removes it.

Both engines correctly answer "show me every version of this row," but the metadata that identifies a version is the mirror image of the other: PostgreSQL's ctid changes on every version while xmin/xmax name the transactions that created and superseded it, and Oracle's ROWID never changes while VERSIONS_STARTSCN/VERSIONS_ENDSCN/VERSIONS_XID name the time range and transaction instead. That is the distinction from section 11, now visible in two SELECT statements rather than two architecture descriptions.

13. History moves between layers

I discussed the operational consequences of these designs in my FOSDEM 2024 talk, Isolation Levels and MVCC in SQL Databases: A Technical Comparative Study. The broader lesson is not that one versioning architecture has eliminated the weaknesses of another. Each chooses where to retain history, how long to retain it, and who pays to reconstruct or remove it.

Application tables preserve business history only where it matters. PostgreSQL heap tuples preserve transaction history beside current rows. Oracle undo keeps before-images outside current data blocks. LSM engines preserve versions in immutable SST files until compaction. Azure HorizonDB moves the idea below tuples entirely. Its stateless compute replicas send only WAL to the storage layer, and its data storage nodes reconstruct a requested page by replaying that WAL, while Azure Blob storage keeps the durable, longer-term copy of those pages. Cold history in blob storage is the cloud-native descendant of the optical disks imagined in 1987. The analogy is about storage hierarchy, not identical implementations.

14. The old decision is still visible in current operations

The 1987 designers did not secretly implement today's PostgreSQL concurrency control. The 1999 implementation did not secretly contain HOT, visibility maps, modern freezing, SSI, or two decades of scalability work. Historical lineage is not identity.

But the old storage decision constrained and enabled what followed. Because an update could coexist with its predecessor:

Time Travel could once expose old versions directly
MVCC could later assign different versions to different snapshots
VACUUM had to become the arbiter of when obsolete evidence could be reused
indexes inherited version churn, leading eventually to HOT
page free space became a concurrency-performance parameter through fillfactor
old snapshots became an operational retention horizon
32-bit tuple transaction metadata made freezing a condition of correctness.

PostgreSQL's current behavior is easier to remember when read as this chain of decisions. It does not vacuum because its MVCC implementation is carelessly unfinished. It vacuums because versions are stored where ordinary table access can find them, and somebody must eventually prove that no legitimate observer can need them. It does not reserve page space merely to make inserts less dense. It reserves space so an updated version can remain on-page and avoid multiplying index work. It does not fear a forgotten transaction because locks are scarce. It fears the transaction's old claim on history.

The original Time Travel feature died. Its central physical possibility was repurposed from answering "what did this row look like then?" to answering "which version is true for me now?" That is the useful continuity between POSTGRES and PostgreSQL MVCC.

DEV Community: Franck Pachot

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

A monotonic coordinate still has a finite representation

YugabyteDB: commit HybridTime becomes the MVCC time

Read time

Update time: provisional HybridTimes

Commit time: one final HybridTime

HybridTime orders causality, not an omniscient wall clock

SQL Server: XSN, LSN, and rowversion

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

MongoDB/WiredTiger: three time domains in one stack

Read and update time

Commit, oplog, and durability

What survives later?

The same questions, side by side

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

Wall-clock time is another coordinate

The practical rule

References

PostgreSQL

Oracle Database

YugabyteDB

SQL Server

MySQL/InnoDB

MongoDB and WiredTiger

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`