Forem: Franck Pachot

The Autovacuum Scale Factor Problem at Scale - Know Your Defaults

Franck Pachot — Tue, 26 May 2026 10:45:53 +0000

In PostgreSQL, autovacuum and autoanalyze exist to clean up dead tuples (old versions of updated/deleted rows) and update query planner statistics, respectively. The challenge is running them frequently enough so that query plans and execution do not degrade after data modifications, but not so frequently as to cause excessive I/O overhead.

Databases often maintain a counter of the number of modifications to trigger these background jobs. Oracle Database and MySQL use a stale percentage (the ratio of modifications to total rows) for statistics gathering. SQL Server uses a dynamically decreasing percentage to ensure statistics do not remain stale for too long on massive tables. PostgreSQL uses a hybrid approach: a fixed base threshold combined with a scale factor (a percentage) that grows proportionally with the table size.

This hybrid approach hits the sweet spot for most workloads, but it often requires tuning based on your specific data. The key factor to watch is the amount of static, "cold" data in your tables. Because the scale factor is calculated against the total table size, a large volume of cold data will significantly inflate the threshold. This can delay maintenance on the active working set—the "hot" data actually used by your queries—leaving it vulnerable to stale statistics or bloat.

Here are the default base thresholds:

postgres=# \dconfig *autovacuum*threshold
        List of configuration parameters
             Parameter              |   Value
------------------------------------+-----------
 autovacuum_analyze_threshold       | 50
 autovacuum_vacuum_insert_threshold | 1000
 autovacuum_vacuum_max_threshold    | 100000000
 autovacuum_vacuum_threshold        | 50
(4 rows)

At first glance, this suggests tables are analyzed when 50 rows are modified, and vacuumed when 50 dead tuples accumulate (from deletes or updates) or 1,000 rows are inserted. But this is only true without the scale factor—10% for statistics, 20% for vacuum:

postgres=# \dconfig *autovacuum*scale_factor
       List of configuration parameters
               Parameter               | Value
---------------------------------------+-------
 autovacuum_analyze_scale_factor       | 0.1
 autovacuum_vacuum_insert_scale_factor | 0.2
 autovacuum_vacuum_scale_factor        | 0.2
(3 rows)

Because of the scale factor, the actual trigger thresholds increase with the size of the table. For the default settings, the formulas are:

Analyze when inserts or modifications > 10% table row count plus 50 rows
Vacuum when dead tuples (from DELETE or UPDATE) > 20% table row count plus 50 rows
Vacuum when inserts > 20% table row count plus 1000 rows

As these formulas show, a larger table requires a much larger accumulation of changes before maintenance fires. This is perfectly acceptable if data churn is uniformly distributed, as small changes across a massive dataset will not drastically impact query cost estimations.

However, data distribution is rarely uniform and evolves over time (e.g., seasonal sales spikes, market expanding to new countries). Because static data inflates the table row count in the formulas above, your database waits too long to trigger maintenance on the active working set.

This is the core problem with default autovacuum settings at scale: a table with 5 million rows can accumulate half a million stale modifications before the planner statistics are refreshed, and over a million dead tuples before bloat is cleaned up. The larger the table grows, the longer it waits, and the worse the situation becomes:

Query planner statistics become increasingly stale between analyzes.
The visibility map is stale for longer and index-only scans become less efficient.
Dead tuple bloat accumulates more between cleanups, wasting storage and slowing scans.

To demonstrate this, I have run the following script to simulate this kind of activity, constantly inserting 100 rows and then updating them. We delete nothing because we want to keep the history, but queries operate on those recent rows. Think of it like orders being entered, then processed, and remaining stored:

\c
\o tmp.log

-- run autovacuum frequently for the demo
alter system set autovacuum_naptime = '1s';
select pg_reload_conf();

-- create a table
drop table demo;
create table demo (
 id bigserial primary key, n int default 0
);
vacuum analyze demo
;

-- show the vacuum and analyze statistics,
-- insert 100 rows and update them
-- run that in a loop every 5 seconds
select relname, n_tup_ins, n_tup_upd , n_mod_since_analyze, n_ins_since_vacuum
 , autovacuum_count,  last_autovacuum  --, vacuum_count,  last_vacuum
 , autoanalyze_count, last_autoanalyze --, analyze_count, last_analyze
from pg_stat_user_tables where relid='demo'::regclass
\;
insert into demo select from generate_series(1,100)
\;
update demo set n=n+1 where id in (
select id from demo order by id desc limit 100
)
\watch i=5 c=100000

For each iteration, the total number of rows inserted (n_tup_ins) and updated (n_tup_upd), as visible in pg_stat_user_tables, increases by 100. It is the X-axis on this diagram (n_tup):

The Y-axis shows the staleness of statistics (n_mod_since_analyze) and the accumulation of dead tuples (n_mod_since_vacuum) until the auto vacuum/analyze kicks in.

With 5 million rows, the last million inserted rows accumulated dead tuples. That is 20% of the total table, as defined by the default vacuum scale factor, but it likely represents 100% of the data actively read by your queries (for example, if the application processes the last year or less of a 5-year history). Furthermore, the last 500,000 rows have completely stale statistics, the 10% default analyze scale factor, and the past months may not have the same data distribution as the previous years.

Think about the impact this has on the maximum value for an ID sequence or a created_at timestamp. It also completely skews the query planner's understanding of your data distribution (such as querying by country or day of the week). I have seen this cause severe performance issues in the real world: a retail company where shops only open on Sundays during the summer, or a trading platform suddenly processing entirely new market trends. Because the statistics are stale, the planner assumes your new, active data looks exactly like your old, historical data.

As the table grows, the impact of this bloat and staleness compounds, and performance will no longer scale. Eventually, your execution plans will flip—not because the queries changed, but simply because the estimations of the query planner are completely wrong.

For very large tables where the total size increases but the active working set is a small, predictable number of rows, you can effectively disable the scale factor and rely almost entirely on the fixed threshold:

ALTER TABLE demo SET (  
    autovacuum_analyze_scale_factor = 0.001,  
    autovacuum_analyze_threshold    = 10000,  
    autovacuum_vacuum_scale_factor  = 0.001,  
    autovacuum_vacuum_threshold     = 10000  
);

This sets a nearly flat threshold that does not grow with the table size. The right threshold value depends on how many rows your active working set changes per hour and how much staleness you can tolerate. However, you must monitor the consequences of running autovacuum frequently on a growing table to ensure it does not cause localized I/O spikes.

Here is how the same run starts with the new table settings:

Auto analyze never left more than ~10,000 modified rows without refreshing statistics. This threshold grows slightly with the table (at 10 million rows it doubles to 20,000), but remains vastly better than the default. Auto vacuum follows the same pattern for dead tuples, but runs more frequently here because the insert-specific vacuum trigger was left at its default (1,000 rows + 20% scale factor), which only triggers the analyze threshold beyond 45,000 rows.

To address this unbounded growth natively, recent PostgreSQL versions introduced autovacuum_vacuum_max_threshold (with a default of 100 million—which is too high for my example). Rather than letting the scale factor dictate an endlessly growing target, this parameter imposes a hard ceiling on the vacuum threshold calculation. This means that even on a massive 1-billion-row table, autovacuum will forcibly trigger once dead tuples reach the configured maximum, serving as a built-in safety net. You can even adjust this globally via a simple config reload, or set it as a per-table storage parameter to enforce a strict upper limit on staleness without micromanaging scale factors across your entire schema.

Naturally, enforcing stricter thresholds—whether through these new maximum caps or manual table-level tuning—means autovacuum will run more frequently, which demands more background worker capacity. Historically, increasing autovacuum_max_workers to handle this extra load required a full database restart. However, PostgreSQL now thoughtfully splits this architecture: autovacuum_worker_slots reserves the hard upper bound of backend slots at postmaster startup, while autovacuum_max_workers dictates how many of those slots are actively used. This allows you to dynamically scale up your active workers on the fly (ALTER SYSTEM SET autovacuum_max_workers = 8; SELECT pg_reload_conf();) to absorb heavy maintenance workloads without incurring any downtime.

Alternatively, if your table has a clear time-based or categorical boundary between hot and cold data, partitioning is worth considering. Autovacuum operates per partition, so a current_year partition with 100,000 rows will trigger maintenance far sooner than a monolithic 5-million-row table, meaning the default scale factor will naturally behave exactly as intended.

Avoiding range overlaps in PostgreSQL with EXCLUDE constraint, better than serializable or assertions

Franck Pachot — Fri, 15 May 2026 18:21:25 +0000

Two groups of friends try to book seats ranges 5–8 and 7–9 in the same row, for the same show, at the same time. One of them must fail and retry. An ACID database can help enforce this rule. The question is: how much collateral damage does your concurrency control cause for everyone else?

In modern development, we tend to put the business logic in the application first. Still, enforcing business invariants in the database with integrity constraints may provide better performance when it avoids the need for the serializable isolation level required when the invariant concerns multiple rows. This is the case for foreign keys and unique constraints, and here is another example.

In a previous post of this series, we saw how Oracle SQL Assertions can work around the limitations of Oracle's Snapshot Isolation (improperly called SERIALIZABLE). We explored the rule: "each shift must always have at least one doctor on call", which is an example of a table-level invariant — a constraint that requires at least one row satisfying a given condition to exist at all times. This goes beyond what traditional row-pair constraints can express.

Most databases provide only the unique constraint, which enforces that no two rows share the same value(s) for a set of columns. PostgreSQL extends this with exclusion constraints, which generalize the concept: rather than just preventing duplicate values, they prevent any two rows from satisfying a specified pairwise condition — for example, preventing two time ranges from overlapping.

Here is an example of such a business rule: we manage a movie theater where customers can book consecutive seats in a row for themselves and their friends.

Oracle Database: assertion

The booking table identifies the seats for a session (SHOWING_ID) by the row (ROW_NUMBER) and the seat number range (SEAT_START, SEAT_END):


Name           Null?       Type
______________ ___________ ______________________________
BOOKING_ID     NOT NULL    RAW(16 BYTE)
SHOWING_ID     NOT NULL    RAW(16 BYTE)
ROW_NUMBER     NOT NULL    NUMBER(38)
SEAT_START     NOT NULL    NUMBER(38)
SEAT_END       NOT NULL    NUMBER(38)
CUSTOMER_ID    NOT NULL    RAW(16 BYTE)
CREATED_AT                 TIMESTAMP(6) WITH TIME ZONE

Typically, customers query look at the free seats in their preferred row, and book an available range that is free and can fit the number of seats they need. A serializable transaction would be needed to be sure that what is read is not booked by another in the meantime, but Oracle's SERIALIZABLE isolation level does not provide true serializability — it implements Snapshot Isolation, which permits write skew anomalies and doesn't prevent a new booking from being committed that overlaps what was read. With SQL assertions, you can take another approach: create an integrity constraint that raises an error in case of overlap:


CREATE ASSERTION no_overlapping_seats
CHECK (
    NOT EXISTS (
        SELECT 1 
        FROM bookings b1
        WHERE EXISTS (
            SELECT 1 
            FROM bookings b2
            WHERE b1.showing_id = b2.showing_id
              AND b1.row_number = b2.row_number
              AND b1.booking_id <> b2.booking_id
              -- Intersection logic:
              AND b1.seat_start < b2.seat_end
              AND b2.seat_start < b1.seat_end
        )
    )
);

The rule says that there cannot exist two bookings within the same show for the same row with overlapping start/end seat ranges.

The assertion uses the existential negation form (NOT EXISTS). The universal quantifier version, which may be easier to understand, says that for any two different bookings on the same show and row, the seat ranges must not overlap (one ends before the other starts):

∀ b1, b2 ∈ Bookings : (
    b1.show = b2.show ∧ b1.row = b2.row ∧ b1.id ≠ b2.id
) ⇒ (
    b1.start ≥ b2.end ∨ b2.start ≥ b1.end
)

This is equivalent, by De Morgan's law (x ∨ y ≡ ¬(¬x ∧ ¬y)), to saying it cannot be the case that both ranges extend into each other:

∀ b1, b2 ∈ Bookings : (
    b1.show = b2.show ∧ b1.row = b2.row ∧ b1.id ≠ b2.id
) ⇒ ¬ (
    b1.start < b2.end ∧ b2.start < b1.end
)

Now, applying quantifier duality (∀x : P ⇒ ¬Q ≡ ¬∃x : P ∧ Q), it is easy to understand the existential negation version that maps directly to the NOT EXISTS assertion:

¬∃ b1, b2 ∈ Bookings : (
    b1.show = b2.show ∧ b1.row = b2.row ∧ b1.id ≠ b2.id
  ∧
    b1.start < b2.end ∧ b2.start < b1.end
)

With this, any concurrent inserts for the same show and same row will wait, and after the wait, the seat ranges will be checked and ORA-08601: SQL assertion (NO_OVERLAPPING_SEATS) violated will be raised if they overlap. This works but is not ideal because it will wait even if the seats do not overlap. However, two non-overlapping bookings in the same show and row will still block before being accepted becaise assertions lock on exact column values (here, the combination of showing_id and row_number), not on ranges within those values.

It is possible to get better concurrency for that rule with PostgreSQL exclusion constraints, but first, let's check with serializable as PostgreSQL supports true Serializable Snapshot Isolation (SSI).

PostgreSQL: serializable

To get a fully reproducible example, I create all necessary tables for the theater's screens, movies, showings, and bookings:


CREATE TABLE screens (
    screen_id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    rows INT NOT NULL, 
    seats_per_row INT NOT NULL 
);

INSERT INTO screens (screen_id, name, rows, seats_per_row) VALUES
('a1b2c3d4-0001-4000-8000-000000000001', 'Alpha Station', 25, 36),
('a1b2c3d4-0001-4000-8000-000000000002', 'Big Market', 18, 28),
('a1b2c3d4-0001-4000-8000-000000000003', 'Mül Paradise', 12, 20),
('a1b2c3d4-0001-4000-8000-000000000004', 'Kirian Corridor', 15, 24);

CREATE TABLE movies (
    movie_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    year INT NOT NULL,
    duration_minutes INT NOT NULL,
    director TEXT NOT NULL DEFAULT 'Luc Besson'
);

INSERT INTO movies (movie_id, title, year, duration_minutes) VALUES
('b0b0b0b0-0001-4000-8000-000000000001', 'Subway', 1985, 104),
('b0b0b0b0-0001-4000-8000-000000000002', 'The Big Blue', 1988, 168),
('b0b0b0b0-0001-4000-8000-000000000003', 'Nikita', 1990, 117),
('b0b0b0b0-0001-4000-8000-000000000004', 'Léon: The Professional', 1994, 110),
('b0b0b0b0-0001-4000-8000-000000000005', 'The Fifth Element', 1997, 126),
('b0b0b0b0-0001-4000-8000-000000000006', 'Angel-A', 2005, 90),
('b0b0b0b0-0001-4000-8000-000000000007', 'Lucy', 2014, 89),
('b0b0b0b0-0001-4000-8000-000000000008', 'Valerian and the City of a Thousand Planets', 2017, 137),
('b0b0b0b0-0001-4000-8000-000000000009', 'Anna', 2019, 119),
('b0b0b0b0-0001-4000-8000-000000000010', 'Dogman', 2023, 114);

CREATE TABLE showings (
    showing_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    movie_id UUID NOT NULL,
    screen_id UUID NOT NULL,
    start_time TIMESTAMPTZ NOT NULL
);

INSERT INTO showings (showing_id, movie_id, screen_id, start_time) VALUES
-- Friday
('c0c0c0c0-0001-4000-8000-000000000001', 'b0b0b0b0-0001-4000-8000-000000000005', 'a1b2c3d4-0001-4000-8000-000000000001', '2025-01-24 19:00:00+01'),
('c0c0c0c0-0001-4000-8000-000000000002', 'b0b0b0b0-0001-4000-8000-000000000004', 'a1b2c3d4-0001-4000-8000-000000000003', '2025-01-24 20:00:00+01'),
('c0c0c0c0-0001-4000-8000-000000000003', 'b0b0b0b0-0001-4000-8000-000000000008', 'a1b2c3d4-0001-4000-8000-000000000002', '2025-01-24 21:00:00+01'),
('c0c0c0c0-0001-4000-8000-000000000004', 'b0b0b0b0-0001-4000-8000-000000000007', 'a1b2c3d4-0001-4000-8000-000000000004', '2025-01-24 22:00:00+01')
;

CREATE TABLE bookings (
    booking_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    showing_id UUID NOT NULL REFERENCES showings(showing_id),
    row_number INT NOT NULL,
    seat_range INT4RANGE NOT NULL,  -- e.g., [5,9) means seats 5,6,7,8
    customer_id UUID NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO bookings (booking_id, showing_id, row_number, seat_range, customer_id) VALUES
('d0d0d0d0-0001-4000-8000-000000000001', 'c0c0c0c0-0001-4000-8000-000000000001', 12, '[15,19)'::int4range, 'e0e0e0e0-0001-4000-8000-000000000001'),
('d0d0d0d0-0001-4000-8000-000000000002', 'c0c0c0c0-0001-4000-8000-000000000001', 12, '[19,21)'::int4range, 'e0e0e0e0-0001-4000-8000-000000000002'),
('d0d0d0d0-0001-4000-8000-000000000003', 'c0c0c0c0-0001-4000-8000-000000000001', 13, '[10,16)'::int4range, 'e0e0e0e0-0001-4000-8000-000000000003'),
('d0d0d0d0-0001-4000-8000-000000000004', 'c0c0c0c0-0001-4000-8000-000000000001', 14, '[1,5)'::int4range, 'e0e0e0e0-0001-4000-8000-000000000004'),
('d0d0d0d0-0001-4000-8000-000000000005', 'c0c0c0c0-0001-4000-8000-000000000001', 14, '[30,36)'::int4range, 'e0e0e0e0-0001-4000-8000-000000000005');

INSERT INTO bookings (
 showing_id,
 row_number,
 seat_range,
 customer_id
) VALUES (
 'c0c0c0c0-0001-4000-8000-000000000002',
  12,
  '[ 1, 3)'::int4range,
  gen_random_uuid()
)
;

PostgreSQL has a built-in range datatype. A range like [5,8) represents a half-open interval — it includes 5, 6, and 7 but not 8. The [ means inclusive, ) means exclusive. This is the standard representation for discrete ranges in PostgreSQL, and it means I can store the seat range in a single column instead of two columns like in Oracle Database. PostgreSQL also provides operators on ranges, notably && (overlaps) which returns true when two ranges share any points in common.

Typically a service that checks the available seats for a show and books a range of seats will use a serializable transaction:


BEGIN ISOLATION LEVEL SERIALIZABLE;

-- query the show's booked seats
SELECT row_number,seat_range,now() 
 FROM bookings 
 WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002';

-- find a range available and book it 
INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)  
 VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[ 5, 8)'::int4range, gen_random_uuid())
 RETURNING customer_id, booking_id, created_at;

-- wait time added to hold the transaction open long enough for others 
-- to start and attempt to commit concurrently
select pg_sleep(10); 

-- commit the transaction or fail if not serializable
COMMIT;

For this show and row, only seats in range [ 1, 3) are currently booked. I run my transactions in parallel for ranges [ 5, 8), [25,28), [ 7,10) so I expect only one of [ 5, 8) and [ 7,10) to succeed, in addition to [ 5, 8) which do not overlap with anyone:



\! psql -ec '\timing on' -c "begin isolation level serializable; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)  VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[ 5, 8)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;"   & sleep 1

\! psql -ec '\timing on' -c "begin isolation level serializable; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)  VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[25,28)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;"   & sleep 1

\! psql -ec '\timing on' -c "begin isolation level serializable; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)  VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[ 7,10)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;"   & sleep 1

Note: the \! commands launch background shell processes with &, staggered by 1-second sleeps, to simulate concurrent users hitting the database at nearly the same time.

As it was first, [5,8) succeeded:



postgres=# SELECT row_number,seat_range,created_at
 FROM bookings
 WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002' 
 ORDER BY created_at
;
 row_number | seat_range |          created_at
------------+------------+-------------------------------
         12 | [1,3)      | 2026-05-14 10:12:58.322202+00
         12 | [5,8)      | 2026-05-14 10:15:26.063663+00
(2 rows)

Both [25,28) and [ 7,10) have waited for the first one to commit and have failed, not because of overlap, as both viewed the seats free from their query (the first transaction wasn't committed yet), but because of serialization:

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.
Time: 10004.080 ms (00:10.004)

Serializable Snapshot Isolation (SSI) uses predicate locks to track what each transaction has read. Here, all three transactions read the same set of rows for the showing — SSI records a predicate lock on the showing_id filter condition. When the first transaction commits its insert, the other transactions have a read-write dependency: they read a state that no longer exists (the showing now has a new booking). SSI detects this (a "pivot" in the dependency graph) and aborts the transactions that cannot be guaranteed to produce a serializable outcome. The granularity of SSI predicate locks means the entire showing's bookings are effectively locked — not just the specific seat range. This is why [25,28), which doesn't overlap with anything, still fails.

For better concurrency, we need to lower the isolation level and enforce the overlap prevention as an in-database integrity constraint. In short, we enforce the logic using the C instead of I from ACID properties.

PostgreSQL: EXCLUDE constraint

PostgreSQL's exclusion constraints generalize unique constraints: instead of checking only equality between rows, they can check any combination of operators. The constraint is backed by an index (typically GiST) that efficiently finds conflicting rows at insert or update time. The key insight is that the constraint acts as a materialized conflict check — it evaluates against the current committed state of the table at the moment of the insert, independent of what the transaction previously read. This is why we can safely drop down to Read Committed isolation.

Exclusion constraints require a GiST index on the range for the overlap operator &&. Since I also have equality operators = on non-range columns ("showing_id", "row_number"), I need to enable the btree_gist extension — it's shipped with PostgreSQL and adds GiST operator classes for scalar types like UUID and integer, allowing them to be mixed with range types in a single GiST index. Then I can declare the exclusion constraint that will raise an error on seat range overlap for the same show and row:

-- Need the btree_gist extension for mixing = and && operators
CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookings ADD
    CONSTRAINT no_overlapping_seats
        EXCLUDE USING GIST (
            showing_id WITH =,
            row_number WITH =,
            seat_range WITH &&      -- && = "overlaps" operator
        )
;

The EXCLUDE USING GIST declaration reads: "no two rows may exist where showing_id equals, row_number equals, AND seat_range overlaps." The GiST index makes this check efficient — PostgreSQL doesn't scan the whole table, it uses the index to find only potentially conflicting rows.

If you forget to create the btree_gist extension, you'll encounter the following error:

ERROR:  data type uuid has no default operator class for access method "gist"  
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

The reason is that GiST indexes natively support only geometric and range types (with operators like &&, @>, <<). Scalar types like uuid, integer, or text have no built-in GiST operator class — they normally live in B-tree indexes. The btree_gist extension adds GiST operator classes for these scalar types, enabling them to participate in exclusion constraints alongside range operators. The error is misleading because it doesn't say "you need btree_gist" but says "this is not supported by GiST alone".

Here are some inserts to test it:


-- This should FAIL (overlaps with seats [15,19) in row 12, showing ...0001)
INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)
VALUES ('c0c0c0c0-0001-4000-8000-000000000001', 12, '[17,22)'::int4range, gen_random_uuid());

-- This should SUCCEED (adjacent, seats [21,25) in row 12)
INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)
VALUES ('c0c0c0c0-0001-4000-8000-000000000001', 12, '[21,25)'::int4range, gen_random_uuid());

-- This should SUCCEED (same row 12 but different showing)
INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)
VALUES ('c0c0c0c0-0001-4000-8000-000000000004', 12, '[15,19)'::int4range, gen_random_uuid());

You can validate that overlapping ranges of seats are not permitted for the same row in the same show. However, this doesn't show the concurrency advantages of it like a parallel testing.

I delete the booking for [5,8) that succeeded before, so that I can run my parallel test again:


DELETE FROM bookings 
 WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002' 
 AND row_number=12 AND seat_range='[ 5, 8)'::int4range
;

Now, I can run my transactions in Read Committed isolation level because the constraint prevents the overlap, regardless of what was read initially. The exclusion constraint checks for conflicts against committed data at insert time — even if the transaction's earlier SELECT didn't see the conflicting row:



\! psql -ec '\timing on' -c "begin isolation level read committed; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)  VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[ 5, 8)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;"   & sleep 1

\! psql -ec '\timing on' -c "begin isolation level read committed; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)  VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[25,28)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;"   & sleep 1

\! psql -ec '\timing on' -c "begin isolation level read committed; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)  VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[ 7,10)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;"   & sleep 1

Two bookings have succeeded, [5,8) and [25,28), and from the creation time you can see that they didn't have to wait on each other:


postgres=# SELECT row_number,seat_range,created_at
 FROM bookings
 WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002' 
 ORDER BY created_at
;
 row_number | seat_range |          created_at
------------+------------+-------------------------------
         12 | [1,3)      | 2026-05-14 10:12:58.322202+00
         12 | [5,8)      | 2026-05-14 10:33:25.935444+00
         12 | [25,28)    | 2026-05-14 10:33:26.936793+00
(3 rows)

Only the conflicting range [7,10) waited on the first transaction to commit and then detected the overlap:


ERROR:  conflicting key value violates exclusion constraint "no_overlapping_seats"
DETAIL:  Key (showing_id, row_number, seat_range)=(c0c0c0c0-0001-4000-8000-000000000002, 12, [7,10)) conflicts with existing key (showing_id, row_number, seat_range)=(c0c0c0c0-000
1-4000-8000-000000000002, 12, [5,8)).
Time: 8009.347 ms (00:08.009)

Note that the wait time (~8 seconds instead of ~10) corresponds to the 1-second stagger between the sessions: the third transaction started 2 seconds after the first, so it waited the remaining duration until the first committed. During this wait, PostgreSQL holds a lightweight lock on the potentially conflicting index entry — only transactions inserting an actually overlapping range will block.

Indexes are often used to avoid broader table locks. For example, unique constraints in most databases rely on an index to detect conflicts, and in Oracle, creating an index on a foreign key helps avoid locks for referential integrity checks. PostgreSQL does not need that because it can use share row locks, whereas Oracle implements only exclusive row locks.

The application must interpret this error (SQLSTATE 23P01 — exclusion_violation) as: "those seats are not available" and it should start again the transaction with a fresher seat map. This is similar to how applications handle serialization failures (SQLSTATE 40001) with SSI, but with an important difference: with the exclusion constraint, only actual overlaps trigger the error, so retries are far less frequent.

Summary

Approach	Blocking Behavior	Concurrency	Constraint Expressiveness
Oracle 26ai Assertion	Blocks all inserts for same show+row, even non-overlapping	Medium (wait even non-conflicting seats)	Very high (arbitrary SQL predicates)
PostgreSQL Serializable (SSI)	Aborts transactions that read the same predicate range, even non-overlapping inserts	Low (requires retries)	N/A (application logic)
PostgreSQL EXCLUDE constraint	Blocks only actually overlapping ranges	High (fails only on conflict)	Pairwise operators supported by an index

The exclusion constraint with GiST index provides the best concurrency: non-conflicting inserts proceed in parallel without waiting or aborting, while true overlaps are precisely detected and rejected. Serializable isolation is more conservative — it must protect against any possible anomaly from the read set, not just overlaps. Oracle assertions are the most expressive (they can enforce arbitrary multi-row predicates) but operate at the granularity of exact value locks, which causes unnecessary blocking when the actual conflict depends on range overlap.

The tradeoff is clear: if your invariant can be expressed as a pairwise exclusion condition with indexable operators, PostgreSQL's EXCLUDE constraint gives you the best of both worlds — declarative integrity with fine-grained concurrency.

Do you need foreign keys and surrogate keys because you broke your relationships ?

Franck Pachot — Sat, 09 May 2026 20:47:56 +0000

Many developers believe they need a relational database because their data has relationships. The reasoning usually goes: "My entities are related, therefore I need foreign keys, therefore I need an RDBMS."

But the causality is actually reversed. Normalization creates the need for foreign keys—not the other way around.

This misunderstanding persists when the builders are agents, because LLMs operate on high-dimensional similarity (vectors), which may catch correlation better than causality.

Normalization and relations

When you normalize your domain model into Normal Forms, you decompose aggregates—cohesive groups of entities that form a natural consistency boundary—into separate, independent tables. Each table represents a single relation in the mathematical sense: a set of tuples (rows) constrained by functional dependencies on a key. That is what "relational" means in "relational model"—it refers to these mathematical relations (tables), not to the relationships between entities.

Actually, this normalization is a decomposition that removes the structural associations between entities. Co-location, direct references, and pointers that once made related data physically and logically cohesive are eliminated. The connections between entities exist only through shared attribute values, and are reconstructed at query time via joins on those columns. Normalization eliminates those relationships from the data structure, keeping only direct, functional dependency of an attribute on its key.

In other words, relational databases are not meant to strengthen relationships—they dissolve them from the schema and defer their reconstruction to the query, in order to have an application-agnostic representation of data.

An example: the Order aggregate

Consider a typical e-commerce domain. An Order is a natural aggregate: it contains line items, a shipping address, and payment details. In your domain model, these are not independent things—a line item has no meaning outside its order, a shipping address belongs to that specific purchase, and the payment is authorized for that exact total.

In a document database, this aggregate stays whole:

{
  "_id": ObjectId("order_123"),
  "customer": "Alice",
  "status": "confirmed",
  "shippingAddress": {
    "street": "123 Main St",
    "city": "Springfield",
    "zip": "62704"
  },
  "items": [
    { "sku": "WIDGET-A", "name": "Blue Widget", "qty": 2, "price": 9.99 },
    { "sku": "GADGET-B", "name": "Red Gadget", "qty": 1, "price": 24.99 }
  ],
  "payment": {
    "method": "credit_card",
    "last4": "4242",
    "authorized": 44.97
  }
}

The consistency boundary is the document. A single write replaces or updates the entire order atomically. No line item can be orphaned. No shipping address can drift out of sync. The structure is the integrity.

Now normalize this into Third Normal Form:

orders (order_id PK, customer, status)
order_items (item_id PK, order_id FK, sku, name, qty, price)
shipping_addresses (address_id PK, order_id FK, street, city, zip)
payments (payment_id PK, order_id FK, method, last4, authorized)

Notice what happened:

The aggregate was shattered into four independent tables with no structural cohesion.
Foreign keys had to be added (order_id FK) to re-express the parent-child relationship that was previously implicit through embedding.
Surrogate keys had to be invented (address_id) because value objects don't have a natural identity—they were identified by their values.
Joins are now required to reconstitute what was a single read—SELECT ... FROM orders JOIN order_items JOIN shipping_addresses JOIN payments.
Referential integrity constraints must be declared to prevent orphan rows—a problem that could not exist when the data was embedded.
Transaction scope must be explicitly managed because inserting an order now requires manipulating data in four tables. You need BEGIN/COMMIT to ensure atomicity that was previously guaranteed by the single-document write.

Surrogate keys: identity invented for decomposition

This point deserves emphasis. In Domain-Driven Design, a Value Object has no identity—it is defined entirely by its attributes. A shipping address is a value object. A line item, depending on your domain, may be a dependent entity whose identity is meaningful only within its parent aggregate.

But a relational table requires every row to be uniquely identifiable by a primary key. When you extract a value object or dependent entity into its own table, you must assign it an artificial surrogate key—an auto-incremented integer or UUID that has no meaning in your domain. This surrogate exists solely because normalization required the value object to live independently.

The surrogate key is not modeling your domain. It is compensating for a storage decision.

This normalization has consequences for consistency and concurrency

In most RDBMS implementations, the physical model mirrors the logical one: the default unit of atomicity and locking is the row, not the original multi-entity aggregate from your domain model. Since normalization has scattered what was once a single aggregate across multiple tables, the database must provide additional mechanisms to restore consistency across those rows:

Referential integrity constraints (foreign keys) exist to reenforce cross-row consistency that was inherent before decomposition, when related entities were embedded under a common root (the aggregate root).
Isolation levels and explicit locks exist because the natural consistency boundary (the aggregate, in Domain-Driven Design terms) no longer maps to a single lockable unit. The database must escalate from row-level locks to approximate the transactional boundary of the original aggregate root. This is implicit with serializable isolation level (often implemented with range or predicate locks) or explicit in lower isolation levels (SELECT FOR UPDATE on the aggregate root).

What normalization costs you—and what it buys

To be fair, normalization is not without benefits. It eliminates data redundancy, which simplifies updates to shared reference data and reduces storage when the same entity participates in many aggregates (e.g., a product referenced by thousands of order lines). It provides a flexible query model in which any column can serve as a join path, enabling ad hoc analytics that weren't anticipated at design time. The normalized model is also the best choice when you don't know your use cases or data distribution, because it is an abstraction that doesn't depend on access patterns or cardinalities.

But these benefits come at a structural cost:

What normalization removes	What must be added to compensate
Embedding (co-location)	Joins at query time
Structural association	Foreign key constraints
Aggregate-level atomicity	Explicit transactions across tables
Identity through containment	Surrogate keys
Single-unit locking	Isolation levels and lock escalation

Many foreign keys in your schema are evidence of a relationship that was structurally present and had to be re-declared as a constraint after decomposition.

The relational model as a middle ground

This application-agnostic quality is not accidental—it is the relational model's primary design goal. And it positions the relational model as an intermediate representation between two extremes:

Document model (full business aggregates): Optimized for OLTP, where the unit of read and write aligns with the domain's consistency boundary. You read what you need in one operation, and you write what belongs together atomically. The structure serves the application.
Columnar model (individual attributes): Optimized for analytics and ad-hoc queries, where any combination of columns across millions of rows can be scanned, filtered, and aggregated without regard to entity boundaries. The structure serves the query engine.

The relational model sits between these two. It doesn't fully optimize for either transactional aggregates or analytical scans, but it provides a general-purpose representation that can serve both reasonably well when the database engines adds physical optimizations. Rows are decomposed enough to avoid redundancy and enable flexible joins, but not so decomposed that individual attributes lose their tuple context. This is why the relational model became the default: when you don't know your access patterns or future use cases, and need a single schema to serve multiple applications, normalization into relations is the safest bet.

But "safest bet" is not the same as "best fit." When you do know your domain, your access patterns, and your consistency boundaries, that middle-ground positioning becomes over-engineering for your transactional workload and under-optimization for your analytical one.

Beyond the aggregates

Everything above describes referential integrity within a domain aggregate—the consistency boundary where entities and value objects are tightly coupled under a single root. But there are relationships between aggregates too, and this is where your software architecture determines whether the database should enforce them:

Domain-Driven Design decouples aggregates so that each team can operate independently within a bounded context. You may reference another aggregate by its root identity, but enforcement is asynchronous—through application events between services—always routing consistency through the aggregate root. In this architecture, cross-aggregate foreign keys in the database would create the tight coupling that DDD explicitly avoids.
Monolithic data models keep all entities tightly coupled. Foreign keys can be defined across the entire schema, and any application module can update any entity. In this world, in-database integrity constraints are the sole guarantor of business invariants, because no architectural boundary prevents inconsistent writes.

The choice is not purely technical—it reflects whether you trust your architecture or your database to enforce boundaries between aggregates.

When the application evolves

This architectural distinction becomes critical when new use cases emerge—and they always do.

In a relational model, evolution means adding columns, adding tables, and adding joins. The normalized schema was over-engineered for the first use case, but that over-engineering pays off as flexibility: a new query path is just a new join condition. Schema changes to existing data require migration scripts—adding columns, backfilling values, updating constraints—but the work is well understood, and the tooling is mature. New use cases accumulate within the same schema without requiring organizational change: no new services to deploy, no event streams to operate, no eventual consistency to reason about. A single team can absorb new requirements by extending the model, and each evolution is a small, incremental step.

An ORM can significantly lower the initial barrier. Tools like Hibernate, ActiveRecord, or SQLAlchemy let developers define entities in application code and automatically generate a normalized schema—making prototyping and MVPs feel almost as frictionless as with a schema-flexible database. But the ORM is an abstraction over decomposition, not an elimination of it. The joins, the transactions, the N+1 query problems are still there—just hidden until load reveals them. An ORM that isn't tuned (lazy loading in loops, missing fetch strategies, implicit transaction boundaries) will generate SQL that defeats the relational model's strengths. The ease of starting must be followed by the discipline of tuning.

However, as the schema grows—more tables, more joins, more constraints, more cross-cutting migrations—the monolithic model becomes harder to reason about, harder to change safely, and harder to split later when organizational growth demands it. Every new use case widens the blast radius of a schema change.

In a document model, evolution follows the bounded context. When a new use case aligns with an existing aggregate, you add fields to the document—and because documents are schema-flexible, existing data doesn't require migration. Old documents simply lack the new field, which the application handles gracefully. But when a new use case crosses aggregate boundaries or introduces a different access pattern—say, analytics across all orders, or a recommendation engine that needs to correlate items across customers—you don't contort your existing domain model to accommodate it. Instead, you create a new bounded context with its own collection and schema optimized for its access pattern, and you feed it through event streaming (change streams, CDC) from the source aggregates.

This comes at a cost, too: you need the infrastructure for event streaming, you must reason about eventual consistency between contexts, and you need organizational maturity to define clean boundaries. For a small team building an MVP, spinning up event pipelines, or managing change stream consumers may be premature architecture.

This is a fundamental difference in how complexity grows:

The monolithic model absorbs complexity within a single schema. New use cases add joins and constraints. The schema becomes harder to reason about, harder to migrate, and harder to split later—but each evolution is a small step that doesn't require organizational change.
The domain model distributes complexity across bounded contexts. Each context remains simple—optimized for its specific use case—and the integration between them is through events. You need to recognize when a new use case doesn't fit the existing bounded context, and spawn a new collection with its own stream, rather than adding multi-document transactions or cross-collection lookups that would compromise the aggregate's integrity.

In practice, this means a document-oriented architecture scales with organizational complexity. Each team owns its bounded context, its schema, and its data. A new analytical use case doesn't require the order service to change—it subscribes to order events and materializes its own read-optimized view. The order aggregate stays clean, stays fast, and stays owned by a single team. Meanwhile, the relational model scales with functional complexity within a single team—allowing a small group serve many use cases from a single schema, provided they can manage its growing weight.

Dimension	Relational model	Document model
Getting started	More upfront schema work	Fast to prototype
Adding use cases	Extend the schema, add joins	Add fields or spawn new bounded contexts
Complexity growth	Accumulates within one schema	Distributes across contexts
Organizational impact	Larger team synchronization	Requires team boundaries
Risk at scale	Monolithic schema becomes rigid	Must continue in event-driven design

The frequency of releases reflects the difference. Monthly migration windows are often considered undesirable in relational databases. With schema-flexible documents, schema evolution happens at the pace of code deployment: weekly or even daily iterations, without migration scripts or downtime windows.

The takeaway

Relationships exist in your domain model regardless of your database choice. A document database preserves them—a document maps to an aggregate where entities and value objects are embedded, with transactions and locks scoped to that boundary. A relational database decomposes them through normalization and reconstructs them through joins, foreign keys, and transaction isolation.

The relational model earns its place as a general-purpose middle ground that scales with functional complexity—enabling a single team to serve many use cases from a single schema without organizational change. The document model scales with organizational complexity—each team owns its bounded context, its aggregate stays clean, and new use cases spawn new contexts connected by events rather than widening a monolithic schema.

The question is not "Does my data have relationships?" (it always does). The question is: "Do I want my database to preserve my aggregate boundaries or decompose them? And when my application evolves, do I want complexity to accumulate in my schema, or to distribute across my architecture?".

Codd's Connection Trap and Oracle's JOIN TO ONE

Franck Pachot — Sat, 02 May 2026 21:52:47 +0000

In a previous post, I explored Codd's connection trap in PostgreSQL and MongoDB — the classic pitfall where joining two independent many-to-many relationships through a shared attribute produces spurious combinations that look like facts but aren't.

The example followed Codd's 1970 suppliers–parts–projects model: we know which suppliers supply which parts, and which projects use which parts, but joining through parts to derive supplier–project relationships is a relational composition — it tells us what could be true, not what is true.

Oracle Database 26ai introduces JOIN TO ONE, a SQL extension that structurally prevents this class of errors. In this post, I'll reproduce Codd's connection trap in Oracle, show how JOIN TO ONE catches it, and demonstrate the correct solutions.

Why This Matters: A Gap in SQL joins

When developers build joins at the application level — fetching a parent row for a given foreign key in PL/SQL or application code — they naturally get safety checks: TOO_MANY_ROWS tells them a lookup that should have been unique returned multiple rows, and NO_DATA_FOUND tells them the expected parent doesn't exist. These exceptions act as guardrails, catching data or logic errors immediately.

But when the same logic moves into a SQL JOIN, those guardrails disappear. A join that silently matches multiple rows simply multiplies the result set — no error, no warning, just quietly wrong numbers. A join that finds no match either drops the row (inner join) or pads it with NULLs (outer join), but never raises an alarm about violated assumptions.

JOIN TO ONE bridges this gap. It brings the equivalent of TOO_MANY_ROWS protection into SQL joins: if a join that you declared as "to one" ever reaches a second row, Oracle raises a runtime error instead of silently corrupting your results. The default outer-join behavior handles the "zero matches" case gracefully (like a NO_DATA_FOUND that returns NULL columns instead of erroring), and you can override it to INNER JOIN TO ONE when the absence of a match should eliminate the row.

A note on naming: JOIN TO ONE is semantically JOIN TO ZERO OR ONE AND ONLY ONE (for the default outer case) or JOIN TO ONE AND ONLY ONE (for the inner case). SQL has never been shy about verbosity, so a more precise name might have been warranted.

Schema & Sample Data

Following Codd's example, and the previous blog post, we have suppliers, parts, projects, and two independent many-to-many relationships — now with quantities to make the consequences of the trap concrete:

CREATE TABLE suppliers (
    supplier_id VARCHAR2(10) PRIMARY KEY
);

CREATE TABLE parts (
    part_id VARCHAR2(10) PRIMARY KEY
);

CREATE TABLE projects (
    project_id VARCHAR2(10) PRIMARY KEY
);

-- Supplier supplies parts
CREATE TABLE supplier_part (
    supplier_id   VARCHAR2(10) REFERENCES suppliers,
    part_id       VARCHAR2(10) REFERENCES parts,
    qty_available INT NOT NULL,
    PRIMARY KEY (supplier_id, part_id)
);

-- Project uses parts
CREATE TABLE project_part (
    project_id VARCHAR2(10) REFERENCES projects,
    part_id    VARCHAR2(10) REFERENCES parts,
    qty_needed INT NOT NULL,
    PRIMARY KEY (project_id, part_id)
);

-- Reference data
INSERT INTO suppliers VALUES ('S1');
INSERT INTO suppliers VALUES ('S2');

INSERT INTO parts VALUES ('P1');
INSERT INTO parts VALUES ('P2');
INSERT INTO parts VALUES ('P3');

INSERT INTO projects VALUES ('Alpha');
INSERT INTO projects VALUES ('Beta'); 

-- S1 supplies P1 (100 units) and P2 (200 units)
-- S2 supplies P2 (150 units) and P3 (300 units)
INSERT INTO supplier_part VALUES ('S1', 'P1', 100);
INSERT INTO supplier_part VALUES ('S1', 'P2', 200);
INSERT INTO supplier_part VALUES ('S2', 'P2', 150);
INSERT INTO supplier_part VALUES ('S2', 'P3', 300);

-- Alpha uses P1 (50 units) and P2 (75 units)
INSERT INTO project_part VALUES ('Alpha', 'P1', 50);
INSERT INTO project_part VALUES ('Alpha', 'P2', 75);

-- Alpha uses P1 (50 units) and P2 (75 units)  
-- Beta  uses P2 (60 units) and P3 (90 units)  
INSERT INTO project_part VALUES ('Alpha', 'P1', 50);  
INSERT INTO project_part VALUES ('Alpha', 'P2', 75);  
INSERT INTO project_part VALUES ('Beta',  'P2', 60);  
INSERT INTO project_part VALUES ('Beta',  'P3', 90); 

COMMIT;

The Connection Trap in Action

A developer wants to know which suppliers are connected to which projects and executes the following query:

SELECT sp.supplier_id,
       pp.project_id,
       sp.part_id,
       sp.qty_available,
       pp.qty_needed
FROM   supplier_part sp
JOIN   project_part pp ON sp.part_id = pp.part_id
ORDER  BY sp.supplier_id, sp.part_id
;

SUPPLIER_ID    PROJECT_ID    PART_ID       QTY_AVAILABLE    QTY_NEEDED
______________ _____________ __________ ________________ _____________
S1             Alpha         P1                      100            50
S1             Alpha         P2                      200            75
S1             Beta          P2                      200            60
S2             Alpha         P2                      150            75
S2             Beta          P2                      150            60
S2             Beta          P3                      300            90

6 rows selected.

6 rows from only 4 supplier-part rows and 4 project-part rows. The query asserts, for example, "S2 supplies P2 to Alpha" — but our data only says S2 can supply P2 and Alpha needs P2. The join inferred relationships through the shared attribute part_id that were never recorded as facts.

As Codd warned in his 1970 paper, this is exactly the connection trap: deriving relationships that were never asserted.

The Damage with Aggregates

Now the developer summarizes:

SELECT sp.supplier_id,
       SUM(sp.qty_available) AS total_available,
       SUM(pp.qty_needed)    AS total_needed
FROM   supplier_part sp
JOIN   project_part pp ON sp.part_id = pp.part_id
GROUP  BY sp.supplier_id
ORDER  BY sp.supplier_id
;

SUPPLIER_ID       TOTAL_AVAILABLE    TOTAL_NEEDED
______________ __________________ _______________
S1                            500             185
S2                            600             225

Compare with the actual totals from each table independently:

SELECT supplier_id, SUM(qty_available) AS total_available  
FROM   supplier_part  
GROUP  BY supplier_id  
ORDER  BY supplier_id;  

SUPPLIER_ID       TOTAL_AVAILABLE
______________ __________________
S1                            300
S2                            450

SELECT project_id, SUM(qty_needed) AS total_needed  
FROM   project_part  
GROUP  BY project_id  
ORDER  BY project_id;  

PROJECT_ID       TOTAL_NEEDED
_____________ _______________
Alpha                     125
Beta                      150

The connection trap inflated both sides:

S1's availability jumped from 300 to 500: P2's 200 was counted twice (once for Alpha, once for Beta)
S2's availability jumped from 450 to 600: P2's 150 was counted twice (once for Alpha, once for Beta)
Needs were scrambled: the 225 attributed to S2 mixes Alpha's and Beta's needs, double-counting P2's demand

The trap corrupts aggregates in whichever direction the data happens to push — inflation, deflation, or both at once — and it does so silently. In application code, a lookup-by-key that returns two rows would raise TOO_MANY_ROWS. In a SQL join, the same situation just silently multiplies your totals.

This explains why, in a data warehouse, we denormalize into a dimensional model, or star schema, with a single fact table and dimension tables. Normalization makes the relational schema unsafe for users who see only the SQL schema, without the details of the domain model or the safeguards provided by the application.

The Join Graph: Why There Is No RWT

Oracle's JOIN TO ONE documentation introduces the concept of a Row-Widened Table (RWT) — a table from which all other tables can be reached through unique (many-to-one) joins, ensuring the query result maps one-to-one to the RWT rows. A query where such an RWT exists is a Row Widening Only Query (RWOQ), and it's almost always what you need for correct results.

Here's the join graph of our broken query:

   supplier_part ──→ parts ←── project_part
         (FK)        (PK)         (FK)

The "parts" table is a parent node reached from two sibling child nodes. This is the chasm trap:

Starting from "supplier_part": the path to "project_part" via "parts" goes many-to-one then one-to-many — not unique
Starting from "project_part': same problem in reverse
Starting from "parts": both children fan out

No table qualifies as a RWT. This is not a RWOQ. The output rows don't map one-to-one to any table's rows.

How JOIN TO ONE Catches the Trap

With Oracle 26ai's JOIN TO ONE, attempting to write this query produces an error:

-- THIS FAILS — and that's exactly what we want  
SELECT sp.supplier_id,  
       pp.project_id,  
       sp.qty_available,  
       pp.qty_needed  
FROM   supplier_part sp  
JOIN TO ONE (parts p, project_part pp); 

Error at Command Line : 6 Column : 23
Error report -
SQL Error: ORA-18641: No join key found for "PROJECT_PART"

JOIN TO ONE requires every table inside the parentheses to be reachable from the leading RWT through a chain of unique joins. The path supplier_part → parts is many-to-one (valid), but parts → project_part is one-to-many (invalid). Oracle detects that part_id alone is not unique in project_part (the PK is (project_id, part_id)) and blocks the query.

Even forcing an explicit ON clause doesn't help:

SELECT sp.supplier_id,  
       pp.project_id,  
       sp.qty_available,  
       pp.qty_needed 
FROM supplier_part sp
JOIN TO ONE (
    parts p,
    project_part pp ON p.part_id = pp.part_id
);

Error at Command Line : 8 Column : 5
Error report -
SQL Error: ORA-18640: JOIN TO ONE reached multiple rows joining to "PP", resulting in a non-unique join

https://docs.oracle.com/error-help/db/ora-18640/

Oracle either rejects at parse time or raises a runtime error the moment a part matches multiple project_part rows — the SQL equivalent of the TOO_MANY_ROWS exception that application developers rely on. Instead of silently producing wrong numbers for months or years, you get an immediate, clear signal: this query structure doesn't support the one-to-one mapping you're claiming.

The Correct Solutions: two Separate RWOQs

Since the schema doesn't record the three-way relationship, we must run two separate queries, and that's exactly what JOIN TO ONE forces us to do:

-- RWOQ 1: "What can each supplier supply?"  
-- RWT = supplier_part → unique joins to suppliers and parts  
SELECT sp.supplier_id,  
       sp.part_id,  
       sp.qty_available  
FROM   supplier_part sp  
JOIN TO ONE (suppliers s, parts p)  
ORDER  BY sp.supplier_id, sp.part_id;  

SUPPLIER_ID    PART_ID       QTY_AVAILABLE
______________ __________ ________________
S1             P1                      100
S1             P2                      200
S2             P2                      150
S2             P3                      300

-- RWOQ 2: "What does each project need?"  
-- RWT = project_part → unique joins to projects and parts  
SELECT pp.project_id,  
       pp.part_id,  
       pp.qty_needed  
FROM   project_part pp  
JOIN TO ONE (projects j, parts p)  
ORDER  BY pp.project_id, pp.part_id;  

PROJECT_ID    PART_ID       QTY_NEEDED
_____________ __________ _____________
Alpha         P1                    50
Alpha         P2                    75
Beta          P2                    60
Beta          P3                    90

Both are valid RWOQs. Clean star-shaped join graphs. No spurious combinations. Aggregates on qty_available or qty_needed are guaranteed correct.

Conclusion

Codd identified the connection trap in 1970: inferring relationships from shared attributes produces combinations that could be true, not combinations that are true. Over fifty years later, this trap remains one of the most common sources of silently wrong SQL — aggregates that are "slightly off," duplicates masked by DISTINCT, totals that nobody questions because they look plausible.

Application developers have long relied on TOO_MANY_ROWS and NO_DATA_FOUND exceptions to catch violated uniqueness assumptions in procedural lookups. But the moment those lookups become SQL joins, the safety net vanishes — a many-to-one assumption that silently becomes many-to-many just multiplies rows without complaint.

Oracle's JOIN TO ONE in Database 26ai brings that safety net back into SQL:

	Traditional JOIN	JOIN TO ONE
Connection trap	⚠️ Silently produces wrong results	⛔️ Blocked at parse/runtime
Row multiplication	⚠️ Cartesian per shared parent	⛔️ Prevented by RWOQ enforcement
Aggregates	⚠️ Inflated or deflated silently	✅ Guaranteed by one-to-one mapping
Equivalent of `TOO_MANY_ROWS`	❌ Not available in joins	🛑 Runtime error on violated uniqueness
Developer awareness	⚠️ Can go unnoticed for years	🛑 Immediate error

The rule is the same whether you use normalized relations, star schemas, or document models: if a relationship is a fact, it must be stored as one — not derived through joins. JOIN TO ONE ensures that when you do join, the result stays faithful to the facts your schema actually records, or the query fails.

If you think SQL databases, normalization, and referential integrity automatically protect data consistency better than denormalized models, this is proof that they do not. A document model can preserve business invariants by storing them consistently, whereas normalization can break them across multiple tables to be joined. The relational model is an abstraction that simplifies data relationships and can hide business invariants that exist in the domain model and the application. Applications must then compensate by writing safer queries, often at a performance cost by running multiple queries. The new JOIN TO ONE syntax helps SQL users find the right balance by declaring their intent: to look up additional columns from dimensions without changing the number of fact rows.

If you're used to MongoDB, this RWOQ (Row Widening Only Query) is simply a $lookup. It doesn't change the cardinality, but adds an array that can be:

an empty array (similar to NO_DATA_FOUND and LEFT OUTER JOIN)
a single-item array (as expected with JOIN TO ONE)
a multi-item array (equivalent to TOO_MANY_ROWS if unexpected) There's no risk of silent row multiplication, because when that expansion is expected, it must be made explicit with $unwind.

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.