Polyglot Persistence — Dual Write Failure Omits Search

Why Polyglot Persistence Fails When Writes Aren't Atomic

Polyglot persistence means using different storage technologies for different data needs within the same system — PostgreSQL for transactions, Elasticsearch for search, Redis for caching. The core mechanic is that each write operation must succeed in every store for the system to remain consistent. In practice, this introduces a dual-write problem: when you update a record in both a relational database and a search index, a failure in one leaves the system in an inconsistent state. The search index may miss the update, causing stale or missing results. Use polyglot persistence when query patterns demand specialized engines — e.g., full-text search, geospatial queries, or high-speed caching — that a single database cannot serve efficiently. It matters because it optimizes read performance and scalability, but only if you handle write failures with patterns like outbox, change data capture, or distributed transactions. Without these, your search becomes silently incomplete.

Database Types and Their Sweet Spots

Each database type excels at specific access patterns. Relational databases enforce ACID and support complex joins – they're for transactional core. Document databases tolerate schema variations – ideal for catalogs. Key-value stores provide constant-time lookups – perfect for session data. Search engines invert indices for fast full-text queries. Graph databases traverse relationships efficiently. Time-series databases optimize for write-heavy append-only data. Choosing the wrong database for a pattern leads to painful workarounds: for example, using a relational database for full-text search leads to inefficient LIKE queries and poor relevance ranking.

# Package: io.thecodeforge.python.system_design

# Typical polyglot architecture for an e-commerce system:

# PostgreSQL — relational: users, orders, products, payments
# - ACID transactions: order.create() and inventory.decrement() atomically
# - Complex queries: reporting, joins between entities
# - Example: user creates an order

# Redis — key-value: sessions, caching, rate limiting
# - Sub-millisecond reads: session token → user object
# - TTL-based expiry: sessions expire automatically
# - Example: cache product page for 10 minutes

# Elasticsearch — search: product search with relevance
# - Full-text search with typo tolerance
# - Faceted search: filter by price, brand, rating
# - Example: user searches 'wireless headphones'

# MongoDB (document) — product catalogue
# - Flexible schema: different products have different attributes
# - Laptop has CPU, RAM; T-shirt has size, colour
# - Example: store varied product attributes without schema migration

# Neo4j (graph) — social features, recommendations
# - 'Users who bought X also bought Y'
# - Friend-of-friend queries
# - Example: find all users within 3 hops in social graph

# InfluxDB (time-series) — metrics, analytics
# - Write-optimised for timestamped data
# - Example: page views, API response times

Data Consistency Across Systems

Data consistency across multiple databases is the central challenge of polyglot persistence. When the same logical entity (e.g., a product) lives in PostgreSQL (source of truth) and Elasticsearch (search index), any update must propagate to both. Two common patterns: dual writes (application writes to both) and change data capture (CDC) where the primary database's write-ahead log is streamed to secondary systems. Dual writes are simple but fragile – partial failures leave data permanently inconsistent. The outbox pattern mitigates this by writing a message within the same transaction as the primary write, then having a separate process deliver it asynchronously. CDC avoids application-level dual writes entirely but adds infrastructure complexity (Debezium, Kafka).

import asyncio

# When a product is created: must update PostgreSQL AND Elasticsearch
# Option 1: Dual write (naive — risks partial failure)
async def create_product_bad(product):
    await postgres.insert('products', product)      # succeeds
    await elasticsearch.index('products', product)  # what if this fails?
    # Product exists in Postgres but not in search — inconsistency

# Option 2: Write to primary, sync via CDC (Change Data Capture)
# Debezium reads PostgreSQL WAL → publishes to Kafka → Elasticsearch consumer
# Primary write is source of truth; Elasticsearch is eventually consistent

# Option 3: Outbox pattern
async def create_product_outbox(product):
    async with postgres.transaction():
        await postgres.insert('products', product)
        await postgres.insert('outbox', {
            'event': 'product_created',
            'data': product,
            'processed': False
        })
    # Separate process reads outbox and syncs to Elasticsearch
    # If it fails, retries safely (at-least-once delivery)

print('Outbox pattern ensures eventual consistency')

When to Adopt Polyglot Persistence

Polyglot persistence adds operational cost. Adopt it only when a single database clearly fails to meet requirements. Signs you need multiple databases: your relational database has a 3-second full-text search query, your document database can't enforce a unique constraint across documents, your key-value store can't do aggregation queries. Start with one database (usually relational) and add others incrementally. The rule: each new database must solve a problem that the existing stack cannot solve without significant hackery. If you can solve it with a secondary index, a materialized view, or a dedicated read replica, do that first.

Operational Complexity Realities

Running a polyglot system means managing multiple database technologies, each with its own backup strategy, monitoring stack, scaling approach, and failure modes. You need expertise in each database – a PostgreSQL DBA might not know Elasticsearch shard sizing. Monitoring must cover each database's health metrics. Incident response becomes more complex because a single business transaction touches multiple systems. Invest in automation: consistent deployment via IaC, standardized monitoring dashboards, and runbooks for each database. The cost of this operational overhead must be justified by the business value gained from using the right tool.

Data Synchronization Patterns (Outbox, CDC, Batch)

Three common patterns to synchronize data between databases: 1) Outbox pattern: write to primary and an outbox table in the same transaction. A separate process reads the outbox and pushes to secondary systems. Guarantees at-least-once delivery. Requires deduplication. 2) Change Data Capture: use a tool like Debezium to stream changes from the primary database's transaction log. Decouples application from sync logic. Adds infrastructure components (Kafka, connectors). 3) Batch synchronization: scheduled jobs that periodically compare data between systems. Simple to implement but latency can be minutes to hours. Only suitable for non-critical data. Choose based on latency requirements and infrastructure maturity.

# Package: io.thecodeforge.python.sync_patterns

# Outbox pattern - reliable async sync
# Write to PostgreSQL and outbox in same transaction
async def outbox_create_product(product):
    async with postgres.transaction():
        await postgres.insert('products', product)
        await postgres.insert('outbox', {
            'event': 'product_created',
            'data': product,
            'processed': False
        })

# Worker process (runs periodically or via message queue)
async def outbox_worker():
    events = await postgres.query(
        "SELECT * FROM outbox WHERE processed = false"
    )
    for event in events:
        try:
            await elasticsearch.index('products', event.data)
            await postgres.update('outbox', 
                {'processed': True}, 
                {'id': event.id}
            )
        except Exception:
            # Retry later (exponential backoff)
            pass

print('Outbox pattern ensures eventual consistency with retries')

Why Your Query Patterns Dictate Storage Before You Write a Line of Code

The most expensive mistake I see is teams choosing a database because it sounds cool, then twisting their queries to fit. That's backwards. Every database makes a bet on access patterns. Document stores assume you read entire aggregates. Graph databases assume you traverse relationships. Key-value stores assume you fetch by single key. If your query pattern doesn't match that bet, you're fighting the tool.

Here's the specific trap: teams start with a relational model because it's familiar, then realize they need complex joins across documents. They switch to Mongo but still write relational patterns—multiple collections, manual joins in app code. The result is worse performance and harder reasoning than a simple Postgres setup.

The fix: before provisioning any store, write out the five most critical query paths. Include frequency, latency requirements, and consistency needs. If most queries are point lookups by ID, use a key-value store. If most traverse entity relationships, use a graph database. If you need ad-hoc aggregations, stick with relational. This decision should be the output of your requirements, not the input.

// io.thecodeforge
// This class models the decision: map query patterns to storage types
// before writing any persistence code.

import java.util.*;

enum QueryPattern {
    POINT_LOOKUP,
    RANGE_SCAN,
    RELATIONSHIP_TRAVERSAL,
    AGGREGATION,
    TEXT_SEARCH
}

enum StorageType {
    KEY_VALUE, DOCUMENT, GRAPH, RELATIONAL, SEARCH_INDEX
}

public class QueryPatternAnalyzer {
    private final Map<QueryPattern, StorageType> patternToStore = Map.of(
        QueryPattern.POINT_LOOKUP, StorageType.KEY_VALUE,
        QueryPattern.RELATIONSHIP_TRAVERSAL, StorageType.GRAPH,
        QueryPattern.AGGREGATION, StorageType.RELATIONAL,
        QueryPattern.TEXT_SEARCH, StorageType.SEARCH_INDEX
    );

    public StorageType recommend(List<QueryPattern> topPatterns) {
        Map<StorageType, Integer> votes = new HashMap<>();
        for (QueryPattern p : topPatterns) {
            StorageType s = patternToStore.get(p);
            if (s != null) votes.merge(s, 1, Integer::sum);
        }
        return votes.entrySet().stream()
            .max(Map.Entry.comparingByValue())
            .map(Map.Entry::getKey)
            .orElse(StorageType.RELATIONAL); // default for unhandled
    }
}

The Real Cost of Polyglot Persistence Isn't Code — It's Operational Debt

I've debugged production incidents where the database was fine but the operational chain wasn't. Polyglot persistence multiplies your monitoring surfaces, backup strategies, and disaster recovery plans. Each store has its own failure modes. Redis hits OOM under memory pressure. Mongo can lock under heavy writes. Postgres has vacuum storms. You need distinct runbooks for each.

Here's what nobody tells you: operational debt compounds faster than code debt. Adding a second database type doubles your alerting rules, triples your backup restore tests, and introduces data loss scenarios that span systems. A crash in the document store that doesn't propagate to the relational store creates partial state that's nearly impossible to reconcile.

The rule I enforce: for every new storage technology, the team must write a failure mode document before production deploy. List every planned recovery strategy, backup SLA, and cross-system consistency check. If the team can't produce that in a day, they don't understand the technology well enough to run it.

Do not add a new database because it optimizes one query. Add it because the query's value justifies the operational cost across the entire system lifecycle.

// io.thecodeforge
// Validates that before adding a new store, the team has accounted for failure modes.

public class FailureModeChecklist {
    static class StoreChecklist {
        final String storeName;
        final boolean hasBackupStrategy;
        final boolean hasRestoreTest;
        final boolean hasAlertForEachFailure;
        final boolean hasCrossSystemConsistencyCheck;

        StoreChecklist(String name, boolean backup, boolean restore,
                       boolean alerts, boolean consistency) {
            this.storeName = name;
            this.hasBackupStrategy = backup;
            this.hasRestoreTest = restore;
            this.hasAlertForEachFailure = alerts;
            this.hasCrossSystemConsistencyCheck = consistency;
        }

        boolean readyForProduction() {
            return hasBackupStrategy && hasRestoreTest
                && hasAlertForEachFailure && hasCrossSystemConsistencyCheck;
        }
    }

    public static void main(String[] args) {
        StoreChecklist redis = new StoreChecklist(
            "Redis", true, true, true, false
        );
        System.out.println("Redis ready: " + redis.readyForProduction());
        // Output: Redis ready: false — missing cross-system consistency
    }
}