System Design Data

Data Modeling, Normalization & Schema Evolution

You open Instagram and the feed must paint posts with captions, authors, and like counts in one round trip. A perfectly normalized ER diagram may need five joins; a denormalized document may paint fast, until an author renames and millions of copies must update.

Data modeling decides how facts are grouped, joined, and changed over time. reduces duplication; speeds reads; schema evolution lets you ship changes without stopping the world.

Model journey

Try it: walk expand, migrate, and contract without breaking readers

Flip writer and reader versions across phases. Expand keeps new columns optional. Migrate backfills and dual-reads. Contract drops legacy fields only after every consumer upgraded. The status line names the compatibility class you would assert in code review.

Compatibility verdict

Backward compatible: v1 rows only.

Phase: expand
Writers: v1
Readers: v1
Backfill: 0%

Phase expand · writers v2 off · readers v2 off · backfill 0%. Backward compatible: v1 rows only. Expand: add optional display_name column. Old writers and readers keep working.

Never contract on day one. Adding a required field in one deploy breaks old binaries still deployed during rolling updates.

Backfill is a workload. Throttle it like any other job; watch hot keys and replication delay while old and new shapes coexist.

What actually happens when the shape must change

Product queries change faster than entity diagrams. A marketplace that once listed "shipping address" as a free-text string now needs structured street, city, postcode, and tax region, while 800 million order rows still hold the old string, and both N and N−1 app versions must keep serving.

Interviews reward naming entities, but production rewards : what you read together, how hot keys skew, and how migrations roll forward while replicas still serve traffic.

The naive claim is a one-night big-bang: ALTER TABLE … RENAME, set NOT NULL, rewrite every row, deploy new code the same night. A full-table rewrite takes a long Access Exclusive lock (or floods WAL). Replicas lag past the read-your-writes budget. Old pods still expect the old column name. Checkout returns 500s for half an hour: the schema is "correct" and the product is down.

How to choose a model

After you see why big-bang locks fail, pick shape and migration steps from the access path, not from table names alone.

1. Is the path read-heavy with stable joins? Denormalize or precompute projections.

2. Do shared facts change often? Prefer plus caches.

3. Can you migrate online? If not, redesign the change into expand-contract steps.

Simple rule of thumb

Start from read/write paths and invariants, not table names. Denormalize where the read:write ratio and join cost justify the fan-out.

From problem to mechanism: expand → migrate → contract

The choice questions above become a migration playbook once the correctness bar is clear.

Correctness invariant

Every migration phase must be readable by the currently deployed and immediately previous application versions. A must be resumable and must not overwrite a newer user write. Until contract completes, the old shape remains a valid source of truth for any consumer still on it.

Once the invariant is clear, follows. Expand adds a nullable or optional new shape without rewriting meaning under the same name. Deploy dual-compatible code that can read old and write both (). Run a version-guarded . Cut readers over with fallback telemetry. Only then contract: drop the old column after zero use.

Measure before denormalizing for speed. Prefer expand-contract over big-bang locks at peak.

Expand-contract (SQL)

Add nullable column (or shadow table).
Dual-write or backfill job with version guards.
Switch readers to new column; keep fallback.
Drop old column only after zero use and export consumers migrate.

Compatible events (async)

Compatible schema evolution depends on both writer and reader rules. An added field helps only if old readers tolerate it and new readers define what its absence means in old records. Protobuf field numbers must retain their meanings and removed numbers should not be reused; other formats have their own compatibility rules. Upcasting should translate an old event into the new in-memory shape without rewriting the immutable event or inventing unavailable historical information.

Worked example: author rename fan-out vs structured address backfill

With expand-contract in mind, compare denormalized feed paint against an 800M-row address migration (see Numbers to know).

Feed items denormalize author_name. An influencer with 40M posts renames. Separately, orders migrate address_v1 string → address_v2 JSON for 800M rows.

Rename write cost

40M updates × ~200 B ≈ 8 GB update payload before log/index overhead

Forty million derived author-name updates at 20,000 rows per second take about 2,000 seconds, or 33 minutes, even before contention and throttling. The eight-gigabyte figure is an illustrative payload volume at 200 bytes per update, not an exact write-ahead-log prediction: indexes, page images, replicas, and storage format can amplify writes. Keep the canonical author identity separate so the rename can succeed while derived displays catch up within a stated freshness target.

Backfill wall time

800M / 20K rows/s ≈ 11.1 h

Throttle the backfill when replica lag or user-query latency rises. Persist a cursor after each committed batch, and condition each update on the source row version that the batch actually read. A migration marker tracks progress but cannot by itself detect a concurrent user edit unless that edit also advances the checked version.

Join vs denorm read

5 joins × 2 ms ≈ 10 ms vs 1 fetch × 2 ms

If measurement shows a query-shape change removes eight milliseconds of database elapsed time per request, 50,000 requests per second removes roughly 400 seconds of aggregate database occupancy per second. That is not automatically 400 CPU cores: the original time may include I/O, locks, and network wait. Joins also do not universally cost two milliseconds each. Benchmark the real query plan and use the result to compare read savings against update fan-out.

Numbers to know

40M denormalized posts × ~200 B ≈ 8 GB update payload before log/index overhead for one influencer rename.
20K row updates/s → ~33 minutes of sustained rename write load.
800M address rows / 20K rows/s ≈ 11.1 h backfill wall time.
5 joins × 2 ms ≈ 10 ms vs 1 denorm fetch × 2 ms for feed paint.
50K feed reads/s × 8 ms saved ≈ 400 aggregate elapsed-seconds/s avoided; CPU savings require separate measurement.

Decision: denormalize feed paint paths; keep author identity normalized as source of truth with a repair job. For address, use expand-contract with dual-compatible binaries, never a single-night NOT NULL rewrite (see Numbers to know above).

Normalization vs denormalization

Normalization stores each shared fact under a clear owner, such as an author name in the author record. Denormalization copies that fact into records shaped for reads, reducing joins at the cost of keeping copies synchronized. Some copied data is intentionally historical: an order’s shipping address or price at purchase may need to remain unchanged after the customer edits their profile. Decide whether a field means “current shared fact” or “fact captured at this event” before designing a repair job.

Approach	Wins	Costs
3NF relational	Single source per fact, easier updates	Joins at read time
Denormalized document	One read fetches feed item + author	Write amplification on author rename
Wide event + projections	Multiple read shapes from one log	Projection lag and rebuild ops

When expand-contract still breaks

Even with the numbers above, backfill races, premature drops, and denormalized drift put the improved design at risk again.

Expand deploys

Add address_v2 without making old readers depend on it, then deploy code that can understand both representations. While old binaries can still update only address_v1, the migration must account for those writes through compatible write logic, a capture mechanism, or delaying cutover until old writers are gone. Simply declaring new pods “dual-write” does not synchronize changes made by old pods. Track which representation remains authoritative in every phase.

Backfill floods WAL

Large batches push replica lag past the read-your-writes SLO. Interactive checkout reads stale addresses on replicas.

Response: auto-throttle or pause; durable cursor keeps progress.

User edit races backfill

A backfill reads address_v1 and row version 7, converts the address, then pauses. The user saves a newer address at version 8. The backfill must update address_v2 only if the row still matches the version it read; checking only a separate migration-version flag may miss this concurrent edit. When the comparison fails, reread current source data and recompute instead of overwriting the newer address with a stale conversion.

Response: conditional migration_version / row version check; retry from current source.

Premature contract

Ops drops address_v1 while a nightly export and one canary pod still read it → batch failures and 500s.

Response: require zero fallback-read rate + consumer inventory before contract; never emergency reverse of a destructive drop.

ASCII sketch
  expand ──► dual-write ──► backfill ──► cutover reads ──► contract
                 │              │               │
                 │              ▼               ▼
                 │         lag / race      fallback > 0
                 │              │               │
                 └──────── pause / version guard / delay drop

Denormalized drift

Repair jobs lag; feed shows a stale author name for hours. Need reconciliation SLOs, not hope.

Index tax

Each secondary index adds writes, storage, and maintenance work, but the multiplier depends on indexed key size, included columns, storage engine, and update pattern. An index on a low-cardinality or monotonically increasing key can concentrate traffic even when the base table is well partitioned. Add indexes for demonstrated query paths and measure their write amplification rather than assuming every index exactly doubles cost.

JSON flexibility trap

Unconstrained document fields speed experiments but defeat indexes, constraints, and migration discipline.

Modeling worksheet

Top 3 read queries and their p99 latency budget.
Write rate and fan-out if one row change touches N derived rows.
Hot key risk: celebrity user, global counter, single shard?
Retention: does history grow unbounded?
Migration: can you add a column online without a full table lock?

Trade-offs and what to watch

Normalization simplifies writes; denormalization simplifies reads at fan-out cost.
Shadow table dual-read: safer rollback, double storage, catch-up to operate.
Watch top-key QPS, replica lag, backfill rate, fallback-read rate.
Kill switch: pause writer/backfill; do not drop the old shape under fire.

Query-first data design

What production needs to hold

A production design needs a way to recognize trouble and a rule it preserves while recovering. A trigger is the measurement or event that starts a response. An invariant is a statement that must remain true, such as never confirming the same reservation to two customers.

Trigger metric or condition

Revisit the model when a critical access pattern needs expensive joins or fan-out, a partition becomes hot, an index dominates write cost, or a schema change cannot be deployed independently.

Correctness invariant

Every migration phase must be readable by the currently deployed and immediately previous application versions; backfill must be resumable and must not overwrite a newer user write.

Zero-downtime expand, backfill, cutover, contract

Expand schema

nullable / optional

Dual compatible code

old + new shapes

Backfill

version-guarded batches

Cut over reads

observe fallback

Contract old

after zero use

Two domain modeling exercises

Marketplace: orders need customer history, seller fulfilment queues, and inventory reservation. Chat: messages need ordered conversation pagination, unread counts, and retention by tenant. Both must evolve without a global maintenance window.

Marketplace indexes

(customer_id, created_at, id) + (seller_id, status, created_at)

Two query-specific composite indexes avoid one giant index. Inventory uses a conditional atomic update so reserved ≤ available; order rows keep immutable price snapshots.

Chat partition key

conversation_id + time bucket

A celebrity channel would hot-spot one partition, so large conversations add a bucket while a directory records ranges. Cursor = (sent_at, message_id) gives stable pagination.

Backfill load

800M rows / 20K rows/s ≈ 11.1 hours

Throttle by replica lag and p99 latency. Each batch records a cursor and updates only when migration_version is older, protecting concurrent writes.

Decision

The order migration adds shipping_address_v2 as optional, writes both versions, backfills in resumable key ranges, switches readers with fallback telemetry, verifies counts/checksums, then removes v1 only after every service and export consumer stops reading it.

How the mechanism holds

Follow how each part protects the rule above. The mechanism explains what the system does; the reasoning explains why that action is sufficient under the stated assumptions. If an assumption changes, the guarantee may change too.

Access-pattern worksheet

For each query record predicate, sort, limit, consistency, p99, frequency, and expected cardinality. Derive partition and index keys from those facts, then price write amplification.

Why it holds: EXPLAIN or a representative benchmark shows the intended index and bounded rows scanned.

Hot-key analysis

Model skew, not only averages. Track top-key QPS, partition CPU, and size. Split time ranges, isolate whale entities, or add controlled salting with a read-fan-out budget.

Why it holds: A top-tenant load test remains within the hottest-partition SLO.

Compatibility rules

Database: add nullable/default-safe fields first. Events: readers tolerate unknown optional fields. Documents: schema_version chooses validation and upcast. Never change meaning under the same name.

Why it holds: N and N−1 binaries pass against old, mixed, and new records.

Recovery-aware backfill

Use small idempotent batches, durable cursor, rate control, version guard, validation totals, and a kill switch. Separate schema rollback from data rollback because destructive transforms may be irreversible.

Why it holds: Kill and restart at any batch; no row is lost or overwrites a newer value.

Migration failure: replica lag rises

Expand is deployed

Old application ignores the optional v2 column; new code dual-writes both.

Response: Confirm no table rewrite or long lock before enabling writes.

Backfill starts

Large batches increase WAL and replica lag beyond the read-your-writes budget.

Response: Throttle or pause automatically; durable cursor preserves progress.

Concurrent user edit wins

A user updates the address while an old snapshot is being backfilled.

Response: Conditional migration_version check prevents stale overwrite; retry from current source.

Cutover finds mismatch

Fallback reads show 0.03% missing v2 values.

Response: Keep reading v1, repair the gap, and delay contract. No emergency reverse migration is needed.

How to choose among alternatives

Normalize vs denormalize

Choose when: Normalize ownership and update-heavy facts; denormalize bounded read views with reconciliation.

Caution: Price fan-out, staleness, privacy deletion, and rebuild before copying data.

Secondary index vs projection

Choose when: Use an index for same-store selective access; use a projection for a distinct shape, lifecycle, or scaling boundary.

Caution: Every index and projection multiplies writes and needs repair observability.

In-place vs shadow migration

Choose when: Use in-place additive changes for small compatible moves; shadow tables for transformations needing validation and instant rollback.

Caution: Shadow copies double storage and require a catch-up path.

Running it day to day

Measurements tell you what is happening; alerts tell an operator when action is needed. A rollout plan limits how many users encounter a change before it is checked. A runbook records the steps to diagnose and recover from a known failure.

Measure

top-key QPS/bytes and rows scanned per query
index hit, write amplification, replica lag, and lock wait
backfill rate, remaining rows, fallback-read rate, and mismatch count

Alert

migration lock exceeds safe duration
replica lag or p99 crosses throttle gate
new-shape validation or fallback rate regresses

Roll out safely

test N/N−1 compatibility
expand and deploy tolerant readers before writers
backfill, dual-read verify, cut over, wait, then contract

Runbook

disable writer/backfill flag, not the whole service
keep old read path and cursor intact
repair mismatches, resume slowly, and postpone destructive contract

Practice scenario

Model a multi-seller order system and migrate a single address string into a structured address without downtime.

Questions worth asking next

Which indexes serve buyers and sellers?
What prevents a stale backfill overwrite?
How is rollback different after contract?

A solid answer sketch

Keep immutable order price/address snapshots, index buyer history and seller status queues separately, and reserve inventory atomically. Add optional v2, deploy dual-compatible code, backfill with version guards, observe fallback reads, cut over, then contract after all consumers are proven idle.

Common mistakes

• one partition key chosen from average traffic
• a NOT NULL column or default that rewrites the whole table
• dropping the old shape before batch exports and replicas migrate

Authoritative references

PostgreSQL ALTER TABLE documentation Apache Avro schema resolution specification

Last reviewed: 6 August 2026

Choosing and explaining data modeling and schema evolution

A complete explanation connects the mechanism to a requirement. Describe what improves when you introduce it, which new failure cases become possible, and how you would know that the trade-off is worthwhile.

Explain the core idea

Start from read/write paths and invariants, not table names.

Denormalize where read:write ratio and join cost justify it.

Plan expand-contract migrations before the schema is frozen in production.

When it belongs

Feed, timeline, or catalog designs need one-fetch reads.

Author or product metadata changes fan out to many rows.

Zero-downtime deploys require backward-compatible schema steps.

How it fails

Denormalized copies drift without repair jobs.

Big-bang migration locks table during peak.

Global secondary index doubles write cost unexpectedly.

Trade-offs to weigh

Normalization simplifies writes; denormalization simplifies reads.

Flexible JSON columns speed iteration; indexes and constraints suffer.

Strict schemas catch bugs early; slow product experiments.

Common misunderstandings

Normalizing a read-heavy feed that always joins five tables.

Storing derived counts without invalidation path.

Breaking API consumers when DB migration completes.

Wrap-up

You have walked expand-contract, the rename vs address numbers, and the failure modes. Use these exercises to rehearse the deploy steps before a real migration.

Exercises

Zero-downtime address migration. Migrate a single address string into structured fields for 500M orders with zero downtime. List every deploy step and what N−1 pods must still do.
Celebrity partition. A celebrity channel is one partition key. Propose a salting or time-bucket scheme and compute the read fan-out cost for a 100-message page.
Denorm vs join. Decide: denormalize author name into posts, or join at read time with a cache. Use a concrete read:write ratio and rename frequency to justify the choice.

Topic-specific references

PostgreSQL ALTER TABLE Avro schema resolution Fowler: Parallel Change (expand-contract)

Key takeaway

Schema is a living contract with your queries and your team. Model for access patterns, evolve with expand-contract discipline, and measure fan-out before denormalizing for speed.

Practice and further reading

Use these exercises to connect the lesson to a design decision. State your assumptions, trace what happens to one request, and explain the rule that must remain true even during a failure. That rule is the design's correctness invariant.

Focus for this lesson: Model access patterns and invariants, identify hot keys/index cost, then use expand-backfill-cutover-contract with compatibility and recovery gates.

Explain the decision

Why is a NOT NULL column addition risky on a large live table?

Read the reasoning

Old writers may omit it and validation/backfill can lock or scan heavily; expand with nullable/default-compatible schema, backfill, validate, then contract.

Apply it to a production scenario

A backfill corrupts 0.2% of rows. How do you recover?

Read one approach

Pause by checkpoint, identify versions, restore/recompute from authority, verify dual-read comparison, and keep old schema/path until confidence passes.

Work through the numbers

Backfilling 3 billion rows at 20,000 rows/s takes how long?

Read the worked calculation

150,000 seconds, about 41.7 hours, before throttling, retries, replication lag, and validation.

Reason through a failure

What must expand-contract preserve?

Read the recovery reasoning

Every deployed old/new reader and writer remains compatible at each phase; destructive schema removal waits for observed consumer retirement.

Sources and further study

Continue with Multi-Tenancy & Noisy-Neighbor Isolation.