> 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. 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

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.

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.

Normalisation 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.

In other words, normalization doesn't strengthen relationships—it dissolves them from the schema and defers 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:

1. The aggregate was shattered into four independent tables with no structural cohesion.
2. Foreign keys had to be added (order_id FK) to re-express the parent-child relationship that was previously implicit through embedding.
3. Surrogate keys had to be invented (address_id) because value objects don't have a natural identity—they were identified by their values.
4. Joins are now required to reconstitute what was a single read—SELECT ... FROM orders JOIN order_items JOIN shipping_addresses JOIN payments.
5. Referential integrity constraints must be declared to prevent orphan rows—a problem that could not exist when the data was embedded.
6. Transaction scope must be explicitly managed because inserting an order now means inserting into 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 demanded that the value object 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 re-enforce 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 where any column can become a join path, enabling ad-hoc analytics that weren't anticipated at design time. The normalized model is also the best when you don't know your use cases and data distribution, because it is an abstraction that doesn't depend on access patterns and cardinalities.

But these benefits come at a structural cost:

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, 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, don't know your 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 against existing data require migration scripts—ALTER TABLE ADD COLUMN, 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 lower the initial barrier significantly. Tools like Hibernate, ActiveRecord, or SQLAlchemy let developers define entities in application code and generate the normalized schema automatically—making prototyping and MVPs feel almost as frictionless as 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, its own schema optimized for its own 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—letting a small group serve many use cases from one schema, provided they can manage its growing weight.

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—letting a single team serve many use cases from one 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?"

When talking about TLA+, I keep referring to "abstraction" as the most important thing to learn. And it is about the hardest to learn as well.

But a contradiction has been bugging me. Aren't CS people already supposed to be good at abstraction? Isn't abstraction supposed to be at the root of OS, networking, software engineering? Abstract Data Types (ADTs) are a staple of every in CS curriculum. So why do I (and every other formal methods/modeling person) see such a large skill gap in abstraction, and flag it as the core, make-or-break skill for modeling?

I think I finally get to the root of this cognitive disonance. There are two kinds of "abstraction" conflated under the same umbrella term.

• Modularity abstraction: This is the traditional abstraction taught in CS curricula as ADTs, APIs, layered design, etc. It is all about encapsulation, drawing boundaries, and hiding internals.
• Modeling abstraction: This is what I talk about when I talk about abstraction in the context of modeling. This is the same sense of abstraction mathematicians and physicists when building models for thinking and reasoning. The goal is to find the minimal and most elegant description that preserves the property you care about. It is all about cutting away everything orhtogonal to the essence of that property.

These two couldn't be further apart in terms of their goal! Let me try to explain in the next two sections.

Modularity abstraction hides. Modeling abstraction reduces.

Modularity abstraction is about interfaces that hide internals. Modeling abstraction is about behaviors, and about reducing a system to its minimal behavioral skeleton for the property you care.

Modularity abstraction encapsulates, draws a vertical boundary, and hides the layer below. Modeling abstraction is crosscutting: it slices the system along a behavioral plane and keeps only what is absolutely relevant to the property under investigation, and even then in the form of "what", not "how". This slice usually looks nothing like the system's organization.

Modularity abstraction hides concurrency. Modeling abstraction exposes it.

Modularity abstraction is all about sealing the leaks, hence Joel Spolsky's famous post lamenting that "all abstractions are leaky". [ Note that his list is all about modularity abstraction: TCP (hide IP), string libraries (hide character arrays), file systems (hide spinning disks), virtual memory / flat address space (hide MMU and paging), SQL (hide query plans), NFS / SMB (hide the network), C++ string classes (hide char*). ] Modularity abstraction aspires to hide the interleavings and present operations as if they were atomic. Its goal is to make the module easy to use, but in doing so it forgoes exposing concurrency or efficiency opportunities.

In stark contrast, the modeling abstraction is about identifying what should leak and leveraging it! It exposes the fine-grained actions and orderings, and proves that invariants hold despite the interleavings. The payoff for this work is to harvest the maximum safe concurrency from the system.

Examples of modeling abstraction 

There is an abundance of modeling abstraction in distributed systems field. It feels like almost all protocols are designed this way.

• Lamport logical clocks: throw away wall-clock time, keep happens-before
• Hybrid logical clocks: keep wall-clock and causality, throw away the rest
• TrueTime: time as a bounded-uncertainty interval 
• Consensus: agree on a single decision. The way Lamport designed Paxos is a masterclass in abstraction; from Consensus, Voting, to the final protocol. 
• Linearizability (and really all consistency models ): throw away replication, caching, retries
• Log is the database idea: throw away materialized state as the source of truth; keep only the ordered, append-only sequence of events.
• MapReduce/Spark: throw away orchestration, parallelism, scheduling, and fault tolerance. Keep a DAG of deterministic transforms over partitioned data—and let the framework reconstitute the rest from that skeleton.

This video is brought to you by PodPitch, the AI-powered platform for PR experts. PodPitch is the first and only software that finds, writes, and sends pitches to podcast hosts from your actual email address. Whether or not you’re already pitching podcasts, PodPitch will make sure you get podcast bookings – guaranteed. And so long as PodPitch’s AI keeps confusing me with Kyle Kingsbury, MMA fighter and famous podcast host, some of those bookings… just might be with me.

My name is Kyle Kingsbury, I am not as popular nor ruggedly handsome as Kyle Kingsbury, and I have never hosted a podcast in my life. Now I’m struggling to read books, write interesting questions, and generally making a fool of myself.

Today’s guest is Dave Rossi, Silicon Valley entrepreneur and author of the book “Alphas Die Early”.

One of the PR firms figured out what’s happening and we’ve all had a good laugh about it, but they were contractually obligated to cancel my third guest’s interview, which I was very much looking forward to. Dr. Deborah Rozman of the HeartMath Institute, if you’re out there… I’d be delighted to interview you.

In this blog post, we are going to briefly discuss pg_enhanced_query_logging (PEQL for short), a PostgreSQL extension that produces slow query logs in the same format MySQL and Percona Server users have been feeding into pt-query-digest for years. The idea is simple: reuse the tried-and-true tools and concepts we have been using for performing full … Continued

The post Bringing pt-query-digest-Style Slow Query Analysis to PostgreSQL with pg_enhanced_query_logging appeared first on Percona.

For the last few weeks publicists have bombarded me with emails asking if I’d interview their clients on the Kyle Kingsbury podcast. While my name is Kyle Kingsbury, and I have been a guest on several podcasts, I’ve never hosted one myself. I called one of the PR firms to ask if they might have me confused with the MMA fighter of the same name, but the woman I reached was so friendly and eager to tell me about all the potential guests I might interview and, well… things just got out of hand. Now I’m doing background research, writing questions, learning Riverside, and struggling mightily to avoid swallowing my tongue.

Joining me today and being an extraordinarily good sport about it is Alex Dripchak, sales director, productivity expert, and author of the new book “Maximize”–full of time-management habits that I really should get around to trying one of these days.