NoSQL · 07

Polyglot Persistence

Using the right store for each job sounds obvious and is easy to get wrong. This page works through one e-commerce platform that combines PostgreSQL, Redis, Elasticsearch, Kafka and an analytics store: who owns which truth, how a write travels, how stores stay in sync without dual writes, what the user sees after a write, how each store fails and degrades, what each extra store really costs, and how to decide, and to migrate, with discipline.

Chapters

Stores in the case study

System of record

Compiled sample

Map · Fundamentals · Redis · DynamoDB · MongoDB · Elasticsearch · Caching · Polyglot

Chapter One - The Idea

What Polyglot Persistence Is, and What It Costs

Polyglot persistence means using more than one kind of data store in a single system, each chosen for the access pattern it serves best. The term is a play on "polyglot programming". A typical shop might keep orders in a relational database, sessions in Redis, search in Elasticsearch, and an event stream in Kafka. No single store is best at transactions, sub-millisecond lookups, relevance-ranked text search and replayable history at once, so the argument goes: use the right tool for each job.

The argument is true and dangerous. Every store you add is a second source of data that can disagree with the first, a new thing that can be down, and a new thing to back up, upgrade, secure, monitor and understand at 3 a.m. The craft is not "pick the best store per feature" but "add a store only when its benefit is greater than the permanent cost of operating it and keeping it consistent".

What you gain	What it costs, permanently
Each workload on a store built for it (latency, scale, features)	Data now exists in several places that must be kept in sync
Independent scaling of reads, search and writes	Consistency is no longer one transaction: it is eventual, and you must design for the gap
Failure isolation (a cache outage need not stop checkout)	More failure modes to design, test and rehearse
Features you cannot get elsewhere (ranking, aggregations, TTL, replay)	Every store is a skill to hire for, an upgrade to run, a bill to pay, a security surface to audit

Key insight: Polyglot persistence is like owning several specialised vehicles: a truck, a bike and a van each beat a car at one job, but you pay for insurance, parking and repairs on all of them, and you must never lose track of which one holds your luggage. Most people are better off with one good car until the truck is clearly justified.

This page uses one concrete case, an e-commerce platform, to show how stores fit together, who owns the truth, how data flows between them, what each store promises the user after a write, how each fails, and how to decide whether to add another. It connects the single-store pages of this section (page 02 Redis, page 04 MongoDB, page 05 Elasticsearch, page 06 Caching Patterns) with the messaging section, especially Event-Driven Patterns, and with SQL.

Chapter Two - The Case Study

An E-Commerce Platform with Five Stores

Take an online shop with products, customers, carts and orders. A realistic mature design uses five stores. The diagram shows how they relate, and the table says what each is for and what it owns.

Case study: e-commerce platform, five stores around one source of truth

Store	Role	Holds	Owns the truth of	If it is lost
PostgreSQL	System of record	Customers, products, prices, orders, payments, inventory counts, the outbox table	Everything the business must not lose	Restore from backup and WAL archive. Business incident
Redis	Cache and session store	Product pages, price lookups, cart-in-progress, login sessions, rate-limit counters	Nothing authoritative (sessions may be the only copy of a logged-in state)	Cache refills from Postgres; users may be logged out
Elasticsearch	Search projection	Denormalised product documents for search and facets	Nothing. Derived from Postgres	Rebuild the index from Postgres or by replaying Kafka
Kafka	Event log	OrderPlaced, PriceChanged, ProductUpdated events	The history of changes for the retention period	Consumers lose the replay window. Re-derive from Postgres where possible
Analytics store (a columnar warehouse)	Reporting	Flattened order and event facts for dashboards	Nothing. Derived	Reload from the log or the database

The shape matters more than the product names. There is exactly one writer of business truth (Postgres), and the other four are projections: copies shaped for a specific read pattern, updated from a stream of changes. That one-directional flow is the thing that keeps the design sane.

Why not just Postgres?

It is a fair question and chapter 08 returns to it. A single well-tuned PostgreSQL handles transactions, JSON, basic full-text search, a job queue and a notification channel. The five-store design is what you grow into when scale, latency or feature needs outrun that, and not before.

Chapter Three - Data Flow

Who Owns Which Truth, and How a Write Travels

Follow one write through the system. The key design rule: the user-visible commit happens in the system of record, in one transaction, and everything else follows asynchronously.

Placing an order: the data flow and what each store learns, in order

Question	Answer in this design
Where is "the order" authoritative?	The `orders` row in PostgreSQL. Nowhere else
Can Elasticsearch ever be ahead of Postgres?	No. It is only updated from committed changes
Can a store be behind Postgres?	Yes, by seconds in normal times and by hours after an outage. This lag is the price of decoupling
Who writes to Redis?	The read path (fill on miss) and the invalidation consumer. Never business logic that bypasses Postgres
What is a projection?	A derived copy built from the stream of changes, shaped for one read pattern, rebuildable
What does Kafka own?	The ordered history of events for its retention period. It is the transport and replay log, not the system of record

⚠ Two stores that both think they are the master

The classic polyglot bug: the product title is edited in the admin tool (Postgres) and also patched directly in Elasticsearch "just to fix search". The next sync overwrites the patch, or worse, nothing overwrites it and the two differ forever. One field, one owner. All edits go to the owner; everything else is regenerated.

Rule of thumb: If you cannot answer "which store is the authority for this field?" in one word, the design is not finished.

Chapter Four - Staying in Sync

Outbox, CDC, Idempotent Projections and Replay

Once data lives in several stores, the question is how a change in the system of record reaches the others reliably. The weakest answer is the most common.

Dual write vs transactional outbox

Why dual writes fail

A dual write is when application code writes to two systems in sequence. There is no transaction across a database and a search cluster, so any failure between the two writes (a crash, a timeout, a deployment, a rejected request) leaves them permanently different, with no record that anything went wrong. Retrying does not fix it: two concurrent updates to the same row can reach the second store in the opposite order from the first. The failure is rare per request and certain over millions of requests.

Transactional outbox

The fix is to make the intent to publish part of the database transaction. Alongside the business change you insert a row into an outbox table in the same transaction. Either both rows commit or neither does. A separate process then reads the outbox and publishes the events.

SQL - an outbox table and one transaction that writes the business row and the event

CREATE TABLE outbox (
  id            BIGSERIAL PRIMARY KEY,
  aggregate_id  TEXT        NOT NULL,        -- e.g. the product or order id (Kafka key)
  event_type    TEXT        NOT NULL,        -- e.g. ProductUpdated
  version       BIGINT      NOT NULL,        -- monotonic per aggregate, used by consumers
  payload       JSONB       NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- in the same transaction as the business change:
BEGIN;
UPDATE products SET name = 'City bike v2', version = version + 1 WHERE id = 'p1';
INSERT INTO outbox (aggregate_id, event_type, version, payload)
VALUES ('p1', 'ProductUpdated', 2, '{"id":"p1","name":"City bike v2"}');
COMMIT;

The version column lets consumers ignore stale or duplicate events (below). The relay can be a polling job (select unsent rows, publish, mark sent, using FOR UPDATE SKIP LOCKED to allow several relays) or log-based CDC.

Change data capture with Debezium

CDC reads the database's own transaction log instead of polling. Debezium is an open-source CDC platform; its PostgreSQL connector reads committed changes through logical decoding and the streaming replication protocol, using a replication slot, and supports the pgoutput plug-in (the standard logical decoding output plug-in in PostgreSQL 10 and later, no extra libraries needed) as well as decoderbufs. After a restart the connector resumes from its recorded log position. Debezium documentation (3.7 at the time of writing) also notes that some situations can produce duplicate events, so consumers must tolerate duplicates.

Connector configuration (abridged, illustrative; check property names against the Debezium docs for your version)

{
"name": "shop-outbox",
"config": {
  "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
  "plugin.name": "pgoutput",
  "database.hostname": "pg.internal",
  "database.port": "5432",
  "database.user": "debezium",
  "database.password": "<from a secret store>",
  "database.dbname": "shop",
  "topic.prefix": "shop",
  "table.include.list": "public.outbox",
  "slot.name": "debezium_shop",
  "transforms": "outbox",
  "transforms.outbox.type": "io.debezium.transforms.outbox.EventRouter"
}
}

The EventRouter single message transform is Debezium's helper for the outbox pattern: it turns outbox rows into properly keyed events on topics. Two operational points for any log-based CDC on PostgreSQL: an inactive replication slot makes the database retain WAL and can fill the disk, so monitor slot lag and consider limiting retained WAL with max_slot_wal_keep_size; and the connector needs its own database role with replication privileges.

Idempotent, version-guarded projections

Both outbox and CDC give at-least-once delivery: an event may arrive twice, and after a rebalance or retry events for different keys interleave. The projection must therefore be idempotent: applying the same event twice has the same result as applying it once. The usual recipe:

Key by aggregate id, so all events of one product are in one Kafka partition and arrive in order for that product.
Carry a version (or the log offset). The projection stores the last applied version per id and ignores events whose version is not newer.
Keep tombstones. After a delete, remember the version, otherwise a late older event resurrects the item.
For Elasticsearch, use the database id as the document _id, so a replay overwrites rather than duplicates (page 05 explains version_type=external).

Java - a version-guarded projection; replaying the log gives the same view (compiled and run)

import java.util.*;

/** An idempotent, version-guarded projection that can be rebuilt by replaying the log. */
public class ProjectionDemo {

  record Event(long offset, String productId, long version, String name, boolean deleted) {}

    /** Stand-in for the search index: productId -> (version, name). */
  static class SearchProjection {
      private final Map<String, long[]> versions = new HashMap<>();
      private final Map<String, String> names = new HashMap<>();

      void apply(Event e) {
          long[] cur = versions.get(e.productId());
          if (cur != null && cur[0] >= e.version()) {
              return;                       // duplicate or out-of-order: ignore (idempotent)
          }
          versions.put(e.productId(), new long[] { e.version() });
          if (e.deleted()) {
              names.remove(e.productId());  // keep the version so an older event cannot resurrect it
          } else {
              names.put(e.productId(), e.name());
          }
      }

      Map<String, String> view() { return new TreeMap<>(names); }
  }

  public static void main(String[] args) {
      List<Event> log = List.of(
          new Event(0, "p1", 1, "City bike", false),
          new Event(1, "p2", 1, "Hiking boots", false),
          new Event(2, "p1", 2, "City bike v2", false),
          new Event(3, "p1", 2, "City bike v2", false),   // redelivered duplicate
          new Event(4, "p2", 2, null, true),              // delete
          new Event(5, "p2", 1, "Hiking boots", false));  // stale event arriving late

      SearchProjection live = new SearchProjection();
      log.forEach(live::apply);
      System.out.println("live view:     " + live.view());

      // rebuild: start from an empty projection and replay the whole log
      SearchProjection rebuilt = new SearchProjection();
      for (Event e : log) rebuilt.apply(e);
      System.out.println("rebuilt view:  " + rebuilt.view());
      System.out.println("identical:     " + live.view().equals(rebuilt.view()));
  }
}

Running it prints live view: {p1=City bike v2}, rebuilt view: {p1=City bike v2} and identical: true: the duplicate, the stale event and the delete are all absorbed, and a projection rebuilt from an empty state by replaying the whole log ends in the same state.

Rebuilding a projection by replaying the log

Create a new, empty projection

▶

A new Elasticsearch index products-v2 (or a fresh Redis keyspace or table)

Replay from the start

▶

Reset a new consumer group to the earliest Kafka offset, or bulk-load a snapshot from PostgreSQL and then continue from the offset recorded at snapshot time

Run old and new side by side

▶

Compare counts and sample documents; the old projection keeps serving users

Switch

▶

Flip the alias or the read path to the new projection, keep the old one briefly, then delete it

Replay only works for as long as the log retains the history. For long-lived projections either use a compacted topic (latest value per key) or keep the ability to bulk-load from the database. Design for both: the log for catch-up, the database for a full rebuild.

⚠ Assuming CDC or an outbox gives exactly-once

Both are at-least-once. Exactly-once effects come from idempotent consumers, not from the transport. If a consumer is not idempotent, the first redelivery corrupts the projection.

Chapter Five - What the User Sees

Consistency Expectations per Store

Because projections lag, the same moment looks different through each store. The product decision is not "how do we make everything instantly consistent?" (you cannot, for free) but "what must the user be able to rely on right after a write, and how do we hide the gap for the rest?"

Store	After the write commits in PostgreSQL	Typical lag	How to hide or handle the gap
PostgreSQL	Immediately correct (read your own writes from the primary)	None on the primary; replica lag if you read from replicas	Read critical data (order status after checkout) from the primary or a session-consistent source
Redis cache	Possibly the old value until invalidated or expired	Until invalidation or TTL	Invalidate (delete) on change, short TTLs, and read from the database for the user's own just-changed data. See page 06 for cache-aside and invalidation patterns
Elasticsearch	New or changed item may not appear yet	Pipeline lag plus the refresh interval (default 1s, page 05)	Show the confirmation page from Postgres, not from search; on "my orders" read from the database, not from the index
Kafka consumers	Process after a delay proportional to their lag	Seconds normally, longer when a consumer is behind	Monitor consumer lag as the lag SLO
Analytics	Updated in batches or micro-batches	Minutes to hours	State the freshness on the dashboard ("data as of ...")

Read-your-writes in a polyglot system

The most visible bug is: "I added an item, I searched, it is not there". Avoid it by not using the projection for read-after-write flows: after a write, render from the system of record.
For a short window you may merge: the application remembers the id it just wrote (in the session) and adds it to the search results itself.
Alternatively, wait for the projection: the write path publishes the event and the UI polls "is version N visible?". This is complex; use it only where the product demands it.

Everywhere else, set the expectation in the interface: "your order will appear in search shortly" is cheaper than distributed transactions.

⚠ Promising stronger consistency than the pipeline provides

If the UI copy or an API contract says "immediately", but the data path includes Kafka and a search refresh, someone will file a bug every day. Write the real consistency contract per feature and per store, and review it like an API.

Chapter Six - Choosing

A Selection Matrix: Access Pattern by Store

Pick stores by access pattern, not by feature lists or fashion. For each pattern in the application, ask what the query looks like, how fast it must be, how fresh the data must be, and how much data there is. The matrix gives a starting point; "Good" means the store is built for it, "OK" means it works until scale or latency says otherwise, "Poor" means do not.

Access pattern	PostgreSQL	Redis	MongoDB / DynamoDB	Elasticsearch	Kafka	Warehouse
Transactions across entities (order + stock + payment)	Good	Poor	OK (limited, varies)	Poor	Poor	Poor
Lookup by primary key, very low latency	Good	Good	Good	OK	Poor	Poor
Cache of derived or hot data, TTL	OK	Good	OK	Poor	Poor	Poor
Sessions, counters, rate limiting	OK	Good	OK	Poor	Poor	Poor
Ad hoc relational queries and joins	Good	Poor	Poor	Poor	Poor	Good
Flexible-schema documents at scale	OK (JSONB)	Poor	Good	OK	Poor	OK
Full-text search with ranking and facets	OK for modest needs (built-in full-text)	Poor	OK (varies)	Good	Poor	Poor
Replayable history of changes, fan-out to many consumers	Poor	Poor	Poor	Poor	Good	Poor
Large scans and aggregates over history	OK to a point	Poor	Poor	Good (logs, metrics)	Poor	Good
Work queue with retries	OK (`SKIP LOCKED`)	OK (streams, lists)	Poor	Poor	OK (but see page 01 of messaging)	Poor

Treat the matrix as a hypothesis list, not a verdict. Every "Good" outside PostgreSQL has to be weighed against the cost in chapter 08. Where PostgreSQL says "OK", try it first.

If the requirement is...	First try	Add a store when...
"Search by words in titles and descriptions"	PostgreSQL full-text search (`tsvector`, GIN index)	You need relevance tuning, typo tolerance, facets over millions of documents, or search load that hurts the primary
"Cache hot reads"	Application cache in-process, or PostgreSQL read replica	Latency target or read volume exceeds what the database serves at acceptable cost
"Notify other services of changes"	Outbox table and a poller; `LISTEN/NOTIFY` for light, loss-tolerant cases	Many consumers, replay, or high volume: introduce Kafka
"Store flexible attributes per product"	PostgreSQL `jsonb` column with a GIN index	Scale or access pattern is truly document-shaped at volume
"Reports for management"	Read replica or a nightly export	Analytical queries slow the OLTP database: a warehouse

Chapter Seven - When Things Break

Failure Scenarios and Graceful Degradation

A polyglot system has more ways to fail than a single database, but if it is built around one system of record it can fail softly: most failures degrade a feature rather than stopping the business. The diagram ranks the failures; the table gives symptoms and responses.

Failure scenarios: what breaks, what the user sees, how it degrades

Scenario	Symptoms you see	Contain it with	Recover with
Search index stale (consumer stopped, events lost, mapping rejected documents)	New products missing from search; deleted products still appear; count mismatch with Postgres	Dead-letter topic for rejected documents; alert on index freshness (newest document timestamp vs source)	Fix the cause, replay the log, or rebuild into a new index and swap the alias
Cache down or flushed	Latency jumps, database CPU spikes, possible stampede on popular keys	Timeouts and a circuit breaker so the app does not hang on Redis; request coalescing; DB capacity planned for a cold cache	Bring Redis back; warm gradually
Cache returns stale data	Users see an old price or stock	Invalidate on event, short TTL for volatile fields; read the price at checkout from the database	Delete the keys; fix the missed invalidation
Projection lag	Consumer lag metric grows; features based on that store go stale	Lag SLO and alert; autoscale consumers; keep processing idempotent so catch-up is safe	Add consumers (up to partition count), fix slow handler, replay if needed
Outbox backlog	Oldest unsent outbox row is minutes old	Alert on age of the oldest unsent row; monitor replication slot lag for CDC	Restore Kafka or the connector; the relay catches up. No data lost
Dual-write drift (a legacy path writes directly)	Stores disagree with no error anywhere	Remove direct writes; a periodic reconciliation job comparing counts and checksums	Rebuild the projection from the system of record
Poison event	One consumer stuck retrying one record forever; lag grows	Bounded retries, then dead-letter queue; skip with an alert	Fix the bug, reprocess the dead-lettered events

Reconciliation is a feature, not an admission of failure

Run a small, cheap job that compares the system of record to each projection (row counts, max updated timestamp, a sample of ids) and alerts on drift. It catches the failures no per-request error would ever reveal, such as dropped events and dual-write leftovers.

⚠ Letting a non-core store sit on the critical path

If checkout calls Elasticsearch or waits for Redis, the weakest store sets your availability. Keep projections off the synchronous path of anything that earns money, and give every call to them a timeout and a fallback.

Chapter Eight - The Bill

Operational Cost and the "Do We Need This Store?" Checklist

Every datastore has a fixed cost that does not depend on how much you use it. Before adding one, price all of it, and compare with what PostgreSQL can already do.

Cost of one more store	Questions to answer before adding it
Backups and restore	Who owns backups? Has a restore been tested? What is the RPO/RTO? (For a projection, "rebuild" may be the plan, but how long does it take?)
Upgrades and patches	Who upgrades it, how often, with what downtime? What is the client compatibility story?
Monitoring and alerting	What are its golden signals (lag, memory, disk, latency, error rate)? Dashboards, alerts, runbooks
On-call surface	Who is paged when it breaks? Do they understand it at 3 a.m.?
Security	Authentication, TLS, network policy, secrets, audit, patching of CVEs
Skills and hiring	How many people on the team can operate it? What is the bus factor?
Data governance	Does it hold personal data? How is deletion propagated (chapter 09)?
Money	Licence, managed-service price, cluster size, cross-zone traffic
Cognitive load	Another consistency contract and another failure mode in every design discussion

The boring-technology argument

The "innovation tokens" idea (popularised by Dan McKinley's essay "Choose Boring Technology") says a team can afford only a few unproven or unfamiliar technologies; spend them on what differentiates your product, and choose well-understood tools for the rest. For storage, the practical translation is: prefer the store your team already runs well, and make the new one earn its place with a measured problem, not a forecast.

Start with what PostgreSQL already does

Before a new store, check whether PostgreSQL can serve the need. Verified against the PostgreSQL 18 documentation (current at the time of writing):

Need	PostgreSQL feature	What to know (limits)
Flexible attributes, JSON documents	`jsonb` column, operators, GIN indexes	Good for moderate document workloads; not a substitute for a document database at very large scale or for relevance search
Search by words with stemming and ranking	Built-in full-text search: `tsvector`, `tsquery`, the `@@` operator, ranking functions, GIN indexes	Linguistic normalisation through dictionaries, phrase search with `<->`. Fewer features than a dedicated engine for typo tolerance, facets and tuning, and it competes with OLTP for resources
Job queue	`SELECT ... FOR UPDATE SKIP LOCKED`	Fine for modest throughput; you build retries and visibility yourself
Light notifications	`LISTEN` / `NOTIFY`	A notification is sent to sessions listening at that moment, is delivered only after the sending transaction commits, and the payload must be shorter than 8000 bytes in the default configuration; the queue is bounded and a full queue makes `NOTIFY` fail at commit. Treat it as a wake-up signal, not a durable queue: a listener that is down misses it
Cache of derived data	Materialized views, `UNLOGGED` tables	Fine for small, slowly changing derived data; refresh is your responsibility
Reporting	Read replica, partitioning	Works until heavy analytics hurt the primary

The LISTEN/NOTIFY row is worth a second look: it is excellent as the "something changed, go look at the outbox" nudge that lets a relay avoid tight polling, and a poor choice as the only carrier of events, because it carries no history and no delivery guarantee.

A decision checklist

Question	If the answer is "no" or "not sure"
1. Do we have a measured problem (latency, load, missing feature), not a forecast?	Do not add a store yet; measure first
2. Have we tried the PostgreSQL feature for this and found it insufficient, with numbers?	Try it; tune queries and indexes first
3. Can the new store be a projection (rebuildable) rather than a second source of truth?	Redesign; a second master is the most expensive mistake
4. Is there a reliable sync mechanism (outbox or CDC) and an idempotent consumer?	Build that first
5. Do we have an owner, a runbook, monitoring, backups and an on-call who understands it?	Not ready to run it in production
6. What does the system do for users when this store is down?	Define the degraded mode before launch
7. How do personal-data deletions reach it?	Do not store personal data there until answered
8. Is the benefit bigger than the permanent cost for a team of our size?	Probably not; revisit later

Chapter Nine - Governance and Discipline

Personal Data, Deletion, Anti-Patterns and a Migration Playbook

Data governance across stores

Personal data copied into five stores must be found, controlled and deleted from all five. This is where polyglot designs quietly break the law of the land. Under the EU GDPR the right to erasure (Article 17) obliges a controller to erase personal data without undue delay when the right applies, and the controller must generally respond to such requests within one month (Article 12), which can be extended in some cases. This page is engineering guidance, not legal advice; ask your privacy team what applies to you.

Store	Where personal data hides	How deletion reaches it
PostgreSQL	Customer and order tables, backups, WAL archives, logs	Delete or anonymise the rows; define backup retention so old backups age out; keep a record of requests
Redis	Sessions, cached profiles, cached pages with names	Delete keys on the erasure event; use TTLs so nothing lives forever
Elasticsearch	Search documents with names, emails, addresses	Delete by id on the erasure event (and from replicas by design); do not index fields you do not need to search
Kafka	Events with personal data in the payload	Hard, because logs are append-only. Keep personal data out of events (send ids and fetch from the owner), use compacted topics with tombstones, short retention, or encrypt per-subject and delete the key (crypto-shredding)
Analytics	Facts with user ids and attributes	Pseudonymise on load; apply deletion or key removal in the warehouse; refresh derived tables

A deletion request is recorded in the system of record

▶

One authoritative "erased" state and an ErasureRequested event

Every store has a consumer for that event

▶

Redis keys deleted, Elasticsearch documents deleted, analytics rows removed or keys destroyed

A reconciliation job verifies it

▶

Searches the projections for the subject and reports leftovers; the result is kept as evidence

Data minimisation helps most

The cheapest deletion is data that never reached the other stores. Put identifiers, not names and emails, into events and search documents unless the feature truly needs them.

Anti-patterns

Anti-pattern	Why it hurts	Instead
Resume-driven architecture: adding a store because it is interesting	Permanent cost without a measured need	Use the chapter 08 checklist
Two masters for the same field	Drift that no process fixes	One owner per field; others are projections
Dual writes	Silent divergence on any partial failure	Outbox or CDC
Using Kafka or a cache as the system of record	Retention expiry or eviction becomes data loss	Keep a durable database as the authority
Projection on the synchronous critical path	The weakest store sets your availability	Async updates; fallbacks and timeouts
Joins in the application across stores	N+1 calls, inconsistent snapshots	Denormalise into the projection built for that query
No rebuild procedure	The first mapping change or corruption becomes an incident	Write and rehearse the rebuild
Forgetting the deletion path	Personal data lingers in forgotten stores	Erasure events and reconciliation
One shared cluster for everything	A noisy projection takes down the rest	Separate clusters or quotas per workload

Migration playbook: adding a store to a running system

Step	What you do	Exit criterion
1. Prove the need	Measure the problem; try the PostgreSQL option; write the decision down	A number that the new store must improve
2. Define ownership	The new store is a projection; list fields and their owner	No field has two owners
3. Build the sync	Outbox or CDC into Kafka; idempotent, version-guarded consumer	Replaying the same events twice changes nothing
4. Backfill	Bulk-load existing data from the system of record, recording the log position	Counts and sample records match
5. Shadow read	Send read traffic to both; compare results, do not serve the new one yet	Differences explained or fixed
6. Gradual cutover	Route a small percentage of reads to the new store behind a flag	Latency and error rate at or better than target
7. Degrade test	Kill the new store in a test environment; verify the fallback works	The product stays usable
8. Operate	Dashboards, alerts, runbook, backup or rebuild drill, on-call training, deletion path	Owner and on-call named
9. Remove the old path	Delete the old query code or the old store if it is replaced	No dead path left to rot

The same playbook runs in reverse when removing a store that no longer earns its cost. Consolidation is a legitimate and often underrated move.

Chapter Ten - Interview

Questions and Design Scenarios

Crisp answers for the questions that come up in interviews on this topic.

Q: What is polyglot persistence?
Using several data stores in one system, each chosen for the access pattern it serves best (for example PostgreSQL for transactions, Redis for caching, Elasticsearch for search, Kafka for an event log).
Q: What are its main costs?
Data synchronisation and eventual consistency, extra failure modes, and the operational burden of each store: backups, upgrades, monitoring, security, skills and on-call.
Q: What is a system of record?
The one store that is authoritative for a piece of data. Other stores hold derived copies (projections) that can be rebuilt from it.
Q: Why is dual writing a bad idea?
There is no transaction across two systems, so a failure between the writes leaves them different without any error. Retries can also reorder updates. Use an outbox or CDC.
Q: Explain the transactional outbox.
Write the business change and an event row to an outbox table in the same database transaction, then have a relay (poller or CDC) publish it to a log. Atomic on the database side, at-least-once afterwards, so consumers must be idempotent.
Q: Outbox vs CDC?
Both need the outbox row to be atomic. Polling relays are simple but add latency and load; log-based CDC such as Debezium reads the transaction log, with low latency and no polling, but needs replication slot management and database privileges.
Q: How do you make a projection idempotent?
Key events by aggregate id, carry a monotonic version, store the last applied version per id and ignore older or equal ones, and keep tombstones for deletes. For Elasticsearch, use the source key as the document _id.
Q: How do you rebuild a search index?
Create a new index, bulk-load from the database or replay the log from the beginning, verify, then switch an alias. Keep the old one until the new one is proven.
Q: What does the user see right after a write?
The system of record is immediately consistent; projections lag. Render read-after-write flows from the system of record, show honest expectations in the UI, and monitor lag.
Q: What happens if Redis goes down?
Reads fall back to the database (slower, more load). The system must be sized for a cold cache, use timeouts and circuit breakers, and avoid stampedes.
Q: What happens if Elasticsearch goes down?
Search degrades; ordering and checkout must be unaffected because search is a projection off the critical path. Fall back to a simple query or message, then rebuild or replay.
Q: Is LISTEN/NOTIFY a message queue?
No. It is a lightweight signal: delivered to currently listening sessions after commit, small payload (under 8000 bytes by default), no history. Use it to nudge a relay to read an outbox table, not to carry data that must not be lost.
Q: How do you handle GDPR deletion across stores?
Record the erasure in the system of record, publish an erasure event, have each store delete or anonymise, handle Kafka via compaction, keeping personal data out of events, or crypto-shredding, and reconcile to verify. Minimise the personal data copied around in the first place.
Q: When should you NOT add another store?
When there is no measured problem, when a PostgreSQL feature (JSONB, full-text search, SKIP LOCKED, materialized views) already meets the need, when the team cannot operate it, or when it would become a second source of truth.
Q: What is a projection?
A derived, denormalised copy of data built from a stream of changes and shaped for one read pattern. Rebuildable by replay.

Design scenarios (outline of a good answer)

"Design search for a catalogue of 5 million products with 50 writes per second." PostgreSQL as record; outbox with Debezium to Kafka keyed by product id; an idempotent indexer to an Elasticsearch alias with the DB id as _id and the version for ordering; reindex runbook with a new index and alias swap; fallback to a database query if search is down; lag and freshness alerts; start with PostgreSQL full-text search if the need is modest and say when you would graduate.
"Our product page is slow and the database is hot." Measure first; cache-aside in Redis for the read model with event-driven invalidation and TTL; protect against stampedes; keep price at checkout read from the database; plan for a cold cache; mention the read replica as the lower-cost first step.
"An order was placed but never shows in analytics and search." Follow the flow: committed in Postgres? outbox row present? relay or CDC lag? Kafka topic has the event? consumer lag or dead-letter? Check version guard dropping it. Fix, replay, add the reconciliation job so it is detected next time.
"We were asked to delete a user across the platform." Authoritative erase in Postgres, ErasureRequested event, per-store consumers, Kafka strategy (no personal data in events, compaction or crypto-shredding), backups retention, verification report.
"Management wants to add MongoDB because it is flexible." Ask for the measured need; try JSONB first; if adopted, name it a projection or a clear owner of a distinct aggregate, with the outbox, ownership table and operational checklist, and a removal plan if it does not earn its keep.

Chapter Eleven - Summary

Cheat Sheet and Recap

Cheat sheet

Topic	Rule	Detail
Number of masters	One per field	Everything else is a projection
Sync mechanism	Outbox or CDC, never dual write	At-least-once; consumers idempotent
Event ordering	Key by aggregate id	One Kafka partition per key preserves order
Idempotent projection	Version guard + tombstones	ES `_id` = source key
Rebuild	New projection, replay or bulk-load, alias swap	Needs retention or a snapshot source
Read-after-write	Read from the system of record	Projections lag by design
Cache failure	Fall back to DB; size for a cold cache	Timeouts, circuit breaker
Search failure	Degrade search only	Not on the critical path
`LISTEN/NOTIFY`	Signal, not a queue	Payload under 8000 bytes (default), delivered after commit, no history
Adding a store	Measured need + owner + runbook + deletion path	Try PostgreSQL features first
Personal data	Minimise, propagate erasure, reconcile	Kafka: compaction, no PII in events, crypto-shredding

∑ Polyglot persistence - what to remember

Each extra store is a permanent cost: another copy to keep consistent, another failure mode, another thing to operate.
One system of record; all other stores are rebuildable projections fed by one-way flows.
Never dual write. Use a transactional outbox or CDC, accept at-least-once, make consumers idempotent and version-guarded.
Design the user-visible consistency per store and read-your-writes from the record.
Make non-core stores fail soft: timeouts, fallbacks, lag alerts, reconciliation.
Start with PostgreSQL features (jsonb, full-text search, SKIP LOCKED, LISTEN/NOTIFY as a signal) and add a store only for a measured need.
Plan governance from the start: where personal data lands and how deletion reaches every store.

Version note

Checked on 2026-10-31: PostgreSQL 18 documentation for NOTIFY (payload under 8000 bytes in the default configuration, delivery only after commit, bounded queue that fails NOTIFY at commit when full, no durability described) and full-text search concepts (tsvector, tsquery, @@, ranking, GIN); Debezium 3.7 PostgreSQL connector documentation (pgoutput and decoderbufs, replication slot, resumes from recorded position, possible duplicate events); Elasticsearch 9.x documentation for refresh and routing facts referenced from page 05. The Debezium connector JSON and the outbox SQL are illustrative and were not executed against a live system. The Java projection sample was compiled and run. GDPR article references are a general pointer, not legal advice. Choose Boring Technology is an essay by Dan McKinley, referenced from memory as an idea, not quoted.

Next in this section

05 Elasticsearch - the search projection in this case study.
06 Caching Patterns and 02 Redis - the cache layer.
01 NoSQL Fundamentals and the Choosing a Database map - the decision guide.
Event-Driven Patterns and the Messaging map - outbox, CDC, idempotence.
SQL - the relational features to exhaust first.

← 06 · Caching Patterns
NoSQL map

LearningTree

Polyglot Persistence | NoSQL