Polyglot Persistence

"Which database should we use?" The question sounds reasonable. It contains a hidden mistake.

The mistake is the word one.

Bonus C's maturity list ended with a warning worth its own article: five databases, one team, zero depth. This is that article — because the opposite failure is just as real: one database, strained into five jobs it was never shaped for.

Blog 5 planted the seed: SQL vs NoSQL is the wrong debate. Today we finish the thought. Real systems usually hold several kinds of data with several access patterns — and the mature question was never "which database?" It's: which data belongs in which store, which store owns the truth, which stores are derived, and what consistency and operational cost are we accepting?

That discipline has a name: polyglot persistence. And it is emphatically not about collecting databases to look sophisticated.

The Simple Mental Model: Different Tools in a Workshop

A good workshop has a hammer, a screwdriver, a drill, a measuring tape, a saw, and storage shelves.

A good craftsperson does not use the hammer for every job. Screws driven with a hammer technically go in — splitting the wood on the way.

But walk into a workshop with forty tools, half unlabeled, some rusting, nobody sure which drill takes which bit — and every job slows down. A messy workshop is slow, dangerous, and expensive to maintain.

Both failures are real: one tool for everything puts pressure on the tool and the work (the relational database doing full-text search, metrics, session storage, and blob hosting, badly), and too many tools puts pressure on the craftsperson (every store needs backups, monitoring, patching, and someone who actually knows it — Bonus C's muscles, precisely).

The skill is neither minimalism nor collection. It's choosing the few tools that match the system's real jobs, and keeping the workshop clean enough to work in.

SQL vs NoSQL Is the Wrong Question

Blog 5 said it; here's the full replacement. Instead of a two-bucket debate, interrogate the data: is it relational — do records reference each other in ways queries must follow? Does it need transactions — must several changes succeed or fail together? What's the access pattern — key-based lookup, full-text search, graph traversal, time-windowed, or analytical aggregation? What's the shape — structured, semi-structured, or unstructured (Blog 5's taxonomy)? What's the read/write ratio (Bonus A's architecture-shaping number)? What consistency does it need (Bonus F's per-data question)? What scale is expected, honestly, per Bonus A's estimates, not the pitch deck? Who owns this data — which service, which team? And the Bonus C question: what can this team actually operate?

Nine questions. Notice that only the first two are about SQL at all — and the last one isn't about technology whatsoever.

Data shape and access pattern pick the store. Team maturity vetoes it.

Source of Truth vs Derived Stores

The organizing principle of everything below — Blog 5's oldest lesson, now applied to a fleet of stores: not every store is equally authoritative.

A mature system typically has one source-of-truth store for its core records, surrounded by derived copies, each specialized:

Relational DB   →  SOURCE OF TRUTH
      ↓  events / CDC / batch (the plumbing, below)
Cache           →  fast reads          (Part 6)
Search index    →  search queries
Analytics DB    →  reports
Object store    →  blobs and files     (Part 8)
Time-series DB  →  metrics             (Part 12's data)

The rule when copies disagree: if derived stores disagree, the source of truth decides — unless the architecture explicitly, deliberately, in-writing says otherwise.

And here's the practical test for which is which: if you can delete a store and rebuild it from somewhere else, it's derived. If deleting it loses business data, it had better be your source of truth — on purpose.

One more line that carries P0-level weight: ownership follows truth. The service that owns the source-of-truth store owns the data, and it publishes the changes everything else consumes (Part 10's contracts, Part 7's events). Derived stores never write back uphill.

Common Store Types and Their Best Jobs

The workshop inventory — each tool, its job, and its warning label:

Nine tools. Most systems need two to four of them. Which brings us to the trouble with copies.

The Dual-Write Problem

The moment a second store exists, an innocent-looking pattern appears:

1. Write product to database        ✓
2. Write same product to search index

Two writes, one flow, straight from the application. What could go wrong?

1. Write product to database        ✓  succeeded
2. Write product to search index    ✗  failed (timeout, crash, deploy…)

Now the source of truth and the search index disagree — silently. The product exists but can't be found. Or worse, reverse the order and search advertises a product the database never committed.

And here's the physics (Bonus C, Type 1): no transaction spans two independent stores. You cannot make "write to Postgres AND write to Elasticsearch" atomic by wishing. The transaction boundary is the source-of-truth write. Everything after it is eventual — the only question is whether it's eventual by design or by accident.

The safe shape, conceptually: write to the source of truth first, one store, one transaction, one moment of commitment. Record the "this changed" event durably as part of that same commit — the outbox pattern: the event rides the same transaction as the data. Update derived stores asynchronously from that event stream (Part 7's machinery — at-least-once delivery, idempotent consumers). Retry safely, monitor the lag (Part 12), and keep the rebuild button — a derived store you can regenerate from truth is an inconvenience; one you can't is a hostage.

Keeping Stores in Sync

The plumbing between truth and its copies comes in a few shapes: event-driven updates (the owning service publishes changes via Part 7's pub/sub, consumers update their stores — fresh within seconds, needs the full Part 7 discipline of retries, DLQs, idempotency); the outbox pattern (the event written in the same transaction as the data, then relayed — closes the dual-write gap at its source); change data capture, or CDC (instead of the app publishing, the pipeline tails the database's own change log — nothing gets forgotten, because the database can't lie to its own log); batch sync (a scheduled job copies changes over — simple, robust, hours-fresh, perfect for the warehouse); rebuild/reindex pipelines (the nuclear option kept warm: regenerate the whole derived store from truth); and reconciliation jobs (the auditor: periodically compare stores and flag drift — Part 8's sweeper, generalized).

The trade-off axis is always the same: freshness vs complexity. Events are fresh and operationally demanding; batch is stale and boring. Boring is underrated — match the freshness to the requirement (Bonus B), not to the coolest option.

Eventual Consistency Across Stores — the Honest Bill

Every pattern above creates windows where stores disagree: a product updated in the database while search still shows the old price; an image uploaded while CDN and cache haven't caught up (Part 6); an order completed while the analytics dashboard updates in an hour, fine by design; a permission revoked while a derived access cache still honors it, Bonus I's "security hole with a timestamp," now between stores.

You know the question by heart now — it's the same one from Bonus F, and it applies between stores exactly as it did between replicas: "What can be wrong, for whom, for how long — and how do we recover?"

Bonus I's photocopy model was the preview. Polyglot persistence is the full building: many desks, many delivery schedules, one original.

Caches and Search Indexes Are Not Sources of Truth

Two stores tempt teams into forgetting the hierarchy. Both deserve explicit warning labels.

Caches (Part 6) improve speed, go stale, get evicted without warning, and are rebuilt constantly — that's their nature and their virtue. The failure mode: a "cache" that quietly became the only copy of something — session data, computed state nobody can recompute. The eviction that was routine yesterday is data loss today. If losing it hurts, it wasn't a cache; it was an unacknowledged database with no backups.

Search indexes are optimized for finding, not owning. They lag the source by design, and their superpower is being rebuildable — schema change? Reindex from truth. The failure mode: business logic reading canonical facts (price at checkout, stock at confirmation) from the index. Part 6's rule extends naturally: browse from the derived store; confirm from the truth.

The shared principle: a store you can afford to lose must never hold anything you can't.

The Operational Cost of Multiple Stores

Now Bonus C's muscles section, itemized. Every store you add — every single one — brings its own: deployment and provisioning; backups and restore drills (Part 8's rule — a backup never restored is a hope, now multiply by N stores, each with different tooling); monitoring and alerts (Part 12's table grows a row); access control and secrets (Part 11's vault gets new tenants, every store is a new place data can leak); patching and upgrades; scaling behavior; schema and index management; cost tracking (Part 8's four line items, per store); team knowledge (someone must actually understand it at depth); and incident ownership (a named human answers when it breaks at 2 AM).

Thirteen obligations. Per store. Forever.

That's not an argument against polyglot persistence. It's the price tag, and Bonus C taught you which column it lives in: this is a muscles question first, a budget question second, and almost never a physics question.

Polyglot persistence is powerful, and every new store is something the team must operate.

Polyglot Persistence in a URL Shortener

The last rehearsal before Blog 13 designs it for real. The inputs are already on the table: Bonus A's numbers (~4 writes/sec, ~4,000 peak read QPS, ~½ TB over three years), Bonus B's NFRs (p99 < 100 ms, 99.9% redirects, async analytics), Bonus C's edge-caching trade-off.

Now the store map. URL mappings need a relational DB or a key-value store as source of truth — the access pattern is pure key lookup (short code → URL), which fits key-value beautifully, but at ½ TB and 4 writes/sec, a boring relational database also does the job with muscles every team has; Bonus C's taxonomy says this is a maturity call, not physics. Hot redirects get a cache, absolutely (Part 6; Bonus A's 18 GB hot set) — derived, evictable, rebuildable, exactly as a cache should be. Click events go through an event stream/queue (Part 7): the redirect path publishes and moves on, the p99 budget never notices. Click analytics get an analytics store fed from those events, hours-fresh, by design. Object storage is needed only if the product stores real assets (QR images, exported reports) — not for mappings, since 500-byte records are database data, not blobs (Part 8's split). A search index is usually unnecessary — unless a management dashboard needs full-text search over millions of links, this tool stays on the shelf.

Count the honest answer: a basic shortener needs one database and one cache. A production-grade, analytics-heavy shortener earns four stores, each justified by a requirement, not a trend.

That's the whole discipline in one example: the stores follow the NFRs, the NFRs follow the business, and every store on the map has a reason written next to it.

When Not to Use Polyglot Persistence

The most senior section in this article. Skip or delay multiple stores when: scale is low (Bonus A's numbers say one database yawns through your traffic); access patterns are simple (key lookups and basic queries need no exotic tooling); the team is small (thirteen obligations per store, remember); operational maturity is limited (Bonus C's honest answer applies); one relational database satisfies the current NFRs (then it is the right architecture, not a compromise); search and reporting needs are basic (SQL LIKE and a nightly query beat an unowned Elasticsearch cluster); derived data can be computed later (because it can always be added later, that's what "derived" means); or the complexity cost exceeds the performance gain (run the arithmetic).

And the sentence to keep for design reviews: starting simple is not junior. It is disciplined architecture, with the evolution path already understood — truth first, events when needed, derived stores when the requirement, not the résumé, demands them.

Common Mistakes Engineers Make with Polyglot Persistence

Choosing databases by trend — the access pattern wasn't consulted, the conference talk was. Too many stores too early — forty tools, no labels. Treating the cache as source of truth — the eviction that became a data-loss incident. Treating the search index as truth — checkout prices from the thing that lags by design. Dual writes without failure handling — two stores, one hope. No rebuild strategy — a derived store you can't regenerate is a hostage. Ignoring lag between stores — Bonus I's window, unmeasured, now multiplied. Backups for the main DB only — every store holding anything real needs its restore drill. Access control on one store, defaults on the rest — Part 11's boundary, with a side door. No clear data ownership — two services writing one store is a merge conflict wearing production clothes. No observability for derived stores — Part 12's table needs every row filled. No reconciliation jobs — drift discovered by customers. Copying big-tech store maps — their muscles, not yours (Bonus C). Not documenting why each store exists — Bonus C's nine-field note, once per store, the REJECTED field especially.

Applying C.R.E.D

Clarify the data types and who owns each. Requirements per data type — consistency, freshness, retention, search needs (Bonus B). Estimate the volumes, ratios, and hot sets per store (Bonus A). Design the map: one source of truth, derived stores only where a requirement demands them, the sync pattern per link, the lag budget per copy, and an owner's name on every box.

A good architect does not ask only, "Which database should we use?" A good architect asks, "What data are we storing, how is it accessed, which store owns the truth, which stores are derived, and how do they stay consistent enough?"

From My Journey: An Architectural Lesson

Many systems begin with one database, and that's usually the right beginning. One store, one backup, one mental model. It carries the product further than most people expect.

Then the new needs arrive, each one reasonable: reads need to be faster. Search needs to be smarter. Analytics needs its own queries. Files need a home. Metrics pile up.

And the easy mistake has a seductive shape: add a new store for every new pain. Each addition genuinely helps — the system gets faster in one place. And quietly harder to reason about everywhere: more copies, more lag, more backups, more secrets, more places something can break at 2 AM.

The realisation that reorganizes the whole topic: adding another database was never the hard part. Any afternoon can do that. The hard part is the questions that arrive with it — which store owns truth, which are derived, how data moves between them, how the lag is handled, and who operates every single piece.

The trade stays honest in both directions. One database keeps the system simple and eventually strains under access patterns it wasn't shaped for. Multiple stores fit the workloads and multiply the consistency, cost, and maturity burden. Neither side is free; the skill is knowing which bill you're choosing to pay.

The lesson that stuck: polyglot persistence is not a database shopping list. It is a responsibility map for data.

Every new database solves one problem and creates one ownership obligation.

Key Takeaways

• Polyglot persistence means the right store per data shape and access pattern — never a collection for sophistication.
• SQL vs NoSQL is the wrong question; the nine real questions end with "what can this team operate?"
• One source of truth; everything else is derived — and if you can rebuild it from elsewhere, it's derived; if losing it loses business data, it's the truth.
• Ownership follows truth: the owning service publishes changes; derived stores never write uphill.
• No transaction spans two stores — the boundary is the source-of-truth write; everything after is eventual, by design or by accident.
• Dual writes are a disagreement waiting to happen; outbox, CDC, and events close the gap — with Part 7's full discipline attached.
• Match sync freshness to the requirement: events for seconds, batch for hours, and boring wins where boring suffices.
• A store you can afford to lose must never hold anything you can't — caches and indexes are copies, always.
• Every store adds thirteen operational obligations; the store count is a muscles question (Bonus C) before anything else.
• Starting with one database is disciplined architecture — with the evolution path understood in advance.

Interview Lens

The polyglot question hides inside every "design X" prompt the moment you draw a second data store, and the interviewer is watching whether you know what you just signed up for.

The weak move: drawing five stores because the reference architecture had five. The strong move narrates the map: "The relational DB is the source of truth for mappings; the cache is derived and evictable; click events flow through the stream to the analytics store, which is hours-fresh by design. Sync is outbox-plus-events, so there's no dual write; search index is deliberately omitted until a dashboard requirement earns it."

Truth named. Derived stores justified. Sync pattern chosen. One tool left on the shelf, with a reason.

Expect the probes: "What if the index write fails?" — the dual-write question, answer with outbox/events and the rebuild button. "The search shows stale data — is that a bug?" — "It's a designed window; here's its size and its monitor" (Bonus F's question, applied). "Why not just use one database?" — sometimes the correct answer is "we should," saying it wins more rooms than it loses. "Who owns the analytics store?" — the question behind the question is ownership, have a name-shaped answer.

The differentiator: the phrase "derived and rebuildable." Candidates who classify their stores by authority, not just by technology, sound like they've operated the disagreements.

Real-World Engineering Lens

Draw the truth map. One diagram: every store, marked SOURCE or DERIVED, with the sync arrow and its freshness labeled. If two boxes both claim SOURCE for the same data, you've found this quarter's incident early.

Run the delete test on every store. "If this vanished right now, can we rebuild it, from where, in how long?" Any store that fails the test needs either backups-and-drills or a demotion in importance. Today.

One nine-field note per store (Bonus C's template): why it exists, what was rejected, and the metric that says it's earning its keep.

Put lag-between-stores on the Part 12 dashboard — index freshness, projection lag, reconciliation drift — next to replication lag, where it belongs.

Hold the annual store audit. Every store re-justifies its thirteen obligations against a current requirement. The ones that can't get a deprecation plan — workshops need cleaning.

What's Next

Stop and look at the bench. Bonus A gave us the numbers. Bonus B gave us the requirements. Bonus C gave us the trade-off discipline. And today gave us the store map.

The sharpening is done. Section 2 begins.

Blog 13 — URL Shortener: The Gateway Problem of System Design. The first full design of the series — where every building block from Parts 4 through 12, and every method from the bonus run, converges on one deceptively simple problem. The numbers are estimated, the NFRs are written, the trade-offs are classified, the stores are mapped.

Time to draw the boxes.

Question for Readers

Draw your system's truth map from memory: every data store, marked SOURCE or DERIVED, with the sync arrow between them.

Two checks: Is any derived store secretly holding data you can't rebuild? And do any two boxes both believe they're the source?

Either answer is this week's finding.