Choosing a Database

Jr Codex DBMS Notes

Level: Intermediate–Advanced Prerequisites: Chapter 3: CAP, Consistency and Sharding Time to complete: ~20 minutes

---

Table of Contents

1. Start With Postgres
2. The Questions That Decide It
3. NewSQL
4. Polyglot Persistence
5. The Decision Table
6. Migration Reality
7. Summary & Next Steps

---

1. Start With Postgres

The Default Recommendation
─────────────────────────────────────────
  Unless you can name a specific requirement it
  fails, use a mainstream relational database.

  It is not a conservative non-answer. It is what
  the previous three chapters actually imply:
  every alternative trades away a guarantee, and
  you should only pay that price for a reason you
  can articulate.
─────────────────────────────────────────

How Much One Instance Covers
─────────────────────────────────────────
  A single well-tuned PostgreSQL instance on modern
  hardware routinely handles:

    - tens of thousands of transactions per second
    - single-digit terabytes
    - JSONB documents with GIN indexes, if you want
      document storage (Module 5, Chapter 3)
    - full-text search
    - geospatial via PostGIS
    - time-series via partitioning or TimescaleDB
    - queues via SKIP LOCKED (Module 7, Chapter 3)

  Most systems that adopted a specialised database
  never reached the limits of the general one.
─────────────────────────────────────────

The Cost of Being Wrong, Each Way
─────────────────────────────────────────
  CHOSE RELATIONAL, needed to scale out
    ──► a painful but well-understood migration,
        with your data intact and your invariants
        enforced throughout.

  CHOSE EVENTUALLY CONSISTENT, needed transactions
    ──► you discover the problem as CORRUPTED DATA,
        found late, with no record of what the
        correct values were.

  The failure modes are not symmetric. That
  asymmetry is the argument.
─────────────────────────────────────────

---

2. The Questions That Decide It

Six Questions, In Order
─────────────────────────────────────────
  1. DOES CORRECTNESS REQUIRE TRANSACTIONS?
     Money, inventory, bookings, anything with a
     multi-row invariant.
     YES ──► relational or NewSQL. Stop here.

  2. WHAT IS THE ACCESS PATTERN?
     Known and fixed        ──► anything works
     Ad-hoc and evolving    ──► needs a query
                                language
     Pure key lookup        ──► key-value
     Deep traversal         ──► graph

  3. WHAT IS THE ACTUAL SCALE?
     Measured, not imagined. Under a terabyte and
     under ~10k writes/sec is comfortably one
     relational instance.

  4. WHAT CONSISTENCY DOES EACH OPERATION NEED?
     Different rungs for different operations
     (Chapter 3). Do not pick one global answer.

  5. WHAT IS THE DATA SHAPE?
     Tabular, nested, time-series, or graph.

  6. WHO OPERATES IT AT 3AM?
     A database nobody on the team can debug is a
     liability regardless of its benchmarks.
─────────────────────────────────────────

Question 6 Is Underweighted
─────────────────────────────────────────
  Technology choices are usually argued on
  questions 1-5 and regretted on question 6.

  A team that knows PostgreSQL deeply will get
  better results from PostgreSQL than from a
  theoretically better-suited system they have
  never operated under load.
─────────────────────────────────────────

---

3. NewSQL

The Premise
─────────────────────────────────────────
  Chapter 1 framed the choice as "relational
  guarantees OR horizontal scale".

  NewSQL systems reject that framing: SQL, ACID
  transactions and horizontal scale together.

  CockroachDB, TiDB, YugabyteDB, Google Spanner,
  Vitess.
─────────────────────────────────────────

How They Do It
─────────────────────────────────────────
  - Data is sharded into ranges, automatically
    rebalanced
  - Each range is replicated via a CONSENSUS
    protocol (Raft or Paxos), so a majority agrees
    on every write
  - Distributed transactions use two-phase commit
    over those consensus groups
  - Spanner adds TrueTime — GPS and atomic clocks
    giving bounded clock uncertainty — to order
    transactions globally
─────────────────────────────────────────

The Honest Costs
─────────────────────────────────────────
  LATENCY      a write needs consensus across a
               majority of replicas. Single-row
               writes are slower than on a single
               node — meaningfully so across
               regions.

  COMPLEXITY   far more moving parts to operate,
               understand and debug.

  MATURITY     younger than PostgreSQL by decades.
               Fewer tools, fewer people who know
               them, more surprises.

  COST         more nodes, and usually commercial
               support.
─────────────────────────────────────────

When It Is Right
─────────────────────────────────────────
  ✓ you genuinely exceed one machine for WRITES
  ✓ and still need transactions and SQL
  ✓ and need multi-region with survivable failures

  That combination is real, and rarer than the
  marketing implies. Reaching it is a good problem
  to have; assuming it in advance is not.
─────────────────────────────────────────

---

4. Polyglot Persistence

The Idea, and the Trap
─────────────────────────────────────────
  Use several databases, each for what it is best
  at.

  Sound in principle. In practice each additional
  system brings:

    - its own operations, monitoring, backups
      (Module 8) and failure modes
    - its own client library and connection pool
    - a CONSISTENCY problem between systems, with
      no transaction spanning them
    - another thing to learn and to be on call for
─────────────────────────────────────────

A Sensible Version
─────────────────────────────────────────
  PostgreSQL     the system of record. All
                 transactional data.
  Redis          cache and sessions. REBUILDABLE —
                 losing it costs latency, not data.
  Object storage files and blobs.

  Three systems, and only ONE holds state that
  cannot be reconstructed. That containment is what
  makes it manageable.
─────────────────────────────────────────

The Version That Goes Wrong
─────────────────────────────────────────
  Orders in PostgreSQL, inventory in MongoDB.

  "Decrement stock and create the order" now spans
  two databases with no shared transaction.

  A failure between them leaves an order with no
  stock deducted, or stock deducted with no order.
  You are now implementing distributed transactions
  by hand, in application code, without the tools
  a database gives you.

  RULE: never split a transactional boundary across
  two systems. Data that must change together must
  live together.
─────────────────────────────────────────

---

5. The Decision Table

Requirement	Choose	Because
Transactions, relationships, ad-hoc queries	PostgreSQL / MySQL	The default; every guarantee intact
Cache, sessions, rate limits	Redis	Sub-millisecond, TTLs, rebuildable
Nested aggregates, varying shapes	Document store, or JSONB in Postgres	Try the column first
Huge time-series or event volume	Cassandra, Timescale, ClickHouse	Partition-key design fits the access pattern
Deep relationship traversal	Neo4j	Pointer-following beats repeated joins
Analytics over billions of rows	ClickHouse, DuckDB, BigQuery	Column store (Module 5, Chapter 1)
Full-text search at scale	Elasticsearch, or Postgres GIN	Try the index first
Transactions and write scale-out	CockroachDB, Spanner	The only category that offers both
Global multi-region low latency	DynamoDB, Cassandra	Designed for it; consistency is the price

Notice the Bold Entries
─────────────────────────────────────────
  Two rows say "try PostgreSQL's feature first".

  Adopting a whole new system for document storage
  or full-text search is a large operational cost
  to avoid learning one index type. Exhaust the
  database you already run before adding another.
─────────────────────────────────────────

---

6. Migration Reality

What a Database Migration Actually Costs
─────────────────────────────────────────
  - rewriting every query and every data access
    layer
  - re-implementing constraints the old database
    enforced for you (Module 2, Chapter 2), now in
    application code
  - a dual-write period where both systems run and
    must agree
  - backfilling historical data, and verifying it
  - new operational knowledge: backups, monitoring,
    failure modes, tuning
  - the bugs you find only under production load

  Typically MONTHS, and it is dominated by the
  application, not the data.
─────────────────────────────────────────

Cheaper Things to Try First
─────────────────────────────────────────
  1. INDEX properly (Module 5)
  2. READ THE PLANS and fix the estimates
     (Module 6)
  3. ADD READ REPLICAS (Module 8)
  4. CACHE the hot reads
  5. PARTITION the large tables — same database
  6. ARCHIVE cold data out of the hot path
  7. SCALE UP the machine

  Each of these is days of work rather than months,
  and most performance problems are solved before
  reaching step 7.
─────────────────────────────────────────

The Question to Ask
─────────────────────────────────────────
  "Which specific guarantee do I need that this
   database cannot provide — and have I measured
   that, or assumed it?"

  If you cannot name the guarantee, you do not need
  a different database. You need Module 5 and
  Module 6.
─────────────────────────────────────────

---

7. Summary & Next Steps

Key Takeaways

• Default to a mainstream relational database unless you can name the specific guarantee it fails to provide, because the failure modes of choosing wrongly are not symmetric.
• Six questions decide it, and the last — who operates it at 3am — is the one most often underweighted and most often regretted.
• NewSQL genuinely delivers SQL and ACID at horizontal scale, paying for it in write latency, operational complexity and maturity.
• Never split a transactional boundary across two systems; data that must change together must live together.

Module 9 Complete — What's Next

You can now place any database in a landscape and justify a choice rather than follow a trend. Module 10 returns to practice: tuning a real workload end to end, and a capstone that exercises the whole curriculum.

Concept Check

1. Why are the costs of wrongly choosing relational and wrongly choosing eventually-consistent asymmetric?
2. What does NewSQL provide that neither traditional relational nor NoSQL does, and what does it cost?
3. Why is putting orders in PostgreSQL and inventory in MongoDB a specific mistake rather than a style choice?

Next Module

→ Module 10: Practice and Capstone

---

Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index