Skip to content
AI Engineer Guide.

concept · basics · intermediate · concept 9 of 24

SQL & relational modelling

0 of 24 complete

Why it matters

Relational databases are the default store for application state because they offer constraints, transactions and a planner that can answer questions you did not anticipate. Those features depend on the schema and the queries being written with the planner in mind: an unindexed filter scans the table, a long transaction holds locks, and a query per row turns one page load into hundreds of round trips.

Mental model

It is a library with a catalogue and a rulebook: the catalogue answers questions, and the rulebook stops inconsistent entries. The analogy breaks because the librarian can choose a completely different search strategy for the same question depending on statistics.

How it works

Mechanism, step by stepInteractive
  1. 1Model entities and relationships first, then add constraints for every invariant the application relies on.
  2. 2Write the query, read its plan, and add an index that matches the filter and sort order rather than one column at a time.
  3. 3Choose the isolation level from the anomalies you must prevent, and test the concurrent case explicitly.
  4. 4Keep transactions short and never hold one open across a network call.
  5. 5Make migrations additive first: add nullable columns and new tables, backfill, then switch reads and drop the old path.
  6. 6Detect repeated per-row queries by counting statements per request in a test, not by noticing them in production.

Step 1 of 6

Model entities and relationships first, then add constraints for every invariant the application relies on.

Step 1/6 · step 1

Figure. The mechanism in this page's own steps. The figure adds sequence, not new claims.

What to notice. A step can be read alone, but the order is the mechanism. The control lets you stop on any one.

Text version

The page's steps: 1. Model entities and relationships first, then add constraints for every invariant the appl… 2. Write the query, read its plan, and add an index that matches the filter and sort order r… 3. Choose the isolation level from the anomalies you must prevent, and test the concurrent c… 4. Keep transactions short and never hold one open across a network call. 5. Make migrations additive first: add nullable columns and new tables, backfill, then switc… 6. Detect repeated per-row queries by counting statements per request in a test, not by noti…

  1. Model entities and relationships first, then add constraints for every invariant the application relies on.
  2. Write the query, read its plan, and add an index that matches the filter and sort order rather than one column at a time.
  3. Choose the isolation level from the anomalies you must prevent, and test the concurrent case explicitly.
  4. Keep transactions short and never hold one open across a network call.
  5. Make migrations additive first: add nullable columns and new tables, backfill, then switch reads and drop the old path.
  6. Detect repeated per-row queries by counting statements per request in a test, not by noticing them in production.

Trade-offs

ChoiceGood forCostFails when
Normalised schemaNo duplicated facts and simple updatesMore joins per readRead volume dominates and joins are hot
Denormalised columnsFewer joins and faster readsUpdate anomaliesSeveral writers change the same fact
Strict constraintsBad data is rejected earlyMigration and backfill complexityLegacy data violates the constraint
Long transactionSimple atomic logicLocks and bloatThe transaction spans an external call
Trade-off mapInteractive

Good for

No duplicated facts and simple updates

Cost

More joins per read

Fails when

Read volume dominates and joins are hot

  1. Read volume dominates and joins are hot
  2. Several writers change the same fact
  3. Legacy data violates the constraint
  4. The transaction spans an external call

Figure. Each choice with what it buys, what it costs, and the case where it loses.

What to notice. The fourth column is the decision: if the losing case describes your situation, the choice is wrong.

Text version

Four trade-offs: Normalised schema is good for No duplicated facts and simple updates, costs More joins per read, and fails when Read volume dominates and joins are hot. Denormalised columns is good for Fewer joins and faster reads, costs Update anomalies, and fails when Several writers change the same fact. Strict constraints is good for Bad data is rejected early, costs Migration and backfill complexity, and fails when Legacy data violates the constraint. Long transaction is good for Simple atomic logic, costs Locks and bloat, and fails when The transaction spans an external call

Failure modes

  • Missing index on a filter. The plan switches to a sequential scan as the table grows and latency jumps. Debug: run the query with an execution plan and compare rows scanned with rows returned.
  • Query per row. One request issues hundreds of small queries and connection pool wait rises. Debug: count statements per request in a test or in the database log.
  • Lock contention. A long transaction blocks writers and requests queue behind it. Debug: query the lock views for waiting sessions and the statement holding the lock.

In production

Monitor slow queries, lock waits and connection pool saturation, and review the plans of the top queries after a data growth milestone. Keep a migration rollback plan and test it on a copy with production-sized data.

Common misconceptions

People think: Adding an index always makes queries faster.

Actually: Every index costs write throughput and storage, and the planner may ignore an index that does not match the predicate.

People think: The default isolation level prevents all anomalies.

Actually: Each level permits specific anomalies; which ones matter is a property of your workload.

Interview angle

Read the plan, match the index to the predicate and sort, and keep transactions short. Mention backward-compatible migrations. Adding an index to every column, without looking at what the planner did, is the miss.

Sources