Polyglot Persistence

A marketplace has orders in Postgres, sessions in Redis, product search in Elasticsearch, analytics in ClickHouse, and a document store holding seller profiles. Five teams chose five engines over five years, and on paper this looks like drift. It is actually polyglot persistence working correctly: each bounded context gets a store matched to its own access pattern, and no store is asked to serve a workload it was not built for. The alternative — one database for everything — is polyglot persistence's opposite number, and its cost shows up as query contortions and scalability ceilings rather than as operational variety.

The assignment exercise, done properly for a marketplace:

Two boundary rules keep this from becoming chaos. First, one writer per fact: only the orders service writes orders, and every other store holds a copy derived from it — Elasticsearch, Redis, and the analytics tables are projections of the system of record, never peer authorities. When two systems can both write the same fact, you no longer have polyglot persistence; you have two sources of truth and an eventual argument. Second, the boundaries follow the domain model (orders, catalog, search, sessions), not the technology — if two contexts would share one engine and one schema, that is one context split by accident.

A trap to avoid: adding a store per query instead of per context. Redis for a cache tier, Elasticsearch for search, ClickHouse for analytics are all defensible — but a 'graph microservice' whose only query is a single friendship lookup is a sixth technology to operate for one feature. The test: does this context have a workload family the current engine serves poorly (not one slow query), and will it still exist in two years? If the answer to the second question is no, use a materialized view, a precomputed table, or a cached adjacency list instead.

Interview framing that reads as experience: polyglot persistence is a distributed-systems decision wearing a database hat. Splitting storage per context is what lets each team scale, deploy, and evolve independently — and it is exactly what makes cross-context queries, transactions, and consistency your problem to engineer. Those glue questions — how the search index, the cache, and the ledger stay honest with each other — are the next chapter, and the cost of each additional engine is the one after.

The seller renames a product; search still shows the old name for ninety seconds; a cached page serves stale data for an hour; a report counts the order twice because the analytics pipeline delivered it twice. These are not three bugs — they are one design question with three answers: when the system of record changes, how does each derived store find out, and what does a reader see in the gap?

The three mechanisms, with their trade-offs:

1. SYNCHRONOUS dual write (write DB, then write the copy, in the request path)
   + copy always fresh after ack   − both must succeed or they diverge
   − latency = both services       − no atomicity across two systems
     (the classic dual-write problem: commit succeeded, index write failed
      ⇒ permanent divergence with no signal)

2. ASYNC outbox + relay (Debezium/CDC or poller on an outbox table)
   + one atomic commit makes the event durable with the row
   + decoupled latency, replayable, multiple copies fed from one stream
   − copies lag by the relay's lag (measure it)   − needs offset/DLQ handling

3. RECONCILIATION / rebuild (nightly compare, or full reindex from the log)
   + heals whatever the first two missed   − bounded staleness window = your SLA
   − full reindex of 20M docs is an operational event, not a click

Notice the shape of a correct system: (2) for delivery, (3) as the safety net, and (1) only where the copy must be atomically fresh — which usually means the copy is not a copy but the same transaction (a materialized view refreshed in-process). This is CQRS's logic appearing naturally: the write model updates the system of record; read models (search documents, cached aggregates, analytics rows) are rebuilt from its changes.

Per-store failure modes worth naming:

• Search index: near-real-time by nature (refresh interval ≈ 1 s), so brief staleness is normal — but a failed indexing consumer is not; monitor the consumer's lag against the DB's change stream, not just the index's document count.
• Cache: choose explicit semantics — cache-aside with TTL plus invalidation on write (stale window = TTL if invalidation is lost), or write-through (fresh but couples write latency to Redis). A cache without a version/invalidation story is a correctness bug with a hit-rate dashboard.
• Analytics store: exactly-once is unattainable in general, so make loads idempotent (upsert keyed on event id, or full-partition replacement — the lambda/batch-rebuild pattern) and dedupe downstream in queries where needed.
• Cross-store reads: if one page must join orders (Postgres) with profiles (document) and search results (Elasticsearch), accept the fan-out read or materialize a serving copy — do not attempt distributed transactions across the three.

The debugging story matters as much as the design: give every write a correlationId that rides through the outbox into every projection, so 'why is search stale?' is one query against the relay's offsets rather than three hours of log archaeology. In interviews, state the rule of thumb that signals seniority: derived stores may lag; they may not silently diverge — lag is measured and alerted, divergence is prevented by one atomic origin (outbox) and healed by reconciliation.

Adding Elasticsearch to the stack takes one afternoon: helm install, index a sample, ship the feature. Removing it two years later takes a quarter. Polyglot persistence is cheap to enter and expensive to leave, so the decision must be priced the way the exit price, not the entry price. Here is the honest ledger for each new engine:

The multiplier is not one cost — it is the number of pairwise integrations: stores synchronizing with each other grow roughly as n(n-1)/2, which is why a five-store landscape with mutual sync edges is a support burden that dwarfs any single engine's. Cap the count deliberately: most teams should be able to justify every store on one page — engine, context, access pattern, owner, integration edges, exit plan — and if two stores serve the same context, one of them is wrong.

Concrete decision heuristics:

1. Exhaust the features of the current store first. Postgres alone covers a staggering range: jsonb for semi-structured data, pg_trgm/full-text for search-lite, LISTEN/NOTIFY plus materialized views for read models, table partitioning for time-series-ish loads, pg_cron for jobs. Reach the boundary of those before adding a technology.
2. Buy the managed version or do not buy it. Self-hosted Kafka + Elasticsearch + Cassandra is a second team's worth of work; managed equivalents (MSK, Elastic Cloud, DynamoDB, Memorystore) convert ops tax into a bill — usually the right trade for a product team.
3. Require an owner and an exit plan at adoption: who is on-call for it, and how do we get the data out if we drop it? 'We'll figure it out later' is how abandoned clusters persist for years, still holding production data nobody trusts.
4. Re-review annually. Stores outlive their reasons; a cache added for a launch in 2023 may be a Redis Cluster you now operate at six figures for 40% hit rate.

Interview framing: the question 'would you add Redis here?' deserves a ledger answer — what it buys (latency, decoupling), what it costs (a technology, an invalidation contract, an edge in the sync graph), and what you tried first (a covering index, a materialized view, PgBouncer). Showing that you price exits and integration edges, not just features, is what separates an architect from an assembler of tools. The next topic splits an even bigger boundary: serving queries versus running analytics — Transactional vs Analytical Workloads.