DEV Community: Franck Pachot

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.

Read‑your‑writes on replicas: PostgreSQL WAIT FOR LSN and MongoDB Causal Consistency

Franck Pachot — Sat, 21 Feb 2026 21:36:36 +0000

In databases designed for high availability and scalability, secondary nodes can fall behind the primary. Typically, a quorum of nodes is updated synchronously to guarantee durability while maintaining availability, while remaining standby instances are eventually consistent to handle partial failures. To balance availability with performance, synchronous replicas acknowledge a write only when it is durable and recoverable, even if it is not yet readable.

As a result, if your application writes data and then immediately queries another node, it may still see stale data.

Here’s a common anomaly: you commit an order on the primary and then try to retrieve it from a reporting system. The order is missing because the read replica has not yet applied the write.

PostgreSQL and MongoDB tackle this problem in different ways:

PostgreSQL 19 should introduce a WAIT FOR LSN command, allowing applications to explicitly coordinate reads after writes.
MongoDB provides causal consistency within sessions using the afterClusterTime read concern.

Both approaches track when your write occurred and ensure subsequent reads observe at least that point. Let’s look at how each database does this.

PostgreSQL: `WAIT FOR LSN` (PG19 - currently in development)

PostgreSQL records every change in the Write‑Ahead Log (WAL). Each WAL record has a Log Sequence Number (LSN): a 64‑bit position, typically displayed as two hexadecimal halves such as 0/40002A0 (high/low 32 bits).

Streaming replication ships WAL records from the primary to standbys, which then:

Write WAL records to disk
Flush them to durable storage
Replay them, applying changes to data files

The write position determines what can be recovered after a database crash. The flush position defines the recovery point for a compute instance failure. The replay position determines what queries can see on a standby.

A recent commit for the next PostgreSQL release (19) adds a WAIT FOR LSN command to allow a session to block until one of these points reaches a target LSN:

standby_write → WAL written to disk on the standby (not yet flushed)
standby_flush → WAL flushed to durable storage on the standby
standby_replay (default) → WAL replayed into data files and visible to readers
primary_flush → WAL flushed on the primary (useful when synchronous_commit = off and a durability barrier is needed)

A typical flow is to write on the primary, commit, and then fetch the current WAL insert LSN:

pg19rw=*# BEGIN;

BEGIN

pg19rw=*# INSERT INTO orders VALUES (123, 'widget');

INSERT 0 1

pg19rw=*# COMMIT;

COMMIT

pg19rw=# SELECT pg_current_wal_insert_lsn();

 pg_current_wal_insert_lsn
---------------------------
 0/18724C0

(1 row)

That LSN is then used to block reads on a replica until it has caught up:


pg19ro=# WAIT FOR LSN '0/18724C0'
  WITH (MODE 'standby_replay', TIMEOUT '2s');

This LSN‑based read‑your‑writes pattern in PostgreSQL requires extra round‑trips: capturing the LSN on the primary and explicitly waiting on the standby. For many workloads, reading from the primary is simpler and faster.

The pattern becomes valuable when expensive reads must be offloaded to replicas while still preserving read‑your‑writes semantics, or in event‑driven and CQRS designs where the LSN itself serves as a change marker for downstream consumers.

MongoDB: Causal Consistency

While PostgreSQL reasons in WAL positions, MongoDB tracks causality using oplog timestamps and a hybrid logical clock.

In a replica set, each write on the primary produces an entry in local.oplog.rs, a capped collection. These entries are rewritten to be idempotent (for example, $inc becomes $set) so they can be safely reapplied. Each entry carries a Hybrid Logical Clock (HLC) timestamp that combines physical time with a logical counter, producing a monotonically increasing cluster time. Replica set members apply oplog entries in timestamp order.

Because MongoDB allows concurrent writes, temporary “oplog holes” can appear: a write with a later timestamp may commit before another write with an earlier timestamp. A naïve reader scanning the oplog could skip the earlier operation.

MongoDB prevents this by tracking an oplogReadTimestamp, the highest hole‑free point in the oplog. Secondaries are prevented from reading past this point until all prior operations are visible, ensuring causal consistency even in the presence of concurrent commits.

Causal consistency in MongoDB is enforced by attaching an afterClusterTime to reads:

Drivers track the operationTime of the last operation in a session.
When a session is created with causalConsistency: true, the driver automatically includes an afterClusterTime equal to the highest known cluster time on subsequent reads.
The server blocks the read until its cluster time has advanced beyond afterClusterTime.

With any read preference that allows reading from secondaries as well as the primary, this guarantees read‑your‑writes behavior:


// Start a causally consistent session
const session = client.startSession({ causalConsistency: true });

const coll = db.collection("orders");

// Write in this session
await coll.insertOne({ id: 123, product: "widget" }, { session });

// The driver automatically injects afterClusterTime into the read concern
const order = await coll.findOne({ id: 123 }, { session });

Causal consistency is not limited to snapshot reads. It applies across read concern levels. The key point is that the session ensures later reads observe at least the effects of earlier writes, regardless of which replica serves the read.

Conclusion

Here is a simplified comparison:

Feature	PostgreSQL `WAIT FOR LSN`	MongoDB Causal Consistency
Clock type	Physical byte offset in the WAL (LSN)	Hybrid Logical Clock (HLC)
Mechanism	Block until replay/write/flush LSN reached	Block until `afterClusterTime` is visible
Tracking	Application captures LSN	Driver tracks `operationTime`
Granularity	WAL record position	Oplog timestamp
Replication model	Physical streaming	Logical oplog application
Hole handling	N/A (serialized WAL)	`oplogReadTimestamp`
Failover handling	Error unless `NO_THROW`	Session continues, bounded by replication state

Both PostgreSQL’s WAIT FOR LSN and MongoDB’s causal consistency ensure reads can observe prior writes, but at different layers:

PostgreSQL offers manual, WAL‑level precision.
MongoDB provides automatic, session‑level guarantees.

If you want read‑your‑writes semantics to “just work” without additional coordination calls, MongoDB’s session‑based model is a strong fit. Despite persistent myths about consistency, MongoDB delivers strong consistency in a horizontally scalable system with a simple developer experience.

Relational composition and Codd's "connection trap" in PostgreSQL and MongoDB

Franck Pachot — Mon, 16 Feb 2026 15:39:55 +0000

Relational composition is to joins what the cartesian product is to tables: it produces every result that could be true, not just what is true. This often leads to SQL mistakes and can be suspected when a SELECT DISTINCT is added after a query returns more rows than expected, without the root cause being understood.

In its mathematical definition, relational composition is the derived relation obtained by existentially joining two relations on a shared attribute and projecting away that attribute. In a database, it is meaningful only when a real‑world invariant ensures that the resulting pairs reflect actual facts. Otherwise, the result illustrates what E. F. Codd, in his 1970 paper A Relational Model of Data for Large Shared Data Banks, called the connection trap.

Codd uses two relations in his example: a supplier supplies parts, and a project uses parts. At an intuitive level, this connection trap mirrors a syllogism: if a supplier supplies a part and a project uses that part, a join can derive that the supplier supplies the project—even when that conclusion was never stated as a fact.

Codd observed that the connection trap was common in pre‑relational network data models, where users navigated data by following physical pointers. Path existence was often mistaken for semantic relationship. The relational model solved this problem by replacing navigational access with declarative queries over explicitly defined relations, and modern document models now do the same.

However, while the relational model removes pointer‑based navigation, it does not eliminate the trap entirely. Joins can still compute relational compositions, and without appropriate cardinality constraints or business invariants, such compositions may represent only possible relationships rather than actual ones. The connection trap is not a failure of the relational model, but a failure to align query intent with modeled semantics. In this way, the connection trap can be reintroduced at query time, even in modern relational systems such as PostgreSQL, and similarly through $lookup operations in MongoDB.

PostgreSQL — reproducing the connection trap

This model declares suppliers, parts, projects, and two independent many‑to‑many relationships:

CREATE TABLE suppliers (
    supplier_id TEXT PRIMARY KEY
);

CREATE TABLE parts (
    part_id TEXT PRIMARY KEY
);

CREATE TABLE projects (
    project_id TEXT PRIMARY KEY
);

-- Supplier supplies parts
CREATE TABLE supplier_part (
    supplier_id TEXT REFERENCES suppliers,
    part_id     TEXT REFERENCES parts,
    PRIMARY KEY (supplier_id, part_id)
);

-- Project uses parts
CREATE TABLE project_part (
    project_id TEXT REFERENCES projects,
    part_id    TEXT REFERENCES parts,
    PRIMARY KEY (project_id, part_id)
);

This follows Codd’s classic suppliers–parts–projects example, where suppliers supply parts and projects use parts as independent relationships.

The following data asserts that project Alpha uses parts P1 and P2, that supplier S1 supplies parts P1 and P2, and that supplier S2 supplies parts P2 and P3:

INSERT INTO suppliers VALUES ('S1'), ('S2');
INSERT INTO parts     VALUES ('P1'), ('P2'), ('P3');
INSERT INTO projects  VALUES ('Alpha');

-- Supplier capabilities
INSERT INTO supplier_part VALUES
('S1', 'P1'),
('S1', 'P2'),
('S2', 'P2'),
('S2', 'P3');

-- Project uses parts P1 and P2
INSERT INTO project_part VALUES
('Alpha', 'P1'),
('Alpha', 'P2');

The following query is valid SQL:

SELECT DISTINCT
    sp.supplier_id,
    pp.project_id
FROM supplier_part sp
JOIN project_part pp
  ON sp.part_id = pp.part_id;

However, this query falls into the connection trap:

 supplier_id | project_id
-------------+------------
 S2          | Alpha
 S1          | Alpha
(2 rows)

As we defined only supplier–part and project–part relationships, any derived supplier–project relationship is not a fact but a relational composition. We know that Alpha uses P1 and P2, and that part P2 can be supplied by either S1 or S2, but we have no record of which supplier actually supplies Alpha.

This query asserts “Supplier S1 supplies project Alpha”, but the data only says: “S1 and S2 supply P2” and “Alpha uses P2”.

This is the connection trap, expressed purely in SQL.

PostgreSQL — the correct relational solution

If the business meaning is "actual supply", then the relationship must be modeled explicitly. If a supplier actually supplies a part to a project, that fact must be represented directly. We need a new table:

CREATE TABLE supply (

    supplier_id TEXT,
    project_id  TEXT,
    part_id     TEXT,

    PRIMARY KEY (supplier_id, project_id, part_id),

    FOREIGN KEY (supplier_id, part_id)
        REFERENCES supplier_part (supplier_id, part_id),

    FOREIGN KEY (project_id, part_id)
        REFERENCES project_part (project_id, part_id)
);

These foreign keys encode subset constraints between relations and prevent inserting supplies of parts not supplied by the supplier or not used by the project. In a relational database, meaning emerges from constraints, not from joins.

This relation explicitly states who supplies what to which project. We assume that the real‑world fact is “Alpha gets part P2 from supplier S1”:

INSERT INTO supply VALUES
('S1', 'Alpha', 'P2');

The correct query reads from this relation:

SELECT supplier_id, project_id
FROM supply;

 supplier_id | project_id
-------------+------------
 S1          | Alpha
(1 row)

The relationship is now real and asserted, not inferred. In total, we have six tables:

postgres=# \d
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | parts         | table | postgres
 public | project_part  | table | postgres
 public | projects      | table | postgres
 public | supplier_part | table | postgres
 public | suppliers     | table | postgres
 public | supply        | table | postgres
(6 rows)

In practice, you should either store the relationship explicitly or avoid claiming it exists. Although the relational model avoids pointers, it is still possible to join through an incorrect path, so the application must enforce the correct one.

In ad-hoc query environments such as data warehouses, data is typically organized into domains and modeled using a dimensional ("star schema") approach. Relationships like project–supplier are represented as fact tables within a single data mart, exposing only semantically valid join paths and preventing invalid joins.

MongoDB — reproducing the connection trap

The following MongoDB data mirrors the PostgreSQL example. MongoDB allows representing relationships either as separate collections or by embedding, depending on the bounded context. Here we start with separate collections to mirror the relational model:

db.suppliers.insertMany([
  { _id: "S1" },
  { _id: "S2" }
]);

db.parts.insertMany([
  { _id: "P1" },
  { _id: "P2" },
  { _id: "P3" }
]);

db.projects.insertMany([
  { _id: "Alpha" }
]);

// Supplier capabilities
db.supplier_parts.insertMany([
  { supplier: "S1", part: "P1" },
  { supplier: "S1", part: "P2" },
  { supplier: "S2", part: "P2" },
  { supplier: "S2", part: "P3" }
]);

// Project uses parts P1 and P2
db.project_parts.insertMany([
  { project: "Alpha", part: "P1" },
  { project: "Alpha", part: "P2" }
]);

Using the simple find() API, we cannot fall into the trap directly because there is no implicit connection between suppliers and projects. The application must issue two independent queries and combine the results explicitly.

Simulating the connection trap with simple queries therefore requires explicit composition at the application level:

const partsUsedByAlpha = db.project_parts.find(
  { project: "Alpha" },
  { _id: 0, part: 1 }
).toArray();

const suppliersForParts = db.supplier_parts.find(
  { part: { $in: partsUsedByAlpha.map(p => p.part) } },
  { _id: 0, supplier: 1, part: 1 }
).toArray();

const supplierProjectPairs = suppliersForParts.map(sp => ({
  supplier: sp.supplier,
  project: "Alpha"
}));

print(supplierProjectPairs);

When forced by the application logic, here is the connection trap associating suppliers and projects:

[
  { supplier: 'S1', project: 'Alpha' },
  { supplier: 'S1', project: 'Alpha' },
  { supplier: 'S2', project: 'Alpha' }
]

As with SQL joins, a $lookup in an aggregation pipeline can fall in the same connection trap:

db.supplier_parts.aggregate([
  {
    $lookup: {
      from: "project_parts",
      localField: "part",
      foreignField: "part",
      as: "projects"
    }
  },
  { $unwind: "$projects" },
  {
    $project: {
      _id: 0,
      supplier: "$supplier",
      project: "$projects.project"
    }
  }
]);

The result is similar and the projection removed the intermediate attributes:

{ "supplier": "S1", "project": "Alpha" }
{ "supplier": "S1", "project": "Alpha" }
{ "supplier": "S2", "project": "Alpha" }

We reproduced the connection trap by ignoring that $lookup produces a derived relationship, not a real one, and that matching keys does not carry business meaning. MongoDB does not prevent the connection trap, but makes composition more explicit by requiring the application or aggregation pipeline to perform it intentionally.

MongoDB — normalized solution

As with SQL, we can add an explicit supplies collection that stores the relationship between projects and suppliers:

db.supplies.insertOne({
  project: "Alpha",
  supplier: "S1",
  part: "P2"
});

Then we simply query this collection:

db.supplies.find(
  { project: "Alpha" },
  { _id: 0, supplier: 1, part: 1 }
);

[ { supplier: 'S1', part: 'P2' } ]

Document models can represent relational structures, as relations can be stored as flat collections. The difference is that referential integrity is enforced by the application rather than in-database. To enforce relationships in the database, they must be embedded as sub-documents and arrays.

MongoDB — domain-driven solution

It's not the only solution in a document database, as we can store a schema based on the domain model rather than normalized. MongoDB allows representing this relationship as part of an aggregate. In a project‑centric bounded context, the project is the aggregate root, and the supplier information can be embedded as part of the supply fact:

db.projects.updateOne(
  { _id: "Alpha" },
  {
    $set: {
      parts: [
        { part: "P2", supplier: "S1" },
        { part: "P1", supplier: null }
      ]
    }
  },
  { upsert: true }
);

The query doesn't need a join and cannot fall into the connection trap:

db.projects.find(
  { _id: "Alpha" },
  { _id: 1, "parts.supplier": 1 }
);

[
  {
    _id: 'Alpha',
    parts: [ 
      { supplier: 'S1' },
      { supplier: null }
    ]
  }
]

This avoids the connection trap by construction. It may look like data duplication—the same supplier name may appear in multiple project documents—and indeed this would be undesirable in a fully normalized model shared across all business domains. However, this structure represents a valid aggregate within a bounded context.

In this context, the embedded supplier information is part of the supply fact, not a reference to a global supplier record. If a supplier’s name changes, it is a business decision, not a database decision, whether that change should be propagated to existing projects or whether historical data should retain the supplier name as it was at the time of supply.

Even when propagation is desired, MongoDB allows updating embedded data efficiently:

db.projects.updateMany(
  // document filter
  { "parts.supplier": "S1" },
  // document update using the array's item from array filter
  {
    $set: {
      "parts.$[p].supplier": "Supplier One"
    }
  },
  // array filter defining the array's item for the update
  {
    arrayFilters: [{ "p.supplier": "S1" }]
  }
);

This update is not atomic across documents, but each document update is atomic and the operation is idempotent and can be safely retried or executed within an explicit transaction if full atomicity is required.

Conclusion

The connection trap occurs whenever relationships are inferred from shared keys, at query time, instead of being explicitly represented as facts, at write time. Derived relationships are not wrong—they are just not facts unless asserted. In SQL, this means introducing explicit association tables and enforcing integrity constraints, rather than deriving them through joins. In MongoDB, it means modeling relationships as explicit documents or embedded subdocuments rather than deriving them through lookups.

In a relational database, the schema is designed to be normalized and independent of specific use cases. All many‑to‑many and fact‑bearing relationships must be declared explicitly, and queries must follow the correct relational path. Referential and cardinality constraints are essential to restrict to only actual facts.

The data model is typically driven by the domain and the application’s use cases. In a domain-driven design (DDD), strong relationships are modeled as aggregates, embedding related data directly within a document in MongoDB collections. This makes the intended semantics explicit and avoids inferring relationships at query time. Apparent duplication is not a flaw here, but a deliberate modeling choice within a bounded context.

Ultimately, the connection trap is not fully avoided by the normalized data model, but can be a query-time error with joins and projections: deriving relationships that were never asserted. Whether using normalized relations, star schema, or domain‑driven documents, the rule is the same—if a relationship is a fact, it must be stored as one.

DEV Community: Franck Pachot

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

Example: player's scores

Documents in MongoDB

UpdateMany() in MongoDB

Comparison with PostgreSQL and JSONB

Comparison with Oracle Database and OSON

Conclusion

PostgreSQL global statistics on partitionned table require a manual ANALYZE

Read‑your‑writes on replicas: PostgreSQL WAIT FOR LSN and MongoDB Causal Consistency

PostgreSQL: WAIT FOR LSN (PG19 - currently in development)

MongoDB: Causal Consistency

Conclusion

Relational composition and Codd's "connection trap" in PostgreSQL and MongoDB

PostgreSQL — reproducing the connection trap

PostgreSQL — the correct relational solution

MongoDB — reproducing the connection trap

MongoDB — normalized solution

MongoDB — domain-driven solution

Conclusion

PostgreSQL: `WAIT FOR LSN` (PG19 - currently in development)