Engineering Atlas › System Design Interviews

Choosing tables and schemas in an interview

14 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

Start from access patterns

Write the queries as sentences with their rates before drawing tables. For a messaging app: "fetch the latest 50 messages in a conversation, 200k per second"; "append a message, 50k per second"; "list a user's conversations ordered by last activity, 30k per second"; "search messages by keyword, rare". Each sentence suggests a key. The first wants (conversation_id, message_id) with messages sorted by time inside the conversation. The third wants a per-user table ordered by last_activity. The fourth does not belong in the primary store at all; it goes to a search index fed by change events.

Schema, keys and constraints

In a relational design, normalise first so each fact lives in one place, then denormalise the specific paths that are hot. Use surrogate keys (64-bit IDs) for rows and enforce natural uniqueness with unique constraints (UNIQUE (email)). Time-ordered IDs such as Snowflake-style IDs or UUIDv7 keep inserts at the right edge of the B-tree, which keeps the hot pages in memory; random UUIDv4 keys scatter inserts across the whole index. UUIDv7 is now standard: RFC 9562 (2024) replaced RFC 4122 and tells implementations to prefer it over the older time-based versions (RFC 9562), and PostgreSQL 18 (September 2025) added a built-in uuidv7() function (PostgreSQL 18 release notes).

Indexes

An index serves a query when the query's equality columns form a prefix of the index, optionally followed by one range column. The same prefix rule decides whether ORDER BY can be read in index order without a sort. So for "latest orders for a user", the index is (user_id, created_at DESC), not two separate indexes. The rule has softened slightly: PostgreSQL 18 and MySQL 8.0.13 and later can use a skip scan, probing each distinct value of a leading column the query does not filter on. That helps when the leading column has few distinct values, such as (status, created_at), but it is a fallback, not a reason to design indexes in the wrong order (PostgreSQL 18 release notes, MySQL 8.0.13 release notes). A covering index includes the selected columns (INCLUDE in Postgres) so the query never touches the table. Every index costs space and a write on every insert and on updates to indexed columns, so a write-heavy table with eight indexes pays roughly eight extra page modifications per insert.

Partition keys

When one node is not enough, pick a partition key so that the most frequent queries hit one partition and the load spreads evenly. user_id is common for user-centric apps; conversation_id for messaging; tenant_id for SaaS (with large tenants split further). Avoid keys with low cardinality or temporal skew, such as country or date, which put all of today's writes on one shard. Cross-partition queries become scatter-gather or need a secondary store. Each partition also has a throughput ceiling: a DynamoDB partition delivers at most 3,000 read units and 1,000 write units per second (AWS docs). Size is bounded too, though less strictly than people assume: DynamoDB caps an item collection at 10 GB only on tables with local secondary indexes (AWS docs), while in Cassandra very large partitions slow reads, repairs and compaction. So a key that grows without limit (all events for a popular device forever) needs a bucket such as (device_id, day). Discord did exactly this for messages, keying them by ((channel_id, bucket), message_id) with about 10 days per bucket to keep partitions under 100 MB (Discord).

What interviewers look for, and a repeatable approach

  1. List the top queries and writes with rough QPS.
  2. Choose the store family (relational unless something rules it out).
  3. Sketch tables with primary keys, then add one index per important query.
  4. Pick the partition key and check it against hot spots and the top queries.
  5. Mention denormalised copies and how they are maintained, and how data grows.

Common mistakes

Down the stack

Layer 1The contract

A database design promises: uniqueness where constraints exist; atomicity within a transaction (single-partition in most distributed stores, multi-row in a single relational node or in systems such as Spanner or CockroachDB); an isolation level (Postgres defaults to Read Committed, Postgres docs; MySQL InnoDB to Repeatable Read, MySQL docs); and bounded query cost for the listed access patterns, for example "any conversation page in one index range scan of at most 50 rows". Writing these down shows which invariants the schema enforces and which the application must enforce.

Layer 2The mechanism

In Postgres, a table is a heap of 8 KB pages and each B-tree index maps keys to tuple IDs (block, offset). Updates use MVCC: an update writes a new tuple version with new xmin, marks the old one's xmax, and normally inserts new entries in every index. A heap-only tuple (HOT) update avoids new index entries when no indexed column changes and the new version fits on the same page; since PostgreSQL 16 a change to a column covered only by a summarizing (BRIN) index no longer blocks HOT (Postgres docs). That is one reason to keep frequently updated columns out of indexes and to leave free space with fillfactor. Declarative partitioning splits a table by range, list or hash into child tables, so dropping a month of data is a cheap DETACH and DROP rather than a huge DELETE. In DynamoDB, the partition key is hashed to choose a storage partition and the sort key orders items within it; a Query reads one partition key's items in sort order, and a global secondary index is an asynchronously maintained copy with its own keys. Cassandra uses the same idea with partition keys and clustering columns stored sorted in SSTables.

Layer 3The machine

An index lookup in a B-tree of a billion rows touches about four pages; the upper levels are almost always in the buffer pool, so the cost is one or two page reads from disk if cold, roughly 0.1 ms each on NVMe and 5 to 10 ms on a spinning disk. A range scan of 50 adjacent rows in the same index costs one or two leaf pages, while fetching those 50 rows from a heap ordered differently can cost 50 random page reads, which is why clustering and covering indexes matter. Each insert writes the heap page, every index page touched, and WAL records for all of them; WAL is flushed with fsync at commit unless commit batching or synchronous_commit = off relaxes it. Random UUID keys cause each insert to dirty a different leaf page, multiplying page writes and cache misses compared to monotonically increasing keys.

Why it's built this way

Disks and SSDs move data in pages, so storage engines lay out data in pages and design indexes to minimise page touches. Different queries need different orders, so you add indexes, and each copy costs writes. One machine eventually runs out of disk, IOPS or CPU, so data is partitioned, and because cross-partition coordination costs round trips, transactions and queries are kept inside a partition where possible. That is why the partition key ends up mirroring the dominant access pattern.

Diagram

flowchart LR
    Q1["Query: last 50 messages in convo"] --> I1["PK (conversation_id, message_id)"]
    Q2["Query: user's convos by activity"] --> I2["user_conversations (user_id, last_activity)"]
    Q3["Query: keyword search"] --> S["Search index via CDC"]
    W["Write: send message"] --> I1
    W -->|"same transaction or async"| I2
    W -->|"change event"| S
    I1 --> P["Partition key: conversation_id"]
    I2 --> P2["Partition key: user_id"]

Worked example

Design the order store for a marketplace with 5 million orders per day, 100k order reads per second at peak, and a five-year retention requirement.

Volume: 5 million a day is about 58 writes per second on average; assume 10 times that at peak, about 600 per second, comfortable for one Postgres primary. Over five years that is about 9 billion orders. At roughly 500 bytes per order plus about 300 bytes for indexes, that is around 7 TB, too large to keep comfortably on one node with headroom, so plan partitioning now.

Tables: orders(order_id PK, buyer_id, seller_id, status, total_cents, created_at, ...), order_items(order_id, line_no, sku, qty, price_cents, PRIMARY KEY (order_id, line_no)). Queries and indexes: buyer order history uses (buyer_id, created_at DESC); the seller dashboard uses (seller_id, status, created_at DESC); order detail is the primary key.

Partitioning: shard by buyer_id hash, because buyer history and checkout are the hottest paths and order items live with their order. Seller queries become cross-shard, so maintain a seller-side copy (seller_orders partitioned by seller_id) through an outbox and CDC, accepting a second or two of lag on the seller dashboard. Use monthly range partitions inside each shard so data older than 18 months can move to cheaper storage.

In the wild

Trade-offs

OptionStrengthsWeaknessesChoose when
Normalised schemaOne source of truth, easy writesJoins on hot readsTransactional core data
Denormalised copiesOne-lookup readsSync logic, write amplificationHot read paths with known shape
Composite indexServes filter and sort togetherOnly for queries matching the prefixEach top query
Hash partition keyEven spreadRange queries go scatter-gatherPoint lookups by entity ID
Range partition keyEfficient range scans, easy archivingHot latest rangeTime series with bucketing
Global secondary indexAlternate access path at scaleAsynchronous, extra costSecond query pattern in a KV store

Pitfalls in production

Leadership lens

Check yourself

  1. A query filters WHERE user_id = ? AND created_at > ? ORDER BY created_at. Why does an index on (created_at, user_id) serve it poorly?

    Show answer

    The equality column is not the index prefix, so the database must scan all recent rows for every user and filter, instead of seeking straight to one user's range.

  2. Why do random UUIDv4 primary keys hurt insert throughput in a B-tree?

    Show answer

    Each insert lands on a random leaf page, so the working set of dirty pages is the whole index, causing cache misses, more page writes and more splits.

  3. Why might you shard orders by buyer and still keep a seller-partitioned copy?

    Show answer

    Each access pattern wants its own partition key; one copy keeps buyer reads single-shard and the other keeps seller reads single-shard, at the cost of asynchronous sync.

  4. What is a HOT update in Postgres and how does schema design enable it?

    Show answer

    An update that writes a new tuple on the same page without touching indexes; it requires that no indexed column changes and that the page has free space, so keep frequently updated columns unindexed and set a lower fillfactor.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →