Day 461: Polyglot Persistence and Data Integration

The core idea: Polyglot persistence works in production only when each fact has one authoritative home and every other store is fed through explicit, replayable integration paths instead of ad hoc dual writes.

Today's "Aha!" Moment

In Schema Evolution and Compatibility Modes, PayLedger learned how to change event schemas without breaking lagging consumers or replay jobs. That solved the "can old and new binaries still understand the same message?" problem. The next production question is harder: where should those messages go when one database can no longer serve every workload well?

PayLedger now has three very different needs. Payroll coordinators need strict transactional updates when a payroll run moves from draft to approved to settled. Support agents need fast search by employee name, bank reference, and payout status across millions of records. Finance analysts need multi-month aggregation over settlement fees and country-level payout patterns. PostgreSQL is a good home for the first job, but a poor fit for the other two once traffic and data volume grow.

The tempting mistake is to treat this as a shopping problem: add Elasticsearch for search, a warehouse for analytics, maybe Redis for hot reads, and call the architecture "modern." The real challenge is integration. If settled_at changes in PostgreSQL but the search index lags, support sees stale state. If the warehouse loads the same payout twice during a retry, finance reports drift. Polyglot persistence is not about owning many databases. It is about making multiple stores cooperate without creating multiple truths.

Why This Matters

Production systems rarely have one workload forever. The same payroll data that begins as OLTP rows later becomes search documents, risk features, reconciliation inputs, and executive reporting. Trying to force all of that into one engine often creates familiar pain: overloaded replicas, fragile hand-built reporting queries, bloated indexes, and operators arguing about whether the database is slow or the access pattern is wrong.

Splitting workloads across stores can absolutely help. A relational database preserves transactional invariants. A search index optimizes inverted lookups and faceting. A columnar or lakehouse-style analytics store handles long scans and wide aggregations far more efficiently than an OLTP engine. The benefit is real, but the failure modes move. Instead of one overloaded database, you now have consistency windows, replay requirements, change contracts, and multiple operational surfaces to monitor.

For PayLedger, the central question becomes: when a payroll fact changes, what is the authoritative record, how does that change leave the source system, and how do downstream stores apply it exactly once or at least idempotently? If that path is vague, every new store adds accidental complexity. If that path is explicit, specialized stores become an advantage rather than an integrity risk.

Learning Objectives

By the end of this session, you will be able to:

1. Explain when polyglot persistence is justified - Distinguish a legitimate workload mismatch from database sprawl disguised as architecture.
2. Trace a safe integration path across stores - Follow how authoritative writes, change capture, replay, and derived projections fit together.
3. Evaluate the operational trade-offs - Judge freshness, failure recovery, and ownership boundaries before adding another durable store.

Core Concepts Explained

Concept 1: Different stores solve different workload shapes, but authority must stay singular

PayLedger keeps payroll runs, settlement instructions, and idempotency keys in PostgreSQL because those records need transactional guarantees. A coordinator approving a payroll batch cannot tolerate duplicate payouts or half-applied state transitions. The system needs constraints, point updates, and predictable commit semantics. That is classic relational territory.

The same data becomes painful when used for other jobs. Support agents want to search "Garcia" and filter by failed SEPA transfers in the last 24 hours. Finance wants to compute average settlement fees by corridor across six months of data. Those queries are possible in PostgreSQL, but not at the same efficiency or operational safety once the table sizes and concurrency levels climb. Search engines and analytics stores exist because they optimize different read paths, storage layouts, and indexing strategies.

This is the legitimate case for polyglot persistence: one product domain, multiple access patterns, each with materially different performance and query needs. The mistake is to stop the analysis there. Before adding a second store, the team has to answer four ownership questions for every fact it cares about: where is the authoritative write accepted, what identifier travels across stores, what freshness is acceptable downstream, and whether downstream copies are allowed to derive or reinterpret the source data.

For PayLedger, the answers are explicit. PostgreSQL is authoritative for payout state. Elasticsearch holds a searchable projection of that state for operations staff. The warehouse holds historical facts and derived aggregates for finance. Those latter stores are valuable, but they are not allowed to invent settlement truth. That constraint sounds administrative, yet it is the mechanism that prevents a stale search index or a buggy ETL job from becoming an accidental source of record.

The trade-off is straightforward. Specialized stores let each workload run on machinery designed for it, but every additional durable copy creates a synchronization obligation. If the team cannot name the owner of a fact in one sentence, it is adding persistence diversity faster than it is adding architecture clarity.

Concept 2: Safe integration starts with one durable write and a replayable change stream

The worst version of PayLedger's write path looks like this:

API request
   |-- write payout row to PostgreSQL
   |-- index document in Elasticsearch
   `-- publish event for analytics

That looks efficient until the middle call fails. The request may commit in PostgreSQL but never reach Elasticsearch. Or the search index may update while the database transaction later rolls back. Direct dual writes push distributed coordination into application code, where retries, partial failure, and ordering bugs are hardest to reason about.

The safer pattern is to make the authoritative store the only place where the business write becomes durable, then let downstream systems subscribe to a change stream derived from that source. In PayLedger, the payroll API commits the business row and an outbox row in the same PostgreSQL transaction. Logical decoding or an outbox relay publishes that committed change to Kafka. A search projector consumes the event and updates Elasticsearch. A finance ingestion job consumes the same stream and lands normalized facts in the warehouse.

API request
   |
   v
PostgreSQL transaction
   |- payroll tables
   `- outbox table
   |
   v
CDC / outbox relay
   |
   v
Kafka topic
   |\
   | \-> search projector -> Elasticsearch
   `--> finance loader -> warehouse

This design does not eliminate distributed systems problems; it relocates them to a shape the team can manage. The atomicity boundary is local and clear: either the source-of-truth row and its outbox record commit together or neither does. Downstream consumers still need idempotency, ordering discipline, and replay support, but they no longer have to guess whether the original business write happened. That is a major simplification.

The previous lesson on schema evolution matters here immediately. Once changes are moving through Kafka and living long enough for replays, integration messages become durable contracts, not just internal implementation details. PayLedger cannot casually rename a field or change the meaning of amount_minor because search reindexing and warehouse backfills may process old and new events in the same week.

The trade-off is that freshness becomes asynchronous by design. Search results might lag committed state by a few seconds. Analytics may lag by minutes. That is acceptable only if the product acknowledges those windows and the platform makes lag measurable. Safe polyglot persistence is usually eventual consistency plus strong replay and observability, not fake synchrony spread across multiple stores.

Concept 3: Operating polyglot persistence means managing lag, repair, and drift explicitly

Once PayLedger adds Elasticsearch and a warehouse, it now owns a living pipeline, not just a database schema. The support team will ask why a payout shows as settled on the detail page but still appears as processing in search. Finance will ask why yesterday's fee totals changed after a backfill. Those are not edge cases. They are the normal operational cost of derived data.

The right response is not to promise perfect simultaneity. It is to define which inconsistencies are allowed, for how long, and how they are detected. PayLedger can say that PostgreSQL-backed payout detail pages are authoritative immediately after commit, search freshness should stay within 30 seconds, and finance dashboards are rebuilt hourly with replay-safe loads. That is a much more honest and operable contract than implying all views are equally current.

Three capabilities matter in practice. First, each projection needs an idempotent apply model, usually keyed by a stable event identifier and version, so retries do not duplicate or regress state. Second, each projection needs a repair path: reindexing Elasticsearch from the source stream, replaying warehouse loads from Kafka, or recomputing a derived table from retained events. Third, the team needs observability on the integration layer itself: outbox age, consumer lag, dead-letter rates, projection version skew, and reconciliation mismatches between source and derived stores.

This is also where architecture discipline starts shading into product semantics. If a support workflow can initiate a customer-visible action from a search result, the UI must either tolerate staleness or fetch final confirmation from the authoritative store before acting. If finance closes the books from warehouse data, the ingestion pipeline needs stronger completeness checks than a casual dashboard would. The technical mechanism and the business consequence are linked.

The next lesson, Systems of Record vs Derived Data, sharpens that distinction directly. Polyglot persistence tells you why multiple stores may exist. The follow-up lesson clarifies which of those stores are allowed to define truth when they disagree.

Troubleshooting

Issue: A payout is visible on the PostgreSQL-backed detail page but missing from search for several minutes.

Why it happens / is confusing: The business transaction committed, but the search projection is behind because the outbox relay or Kafka consumer lagged. Teams often misdiagnose this as a "database inconsistency" when it is really an integration freshness problem.

Clarification / Fix: Monitor outbox age and consumer lag as first-class metrics. In user-facing flows, label search as near-real-time and let authoritative detail views read from the source store when exact current status matters.

Issue: Warehouse totals double-count some payouts after a retry storm.

Why it happens / is confusing: The ingestion job treated events as append-only facts without deduplicating by stable event key or source version. Retries are normal in distributed pipelines, so "processed twice" is not exceptional behavior.

Clarification / Fix: Make downstream writes idempotent. Use immutable event IDs, source sequence numbers, or merge semantics that replace older versions instead of blindly inserting duplicates.

Issue: Engineers start adding direct writes from application code into Elasticsearch "for freshness."

Why it happens / is confusing: The search lag is visible to users, so bypassing the integration path feels like a targeted optimization. In reality it creates two write authorities and makes repair much harder after failure.

Clarification / Fix: Keep the source-of-truth boundary strict. If freshness requirements tighten, improve projection latency or product behavior; do not reintroduce ad hoc dual writes that undermine replay and auditability.

Advanced Connections

Connection 1: Schema Evolution and Compatibility Modes defines the contract carried by integration pipelines

Once change propagation is event-driven, message schemas become part of the storage architecture. Safe polyglot persistence depends on compatible evolution because search reindex jobs, warehouse backfills, and lagging consumers all rely on being able to read historical events long after the source schema has changed.

Connection 2: Systems of Record vs Derived Data turns integration topology into decision authority

This lesson explains how data moves across heterogeneous stores. The next one asks the governance question hidden inside that movement: when PostgreSQL, Elasticsearch, and the warehouse disagree, which one is allowed to settle the argument? That distinction is what keeps polyglot persistence from collapsing into truth by whichever dashboard updated last.

Resources

• [ARTICLE] Polyglot Persistence - Martin Fowler
  • Focus: Why different persistence models emerge in one system and why this is a workload-design decision, not just a tooling preference.
• [DOC] Debezium Outbox Event Router
  • Focus: A concrete implementation pattern for publishing committed relational changes without application-level dual writes.
• [ARTICLE] Turning the database inside out with Apache Samza - Martin Kleppmann
  • Focus: How logs and materialized views support replayable derived data systems.
• [DOC] Near real-time search - Elasticsearch
  • Focus: Why search freshness is usually measured in seconds rather than instantaneous transaction visibility.

Key Insights

1. Polyglot persistence starts with workload mismatch, not database collecting - Use multiple stores only when transactional, search, and analytical needs truly differ.
2. Integration safety depends on one authoritative write path - Commit once in the source of truth, then derive downstream views through replayable change capture.
3. Derived stores are operational systems, not passive copies - They need lag budgets, idempotent consumers, repair workflows, and explicit authority boundaries.