Polyglot Persistence — Dual Write Failure Omits Search

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')