Database

Updated Nov 16, 2026 · 5 min read
On this page

    Your shop starts with one Postgres server and everything is fine. A year later, the orders table has 10 million rows, the "my orders" page takes three seconds, and a marketing email sends 20,000 people to the site at once and the database CPU sits at 100%. Nothing is broken in the code. The database is simply being asked to do more work per query, and more queries, than one machine can handle.

    Almost every system design answer has a database at its centre, and most deep dives are about one of three things: finding rows fast, handling more reads, and handling more writes. This article covers the choices you make for each.

    How it works

    Finding rows: indexes

    Without an index, finding order 9,001,234 means reading every row until you hit it. With a B-tree index on order_id, the database walks a short tree: each step cuts the search space by a large factor, so 10 million rows need only about four page reads.

    Full table scan versus B-tree index: without an index the database reads all 10 million rows; with an index on order_id it follows about four nodes to the row in roughly a millisecond.
    Full table scan versus B-tree index: without an index the database reads all 10 million rows; with an index on order_id it follows about four nodes to the row in roughly a millisecond.

    Rules that come up in interviews:

    • Index the columns you filter and sort by. For "a user's orders, newest first", a composite index on (user_id, created_at DESC) serves the whole query from the index.
    • Column order matters. (user_id, created_at) helps queries that filter by user_id; it does not help a query on created_at alone.
    • Every index slows writes a little, because each insert updates every index on the table. Do not index everything.

    Two families of databases

    Relational (SQL)NoSQL (key-value, wide-column, document)
    Data modelTables, rows, joins, foreign keysItems looked up by key; related data stored together
    TransactionsMulti-row ACID transactionsUsually per-item; some offer limited transactions
    ScalingVertical first, then read replicas, then sharding (manual work)Built to partition across many nodes from day one
    QueriesAny query the indexes support, ad hocFast for the access patterns you designed for; others are expensive
    Typical useOrders, payments, users, inventorySessions, events, feeds, time series, very high write rates

    The honest default for most interview problems is a relational database. Switch when the numbers or the access pattern say so: billions of rows written per day, simple lookups by key, or a schema that changes per item.

    Scaling reads: replicas

    A read replica is a copy of the database that receives every change from the primary and serves reads. One primary takes all the writes; several replicas share the reads.

    Primary with two read replicas: the Order Service sends writes to the primary and reads to the replicas; changes are copied asynchronously, so replicas can lag behind by milliseconds to seconds.
    Primary with two read replicas: the Order Service sends writes to the primary and reads to the replicas; changes are copied asynchronously, so replicas can lag behind by milliseconds to seconds.

    Replication is usually asynchronous, so a replica can be slightly behind. If a user updates their address and immediately reloads the page from a replica, they might see the old address. The common fix is to read a user's own recent writes from the primary for a short time (read-your-writes).

    Scaling writes: sharding

    Replicas do not help writes: every write still goes to one primary. When one machine cannot take the write rate or hold the data, you shard: split the rows across several databases by a key, such as user_id. Each shard holds a slice and takes a slice of the writes. See Partitioning / Sharding Basics for how to choose the key.

    Trade-offs and costs

    • Consistency vs availability. A single primary gives you strong consistency for writes. Replicas trade a little freshness for read capacity. Multi-region writes add conflict handling.
    • Joins vs scale. Joins are cheap on one machine and expensive across shards. Sharded designs often denormalize (store the user's name on each order) to avoid cross-shard joins.
    • Indexes vs write speed. Each index makes some reads fast and every write slower, and takes disk space.
    • Flexibility vs predictability. SQL answers new questions with a new query. Key-value stores answer the questions you designed for very fast and others badly.
    • Operations. Sharding, resharding, backups and failover are real work. Managed services reduce it but cost more.

    When to reach for it

    • Relational: money, orders, inventory, anything with invariants across rows ("never sell more than we have"), and anything that needs reporting queries.
    • Key-value / wide-column: lookups by one key at very high volume (sessions, URL mappings, user timelines, IoT readings), and write-heavy event data.
    • Document store: items with varying shape read and written as a whole (a product with different attributes per category).
    • Not a database: large files go to Blob Storage, full-text search to Elasticsearch, hot repeated reads to a Cache.

    Example

    An order system for an online shop. Assumptions: 5 million orders a day (about 60 per second on average, 600 at peak), 50 reads per write, 3 years of history.

    • Choice: Postgres. Orders need transactions (create the order and reserve stock together) and support staff run ad hoc queries.
    • Tables: orders(order_id PK, user_id, status, total, created_at), order_items(order_id, product_id, qty, price).
    • Main queries and indexes: "order by id" uses the primary key. "My orders, newest first" uses an index on (user_id, created_at DESC). "Orders stuck in PENDING for 30 minutes" uses a partial index on created_at WHERE status = 'PENDING'.
    • Reads: 600 writes per second at peak means up to 30,000 reads per second. Add two read replicas and a cache for order details; read the user's just-placed order from the primary.
    • Size: about 5.5 billion orders over 3 years at roughly 1 KB each is about 5.5 TB. That is past a comfortable single node, so plan to shard by user_id (all of a user's orders on one shard keeps "my orders" a single-shard query) or move old orders to cheaper storage.

    Common mistakes

    • Naming a database without the access pattern. "We'll use Cassandra" means nothing until you say which queries it serves and why.
    • No indexes in the design. Interviewers expect you to name the index behind each main query.
    • Choosing NoSQL "for scale" at 100 writes per second. One Postgres server handles thousands of writes per second. Justify a switch with numbers.
    • Sharding before you need to. Indexes, replicas and caching go a long way; sharding is the expensive last step.
    • Forgetting replica lag. Reading your own write from a lagging replica is a classic bug.
    • Picking a shard key with hot spots, such as the current date, so all of today's writes land on one shard.

    Say this in the interview

    "Orders need transactions and have clear relationships, so I'll use Postgres. Primary key is order_id, and the main query, a user's recent orders, is served by an index on user_id and created_at. Reads are about 50 to 1, so I'll add read replicas and a cache, and read a user's own new order from the primary to avoid replica lag. At about 5 TB in three years we'll shard by user_id, which keeps a user's orders on one shard."

    Used in