Polyglot Persistence Without Distributed Regret

The Copy That Sent a Cancelled Order to Packing

An operations team adds a search index to its support console. The order database can find an order by id, but agents also need to search by customer name, address fragment, carrier reference, and exception note. The index makes those searches fast without placing unpredictable text queries on the transactional system.

Then one partition of the projection stalls.

At 10:02, a customer cancels an order. The order database commits the cancellation and releases the inventory reservation. At 10:09, a support agent finds the old indexed document, sees ready_to_pack, and asks the warehouse to hurry it along. The search service is available. Its query is fast. The result is even an accurate copy of what the order said seven minutes earlier.

The architecture failed because nobody decided what the copy was allowed to promise. A new store solved a real query problem, then quietly became a second place from which people acted on order state.

Polyglot persistence is the deliberate use of different storage systems for different workloads. It can be the simplest responsible design: transactions in one system, text retrieval in another, historical scans elsewhere, hot reads in a cache, blobs in object storage. The regret arrives when adding a store adds an unnamed authority, an invisible consistency boundary, or an operating obligation no team accepted.

A store should enter the architecture only with a contract for the facts it serves, the facts it owns, the path by which copies change, the stale behavior the product permits, the way drift is repaired, the policies that follow the data, and the evidence that would justify removing it.

A Store Must Solve a Named Workload

The search index in the support console has a defensible job. It serves text and multi-field retrieval over millions of orders without turning the order database into an accidental search engine. That claim can be tested with representative queries, latency targets, source load, index freshness, and the team’s ability to operate the result.

Other pressures can justify other stores. Analytical history favors large scans, repeatable transformations, and different retention from current transactional state. A cache may be warranted when a hot, tolerant read path would otherwise exhaust the source. An event log can decouple consumers that need independent replay. Object storage can hold large immutable objects more economically than a row store. Separate systems may also provide needed isolation between operational and analytical workloads.

“It scales” is not such a reason. Neither are team preference, a vendor feature list, or a possible future use. Before adding the search index, the team should be able to show which important query the current architecture cannot meet responsibly and why a smaller change—an index, a read replica, a bounded cache, a batch export, or a narrower feature—does not suffice.

That standard does not demand one database forever. It makes each departure from one database earn the permanent surface it creates.

Assign Authority to Facts, Not Products

Calling the order database “the source of truth” is not precise enough. Authority belongs to facts.

The order service may own lifecycle state and the accepted total. Payments may own the provider’s settlement result. Fulfillment may own the carrier handoff. Identity may own the customer’s verified email. Finance may govern a transformed definition such as recognized revenue. The search index can contain copies of all four without owning any of them.

For the support index, that distinction changes the interface. An agent may use search to discover the order, but an action such as ship, cancel, refund, or disclose an address must re-read the current authoritative state and recheck authorization. Search results should expose their source version or indexed time when staleness affects judgment. The UI must not turn a convenient projection into an enforcement boundary.

A derived store can still be critical. If customer support depends on it, its availability and recovery targets may be strict. “Derived” means that another named record or governed computation determines the fact; it does not mean disposable or unimportant. If the store contains facts that cannot be recovered or arbitrated from elsewhere, those facts need to be acknowledged as authoritative and protected accordingly.

The dangerous condition is independent mutation without a conflict rule. If support can change order state in the index while checkout changes it in the order service, disagreement is built into the design. One fact should have one authority at a given point in its lifecycle, or an explicit arbitration process when multiple writers are unavoidable.

Follow One Cancellation Across the Boundary

The update path determines whether the support index remains a projection or becomes a collection of guesses.

The naive request handler writes the order database, then writes the search index. If the database commits and the index call times out, the caller cannot infer whether the second write happened. A retry may duplicate work or overwrite a newer document. Reversing the order merely reverses the failure: search can say cancelled while the order remains active. Synchronous dual writes do not become atomic because they appear next to each other in application code.

Several paths can carry the cancellation more honestly:

• A transactional outbox commits the order change and a durable publication obligation together. A relay may lag or publish twice, so the indexer still needs idempotency, source versions, and monitoring.
• Change data capture reads committed database changes. It can serve many projections and retrofit legacy applications, but table mutations may omit business meaning. Schema changes, deletes, ordering, snapshots, and backfills become part of the consumer contract.
• A domain event such as OrderCancelled gives downstream systems a stable business fact to project. Its producer must preserve meaning and compatibility, and consumers must tolerate delay, duplication, replay, and any ordering the transport does not guarantee.
• A batch sync is often the clearest choice when the product can honestly tolerate minutes or hours. It still needs watermarks, completeness checks, late-data handling, visible freshness, and a way to rerun safely.

A controlled synchronous write can be reasonable inside a genuine shared transaction boundary or when partial failure is bounded, visible, idempotent, and safely reconciled. The burden is proof, not a blanket preference for asynchronous machinery.

For the cancelled order, the projection should apply only a source version newer than the document it holds. A duplicate cancellation becomes harmless. An older OrderPacked update cannot resurrect stale state. If ordering is only guaranteed per order, the consumer and replay tools must preserve that key. If the source cannot provide comparable versions, the design needs another explicit rule rather than arrival-time optimism.

The next chapter examines how commands, transactions, outboxes, events, and side effects establish these facts. At the store-selection boundary, the essential decision is already visible: choose the propagation semantics before approving the new store.

Freshness Must Change Product Behavior

“Near real time” does not tell an agent whether a search result is safe to act on. A useful freshness contract names the data class, the allowed delay, the signal that measures it, and the behavior after the limit is crossed.

The support index can tolerate a short delay for searchable notes while requiring a source read before any consequential action. A public catalog may tolerate stale descriptions but recheck price and availability at checkout. An analytical report may have a daily cutoff and a declared policy for late refunds. Access revocation may require urgent propagation and fail-closed behavior. These are different promises even when one index stores all four kinds of data.

Lag also has to be measured in terms of the promise. Queue depth can look healthy while one tenant or partition is stuck. Better evidence includes the oldest unapplied source time, source version versus indexed version, delete age, failed-record count, and freshness by partition or tenant. In the opening failure, an alert on the age of the oldest unapplied order version would reveal the stalled projection; average indexing latency might conceal it.

The degraded mode belongs in the design. When the freshness bound is breached, the console might label results as delayed, disable actions, fetch current order state on selection, fall back to a narrower source query, or hide the affected partition. Continuing silently is itself a product decision, usually made by accident.

Design Correction Before the First Backfill

Every derived store will eventually drift. A consumer bug skips a record. A schema change drops a field. A restore moves the source backward while the index remains ahead. A deletion fails on one shard. An operator replays old events with new transformation code.

The team needs both detection and repair. For the support index, a reconciliation job can compare sampled source and indexed versions, count missing and unexpected ids, test deleted and restricted orders, and check field-level transforms for high-risk data. A mismatch should identify a repair unit—order, tenant, time range, or partition—rather than merely produce a global percentage.

Repair may replay a retained log, reproject a partition, or rebuild the whole index from authoritative records. A full rebuild needs a concrete sequence: create a versioned replacement, load a consistent snapshot, catch up changes after the snapshot boundary, compare coverage and query behavior, switch reads, keep a retreat window, then remove the old copy. If the rebuild takes four days but retained changes cover only two, the advertised recovery path cannot work.

Restoring multiple authoritative stores is harder. Recovery order and reconciliation must respect which facts each one owns. A derived index usually follows the restored source; restoring an old index backup and declaring success may preserve exactly the drift the recovery was meant to remove.

“Rebuildable” therefore needs a measured duration, required inputs, catch-up capacity, traffic-switch method, and owner. Otherwise it is hope written as an adjective.

Every Copy Inherits Policy and Operations

The support index copies names, addresses, notes, perhaps payment references, and perhaps fields an agent is not allowed to see. The copy inherits the sensitivity, tenant isolation, residency, retention, deletion, audit, and access-review duties of those facts. Redacting the source while leaving an old search document, event payload, cache entry, analytical snapshot, export, or debug log is not deletion.

Minimize the copy before securing it. If search never uses the full address or payment reference, do not index them. Apply authorization at retrieval as well as ingestion when eligibility can change. Decide whether historical events may retain a deleted value and under which policy. Test revocation and deletion through every materialized surface, including caches and rebuild inputs.

The operational bill also follows the copy: capacity, upgrades, schema and index changes, backups or reproducible rebuilds, restore drills, observability, incidents, vendor limits, cost allocation, and on-call knowledge. A platform team can operate the search cluster without owning the meaning of ready_to_pack; the order team can own that meaning without knowing index internals. The contract must join those responsibilities instead of hiding the gap between them.

Ownership is credible when it includes money and a 03:00 action. Who sees projection lag? Who may disable the read path? Who repairs a poisoned record? Who approves a mapping change? Who follows a privacy deletion? Who decides that the store no longer earns its cost?

Admission Includes Removal

Imagine that the support product later gains acceptable search through the primary platform, or that agents stop using free-text investigation. The separate index may no longer justify its operating surface. Removing it will be easy only if the team knows its consumers.

Admission should therefore create the evidence needed for exit: read-path telemetry, named consumers, source fallbacks, versioned interfaces, retention rules, and a reason the store exists. Decommissioning can then proceed by finding consumers, moving or retiring their queries, draining update paths, preserving required records, revoking access, deleting data, removing alerts and runbooks, and confirming that cost has actually disappeared.

Ask the exit question before launch: What observable change would make this store unnecessary? “Never” may be an honest answer for a durable authority. It is a suspicious answer for an experimental projection.

The Polyglot Store Contract

Use one record per store, then repeat the authority fields for each material data class it carries:

Store and production role:
Named workload that the current architecture cannot meet responsibly:
Evidence for expected fit: query shape, latency, scale, isolation, retention, or cost:

Data class:
Authority for this fact and lifecycle stage:
Role here: authoritative, derived, cached, analytical, archival, or temporary:
Allowed decisions from this copy; decisions that must recheck the authority:

Update path and source version or ordering rule:
Partial-failure, duplicate, replay, and delete behavior:
Freshness bound, measurement, and degraded product behavior:

Drift checks and repair unit:
Rebuild or restore inputs, measured duration, catch-up, switch, and rollback:
Sensitivity, isolation, residency, retention, deletion, and audit rules:

Meaning owner; platform owner; on-call action; cost owner:
Consumers and read-path telemetry:
Decommission trigger, migration path, and final data disposal:

Smaller alternatives considered:
Evidence that would reverse this decision:

Apply the record to the support index, then break its projection for six hours on paper. Can the team detect the affected partition, prevent stale state from authorizing action, catch up or rebuild, and prove that cancelled and restricted orders are correct afterward? Next, remove the index on paper. The dependencies that become visible are part of its real cost.

Multiple stores are worthwhile when they make distinct workloads simpler without making the system’s facts mysterious. The architecture remains legible because authority is assigned per fact, every arrow has failure semantics, every copy has a repair path and policy, and every store can explain both why it entered and how it leaves. With those boundaries chosen, the design can move to the harder moment: how a command creates a fact and publishes its consequences without hiding partial failure.