Databases in System Design: SQL vs NoSQL, Replication and Sharding

In an e-commerce checkout, the order must be saved exactly once, stock must go down by exactly the right amount and a payment must never be charged twice. Meanwhile the product catalogue is read a thousand times more often than it changes. Different data has different needs, and the database choice is where those needs meet reality.

SQL vs NoSQL: choosing by access pattern

Relational (SQL) databases such as PostgreSQL and MySQL store data in tables with a fixed schema, support joins and offer strong transactional guarantees. NoSQL is an umbrella term for several different models: key-value stores, document databases, wide-column stores and graph databases. They typically trade rich querying and strict guarantees for flexible schemas and easier horizontal scaling.

A sound default is to start with a relational database. It handles far more load than many people expect, and you can always move specific high-volume data to a specialised store later. Reach for NoSQL when you have a clear reason: a simple access pattern at very large scale, flexible document shapes, or write volumes that a single relational node cannot absorb.

ACID vs BASE

ACID describes the guarantees of a classic transaction: Atomicity (all steps happen or none do), Consistency (the data respects its rules after the transaction), Isolation (concurrent transactions do not see each other's half-finished work) and Durability (committed data survives a crash). Checkout is a textbook case: reducing stock, creating the order and recording the payment must succeed or fail together.

BASE (Basically Available, Soft state, Eventually consistent) describes the looser model many distributed NoSQL systems adopt: the system stays available even when nodes disagree for a while, and replicas converge over time. A "like" counter or a view count can tolerate being off by a few for a few seconds; a bank balance cannot. Many real systems use both, each for the data that suits it.

Indexes: faster reads, slower writes

Without an index, finding all orders for one user means scanning every row. An index (commonly a B-tree) is a sorted structure that lets the database jump straight to matching rows. The trade-off is that each index uses storage and must be updated on every insert, update and delete, so more indexes mean slower writes.

CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  user_id     BIGINT      NOT NULL,
  status      VARCHAR(20) NOT NULL,
  total_cents BIGINT      NOT NULL,
  created_at  TIMESTAMP   NOT NULL
);

-- Composite index: filter by user, then sort by newest
CREATE INDEX idx_orders_user_created
  ON orders (user_id, created_at DESC);

-- Served efficiently by the index above
SELECT id, status, total_cents
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

Design indexes from your real queries. Put equality filters first and the sort column after, keep the index narrow, and check the plan with EXPLAIN instead of guessing. Index columns you filter or join on; do not index every column.

Replication: scaling reads and surviving failures

In leader-follower replication, one node (the leader) accepts writes and copies them to followers, which serve reads. This scales read-heavy workloads, gives you standby copies for failover and lets you run backups and analytics away from the main node.

        writes
          |
          v
    +------------+
    |   Leader   |
    +------------+
      |    |    |    replicate (usually async)
      v    v    v
 +------+ +------+ +------+
 | Rep1 | | Rep2 | | Rep3 |   <-- reads
 +------+ +------+ +------+

The catch is replication lag. With asynchronous replication a follower can be slightly behind, so a user who just updated their profile might read the old version from a replica. Common fixes are to read the user's own recent writes from the leader ("read your writes"), or to route that session to the leader for a short time. Synchronous replication removes the lag but makes every write wait for followers and hurts availability if one is slow.

Sharding: splitting data across nodes

Replication copies the same data everywhere, so it cannot help when the data or the write rate outgrows one machine. Sharding (horizontal partitioning) splits the data so each node holds only a slice. The most important decision is the shard key, the field that decides where a row lives.

 App server
    |  shard = hash(user_id) % 3
    v
 +--------+   +--------+   +--------+
 | Shard0 |   | Shard1 |   | Shard2 |
 | users  |   | users  |   | users  |
 | A-slice|   | B-slice|   | C-slice|
 +--------+   +--------+   +--------+
 (each shard normally has its own replicas)

Plain hash(key) % N is easy but brittle. Going from 3 shards to 4 changes the result for about three quarters of all keys, which means moving most of your data. Consistent hashing, or a fixed larger number of logical partitions mapped to physical nodes, avoids that.

Hot partitions

Even a hash key can create a hot partition. If you shard a social app by user_id, one celebrity account with millions of followers sends disproportionate traffic to its shard. If you shard by created_at, every new write hits the latest shard. Choose a key with high cardinality and even access, and for known hot keys add a random suffix ("salting") to spread them, at the cost of reading from several places.

Common mistakes

• Choosing NoSQL because it "scales" without a reason. Symptom: you rebuild joins and transactions by hand in application code, with bugs.
• Missing or wrong indexes. Symptom: queries get slower as the table grows, and one slow query saturates the database.
• Over-indexing. Symptom: slow inserts and updates, and large storage bills from indexes that no query uses.
• Reading from a replica right after a write. Symptom: users save a change and then see the old data. Route read-your-writes traffic to the leader.
• A poor shard key. Symptom: one shard runs hot while others are idle, so adding shards does not help.
• Forgetting backups and failover. Replication is not a backup: a bad delete is replicated to every follower too.

How this shows up in interviews and real projects

Expect questions such as "SQL or NoSQL for this, and why?", "how would you scale the database?" and "what is your shard key?". Answer by naming the access patterns and consistency needs first, then the choice. In real projects most teams stay on a single relational primary with replicas for a long time, and shard only the data that forces it. The next lesson, CAP Theorem and Consistency Models, explains what happens when replicas cannot agree. See also the system design interview prep guide.