Polyglot Persistence: Assigning Systems by Invariant, Query Shape, Latency, and Ownership

Learning outcomes

Polyglot persistence means using more than one data technology because different data paths have different invariants and access patterns. It does not mean every service gets its favorite database. Every added store creates a second question: how does it stay correct enough when the source changes or failures interrupt synchronization?

1. Ownership first: source of truth versus derived purpose

A system of record is the authoritative owner for a fact or invariant. A derived view is a representation computed from source data to serve a different access pattern. A cache is usually reconstructable temporary state. A search index is usually a derived retrieval view. A graph projection may derive edges from transactional records. These labels are responsibilities, not product types.

For AtlasMart, payment and order acceptance remain authoritative in one transactional path. Search can receive an eventually updated order or product projection. If the search index disappears, AtlasMart should be able to rebuild it from authoritative data and retained change history. If the order database disappears, rebuilding from search is not an acceptable recovery plan.

2. The dual-write trap

The naive workflow is: commit order → update search index → return success. If the process crashes after the database commit but before the index update, the order exists but the derived view does not. Reversing the order is not safer: the index can expose data whose source transaction later fails. Retries can create duplicate side effects unless each operation is idempotent.

A distributed transaction protocol can coordinate some resources, but that introduces availability, blocking, topology, and product-support constraints and is not a universal answer. A common alternative is a transactional outbox: commit the business state and an event/outbox row in the same local transaction. A separate projector publishes or applies those events and can retry after failure.

3. Run the broken path and repaired path

import json, sqlite3, tempfilefrom pathlib import Pathroot=Path(tempfile.mkdtemp(prefix='atlasmart-polyglot-'))db=root/'atlasmart.db'; view=root/'search_view.json'print('AtlasMart Lesson 4 — system of record + replayable derived view')print('workspace:', root)con=sqlite3.connect(db)con.execute('PRAGMA journal_mode=WAL')con.executescript('''CREATE TABLE orders(id TEXT PRIMARY KEY, customer_id TEXT NOT NULL, status TEXT NOT NULL, total_cents INTEGER NOT NULL);CREATE TABLE outbox(event_id TEXT PRIMARY KEY, event_type TEXT NOT NULL, payload TEXT NOT NULL, processed INTEGER NOT NULL DEFAULT 0);''')def load_view():    return json.loads(view.read_text()) if view.exists() else {}def save_view(v): view.write_text(json.dumps(v, indent=2, sort_keys=True))# WRONG: dual write. Database commit succeeds, process 'crashes' before derived view write.with con:    con.execute('INSERT INTO orders VALUES (?,?,?,?)',('o-bad','c-1','paid',4200))print('\nWrong dual-write state after simulated crash:')print('system of record has o-bad =', bool(con.execute("SELECT 1 FROM orders WHERE id='o-bad'").fetchone()))print('derived view has o-bad =', 'o-bad' in load_view())# REPAIR: order + outbox event commit atomically.payload={'order_id':'o-good','customer_id':'c-2','status':'paid','total_cents':9900}with con:    con.execute('INSERT INTO orders VALUES (?,?,?,?)', tuple(payload.values()))    con.execute('INSERT INTO outbox(event_id,event_type,payload) VALUES (?,?,?)',('evt-1001','OrderPaid',json.dumps(payload)))print('\nAtomic source transaction committed order + outbox event.')# Idempotent projector: replaying same event overwrites same key instead of duplicating side effects.def project_pending():    v=load_view()    rows=con.execute('SELECT event_id,payload FROM outbox WHERE processed=0 ORDER BY event_id').fetchall()    for event_id,payload_json in rows:        p=json.loads(payload_json)        v[p['order_id']]={'customer_id':p['customer_id'],'status':p['status'],'total_cents':p['total_cents'],'source_event':event_id}        save_view(v)        with con: con.execute('UPDATE outbox SET processed=1 WHERE event_id=?',(event_id,))    return len(rows)print('projected events =', project_pending())print('projected events on replay =', project_pending())print('derived view:', json.dumps(load_view(), indent=2, sort_keys=True))# Rebuild proves derived data is disposable when source + event history are retained.view.unlink()with con: con.execute('UPDATE outbox SET processed=0')print('\nRebuild after deleting derived view: projected events =', project_pending())print('recovered o-good =', load_view().get('o-good'))print('Note: o-bad cannot be reconstructed from the outbox because the wrong path never emitted an event. Reconciliation would be required.')

Verified generation-time output

AtlasMart Lesson 4 — system of record + replayable derived viewworkspace: /tmp/atlasmart-polyglot-xykkxckuWrong dual-write state after simulated crash:system of record has o-bad = Truederived view has o-bad = FalseAtomic source transaction committed order + outbox event.projected events = 1projected events on replay = 0derived view: {  "o-good": {    "customer_id": "c-2",    "source_event": "evt-1001",    "status": "paid",    "total_cents": 9900  }}Rebuild after deleting derived view: projected events = 1recovered o-good = {'customer_id': 'c-2', 'source_event': 'evt-1001', 'status': 'paid', 'total_cents': 9900}Note: o-bad cannot be reconstructed from the outbox because the wrong path never emitted an event. Reconciliation would be required.

The first state is intentionally inconsistent: o-bad exists in the source but not the derived view. That is the exact failure window a two-step dual write creates. The repaired path commits o-good and evt-1001 together. The projector can run later, and running it again produces zero new work because processed events are tracked.

Then the lab deletes the derived JSON file and resets the outbox processed flags. Replaying reconstructs o-good. It cannot reconstruct o-bad because the broken path never recorded an event. This is why production designs also need reconciliation: compare source state with derived state and repair historical gaps that predate the reliable publication mechanism.

4. Every additional store adds a failure matrix

Adding search, cache, graph, or analytical stores expands the architecture. Now an incident can affect the source, the change capture mechanism, the queue or outbox reader, the projector, the derived store, or the network between them. Monitoring only “database is up” is insufficient.

5. Deliberately wrong approach: let the derived store become authoritative

A team may discover that search is easier to query than the transactional source and begin reading authorization, availability, or payment state from it. That silently changes the architecture: a view with allowed lag is now making correctness decisions. During projector delay, a revoked tenant permission could remain visible or an out-of-stock product could remain purchasable.

The repair is explicit read-model scope. Search may answer “which products match these words?” Checkout must revalidate current price, inventory, tenant authorization, and payment rules against the authoritative owners before committing a purchase. A derived view can accelerate discovery without owning the invariant.

6. Production judgment: polyglot architecture is a budgeted dependency

Add a store only when the requirement justifies its synchronization and operations tax. Record the owner team, source of record, data classification, publication mechanism, ordering scope, idempotency key, allowed lag, retention, replay capacity, rebuild steps, backup policy, access control, metrics, incident runbook, cost, and decommission plan. If those fields are unknown, the architecture is not ready.

The next lesson turns this chapter’s reasoning into a reusable decision notebook. Instead of choosing MongoDB, Redis, Cassandra, Neo4j, or a search engine by reputation, AtlasMart will first write down volumes, key distributions, latency percentiles, invariants, availability, recovery, compliance, skills, and cost constraints.

Verification checklist

• The wrong path visibly leaves source and derived state inconsistent.
• The source business row and outbox event share one local transaction.
• The projector is replayable and its update is idempotent for the same order key/event.
• Deleting the derived view demonstrates rebuildability.
• The lesson explicitly states that outbox/replay does not guarantee magical exactly-once delivery.

Authoritative references

• SQLite atomic commit — Official description of SQLite’s transactional durability/atomic-commit mechanisms used as the local source-of-record lab.
• SQLite write-ahead logging — Official WAL documentation; the lab enables WAL for the temporary database while remaining single-process.
• Debezium outbox event router — Official implementation documentation for the transactional outbox pattern in CDC-based architectures.
• Polyglot Persistence — Widely cited architecture framing for choosing different persistence technologies by data/workload need; use as design context rather than protocol specification.
• Dynamo: Amazon’s Highly Available Key-value Store — Primary example of explicit application-visible tradeoffs and ownership semantics in a distributed store.