Databases, Indexing & Transactions

A database is not where you put data after the design is done. It is the system that decides which reads are cheap, which writes are safe, and which failure modes your product can tolerate. Pick the wrong one and every downstream service inherits the pain.

Senior design answers do not say “use SQL” or “use NoSQL” as a reflex. They name the access patterns, consistency needs, data shape, and operational constraints — then pick the store that makes those constraints boring.

The mental model

A datastore is two things at once:

1. A query engine optimized for certain access patterns.
2. A consistency contract for what reads and writes mean under concurrency and failure.

# The design question hiding behind every database choice:
store = choose_database(
    reads="lookup order by id, list orders by user, search recent failures",
    writes="hundreds per second, must not lose money",
    consistency="read your own writes and no double charges",
    scale="fits on one primary today, replicas soon",
)

If you cannot list the reads and writes, you cannot choose the database. Start there before naming technology.

Common datastore shapes

Most real systems use more than one: a relational source of truth, a cache for hot reads, an object store for blobs, and a search index for discovery.

Why indexes make reads fast

A table scan is O(n): the database checks row after row until it finds matches. A B-tree index keeps keys sorted in a shallow tree of pages, so lookup becomes O(log n) page hops. Because each page holds many keys, the tree stays short even for millions or billions of rows.

root page
  -> internal page for org_id=42
      -> leaf page with sorted (status, updated_at, row_pointer)

That read speed is not free. Every index adds:

• Write cost — inserts, updates, and deletes must update the index too.
• Storage cost — indexes can be as large as the table for wide keys.
• Memory pressure — hot index pages compete with hot table pages in cache.

A good index matches a query shape. For compound indexes, order matters: an index on (org_id, status, updated_at) helps queries that filter by org_id, then maybe status, then sort or range-scan by updated_at.

ACID vs. BASE

ACID is the relational transaction promise:

• Atomicity — all writes in the transaction happen, or none do.
• Consistency — constraints are preserved.
• Isolation — concurrent transactions do not corrupt each other.
• Durability — committed data survives crashes.

BASE describes many distributed NoSQL systems: basically available, soft state, eventually consistent. You trade some immediate consistency for higher availability and easier horizontal scaling.

Neither is morally better. Money movement wants ACID. A like counter can be BASE. The mistake is applying the same consistency requirement to every feature.

Isolation levels in practice

Isolation is where many production bugs hide. You do not need every anomaly name, but you should know the practical ladder:

For weak isolation, protect critical writes with one of these patterns:

• Row locks: SELECT ... FOR UPDATE before modifying shared state.
• Optimistic concurrency: update only if version still matches.
• Idempotency keys: make retries safe for externally visible actions.
• Database constraints: let uniqueness and foreign keys enforce invariants.

Pattern recognition

Variations

Worked problems

These are design drills, not coding puzzles. Name the access pattern first; the technology choice should follow naturally.

begin transaction
  read listing row for update
  if inventory > 0 and listing is active:
      decrement inventory
      create order
  else:
      reject purchase
commit

primary key: (org_id, task_id)

Admin A reads quota = 100
Admin B reads quota = 100
Admin A writes quota = 110
Admin B writes quota = 110
Final value: 110, but two increments happened

read quota=100, version=7
try update quota=110, version=8 where account_id=... and version=7
if 0 rows updated: someone else wrote first; reread and retry

The classic pitfall