Companion question bank for the 8-part tutorial series in this folder: 01-replication-and-failover.md, 02-sharding-and-partitioning.md, 03-backup-recovery-and-durability.md, 04-sql-fundamentals.md, 05-nosql-database-types.md, 06-mysql-in-depth.md, 07-dynamodb-in-depth.md, 08-mongodb-in-depth.md.

Answers are short and plain — expand out loud using the diagrams and worked examples in the tutorials.

---

Part 1 Questions: Replication & Failover

1. What's the fundamental tradeoff between synchronous and asynchronous replication?#

Synchronous waits for the replica to confirm before acknowledging a write — zero data loss risk, but higher latency. Asynchronous acknowledges immediately without waiting — lower latency, but a real risk of losing the most recent writes if the primary dies right after acknowledging.

2. What is semi-synchronous replication, and why is it a good practical default?#

Waiting for confirmation from at least one replica (not all of them) before acknowledging a write. It gives real protection against losing a committed transaction without the full latency cost (or fragility) of waiting for every single replica.

3. What mechanism does most relational database replication actually run on?#

The write-ahead log (WAL) — every change is recorded in a sequential log before being applied, and that same log is streamed to replicas, which replay it in order. It's also the same mechanism enabling point-in-time recovery.

4. Why should replication lag be actively monitored rather than just assumed to be fine?#

It's a Saturation-style leading indicator — rising lag means a replica is falling behind and gives operators time to intervene before it's so far behind that failing over to it would mean losing a meaningful amount of recent data.

5. Why can't a replica be promoted instantly the moment a primary is detected as down?#

It needs to finish applying every WAL entry it's already received first — promoting before that completes risks new writes being applied out of order relative to the still-in-progress replication stream, risking corruption.

6. What's the split-brain problem, and why is it so dangerous?#

A network partition makes the monitoring system think the primary is dead (it's actually still running, just unreachable), so a replica gets promoted — now TWO primaries are simultaneously accepting independent, potentially conflicting writes. There's no clean, automatic way to merge them back together afterward.

7. What two techniques prevent split-brain?#

Fencing (actively cutting off the old primary's ability to accept writes before promoting a new one) and requiring quorum (a majority of monitoring/consensus nodes must agree the primary is truly down before any promotion happens).

8. How does database failover consensus relate to etcd's own consensus mechanism?#

They use the exact same quorum-based principle — tools like Patroni are often built directly on etcd or a similar consensus store. A minority partition can never unilaterally decide to promote a new primary, in either case.

9. Why is multi-region database replication almost always asynchronous?#

The physical speed-of-light latency of a cross-continental network round trip (often 60-150ms+) would make every single write unacceptably slow if it had to wait synchronously — asynchronous replication accepts some risk of losing the most recent writes in a true regional disaster in exchange for acceptable everyday write latency.

---

Part 2 Questions: Sharding & Partitioning

10. What problem does sharding solve that replication alone can't?#

Replication gives many copies of the SAME data (scales reads), but every write still goes through one primary, and the whole dataset must still fit on one machine. Sharding splits the data itself across independent databases, removing both the write-volume ceiling and the single-machine data-size ceiling.

11. Why is choosing a shard key described as the single most important decision in sharding?#

It shapes data distribution, query efficiency, and — critically — how painful resharding will eventually be if it needs to change later, which is a genuinely high-risk, high-effort operation.

12. Compare range-based, hash-based, and directory-based sharding in one line each.#

Range-based: good for range queries, but risks hot shards on sequential/time-correlated keys. Hash-based: spreads load evenly, but makes range queries expensive (often requires querying every shard). Directory-based: maximum flexibility via an explicit lookup table, at the cost of that lookup service becoming critical infrastructure itself.

13. Describe the "hot shard" / celebrity problem.#

Real-world data is often unevenly distributed (a viral user, a popular product, or writes concentrating on the newest range with sequential keys) — one shard receives dramatically more traffic than the others, even though the sharding scheme looks "even" numerically.

14. Why is resharding described as a "nightmare," and how does consistent hashing help?#

With naive hash(key) % N, changing the shard count reassigns almost every key to a different shard, requiring massive, risky live data movement. Consistent hashing means adding/removing a shard only reassigns the small slice of keys nearest to it on the hash ring — the same fix already covered for caches, but with much higher stakes here.

15. Why are cross-shard queries and transactions fundamentally harder than single-shard ones?#

A single shard's database can guarantee ACID atomicity within itself, but has no native way to guarantee atomicity across a separate, independent database. A query needing data from multiple shards must fan out and merge results in the application layer, and a transaction spanning shards risks a partial, inconsistent update if one side succeeds and the other fails.

16. What's the strongest practical strategy for avoiding the cross-shard transaction problem?#

Design the shard key so data that needs to be updated together atomically (e.g., accounts within the same organization) is deliberately co-located on the same shard, avoiding the problem by design rather than trying to solve distributed atomicity after the fact.

17. When should a team actually reach for sharding?#

Only after real capacity planning confirms write volume or raw data size is genuinely the bottleneck, and after simpler options (vertical scaling, read replicas, caching) have been exhausted — sharding introduces real new complexity and risk of its own.

---

Part 3 Questions: Backup, Recovery & Durability

18. Why is replication not a substitute for backups?#

Replication faithfully, automatically propagates every write — including a catastrophic mistake like an unscoped DELETE — to every replica, usually within seconds. Only a genuinely separate, point-in-time backup, disconnected from the live replication stream, protects against that class of disaster.

19. Define RPO and RTO in plain terms.#

RPO: how much data can we afford to lose, measured in time (e.g., up to 15 minutes). RTO: how long can we afford to be down while recovering, also measured in time. Both are deliberate business decisions that should directly drive backup frequency and recovery tooling choices.

20. Compare full, incremental, and differential backups.#

Full: complete copy every time — largest, but simplest/fastest to restore. Incremental: only changes since the last backup (of any type) — smallest, but slowest/riskiest to restore since every incremental in the chain must be replayed in order. Differential: only changes since the last full backup — a middle ground, faster to restore than incremental since only two files are needed.

21. What makes point-in-time recovery possible, and why is it so valuable?#

Continuous WAL archiving on top of periodic full backups — you can restore the full backup, then replay WAL entries up to any exact moment (like one second before a bad DELETE ran), instead of only being able to restore to whenever the last backup happened to be taken.

22. Spell out ACID.#

Atomicity (a transaction fully completes or has no effect at all), Consistency (a transaction only moves the database between valid states), Isolation (concurrent transactions don't interfere with each other's intermediate state), Durability (a committed transaction survives a crash immediately afterward).

23. What's a "non-repeatable read," and under which isolation level can it happen?#

Reading the same row twice within one transaction returns two DIFFERENT values, because another transaction committed a change in between. This can happen under READ COMMITTED (a very common default) but not under REPEATABLE READ or SERIALIZABLE.

24. What's the fundamental tradeoff across isolation levels?#

Stronger isolation levels give stronger correctness guarantees but cost more locking/coordination overhead and lower concurrency — the same latency-vs-consistency tradeoff seen elsewhere in this course (PACELC), applied specifically to transaction isolation.

25. How does a database actually guarantee "durability" mechanically?#

A write is recorded in the WAL, then an explicit fsync() call forces the operating system to actually flush it to physical disk (not just buffer it in memory) — only then is it considered truly, durably committed. Skipping fsync for extra speed means a "committed" transaction can be silently lost on a crash before the OS flushes its buffer.

26. State the 3-2-1 backup rule and explain what each number protects against.#

3 total copies (protects against losing any single copy to a normal failure), 2 different storage media/systems (protects against a systemic failure affecting one specific technology/vendor), 1 copy off-site (protects against a location-specific catastrophe like a fire or a whole region going down).

27. Why is an untested backup described as "a hypothesis, not a verified safety net"?#

Because real, documented incidents exist where teams discovered their backups were corrupted, incomplete, or simply didn't work — only during an actual disaster, when it was already too late. Regular, real restore drills in a non-production environment are what actually verify a backup strategy works.

---

Part 4 Questions: SQL Language Fundamentals

28. Why should you avoid SELECT * in real application code?#

It wastes bandwidth pulling back unneeded columns, and — more dangerously — if a new column is later added to the table, SELECT *-based code silently starts behaving differently with no code change at all.

29. What safety habit should precede any production UPDATE/DELETE?#

Run the exact same WHERE clause as a SELECT first, to confirm precisely which rows would actually be affected, before running the destructive statement.

30. Explain the difference between INNER JOIN and LEFT JOIN with a concrete example.#

INNER JOIN only returns rows matching in both tables — a user with zero orders is excluded entirely. LEFT JOIN returns all rows from the left table regardless of a match, with NULLs where there's no match — so a "count orders per user, including users with none" report needs LEFT JOIN, or those users silently vanish from the results.

31. Why does choosing the wrong JOIN type cause a silent bug rather than an error?#

It doesn't fail — it just quietly returns an incomplete or wrong result set (e.g., undercounting users), which is exactly why it's a hard class of bug to catch without deliberately testing edge cases like "no matches."

32. What's the difference between WHERE and HAVING?#

WHERE filters rows before aggregation happens; HAVING filters groups after aggregation. You can't use an aggregate function like COUNT(*) inside WHERE, because the aggregation hasn't happened yet at that point in query execution.

33. What is normalization, and what's the real cost of it?#

Structuring data so each fact is stored in exactly one place, eliminating duplication and inconsistency risk. The real cost is needing more JOINs to reconstruct a full picture — which is exactly why deliberate denormalization is a legitimate performance tradeoff for high-read systems.

34. Why doesn't adding an index to every column make everything faster?#

Every index must be updated on every INSERT/UPDATE/DELETE, so more indexes mean slower writes, plus real storage cost. Indexes should be added deliberately, based on actual query patterns.

35. Why does column order matter in a composite index?#

A composite index on (A, B) is sorted by A first, then B — it efficiently supports queries filtering by A alone or A+B together, but NOT by B alone, exactly like a phone book sorted by last name can't efficiently answer "everyone named John."

36. What does EXPLAIN tell you, and what's the key thing to look for?#

Exactly how the database plans to execute a query. The key thing to look for is an unexpected "Seq Scan" (full table scan) on a large table where an "Index Scan" was expected — one of the most common, actionable findings in a slow-query incident.

37. Why must a funds transfer between two accounts be wrapped in a transaction?#

Without one, if the second UPDATE fails after the first already succeeded, money would simply vanish — debited from one account but never credited to the other. The transaction guarantees both succeed or fail together, the Atomicity guarantee from ACID.

---

Part 5 Questions: NoSQL Database Types

38. What does "NoSQL" actually stand for, and why is the name misleading?#

"Not Only SQL" — it's misleading because it implies one unified alternative to SQL, when it actually covers four genuinely different data models (key-value, document, wide-column, graph), each with different tradeoffs.

39. Distinguish schema-on-write from schema-on-read.#

Schema-on-write (traditional SQL) enforces structure before any data can be written, guaranteeing consistency centrally. Schema-on-read (common in document databases) allows flexible, evolving data shapes, shifting the burden of handling inconsistent shapes onto application code.

40. When would you choose a key-value store, and what's its main limitation?#

For simple, known-key lookups like caching and sessions (Redis, DynamoDB). The limitation: you generally can't efficiently query by the VALUE's internal content — only by the key itself.

41. Why do document databases map so naturally onto how applications already think about data?#

A JSON document closely mirrors an application's own in-memory object/struct, avoiding the "object-relational impedance mismatch" that SQL/ORM-based applications have always had to manage.

42. What are wide-column databases optimized for, and what do they sacrifice?#

Extremely high write throughput and massive horizontal scale (Cassandra, HBase) — sacrificing flexible, ad hoc querying. You generally need to know and design around your access patterns up front.

43. Why do graph databases handle "friends of friends of friends" queries so much better than relational JOINs?#

A relational query needs several sequential, increasingly expensive JOINs, growing worse with each degree of separation. A graph database stores relationships as first-class, directly-traversable data, making even deep traversals fast by design.

44. Name the two influential papers behind much of the modern NoSQL landscape.#

Amazon's "Dynamo" paper (2007 — highly-available, eventually-consistent key-value store, behind Cassandra/DynamoDB/Riak) and Google's "Bigtable" paper (2006 — distributed wide-column store, behind HBase and Bigtable itself).

45. What's the right way to decide between SQL and NoSQL for a given system?#

Never "SQL vs NoSQL" in the abstract — start from actual access patterns, how often the data's shape changes, what consistency guarantees are genuinely needed, and expected scale. The model follows from those answers.

46. What is polyglot persistence?#

Deliberately using several different, specialized databases within one larger system — each chosen for the specific job it's best suited for (e.g. PostgreSQL for transactional data, Redis for caching, Cassandra for event logging) — rather than forcing one database to handle every access pattern.

---

Part 6 Questions: MySQL In Depth

47. What's MySQL's most distinctive architectural trait compared to most other relational databases?#

A pluggable storage engine architecture — the SQL-processing layer is separate from the actual storage layer, and different tables can even use different storage engines (InnoDB, MyISAM).

48. Compare InnoDB and MyISAM.#

InnoDB (the modern default): full ACID transactions, row-level locking (high concurrency), foreign key enforcement. MyISAM (legacy): no transactions, table-level locking (a write locks the ENTIRE table), no foreign keys — rarely the right choice for new tables today.

49. Why does MyISAM's table-level locking matter so much practically?#

A single write to ANY row blocks every other query against the entire table until it completes — a severe bottleneck under real concurrent load, and the primary reason InnoDB became the standard default.

50. What is the InnoDB buffer pool, and what's a common sizing rule of thumb?#

An in-memory cache of table/index data — a query finding its data there ("a hit") is fast; a miss requires a much slower disk read. Commonly sized to roughly 70-80% of available RAM on a dedicated database server.

51. What is MySQL's binlog, and how does it relate to concepts from Part 1?#

The binary log — MySQL's specific implementation of the write-ahead-log pattern from Part 1, used for both replication and point-in-time recovery via mysqlbinlog.

52. Why can statement-based binlog replication cause a replica to silently diverge from the primary?#

Non-deterministic statements (using NOW() or RAND()) get re-evaluated independently when replayed on the replica, potentially producing different values than what actually happened on the primary. Row-based replication avoids this by logging the actual resulting data change, not the statement.

53. What's the MySQL-specific name for replication lag, and why monitor it?#

Seconds_Behind_Master, shown in SHOW SLAVE STATUS — a rising value is a Saturation-style leading indicator that a replica is falling dangerously behind, warning before it becomes unsafe to fail over to.

54. What's the single most important first command when diagnosing "MySQL is slow"?#

SHOW PROCESSLIST (or SHOW FULL PROCESSLIST) — shows live, exactly what's currently running, for how long, and in what state (e.g. Locked) — the database-level equivalent of top from the Linux troubleshooting toolkit.

55. Why can a small, targeted UPDATE affecting only a few rows end up locking a huge table?#

Without a matching index on the WHERE clause, InnoDB must scan (and lock, row by row) every row it examines to find matches — not just the ones that ultimately match — holding those locks for the entire scan duration.

56. Why use --single-transaction with mysqldump?#

It wraps the entire dump in one consistent transaction (via InnoDB's MVCC), ensuring the backup reflects one coherent moment in time even while other writes continue on the live database during the dump.

57. Why might a team switch from mysqldump to Percona XtraBackup for a very large database?#

mysqldump (logical backup, replaying SQL) is dramatically slower to create AND restore at scale compared to XtraBackup (physical backup, copying data files directly) — directly impacting RTO for large production databases.

---

Part 7 Questions: DynamoDB In Depth

58. In DynamoDB, what determines which physical partition an item lands on?#

The partition key value is hashed internally by DynamoDB — the same hash-based sharding concept from Part 2, baked directly into the service rather than exposed as a configurable choice.

59. Why does a composite key (partition key + sort key) help model one-to-many relationships well?#

Items sharing the same partition key but different sort keys are stored together and can be queried as a range in a single, efficient operation — e.g. all of one user's orders, sorted by date, without touching any other user's data.

60. Why does using a low-cardinality field (like order status) as a partition key cause throttling even when the table's overall provisioned capacity looks fine?#

Capacity is allocated per-partition. A field with only a few possible values concentrates nearly all traffic onto one physical partition, which has its own fixed slice of capacity — the classic hot-partition problem from Part 2, made highly visible.

61. What's the practical tradeoff between provisioned and on-demand capacity modes?#

Provisioned is cheaper at steady, predictable load but throttles requests beyond the configured capacity; on-demand auto-scales to match actual traffic with no capacity planning, at a higher per-request cost.

62. What's the real difference between eventually consistent and strongly consistent reads?#

Eventually consistent reads are faster and cost half the read capacity but might return slightly stale data; strongly consistent reads guarantee the latest successful write is reflected, at higher latency and cost — a direct, per-request exposure of the PACELC latency-vs-consistency tradeoff.

63. How do DynamoDB Global Tables resolve conflicting writes made to the same item in two regions at once?#

Last-writer-wins, based on timestamps — a deliberate AP-style (availability-favoring) choice, not strict consistency.

64. What's the key operational difference between a GSI and an LSI?#

A GSI (different partition key, its own capacity) can be added to a table at any time; an LSI (same partition key, different sort key) must be defined at table creation and can never be added later.

65. What are DynamoDB Streams conceptually equivalent to?#

The same "ordered change log" idea as MySQL's binlog (Part 6) or PostgreSQL's WAL — but deliberately exposed as a consumable event source (e.g. for triggering Lambda functions) rather than an internal replication mechanism.

66. Why is a full table Scan discouraged in DynamoDB?#

It reads and filters every item in the table, exactly like a SQL full table scan (Part 4) — expensive at real scale. Query, which uses the partition key, is the efficient default access pattern.

---

Part 8 Questions: MongoDB In Depth

67. What's the core tradeoff between embedding and referencing in MongoDB schema design?#

Embedding puts related data inside the parent document for one fast read, but risks unbounded document growth and duplication; referencing avoids duplication but requires a second query (or $lookup) to assemble full data — design around actual access patterns, not normalization rules.

68. What is the oplog, and what is it equivalent to in other databases?#

MongoDB's operation log — a continuously appended, ordered record of every write that secondaries replay to stay in sync. The same underlying concept as MySQL's binlog (Part 6) or PostgreSQL's WAL (Part 1).

69. Why won't MongoDB elect a secondary that's behind on replication as the new primary?#

It would risk losing already-acknowledged writes and violate durability guarantees. A candidate's oplog must be at least as up to date as the voters' before it can win an election.

70. What does writeConcern: { w: "majority" } actually guarantee?#

That a majority of replica set members have acknowledged the write before it's considered successful — meaning it will survive any single-node failure and a subsequent election, unlike the faster but riskier default w: 1.

71. In a sharded MongoDB cluster, what is each individual shard actually made of?#

A full replica set. Sharding and replication are stacked, not alternatives — replication provides HA within a shard, sharding provides horizontal scale across shards.

72. Why is a monotonically increasing field (like a timestamp) a poor MongoDB shard key choice?#

All new writes target whichever shard owns the "latest" range, creating a hot shard — the exact same monotonic-key problem already seen in Part 2 and with DynamoDB in Part 7. A hashed shard key avoids this.

73. What's the leftmost-prefix rule for a compound index in MongoDB?#

A compound index on {a: 1, b: 1} can serve queries filtering on a alone or a and b together, but NOT b alone — the same rule already covered for MySQL/InnoDB in Part 6, since both are B-tree indexes.

74. What changed about MongoDB transactions in version 4.0?#

Multi-document ACID transactions became available. Single-document writes were always atomic; 4.0 extended that all-or-nothing guarantee across multiple documents/collections for cases good schema design alone can't avoid.

75. Why should the balancer be stopped before backing up a sharded MongoDB cluster?#

The balancer continuously migrates data chunks between shards; backing up mid-migration risks capturing an inconsistent snapshot where a chunk appears in neither, or both, shard backups.

---

Quick-Fire / Rapid Recall#