DEV Community: Franck Pachot

Anti-Join in MongoDB

Franck Pachot — Sat, 25 Apr 2026 16:00:17 +0000

SQL databases implement anti-joins—finding rows with no match—through NOT EXISTS. Although anti-join is not a distinct relational algebra operator, it can be derived from set difference and semi-join. A naive implementation that scans both sets to find the complement would not scale, so databases use short-circuit evaluation: the inner scan stops as soon as a match is found or ruled out, because only existence matters. That is why a subquery without any JOIN keyword can still appear as an Anti Join in the execution plan.

Here is an example in PostgreSQL, where we find users who haven't made any paid transactions:


postgres=# EXPLAIN ANALYZE
SELECT u.*
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM transactions t
    WHERE t.user_id = u.id
      AND t.type = 'paid'
);
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Nested Loop Anti Join (actual time=17.087..17.087 rows=0.00 loops=1)
   Buffers: shared hit=30095
   ->  Seq Scan on users u (actual time=0.007..0.628 rows=10000.00 loops=1)
         Buffers: shared hit=94
   ->  Index Only Scan using idx_transactions_user_type on transactions t (actual time=0.001..0.001 rows=1.00 loops=10000)
         Index Cond: ((user_id = u.id) AND (type = 'paid'::text))
         Heap Fetches: 0
         Index Searches: 10000
         Buffers: shared hit=30001
 Planning:
   Buffers: shared hit=14
 Planning Time: 0.200 ms
 Execution Time: 17.109 ms
(13 rows)

How does this translate to MongoDB, where there is no NOT EXISTS syntax? It can be very similar with on index lookup per user (Index Searches: 10000) returning at most one entry per user (rows=1.00 loops=10000).

In MongoDB, there are two equivalents to SQL joins:

The left outer join materialized at write time, where you embed the inner set as an array nested into the outer document. This is best when the cardinality is known and bounded, the entities are queried together as a single aggregate, and they share the same consistency boundaries within an atomic transaction. When you query the document, it is already joined, and you can $unwind the array to get the equivalent of a relational left outer join.
The $lookup operator, which finds matching documents from an aggregation pipeline and adds them as an array that can be used as if it were embedded, or can be $unwind.

With reference (join on read)

With $lookup, an anti-join is possible and efficient by defining the lookup pipeline with a $limit and a $project to check for existence only, without fetching all matching documents and all fields.

Here is an example with a collection of ten thousand users and a collection of transactions:

db.users.drop();
db.transactions.drop();

// Insert 10000 users
const users = [];
for (let i = 0; i < 10000; i++) {
    users.push({
        _id: ObjectId(),
        name: `user_${i}`,
        email: `user_${i}@example.com`,
        created_at: new Date()
    });
}
db.users.insertMany(users);

// Insert between 0 and 100 transactions per user
// ~80% are "paid", ~20% are "free"
const transactions = [];
users.forEach(user => {
    const numTransactions = Math.floor(Math.random() * 101); // 0 to 100
    for (let j = 0; j < numTransactions; j++) {
        transactions.push({
            user_id: user._id,
            type: Math.random() < 0.8 ? "paid" : "free",
            amount: Math.round(Math.random() * 10000) / 100,
            date: new Date(
                Date.now() - Math.floor(Math.random() * 365 * 24 * 60 * 60 * 1000)
            )
        });
    }
});

// Insert in batches (insertMany has a limit)
const BATCH_SIZE = 10000;
for (let i = 0; i < transactions.length; i += BATCH_SIZE) {
    db.transactions.insertMany(transactions.slice(i, i + BATCH_SIZE));
}

print(`Users created: ${db.users.countDocuments()}`);
print(`Users with transactions: ${db.transactions.distinct("user_id").length}`);
print(`Users with paid transactions: ${db.transactions.distinct("user_id", { type: "paid" }).length}`);
print(`Transactions created: ${db.transactions.countDocuments()}`);
print(`Paid transactions: ${db.transactions.countDocuments({ type: "paid" })}`);
print(`Free transactions: ${db.transactions.countDocuments({ type: "free" })}`);

This generated random data for 10,000 users, of whom 105 have no transactions at all, and 24 have only unpaid transactions.

test> print(`Users created: ${db.users.countDocuments()}`);
Users created: 10000

test> print(`Users with transactions: ${db.transactions.distinct("user_id").length}`);
Users with transactions: 9895

test> print(`Users with paid transactions: ${db.transactions.distinct("user_id", { type: "paid" }).length}`);
Users with paid transactions: 9871

test> print(`Transactions created: ${db.transactions.countDocuments()}`);
Transactions created: 498812

test> print(`Paid transactions: ${db.transactions.countDocuments({ type: "paid" })}`);
Paid transactions: 399612

test> print(`Free transactions: ${db.transactions.countDocuments({ type: "free" })}`);
Free transactions: 99200

In this sample dataset, there are 129 users with no paid transactions (either no transactions at all or only unpaid transactions).

As it will have to find transactions per user and type, I create the following index:


db.transactions.createIndex({ user_id: 1, type: 1 });

Here is the aggregation pipeline that returns the users with no paid transactions:


db.users.aggregate([
    {
        $lookup: {
            from: "transactions",
            localField: "_id",
            foreignField: "user_id",
            pipeline: [
                { $match: { type: "paid" } },
                { $limit: 1 },                  // ✅ Stop as soon as we find ONE
                { $project: { _id: 0 , user_id: 1 } }        // ✅ Return minimal data
            ],
            as: "paid_transactions"
        }
    },
    {
        $match: {
            paid_transactions: { $eq: [] }      // Keep only users with NO match
        }
    },
    {
        $project: {
            paid_transactions: 0                // Clean up the temporary field
        }
    }
]).explain("executionStats").stages

The $lookup stage has examined only index key per user with a paid transaction, so 9,871 ones, thanks to the { '$limit': 1 }, and no document thanks to the projection covered by the index:

  {
    '$lookup': {
      from: 'transactions',
      as: 'paid_transactions',
      localField: '_id',
      foreignField: 'user_id',
      let: {},
      pipeline: [
        { '$match': { type: 'paid' } },
        { '$limit': 1 },
        { '$project': { _id: 0, user_id: 1 } }
      ]
    },
    totalDocsExamined: Long('0'),
    totalKeysExamined: Long('9871'),
    collectionScans: Long('0'),
    indexesUsed: [ 'user_id_1_type_1' ],
    nReturned: Long('10000'),
    executionTimeMillisEstimate: Long('1011')
  },

It has returned one document for each of the 10,000 users, with an empty array when there was no match, so the $match stage filtered out 129 documents:

  {
    '$match': { paid_transactions: { '$eq': [] } },
    nReturned: Long('129'),
    executionTimeMillisEstimate: Long('1011')
  },

This (1011ms) is notably slower than PostgreSQL anti-join (17ms). The explanation is that $lookup executes a separate pipeline per document in a subpipeline, which has high per-document overhead compared to a native join operator.

SBE (Slot-Based Execution) pushdown for $lookup is not supported when the lookup contains a pipeline, which forces execution to fall back to the classic engine with independent subpipelines. You can use SBE when not pushing down the { $match: { type: "paid" } } and { $limit: 1 } but then you need to filter and group afterwards on all matching documents:

db.users.aggregate([
    {  $lookup: {  from: "transactions",  localField: "_id",  foreignField: "user_id",  as: "transactions"  }  },
    {  $match:  {  "transactions.type": "paid"  }  },
    {  $group:  {  _id: "$_id",  user: { $first: "$$ROOT" },  hasPaid: { $first: "$transactions" }  }  },
    {  $match:  {  hasPaid: { $eq: [] }  }  },
    {  $replaceRoot: { newRoot: "$user" }  }  ]).explain("executionStats")

This will be efficient only if there's few transactions by users.

In MongoDB when there's a join from thousands of documents to a bounded amount of associated sub-document, you should consider embedding as an array.

With embedding (join on write)

If you preferred to store the transactions embedded within each user, the query can be simpler and faster.

I update users to embed the transactions:

db.users.aggregate([  
    {  
        $lookup: {  
            from: "transactions",  
            localField: "_id",  
            foreignField: "user_id",  
            as: "transactions"  
        }  
    },  
    {  
        $merge: {  
            into: "users",  
            whenMatched: "merge"  
        }  
    }  
]);

The query to find users with no paid transactions is simply:

db.users.find(
{ "transactions.type": { $ne: "paid" } } ,
{ "transactions": 0 }
).explain("executionStats");

This scans the users to return the 129 ones without paid transactions:

    inputStage: {
      stage: 'COLLSCAN',
      filter: { 'transactions.type': { '$not': { '$eq': 'paid' } } },
      nReturned: 129,
      executionTimeMillisEstimate: 20,
      works: 10001,
      advanced: 129,
      needTime: 9871,
      needYield: 0,
      saveState: 1,
      restoreState: 1,
      isEOF: 1,
      direction: 'forward',
      docsExamined: 10000
    }
  }

This is not efficient because it has to read large documents, with all transactions, and discard them later.

A multikey index on "transactions.type" does not help much either. Because it indexes every element in the array, a user with both "paid" and "free" transactions has entries under both values. The $ne: "paid" scan retrieves all users who have any non-paid entry — which is nearly all of them — and then the FETCH stage must read each full document to verify that none of their transactions are paid:

      inputStage: {
        stage: 'IXSCAN',
        nReturned: 9587,
        executionTimeMillisEstimate: 10,
        works: 9589,
        advanced: 9587,
        needTime: 1,
        needYield: 0,
        saveState: 2,
        restoreState: 2,
        isEOF: 1,
        keyPattern: { 'transactions.type': 1 },
        indexName: 'transactions.type_1',
        isMultiKey: true,
        multiKeyPaths: { 'transactions.type': [ 'transactions' ] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          'transactions.type': [ '[MinKey, "paid")', '("paid", MaxKey]' ]
        },
        keysExamined: 9588,
        seeks: 2,
        dupsTested: 9587,
        dupsDropped: 0
      }

but it will still fetch documents with some paid transactions, in addition to unpaid, and that must be discarded later:

      stage: 'FETCH',
      filter: { 'transactions.type': { '$not': { '$eq': 'paid' } } },
      nReturned: 129,
      executionTimeMillisEstimate: 36,

In general, indexes do not help with anti-joins because they index existing values, not the absence of values. In MongoDB with embedded documents, you can work around this by adding a computed flag — such as "has_paid": false — that is maintained when transactions are inserted or removed, and indexing that flag. Since the flag and the transactions live in the same document, this adds negligible write overhead while making the anti-join a simple indexed equality match.

Mutable BSON and Oracle OSON

Franck Pachot — Sat, 18 Apr 2026 22:59:16 +0000

AskTom Live is a great source of information from Oracle developer advocates and product managers, but I recently came across a clickbait marketing title ("Not All Binary Protocols Are Created Equal: The Science Behind OSON's 529x Performance Advantage") which compares apples to oranges, and it's an opportunity to explain what BSON is, the binary JSON format used by MongoDB.

TL;DR: If you want to compare with OSON, the Oracle Database datatype for JSON, you should compare the Mutable BSON Document which is the structure that MongoDB uses to access documents, reading and updating individual fields. Raw BSON is closer to protobuf: a compact serialization format for disk or network transfer, with access metadata removed and no blocks or headers.

I've left the following comment to the YouTube video but it seems that it is not publicly visible, so here it is.

Let me explain how Oracle Database and MongoDB handle disk-based data access, and you will understand the different design purposes of OSON and BSON, and why you are not testing the right thing to compare them.

Oracle Database, like many traditional databases, uses the same format on disk (blocks) and in memory (buffers), and must store all transient metadata that helps access it in memory on persistent storage. This applies to table blocks (which contain a table directory, a row directory, and even lock flags, ITLs, that need to be cleaned up later), and the same idea was used for OSON (header, dictionary, sorted field IDs, offset arrays). Think of it as a mini database with its catalog, like the Oracle database has its dictionary and segment headers, which map physical extents and blocks. Then accessing the on-disk OSON structure directly makes sense — it's designed to be used through buffers that match the disk blocks.

But MongoDB with WiredTiger uses a smarter cache where the in-memory structures are optimized for RAM: adding pointers instead of disk offsets, building an Elements Vector for O(1) field access, and adding skiplists to navigate fields, all when data is loaded into the database cache. So there are two formats: the mutable BSON that the database actually works on in memory for query processing and updates, and the on-disk raw BSON that, on purpose, strips any unnecessary metadata and compresses it, to maximize the OS filesystem cache usage, and fits to the major advantage of MongoDB for documents: read/write a document in a single I/O.

The raw BSON is a serialization format for disk and network, not to be accessed partially, because MongoDB has a powerful mutable BSON format in memory with O(1) access through its Elements Vector indexing. The O(n) sequential scan, the "no partial updates" limitation, and the field position penalties you describe — those are properties of the serialization format, not how MongoDB actually processes queries. And by definition, the serialization format is read sequentially, even though BSON can jump between fields. Don't do that except when you need a full document. Use the MongoDB server and drivers to access BSON, and learn how to use it correctly.

With this understanding, you can see that the "529x performance" clickbait title comes from a mistake: you used raw BSON to access individual fields, bypassing everything MongoDB does when serving a query. It would be like using BBED to query Oracle Datafiles without going through the instance — no buffer cache, no row directory navigation, no dictionary lookups — and then concluding that Oracle's storage format is slow.

Notably, the original OSON VLDB paper (Liu et al., 2020) by Zhen Hua Liu doesn't make the claims this video does. That paper honestly compares OSON against Oracle's own JSON text storage, not against MongoDB's query processing. It compares encoding sizes with BSON, which is legitimate for a serialization format comparison (though it overlooks that BSON in MongoDB is compressed on disk and over the network). The paper authors understood they were comparing serialization formats and storage approaches within Oracle, not benchmarking MongoDB's actual runtime performance.

I believe OSON is the optimal format for Oracle because it integrates with the existing instance, cache, blocks, and securefiles, which were created a long time ago. Conversely, BSON is ideal for MongoDB, as it capitalizes on the document database's design and the WiredTiger storage engine.

The origins of MongoDB

Franck Pachot — Tue, 07 Apr 2026 13:56:22 +0000

The Internet Archive holds some real gems. Let’s trace the origins of MongoDB with links to its archived 2008 content. The earliest snapshot is of 10gen.com, the company that created MongoDB as the internal data layer subsystem of a larger platform before it became a standalone product.

MongoDB was first described by its founders as an object-oriented DBMS, offering an interface similar to an ORM but as the native database interface rather than a translation layer, making it faster, more powerful, and easier to set up. The terminology later shifted to document-oriented database, which better reflects a key architectural point: object databases store objects together with their behavior (methods, class definitions, executable code), while document databases store only the data — the structure and values describing an entity. In MongoDB, this data is represented in JSON (because it is easier to read than XML), or more precisely BSON (Binary JSON), which extends JSON with types such as dates, binary data, and more precise numeric values.

Like object-oriented databases, MongoDB stores an entity's data — or, in DDD terms, an aggregate of related entities and values — as a single, hierarchical structure with nested objects, arrays, and relationships, instead of decomposing it into rows across multiple normalized tables, as relational databases do.
Like relational databases, MongoDB keeps data and code separate, a core principle of database theory. The database stores only data. Behavior and logic live in the application, where they can be version-controlled, tested, and deployed independently.

MongoDB's goal was to combine the speed and scalability of key-value stores with the rich functionality of relational databases, while simplifying coding significantly using BSON (binary JSON) to map modern object-oriented languages without a complicated ORM layer.

Object-oriented, object-relational, and XML databases never gained broad adoption, but the JSON document model did: MongoDB became the most popular NoSQL database, and all major SQL databases now offer JSON support and a document API.

An early 10gen white paper, A Brief Introduction to MongoDB, framed MongoDB's creation within a broader database evolution — from three decades of relational dominance, through the rise of OLAP for analytics, to the need for a similar shift in operational workloads. The paper identified three converging forces: big data with high operation rates, agile development demanding continuous deployment and short release cycles, and cloud computing on commodity hardware. Today, releasing every week or even every day is common, whereas in the relational world, a schema migration every month is often treated as an anomaly in the development process.

The same paper explains that horizontal scalability is central to the architecture, using sharding and replica sets to be cloud-native — unlike relational databases, where replication was added later by reusing crash and media recovery techniques to send write-ahead logs over the network.

Before MongoDB, founders Dwight Merriman and Eliot Horowitz had already built large-scale systems. Dwight co-founded DoubleClick, an internet advertising platform that handled hundreds of thousands of ad requests per second and was later acquired by Google, where it still underpins much of online advertising. Eliot, at ShopWiki, shared Dwight's frustration with the state of databases. Whether they used Oracle, MySQL, or Berkeley DB, nothing fit their needs, forcing them to rely on workarounds like ORMs, caches that could serve stale data, and application-level sharding.

Dwight Merriman explained this frustration in Databases and the Cloud.

In 2007, architects widely accepted duct-tape solutions and workarounds for SQL databases:

Caching layers in front of databases, with no immediate consistency. Degraded consistency guarantees were treated as normal because SQL databases were saturated by the calls from the new object-oriented applications.
Hand-coded, fragile, application-specific sharding. Each team reinvented distributed data management from scratch, inheriting bugs, edge cases, and heavy maintenance.
Stored procedures to reduce the multi-statement transactions to a single call to the database. Writes went through stored procedures while reads hit the database directly, pushing critical business logic into the database, outside version control, and forcing developers to work in three languages: the application language, SQL, and the stored procedure language.
Query construction via string concatenation, effectively embedding custom code generators in applications to build SQL dynamically. Although the SQL standard defined embedded SQL, precompilers were available only for non–object-oriented languages.
Vertical scaling: when you needed more capacity, you bought a bigger server. Teams had to plan scale and costs upfront, ran into a hard ceiling where only parallelism could help, and paid a premium for large enterprise machines compared with commodity hardware. Meanwhile, startups were moving to EC2 and cloud computing. A database that scaled only vertically was fundamentally at odds with the cloud-native future they saw coming.

Beyond infrastructure workarounds, there was a deeper disconnect with how software was being built. By 2008, agile development dominated. Teams iterated quickly — at Facebook, releases went out daily, and broken changes were simply rolled back. Relational databases, however, remained in a waterfall world. Schema migrations meant downtime, and rollbacks were risky. The database had become the primary obstacle to the agile experience teams wanted.

Scaling horizontally was the other key challenge. Many NoSQL databases solved it by sharply reducing functionality—sometimes to little more than primary-key get/put—making distribution trivial. MongoDB instead asked: what is the minimum we must drop to scale out? It kept much more of the relational model: ad hoc queries, secondary indexes, aggregation, and sorting. It dropped only what it couldn’t yet support at large distributed scale: joins across thousands of servers and full multi-document transactions. Transactions weren’t removed but were limited to a single document, which could be rich enough to represent the business transaction that might otherwise be hundreds of rows across several relational tables. Later, distributed joins and multi-document ACID transactions were added via the lookup aggregation stage and multi-document transactions.

Many people think MongoDB has no schema, but "schemaless" is misleading. MongoDB uses a dynamic, or implicit, schema. When you start a new MongoDB project, you still design a schema—you just don’t define it upfront in the database dictionary. And it has schema validation, relationships and consistency, all within the document boundaries owned by the application service.

It's interesting to look at the history and see what remains true or has changed. SQL databases have evolved and allow more agility, with some online DDL and JSON datatypes. As LLMs become fluent at generating and understanding code, working with multiple languages may matter less. The deeper problem is when business logic sits outside main version control and test pipelines, and is spread across different execution environments.

Cloud-native infrastructure is even more important today, as the application infrastructure must not only be cost-efficient on commodity hardware but also resilient to the new failure modes that arise in those environments. Agile development methods are arguably even more relevant with AI-generated applications. Rather than building one central database with all referential integrity enforced synchronously, teams increasingly need small, independent bounded contexts that define their own consistency and transaction boundaries — decoupled from other microservices to reduce the blast radius of failures and changes.

Finally, the video from the What Is MongoDB page from 2011 summarizes all that:

Over the past two decades, MongoDB—like many other databases—has evolved dramatically. It began with a strong focus on developer experience and the idea that data consistency should be handled in the application layer, where data lifecycle starts, before the database. This marked a shift as developers took architectural decisions away from DBAs. Today, we face a new inflection point, with AI agents increasingly making these choices. To navigate this era, where application builders are agents, it’s useful to revisit past trade-offs, learning from both mistakes and innovations.

MongoDB Query Planner

Franck Pachot — Tue, 07 Apr 2026 06:23:59 +0000

SQL databases use query planners (often cost-based optimizers) so developers don’t worry about physical data access. Many NoSQL systems like DynamoDB and Redis drop this layer, making developers act as the query planner by querying indexes directly. MongoDB keeps a query planner—an empirical, trial-based multi-planner—that chooses the best index and reuses the winning plan until it’s no longer optimal.

Single-Cluster Duality View 🃏

Franck Pachot — Wed, 01 Apr 2026 02:00:00 +0000

In DynamoDB, a single-table design stores one-to-many relationships in a single physical block while still following relational-like normal form decomposition. In MongoDB, the Single Collection Pattern unnests relationships from a single document, but goes against the general recommendation as it sacrifices one of MongoDB’s key advantages—keeping a document in a single block. In Oracle Database and MySQL, JSON-relational duality views normalize JSON documents into relational tables that span multiple blocks, also without the data-locality benefits of MongoDB. Can we turn these duality views into a document-database equivalent that keeps together the data accessed together?

Here is SCDV™, the Single-Cluster Duality View 🥁.

DynamoDB

NoSQL started with a simple key‑value API. For example, DynamoDB can get an item using the full key, query multiple items using a partial key, or scan all items without a key. The value, in this key‑value datastore, is atomic, but sometimes you need partial reads or writes. Then you have two solutions: read or write the full item, which is inefficient if you don't need it, or change your schema design to split it into multiple items.

The latter was known as the single table design because it not only splits items into multiple entities but also stores them in the same table, sharing a key prefix, to retrieve all items with a single query. The idea is that you can access individual objects while also benefiting from data locality when querying the full aggregate.

The benefit of this design relies heavily on DynamoDB specifics: the storage internals, where items are partitioned and clustered by their key, and the billing model, where you pay per table request unit.

Here is an example, using the traditional one-to-many schema with departments employees:

One DynamoDB table
Department and employees stored as separate items
Same partition key (DEPT#<deptno>) for both:

aws dynamodb create-table \
  --table-name scott \
  --attribute-definitions \
    AttributeName=PK,AttributeType=S \
    AttributeName=SK,AttributeType=S \
  --key-schema \
    AttributeName=PK,KeyType=HASH \
    AttributeName=SK,KeyType=RANGE \
  --billing-mode PAY_PER_REQUEST

The logical model, where the relationship is materialized by sharing the department number as the same partition key, is also the physical model in which they are stored together, since items are partitioned on this key:

PK	SK	Meaning
`DEPT#10`	`DEPT`	Department row
`DEPT#10`	`EMP#7782`	Employee
`DEPT#10`	`EMP#7839`	Employee
`DEPT#20`	`DEPT`	Department row
`DEPT#20`	`EMP#7369`	Employee

I insert some data

aws dynamodb put-item --table-name scott --item '{
  "PK": {"S": "DEPT#10"},
  "SK": {"S": "DEPT"},
  "deptno": {"N": "10"},
  "dname": {"S": "ACCOUNTING"},
  "loc": {"S": "NEW YORK"}
}'

aws dynamodb put-item --table-name scott --item '{
  "PK": {"S": "DEPT#10"},
  "SK": {"S": "EMP#7782"},
  "empno": {"N": "7782"},
  "ename": {"S": "CLARK"},
  "job": {"S": "MANAGER"},
  "sal": {"N": "2450"}
}'

Here is a query that retrieves the whole aggregate by its partition key:

aws dynamodb query \
  --table-name scott \
  --key-condition-expression "PK = :d" \
  --expression-attribute-values '{":d":{"S":"DEPT#10"}}' \
  --consistent-read \
  --return-consumed-capacity TOTAL 


{
    "Items": [
        {
            "deptno": { "N": "10" },
            "PK": { "S": "DEPT#10" },
            "loc": { "S": "NEW YORK" },
            "dname": { "S": "ACCOUNTING" },
            "SK": { "S": "DEPT" }
        },
        {
            "ename": { "S": "CLARK" },
            "PK": { "S": "DEPT#10" },
            "job": { "S": "MANAGER" },
            "empno": { "N": "7782" },
            "sal": { "N": "2450" },
            "SK": { "S": "EMP#7782" }
        }
    ],
    "Count": 2,
    "ScannedCount": 2,
    "ConsumedCapacity": {
        "TableName": "scott",
        "CapacityUnits": 1.0
    }
}

All items are read from a single partition with 1 consistent read capacity unit (RCU), thanks to physical colocation by key design.

MongoDB

MongoDB doesn't need to split documents to get partial reads and writes. The data modeling objective is to keep aggregates in a single document and use the advanced API to access individual items. For example, you can use covering indexes or search indexes with returnStoredSource to avoid reading the whole document. And you can use $push or $set with arrayFilters to update individual array items.

Still, some users preferred to split the documents and, by analogy to DynamoDB, called it the Single Collection Pattern. In truth, it is rarely useful. MongoDB is not DynamoDB. Storing documents in one or multiple collections doesn't impact billing, and using the same key prefix doesn't co‑locate them — except in two cases: clustered collections (special‑purpose, not generally recommended), and sharing a sharding key to co‑locate data on the same shard (but still different blocks in the filesystem).

At the storage block level, documents may be stored together only if they were inserted together.

Although this is not a recommendation when you use the full power of the MongoDB API, here is an example using a single collection and embedding only references, following the documentation: one document per department and one document per employee:

db.empdept.insertMany([
{
  _id: "DEPT#10",
  doc_type: "dept",
  deptno: 10,
  dname: "ACCOUNTING",
  loc: "NEW YORK",
  links: [
    { target: "DEPT#10", doc_type: "dept" },
    { target: "EMP#7782", doc_type: "emp" },
    { target: "EMP#7839", doc_type: "emp" }
  ]
},{
  _id: "EMP#7782",
  doc_type: "emp",
  empno: 7782,
  ename: "CLARK",
  job: "MANAGER",
  sal: 2450,
  deptno: 10,
  links: [
    { target: "EMP#7782", doc_type: "emp" },
    { target: "DEPT#10", doc_type: "dept" }
  ]
},{
  _id: "EMP#7839",
  doc_type: "emp",
  empno: 7839,
  ename: "KING",
  job: "PRESIDENT",
  sal: 5000,
  deptno: 10,
  links: [
    { target: "EMP#7839", doc_type: "emp" },
    { target: "DEPT#10", doc_type: "dept" }
  ]
}];

With this schema, the following gets the full aggregate as multiple documents without an aggregation pipeline:


db.empdept.find({ "links.target": "DEPT#10" })

This can identify the document using a single index range with a multi-key index on "links.target", but it will have to fetch multiple small documents, introduces strong coupling between those documents without the possibility of validating the reference, and requires an explicit transaction and retry logic to update them.

MongoDB is designed to store aggregates as a single document, and the right schema is simply:

db.empdept.insertOne({
  _id: 10,
  dname: "ACCOUNTING",
  loc: "NEW YORK",
  employees: [
    { empno: 7782, ename: "CLARK", job: "MANAGER", sal: 2450 },
    { empno: 7839, ename: "KING",  job: "PRESIDENT", sal: 5000 }
  ]
})

With this schema, you can still query the full aggregate with a simple find():


db.empdept.find({ _id: 10 })

and you can still update a single employee in‑place:

db.empdept.updateOne(
  { _id: 10, "employees.empno": 7782 },
  { $set: { "employees.$.sal": 2600 } }
)

This updates the first matching array item using the positional $ operator. You can be more precise with arrayFilters

db.empdept.updateOne(  
  { _id: 10 },  
  { $set: { "employees.$[e].sal": 2600 } },  
  { arrayFilters: [{ "e.empno": 7782 }] }  
)

I've written about this and measured the efficiency in a previous post:

Franck Pachot

Mar 3

Updating "denormalized" aggregates with "duplicates": MongoDB vs. PostgreSQL

#mongodb #postgres #oracle #denormalization

Comments

9 min read

Emulations

A single document per aggregate, with embedded one-to-many relationships, may rewrite more than expected. OSON is optimized for partial updates even if not visible in the execution plan:

ora> db.empdept.updateOne(
  { _id: 10, "employees.empno": 7782 },
  { $set: { "employees.$.sal": 2600 } }
)

{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}

ora> db.aggregate( [ { $sql : " select * from dbms_xplan.display_cursor( format=>'BASIC') " } ] ).forEach(row => print(row.PLAN_TABLE_OUTPUT));

EXPLAINED SQL STATEMENT:
------------------------
update "ORA"."empdept" set "DATA" = :1 where ("RESID" = :2 )  returning "RESID", "ETAG" into :3 , :4

Plan hash value: 893016358

-----------------------------------------------------
| Id  | Operation                    | Name         |
-----------------------------------------------------
|   0 | UPDATE STATEMENT             |              |
|   1 |  UPDATE                      | empdept      |
|   2 |   TABLE ACCESS BY INDEX ROWID| empdept      |
|   3 |    INDEX UNIQUE SCAN         | SYS_C0031043 |
-----------------------------------------------------

You have also the possibility to store the collection with JSON-relational duality views (JDV), but this partial update is not supported:

arrayFilters option is not supported on duality view collections

However, even if the JDV doesn't allow partial updates, the aggregate is stored as individual in SQL tables, and users can switch to SQL statements for partial updates.

Duality Views

In relational databases, applications typically work with a logical view of the data model, while the storage layer may transparently cluster or scatter data into fixed-size blocks. Oracle Database (and MySQL) added JSON-Relational Duality Views (JDV) to present a single logical view for JSON documents, while splitting them to multiple SQL tables. As each SQL tables have their own physical segment(s), aggregates are stored across multiple physical blocks.

If you are a fan of the single‑table or single‑collection idea, you may create a single view. However, it has no advantage in data locality, as you explicitly normalized to multiple tables:

CREATE TABLE dept (
  deptno NUMBER CONSTRAINT dept_pk PRIMARY KEY,
  dname  VARCHAR2(14),
  loc    VARCHAR2(13)
);

CREATE TABLE emp (
  empno  NUMBER CONSTRAINT emp_pk PRIMARY KEY,
  ename  VARCHAR2(10),
  job    VARCHAR2(9),
  sal    NUMBER(7,2),
  deptno NUMBER NOT NULL,
  CONSTRAINT emp_dept_fk FOREIGN KEY (deptno) REFERENCES dept(deptno)
);

CREATE INDEX emp_dept_fk ON emp (deptno);

INSERT INTO dept (deptno, dname, loc)  VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO emp (empno, ename, job, sal, deptno)  VALUES (7782, 'CLARK', 'MANAGER', 2450, 10);
INSERT INTO emp (empno, ename, job, sal, deptno)  VALUES (7839, 'KING', 'PRESIDENT', 5000, 10);
-- add many departments to check the number of blocks scanned
INSERT INTO dept(deptno) SELECT rownum+1000
 FROM xmltable('0 to 999')
;

-- one view over multiple tables with JDV

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW empdept_dv AS
SELECT JSON {
  '_id'  : d.deptno,
  'dname': d.dname,
  'loc'  : d.loc,
  'employees' : [
    SELECT JSON {
      'empno': e.empno,
      'ename': e.ename,
      'job'  : e.job,
      'sal'  : e.sal
    }
    FROM emp e WITH INSERT UPDATE DELETE
    WHERE e.deptno = d.deptno
  ]
}
FROM dept d WITH INSERT UPDATE DELETE
;

The view looks like a document collection with an embedded one-to-many relationship, similar to MongoDB’s recommended model, but it’s not truly embedded—the view actually splits it into two tables. We have lost the main advantage of MongoDB: data that's accessed together should be stored together.

I can query it as if it were a single table:

SELECT data 
FROM empdept_dv
WHERE
JSON_EXISTS("DATA",'$?(@._id.numberOnly() == $B0)' passing 10 as "B0"
type(strict))
;

but it actually reads two tables:

SQL> select * from dbms_xplan.display_cursor(format=>'ALLSTATS LAST -COST');

PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________________
SQL_ID  gfr8jkdwatdnz, child number 0
-------------------------------------
SELECT data  FROM empdept_dv WHERE JSON_EXISTS("DATA",'$?(@._id.numberOnly() == $B0)' passing 10 as "B0" type(strict))

Plan hash value: 2755083285

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | E-Rows | A-Rows | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |        |      1 |       3 |       |       |          |
|   1 |  SORT GROUP BY                       |             |      1 |      1 |      1 |       2 |  2048 |  2048 | 2048  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP         |      1 |      1 |      2 |       2 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | EMP_DEPT_FK |      1 |      1 |      2 |       1 |       |       |          |
|   4 |  TABLE ACCESS BY INDEX ROWID         | DEPT        |      1 |      1 |      1 |       3 |       |       |          |
|*  5 |   INDEX UNIQUE SCAN                  | DEPT_PK     |      1 |      1 |      1 |       2 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("E"."DEPTNO"=:B1)
   5 - access("D"."DEPTNO"=10)

The access is efficient, using the index on the foreign key, but it doesn't colocate a one-to-many relationship as we expect when storing an aggregate into a single document, or when using the single-table design.

You can update through the view, but the following will update a lot more than what you expect, as it rewrites the full document:

UPDATE empdept_dv
SET data = JSON_TRANSFORM(
  data,
  SET '$.employees[0].sal' = 2600
)
WHERE
JSON_EXISTS("DATA",'$?(@._id.numberOnly() == $B0)' passing 10 as "B0"
type(strict))
;

Using duality views instead of OSON storage allows you to fall back to SQL to update a single item, similar to a single-table design. However, unlike DynamoDB, you cannot group different item types in the same table. These are SQL tables with a fixed schema and no polymorphism, so you cannot store both department and employee attributes in a single table. JSON-Relational Duality Views don’t replace document databases: they act like an object-relational mapper (ORM) with all logic deployed in the SQL database.

Cluster

To get closer to the performance of a document database, I need a single-table duality view capable of storing documents that are split across multiple SQL tables, yet behave as if they were in a single physical table. In Oracle Database, this corresponds to a CLUSTER, where only the key is declared, like DynamoDB tables, and an estimated value size are predefined to fill the fixed-size blocks:

CREATE CLUSTER dept_cluster (
  deptno NUMBER ------------------------------------- clustering key
) 
SIZE 1024 --  expected bytes per cluster key
;

CREATE INDEX dept_cluster_idx ON CLUSTER dept_cluster 
;

The logical SQL tables are stored in the cluster under a common clustering key, instead of allocating their own physical segment:

CREATE TABLE dept ( 
  deptno NUMBER CONSTRAINT dept_pk PRIMARY KEY,   --- clustering key
  dname  VARCHAR2(14), 
  loc    VARCHAR2(13)  
 )
 CLUSTER dept_cluster (deptno) ---------------------- clustering key
; 

CREATE TABLE emp (
  empno  NUMBER CONSTRAINT emp_pk PRIMARY KEY, 
  ename  VARCHAR2(10),  
  job    VARCHAR2(9), 
  sal    NUMBER(7,2), 
  deptno NUMBER NOT NULL,      ---------------------- clustering key
  CONSTRAINT emp_dept_fk 
             FOREIGN KEY (deptno) 
             REFERENCES dept(deptno)
 )
 CLUSTER dept_cluster (deptno) ---------------------- clustering key
;

INSERT INTO dept (deptno, dname, loc)  VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO emp (empno, ename, job, sal, deptno)  VALUES (7782, 'CLARK', 'MANAGER', 2450, 10);
INSERT INTO emp (empno, ename, job, sal, deptno)  VALUES (7839, 'KING', 'PRESIDENT', 5000, 10);
-- add many departments to check the number of blocks scanned
INSERT INTO dept(deptno) SELECT rownum+1000
 FROM xmltable('0 to 999')
;

I created the same duality view as before, as the logical tables are the same, and query it:

SELECT data 
FROM empdept_dv
WHERE
JSON_EXISTS("DATA",'$?(@._id.numberOnly() == $B0)' passing 10 as "B0"
type(strict))
;

Physically, it accesses the logical tables in the pre-joined cluster, via its index:

SQL> select * from dbms_xplan.display_cursor(format=>'ALLSTATS LAST');

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  26py2vsmch5kx, child number 0
-------------------------------------
SELECT data FROM empdept_dv WHERE JSON_EXISTS("DATA",'$?(@._id.numberOnl
y() == $B0)' passing 10 as "B0" type(strict))

Plan hash value: 176041294

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Starts | A-Rows | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |      1 |      1 |       3 |      2 |       |         |          |
|   1 |  SORT GROUP BY              |                  |      1 |      1 |       3 |      2 |  2048 |  2048 | 2048  (0)|
|   2 |   TABLE ACCESS CLUSTER      | EMP              |      1 |      2 |       3 |      2 |       |         |          |
|*  3 |    INDEX UNIQUE SCAN        | DEPT_CLUSTER_IDX |      1 |      1 |       2 |      2 |       |         |          |
|   4 |  TABLE ACCESS BY INDEX ROWID| DEPT             |      1 |      1 |       3 |      2 |       |         |          |
|*  5 |   INDEX UNIQUE SCAN         | DEPT_PK          |      1 |      1 |       2 |      2 |       |         |          |
------------------------------------------------------------------------------------------------------------------------

The execution plan shows access to the cluster using its index on DEPTNO. However, the plan is unclear and appears to use both the DEPT index and the cluster index. I displayed the number of buffer gets, but the math doesn’t add up: a total of three buffers seems reasonable—two index blocks (root and leaf) and one cluster block—yet the detailed output shows three for each index, which should mean six in total.

I confirmed that all my data is in a single block—my goal—by checking the ROWID, which contains the block identifier:

select  
  rowid,  
  dbms_rowid.rowid_relative_fno(rowid)  as file_no,  
  dbms_rowid.rowid_block_number(rowid)  as block_no,
to_char(dbms_rowid.rowid_block_number(rowid),'xxxxxxxx') as block_hex,
dept.* from dept where deptno = 10
;

ROWID                 FILE_NO   BLOCK_NO BLOCK_HEX     DEPTNO DNAME          LOC
------------------ ---------- ---------- --------- ---------- -------------- -------------
AAASKBAAAAAAK5hAAA          0      44641      ae61         10 ACCOUNTING     NEW YORK

select  
  rowid,  
  dbms_rowid.rowid_relative_fno(rowid)  as file_no,  
  dbms_rowid.rowid_block_number(rowid)  as block_no,
to_char(dbms_rowid.rowid_block_number(rowid),'xxxxxxxx') as block_hex,
emp.* from emp where deptno = 10
;

ROWID                 FILE_NO   BLOCK_NO BLOCK_HEX      EMPNO ENAME      JOB              SAL     DEPTNO
------------------ ---------- ---------- --------- ---------- ---------- --------- ---------- ----------
AAASKBAAAAAAK5hAAA          0      44641      ae61       7782 CLARK      MANAGER         2450         10
AAASKBAAAAAAK5hAAB          0      44641      ae61       7839 KING       PRESIDENT       5000         10

They are all in block ae61. To try to understand better, I traced the consistent reads (with event 10200):

ktrgtc2(): started for block <0x0000 : 0x0000ae71> objd: 0x00012288, DirtyRead: 0:0 env [0x7ffff8925fd8]: (scn: 0x00000000004cdc0e  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000000000000000  hi-scn: 0x00000000004cdc0e  ma-scn: 0x00000000004cdbea  flg: 0x00000662)ktrgtc2(): completed for block <0x0000 : 0x0000ae71> objd: 0x00012288 ret:0x0 flg:0x7fff
ktrgtc2(): started for block <0x0000 : 0x0000ae72> objd: 0x00012288, DirtyRead: 0:0 env [0x7ffff8925fd8]: (scn: 0x00000000004cdc0e  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000000000000000  hi-scn: 0x00000000004cdc0e  ma-scn: 0x00000000004cdbea  flg: 0x00000662)ktrgtc2(): completed for block <0x0000 : 0x0000ae72> objd: 0x00012288 ret:0x0 flg:0x7fff
ktrgtc2(): started for block <0x0000 : 0x0000ae61> objd: 0x00012281, DirtyRead: 0:0 env [0x7ffff8925fd8]: (scn: 0x00000000004cdc0e  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000000000000000  hi-scn: 0x00000000004cdc0e  ma-scn: 0x00000000004cdbea  flg: 0x00000662)ktrgtc2(): completed for block <0x0000 : 0x0000ae61> objd: 0x00012281 ret:0x0 flg:0x2f04d
ktrgtc2(): started for block <0x0000 : 0x0000ae69> objd: 0x00012282, DirtyRead: 0:0 env [0x7ffff8925fd8]: (scn: 0x00000000004cdc0e  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000000000000000  hi-scn: 0x00000000004cdc0e  ma-scn: 0x00000000004cdbea  flg: 0x00000662)ktrgtc2(): completed for block <0x0000 : 0x0000ae69> objd: 0x00012282 ret:0x0 flg:0x90040
ktrgtc2(): started for block <0x0000 : 0x0000ae6a> objd: 0x00012282, DirtyRead: 0:0 env [0x7ffff8925fd8]: (scn: 0x00000000004cdc0e  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000000000000000  hi-scn: 0x00000000004cdc0e  ma-scn: 0x00000000004cdbea  flg: 0x00000662)ktrgtc2(): completed for block <0x0000 : 0x0000ae6a> objd: 0x00012282 ret:0x0 flg:0x90040

I checked from DBA_OBJECTS and DBA_EXTENTS:

blocks 0xae71 and 0xae72 are DEPT_PK (objd:0x12282)
block ae61 is DEPT_CLUSTER (objd:0x12281), the cluster's block for DEPT=10
blocks ae69 and ae6a are DEPT_CLUSTER_IDX (objd:0x12282)

Note that I've run the query with the same predicate that is generated from the MongoDB emulation, and you can see the full SQL query with:

variable c clob;
set autoprint on long 10000 pagesize 1000
exec DBMS_UTILITY.EXPAND_SQL_TEXT( q'[
SELECT data FROM empdept_dv WHERE JSON_VALUE(data, '$._id') = 10
]', :c)

I got the same execution plan with the simplified syntax:

SELECT data 
 FROM empdept_dv d 
 WHERE d.data."_id"=10
;

With the simplest join syntax, on SQL tables, I got a similar plan reading 6 blocks:

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  cys0qt7ttbkzd, child number 0
-------------------------------------
select * from dept , emp where emp.deptno=dept.deptno and dept.deptno=10 and emp.deptno=10

Plan hash value: 3732773526

----------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |      2 |       6 |
|   1 |  NESTED LOOPS                |         |      1 |      2 |       6 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |       3 |
|*  3 |    INDEX UNIQUE SCAN         | DEPT_PK |      1 |      1 |       2 |
|*  4 |   TABLE ACCESS CLUSTER       | EMP     |      1 |      2 |       3 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPT"."DEPTNO"=10)
   4 - filter(("EMP"."DEPTNO"=10 AND "EMP"."DEPTNO"="DEPT"."DEPTNO"))

This NESTED LOOP with TABLE ACCESS CLUSTER is the typical join on pre-joined rows in a cluster. One index entry gets all employee rows together.

This is likely the closest we can get to a true "single-table" design for JSON-relational duality views. An Oracle indexed cluster stores related rows in a single block, but the optimizer, as shown in execution plans, still does not share the cluster index across the two tables and fully exploit the pre-joined one-to-many relationship.

We’ve come full circle. Using features from 1980 (CLUSTER) and 2020 (JDV), we have achieved a single‑duality that preserves physical colocation like a document database — while still declaring multiple SQL tables underneath, with proud respect for normal forms.

Conclusion

This is mainly interesting for fun (note the publication date) and for how it challenges our mental models. Don't apply this design to your application. Adding layers instead of reducing complexity isn’t why you choose a document model.

Data locality has always driven performance because, despite advances in hardware and software, the laws of physics still apply. In the 1980s, when relational databases abstracted away the physical model, Oracle adopted this abstraction with SQL tables but also introduced physical clustering to preserve data colocation and avoid benchmark regressions. The idea that data accessed together should be stored together stayed relevant in the 2000s, as databases scaled horizontally and document databases like MongoDB emerged from this principle. It remains true today: you get the best performance by storing an application's object aggregates in a single block on disk. MongoDB adopted WiredTiger as its storage engine because it offers a flexible block-size B-tree that stores collection documents without fragmentation.

But flexible storage engine like WiredTiger didnt exists when the traditional RDBMSs designed their storage. You should avoid using the clustering technique described here for Oracle tables, as it is not very flexible when data updates occur on fixed-size blocks. The CLUSTER clause in CREATE TABLE is simply ignored by the Autonomous Database managed service. Interestingly, you may be surprised to realize that indexed clusters are actually used daily in Oracle databases to store dictionary tables. Oracle's implementation of the system dictionary treats these tables as logical relational tables, but physically denormalizes them. If you're skeptical, ask your DBA to run:

SELECT COUNT(*)
FROM dba_objects
WHERE object_type = 'CLUSTER';

You likely have at least nine of them—ten if you created one for EMP/DEPT. If you EXPLAIN PLAN FOR this query on dba_objects, you’ll see a huge UNION-ALL for what today would be stored as a document: a polymorphic aggregate to describe the various database object attributes in the database catalog.

I’ve written this for educational purposes only, and published it on a specific date, where any article can be a joke even when fact-based. When you read recommendations about design patterns, database choices, or data modeling rules, always cross‑check with how the system really works — and whether data accessed together is actually stored together, or presented that way by multiple internal abstraction layers and transformations. In case of doubts, look at the execution plan.

Read Concern "snapshot" for snapshot isolation outside explicit transactions

Franck Pachot — Mon, 30 Mar 2026 21:26:18 +0000

TL;DR: I a previous post I explained Why isn't "majority" the default read concern in MongoDB. If you’re used to SQL databases, you’ll likely prefer the snapshot read concern, which guarantees all rows are read from the same snapshot. Alternatively, run your operations in an explicit transaction, as transactions are ACID.

With majority, you avoid dirty or uncommitted reads because you only see data acknowledged by a majority. However, a scan can yield while the majority state advances, so some rows may come from a newer majority snapshot.

By default, MongoDB’s consistency boundary is the document—similar to an aggregate in domain‑driven design. For multi‑document consistency, use explicit transactions or the snapshot read concern. This is MongoDB’s tunable consistency model, whose defaults often suit event‑driven architectures more than traditional SQL workloads.

To illustrate this, here’s a demo.

I start by setting up the collection, the document count, and other parameters:


const NUM_ACCOUNTS = 1_000_000;
const INITIAL_BALANCE = 10000;  // cents
const BATCH_SIZE = 10_000;
const TOTAL_TRANSFERS = 100_000;
const NUM_WRITERS = 5;
const mydb = db.getSiblingDB("demo");
const coll = mydb.accounts;

I load one million accounts, all with the same amount:


for (let i = 0; i < NUM_ACCOUNTS; i += BATCH_SIZE) {
    const batch = [];
    for (let j = i; j < Math.min(i + BATCH_SIZE, NUM_ACCOUNTS); j++) {
        batch.push({ _id: j, balance: INITIAL_BALANCE });
    }
    coll.insertMany(batch, { ordered: false });
    if (i % 100_000 === 0) print("  " + (i / 1000) + "k...");
}
print("✅ Loaded " + NUM_ACCOUNTS.toLocaleString() + " accounts\n");

This function checks the total balance every second:


async function periodicReader(readConcern) {
    while (readingActive) {
        const result = coll.aggregate([
            { $group: { _id: null, total: { $sum: "$balance" } } }
        ], { readConcern: { level: readConcern } }
                                   // -> majority or snapshot
        ).toArray();
        const total = result.length ? result[0].total : "N/A";
        print("  📊 Aggregate read — total balance: " + total +
              " | transfers so far: " + transfersDone);
        // Wait ~1 second before next read
        await new Promise(r => setTimeout(r, 1000));
    }
}
// run it:
let transfersDone = 0;
let readingActive = true;
const readerPromise = periodicReader("majority");

With no writes and one million accounts initialized with ten thousand each, the total balance is 10,000,000,000:

  📊 Aggregate read — total balance: 10000000000 | transfers so far: 0
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 0
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 0
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 0
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 0
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 0

I perform random account-to-account transfers that debit one account and credit another for the same amount in a single transaction, so the total balance stays the same, and run it in five threads:


print("⏳ Launching " + NUM_WRITERS + " writers + 1 reader...\n");
let writingDone = false;
let transfersDone = 0;
async function writer(id, count) {
    const s = mydb.getMongo().startSession();
    const sc = s.getDatabase("demo").accounts;
    for (let t = 0; t < count; t++) {
        // pick two random accounts
        const from = Math.floor(Math.random() * NUM_ACCOUNTS);
        let to =     Math.floor(Math.random() * NUM_ACCOUNTS);
        while (to === from) to = Math.floor(Math.random() * NUM_ACCOUNTS);
        // same amount to debit on one, credit on the other
        const amount = Math.floor(Math.random() * 1000);
        // do that in a transaction
        try {
            s.startTransaction();
            sc.updateOne({ _id: from }, { $inc: { balance: -amount } });
            sc.updateOne({ _id: to },   { $inc: { balance:  amount } });
            s.commitTransaction();
            transfersDone++;
        } catch (e) {
            try { s.abortTransaction(); } catch (_) {}
        }
    }
    s.endSession();
    print("  ✅ Writer " + id + " done");
}
// Run in threads:
const writerPromises = [];
for (let i = 0; i < NUM_WRITERS; i++) {
    writerPromises.push(writer(i + 1, TOTAL_TRANSFERS / NUM_WRITERS));
}

The reader thread is still running, but it now reports an inconsistent total balance due to the "majority" read concern:

  📊 Aggregate read — total balance: 10000002595 | transfers so far: 634
  📊 Aggregate read — total balance: 10000003902 | transfers so far: 1177
  📊 Aggregate read — total balance: 9999999180 | transfers so far: 1742
  📊 Aggregate read — total balance: 10000002564 | transfers so far: 2325
  📊 Aggregate read — total balance: 9999995030 | transfers so far: 2900
  📊 Aggregate read — total balance: 10000001154 | transfers so far: 3462
  📊 Aggregate read — total balance: 9999996910 | transfers so far: 4029
  📊 Aggregate read — total balance: 9999992085 | transfers so far: 4655
  📊 Aggregate read — total balance: 9999995372 | transfers so far: 5215
  📊 Aggregate read — total balance: 9999999916 | transfers so far: 5792
  📊 Aggregate read — total balance: 9999998316 | transfers so far: 6396
  📊 Aggregate read — total balance: 9999997128 | transfers so far: 6976
  📊 Aggregate read — total balance: 10000006447 | transfers so far: 7516
  📊 Aggregate read — total balance: 9999998330 | transfers so far: 8091
  📊 Aggregate read — total balance: 10000001286 | transfers so far: 8656
  📊 Aggregate read — total balance: 10000001899 | transfers so far: 9240
  📊 Aggregate read — total balance: 9999996708 | transfers so far: 9845
  📊 Aggregate read — total balance: 10000005159 | transfers so far: 10444
  📊 Aggregate read — total balance: 10000002749 | transfers so far: 11012
  📊 Aggregate read — total balance: 9999999925 | transfers so far: 11623

If you’re coming from SQL databases, it may be surprising: in SQL, every statement runs in an explicit transaction with a defined isolation level. MongoDB instead offers several consistency boundaries:

Document-level consistency by default
Statement-level consistency with the snapshot read concern
Transaction-level consistency with explicit transactions

I’ve been using the default document-level consistency, so the total was inconsistent, and I’ll now show the other levels that provide stronger read-time consistency.

I stop the reading thread:

readingActive = false;

Then I restart it with a snapshot read concern:

let readingActive = true;
const readerPromise = periodicReader("snapshot");

Now the results are consistent: the total balance stays the same while money is being transferred between accounts.

  📊 Aggregate read — total balance: 10000000000 | transfers so far: 92845
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 93439
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 94022
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 94590
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 95161
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 95737
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 96307
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 96835
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 97353
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 97920
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 98478
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 99038
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 99621
  ✅ Writer 3 done
  ✅ Writer 1 done
  ✅ Writer 4 done
  ✅ Writer 2 done
  ✅ Writer 5 done
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 99999
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 99999
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 99999
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 99999
  📊 Aggregate read — total balance: 10000000000 | transfers so far: 99999

This doesn't lock anything because it uses WiredTiger Multi-Version Concurrency Control (MVCC) and may even be faster as it doesn't have to wait for the majority committed snapshot that may be delayed if secondaries are not available.

I start the writes again, as they ended, without starting the reader thread as I'll run it manually:

let readingActive = false;
for (let i = 0; i < NUM_WRITERS; i++) {
    writerPromises.push(writer(i + 1, TOTAL_TRANSFERS / NUM_WRITERS));
}

I run a manual aggregate with the default read concern, and it shows an inconsistent balance:

const s = mydb.getMongo().startSession();
const sc = s.getDatabase("demo").accounts;
sc.aggregate([
        { $group: { _id: null, total: { $sum: "$balance" } } }
        ] 
)

test> sc.aggregate([
...         { $group: { _id: null, total: { $sum: "$balance" } } }
...         ] 
... )
[ { _id: null, total: 9999997810 } ]

Instead of specifying "snapshot" read concern, I run it in a transaction:

s.startTransaction();
sc.aggregate([
        { $group: { _id: null, total: { $sum: "$balance" } } }
        ] 
)
s.commitTransaction();

test> s.startTransaction();

test> sc.aggregate([
...         { $group: { _id: null, total: { $sum: "$balance" } } }
...         ] 
... )
[ { _id: null, total: 10000000000 } ]
test> s.commitTransaction();

As you would expect, transactions are ACID and consistent. It's another way to get snapshot isolation.

Before giving more explanation I stop all:

(async () => {
await Promise.all(writerPromises);
readingActive = false;
await readerPromise;
})();

All databases provide different levels of consistency, and “consistency” actually means two things:

Snapshot consistency: all data is read from a single commit point in time. The result is then stale because the read timestamp is fixed at the start of the statement (e.g., SQL Read Committed) or at the start of the transaction (e.g., SQL Repeatable Read). This is the consistency as defined by the ACID properties.
Read-your-writes consistency: you see the latest committed state, and the read time can advance during the scan to reduce staleness. This is the consistency as defined by the CAP theorem.

SQL databases don’t always use the first model. Isolation levels guarantee that you see committed changes, but not that all rows were committed at the same instant. MVCC databases usually provide snapshot consistency by setting a read timestamp in the past, but some may re-read rows later if they change and are about to be updated, to avoid restarting the whole query at a later point in time.

To validate a total balance, you need a consistent snapshot. In some other cases, reading the latest values is preferable to using a stale snapshot. Neither approach is always best.

In the SQL bubble, inconsistent snapshots often seem unacceptable, whereas in event-driven NoSQL systems, reading the latest writes makes more sense. Any database can work well if you understand it, and poorly if you ignore its behavior under race conditions.

Non-MVCC databases can return the latest writes within a single, consistent state but require heavy locking to do so. In contrast, MVCC databases avoid blocking reads, but must choose between a stable, timeline-consistent snapshot that may be stale or a latest-write view whose read times keep moving.

For SQL databases, you need to understand the isolation levels:

Per-statement read time in Read Committed Snapshot Isolation.
Per-transaction read time in Snapshot Isolation

For MongoDB, you should understand the consistency boundaries:

Document-level consistency by default
Statement-level consistency with the "snapshot" read concern
Transaction-level consistency with explicit transactions

SQL Assertions, ANSI join, and ORA-08697

Franck Pachot — Sun, 29 Mar 2026 17:39:06 +0000

In the previous post on consistency boundaries, we saw that an updatable join view can hide a write-skew anomaly when the developer assumes the consistency boundary is a single row, even though there are actually two underlying rows and only one is locked. I wanted to see how the new SQL Assertions introduced in "Oracle AI Database 26ai Release 23.26.1.0.0" handle this, since they use a different locking mechanism.

TL;DR: they handle it correctly, but without ANSI joins.

Without assertions, for an employee with a salary of 1000 and a commission of 100, two concurrent users could each add 42 to both values because they couldn’t see each other’s changes, and updating one column didn’t lock the other. I then added a SQL assertion that doesn’t allow the commission to exceed 1150, so that only one session can add 42. This assertion works as expected, waiting on the other transaction before checking the sum:

CREATE ASSERTION salary_plus_commission_le_1150
CHECK (
  NOT EXISTS (
    SELECT 'violation'
    FROM emp_salary s, emp_commission c
    WHERE s.empno = c.empno
      AND s.salary + c.commission > 1150
  )
);

When the first transaction committed, the second transaction detected the write skew:

ERROR at line 1:
ORA-08601: SQL assertion (FRANCK.SALARY_PLUS_COMMISSION_LE_1150) violated.

I'm writing this blog post because I had trouble finding the correct declaration for this assertion, and I hope it helps others. My first attempt was simply:

CREATE ASSERTION salary_plus_commission_le_1150
CHECK (
  NOT EXISTS (
    SELECT 'violation'
    FROM emp_salary s join emp_commission c USING (empno)
    WHERE s.salary + c.commission > 1150
  )
);

This joins the two tables and raises a violation is the sum of salary and commission is higher than 1150, but the CREATE ASSERTION failed with:

ORA-08689: CREATE ASSERTION failed
ORA-08697: SYS owned tables are not supported.

I'm not using SYS, and cannot use SYS because I'm on the managed service "Autonomous Database" which doesn't give SYSDBA privileges.

I reproduced this in a local environment to trace the SQL statements (ALTER SESSION SET EVENTS 'sql_trace bind=false, wait=false') and found that Oracle internally checked this constraint with:

/* SQL Analyze(250,0) */
SELECT /*+ ALL_ROWS BYPASS_RECURSIVE_CHECK */
1 FROM "SYS"."DUAL" WHERE (
  NOT EXISTS (
    SELECT 1
    FROM emp_salary s
    JOIN emp_commission c ON s.empno = c.empno
    WHERE s.salary + c.commission > 1150
  )
)

The good old DUAL table is owned by SYS and cannot be used in the assertion.

After trying many variations, I realized that SQL assertions are always evaluated with a SELECT from "SYS"."DUAL", even for cases that work, like in my previous post on SQL Assertions.

Then, I remembered what I’d learned from 30 years of working with Oracle Databases: if something fails with an ANSI join in Oracle, try the legacy join syntax (no JOIN clauses, only WHERE conditions). When ANSI joins were added in 9i, Oracle implemented them with internal transformations that affected other features. Most of those issues were fixed over the 20 years that followed, but SQL assertions seem to have fallen into that trap.

With the legacy join syntax, the internal query still reads "SYS"."DUAL", but it works:

=====================
PARSING IN CURSOR #140737366953320 len=208 dep=0 uid=136 oct=290 lid=136 tim=6202261308 hv=3501011950 ad='7ffff8be4960'
 sqlid='fzcfcfv8auczf'
CREATE ASSERTION salary_plus_commission_le_1150
CHECK (
  NOT EXISTS (
    SELECT 'violation'
    FROM emp_salary s, emp_commission c
    WHERE s.empno = c.empno
      AND s.salary + c.commission > 1150
  )
)
END OF STMT

...

=====================
PARSING IN CURSOR #140737364327008 len=286 dep=1 uid=136 oct=3 lid=0 tim=6202334328 hv=225567679 ad='783b9f10' sqlid='fv1va6n6r3sxz'
 /* SQL_ASSERTION obj#=72600 */ 
SELECT /*+  ALL_ROWS BYPASS_RECURSIVE_CHECK */ 1 
FROM "SYS"."DUAL" 
WHERE (   NOT EXISTS (SELECT 'violation' "'VIOLATION'" FROM "FRANCK"."EMP_SALARY" "S","FRANCK"."EMP_COMMISSION" "C" WHERE "S"."EMPNO"="C"."EMPNO" AND "S"."SALARY"+"C"."COMMISSION">1150) )
END OF STMT

Two things to remember:

SQL Assertions provide correct consistency boundaries to avoid write skew in the absence of a serializable isolation level.
ANSI joins are nice, but Oracle was designed before this standard, with another syntax, and some bugs persist. Falling back to the old syntax may be safer.

Consistency boundaries in SQL databases vs. MongoDB

Franck Pachot — Sun, 29 Mar 2026 17:33:17 +0000

Two common myths surround database consistency:

Myth 1: Only SQL databases provide strong consistency. In fact, MongoDB—a NoSQL database—supports ACID transactions with strong consistency, even in sharded clusters, with clear serializable boundaries: the document.
Myth 2: SQL consistency is simple and fully abstracted, so you can treat views like tables and run transactions as if race-condition anomalies never occur. In reality, no RDBMS fully achieves this ideal, so you must understand each system’s consistency boundaries (column?, scanned rows?, returned rowset?, range?, partition?, table?).

With MongoDB, the consistency boundaries follow domain driven design aggregates: concurrent writes to the same document conflict, even if they effectively read or write different fields. This guarantees that the database cannot break the business invariants set by the application. I've detailed an example in Serializable Transactions in MongoDB: The Doctor's On-Call Shift example. Document-level operations provide stronger integrity constraints, since schema validation can cover the entire aggregate, including one-to-many relationships. SQL databases need assertions to provide the same guarantees, but these are rarely implemented in RDBMSs, so only a limited subset of integrity constraints are available, like foreign keys.

Relational databases and SQL are meant to hide locking internals from developers, and full support for the serializable isolation level would make this feasible. In practice, though, serializable is often incomplete or hurts scalability, so developers use weaker isolation levels and must consider what is locked. They commonly add explicit locking, like SELECT FOR UPDATE, to prevent race-condition anomalies.

Before looking at an example, ask yourself whether you understand the consistency boundaries of your databases. Updating a single column often locks the entire row of that column. Some databases lock at a finer granularity (for example, YugabyteDB’s column-level locking), while others may lock more via lock escalation.

Normalization also affects this by spreading a logical aggregate—which in Domain-Driven Design corresponds to a consistency boundary—across multiple tables and rows. If you run a SELECT ... FOR UPDATE with a JOIN, do you expect all the tables to be locked? The FOR UPDATE clause’s OF option lets PostgreSQL specify tables and Oracle Database specify columns, but in both cases, the underlying effect is to lock all the rows involved.

Defaults may be inconsistent. SELECT FOR UPDATE without OF locks all joined rows, but UPDATE on a join behaves differently as we will demonstrate below. Column-level locking also raises another issue without joins: check constraints must see a consistent state, even for columns that were not updated (see the now-fixed YugabyteDB issue: https://github.com/yugabyte/yugabyte-db/issues/15196).

SQL is an abstraction where the tables referenced in queries can also be views. In Oracle Database, join views can be updatable, which raises an important question about consistency: when you update a single column from one underlying table through a join view, should locks be taken on both underlying tables, or only on the one that’s actually being updated?

Demonstration

I'll use Oracle Database for this demonstration because it’s the only one I know that supports updates on joins. Those who say “this wouldn’t happen on PostgreSQL” are right that PostgreSQL has a true serializable isolation level, but updatable views are more limited and would not allow to build such an example. There’s no single best database—only different ones with different trade-offs. Expertise means understanding those differences.

Here is an example. I create two tables to store the employees salary and commission separately, and build a view on top of them to expose it as one:

create table emp_salary ( 
  empno      number primary key,
  salary     number
);

create table emp_commission ( 
  empno      number primary key,
  commission number
);

insert into emp_salary     values (99, 1000);
insert into emp_commission values (99,  100);
commit;

create view emp as 
 select * from emp_salary 
 join emp_commission 
 using ( empno )
;

This view is updateable and exposes all information per employee:

21:44:07 SQL> select * from emp;

   EMPNO    SALARY    COMMISSION
________ _________ _____________
      99     1,000           100

Now, an instruction is sent to the Human Resources department to add 42 to this employee, and the HR agent can choose to add it either to the salary or to the commission.

Alice, in HR, starts a transaction (using the serializable isolation level, which does not change the behavior here because in Oracle it only achieves Snapshot Isolation). She checks the employee’s salary and commission and decides to add 42 to the commission. As an extra safety measure, to ensure the operation is idempotent, she adds a condition on the salary and commission so that the update occurs only if neither has already been increased:

21:43:58 SQL> set transaction isolation level serializable;

Transaction ISOLATION succeeded.

21:44:07 SQL> select * from emp;

   EMPNO    SALARY    COMMISSION
________ _________ _____________
      99     1,000           100

21:44:11 SQL> update emp
  2   set commission=commission+42
  3   where empno=99
  4   and salary=1000 and commission=100
  5* ;

1 row updated.

21:44:19 SQL>

Alice didn't commit yet, and her colleague, Bob, in another office got the same instruction. He does the same, but decides to add 42 to the salary:

21:43:58 SQL> set transaction isolation level serializable;

Transaction ISOLATION succeeded.

21:44:26 SQL> select * from emp;

   EMPNO    SALARY    COMMISSION
________ _________ _____________
      99     1,000           100

21:44:29 SQL> update emp
  2   set salary=salary+42
  3   where empno=99
  4   and salary=1000 and commission=100
  5* ;

1 row updated.

21:44:37 SQL>

As both check the current state (salary=1000 and commission=100) only one should actually update a row. However, nothing blocks, and nothing fails. Alice is able to commit:

21:44:19 SQL> commit;

Commit complete.

21:44:45 SQL> select * from emp;

   EMPNO    SALARY    COMMISSION
________ _________ _____________
      99     1,000           142

Bob is also allowed to commit:

21:44:37 SQL> commit;

Commit complete.

21:44:50 SQL> select * from emp;

   EMPNO    SALARY    COMMISSION
________ _________ _____________
      99     1,042           142

The employee incorrectly received a raise of 42 in both salary and commission because the database failed to prevent a race condition.

Explanation

This is a Multi-Version Concurrency Control (MVCC) anomaly: the WHERE clause was evaluated on a consistent snapshot, stale, but the UPDATE was applied to the current state, and the resulting conflict went undetected. You can reproduce this in db<>fiddle by using an autonomous transaction to simulate a second user.

Neither Bob or Alice are responsible for this error because both of them checked the initial salary and commission in the same statement that updated one of them. It's the database that lied to them, or rather didn't expose that it was not one consistnt state. Users are not supposed to know that they query a view where data is physically stored in different tables and that the database locked only one of the two rows that appeared as one after the join.

Solutions

Write to lock. For safety, users should update both columns, even when values do not change, so that write conflicts protect against concurrent updates. Hibernate does this by default, updating all mapped columns. It is less efficient, but this consistency boundary is one reason dynamic update is not the default. Object–Relational Mappers (ORMs) and document databases such as MongoDB not only map domain model entities but also help ensure their consistency when those entities are mapped to a relational model that splits aggregates behind the abstraction layer.

Explicit locking. Another option is to add FOR UPDATE to a first SELECT in the transaction. Without an OF clause, it locks all rows in the join. Bob’s session then waits, sees Alice’s changes, and skips its own update. However, unless this is wrapped in a stored procedure, it adds an extra database round trip and holds locks across context switches and network calls, which harms scalability.

Avoid abstraction. A third solution is to avoid using views on joins, as they create an ambiguous consistency boundary where the user can’t tell what is locked. Still, the user could update on a join and experience the same:

update (
  select * from emp_salary join emp_commission using ( empno )
) set salary=salary+42
  where empno=99 and salary=1000 and commission=100
;

You might then ask how JSON-relational Duality views work, since they are also updatable views over multiple tables. The following example is similar to a join:

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW emp_dv AS
  emp_salary @insert @update @delete
  {
    _id      : empno,
    salary     : salary @update,
    commission : emp_commission @update
    @link ( from: empno, to: empno ) {
     empno : empno
     commission: commission
    }
  }
/

If I try to update different fields concurrently, the conflict is detected:

UPDATE emp_dv
  SET data = JSON_TRANSFORM(data, 
             SET '$.commission.commission' = 10)
;

declare
 -- using autonomous transaction to simulate a concurrent transaction 
 pragma autonomous_transaction;
begin
 UPDATE emp_dv
  SET data = JSON_TRANSFORM(data, 
             SET '$.salary' = 100)
;
 commit;
end;
/

declare
*
ERROR at line 1:
ORA-40896: Cannot update JSON Relational Duality View 'EMP_DV': Concurrent modification detected to document with ID 'FB03C16400'.
ORA-06512: at line 5
Help: https://docs.oracle.com/error-help/db/ora-40896/

It fails, so you must implement retry logic (MongoDB would automatically retry here). JSON-Relational Duality Views use ETAG to detect conflicts on the same document rather than using row locks.

You may also wonder how SQL assertions work, since they require consistency across multiple rows. They use a different locking mechanism to ensure a consistent state. I’ve included an example in the next post.

Conclusion

Relational databases aim to hide physical storage and locking so developers don’t have to manage them directly. But to achieve good performance, and avoid consistency anomalies, developers still need to understand locking boundaries. Not all RDBMSs support updatable join views (SQL-92 only specifies updatability for single-table views), though all provide explicit locking mechanisms such as SELECT FOR UPDATE. Row-store databases usually lock at the row level, but views may aggregate multiple rows without the developer knowing it.

By contrast, MongoDB, as a NoSQL database, exposes a simpler model: the document is the consistency boundary, like the aggregate in Domain-Driven Design. Developers update documents, not logical views, and write consistency applies to the entire document rather than a portion of it.

Non-First Normal Forms and MongoDB: an alternative to 4NF to address 3NF anomalies

Franck Pachot — Wed, 25 Mar 2026 18:32:29 +0000

SQL databases are grounded in relational algebra, but they are not the only databases with a strong theoretical basis. MongoDB, designed as a document database to solve practical engineering problems and improve developer experience, also builds on theory. It supports non–first-normal-form schemas and extends relational operators to work with them. This foundation is described in a 1986 paper, published 23 years earlier: Theory of Non-First Normal Form Relational Databases

MongoDB's aggregation pipeline operators ($unwind, $group, $lookup, set operations) are concrete implementations of the paper's abstract algebraic operators.

I've build the following examples while reading the paper to illustrate the concepts with practical examples.

The Basic ¬1NF Data Model

The paper defines nested relations where attributes can be atomic or relation-valued:

This structure records facts about employees, like some information about their children, their skills, and exams they passed.

This maps directly to a MongoDB document schema:

// This IS the paper's example, expressed as MongoDB documents
db.employees.insertMany([
  {
    ename: "Smith",
    Children: [
      { name: "Sam", dob: "2/10/84" },
      { name: "Sue", dob: "1/20/85" }
    ],
    Skills: [
      {
        type: "typing",
        Exams: [
          { year: 1984, city: "Atlanta" },
          { year: 1985, city: "Dallas" }
        ]
      },
      {
        type: "dictation",
        Exams: [
          { year: 1984, city: "Atlanta" }
        ]
      }
    ]
  },
  {
    ename: "Watson",
    Children: [
      { name: "Sam", dob: "3/12/78" }
    ],
    Skills: [
      {
        type: "filing",
        Exams: [
          { year: 1984, city: "Atlanta" },
          { year: 1975, city: "Austin" },
          { year: 1971, city: "Austin" }
        ]
      },
      {
        type: "typing",
        Exams: [
          { year: 1962, city: "Waco" }
        ]
      }
    ]
  }
])

MongoDB's document model is essentially what Roth called a "database scheme" — a collection of rules where attributes can be zero-order (scalar fields) or higher-order (embedded arrays of documents). The paper's Figure 3-1 is literally a MongoDB collection:

When using an object-oriented language, this model looks natural but this paper is from 1986 so there was another motivation.

Motivation: ¬1NF as an alternative to 4NF decomposition

The paper's example (Figure 1-1) shows an employee relation in 1NF requires 10 rows with massive redundancy:

employee | child | skill
-----------------------------
Smith    | Sam   | typing
Smith    | Sue   | typing
Smith    | Sam   | filing
Smith    | Sue   | filing
Jones    | Joe   | typing
Jones    | Mike  | typing
Jones    | Joe   | dictation
Jones    | Mike  | dictation
Jones    | Joe   | data entry
Jones    | Mike  | data entry

The ¬1NF version needs only 2 tuples. The paper notes three problems with 1NF:

Insert anomaly: Adding a child for Jones requires adding 3 tuples (one per skill)
Update anomaly: Changing Smith's "typing" to "word processing" requires updating multiple rows
Decomposition cost: The 1NF solution requires splitting into two tables and joining them back

Today, these anomalies are not a major problem because a single updateMany() or insertMany operation in a MongoDB transaction is atomic, just like an UPDATE or INSERT in SQL databases. More importantly, the document model makes multi-document operations rare—the Cartesian explosion of redundant data remains undesirable.

The first four normal forms (1NF, 2NF, 3NF, BCNF) do not address this, because there are no non-trivial functional dependencies in this relation: all three attributes together form the only candidate key. This relation is therefore already in BCNF. This is precisely why Fagin introduced 4NF in 1977.

This example illustrates a multivalued dependency (MVD) violation: children and skills are independent multivalued attributes of an employee, but they’re stored together. This creates a redundant Cartesian product (10 rows for 2 employees). Fourth Normal Form (4NF) addresses this by splitting the independent dependencies into separate tables to remove these anomalies, but at the cost of more tables touched per transaction and more joins per query.

Even a Third Normal Form (3NF) schema can still suffer from insert and update anomalies and data redundancy. MongoDB’s document model can offer many of 4NF’s benefits without incurring the join overhead.

Here is the MongoDB Equivalent:

// The ¬1NF representation — exactly what MongoDB does naturally
db.employees.insertMany([
  {
    employee: "Smith",
    Children: [{ child: "Sam" }, { child: "Sue" }],
    Skills: [{ skill: "typing" }, { skill: "filing" }]
  },
  {
    employee: "Jones",
    Children: [{ child: "Joe" }, { child: "Mike" }],
    Skills: [
      { skill: "typing" },
      { skill: "dictation" },
      { skill: "data entry" }
    ]
  }
])

Adding a new child for Jones is just one operation, no anomaly:


db.employees.updateOne(
  { employee: "Jones" },
  { $push: { Children: { child: "Sara" } } }
)

Changing Smith's "typing" to "word processing" — one operation:


db.employees.updateOne(
  { employee: "Smith", "Skills.skill": "typing" },
  { $set: { "Skills.$.skill": "word processing" } }
)

Querying everything about an employee doesnt need a join:


db.employees.findOne({ employee: "Smith" })

MongoDB exists for a fundamental reason: its document model eliminates the need for decomposition and joins, as well as the update anomalies introduced by the 1NF constraint. Most workloads rely on single-document operations, which MongoDB physically optimizes as single-shard transactions, a single disk read or write, and a single replication operation. These inserts and updates lock the document, which serves as the consistency boundary, but modify only the fields whose values change and update only the corresponding index entries.

Nest and Unnest Operators

The paper defines nest (ν) to aggregate flat rows into nested structures, and unnest (μ) to flatten nested structures back:

ν_{B=(C,D)}(r): nest attributes C,D into a new nested relation B
μ_{B}(r): unnest nested relation B

MongoDB's aggregation pipeline has direct equivalents.

The paper's unnest (μ) operator is $unwind:

db.employees.aggregate([
  { $unwind: "$Skills" }
])

Each skill becomes its own document, duplicating employee info.

Deep unnest (μ* in the paper) is possible with multiple $unnest stages:

db.employees.aggregate([
  { $unwind: "$Children" },
  { $unwind: "$Skills" }
])

This produces the full 1NF Cartesian expansion, flattening both Children and Skills.

When starting with a flat collection, the paper's nest operator (ν) is $group:

db.flat_employees.aggregate([
  {
    $group: {
      _id: "$employee",
      Children: { $addToSet: { child: "$child" } },
      Skills:   { $addToSet: { skill: "$skill" } }
    }
  }
])

The paper proves that the order of unnesting doesn't matter (Thomas & Fischer's result). In MongoDB, these produce the same fully-flat result:

db.employees.aggregate([
  { $unwind: "$Children" },
  { $unwind: "$Skills" }
])

db.employees.aggregate([
  { $unwind: "$Skills" },
  { $unwind: "$Children" }
])

The paper also notes that nest is not always an inverse for unnest in general, but IS an inverse for Partitioned Normal Form (PNF) relations.

Partitioned Normal Form (PNF)

PNF requires that the atomic attributes of each relation (and each nested relation) form a key. The paper shows a pathological relation (Figure 1-3) that violates PNF:

Smith, the same employee, has two different skill sets, and Jones has a duplicate across sets.

In MongoDB, PNF corresponds to the fundamental design principle that _id determines the document. The pathological case above would mean having two documents for "Smith" with different Skills arrays — which is exactly what MongoDB's _id uniqueness constraint prevents at the collection level.

Without using _id as the employee identifier, we could have two employees with the same name:

db.bad_design.insertMany([
  { employee: "Smith", Skills: ["typing", "filing"] },
  { employee: "Smith", Skills: ["sorting", "mailing"] }
])

The correct design if we don't want to use _id is enforcing PNF with a unique index:

db.good_design.createIndex({ employee: 1 }, { unique: true })

db.good_design.insertOne({
  employee: "Smith",
  Skills: ["typing", "filing", "sorting", "mailing"]
})

Like in many papers, the employee name is used as an identifier (two "Smith" is one person) but obviously in real life there's a generated identifier to identify the physical person.

The paper, in theorem 5-1, proves that PNF is closed under unnesting. In MongoDB terms: if your documents are well-designed (one document per logical entity), then $unwind won't create ambiguous or inconsistent flat results.

For nested relations, PNF also applies recursively.

MongoDB schema validation can enforce the PNF constraint by comparing the size of an array with the size when duplicates are ignored (using $setUnion with an empty array):

db.createCollection("employees", {
  validator: {
    $and: [
      // JSON Schema for structure and types
      {
        $jsonSchema: {
          bsonType: "object",
          required: ["ename"],
          properties: {
            ename: { bsonType: "string" },
            Children: { bsonType: "array", items: { bsonType: "object", required: ["name"], properties: { name: { bsonType: "string" }, dob: { bsonType: "string" } } } },
            Skills: { bsonType: "array", items: { bsonType: "object", required: ["type"], properties: { type: { bsonType: "string" },
             Exams: { bsonType: "array", items: { bsonType: "object", required: ["year"], properties: { year: { bsonType: "int" }, city: { bsonType: "string" } } } } } } }
          }
        }
      },
      // PNF uniqueness constraints using $expr
      {
        $expr: {
          $and: [
            // Level 1: Children.name must be unique within the document
            {
              $eq: [
                { $size: { $ifNull: ["$Children.name", []] } },
                { $size: { $setUnion: [{ $ifNull: ["$Children.name", []] }] } }
              ]
            },
            // Level 1: Skills.type must be unique within the document
            {
              $eq: [
                { $size: { $ifNull: ["$Skills.type", []] } },
                { $size: { $setUnion: [{ $ifNull: ["$Skills.type", []] }] } }
              ]
            },
            // Level 2: Within EACH Skills element, Exams.year must be unique
            {
              $allElementsTrue: {
                $map: {
                  input: { $ifNull: ["$Skills", []] },
                  as: "skill",
                  in: { $eq: [ { $size: { $ifNull: ["$$skill.Exams.year", []] } }, { $size: { $setUnion: [{ $ifNull: ["$$skill.Exams.year", []] }] } } ] }
                }
              }
            }
          ]
        }
      }
    ]
  },
  validationLevel: "strict",
  validationAction: "error"
});

The fact that nest is not always the inverse of unnest is a well-known MongoDB pitfall:

db.employees.drop()  
db.employees.insertMany([  
  {  
    _id: 1,  
    employee: "Smith",  
    Children: [{ child: "Sam" }, { child: "Sue" }],  
    Skills: [{ skill: "typing" }]  
  },  
  {  
    _id: 2,  
    employee: "Smith",  
    Children: [{ child: "Tom" }],  
    Skills: [{ skill: "filing" }, { skill: "sorting" }]  
  },  
  {  
    _id: 3,  
    employee: "Jones",  
    Children: [{ child: "Joe" }],  
    Skills: [{ skill: "typing" }]  
  }  
])  

db.employees.aggregate([  
  // unnest (μ) 
  { $unwind: "$Children" },  
  // nest (ν), grouping by employee, which is NOT a key  
  {  
    $group: {  
      _id: "$employee",  
      Children: { $addToSet: "$Children" },  
      Skills: { $first: "$Skills" }  
    }  
  },  
  // Clean up  
  { $project: { _id: 0, employee: "$_id", Children: 1, Skills: 1 } },  
  { $sort: { employee: 1 } }  
])

This results in two Smiths collapsed into one:

[
  {
    Children: [ { child: 'Joe' } ],
    Skills: [ { skill: 'typing' } ],
    employee: 'Jones'
  },
  {
    Children: [ { child: 'Tom' }, { child: 'Sam' }, { child: 'Sue' } ],
    Skills: [ { skill: 'typing' } ],
    employee: 'Smith'
  }
]

The correct way is grouping by the proper key:

db.employees.aggregate([  
  // unnest (μ)
  { $unwind: "$Children" },  
  // nest (ν), grouping by _id, which IS a key (PNF)  
  {  
    $group: {  
      _id: "$_id",  
      employee: { $first: "$employee" },  
      Children: { $push: "$Children" },  
      Skills: { $first: "$Skills" }  
    }  
  },  
  // Clean up  
  { $project: { _id: 1, employee: 1, Children: 1, Skills: 1 } },  
  { $sort: { _id: 1 } }  
])

This is the paper's Theorem 5-2 demonstrated in MongoDB: nest inverts unnest if and only if the grouping key satisfies PNF.

Extended Algebra Operators

I a previous post, From Relational Algebra to Document Semantics I explained how MongoDB extended the semantics of the relational selection Selection (σ) to non-1NF schemas, and mentioned that other relational operations are available in MongoDB. They are covered by the ¬1NF paper.

The paper defines extended union (∪ᵉ) to merge nested relations for tuples that agree on atomic attributes, rather than treating them as separate tuples. In MongoDB, this is achieved with $merge or with aggregation. Suppose we want to merge new course data into existing student records:

db.students.insertMany([
  {
    sname: "Jones",
    Courses: [
      { cname: "Math", grade: "A" },
      { cname: "Science", grade: "B" }
    ]
  },
  {
    sname: "Smith",
    Courses: [
      { cname: "Math", grade: "A" },
      { cname: "Physics", grade: "C" },
      { cname: "Science", grade: "A" }
    ]
  }
])

db.students.updateOne(
  { sname: "Jones" },
  { $addToSet: { Courses: { cname: "Physics", grade: "B" } } }
)

db.students.updateOne(
  { sname: "Smith" },
  {
    $addToSet: {
      Courses: {
        $each: [
          { cname: "Chemistry", grade: "A" },
          { cname: "English", grade: "B" }
        ]
      }
    }
  }
)

This added "Physics:B" to Jones, and "Chemistry:A", "English:B" to Smith without adding two tuples with different course sets like a standard union would do:

db.students.find()

[
  {
    _id: ObjectId('69c3a72344fc089068d4b0c2'),
    sname: 'Jones',
    Courses: [
      { cname: 'Math', grade: 'A' },
      { cname: 'Science', grade: 'B' },
      { cname: 'Physics', grade: 'B' }
    ]
  },
  {
    _id: ObjectId('69c3a72344fc089068d4b0c3'),
    sname: 'Smith',
    Courses: [
      { cname: 'Math', grade: 'A' },
      { cname: 'Physics', grade: 'C' },
      { cname: 'Science', grade: 'A' },
      { cname: 'Chemistry', grade: 'A' },
      { cname: 'English', grade: 'B' }
    ]
  }
]

The paper emphasizes that standard set union treats entire tuples as atomic — if two tuples differ at all, both appear in the result. Extended union is smarter: it matches on keys and merges the nested parts. MongoDB's $addToSet and $merge stage embody this exact semantics.

MongoDB provides $setIntersection (∩ᵉ), $setUnion (∪ᵉ), and $setDifference(−ᵉ) — these are the exact extended operators from the paper, applied to arrays within documents.

The paper defines extended projection (πᵉ) as normal projection followed by tuplewise extended union — merging tuples that agree on remaining atomic attributes. In MongoDB — this is $group after $project.

The paper limits extended join (⋈ᵉ) to relations sharing top-level attributes. Common nested attributes are intersected. In MongoDB, $lookup performs the join, and we can intersect nested parts.

Null Values and Empty Nested Relations

The paper defines three null types ("do not exists", "unknown", "no information") and establishes that empty nested relations means "no-information", not "nonexistent".

MongoDB has null, missing fields, and empty arrays, which map to the paper's null types.

"no information" is an empty array

For example, this adds employee Jones with unknown information as empty arrays:

db.employees.insertOne({
  ename: "Jones",
  Children: [],    // Paper says this equals {(ni, ni)} — we know nothing
  Skills: []       // Same — empty array = no information
})

"unknown" is a field with a null value

Jones has some skills, but we don't know which ones, only that he took an exam somewhere in 1981:

db.employees.updateOne(
  { ename: "Jones" },
  {
    $set: {
      Children: null,  // MongoDB null ≈ paper's "we don't know"
      Skills: [{
        type: null,    // there IS a skill, we don't know which
        Exams: [{ year: 1981, city: null }]  // unknown city
      }]
    }
  }
)

"do not exists" is an inexisting field

Jones definitively has no children:

db.employees.updateOne(  
  { ename: "Jones" },  
  {  
    $unset: { Children: "" }  // Remove the field entirely — no children  
  }  
)

MongoDB's $unwind by default removes documents with empty arrays. The paper says that unnesting an empty set should produce a null tuple, not delete the document. MongoDB equivalent uses preserveNullAndArrays:

db.employees.aggregate([
  { $unwind: { path: "$Skills", preserveNullAndArrays: true } }
])

SQL/NF Query Language → MongoDB Query Language

The paper introduces SQL/NF, a user-oriented non-1NF query language based on commercial SQL and a proposed relational database standard. It never became a standard but MongoDB's query language implements the same.

SQL/NF accepts nested expressions in SELECT (Projection with filtering):

SELECT dname, (SELECT ALL FROM Emps WHERE sal > 35000)
FROM Company
WHERE EXISTS(Emps WHERE sal > 35000)

MongoDB can do the same in $project:

db.company.aggregate([  
  { $match: { "Emps.sal": { $gt: 35000 } } },  
  {  
    $project: {  
      _id: 0,  
      dname: 1,  
      Emps: {  
        $filter: {  
          input: "$Emps",  
          as: "emp",  
          cond: { $gt: ["$$emp.sal", 35000] }  
        }  
      }  
    }  
  }  
])

The paper argues that functions should be applied to relations, not columns:

SELECT dno, COUNT(Emps)
FROM Company;

SELECT dno
FROM Company
WHERE COUNT(Emps) > 10;

This is exactly how MongoDB's aggregation works:

db.company.aggregate([
  {
    $project: {
      dno: 1,
      emp_count: { $size: "$Emps" }
    }
  }
])

db.company.aggregate([
  {
    $match: {
      $expr: { $gt: [{ $size: "$Emps" }, 10] }
    }
  },
  { $project: { dno: 1 } }
])

The paper makes a profound observation: GROUP BY creates a ¬1NF structure (partitioned relations) with special rules, and is unnecessary when you already have nested relations.

In SQL on a 1NF schema the following gets the departements with more than ten employees:

SELECT dno
FROM Dept
WHERE dno IN (
  SELECT dno FROM Emp
  GROUP BY dno
  HAVING COUNT(DISTINCT eno) > 10
)

SQL/NF on a ¬1NF where all is nested, the query is simpler:

SELECT dno
FROM Company
WHERE COUNT(Emps) > 10

In MongoDB, when data is already nested, no $group is needed:

db.company.find(
  { $expr: { $gt: [{ $size: "$Emps" }, 10] } },
  { dno: 1 }
)

This is one of MongoDB's core value propositions: by embedding related data, many queries that require GROUP BY in SQL become trivial reads.

SQL/NF introduce NEST and UNNEST:

NEST Emp ON eno, ename, sal AS Emps;

UNNEST Company ON Emps;

We have seen that the equivalent in MongoDB are $group and $unwind:

db.emp.aggregate([
  {
    $group: {
      _id: "$dno",
      Emps: {
        $push: { eno: "$eno", ename: "$ename", sal: "$sal" }
      }
    }
  }
])

db.company.aggregate([
  { $unwind: "$Emps" }
])

The paper introduces the concept that modifying nested data with with SQL/NF is a MODIFY on the outer document:

-- Insert a new employee into department 10
MODIFY Company
SET Emps = (STORE Emps VALUES <32, Samuels, 49000>)
WHERE dno = 10

-- Give 10% raise to dept 10 employees making >30K
MODIFY Company
SET Emps = (MODIFY Emps SET sal = sal * 1.1 WHERE sal > 30000)
WHERE dno = 10

MongoDB uses $push to insert in a nested array:

// Insert new employee into department 10
db.company.updateOne(
  { dno: 10 },
  { $push: { Emps: { eno: 32, ename: "Samuels", sal: 49000 } } }
)

MongoDB uses $arrayFilters to target an array item:

// 10% raise for dept 10 employees making > 30K
db.company.updateOne(
  { dno: 10 },
  { $mul: { "Emps.$[elem].sal": 1.1 } },
  { arrayFilters: [{ "elem.sal": { $gt: 30000 } }] }
)

The paper also defined SQL/NF Data Definition Language (DDL):

SCHEMA
  TABLE Company
    ITEM dno INTEGER UNIQUE NOT NULL
    ITEM dname CHARACTER 10
    ITEM loc CHARACTER 10
    ITEM (TABLE Emps
      ITEM eno INTEGER UNIQUE
      ITEM ename CHARACTER 10
      ITEM sal REAL)

While MongoDB doesn't require to declare the schema upfront, to allow more agility when building an application, it provides schema validation to enforce the schema when stable:

db.createCollection("company", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["dno"],
      properties: {
        dno: {
          bsonType: "int",
          description: "department number — required, unique via index"
        },
        dname: {
          bsonType: "string",
          maxLength: 10
        },
        loc: {
          bsonType: "string",
          maxLength: 10
        },
        Emps: {
          bsonType: "array",
          items: {
            bsonType: "object",
            properties: {
              eno: { bsonType: "int" },
              ename: { bsonType: "string", maxLength: 10 },
              sal: { bsonType: "double" }
            }
          }
        }
      }
    }
  }
})

// PNF enforcement via unique index:
db.company.createIndex({ dno: 1 }, { unique: true })

To avoid an additional index, you can use "_id" instead of "dno".

The paper defines "don't care values" and pattern matching in SQL/NF:

-- Find company tuples where an employee is named Smith with sal 20000
Company WHERE <?, "Smith", 20000> IN Emps

In MongoDB, $elemMatch is the "don't care" pattern match:

db.company.find({
  Emps: {
    $elemMatch: {
      // eno: ? — not specified (don't care)
      ename: "Smith",
      sal: 20000
    }
  }
})

SQL/NF uses reference names and aliasing:

SELECT dname, (Emps WHERE sal > 35000) AS Emps_rich
FROM Company
WHERE EXISTS(Emps_rich)

In MongoDB we use $let or $addFields:

db.company.aggregate([
  {
    $addFields: {
      Emps_rich: {
        $filter: {
          input: "$Emps",
          as: "emp",
          cond: { $gt: ["$$emp.sal", 35000] }
        }
      }
    }
  },
  { $match: { "Emps_rich.0": { $exists: true } } },
  { $project: { dname: 1, Emps_rich: 1 } }
])

¬1NF applications

The paper shows some examples of applications models in non-first normal forms and here are the equivalent in MongoDB.

The paper shows an example of office Forms, like invoices:

Here is the MongoDB document:

db.invoices.insertMany([
  {
    cname: "Smith",
    caddress: "Chicago",
    total: 80.00,
    Orders: [
      { prod_no: 102, qty: 10, price: 1.30, amount: 13.00 },
      { prod_no: 210, qty: 1, price: 67.00, amount: 67.00 }
    ]
  },
  {
    cname: "West",
    caddress: "Auburn",
    total: 20.80,
    Orders: [
      { prod_no: 102, qty: 5, price: 1.30, amount: 6.50 },
      { prod_no: 213, qty: 43, price: 0.10, amount: 4.30 },
      { prod_no: 456, qty: 10, price: 1.00, amount: 10.00 }
    ]
  }
])

The paper shows a circuit design requiring 6 tables in 1NF:

It is a single table in ¬1NF:

MongoDB does the same in one document:

db.entities.insertMany([
  {
    id: 100,
    name: "2-AND",
    Geometry: [
      { x1: 0, y1: 0, x2: 0, y2: 10 },
      { x1: 0, y1: 10, x2: 10, y2: 10 },
      { x1: 10, y1: 10, x2: 10, y2: 0 },
      { x1: 10, y1: 0, x2: 0, y2: 0 }
    ],
    Pins: [
      { no: 1, class: "IN", x: 0, y: 3 },
      { no: 2, class: "IN", x: 0, y: 7 },
      { no: 3, class: "OUT", x: 10, y: 5 }
    ],
    Blocks: [],
    Connections: []
  },
  {
    id: 200,
    name: "4-AND",
    Geometry: [
      { x1: 0, y1: 0, x2: 0, y2: 50 },
      { x1: 0, y1: 50, x2: 50, y2: 50 },
      { x1: 50, y1: 50, x2: 50, y2: 0 },
      { x1: 50, y1: 0, x2: 0, y2: 0 }
    ],
    Pins: [
      { no: 1, class: "IN", x: 0, y: 13 },
      { no: 2, class: "IN", x: 0, y: 17 },
      { no: 3, class: "IN", x: 0, y: 33 },
      { no: 4, class: "IN", x: 0, y: 37 },
      { no: 5, class: "OUT", x: 50, y: 25 }
    ],
    Blocks: [
      { no: 1, type: 100, x: 10, y: 10 },
      { no: 2, type: 100, x: 10, y: 30 },
      { no: 3, type: 100, x: 30, y: 20 }
    ],
    Connections: [
      { cno: 1, block1: 0, pin1: 1, block2: 1, pin2: 1, segments: [] },
      { cno: 2, block1: 0, pin1: 2, block2: 1, pin2: 2, segments: [] },
      { cno: 6, block1: 1, pin1: 3, block2: 3, pin2: 1,
        segments: [{ x: 25, y: 15 }, { x: 25, y: 23 }] },
      { cno: 7, block1: 2, pin1: 3, block2: 3, pin2: 2,
        segments: [{ x: 25, y: 35 }, { x: 25, y: 27 }] }
    ]
  }
])

Complex queries on this structure can use a single multi-key index, and a single read with no joins.

The paper shows an example of statistical databases:

Here is the MongoDB document for it:

db.salary_summary.insertMany([
  {
    age_group: "[18,30]",
    divisions: [
      {
        div: "div1",
        departments: [
          { dept: "man", sum_sal: "100K" },
          { dept: "personnel", sum_sal: "150K" }
        ]
      },
      {
        div: "div2",
        departments: [
          { dept: "acct", sum_sal: "290K" }
        ]
      }
    ]
  },
  {
    age_group: "[31,40]",
    divisions: [
      {
        div: "div1",
        departments: [
          { dept: "man", sum_sal: "200K" },
          { dept: "personnel", sum_sal: "300K" }
        ]
      },
      {
        div: "div2",
        departments: [
          { dept: "acct", sum_sal: "400K" }
        ]
      }
    ]
  }
])

Nested Normal Form (NNF)

The paper presents a method for achieving nested normal form (NNF) that removes anomalies from partial and transitive dependencies in PNF relations. It differs from prior algorithms by deriving non-1NF relations from an initial 4NF decomposition, incorporating embedded multivalued dependencies, and improving how functional dependencies are handled.

The paper's NNF algorithm starts with functional and multivalued dependencies and builds scheme trees that minimize redundancy. This is essentially MongoDB schema design. Here are a few examples.

In example 9.1, the employee determines the location, so instead of an sigleton array:

{
  employee: "Smith",
  Locations: ["Austin"],
  Skills: ["typing", "filing"],
  Children: ["Sam", "Sue"]
}

we use a scalar:

{
  employee: "Smith",
  location: "Austin",
  Skills: ["typing", "filing"],
  Children: ["Sam", "Sue"]
}

Queries and indexing work the same, but it’s preferable to use a scalar for one-to-one relationships and an array for one-to-many. With polymorphism, the schema is per document, so a collection can hold both.

In example 9.3, the scouting database, the paper's final design is three scheme trees:

This is three collections in MongoDB:

// Collection 1: Boy-Girl activities
db.activities.insertMany([
  {
    boy: "Tom",
    girl: "Jane",
    Dates: ["2024-01-15", "2024-02-20"],
    Dances: ["2024-03-01"]
  },
  {
    boy: "Tom",
    girl: "Lisa",
    Dates: ["2024-01-22"],
    Dances: []
  }
])

// Collection 2: Boy scout leaders and their boys
db.bsl_boys.insertMany([
  {
    bsl: "Mr. Adams",
    Boys: ["Tom", "Jerry", "Mike"]
  }
])

// Collection 3: Girl scout leaders and their girls
db.gsl_girls.insertMany([
  {
    gsl: "Ms. Baker",
    Girls: ["Jane", "Lisa", "Sara"]
  }
])

Normalization still applies in MongoDB to clearly depict the intended relationships. There is no referential integrity ("foreign keys") between documents. This preserve the decoupling of scout activities and scout leadership. If all scouts involved in an activity must have a leader, this must be enforced by the application, or may be checked asynchronously by an application pipeline with $lookup.

In example 9.5, the university database, the tutor's office is redundant:

{
  class: "CS101",
  day: "Monday",
  students: [
    { student: "Alice", major: "CS", tutor: "Prof X", office: "Room 101", exam: "A" },
    { student: "Bob", major: "Math", tutor: "Prof X", office: "Room 101", exam: "B" }
    //                                                  ^^^^^^^^^^^^^^^^ redundant!
  ]
}

The further normalization produces three collections to separate the functional dependencies groups:

// Collection 1: Class schedule (Class → Day, then Students and Tutors nested)
db.classes.insertOne({
  class: "CS101",
  day: "Monday",
  Students: [
    { student: "Alice", exam: "A" },
    { student: "Bob", exam: "B" }
  ],
  Tutors: [
    { tutor: "Prof X", hour: "10am" },
    { tutor: "Prof Y", hour: "2pm" }
  ]
})

// Collection 2: Student majors (Student → Major, separate because it's independent)
db.students.insertMany([
  { student: "Alice", major: "CS" },
  { student: "Bob", major: "Math" }
])

// Collection 3: Tutor offices (Tutor → Office, separate for same reason)
db.tutors.insertMany([
  { tutor: "Prof X", office: "Room 101" },
  { tutor: "Prof Y", office: "Room 205" }
])

This is exactly the MongoDB schema design principle: embed data that is naturally "owned" by the parent document, but reference data that has independent identity and would be redundantly repeated.

This level of normalization is not always necessary, and you may accept some redundancy for performance reasons. The initial design, where the tutor’s office is repeated for all students in a class, is acceptable if there is clear ownership in the application and it can run an updateMany() to update all related records in a single transaction.

Summary

Normalization exists on a non-1NF schema and relational operations as well. Functional dependencies are enforced with schema validation and unique index. Multi-valued dependencies can be modeled as separate arrays in the same document. Join dependencies may use embedding or separate collections.

Here is a summary of the mapping between the paper's concepts and MongoDB:

Paper Concept	MongoDB Equivalent
¬1NF Relation	Collection of documents with embedded arrays/documents
Relation scheme / Database scheme	JSON Schema / Collection structure
Zero-order attribute	Scalar field
Higher-order attribute	Embedded array or sub-document
Nest (ν)	`$group` with `$push` / `$addToSet`
Unnest (μ)	`$unwind`
Unnest* (μ*)	Multiple `$unwind` stages
PNF	Unique `_id` / unique indexes; one document per entity
Extended union (∪ᵉ)	`$addToSet`, `$setUnion`, `updateOne` with `$push`
Extended intersection (∩ᵉ)	`$setIntersection`
Extended difference (−ᵉ)	`$setDifference`
Extended join (⋈ᵉ)	`$lookup` + `$setIntersection` on common nested fields
Extended projection (πᵉ)	`$project` + `$group` with `$setUnion`
Selection (σ)	`$match`, `find()`
SQL/NF nested SELECT	`$filter`, `$map` in `$project`
SQL/NF functions on relations	`$size`, `$sum`, `$avg` on arrays
GROUP BY elimination	Natural consequence of document model
NEST ON	`$group` in aggregation
UNNEST ON	`$unwind` in aggregation
NULL / ni	`null`, missing fields
Empty nested relation	Empty array `[]`
`preserveNullAndArrays`	Paper's requirement that unnesting empty sets preserves tuples
dne (does not exist)	Field absent
Outer join	`$lookup` (preserves unmatched documents)
SUBSUME	No built-in; implementable with aggregation
NNF design	MongoDB schema design best practices
FD → scalar field	"Embed as scalar, not array, for 1:1 relationships"
MVD → separate arrays	"Use separate arrays for independent multi-valued attributes"
Scheme trees	Document structure hierarchy
4NF decomposition → collections	"When to embed vs. reference" decision
Reference names / aliasing	`$addFields`, `$let`, `as` in `$lookup`
Don't-care values	`$elemMatch` (match on specified fields, ignore others)
Recursive schemes (future work)	`$graphLookup` (added later to MongoDB)

Roth's 1986 dissertation effectively laid the theoretical foundation for document databases—23 years before MongoDB. The parallels are striking:

Data model: Documents with embedded document arrays correspond to ¬1NF relations with nested relations.
Query language: SQL/NF's orthogonality principle ("wherever a scalar can appear, a relation can appear") matches MongoDB's rule that array operations are available wherever scalar operations exist.
Schema design: NNF maps to MongoDB's embedding-vs-referencing rules: embed for MVDs (independent multi-valued relationships), use scalar fields for FDs (1:1 relationships), and split into collections when data has its own identity.
Operators: $unwind (unnest), $group (nest), and $setUnion / $setIntersection / $setDifference (extended set operators) all match what the paper anticipated.
Null handling: Options like preserveNullAndArrays implement the paper's recommendation that empty nested relations should not cause information loss during unnesting.

Where MongoDB goes beyond the paper is in recursive structures, which Roth explicitly left as future work. $graphLookup supports the recursive schema traversal the dissertation identified as an open problem.

MongoDB Transaction Performance

Franck Pachot — Tue, 24 Mar 2026 22:32:26 +0000

In MongoDB, all data manipulation operations are transactional and ACID. Single-document operations (insert, update, delete) use internal transactions via the WriteUnitOfWork and RecoveryUnit interfaces, ensuring ACID properties for each document change and its index entries.

In MongoDB, the term transaction refers specifically to multi-document transactions, which provide atomicity across multiple documents and collections via sessions. Their performance differs from single-document operations, but they are not clearly faster or slower:

Atomicity: Multi-document transactions use extra memory to track uncommitted changes and maintain transaction state across operations.
Consistency: Both single-document and multi-document operations enforce index updates and schema validation at write time.
Isolation: Multi-document transactions offer snapshot isolation using optimistic concurrency control, which can lead to write conflicts. When conflicts occur, MongoDB labels the error as TransientTransactionError — clients should handle this with retry logic and exponential backoff, as described in a previous post.
Durability: Multi-document transactions batch changes into fewer oplog entries, so they can reduce latency compared to multiple single-document transactions.

Example: Inserting One Million Documents

Here is an example on a MongoDB Atlas free cluster where I insert one million documents with a single insertMany() call:

db.test.drop();
const start = new Date();
// Insert a million documents
const res = db.test.insertMany(
  Array.from({ length: 1e6 }, (_, i) => ({
    name: `user_${i}`,
    value: Math.random(),
  }))
);
// Show timing
const elapsed = new Date() - start;
print(`Elapsed: ${elapsed} ms`);

On a free MongoDB Atlas cluster, this operation takes Elapsed: 53025 ms. Although it's a single call, ACID properties apply per document — each document is its own unit of consistency, much like an aggregate in domain-driven design. This means another session can see some inserted documents before the operation completes.

If we want the inserted documents to be visible only when they are all there, we can run the same operation in a transaction:

db.test.drop();
// Start a transaction in a session
const se = db.getMongo().startSession();
const sessionDb = se.getDatabase(db.getName());
const start = new Date();
se.startTransaction();
// Insert a million documents
const res = sessionDb.test.insertMany(
  Array.from({ length: 1e6 }, (_, i) => ({
    name: `user_${i}`,
    value: Math.random(),
  }))
);
// Commit and show timing
se.commitTransaction();
const elapsed = new Date() - start;
print(`Elapsed: ${elapsed} ms`);
se.endSession();

On a MongoDB Atlas free cluster, this takes Elapsed: 49047 ms, which is slightly faster. Note that this approaches the default transactionLifetimeLimitSeconds of 60 seconds — larger operations or slower clusters should adjust this parameter to avoid hitting the limit. MongoDB is optimized for OLTP with short transactions — it’s better to fail than to wait an unpredictable amount of time.

You might expect a larger speedup from batched durability, but single-document transactions are already highly optimized. MongoDB can piggyback multiple inserts into a single applyOps oplog entry, and WiredTiger batches log flushes so multiple transactions can share a single disk sync. For j: true and w: majority (the defaults), MongoDB often triggers a journal flush without waiting, piggybacking on replication acknowledgment to ensure durability.

When you hear that transactions are slow, remember this is usually a misunderstanding. Use transactions as your application requires, based on its atomicity, consistency, isolation, and durability boundaries. Performance depends on whether documents are on the same shard or the transaction spans multiple shards. Transactions aren’t inherently slow—the document model is tuned to the domain model’s consistency boundaries, favoring single-document transactions.

Updating "denormalized" aggregates with "duplicates": MongoDB vs. PostgreSQL

Franck Pachot — Tue, 03 Mar 2026 10:26:05 +0000

TL;DR: In database-centric development, duplication is dangerous because, in shared, normalized relational databases, it leads to update anomalies. In contrast, in DDD, aggregates own their data, so duplication is intentional and updates are controlled by the application. MongoDB supports this approach with fine-grained updates on document arrays and fields, with updateMany, $set, and arrayFilters, while PostgreSQL JSONB often requires rewriting entire documents, which can make normalization more appealing. As a result, duplication itself is not the core problem—what matters is who owns the data and how updates are optimized.

In Domain-Driven Design (DDD), aggregates define consistency boundaries, leading to an intentionally denormalized model. In a relational database, this can feel counterintuitive due to concerns over data duplication and update anomalies. In a shared SQL database with direct/unrestricted access (accepting ad-hoc SQL queries across the entire schema), normalization is often preferred to guarantee a single source of truth for each piece of data and to rely on joins/dynamic lookups at query time.

DDD addresses these concerns through the concept of ownership: each aggregate owns its data, and any duplication is deliberate and controlled. Updates occur only through the aggregate root (or the coordinating application service), whose behavior is tested and reviewed before deployment to production. The database is never modified directly from outside this boundary. As a result, changes are propagated safely — often via domain events and eventual consistency — ensuring the value is updated in all necessary places without violating aggregate boundaries. In a bounded context, a single update command can modify many occurrences in a collection of aggregates, but the performance depends on the capability of the database to update within the aggregate.

Example: player's scores

Consider 100,000 game records, each containing 1,000 players and their scores. Each score stores the player’s name directly instead of referencing a separate player document. When a player is renamed—a rare event—the name must be updated in all affected documents with a single updateMany() operation. This is more work than updating a single record, but it removes an extra lookup for every read, which happens far more often, and better matches the aggregate’s read and consistency requirements.

Documents in MongoDB

I built the following dataset:


db.scores.drop()

db.scores.createIndex({ "players.name": 1 })
db.scores.createIndex({ "players.score": 1 })

async function init(docs,players,totalplayers=10000,batchsize=5000){
for (let c = 0; c < docs ; c += batchsize) {
  await db.scores.insertMany(
    Array.from({ length: batchsize }, () => ({
      players: Array.from({ length: players }, (_, i) => ({
        name: `player${Math.round(totalplayers*Math.random())}`,
        score: Math.random()
      }))
    }))
  );
  print(`${db.scores.countDocuments()} documents (with ${players} in array)}`);
  }
}

init(1e5,1e3)
;

In a normalized relational model, this scenario would use a many-to-many schema: a games table with 100,000 rows, a players table with 10,000 rows, and an association table with 100,000,000 rows. That’s the trade-off for storing each player’s name only once.

Instead, with a single collection, I stored 100,000 documents, each embedding 1,000 scores together with the player’s names:


db.scores.countDocuments();

100000

I created two indexes on fields in the array to show the impact of updates. Each document has 1,000 keys, giving a total of 100,000,000 keys per index:


db.scores.validate({full: true}).keysPerIndex;

{
  _id_: 100000,
  'players.name_1': 95165332,
  'players.score_1': 100000000
}

There are fewer than 100,000,000 keys for "name" because they were generated randomly, and some are duplicated within a document (for example, one player may have multiple scores). These duplicates are removed at the index key level.

Below are the index sizes in MB, each index is a few GB:

db.scores.stats(1024 * 1024).indexSizes;

{
  _id_: 2.1640625,
  'players.name_1': 2216.81640625,
  'players.score_1': 3611.44140625
}

I created two indexes to demonstrate that, in MongoDB’s document storage, updating array elements by deleting and reinserting them with new values does not add overhead to other fields, even when those fields are indexed.

A single player, "player42," has a recorded score in 9.5% of the documents:

db.scores.countDocuments({"players.name":"player42"});

9548

I want to rename "player42" so the new name appears in all historical records, as if it had been normalized to a single value.

UpdateMany() in MongoDB

I can change the name "player42" to "Franck" using a single updateMany() operation on the collection:

db.scores.updateMany(  
  { "players.name": "player42" },  
  { $set: { "players.$[i].name": "Franck" } },  
  { arrayFilters: [{ "i.name": "player42" }] }  
);

This means:

Select all documents that contain at least one element in the players array with name equal to "player42".
For each of these documents, consider every players element whose name is "player42" as i.
For each such element i, update i.name to "Franck".

In most cases, it’s fine if this update isn’t atomic. If atomicity is required, you should execute it within a transaction. It is also faster within a transaction, as long as the number of updates is bounded, as it has to be synced to disk only once at commit.

I executed it in a transaction and gathered statistics both before and after the transaction:


function updatePlayer42() { 

  // gather some stats
  const bstat_name = db.scores.stats({ indexDetails: true }).indexDetails["players.name_1"].cursor;
  const bstat_score = db.scores.stats({ indexDetails: true }).indexDetails["players.score_1"].cursor;
  const btime = new Date()
  print(`Start: ${btime}\n Index size:${JSON.stringify(db.scores.stats(1024 * 1024).indexSizes)} MB`);

  // ---- transaction start ----  
  const session = db.getMongo().startSession();  
  const scores = session.getDatabase(db.getName()).scores;  

  try {  

    session.startTransaction();  

    const res = scores.updateMany(  
      { "players.name": "player42" },  
      { $set: { "players.$[m].name": "Franck" } },  
      { arrayFilters: [{ "m.name": "player42" }] }  
    );  

    printjson(res);  

    session.commitTransaction();  

  } catch (e) {  
    print("Transaction aborted:", e);  
    session.abortTransaction();  
  } finally {  
    session.endSession();  
  }  
  // ---- transaction end ----  

  // gather and print stats
  const etime = new Date()
  const estat_name = db.scores.stats({ indexDetails: true }).indexDetails["players.name_1"].cursor;
  const estat_score = db.scores.stats({ indexDetails: true }).indexDetails["players.score_1"].cursor;

  // calculate and print stats
  print(`End: ${etime} - duration: ${ etime-btime} ms\n Index size:${JSON.stringify(db.scores.stats(1024 * 1024).indexSizes)} MB`);
  print(`Index on "players.name" cursor stats:`)
 Object.entries(estat_name).forEach(([k, v]) => {  const delta = v - (bstat_name[k] ?? 0);  if (delta !== 0) print(` ${k}: ${delta}`);  });
  print(`Index on "players.score" cursor stats:`)
 Object.entries(estat_score).forEach(([k, v]) => {  const delta = v - (bstat_score[k] ?? 0);  if (delta !== 0) print(` ${k}: ${delta}`);  });

}

updatePlayer42()

Here is the output:

Start: Mon Mar 02 2026 22:51:31 GMT+0000 (Coordinated Universal Time)
 Index size:{"_id_":2.7734375,"players.name_1":2216.81640625,"players.score_1":3611.44140625} MB
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 9548,
  modifiedCount: 9548,
  upsertedCount: 0
}
End: Mon Mar 02 2026 22:51:43 GMT+0000 (Coordinated Universal Time) - duration: 12472 ms
 Index size:{"_id_":2.7734375, "players.name_1":2216.81640625, "players.score_1":3611.44140625} MB
Index on "players.name" cursor stats:
 cache cursors reuse count: 1
 close calls that result in cache: 2
 create calls: 1
 cursor bounds cleared from reset: 9549
 cursor bounds comparisons performed: 9549
 cursor bounds next called on an unpositioned cursor: 9549
 cursor bounds next early exit: 1
 insert calls: 9548
 insert key and value bytes: 114477
 next calls: 9549
 remove calls: 9548
 remove key bytes removed: 133573
 reset calls: 28648
Index on "players.score" cursor stats:

The statistics show only those that increased and display the difference.

In 12 seconds, this operation atomically updated the name of "player42" in 9,548 documents, out of a total of 100,000 large documents, and the indexes were maintained with strong consistency. The overall index size did not increase significantly. The index on "score" was unchanged. The index on "name" received 9,548 new entries (12 bytes on average) and removed 9,548 entries (14 bytes on average).

Although this is slower than performing a single update in a reference table, it is nowhere near the kind of update nightmare that SQL practitioners typically associate with denormalization. The crucial point is that only the index entries related to the updated array element and field must be maintained. To understand where the fear comes from, let's do the same in a SQL database.

Comparison with PostgreSQL and JSONB

SQL databases were designed for normalisation, and even if they accept some JSON datatypes, they may not have the same optimisations, and such an update is much more expensive.

I created a similar dataset in PostgreSQL 18 with JSONB:


CREATE TABLE scores (  
  id     bigint PRIMARY KEY,  
  doc    jsonb  
)
;

INSERT INTO scores (id, doc)  
SELECT  
  g,  
  jsonb_build_object(  
    'players',  
    jsonb_agg(  
      jsonb_build_object(  
        'name',  'player' || ((g * 1000 + s) % 10000),  
        'score', random()  
      )  
    )  
  )  
FROM generate_series(1, 100000) AS g  
CROSS JOIN generate_series(1, 1000) AS s  
GROUP BY g;  

SELECT count(*) FROM scores 
 WHERE doc @? '$.players[*] ? (@.name == "player42")'
;

CREATE INDEX ON scores 
 USING gin ((doc->'players') jsonb_path_ops)
;

VACUUM ANALYZE scores
;

A single GIN index can cover equality filters (or rather containment) on the two fields.

I executed the same update, using @? in a WHERE clause to retrieve the documents to update via the GIN index, and jsonb_set to modify the name. There's no direct access to the JSONB content stored in the table, and it must read the JSON array with jsonb_array_elements and rebuild it with jsonb_agg:

EXPLAIN (ANALYZE, BUFFERS, WAL, COSTS OFF)
UPDATE scores     
SET doc = (    
  SELECT jsonb_agg(    
    CASE     
      WHEN p->>'name' = 'player42'     
      THEN jsonb_set(p, '{name}', '"Franck"')    
      ELSE p     
    END    
  )    
  FROM jsonb_array_elements(doc->'players') p    
)    
WHERE (doc->'players') @? '$[*] ? (@.name == "player42")'
;

Here is the execution plan:

                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Update on scores (actual time=13126.454..13126.459 rows=0.00 loops=1)
   Buffers: shared hit=641088 read=39252 dirtied=57413 written=57455
   WAL: records=310285 fpi=31129 bytes=406743374 buffers full=33593
   ->  Bitmap Heap Scan on scores (actual time=6.333..7907.504 rows=10000.00 loops=1)
         Recheck Cond: ((doc -> 'players'::text) @? '$[*]?(@."name" == "player42")'::jsonpath)
         Heap Blocks: exact=736
         Buffers: shared hit=88347 read=32397 written=20050
         ->  Bitmap Index Scan on scores_expr_idx (actual time=3.491..3.491 rows=10000.00 loops=1)
               Index Cond: ((doc -> 'players'::text) @? '$[*]?(@."name" == "player42")'::jsonpath)
               Index Searches: 1
               Buffers: shared hit=1 read=7
         SubPlan 1
           ->  Aggregate (actual time=0.519..0.519 rows=1.00 loops=10000)
                 Buffers: shared hit=60000
                 ->  Function Scan on jsonb_array_elements p (actual time=0.060..0.091 rows=1000.00 loops=10000)
                       Buffers: shared hit=60000
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.647 ms
 Execution Time: 13126.742 ms
(20 rows)

To update 10,000 documents, PostgreSQL generated 641,088 WAL records (~64 per document) and wrote 57,413 blocks (~46 KB per document). This is less than rewriting the full raw JSON, thanks to binary internal representation and TOAST compression, but still far larger than the logical change—a single field update. PostgreSQL JSONB makes denormalization workable but write‑amplified when duplicated values must be updated, not because they are duplicated, but because they reside in large, immutable JSONB documents.

Comparison with Oracle Database and OSON

In order to test with another binary JSON implementation on SQL database, I've run the same on Oracle Autonomous 26ai, though the MongoDB emulation proxy, except the lines collecting the statistics, as they are not provided, but looked at the performance through the performance advisor on a window where 5000 documents were updated:

ASH Analytics shows that most of the time is spent on random reads on the multi-value indexes:

Execution statistics show that it executes row-by-row and each update of a document is a single update statement that replaces the whole document: update "ORA"."scores" set "DATA" = :1 where ("RESID" = :2 ) returning "RESID", "ETAG" into :3 , :4:

A document model that stores aggregates as JSON—despite data duplication and possible updateMany() calls—is justified only when the storage layer is optimized for documents, such as MongoDB with BSON in WiredTiger. An RDBMS that emulates updateMany() on storage designed for relational models may not offer the same optimizations and can make such updates problematic. SQL databases, even with a binary JSON type, are generally optimized for normalization and joins rather than document-centric operations.

Of course, one could think it is a good case for JSON-relational duality views, as it can store the embedded array in another table. I tried, but arrayFilters option is not supported on duality view collections.

This explains why SQL experts are afraid of denormalization when storing DDD aggregates as documents. In SQL databases, a normalized schema is generally preferable. Always review the execution plan when assessing the scalability of your design.

Conclusion

In traditional relational databases, avoiding duplication is essential because the database is the main authority for correctness: every application, report, and ad‑hoc SQL query must respect the same invariants. In that context, normalization and foreign keys are the safest way to prevent inconsistencies.

In a Domain‑Driven Design (DDD) architecture, that responsibility shifts. Aggregates define consistency boundaries, a single trusted service mediates database access, and the application enforces invariants. Duplication is therefore intentional and bounded, not accidental.

The experiments above show that this difference is both physical and conceptual. In MongoDB, updating a value embedded in an array changes only the affected elements and their index entries. Index maintenance scales with the number of modified elements, not with overall document size, so even highly denormalized aggregates can still be updated efficiently and atomically.

In PostgreSQL, JSONB supports denormalized structures but with very different update semantics. Any change requires rebuilding the entire JSON value and regenerating all related index entries, regardless of how small the logical update is. Indexes help find the rows, but cannot avoid the cost of rewriting the document.

As a result, the trade‑off is clear: PostgreSQL JSONB–based denormalization mainly optimizes reads while imposing a write cost proportional to document size, whereas MongoDB’s document model supports both read locality and fine‑grained, efficient updates within aggregates. The issue is not whether denormalization is “good” or “bad,” but whether the database’s storage and indexing model fits the aggregate’s read‑write patterns and ownership assumptions.

PostgreSQL global statistics on partitionned table require a manual ANALYZE

Franck Pachot — Sun, 01 Mar 2026 17:55:23 +0000

PostgreSQL auto-analyze collects statistics on tables with rows. For partitioned tables, it excludes the parent as it has no rows by itself. So how does the query planner estimate cardinality when a query spans multiple partitions?

Some statistics are easy to derive: if it knows each partition’s row count, the global count is the total. Column statistics are trickier, especially with the number of distinct values, a key factor to estimate cardinalities with predicates or aggregates. Even with the number of distinct values per partition, it still doesn’t know how much those values overlap across partitions. The global distinct count therefore lies between the maximum per-partition distinct count and the sum of all per-partition counts.

Here is an example:


create table history (
   year int,
   num serial,
   x   int,
   y   int,
   primary key (year, num)
) partition by list (year)
;

create table history_2024 partition of history for values in (2024);
create table history_2025 partition of history for values in (2025);
create table history_2026 partition of history for values in (2026);
create table history_2027 partition of history for values in (2027);

insert into history select 
 extract(year from ( date '2026-01-02' - interval '1 minute' * num ))::int as year
 ,num             -- NDV ≈ rows (unique key, density ≈ 1 / rows)
 ,(num % 2) as x  -- NDV = 2 per partition (very high density: ~50% per value)  
 ,(num / 2) as y  -- NDV ≈ rows / 2 per partition (moderate density: ~2 rows per distinct value) 
 from generate_series(1,1e6) num
;

Here is the real data distribution:

select count(*), year
  , count(distinct x) as "distinct x"
  , min(x) as "min x"
  , max(x) as "max x"
  , count(*)::float / nullif(count(distinct x), 0) as density_x
  , count(distinct y) as "distinct y"
  , min(y) as "min y"
  , max(y) as "max y"
  , count(*)::float / nullif(count(distinct y), 0) as density_y
 from history group by grouping sets ((),year)
;

  count  | year | distinct x | min x | max x | density_x | distinct y | min y  | max y  | density_y
---------+------+------------+-------+-------+-----------+------------+--------+--------+-----------
  472960 | 2024 |          2 |     0 |     1 |    236480 |     236481 | 263520 | 500000 | 1.999991
  525600 | 2025 |          2 |     0 |     1 |    262800 |     262801 |    720 | 263520 | 1.999992
    1440 | 2026 |          2 |     0 |     1 |       720 |        721 |      0 |    720 | 1.997226
 1000000 |      |          2 |     0 |     1 |    500000 |     500001 |      0 | 500000 | 1.999996

(4 rows)

Immediately after the insert, there's no statistics:

select relname, relpages, reltuples, relkind, relhassubclass, relispopulated, relispartition  
 from pg_class where relname like 'history%' order by relkind desc, relname
;

      relname      | relpages | reltuples | relkind | relhassubclass | relispopulated | relispartition
-------------------+----------+-----------+---------+----------------+----------------+----------------
 history_2024      |        0 |        -1 | r       | f              | t              | t
 history_2025      |        0 |        -1 | r       | f              | t              | t
 history_2026      |        0 |        -1 | r       | f              | t              | t
 history_2027      |        0 |        -1 | r       | f              | t              | t
 history           |        0 |        -1 | p       | t              | t              | f
 history_2024_pkey |        1 |         0 | i       | f              | t              | t
 history_2025_pkey |        1 |         0 | i       | f              | t              | t
 history_2026_pkey |        1 |         0 | i       | f              | t              | t
 history_2027_pkey |        1 |         0 | i       | f              | t              | t
 history_num_seq   |        1 |         1 | S       | f              | t              | f
 history_pkey      |        0 |         0 | I       | t              | t              | f
(11 rows)

After a while, autovacuum’s auto-analyze has gathered statistics for the partitions:

select relname, relpages, reltuples, relkind, relhassubclass, relispopulated, relispartition  
 from pg_class where relname like 'history%' order by relkind desc, relname
;

      relname      | relpages | reltuples | relkind | relhassubclass | relispopulated | relispartition
-------------------+----------+-----------+---------+----------------+----------------+----------------
 history_2024      |     2557 |    472960 | r       | f              | t              | t
 history_2025      |     2842 |    525600 | r       | f              | t              | t
 history_2026      |        8 |      1440 | r       | f              | t              | t
 history_2027      |        0 |        -1 | r       | f              | t              | t
 history           |        0 |        -1 | p       | t              | t              | f
 history_2024_pkey |     1300 |    472960 | i       | f              | t              | t
 history_2025_pkey |     1443 |    525600 | i       | f              | t              | t
 history_2026_pkey |        6 |      1440 | i       | f              | t              | t
 history_2027_pkey |        1 |         0 | i       | f              | t              | t
 history_num_seq   |        1 |         1 | S       | f              | t              | f
 history_pkey      |        0 |         0 | I       | t              | t              | f
(11 rows)

However, there are still no global statistics (the "history" table itself). The available column statistics only cover partitions and include the number of distinct values for the two columns:

select tablename, attname, n_distinct, null_frac
 from pg_stats where tablename like 'history%'
;
  tablename   | attname | n_distinct  | null_frac
--------------+---------+-------------+-----------
 history_2025 | year    |           1 |         0
 history_2025 | num     |          -1 |         0
 history_2025 | x       |           2 |         0
 history_2025 | y       | -0.49914953 |         0
 history_2024 | year    |           1 |         0
 history_2024 | num     |          -1 |         0
 history_2024 | x       |           2 |         0
 history_2024 | y       |  -0.4907878 |         0
 history_2026 | year    |           1 |         0
 history_2026 | num     |          -1 |         0
 history_2026 | x       |           2 |         0
 history_2026 | y       | -0.50069445 |         0
(12 rows)

For queries spanning multiple partitions, the optimizer gets good cardinality estimates per partition. However, SELECT DISTINCT over multiple partitions cannot be estimated from the statistics.

For column x it estimates to rows=200 instead of rows=2.00:

explain (analyze, buffers off, summary off)
 select distinct x from history
;
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=22949.38..22951.38 rows=200 width=4) (actual time=263.739..263.742 rows=2.00 loops=1)
   Group Key: history.x
   Batches: 1  Memory Usage: 32kB
   ->  Append  (cost=0.00..20444.75 rows=1001850 width=4) (actual time=0.010..149.330 rows=1000000.00 loops=1)
         ->  Seq Scan on history_2024 history_1  (cost=0.00..7286.60 rows=472960 width=4) (actual time=0.009..37.586 rows=472960.00 loops=1)
         ->  Seq Scan on history_2025 history_2  (cost=0.00..8098.00 rows=525600 width=4) (actual time=0.016..41.565 rows=525600.00 loops=1)
         ->  Seq Scan on history_2026 history_3  (cost=0.00..22.40 rows=1440 width=4) (actual time=0.020..0.132 rows=1440.00 loops=1)
         ->  Seq Scan on history_2027 history_4  (cost=0.00..28.50 rows=1850 width=4) (actual time=0.002..0.002 rows=0.00 loops=1)
(8 rows)

For column y it estimates to rows=200 instead of rows=500000.00:

postgres=# explain (analyze, buffers off, summary off)
 select distinct y from history
;
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=22949.38..22951.38 rows=200 width=4) (actual time=341.476..527.623 rows=500000.00 loops=1)
   Group Key: history.y
   Batches: 5  Memory Usage: 8265kB  Disk Usage: 16048kB
   ->  Append  (cost=0.00..20444.75 rows=1001850 width=4) (actual time=0.009..151.806 rows=1000000.00 loops=1)
         ->  Seq Scan on history_2024 history_1  (cost=0.00..7286.60 rows=472960 width=4) (actual time=0.009..38.840 rows=472960.00 loops=1)
         ->  Seq Scan on history_2025 history_2  (cost=0.00..8098.00 rows=525600 width=4) (actual time=0.011..43.265 rows=525600.00 loops=1)
         ->  Seq Scan on history_2026 history_3  (cost=0.00..22.40 rows=1440 width=4) (actual time=0.011..0.131 rows=1440.00 loops=1)
         ->  Seq Scan on history_2027 history_4  (cost=0.00..28.50 rows=1850 width=4) (actual time=0.003..0.003 rows=0.00 loops=1)
(8 rows)

Here, the misestimate isn’t an issue by itself, but it could become catastrophic if the query needed to join other tables. Even though the two columns have different value distributions, PostgreSQL estimated both as rows=200. When no statistics are available, PostgreSQL falls back to generic defaults. It assumes a small, fixed number of distinct values - even when there are more in one partition.

Autovacuum’s auto-analyze collects statistics for partitions but not for the partitioned parent. To gather global statistics on the parent, you must run analyze manually: ANALYZE ONLY analyzes just the parent (no recursion), while ANALYZE also recursively analyzes each partition:


ANALYZE history
;

select relname, relpages, reltuples, relkind, relhassubclass, relispopulated, relispartition  
 from pg_class where relname like 'history%' order by relkind desc, relname
;

      relname      | relpages | reltuples | relkind | relhassubclass | relispopulated | relispartition
-------------------+----------+-----------+---------+----------------+----------------+----------------
 history_2024      |     2557 |    472960 | r       | f              | t              | t
 history_2025      |     2842 |    525600 | r       | f              | t              | t
 history_2026      |        8 |      1440 | r       | f              | t              | t
 history_2027      |        0 |        -1 | r       | f              | t              | t
 history           |       -1 |     1e+06 | p       | t              | t              | f
 history_2024_pkey |     1300 |    472960 | i       | f              | t              | t
 history_2025_pkey |     1443 |    525600 | i       | f              | t              | t
 history_2026_pkey |        6 |      1440 | i       | f              | t              | t
 history_2027_pkey |        1 |         0 | i       | f              | t              | t
 history_num_seq   |        1 |         1 | S       | f              | t              | f
 history_pkey      |        0 |         0 | I       | t              | t              | f
(11 rows)

select tablename, attname, n_distinct, null_frac
 from pg_stats where tablename like 'history%'
;

  tablename   | attname | n_distinct  | null_frac
--------------+---------+-------------+-----------
 history      | year    |           3 |         0
 history      | num     |          -1 |         0
 history      | x       |           2 |         0
 history      | y       |        -0.5 |         0
 history_2025 | year    |           1 |         0
 history_2025 | num     |          -1 |         0
 history_2025 | x       |           2 |         0
 history_2025 | y       | -0.49914953 |         0
 history_2024 | year    |           1 |         0
 history_2024 | num     |          -1 |         0
 history_2024 | x       |           2 |         0
 history_2024 | y       |  -0.4907878 |         0
 history_2026 | year    |           1 |         0
 history_2026 | num     |          -1 |         0
 history_2026 | x       |           2 |         0
 history_2026 | y       | -0.50069445 |         0
(16 rows)

Now the query planner knows there are one million rows in total (1e+06 in reltuples), with 2 distinct values for x (2 in n_distinct) and 50% distinct values for y (-0.5 in n_distinct, the negative sign is used to mean a relative number that should scale with the table size).

Now the execution plans on the same queries get better estimates:

postgres=# explain (analyze, buffers off, summary off) select distinct x from history;
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=22907.01..22907.03 rows=2 width=4) (actual time=265.727..265.729 rows=2.00 loops=1)
   Group Key: history.x
   Batches: 1  Memory Usage: 32kB
   ->  Append  (cost=0.00..20407.01 rows=1000001 width=4) (actual time=0.009..150.276 rows=1000000.00 loops=1)
         ->  Seq Scan on history_2024 history_1  (cost=0.00..7286.60 rows=472960 width=4) (actual time=0.008..37.620 rows=472960.00 loops=1)
         ->  Seq Scan on history_2025 history_2  (cost=0.00..8098.00 rows=525600 width=4) (actual time=0.015..41.585 rows=525600.00 loops=1)
         ->  Seq Scan on history_2026 history_3  (cost=0.00..22.40 rows=1440 width=4) (actual time=0.018..0.132 rows=1440.00 loops=1)
         ->  Seq Scan on history_2027 history_4  (cost=0.00..0.00 rows=1 width=4) (actual time=0.002..0.003 rows=0.00 loops=1)
(8 rows)

postgres=# explain (analyze, buffers off, summary off) select distinct y from history;
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=74157.06..87067.10 rows=509753 width=4) (actual time=344.505..532.590 rows=500000.00 loops=1)
   Group Key: history.y
   Planned Partitions: 4  Batches: 5  Memory Usage: 8249kB  Disk Usage: 16048kB
   ->  Append  (cost=0.00..20407.01 rows=1000001 width=4) (actual time=0.009..153.913 rows=1000000.00 loops=1)
         ->  Seq Scan on history_2024 history_1  (cost=0.00..7286.60 rows=472960 width=4) (actual time=0.008..39.108 rows=472960.00 loops=1)
         ->  Seq Scan on history_2025 history_2  (cost=0.00..8098.00 rows=525600 width=4) (actual time=0.011..43.762 rows=525600.00 loops=1)
         ->  Seq Scan on history_2026 history_3  (cost=0.00..22.40 rows=1440 width=4) (actual time=0.011..0.130 rows=1440.00 loops=1)
         ->  Seq Scan on history_2027 history_4  (cost=0.00..0.00 rows=1 width=4) (actual time=0.003..0.003 rows=0.00 loops=1)
(8 rows)

The estimation for x is exact (rows=2) and the one for y is close to the actual number (rows=509753). The reason for the approximate number is because of sampling as analyze do not real the full table.

Global statistics are not collected automatically because auto-analyze triggers only after a modification threshold relative to total rows is reached, and the partitioned table itself is empty. Still, you should maintain statistics.

Another option, when the global cardinality is known and stable, is to explicitly override the inherited estimate in PostgreSQL:

ALTER TABLE history ALTER COLUMN x SET (n_distinct_inherited = 2);
ALTER TABLE history ALTER COLUMN y SET (n_distinct_inherited = 500000);
ANALYZE ONLY HISTORY;

This tells the planner the exact global number of distinct values for columns "x" and "y" when planning queries on the partitioned table. It replaces a probabilistic estimate with deterministic knowledge and is particularly useful for large DISTINCT, GROUP BY, and join operations.

When the number of distinct values increases with new rows it is preferrable to set them with a relative factor, set with a negative number:

ALTER TABLE history ALTER COLUMN y SET (n_distinct_inherited = -0.5);
ANALYZE ONLY HISTORY;

In summary, PostgreSQL estimates distinct values via the following rules:

if uniqueness is proven by the query (unique index, DISTINCT, GROUP BY), it uses the number of non‑NULL rows
otherwise pg_statistic is used: stadistinct > 0 is an absolute count, stadistinct < 0 is relative to the total number of tuples (from pg_class)
if no statistics exist, it applies some hard‑coded rules, for example 2 for booleans
finally, it fall back to 200

Autovacuum skips partitioned tables entirely. It only processes partitions. When you ANALYZE a partitioned table, PostgreSQL skips the non-recursive scan for the parent (since it stores no rows) and performs a recursive inherited scan that samples rows from all partitions to compute inheritance statistics used by the planner. The planner uses the parent’s pg_statistic entries (built from the inherited sample) for estimates when planning queries that treat the partitioned table as a single entity. For distinct values, ANALYZE cannot know overlap across partitions, so the estimate is approximate. You can set a per-column n_distinct_inherited value to provide a better global distinct estimate.