GamifiedGamified challengesPracticeRaceLeaderboardPricing

Learn data structures by watching them move, then doing it yourself. Press M anytime to toggle sound.

A product of Amypo Technologies, Coimbatore.

Learn

  • All topics
  • Learning path
  • How it works
  • About dotprep

Practice

  • Practice arena
  • Daily challenge
  • Gamified challenges
  • Race mode
  • Leaderboard

Interview prep

  • Java interview
  • Coding questions
  • Company questions
  • System design
  • Jobs & careers

You

  • Dashboard
  • Settings
  • Pricing
  • Create account

© 2026 dotprep

Content last updated 20 November 2026

GamifiedGamified challengesPracticeRaceLeaderboardPricing
System designDatabases: SQL, NoSQL and replication

Databases: SQL, NoSQL and replication

Choosing a data store, indexes, ACID, read replicas and replication lag.

intermediate 14 min read 5 interview questions

On this page

1/6
  1. 01SQL or NoSQL
  2. 02Indexes
  3. 03Transactions and ACID
  4. 04Replication
  5. 05Interview questions
  6. 06Quick check
Back to top

SQL or NoSQL

TypeExamplesStrengthsTypical uses
Relational (SQL)PostgreSQL, MySQLJoins, transactions, constraints, flexible queriesUsers, orders, payments: most business data
Key-valueRedis, DynamoDBVery fast lookups by key, easy to partitionSessions, caches, carts, feature flags
DocumentMongoDB, CouchbaseFlexible nested recordsCatalogues, content, user profiles
Wide-columnCassandra, HBaseHuge write throughput, scales across many nodesTime series, messages, activity logs
GraphNeo4jTraversing relationshipsSocial graphs, recommendations, fraud rings
SearchElasticsearch, OpenSearchFull-text search and aggregationsSearch boxes, log analysis

Start with a relational database unless you have a reason not to: it's well understood, flexible, and scales further than most systems need. Choose NoSQL for a specific access pattern: enormous write volume, simple key lookups at massive scale, or schemaless data.

Indexes

Without an index, a query scans every row. A B+ tree index keeps keys sorted in a shallow, wide tree, so a lookup or a range scan touches only a few disk pages. Indexes speed up reads but slow down writes (every insert updates every index) and take space.

  • Index the columns you filter, join and sort on.
  • A composite index (country, city) helps queries on country and on country + city, but not on city alone (leftmost prefix rule).
  • Write-heavy stores often use LSM trees (Cassandra, RocksDB): writes go to memory and are flushed to sorted files, trading some read cost for very fast writes.

Transactions and ACID

  • Atomicity: all of a transaction happens or none of it.
  • Consistency: constraints hold before and after.
  • Isolation: concurrent transactions don't see each other's partial work (to a chosen level: read committed, repeatable read, serializable).
  • Durability: once committed, data survives a crash (thanks to the write-ahead log).

Many NoSQL stores offer weaker guarantees (often called BASE: basically available, soft state, eventually consistent) in exchange for availability and scale.

Replication

Replication keeps copies of the data on several machines, for availability and to spread reads.

SetupHowWatch out for
Primary–replica (leader–follower)One primary takes writes and streams changes to read replicasReplication lag: a replica may return stale data
Multi-primarySeveral nodes accept writesWrite conflicts must be resolved
Leaderless (quorum)Write to W of N nodes, read from R; R + W > N gives overlapTuning W and R; repair of stale copies

Synchronous replication waits for replicas before acknowledging (no data loss on failover, slower writes). Asynchronous replication acknowledges immediately (fast, but recent writes can be lost if the primary dies).

Read-your-own-writes: after a user updates their profile, read it back from the primary (or wait for the replica to catch up), or they'll see their change disappear.

See it animated

B+ treeB-treeHash functions

Interview questions

Try answering out loud first, then open the model answer.

Q1How do you choose between SQL and NoSQL?intermediate

Look at the data and access patterns. Relational data with transactions and ad-hoc queries suits SQL. Choose NoSQL for a specific need: very high write throughput, simple key-based access at huge scale, flexible schemas, or graph traversal. Many systems use both.

Q2Why do indexes slow down writes?beginner

Every insert, update or delete must also update each index on the table, which means extra disk writes and tree rebalancing. That's why you index for your real query patterns rather than every column.

Q3What is replication lag and how do you handle it?intermediate

With asynchronous replication, replicas apply changes a little after the primary, so a read from a replica can be stale. Handle it by reading your own recent writes from the primary, routing a user to the same replica (monotonic reads), or waiting for a replica to reach a known position.

Q4Explain quorum reads and writes.advanced

With N replicas, a write succeeds after W acknowledge and a read queries R replicas. If R + W > N, every read set overlaps every write set, so at least one replica returns the latest value. N = 3, W = 2, R = 2 is a common balance.

Q5What do the isolation levels protect against?advanced

Read committed prevents dirty reads (seeing uncommitted data). Repeatable read also prevents non-repeatable reads (a row changing between two reads in one transaction). Serializable prevents phantoms and every other anomaly by making transactions behave as if run one at a time, at a cost in throughput.

Quick check

Question 1

1.Which structure do most SQL indexes use?

Question 2

2.Index on (country, city). Which query can't use its leftmost prefix?

Question 3

3.With N = 3, which settings guarantee a read sees the latest write?

0 of 3 answered

CachingSharding and consistent hashing
Log inStart free