Database Types: SQL, NoSQL and Beyond

1The core idea#

A database does one job: store your data and answer questions about it quickly and safely. Every family exists because it made a different bet about which word matters most.

• The three words in tension: fast, safe, flexible. You cannot max out all three at once.
• So the interesting question is always the same: what are you willing to give up for this problem?
• The four families that actually come up in interviews: relational, key-value, document, wide-column. The rest are special-case tools.

The four families, and the bet each one makes:

• Relational (SQL). Tables with a fixed schema and relationships. You query with SQL, you get transactions and joins. PostgreSQL, MySQL. The bet: correctness and query power over easy horizontal scale.
• Key-value. A giant hash map: hand it a key, get a value. Dead simple, very fast, almost no query power. Redis, DynamoDB in its simplest mode. The bet: raw speed and trivial scaling over asking anything but "give me this key."
• Document. Stores JSON-ish documents. Flexible schema, query inside the document, no real joins. MongoDB. The bet: schema flexibility over cross-entity consistency and joins.
• Wide-column. Rows with up to millions of columns, partitioned by a key, built for huge write volume across a cluster. Cassandra, HBase. The bet: write throughput and horizontal scale over ad-hoc queries.

The exotic ones, reached for only when the access pattern screams for them:

• Graph (Neo4j) for relationship-heavy traversals.
• Time-series (InfluxDB, TimescaleDB) for metrics.
• Search (Elasticsearch) for full-text.

2How it works#

The thing that separates these families is the data model and what they give up to scale.

SQL: one primary owns the writes.

• A single primary node takes all writes, which buys strong consistency and transactions.
• One machine has a ceiling: a single primary caps out in the low thousands of writes per second for typical row sizes.
• You cannot add a second primary without real pain (that is sharding, and you own the logic).

NoSQL: spread across many nodes by a partition key.

• Data is partitioned across nodes, so you add machines to grow.
• The cost: you give up easy joins, and often give up read-your-own-write consistency.

Let me make this concrete, because the numbers are where interviews are won.

Worked example: sizing a read-heavy relational database.

• The product: a catalog. Traffic is 10,000 reads/sec and 200 writes/sec.
• The pick: PostgreSQL, because the data is relational (products, categories, inventory) and I want joins.
• The constraint: a single Postgres instance on decent hardware handles roughly 5,000 to 10,000 simple reads/sec before CPU and disk become the bottleneck. 10,000 reads/sec is right at the edge, so I do not want it all hitting one box.

The plan, with real numbers:

• 1 primary takes all 200 writes/sec. Write volume is low, so one primary is fine, with roughly 95 percent of its write ceiling left as headroom.
• 3 read replicas take the 10,000 reads/sec. If each replica comfortably serves 4,000 reads/sec, I need 10,000 / 4,000 = 2.5, so 3 replicas. That gives 12,000 reads/sec of capacity against 10,000 demand, so I can lose one replica and still serve traffic.
• Connection pooling. The part people forget. Postgres default max_connections is 100, and each connection costs around 5 to 10 MB of RAM. Run 10 app instances that each open 50 connections and that is 500 connections, five times over the limit. The DB rejects connections or runs out of memory and falls over.

Sizing the pool so you never hit that wall:

• With a pool of 20 connections per instance and a 5 ms average query, one connection does 1000 / 5 = 200 queries/sec, so 20 connections do 4,000 queries/sec per app instance.
• Set pool size so instances x pool_size <= max_connections on the DB. For a replica with max_connections = 200 and 8 app instances, that is 200 / 8 = 25, rounded down to 20 for safety.
• For anything bigger, put PgBouncer in front so thousands of app-side connections multiplex onto a small set of real DB connections. In transaction pooling mode, a few hundred app connections share as few as 20 to 50 live DB connections.

The one catch to name out loud: replication lag.

• A write to the primary takes some milliseconds, typically single-digit to low tens under load, to reach replicas.
• If a user updates their profile and immediately reads it from a replica, they might see stale data.
• The fix: route read-after-write to the primary for that user, inside the same request, and send everything else to the replicas.

3Trade-offs#

Here is the comparison I keep in my head. Match the row to the question you were asked.

The real decisions hiding behind that table:

• Joins vs scale. The moment you shard a relational database, joins across shards go slow or impossible and you start denormalizing. Now you are doing NoSQL's job with more pain: you own the sharding logic, the rebalancing, and the cross-shard query stitching by hand. If you genuinely need huge scale and your queries fit one partition, a NoSQL store built for it is cleaner.
• Schema flexibility is not free. Document stores let you skip migrations, which feels great until three services each write a slightly different shape and nobody knows what a "user" document contains. Some have email, some have emailAddress, some have neither. Fixed schema is a feature: the database enforces the contract instead of your six readers each guessing.
• Consistency is a dial, not a yes or no. DynamoDB and Cassandra let you choose eventual or strong reads per request. A strongly consistent read in DynamoDB costs twice the read capacity of an eventual one and is slower, so you pay in both latency and money. Say which you need and why.

My default, said plainly: start with PostgreSQL.

• It does key-value, JSON documents with JSONB, full-text search, and transactions.
• You move off it only when a specific number forces you to, like a write rate past a single primary's ceiling.
• Not on day one because NoSQL sounds bigger.

4Where it is used#

Real systems, so you can name them in the room:

• Relational SQL: anything with money or correctness requirements. Stripe, banking cores, order management. Shopify runs on sharded MySQL. The reason is transactions: you cannot half-charge a card, so you need the all-or-nothing guarantee.
• Key-value: Redis for session storage, leaderboards, and caching in front of nearly every large app. DynamoDB powers Amazon's shopping cart, chosen because a cart is looked up by one key (the user id) and must never go down, even during a Black Friday spike.
• Document: MongoDB for content management and product catalogs where fields vary. A news site where a video article, a photo gallery, and a text story each carry different fields fits naturally, because forcing them into one rigid table means dozens of mostly-null columns.
• Wide-column: Cassandra at Netflix and Discord. Discord stores trillions of messages in Cassandra (and later ScyllaDB) because messages are time-ordered, written constantly, and read by channel, a perfect partition key: one channel's recent messages all live together on one node.
• Graph: Neo4j for fraud detection and recommendations where the question is "who is connected to whom in three hops." That query is a nightmare of recursive joins in SQL and native in a graph store.
• Time-series: Prometheus and InfluxDB for metrics and monitoring, where data is append-only and queried by time range, like "CPU for this host over the last hour."

Every one of these was chosen because of how the data is read and written, never because of a trend.

5Common pitfalls#

The mistakes I see most, and the ones interviewers love to catch:

• "NoSQL scales, SQL doesn't." False, and it signals inexperience. SQL scales vertically very far, and read replicas take you further. Instagram ran on Postgres to tens of millions of users. NoSQL trades away joins and transactions for horizontal writes. That is the actual difference, not magic scaling fairy dust.
• Choosing before knowing the access pattern. Picking a database before you know whether you query by one key or by complex filters is backwards. The query shape decides the store, so ask about reads and writes first, then pick.
• Ignoring the write path. People size reads and forget writes. Wide-column stores exist because some systems do millions of writes per second, which a single SQL primary, capped in the low thousands, cannot absorb no matter how many read replicas you bolt on.
• Forgetting connection limits. As in the worked example, an app that opens unbounded connections knocks over even a healthy database. 500 connections against a 100 limit takes the DB down regardless of how fast the queries are. Pool and cap, always.
• Using a document store to dodge schema work. Skipping schema design does not remove the schema. It moves it into your application code where it is undocumented and inconsistent, and every reader has to defend against every past shape.
• Treating eventual consistency as free. Read from a replica or a NoSQL node right after writing and you may get stale data for tens of milliseconds. Decide per feature whether that is acceptable: fine for a view counter, not fine for an account balance.

6How it shows up in interviews#

Interviewers probe three things:

• Can you pick a database for a reason?
• Do you understand what you traded away?
• Do you know what breaks at scale?

So the questions are predictable. Have the answers loaded:

• Say "SQL" and expect "how does it scale?"
• Say "NoSQL" and expect "how do you do a join?" and "what about consistency?"

Here is the contrast that matters.

Weak answer: "I'll use a NoSQL database like MongoDB because it's more scalable and flexible than SQL, and it's better for large applications."

• Names no access pattern.
• Repeats a myth about scale.
• Collapses under one follow-up about joins or consistency.
• Now the interviewer has to drag the reasoning out of you, which is exactly the signal they do not want.

Strong answer: "The core data is orders, users, and payments with relationships and a need for transactions, so I'll start with PostgreSQL. Writes are low, maybe a few hundred per second, so one primary handles them with plenty of headroom. Reads are heavy, around 10,000 per second, so I'll add three read replicas at roughly 4,000 reads each and put PgBouncer in front to keep connection counts under the DB's limit. The one risk is replication lag, so after a user places an order I'll read their order from the primary, not a replica. If order volume ever grows past what one primary can write, in the low thousands per second, I'd move the high-write part, like an activity feed, to Cassandra partitioned by user, since that query always fits one partition. I'd keep orders in Postgres because I'm not giving up transactions on money."

• Picks a default and defends it with numbers.
• Names the one thing that breaks (replication lag) and the fix.
• Shows it knows when to switch, and what it would and would not move.
• The interviewer barely has to ask a follow-up, because you already answered all three probes.

For practice, try the related Design Interview Question on choosing a database for a URL shortener, where the read-heavy key-value access pattern points somewhere very different from the orders example above.