InterviewKata
System design · Module 11 of 29 · Level 3: Data

Relational data and modeling

The relational database is the default home for an application's data, and for good reason: it keeps data consistent, answers questions you didn't plan for, and one well-run instance carries most products a long way. This module covers modeling data as tables, the keys and relationships that connect them, when to normalise and when to copy, what a transaction guarantees, and how to change a schema without downtime.

New to this?Start with B4 · Storing data in Level 0; this module assumes it.

1. The idea

A well-run library keeps one card per book, one card per member, and a loans register that just says "member 7 has book 42 since Tuesday". It never writes the member's address on every loan slip. When a member moves, one card changes. And a librarian can answer any question ("which books has this member borrowed this year?") by connecting the cards.

Relationships

KindExampleHow it's stored
One-to-many (1:N)A user has many postsposts.user_id foreign key
Many-to-many (N:M)Users like many posts; posts are liked by many usersA join table likes(user_id, post_id) with a composite primary key
One-to-one (1:1)A user has one settings recordUsually the same table; a separate one only to split rarely used or sensitive columns

ACID in one line each

2. How it works

Modeling a ticket booking app

Press Play to go from requirements to tables, one access pattern at a time.

Normalise first, denormalise on purpose

Normalisation means storing each fact once. In practice that's the "third normal form" rule of thumb: every column describes the row's key, the whole key, and nothing but the key. The benefit: an update changes one place, so data can't contradict itself (try it in section 3).

Denormalisation means deliberately copying data to make reads faster: a like_count column on posts instead of counting the likes table, or the event name copied onto a ticket. It's the right call when reads vastly outnumber writes and the copy can be kept in step (in the same transaction, or by a background job if slight lag is fine). Do it for a measured reason, not by default.

A query, from SQL to rows

-- "My upcoming bookings", needed on every app launch
SELECT b.id, e.name, e.starts_at, s.label
FROM bookings b
JOIN events e ON e.id = b.event_id
JOIN seats  s ON s.id = b.seat_id
WHERE b.user_id = $1 AND e.starts_at > now()
ORDER BY e.starts_at
LIMIT 20;

The database's query planner decides how: it finds the user's bookings using an index on bookings(user_id), looks up each event and seat by primary key, filters and sorts. EXPLAIN ANALYZE shows the plan it chose and the time each step took. Indexes are Module 12.

Changing the schema without downtime

Old and new versions of your code run side by side during a deploy, so a schema change must work with both. The standard method is expand and contract:

Beware operations that lock a big table for minutes (some column type changes, adding an index without CONCURRENTLY in PostgreSQL). Tools such as gh-ost, pgroll and pg-osc exist to do large changes online.

Data modelling in the interview

The bar is "good enough", not a full schema. Data modelling shows up twice: you list the core entities during requirements, then sketch the tables beside the database box in the high-level design. Tie every choice to one of three drivers, out loud:

When you add the database, run this checklist:

  1. Pick the database type (relational unless there's a reason, Module 13).
  2. List the columns each entity needs for the functional requirements, and only those.
  3. Mark primary and foreign keys.
  4. Mark indexed columns, each tied to an endpoint's query.
  5. Decide whether anything must be denormalised.
  6. Decide whether sharding is needed, and on which key (Module 15).
Users:    user_id (pk), name, email (unique), created_at
Posts:    post_id (pk), user_id (fk, index), content, media_urls, created_at (index)
Comments: comment_id (pk), post_id (fk, index), user_id (fk, index), content, created_at

Foreign keys stop orphaned rows but slow writes; very large systems sometimes enforce integrity in the application instead. Use domain names (users, bookings), never abstract ones.

3. Try it yourself

Why store each fact once

Below, the same orders are stored two ways. Change Asha's email in each and see what happens.

One wide table (denormalised)

Customers + orders (normalised)

Name the relationship

4. Trade-offs

Why relational is the default

Transactions, constraints, joins and ad-hoc queries in one mature system, with excellent tooling. For most products, choosing anything else needs a reason you can state.

Where it strains

Very high write rates on one machine, datasets beyond one machine's disk, and joins across huge tables. The answers (replicas, partitioning, sharding, caching) come in Modules 12–15.

Foreign keys: safety vs speed

Enforced foreign keys prevent orphans but add a lookup to every write and make sharding harder. Most systems keep them; some very large ones enforce relationships in code instead.

JSON columns: flexibility with limits

A jsonb column holds varying attributes without schema changes, and can be indexed. Keep core, queried fields as real columns; use JSON for the long tail.

5. In real systems

The technologies up close

The default answer in an interviewPostgreSQL, normalised, with these three indexes. List the core entities early; beside the database box write each table with its primary key, foreign keys, and the indexes the API's queries need. Leave the database only for a reason you can state with numbers.

PostgreSQL

The default · start here

An open-source relational database with transactions, constraints and joins, plus JSON, full-text search, geospatial and vector extensions in the same engine. The interview default: start with it, and justify any departure.

How it works

  • Reads: B-tree indexes by default (the primary key gets one automatically); composite, covering (INCLUDE) and partial (WHERE status = 'active') indexes; GIN indexes for full-text search (tsvector) and jsonb; GiST with PostGIS for radius and polygon queries (ST_DWithin). These combine in one query.
  • Writes: a change updates pages in memory and appends a record to the write-ahead log (WAL). At commit, the WAL is flushed to disk: that sequential write sets commit latency. Data pages reach disk later, in the background. Every extra index adds work to each write.
  • Replication: followers replay the WAL. Asynchronous (the default) is fast but can lose the last writes on failover and lets replicas lag; synchronous waits for a replica. A common mix: one synchronous standby plus asynchronous read replicas.
  • Concurrency: the default isolation, Read Committed, lets two transactions read the same value and both act on it (two bidders both see $90). Fix it with a row lock (SELECT … FOR UPDATE), optimistic versioning, or Serializable isolation (Module 17).

Status today (checked October 2026)

PostgreSQL 18 is the current release; 19 reached release candidate on 15 October 2026, with general availability planned for 19 November. Every cloud runs it as a managed service (RDS and Aurora, Cloud SQL and AlloyDB, Azure). There's no built-in sharding: use Citus, or shard in the application.

Rough single-node throughputOrder of magnitude
Simple indexed readstens of thousands per second per core
Indexed joins over a few tablesthousands to tens of thousands per second
Simple insertsa few thousand per second per core
Multi-table transactionshundreds per second

Starting points, not promises: hardware, row size and indexes move them a lot. Tables get unwieldy past about 100 million rows, and performance drops sharply once the working set no longer fits in memory.

Use it when

  • Anything with relationships, rules and transactions: e-commerce, payments, bookings, inventory
  • Mixed structured and JSON data, text search to tens of millions of documents, geospatial queries
  • Until a measured limit says otherwise

In the interview

PostgreSQL, normalised, with an index on bookings(user_id). When a requirement pushes harder, say how you use it: The purchase runs in one transaction with a row lock on the seat. Scaling writes past a few thousand a second, in order: bigger machine, batching, moving logs and analytics off the hot path, time-partitioned tables, then sharding by the main query key.

🚩 Red flags
  • "It's ACID" as the whole argument. Say how you use it: which transaction, which lock, which constraint.
  • "It scales better" as the reason to leave Postgres. Name the limit with numbers, or stay.
  • Sharding on day one. One node handles far more than most interview prompts need; do the math first (Module 03).
  • Forgetting connections: one process per connection, so hundreds of app servers need a pooler (next block).

Connection poolers (PgBouncer)

Situational · many app servers

A proxy that shares a small set of real database connections among many application connections.

How it works

PostgreSQL forks a process for each connection, so thousands of connections waste memory and CPU. A pooler such as PgBouncer (or RDS Proxy, or the pool built into your framework) keeps, say, 100 real connections and hands one to each transaction as it runs ("transaction pooling"), returning it at commit.

Status today

Standard in production PostgreSQL deployments; managed services offer their own (RDS Proxy, Azure's built-in PgBouncer).

Use it when

  • Many app servers, or autoscaling fleets, each with its own pool
  • Serverless functions, which open connections in bursts

In the interview

One line when the fleet is big: A PgBouncer layer keeps the database at a few hundred connections however many app servers we run.

🚩 Red flags
  • Hundreds of servers × 20 connections straight at one database, unmentioned. That's a real outage pattern.

Deleting data: soft deletes, retention and erasure

The default question nobody asks until legal does

Every design stores data; few say how it leaves. "Delete" can mean hide it, keep it for 30 days, archive it, or make it unrecoverable everywhere, including replicas, caches, search indexes, logs and backups. Choose per table and say so.

How it works

ApproachHowUse for
Hard deleteDELETE the row; foreign keys cascade or blockData with no history value
Soft deleteA deleted_at column; every query filters on it; a partial index keeps lookups fastUndo, trash folders, audit trails
Retention + purgeA scheduled job deletes rows older than N days in small batches; or time partitions you drop wholeLogs, events, sessions
ArchiveMove old rows to cheap object storage (Parquet files), queryable but off the hot databaseOrders older than 2 years
Crypto-shreddingEncrypt each user's data with their own key; delete the key and every copy becomes unreadableErasure that must reach backups and logs
  • Soft deletes have costs: unique constraints must allow a deleted duplicate (a partial unique index WHERE deleted_at IS NULL in PostgreSQL), every query and join must remember the filter, and the table keeps growing until something purges it.
  • Big deletes hurt: one DELETE of 50 million rows holds locks, bloats the write-ahead log and lags replicas. Delete in batches of a few thousand, or partition by month and drop the old partition (instant).
  • Distributed stores delete by writing: Cassandra and other LSM stores write a tombstone, removed at compaction. Cassandra keeps tombstones for gc_grace_seconds (default 10 days) so a node that was down can learn of the delete; a node away longer can bring the deleted data back ("zombies"). Lots of tombstones also slow reads.
  • The law: under the GDPR's right to erasure (Article 17), a company must answer a request within one month (extendable by two for complex cases, Article 12(3)). The hard part is finding every copy: replicas, caches, search indexes, the data warehouse, event logs, backups. Crypto-shredding is the usual answer for backups you can't edit.

Status today (checked October 2026)

All of these are standard practice. Checked against the Apache Cassandra compaction docs (tombstones, gc_grace_seconds) and the text of GDPR Articles 12 and 17 (October 2026). Not legal advice: retention periods come from your lawyers and regulators.

Use it when

  • Any design with personal data: say what happens on account deletion
  • High-volume tables that need a retention period
  • Interviews where the interviewer asks about compliance or storage cost

In the interview

Messages are soft-deleted so the sender can undo for 30 seconds, then purged by a batch job. For account deletion, each user's data is encrypted with a per-user key, so deleting the key also covers backups and the event log.

🚩 Red flags
  • "We just delete the row" while copies live in caches, search and backups.
  • Soft deletes without a purge job: the table grows forever and personal data never really leaves.
  • One giant DELETE on a busy production table.

Connection storms, and how big a pool should be

The default · every fleet in front of one database

Databases rarely fall over because the data got big; that happens slowly and you see it coming. They fall over because the number of connections doubles in a minute: during a deploy, after a cache entry expires, or when timeouts turn into retries.

How it works

Why a connection costs so much. PostgreSQL runs one operating-system process per connection, with its own memory for sorting and caching. A few hundred mostly-idle connections already use gigabytes, and every running query competes for the same CPU cores and disks. Past a point, more connections make every query slower, which holds connections longer, which brings in more connections.

How big the pool should be. Little's law (Module 02): queries in flight = queries a second × time per query. A checkout database doing 6,000 queries a second at 4 ms each has about 24 queries running at any moment. A pool of about 30 keeps it busy. A pool of 500 doesn't make it faster: the extra requests just wait inside the database, competing, instead of waiting in a short queue in front of it.

The three usual storms, and the fix for each:

StormWhat happensFix
DeployNew instances open their full pools while old ones are still draining: twice the connections for minutesA pooler in front (below), small per-instance pools, and a gradual rollout
Cache expiryA hot key expires and every instance asks the database the same question at onceOne refresh per key (request coalescing), early refresh, jittered expiry (Module 09)
Retry stormA slow database causes timeouts; clients retry, adding load to the thing that's already slowRetry budgets, backoff with jitter, and a circuit breaker (Module 24)

Transaction pooling has rules. A server connection belongs to a client only for one transaction, so anything that lives longer than a transaction breaks: session SET values, LISTEN, session-level advisory locks, cursors WITH HOLD, and temporary tables kept across transactions. Recent PgBouncer versions do support protocol-level prepared statements in this mode once max_prepared_statements is set.

When it's already on fire, look in this order: how many connections exist and how many are actually running (pg_stat_activity); connections "idle in transaction", which hold locks and block cleanup; queries waiting on locks; then slow query plans, last.

Status today

Facts checked October 2026 against the PgBouncer documentation (pooling modes and the feature table).

Use it when

  • Any fleet of more than a handful of instances in front of one PostgreSQL or MySQL primary
  • Serverless functions or autoscaling groups, which open connections in bursts

In the interview

About 6,000 queries a second at 4 ms is 24 in flight, so the database needs about 30 real connections. PgBouncer in transaction mode holds those; the 60 app instances connect to it, not to Postgres.

🚩 Red flags
  • "Bigger pool, more throughput." Past the in-flight number it's the opposite.
  • Retries without a budget in front of a struggling database.

A backfill that can't corrupt rows

The default · any live data migration

The expand-and-contract figure above has a backfill step. On a busy table it is where most migrations go wrong, because the job and the live application change the same rows at the same time.

How it works

The race. An online shop is replacing orders.placed_at_text (local time as text) with placed_at (a proper UTC timestamp). App v2 already writes both columns; a job fills in the old rows:

Rules that keep a backfill safe:

  • Compute from the row as it is now. Write the new value in the same statement that reads the old one (UPDATE … SET placed_at = convert(placed_at_text) WHERE id BETWEEN …), or update only WHERE placed_at IS NULL. Never carry a value read earlier across to a later write.
  • Make it safe to run twice. A derived value (new = f(old)) can be re-applied; an increment (new = new + x) cannot. The job will be interrupted, so it must restart without harm.
  • Walk the primary key in chunks, and save your place. Store the last id done after each chunk, so a restart resumes. Keyset chunks (id > last ORDER BY id LIMIT 1,000) cost the same at row 90 million as at row 1; OFFSET gets slower every chunk.
  • Slow down on a signal, not a timer. Pause while replica lag or primary CPU is high, so the job yields at peak and runs fast at night.
  • Prove it, by category. Compare old and new columns on every row (or a large sample). Mismatches on old rows shrinking: the backfill is still running. Mismatches on rows written since v2 shipped: a bug in the dual write, and the backfill won't fix it. Mismatches reappearing after reaching zero: something writes the old column by another route, like a nightly admin script.

Status today

Online schema-change tools (gh-ost and pt-online-schema-change for MySQL, pg-osc and pgroll for PostgreSQL) automate copy-and-switch for whole tables; the same rules apply to a backfill you write yourself.

Use it when

  • Any column or table change on data that's being written while you migrate

In the interview

The backfill computes the new column inside the UPDATE from the current row, in keyset chunks with a saved cursor, paused on replica lag. Then a comparison job must show zero mismatches before reads switch over.

🚩 Red flags
  • Read a batch, transform in the app, write it back on a live table.
  • Switching reads without a comparison that reached zero.

6. Remember it

Memory hook: "One fact, one place; connect with keys"Store each fact once, point between tables with keys, and let joins and transactions do the connecting. Copy data only for a measured read problem.
How this hook works: what "One fact, one place; connect with keys" means

What the slogan means, phrase by phrase

"One fact"
A customer's email, an event's start time, a seat's label: each is a single fact.
"one place"
It lives in exactly one row of one table. Updating it is one write, and nothing can contradict it.
"connect with keys"
Other tables refer to it by ID (foreign keys); queries join through those IDs; transactions keep multi-row changes together.

Why this picture fits: it's the library's card system: one card per member, loans that just reference member and book numbers. It scales to any question because nothing is written twice.

The story → the concept

In the libraryIn the database
A drawer of member cardsA table
The member numberThe primary key
"Member 7" written on a loan slipA foreign key
Looking up the member for a loanA join
The loans registerA join table for a many-to-many relationship
A desk copy of today's popular titlesDeliberate denormalisation or a cache

Watch it on an example

  1. An events app stores the venue's address on every ticket row "to save a join".
  2. The venue corrects its address. 400,000 ticket rows now need updating; a batch job fails halfway.
  3. Half the tickets show the old address, and nobody can tell which is right.
  4. With one venues row referenced by ID, it's one update, and the join costs microseconds with an index.

The trap it saves you from

Copying data "for performance" before there's a performance problem, and then spending years keeping the copies in step.

It still works when…

You use a document or wide-column store (Module 13). There you do copy data per query on purpose, but you still decide which copy is the source of truth, and how the others are updated.

7. Common mistakes

Business data as primary keysEmails, usernames and phone numbers change, and every foreign key pointing at them must change too. Use generated IDs and a UNIQUE constraint on the business field.
Lists stuffed into a columntags = "a,b,c" can't be indexed or joined properly. Use a related table (or an array or JSON column with an index, knowingly).
The N+1 queryFetching related rows one at a time in a loop turns one page view into hundreds of queries. Join, or batch with WHERE id IN (...).
Locking migrationsRunning a blocking schema change on a large table during traffic stalls every query on it. Use expand and contract, and online tools.
No connection poolingHundreds of servers each holding idle connections exhaust the database. Pool them.

8. Design exercise (30 minutes)

  1. Model it. A food delivery app: customers, restaurants, menu items, orders with several items, couriers, ratings. Write each table with its primary key, foreign keys and the two or three most important columns.
  2. Find the N:M. Which relationships need a join table? What's its primary key?
  3. Index the access patterns. "A customer's last 10 orders", "a restaurant's open orders", "a courier's current delivery": which index serves each?
  4. Denormalise one thing on purpose. Pick a value you'd copy for read speed, say how it's kept in step, and what happens if it lags.
  5. Try it (optional). docker run -e POSTGRES_PASSWORD=pw -p 5432:5432 postgres:18, create two tables, insert rows, and run EXPLAIN ANALYZE on a join before and after adding an index.

9. Self-check quiz

How do you store "students enrol in many courses, courses have many students"?

Many-to-many needs a join table, e.g. enrolments(student_id, course_id), primary key on both.

What's the main risk of denormalised data?

Copies must be updated together. Miss one and the data contradicts itself.

A transfer debits one account and credits another. Which ACID property ensures you never get only one of the two?

Atomicity: all of a transaction's changes commit together, or none do.

How do you rename a column used by running code without downtime?

Expand and contract: both versions of the code keep working at every step.

Two bidders read the current high bid ($90) at the same moment in PostgreSQL's default isolation, and both write a higher bid. What prevents the lost update?

Read Committed lets both transactions read $90. Lock the auction row, or update only WHERE the version is unchanged.

Which reason for leaving PostgreSQL is weakest in an interview?

Name the limit with numbers. A vague "scales better" invites the follow-up you can't answer.

You must make a user's data unrecoverable, including in 30 days of immutable backups. What's the usual technique?

Crypto-shredding: destroying one key makes every copy unreadable without touching the backups.

With soft deletes, how do you keep usernames unique among live accounts but allow a deleted one to be reused?

A normal unique index would block reuse; app-only checks race. A partial index covers only live rows.

A database runs 3,000 queries a second, each taking about 10 ms. Roughly how many connections does it need?

Little's law: 3,000 × 0.01 s = 30 in flight. A much bigger pool only moves the waiting inside the database.

Why can a backfill corrupt rows even when the app's dual write is correct?

Compute the new value in the same statement that reads the old one, so there's no gap.

10. Go deeper

ResourceTimeWhy
Designing Data-Intensive Applications (Martin Kleppmann), chapter 21.5 hRelational vs document models and how query languages shape them; the standard reference book.
The PostgreSQL tutorial1 hTables, joins, foreign keys and transactions, hands-on.
Using EXPLAIN (PostgreSQL docs)30 minHow to read a query plan, the most useful database skill at work.
Data modeling (Hello Interview)20 minHow much modeling an interview needs, and how to present it.