Polyglot Persistence | The Garnet Wiki

Polyglot persistence is the practice of using multiple data storage technologies within a single application, each chosen for its fit to specific data access patterns, performance requirements, or domain semantics. It matters when a system’s data model evolves beyond the capabilities of a single database—when transactional consistency needs are mixed with analytical depth, when real-time event streams demand low-latency writes, and when data shapes vary widely across bounded contexts. It is not the choice of one database per service, but the intentional orchestration of diverse storage systems that coexist, interoperate, and evolve together.

Selecting Data Stores for Specific Workloads

When a system’s data access patterns diverge significantly, the first decision is which storage technology to use for each workload. The choice is not merely about features, but about trade-offs that affect availability, latency, and consistency.

For high-throughput, low-latency writes with eventual consistency, use a key-value store like Amazon DynamoDB or CockroachDB. Configuration is minimal: define a primary key, and optionally a sort key, with a provisioned or on-demand capacity mode.

{
  "TableName": "UserSessions",
  "KeySchema": [
    { "AttributeName": "UserId", "KeyType": "HASH" },
    { "AttributeName": "SessionId", "KeyType": "RANGE" }
  ],
  "BillingMode": "PAY_PER_REQUEST",
  "AttributeDefinitions": [
    { "AttributeName": "UserId", "AttributeType": "S" },
    { "AttributeName": "SessionId", "AttributeType": "S" }
  ]
}

For complex, transactional data with ACID guarantees and rich query capabilities, use a relational database such as PostgreSQL or MySQL. The schema is explicit, with foreign key constraints and indexes defined in SQL.

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id),
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    INDEX idx_user_status (user_id, status)
);

For real-time analytics, event streaming, and time-series data, use a columnar store or event log such as Apache Kafka with Apache Druid or ClickHouse. Kafka is the ingestion layer: events are produced as JSON records with a schema registry like Confluent Schema Registry.

{
  "topic": "order.created",
  "key": "12345",
  "value": {
    "orderId": "67890",
    "userId": "12345",
    "amount": 49.99,
    "currency": "USD",
    "timestamp": "2024-03-15T10:30:00Z"
  },
  "headers": {
    "content-type": "application/json",
    "schema-id": "101"
  }
}

For semi-structured or document-based data with evolving schemas, use a document database like MongoDB or CouchDB. Documents are stored as JSON, and queries are expressed in a JSON-based syntax with indexing support.

db.users.updateOne(
  { _id: "u123" },
  {
    $set: {
      preferences: {
        theme: "dark",
        notifications: { email: true, push: false }
      },
      lastLogin: new Date()
    },
    $setOnInsert: { createdAt: new Date() }
  },
  { upsert: true }
);

Integrating Databases Across Services

Once storage technologies are selected, the next challenge is how services interact with multiple databases. A service may read from one store and write to another, often in the same transaction.

A common pattern is eventual consistency via change data capture (CDC). When a record is written to PostgreSQL, a background process captures the change and publishes it to Kafka. The event is then consumed by downstream services, which update other databases.


curl -X POST http://debezium:8083/connectors \
  -H 'Content-Type: application/json' \
  -d '{
    "name": "order-db-connector",
    "config": {
      "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
      "database.hostname": "postgres.prod",
      "database.port": "5432",
      "database.user": "app_user",
      "database.password": "app_pass",
      "database.dbname": "orders",
      "database.server.name": "orders-prod",
      "plugin.name": "pgoutput",
      "snapshot.mode": "initial",
      "topic.prefix": "dbserver1"
    }
  }'

The event stream is consumed by a service that updates a document store.

from kafka import KafkaConsumer
import json

consumer = KafkaConsumer(
    'dbserver1.orders.orders',
    bootstrap_servers=['kafka1:9092'],
    value_deserializer=lambda m: json.loads(m.decode('utf-8'))
)

for message in consumer:
    event = message.value
    user_id = event['userId']
    order_id = event['orderId']
    total = event['amount']

    # Update MongoDB with the order
    client = MongoClient('mongodb://mongodb:27017')
    db = client['user-profiles']
    collection = db['users']

    collection.update_one(
        {'_id': user_id},
        {
            '$set': {'lastOrderTotal': total},
            '$push': {'orderHistory': {'orderId': order_id, 'timestamp': event['timestamp']}}
        },
        upsert=True
    )

Ensuring Data Consistency Across Stores

When multiple databases are used, consistency becomes a cross-cutting concern. The primary challenge is handling distributed transactions and data propagation delays.

A service may write to PostgreSQL and Kafka in a single operation using a two-phase commit. However, if the Kafka broker is unreachable, the write may succeed in PostgreSQL but fail in Kafka. The service must handle partial writes and eventual consistency.

A common failure mode is duplicate event processing. A consumer reads an event from Kafka, processes it, and commits the offset. But if the consumer crashes before committing, the same event is processed again. To prevent this, use idempotent consumers with deduplication.

# Idempotent event processor with deduplication
def process_event(event_id, event_body):
    # Check if event was already processed
    if redis.get(f"processed:{event_id}"):
        return  # Skip processing

    # Process event
    try:
        # Update PostgreSQL
        execute("INSERT INTO events (id, body) VALUES (%s, %s)", (event_id, event_body))

        # Update document store
        collection.update_one(
            {'_id': event_body['userId']},
            {'$set': {'lastEvent': event_body}}
        )

        # Mark as processed
        redis.setex(f"processed:{event_id}", 86400, '1')  # 1 day TTL

        return True
    except Exception as e:
        # Log error, but do not commit offset
        logger.error(f"Failed to process event {event_id}: {e}")
        return False

A subtle failure is schema evolution without backward compatibility. A service sends an event with a new field, but the consumer expects an older schema. The consumer fails to parse the event, leading to unprocessed messages.

To handle this, use schema versioning in the event payload.

{
  "event": "order.created",
  "version": 2,
  "timestamp": "2024-03-15T10:30:00Z",
  "data": {
    "orderId": "67890",
    "userId": "12345",
    "amount": 49.99,
    "currency": "USD",
    "metadata": {
      "source": "mobile",
      "deviceType": "iphone"
    }
  }
}

Consumers must handle unknown fields and optional fields, and schema evolution is managed via a registry that maps version numbers to schemas.

Handling Failure Modes and Operational Challenges

Polyglot persistence introduces operational complexity. A failure in one database can cascade through the system.

Database unavailability: A service cannot read from MongoDB during a deployment. The service falls back to a cache (Redis) or uses a fallback query.
Schema drift: A database schema evolves, but not all services update their consumers simultaneously. A service may read from a column that was added later but is missing in older data.
Data inconsistency: A service updates PostgreSQL but fails to publish the event. The system is in a state where the source-of-truth is updated, but downstream systems are stale.

A critical failure mode is data inconsistency due to conflicting updates. Two services write to the same record in different databases. One updates PostgreSQL, the other updates a document store. But they do not coordinate.

-- Service A updates PostgreSQL
UPDATE orders SET status = 'shipped' WHERE id = 100;

// Service B updates document store
{
  "userId": "123",
  "orderId": "100",
  "status": "shipped",
  "trackingNumber": "TRK123456"
}

If both updates happen in parallel, but the document store update is delayed, a query on the user profile may return stale data. The solution is eventual consistency with conflict resolution, often using a last-write-wins or vector clock mechanism.

Another sharp edge is indexing overhead. A service writes to a document database, but the index is not updated immediately. A query for the latest orders returns results from a stale index. The service must either:

Use indexing strategies (e.g., incremental indexing), or
Use read-through caching with a TTL.

# Read-through caching configuration
cache:
  provider: redis
  ttl: 300  # seconds
  refresh: true
  warmup: true
  query: "SELECT * FROM orders WHERE status = 'shipped'"

Synchronizing Data Between Stores

To reduce latency and improve consistency, use data synchronization. A common approach is change data capture (CDC) with materialized views.

A service reads from a PostgreSQL table and maintains a materialized view in a columnar store. The view is updated periodically using a batch process or real-time stream.

-- Materialized view in ClickHouse
CREATE MATERIALIZED VIEW order_summary
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (status, created_at)
AS
SELECT
    status,
    count() AS count,
    sum(total) AS total_revenue,
    avg(total) AS avg_order_value,
    max(created_at) AS last_order
FROM orders
GROUP BY status

The view is updated by a job that runs every 5 minutes:

# Run every 5 minutes via cron
0,5,10,15,20,25,30,35,40,45,50,55 * * * * /usr/local/bin/sync-orders-to-clickhouse.sh

The job reads the latest changes from PostgreSQL and inserts them into ClickHouse. If a new order is created, the materialized view is updated within minutes.

Designing for Evolvability

Polyglot persistence is not a one-time setup. It requires a design that supports future change. The system must handle:

Schema evolution across databases (e.g., adding a column in PostgreSQL, a new field in a document store).
Backward compatibility in event formats.
Monitoring and alerting for each database: latency, throughput, error rates, index health.
Rollback and migration strategies for database changes.

A common failure is migration without downtime. A new version of a service reads from a new column in PostgreSQL, but the old version does not. The migration is applied, but the service crashes during the change. The solution is dual-writing and feature flags.

-- Dual-writing: write to both old and new columns
INSERT INTO orders (
    id, user_id, total, status, created_at, metadata_json
) VALUES (
    101, 123, 59.99, 'confirmed', NOW(), '{"source": "web"}'
);

The old service reads from metadata_json, while the new service reads from both metadata_json and a new metadata JSONB column. Over time, the old service is phased out.

Polyglot persistence is not about choosing the best database for each task—it is about building a system where data flows seamlessly across technologies, where each store is a first-class citizen, and where consistency is managed, not assumed.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.