Editor’s Note: This article was originally authored by Inel Pandzic. Migrating data between MongoDB clusters is rarely a five-minute job. A large initial clone followed by days of change-stream replication is normal, and for all that time, Percona ClusterSync for MongoDB (PCSM) is a critical piece of your infrastructure. Until now, it was also a … Continued
The post Percona ClusterSync for MongoDB Goes Highly Available: Active-Standby Failover appeared first on Percona.
This should have been the first post of this series. A few months ago, I demonstrated (video) what I see as one of the key advantages of a document model: a compound index can support filtering, sorting, and pagination across data embedded in a one-to-many relationship.
In a normalized relational model, that relationship typically spans multiple tables. Indexes belong to individual tables, so answering the same query may require to join more rows before sorting and filtering.
That demo used MongoDB. Some MongoDB emulations on SQL databases pretend compatibility but lack performance it they don't implement MongoDB-style indexing or query plans. That's generally because the emulation is built on top of RDBMS indexes which were not designed for non-1NF schemas.
Azure DocumentDB takes a different approach: it runs as a PostgreSQL extension which, thanks to PostgreSQL’s extensibility, can define native indexing for non-relational datatypes. The DocumentDB extension uses Extended RUM indexes.
In this post, I’ll run the same as I did on MongoDB to show that DocumentDB on PostgreSQL provides the same performance with similar execution plan. You can also play with it on db<>fiddle https://dbfiddle.uk/Ft_2L34U:
This starts by creating, though the MongoDB-compatible endpoint, the collection and index with accounts and operations:
//
// Create same data as https://youtu.be/Hq9CFhxSqgw?si=g6CnDuv_PqPHRquc
//
db.accounts.createIndex({
category: 1,
"operations.date": -1,
});
function insert(num) {
const ops = [];
for (let i = 0; i < num; i++) {
const account = Math.floor(Math.random() * 10_000) + 1;
const category = Math.floor(Math.random() * 3);
const operation = {
date: new Date(),
amount: Math.floor(Math.random() * 1_000) + 1,
};
ops.push({
updateOne: {
filter: { _id: account },
update: {
$set: { category: category },
$push: { operations: operation },
},
upsert: true,
},
});
}
db.accounts.bulkWrite(ops);
}
insert(1_000); insert(1_000); insert(1_000); insert(1_000); insert(1_000);
insert(1_000); insert(1_000); insert(1_000); insert(1_000); insert(1_000);
As the account operations are embedded as an array for each account, a single index can serve filtering on account's attributes, like "category", and operation's attributes, like "date".
A simple query asks Which Category 1 account had the most recent activity? and this involves a filter on category and operation date:
db.accounts.find(
{ "category": 1 },
{ "operations.amount": 1, "operations.date": 1 }
).sort({ "operations.date": -1 }).limit(1);
This is typical of pagination, filtering a specific number of documents from an ordered result:
[
{
_id: 6115,
operations: [
{ date: ISODate('2026-09-25T22:34:03.218Z'), amount: 320 },
{ date: ISODate('2026-09-25T22:34:05.739Z'), amount: 847 }
]
}
]
The MongoDB-compatible execution plan shows that it didn't require a sort operation as the index scan provides the documents in the expected order:
db.accounts.find(
{ "category": 1 },
{ "operations.amount": 1, "operations.date": 1 }
).sort({ "operations.date": -1 }).limit(1).explain().queryPlanner.winningPlan
{
stage: 'LIMIT',
startupCost: 0,
totalCost: 0.2,
estimatedTotalKeysExamined: 1,
inputStage: {
stage: 'PROJECT',
startupCost: 0,
totalCost: 0.2,
estimatedTotalKeysExamined: 1,
inputStage: {
stage: 'FETCH',
ns: 'test.accounts',
startupCost: 0,
totalCost: 208.79,
estimatedTotalKeysExamined: 1055,
inputStage: {
stage: 'IXSCAN',
ns: 'test.accounts',
indexName: 'category_1_operations.date_-1',
direction: 'Forward',
indexUsage: {
indexKeyString: '{"category": 1,"operations.date": -1}',
isMultiKey: true,
bounds: [
'["category": [1, 1], "operations.date": DESC(MinKey, MaxKey)]'
]
},
startupCost: 0,
totalCost: 208.79,
hasOrderBy: true,
indexFilterSet: [ { category: { '$eq': 1 } } ],
estimatedTotalKeysExamined: 1055
}
}
}
}
I can run the same query, as an aggregation pipeline, on the PostgreSQL endpoint though DocumentDB functions:
EXPLAIN (COSTS OFF, ANALYZE ON, BUFFERS ON, VERBOSE ON)
SELECT document FROM documentdb_api_catalog.bson_aggregation_pipeline(
'test',
'{"aggregate": "accounts", "pipeline": [
{"$match": {"category": 1}},
{"$sort": {"operations.date": -1}},
{"$limit": 1},
{"$project": {"operations.amount": 1, "operations.date": 1}}
], "cursor": {}}'::documentdb_core.bson
);
QUERY PLAN
--------------------------------------------------------------------------
Subquery Scan on agg_stage_3 (actual time=0.111..0.112 rows=1.00 loops=1)
Output: documentdb_api_internal.bson_dollar_project(agg_stage_3.document, 'BSONHEX31000000106f7065726174696f6e732e616d6f756e740001000000106f7065726174696f6e732e64617465000100000000'::documentdb_core.bson, 'BSONHEX12000000096e6f7700d660b4daa001000000'::documentdb_core.bson)
Buffers: shared hit=5
-> Limit (actual time=0.101..0.101 rows=1.00 loops=1)
Output: collection.document, (documentdb_api_catalog.bson_orderby(collection.document, 'BSONHEX1a000000106f7065726174696f6e732e6461746500ffffffff00'::documentdb_core.bson))
Buffers: shared hit=5
-> Custom Scan (DocumentDBApiExplainQueryScan) (actual time=0.100..0.100 rows=1.00 loops=1)
Output: collection.document, documentdb_api_catalog.bson_orderby(collection.document, 'BSONHEX1a000000106f7065726174696f6e732e6461746500ffffffff00'::documentdb_core.bson)
namespaceName: test.accounts
indexName: category_1_operations.date_-1
indexKey: {"category": 1,"operations.date": -1}
isMultiKey: true
indexBounds: ["category": [1, 1], "operations.date": DESC(MinKey, MaxKey)]
innerScanLoops: 1 loops
scanType: ordered
scanKeyDetails: key 1: [(isInequality: true, estimatedEntryCount: 116)]
_id_: (startup cost=0.282, total cost=287.743, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=6328, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)
Buffers: shared hit=5
-> Index Scan using "category_1_operations.date_-1" on documentdb_data.documents_2 collection (actual time=0.055..0.055 rows=1.00 loops=1)
Output: collection.document
Index Cond: (collection.document OPERATOR(documentdb_api_catalog.@=) 'BSONHEX130000001063617465676f7279000100000000'::documentdb_core.bson)
Order By: (collection.document OPERATOR(documentdb_api_catalog.|-<>) 'BSONHEX1a000000106f7065726174696f6e732e6461746500ffffffff00'::documentdb_core.bson)
Index Searches: 0
Buffers: shared hit=5
Planning:
Buffers: shared hit=518
Planning Time: 1.605 ms
Execution Time: 0.202 ms
The Index Scan covered the $match filter with Index Cond, the $sort with Order By, the $limit: 1 with rows=1.00 and the $projection with Output. There no sort or filter above it. The Custom Scan is a purely decorative wrapper that DocumentDB injects around the real index access path so that EXPLAIN can expose MongoDB-style index metadata, like isMultiKey: true and the indexBounds.
I got the remark that the planning time is huge here Planning Time: 1.605 ms. I've ran it on db<>fiddle which use micro-VM for fast ephemeral instances, so don't compare time. But still, Buffers: shared hit=518 as a lot and this is due to the first query in a PostgreSQL session that reads from the catalog. If I had run the query with explain first to check the result (https://dbfiddle.uk/eFaEQ0Gq), the next query would have had much faster planning:
Planning:
Buffers: shared hit=2
Planning Time: 0.126 ms
Execution Time: 0.104 ms
The video demonstrated the main advantage of a document model with multi-key indexes on MongoDB. The same exists open-source on PostgreSQL with the DocumentDB extension. Extended RUM was added in v0.106 (August 29, 2025), ordered indexes/scans were enabled by default in v0.109 (March 09, 2026) - be sure that documentdb_extended_rum is listed in shared_preload_libraries - and later releases improved edge cases of it (e.g. collation support across 0.111–0.113, multikey fixes in 0.116).
DocumentDB 0.116-0, released August 20, 2026, introduced prefix sort pushdown for $group accumulators. It lets the grouping stage consume rows in the existing sort order, avoiding a separate blocking sort or hash aggregate.
The previous article in this series covered a different optimization: a distinct scan that skips duplicate index entries when a group returns one row per distinct key. Prefix sort pushdown does not skip entries. It removes the top-level sort. The query pattern is also related to the first-per-group example in First/Last per Group: PostgreSQL DISTINCT ON and MongoDB DISTINCT_SCAN Performance, which shows MongoDB using DISTINCT_SCAN.
DocumentDB implements the MongoDB API as a fully open-source PostgreSQL extension. It offers an open alternative for MongoDB applications and exposes aggregation execution through both MongoDB-like and PostgreSQL plans, providing insight into sorts, buffers, and temporary I/O. Microsoft is the main contributor, with improvements coming from enterprise experience with Azure DocumentDB.
To demonstrate prefix sort pushdown, I load 50,000 documents across 100 groups, add a 200-byte payload, and create an ordered index on {a: 1}. The pipeline sorts by a, groups by the same key, and returns the first name in each group. Because the group key is a prefix of the sort keys, the accumulator can use the existing index order instead of sorting all 50,000 rows again.
MongoDB, DocumentDB 0.114, and DocumentDB 0.116 receive the same data, ordered index, hint, and pipeline. Both DocumentDB versions get the same VACUUM (ANALYZE), and both native runs use the same planner settings. The documentdb.enableSortPushToAccumulatorWithPrefix setting exists in 0.114 but is disabled by default. It is enabled by default in 0.116. I compare explicit sort nodes, temporary I/O, rows, and buffers rather than total elapsed time. The only timing I cite is when the gateway's group stage starts producing rows (executionStartAtTimeMillis), which shows whether grouping blocks or streams. In the native (SQL function) runs, I set enable_hashagg=off (and enable_seqscan / enable_bitmapscan) to force the sorted plan, but the gateway (MongoDB-compatible endpoint) runs use default settings.
I first run the pipeline on MongoDB Atlas 8.0:
db = db.getSiblingDB("perf116");
db.sort_group.drop();
db.sort_group.insertMany(Array.from({length: 50000}, (_, index) => ({
_id: index + 1,
a: (index + 1) % 100,
b: (50000 - index - 1) % 1000,
name: `name_${index + 1}`,
payload: "x".repeat(200)
})));
db.sort_group.createIndex({a: 1});
const pipeline = [
{$sort: {a: 1}},
{$group: {_id: "$a", firstVal: {$first: "$name"}}}
];
print(EJSON.stringify({
resultCount: db.sort_group.aggregate(
pipeline,
{hint: "a_1"}
).toArray().length,
explain: db.sort_group.explain("executionStats").aggregate(
pipeline,
{hint: "a_1"}
)
}, null, 2));
It shows a DISTINCT_SCAN:
{
"resultCount": 100,
"explain": {
"explainVersion": "1",
"stages": [
{
"$cursor": {
"queryPlanner": {
"namespace": "perf116.sort_group",
"parsedQuery": {},
"indexFilterSet": false,
"queryHash": "DC5C6196",
"planCacheShapeHash": "DC5C6196",
"planCacheKey": "979F6906",
"optimizationTimeMillis": 0,
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"prunedSimilarIndexes": false,
"winningPlan": {
"isCached": false,
"stage": "FETCH",
"inputStage": {
"stage": "DISTINCT_SCAN",
"keyPattern": {
"a": 1
},
"indexName": "a_1",
"isMultiKey": false,
"multiKeyPaths": {
"a": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"a": [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 100,
"executionTimeMillis": 2,
"totalKeysExamined": 100,
"totalDocsExamined": 100,
"executionStages": {
"isCached": false,
"stage": "FETCH",
"nReturned": 100,
"executionTimeMillisEstimate": 0,
"works": 101,
"advanced": 100,
"needTime": 0,
"needYield": 0,
"saveState": 3,
"restoreState": 3,
"isEOF": 1,
"docsExamined": 100,
"alreadyHasObj": 0,
"inputStage": {
"stage": "DISTINCT_SCAN",
"nReturned": 100,
"executionTimeMillisEstimate": 0,
"works": 101,
"advanced": 100,
"needTime": 0,
"needYield": 0,
"saveState": 3,
"restoreState": 3,
"isEOF": 1,
"keyPattern": {
"a": 1
},
"indexName": "a_1",
"isMultiKey": false,
"multiKeyPaths": {
"a": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"a": [
"[MinKey, MaxKey]"
]
},
"keysExamined": 100
}
}
}
},
"nReturned": 100,
"executionTimeMillisEstimate": 3
},
{
"$groupByDistinctScan": {
"newRoot": {
"_id": "$a",
"firstVal": "$name"
}
},
"nReturned": 100,
"executionTimeMillisEstimate": 3
}
],
"queryShapeHash": "0822C17532FC5F85649D595163EF78A0BAEF5CDA8811F1689D203E1AD79FD2F7",
"serverInfo": {
"host": "a004f7434a57",
"port": 27017,
"version": "8.0.28",
"gitVersion": "cd6fc9b3b7cf87ff2bbca0af67382ac407fc682a"
},
"serverParameters": {
"internalQueryFacetBufferSizeBytes": 104857600,
"internalQueryFacetMaxOutputDocSizeBytes": 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
"internalDocumentSourceGroupMaxMemoryBytes": 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
"internalQueryProhibitBlockingMergeOnMongoS": 0,
"internalQueryMaxAddToSetBytes": 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600,
"internalQueryFrameworkControl": "trySbeRestricted",
"internalQueryPlannerIgnoreIndexWithCollationForRegex": 1
},
"command": {
"aggregate": "sort_group",
"pipeline": [
{
"$sort": {
"a": 1
}
},
{
"$group": {
"_id": "$a",
"firstVal": {
"$first": "$name"
}
}
}
],
"hint": "a_1",
"cursor": {},
"$db": "perf116"
},
"ok": 1
}
}
MongoDB recognizes the first-per-group pattern. It uses DISTINCT_SCAN on a_1, examines 100 index keys, fetches 100 documents for name, and returns 100 groups. There is no blocking sort.
I load and index the same data through the DocumentDB 0.114 gateway (MongoDB-compatible endpoint):
db = db.getSiblingDB("perf116");
db.sort_group.drop();
db.sort_group.insertMany(Array.from({length: 50000}, (_, index) => ({
_id: index + 1,
a: (index + 1) % 100,
b: (50000 - index - 1) % 1000,
name: `name_${index + 1}`,
payload: "x".repeat(200)
})));
db.sort_group.createIndex(
{a: 1},
{storageEngine: {enableOrderedIndex: true}}
);
print(EJSON.stringify({
insertedDocuments: db.sort_group.countDocuments(),
indexes: db.sort_group.getIndexes().map(index => index.name)
}, null, 2));
{
"insertedDocuments": 50000,
"indexes": [
"_id_",
"a_1"
]
}
Connected to the PostgreSQL endpoint, I make the table statistics and visibility state deterministic:
\pset pager off
SET search_path TO documentdb_api_catalog, public;
SELECT extversion AS documentdb_version
FROM pg_extension
WHERE extname = 'documentdb';
SELECT collection_id
FROM collections
WHERE database_name = 'perf116' AND collection_name = 'sort_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;
SELECT :'collection_id' AS vacuumed_collection_id;
documentdb_version
--------------------
0.114-0
(1 row)
vacuumed_collection_id
------------------------
2
(1 row)
Then I run the same hinted pipeline on the MongoSH:
db = db.getSiblingDB("perf116");
const pipeline = [
{$sort: {a: 1}},
{$group: {_id: "$a", firstVal: {$first: "$name"}}}
];
print(EJSON.stringify({
resultCount: db.sort_group.aggregate(
pipeline,
{hint: "a_1"}
).toArray().length,
explain: db.sort_group.explain("executionStats").aggregate(
pipeline,
{hint: "a_1"}
)
}, null, 2));
{
"resultCount": 100,
"explain": {
"explainVersion": 2,
"command": "db.runCommand({explain: { 'aggregate': 'sort_group', 'pipeline': [{ '$sort': { 'a': 1 } }, { '$group': { '_id': '$a', 'firstVal': { '$first': '$name' } } }], 'hint': 'a_1', 'cursor': {} }})",
"explainCommandPlanningTimeMillis": 2.649,
"explainCommandExecTimeMillis": 349.424,
"stages": [
{
"$cursor": {
"queryPlanner": {
"winningPlan": {
"stage": "FETCH",
"startupCost": 0,
"totalCost": 13.89,
"estimatedTotalKeysExamined": 5556,
"inputStage": {
"stage": "IXSCAN",
"indexName": "a_1",
"direction": "Forward",
"startupCost": 0,
"totalCost": 13.89,
"hasOrderBy": true,
"indexFilterSet": [
{
"a": {
"$range": {
"orderByScan": 1
}
}
}
],
"estimatedTotalKeysExamined": 5556
}
}
},
"executionStats": {
"nReturned": 50000,
"executionTimeMillis": 125.407,
"executionStartAtTimeMillis": 0.036,
"totalDocsExamined": 50000,
"totalKeysExamined": 50000,
"executionStages": {
"stage": "FETCH",
"nReturned": 50000,
"executionTimeMillis": 125.407,
"executionStartAtTimeMillis": 0.036,
"totalKeysExamined": 50000,
"numBlocksFromCache": 50023,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 50000,
"executionTimeMillis": 125.407,
"executionStartAtTimeMillis": 0.036,
"indexName": "a_1",
"totalKeysExamined": 50000,
"numBlocksFromCache": 50023
}
}
}
}
},
{
"$group": {
"queryPlanner": {
"winningPlan": {
"stage": "GROUP",
"startupCost": 111.12,
"totalCost": 236.13,
"aggStrategy": "Hashed",
"estimatedTotalKeysExamined": 5556,
"inputStage": {
"stage": "PROJECTION_DEFAULT",
"startupCost": 0,
"totalCost": 83.34,
"estimatedTotalKeysExamined": 5556
}
}
},
"executionStats": {
"nReturned": 100,
"executionTimeMillis": 349.126,
"executionStartAtTimeMillis": 348.869,
"totalDocsExamined": 100,
"totalKeysExamined": 100,
"executionStages": {
"stage": "GROUP",
"nReturned": 100,
"executionTimeMillis": 349.126,
"executionStartAtTimeMillis": 348.869,
"totalDocsExamined": 100,
"totalKeysExamined": 100,
"numBlocksFromCache": 50023,
"inputStage": {
"stage": "PROJECTION_DEFAULT",
"nReturned": 50000,
"executionTimeMillis": 255.141,
"executionStartAtTimeMillis": 0.041,
"totalDocsExamined": 50000,
"totalKeysExamined": 50000,
"numBlocksFromCache": 50023
}
}
}
}
}
],
"ok": 1
}
}
With default settings, the gateway plan uses a hash aggregate (aggStrategy: "Hashed"), which cannot return any group until it reads all 50,000 rows. The group stage starts at 348.9 ms. The MongoDB-compatible explain doesn't show buffers or temporary I/O, so I inspect the native plan from the PostgreSQL endpoint. I disable hash aggregation there to see the sort-based plan that the new optimization targets:
\pset pager off
\set ON_ERROR_STOP on
SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog,
documentdb_api_internal, public;
SELECT extversion AS documentdb_version
FROM pg_extension
WHERE extname = 'documentdb';
SELECT documentdb_api.drop_collection('perf116', 'sort_group');
SELECT count(documentdb_api.insert_one(
'perf116',
'sort_group',
format(
'{"_id":%s,"a":%s,"b":%s,"name":"name_%s","payload":"%s"}',
i, i % 100, (50000 - i) % 1000, i, repeat('x', 200)
)::documentdb_core.bson,
Valkey Memory Optimization, Version by Version and Encoding: How Many Bytes Did Each Release Actually Save?
Introduction This post is based on a rigorous benchmark and a structural teardown of the source, aimed at answering three questions: For the same data, how much memory does each version actually save? Where exactly does that saving come from at the data-structure level? How do different data structures and encoding result in significantly different … Continued
The post Valkey Memory Optimization, Version by Version and Encoding: How Many Bytes Did Each Release Actually Save? appeared first on Percona.
Measuring the Performance of Deterministic Exceptions
I have always been interested in Zero-Overhead Deterministic
Exceptions from P0709, as they promised a nicer implementation model
and they are much easier to integrate into JIT-compiled code. Some people
forbid regular C++ exceptions, due to code size and performance
concerns, which are concerns that P0709 explicitly wants to address.
Unfortunately, there was no implementation available. But now, with
LLMs, doing this kind of experiment is much more tractable. I basically
gave the official P0709 specification to an LLM, and it built a first
prototype in one shot. Admittedly my initial enthusiasm cooled down a
bit after I found a lot of problems and corner cases, but after about
one day of work I was able to build a reasonable
prototype, which is something that would easily have taken me weeks
or even months in the past. I tested it with complex code, and it
handled everything fine, but note that it is still not production ready
(e.g., Itanium ABI only).
That prototype now allows us to quantify the differences between
exception implementations. P0709 describes two ways to implement them:
Either pass a hidden pointer to an error state to all functions that are
marked with throws (we call this strategy
pointer here), or use the carry bit to indicate errors and
pass the error itself in registers if the bit is set (we call this
strategy carry). That is a very nifty approach suggested by
Herb Sutter, but it somewhat clashes with some of the function epilogues
(e.g., on Windows), and such a flag is not available on all platforms.
We thus introduced a third variant register here that
simply uses another unused caller-saved register as an error indicator,
which is more portable. The classic C++ exception handling mechanism we
call dynamic.
Microbenchmarks
We first run some microbenchmarks
to see the overall effects. These over-emphasize the differences as the
benchmarks basically do nothing besides function calls (ns per outer
call):
Test
failures
dynamic
pointer
register
carry
int through 8 calls0%
6.02
6.09
6.08
6.16
1%
17.5
7.10
6.31
6.33
10%
101
7.59
6.85
6.88
50%
467
9.48
9.26
9.11
forwarding chain, 8 tail calls
0%
1.54
1.34
1.52
1.59
50%
188
4.74
4.31
4.28
void through 4 calls0%
3.00
3.09
3.05
3.06
1%
9.82
3.33
3.32
3.31
16-byte struct (two registers)
0%
3.01
3.24
3.06
3.06
32-byte struct in memory
0%
5.40
6.06
5.75
5.44
1%
11.3
6.03
5.60
5.43
50%
314
6.26
5.93
5.92
pointer, single call
0%
0.75
0.95
0.75
0.75
50%
188
3.66
3.29
3.08
We notice several things. First, the pointer strategy
often has noticeable overhead due to additional memory instructions, and
is inferior to the other two. register and
carry are nearly identical, which, given the fragility of
the carry approach, means that register seems
to be the preferred choice. Compared to classical C++ exceptions,
performance of register is within 1-2% on the happy path,
where no exception occurs, and it is dramatically faster, up to orders
of magnitude, if exceptions indeed occur.
A more realistic workload
These numbers over-inflate the differences because the functions do
so little work. As a more plausible workload, we use RapidJSON and benchmark
the performance on the nativejson-benchmark,
optionally corrupting some inputs to trigger exceptions. RapidJSON can
be configured to report failures by error codes instead of exceptions;
we call that strategy codes here.
The full results are here;
we show a selection below. What the numbers basically show is 1)
deterministic exceptions with the register strategy have
the same performance and the same code sizes as explicit error codes, 2)
performance differences from traditional C++ exceptions are largely
within the noise even on the happy path, and 3) in the case of errors
they are vastly superior to traditional C++ exceptions.
Happy path: SAX parsing
MB/s, higher is better, best of 5 runs; in parentheses: change
relative to codes.
codesdynamicpointerregistercarry
canada.json
1635.8
1606.6 (-1.8%)
1665.1 (+1.8%)
1606.1 (-1.8%)
1603.3 (-2.0%)
citm_catalog.json
2212.7
2216.0 (+0.1%)
2356.3 (+6.5%)
2206.3 (-0.3%)
2341.5 (+5.8%)
twitter.json
1365.2
1376.5 (+0.8%)
1425.4 (+4.4%)
1377.5 (+0.9%)
1472.0 (+7.8%)
Happy path: retired
instructions per parse
Instructions (perf stat), lower is better; in parentheses: change
relative to codes.
codesdynamicpointerregistercarry
sax canada.json
49555378
49903680 (+0.7%)
50294221 (+1.5%)
50850829 (+2.6%)
50738702 (+2.4%)
sax citm_catalog.json
20552806
20212949 (-1.7%)
20761272 (+1.0%)
20802928 (+1.2%)
20730816 (+0.9%)
sax twitter.json
11758968
11547972 (-1.8%)
11799096 (+0.3%)
11850736 (+0.8%)
11813765 (+0.5%)
Errors:
small documents, a fraction of them corrupted
ns per document, lower is better, best of 5 runs; in parentheses:
change relative to codes.
codesdynamicpointerregistercarry
0%
1726.1
1692.2 (-2.0%)
1729.3 (+0.2%)
1720.5 (-0.3%)
1721.0 (-0.3%)
1%
1707.3
1812.3 (+6.2%)
1703.3 (-0.2%)
1707.7 (+0.0%)
1713.9 (+0.4%)
10%
1600.8
1848.9 (+15.5%)
1597.3 (-0.2%)
1598.3 (-0.2%)
1614.3 (+0.8%)
50%
1256.5
1895.7 (+50.9%)
1263.6 (+0.6%)
1264.2 (+0.6%)
1275.3 (+1.5%)
100%
868.6
1934.7 (+122.7%)
871.8 (+0.4%)
869.3 (+0.1%)
882.1 (+1.6%)
Size of the parser
Bytes of all GenericReader functions,
GenericDocument::ParseStream and parseSAX
(into which parts of the parser are inlined); in parentheses: change
relative to codes.
codesdynamicpointerregistercarry
code
17191
18162 (+5.6%)
17540 (+2.0%)
17204 (+0.1%)
17289 (+0.6%)
unwind info
1292
1500 (+16.1%)
1340 (+3.7%)
1268 (-1.9%)
1248 (-3.4%)
exception tables
0
388
0
0
0
total
18483
20050 (+8.5%)
18880 (+2.1%)
18472 (-0.1%)
18537 (+0.3%)
Conclusion
Given that “exceptions” might not always be that exceptional (e.g.,
when parsing large amounts of user input), deterministic exceptions
indeed seem to be a very good idea, and P0709 indeed should be adopted
by the C++ standard. Not that this is likely to happen, given the lack
of progress on this front, but now we at least have numbers to talk
about.
Signed, Unsigned, Misaligned: Lessons from Assembly to SQL
Remember how you learned counting before school?
One, two, buckle my shoe,
Three, four, knock at the door,
Five, six, pick up sticks,
Seven, eight, lay them straight,
Nine, ten, a big fat hen.
There were no negative numbers, no decimals, no fractions, and certainly no floating point numbers.
Of course, one of the reasons to start with natural integers is that it’s simple and maps to your fingers. But like your fingers, most things in the real world are described by positive numbers. The number of kids in the class, your age, the weight of your bag, and the amount of money you have (kids normally don’t have debt).1
Later in life, you learn about negative numbers and fractions. The difference between two values can be negative, years in history are before Christ, and of course your account balance. So you need lots of different number types to map all the data you gather. But for most things in life, unsigned integers are the right choice.
Whether it’s how much inventory is in stock, the number of rows in a table, the IP port number, the ID of a row in a database, an array index, or loop iterations: pretty much everything in real life and a lot in computing is a positive number. This holds true especially in SQL where we have NULL-value semantics, so we don’t need -1 as a special case for unknown.
This article gives an overview of how we at CedarDB handle type information and make sure that your SQL query is both fast and correct. We cover why signedness matters, from the machine instructions your CPU runs up to the columns you declare, and why a Parquet file full of unsigned integers is what finally made us expose them in SQL.
Being a compiling system, we need several type systems that interoperate to make sure sign information does not get lost on the way from SQL user input through our C++ implementation down to the generated machine code. We walk down through those layers in a follow-up post.
Everything Is a Number
Integer types are the foundation of every programming language. Whether you are a systems programmer, embedded developer, or web developer, you always need them.
They come in different flavors: signed for differences and offsets, unsigned for identifiers and addresses.
But in the end, it all boils down to ones and zeros we interpret differently depending on what we need. uint32_t, int, and float are all 4 bytes in size, as is the emoji 😀 in UTF-8.
For example, the emoji 😀 is stored as the 4 bytes 0xF0 0x9F 0x98 0x80 when we encode it as UTF-8.2 Read as one 32-bit value, that is 0xF09F9880. Feed those exact same bytes to different types and you get:3
Type
Value
uint32_t4036991104
int32_t-257976192
float≈ -3.95e29
char[4] (UTF-8)😀
As you can see, the same bits can mean wildly different things.
Isn’t Number Enough of a Type?
In assembly, the bedrock of programming, we notice there are no types. If you have never looked at assembly before, think of registers as named buckets of bits.
There are just registers and they can store everything. The only differentiation is the size, whether it’s 8 bits, 8 bytes, or a larger vector register (x86 register overview).4
The same storage can be addressed at four different widths.
But is the fact that assembly has no types even right?
How can the computer differentiate between the two comparisons if there are no types?
bool biggerSigned(const int8_t* s, int limit) {
return *s > limit;
}
bool biggerUnsigned(const uint8_t* u, unsigned limit) {
return *u > limit;
}
When looking at the assembly, we see that registers don’t have types but assembly does.
Depending on the signedness, we use different assembly instructions.
In this case, either set if less setl for the signed case, or set if below setb for the unsigned case. On x86 less and greater means signed, while below and above indicate unsigned comparisons.
; *s > limit (signed)
movsx eax, byte ptr [rdi] ; sign-extend s into register
cmp esi, eax
setl al ; set al=1 if less (signed)
; *u > limit (unsigned)
movzx eax, byte ptr [rdi] ; zero-extend u into register
cmp esi, eax
setb al ; set al=1 if below (unsigned)
The result depends on instruction and MSB of the contained value. Nothing in the register says whether `AL` holds 200 or -56.
Notice that the load instruction differs too: movsx sign-extends the signed value, movzx zero-extends the unsigned one.
The same holds, e.g., for division instructions or moves with either zero or sign extension.
So the type is implicitly given by choosing the right instruction.
When reading assembly, we can still infer the data type in most places, but that’s quite tedious.
Encoding the type information in the operations means that whoever or whatever writes the assembly needs to keep track of the types. While this is standard procedure for compilers, it’s quite demanding for programmers, which is one of the reasons why most people don’t program in assembly any more. Tracking types by hand opens endless room for errors. Unless, of course, you have always been curious what sqrt(😀) is.5
High-Level Programming Languages
Now let’s go to the other extreme: how do high-level languages handle number types?
At the bottom of the ladder, JavaScript only has support for doubles and not even integers.6 PHP at least stores integers and floats differently. Neither differentiates between signed and unsigned, a clear sign of how far removed from the hardware these languages are.
Java, R, and SQL are a step up: they distinguish between integers and floats, so you won’t accidentally mix them and get flaky results due to numeric instabilities. But signed and unsigned remain the same type here too.
Java noticed the gap about 10 years ago and added the Integer.divideUnsigned function, and for PostgreSQL you can install an extension for custom unsigned types. Some control, but not the full picture.
Systems Programming
Full control over the datatypes, however, is crucial for systems programming. If you disagree, think of the first time you had to debug code like this:
unsigned count = 10;
while (count >= 0) {
std::cout << count << '\n';
count--;
}
If you don’t immediately see the problem, think of when count >= 0 holds true and why the answer is always true!
Static type checking can do a lot of good here. It’ll warn you at compile time that your code won’t terminate.
The mirror image happens with signed values. A classic trap is a count that can go negative meeting an unsigned loop bound:
int n = count_results(); // -1 on error
for (size_t i = 0; i < n; ++i) {
process(buffer[i]);
}
Signed intuition says that if n is -1, then 0 < -1 is false, so the loop body never runs. But n is compared against size_t i, so it is implicitly converted to SIZE_MAX, which is the largest unsigned value. Now 0 < SIZE_MAX is true, and the loop you expected to skip on error instead runs far past the end of the buffer — reading out of bounds almost immediately, and, absent a crash, looping for about 200 years on a 3 GHz machine.7
Both bugs have the same root cause: the programmer’s mental model uses signed semantics, but the machine applies unsigned arithmetic. This is also precisely why SQL’s NULL is a better tool than -1 for representing missing values.
Nullability vs Unsignedness
The intent of -1 in count_results() in the previous example was to mark an invalid value, since a count cannot be negative. The only reason it’s declared an int was the error case.
Comparing nullability with unsignedness seems odd at first glance. But they have two things in common.
First, strictly speaking, neither is a first class citizen of the SQL standard. Nullability is expressed as a constraint, and unsigned simply is not defined.
And second, you often use them to report invalid results, as shown above.
-1 to Mark Invalid Numbers
Way before C++ had optionals, SQL had nullability. This prevents programmers from the unfortunate habit of using -1 or even worse 99998 or January 1, 17539 for invalid values.
Using negative numbers just for error codes wastes half of the number range, which is by far not the worst thing here.
Imagine your unsigned data neatly stored into an int column with some -1 outliers. You cannot take an average, or sum.
You always need to handle your special values.
SQL nullability gives you a better tool: instead of hijacking a valid number to mean “nothing”, you declare that the value simply does not exist. No magic constants, no corrupted aggregates, no silent bugs.
Left: a tree with no leaves, which is still a tree. Right: no tree. The first is `0`, the second is `NULL`.
The number zero is not the same as nothing, like a tree without leaves is not the same as no tree.
Constraints in SQL
You can express unsigned numbers in plain PostgreSQL as:
CREATE TABLE inventory (
product_id INTEGER,
quantity INTEGER CHECK (quantity >= 0)
);
This gives us few advantages but big disadvantages. On the upside, we now have positive numbers in the column.
But the price we pay is that we waste half of the representable range and have to evaluate the constraint for each compute step.
You add two numbers, check the constraint, insert one, check the constraint. And you cannot even store larger numbers here. So it’s not worth it.
Furthermore it gives you a false sense of safety. It only guarantees that the values stored in the table are positive, not that they stay positive during a query. Subtract two quantities and you are negative again, so nothing downstream can rely on it either.
So we need both: values that may be absent (NULL), and values that are never negative (unsigned). SQL gives us only the first one.
Unsigned Types in CedarDB
The SQL standard does not define unsigned datatypes. To be fair to the standard, it also does not define how to page through a result set, so unsigned numbers are in good company here. The common workaround is constraints, but like NOT NULL, it makes more sense to implement the type properly. This gives it a wider range and avoids the overhead of constraint evaluation on every operation.
At CedarDB we believe you should store data in its most natural representation and avoid unnecessary conversions. So we expose the unsigned types we already used internally directly to the user. We used this as an opportunity to refactor our type system, making it easier to add new types and functions going forward.
The driving motivation for integration was Parquet. Parquet’s integer type carries a signedness flag right next to its bit width, and files in the wild use it for identifiers, offsets, counters, and byte sizes. If you are parsing Parquet data types, we can use the native unsigned type directly, otherwise UInt64 columns would have to be widened to 128-bit numerics just to avoid overflow. That costs memory and speed on every single value, for data that would perfectly fit into 64 bit wide registers.
Unlike in C and C++ which inherited it, unsigned does not lead to silent wrap-arounds. We check for overflow on signed and unsigned arithmetic alike, so subtracting 5 from a quantity of 3 raises an error instead of handing you 4294967294. How we do that without giving up performance is a story of its own, in our posts on overflow handling and vectorized overflow checking.
Unsigned types cast implicitly to any type that can always hold them, so mixing them with signed integers just works. Explicit casts let you convert back to unsigned whenever needed. So if you choose not to use unsigned numbers, you will never know they exist.
Using Unsigned Numbers
Everything sounds nice and easy, and using them is the same. Just declare the column with the type you want:
CREATE TABLE inventory (
product_id uint4,
quantity uint4
);
If you already worked with Parquet files following our examples, chances are high you’re already working with unsigned numbers and didn’t notice.
You can find more examples in the docs.
Making unsigned a first-class citizen throughout the whole code-generating system takes more than just adding it to the catalog. In the next blog post we go from the ground up through our type system. We will look where sign information is tracked and where it deliberately is not, why PostgreSQL’s oid is an unsigned in disguise, and what the System V ABI forgot to specify.
Want to see it in action? Load a Parquet file into CedarDB and the right unsigned types come along for free.
-
Although money is a decimal, it does not have to be. Reporting your net worth in cents is both technically correct and psychologically effective. ↩︎
-
How a character turns into bytes depends on the encoding. The same emoji is 0x00 0x01 0xF6 0x00 in UTF-32BE, and Python’s '😀'.encode('utf-32') even prepends a byte order mark. Some emoji are not a single codepoint at all, but a sequence glued together with zero-width joiners. This article covers the rest. ↩︎
-
Reading four bytes as one number is itself an interpretation. We use the big-endian reading here, the order we wrote the bytes in. On a little-endian machine such as x86, loading the same bytes into a uint32_t gives you 2157486064 instead. ↩︎
-
Yes, there are different registers for integers and floats. You can also store integers in vector registers for SIMD processing, or floats in regular registers for bit tricks. ↩︎
-
It’s NaN. sqrt takes a float, and as a float 😀 is negative. ↩︎
-
JavaScript engines do support integers internally, but only as a storage optimization for typed arrays, not as an exposed type. ↩︎
-
Yes, that’s an em-dash I put there. I liked to use them before they became a symbol of LLM generated content and it’s time, we are taking them back. For now I just put one into the post to not overdo it. ↩︎
-
In METAR, the standard format for aviation weather reports, horizontal visibility is given in meters and caps out at four digits, so 9999 means “10 km or more” rather than “9,999 meters”. See the METAR explanation in the IVAO documentation. ↩︎
-
Microsoft’s Date Data Type documentation for Dynamics NAV. The date range starts at January 1, 1753, which is the lower bound of SQL Server’s datetime, which in turn dates back to Britain switching to the Gregorian calendar in 1752. ↩︎
In many real production databases, we often see some tables receiving high-frequency updates (millions per day) and autovacuum running repeatedly (hundreds of times per day). Additionally, those tables can sometimes become heavily bloated. The end effect is poor performance, poor concurrency, and an unmanageable database. In case we are using methods pg_gather for diagnosis, such … Continued
The post PgQ and PgQue: Workflow Engines you might need in PostgreSQL appeared first on Percona.
Percona Toolkit’s pt-online-schema-change (pt-osc) has long been the preferred solution for performing online schema changes with minimal downtime. Its chunk-based copy algorithm is designed to adapt dynamically to the workload, making it suitable for very large tables in production environments. However, under specific conditions, one of its optimization mechanisms can become counterproductive. During a customer … Continued
The post When pt-online-schema-change “where” Meets Galera: Understanding Chunk Auto-Resize and Flow Control appeared first on Percona.
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.
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.
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... (truncated)
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.
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').
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.
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.
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.
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
This reproduction confirms the performance issue is mainly a plan-shape problem, not just a miss... (truncated)
General methods that scale with computation will inevitably displace hand-engineered domain knowledge! Sutton’s Bitter Lesson hangs like a Sword of Damocles over all of us. This paper, which just dropped, applies that lesson to data agents, and says that as LLMs improve, they will quickly absorb the agent scaffolding researchers have spent the last few years painstakingly building. It argues that researchers should instead work on building curated contextual information about the data environment (aka. persistent semantic context) to help data agents be efficient across many queries.
The evaluation section aims to capture the Bitter Lesson in action. The authors compared general coding agents (the Codex harness with no task-specific engineering) against state-of-the-art human-designed data agents on two benchmarks, TAG-Bench and DAB. They use the same models on both sides, so the only variable is the scaffolding.
They find that:
Side Remark: Interestingly, the authors use these efficiency gains to bury their own prior work (in this case Ion and Matei's) from just a year ago. "Supporting Our AI Overlords" argued database systems would be overwhelmed by "agentic speculation": massive bursts of inefficient queries from confused models. Now they say the opposite, that models formulating correct answers efficiently "directly challenges the premise of recent work [11]". Them are fighting words. Why bury your own work when others would happily do it for you? For the record, I still think the direction in the Overlords paper is valid, and we should work on designing data systems for bursty AI workloads. Cheaper per query is not the same as less work for the database. This is Jevons paradox. If a query costs a fraction of what it used to, we will point many more agents at the data, running longer tasks, in parallel, in the background. Secondly, the main point of the Overlords paper was that queries are varied, yet our databases are built for repetitive workloads. Fewer turns per query does not make the queries look any more alike, so the load still arrives in bursts, and data systems will still need to be designed for it.
Ok, those efficiency gains are splendid, but they also expose new bottlenecks. Schema exploration increases from 16% of the turns with o3 to 25% with GPT-5.6 Sol. While it shrank in absolute terms, it now becomes the biggest remaining slice. The failure analysis also says the same thing. Once execution and coding errors are largely eliminated, over 60% of the remaining failures for GPT-5.6 Sol are semantic mistakes, such as not knowing the organization's idiosyncratic business definitions, join keys, or undocumented schemas. The model got smarter, but it still doesn't know about the intricacies of your data environment.
This motivates their research agenda proposal, which they test at small scale. When the authors let the AI "self-curate" a persistent context document by exploring the databases and 12 sample queries, and injected that text into the initial prompt, agent accuracy jumped by up to 19 percentage points.
But these improvements come with a price tag. Table 2 shows the cost of building these contexts, and some of them are very expensive. In their tiny setting (just 12 sample queries and 12 datasets) the schema-focused context took nearly an hour (3,400 seconds) and $9.60 to build, and GEPA cost $12.16. If you extrapolate that to an enterprise environment with terabytes of data and thousands of tables, the overhead becomes astronomical.
To address these overheads and the model's lack of environmental knowledge, the authors propose building a persistent semantic context layer. This context would be built offline, stored by the database system, and served to the agent to prevent redundant exploration. Section 3 outlines this agenda, dividing the problem into maintaining semantic consistency and designing efficient physical data structures.
Unfortunately, this section is the weakest part of the paper. Even when taking into account this is a position paper, the proposal remains superficial. It lists high-level categories like "consistency scope" or "consistency models" without offering technical solutions or even analysis.
There may also be a deeper problem here. The paper itself shows that agents can author their own context offline. If we take the Bitter Lesson to heart, why do we need the database community to build the semantic context layer? With improved models, agents will likely figure out their own memory formats, how to keep them consistent, and how to lay them out on disk, which is all of Section 3. No?
I actually like this problem a lot. The problem is real, and the economics get worse the bigger the organization. The same thing shows up one level above, in software engineering. Here hundreds of engineers point models at a large existing codebase, and every session spends time/money again to rediscover the conventions and ownership boundaries the organization already knows. The costs become prohibitive quickly.
The paper outlines several architectural directions for implementing this persistent semantic context layer natively within future data systems. One approach is to build the layer as a dependency graph, where business definitions and schema rules are linked as traceable nodes. Another option is treating the semantic layer like a live materialized view, using database events to trigger targeted AI rewrites whenever the underlying data shifts. The idea is to shift AI memory from a static text file into a metadata-driven component of the database.
Of course, I have my thoughts and biases on this. I think TLA+ deserves serious consideration at this semantic context layer. As Boris Cherny recently highlighted, formal specification/verification languages like TLA+ should no longer be seen as niche. He showed how he uses Opus 5.5 to verify a codebase, finding hidden bugs and race conditions with TLA+ in just a few short prompts. These models are now fluent enough in TLA+ that you do not need to be an expert in the language to get value out of it.
While TLA+ is most famous for checking the interleaving and ordering of events in distributed systems, at its core it is just set theory plus temporal logic. That makes it versatile. It can model schema properties, define strict relations between data facts, and map out structural dependencies. More importantly, it enables you to capture not just static safety invariants, but also the temporal properties the system must guarantee over time.
This flexibility makes TLA+ a compelling answer to the paper's consistency problem. Instead of treating the persistent semantic context as Markdown text, an AI agent could continuously translate natural-language business rules into explicit TLA+ relations. If an underlying data-access protocol is modified or a core business metric is redefined, the system can use the TLA+ model to show exactly which downstream semantic dependencies break. Beyond keeping memory consistent, this formal foundation also helps the agent answer queries. Before executing a complex analytical query, the agent can check its proposed logic against the TLA+ invariants to rule out superfluous join paths. For example, it would immediately catch that querying for records where a "delivery timestamp" precedes an "order timestamp" violates a temporal invariant, and fix its filtering logic before ever touching the database.
I hate that the Bitter Lesson is so real, and so bitter. However, as I noted in my recent post on LLMs, these models excel at "high-throughput mediocrity". The Bitter Lesson applies best where statistical approximation is acceptable and the domain is flexible. When you need absolute correctness and performance, I still think human-engineered precision can hold the line. I still believe in the importance of good algorithms and abstractions. So, even if it proves futile, I keep looking for ways to push back against the Bitter Lesson, partly as an act of defiance, and partly as my sworn duty as a loyal member of the systems craft club. Grrr.
Which brings me back to the title: What happens when the model eats the stack?
I guess it takes a massive core dump!
Ba dum tss... I'll see myself out.
PS: My colleague Jesse's review of the paper is also worth reading.
Enabling data checksums is strongly advised to identify corruption originating from 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 lacks a cryptographic verification of the page contents. As a result, many types of silent corruption could go unnoticed and produce inaccurate query outcomes.
In PostgreSQL 18, by default, data pages across database clusters include a checksum that is verified each time a page is read from disk and recalculated when written. Since checksum is 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 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.
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.
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.
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.
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.max_worker_processes has enough headroom.on.inprogress-on requires restarting from scratch.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.