Polyglot Persistence: The Proven Case for 5 Data Models

Your transactional database is not failing you. It is doing precisely what it was engineered to do: take a write, make it durable, isolate it from every other write, and never lie about it. The problem is that a consumer-facing business no longer has one data consumption model. It has at least five, and each of them wants a storage layout and a consistency contract that contradicts the others. That contradiction is the whole argument for polyglot persistence, and this post makes it with measurements rather than slogans.

We will show, on a real PostgreSQL 16 and a real ClickHouse 26 build, why the same table and the same question cost 1.79 GB of I/O in one engine and 22 MiB in the other, why sixty-four writers queue behind a single row no matter how many cores you buy, why eventually consistent platforms exist at all, and why a vector index is a different kind of object from anything in the relational world. Then we lay out the polyglot persistence decision frame a CTO or an investor should apply before believing any "one database for everything" story, including ours.

What polyglot persistence actually claims

Martin Fowler gave the pattern its name in 2011: polyglot persistence means using different data storage technologies for different data, chosen by how the application reads and writes that data. The idea of polyglot persistence predates the term. Every large internet company arrived at polyglot persistence independently, usually after an outage, and usually after trying very hard not to.

The version of polyglot persistence that matters to a board is narrower. A business that serves consumers on the internet runs four or five workload classes with incompatible physics: transactions that must be exactly right, interactive traffic that must be fast everywhere, analytics that must scan everything, retrieval that must be semantically close, and the elastic cloud substrate underneath all of it. No single engine is optimal for more than one or two of those. Each additional engine is a real cost, so the decision is about which contradictions you can afford to paper over and which you cannot.

Figure 1. The polyglot persistence landscape: five workload classes, five storage contracts.

The OLTP engine is keeping a promise, not hitting a wall

A transaction-processing database sells one thing: ACID. Atomicity, consistency, isolation and durability are not marketing features; they are mechanisms with costs that show up in specific catalog views. Durability is a synchronous write to the write-ahead log before a commit is acknowledged. Isolation is a row-level lock, or in MVCC engines a tuple version chain plus a lock on the row being updated. Atomicity is the ability to roll every one of those back. In PostgreSQL's MVCC implementation, two sessions that update the same row serialise on that row by design, because that is the only way to keep the second update from clobbering the first.

The cleanest way to see the cost is to measure it. The runs below are on a two-vCPU sandbox with PostgreSQL 16.13, synchronous_commit = off to remove disk latency from the picture, and pgbench driving a single-statement transaction for ten seconds. This is a demonstration of a mechanism, not a benchmark; the absolute numbers are meaningless outside this box, and the shape of the curve is the point.

CREATE TABLE inventory (
    sku_id   INT PRIMARY KEY,
    on_hand  INT NOT NULL
);
INSERT INTO inventory
SELECT g, 1000000000
FROM generate_series(1, 1000) g;

-- hot.sql: every client decrements the same SKU
UPDATE inventory SET on_hand = on_hand - 1 WHERE sku_id = 1;

-- spread.sql: clients decrement a random SKU
\set sku random(1, 1000)
UPDATE inventory SET on_hand = on_hand - 1 WHERE sku_id = :sku;

$ for c in 1 8 32 64; do pgbench -n -f hot.sql -c $c -j 2 -T 10; done
clients=1    latency average = 0.065 ms   tps = 15298
clients=8    latency average = 0.260 ms   tps = 30790
clients=32   latency average = 2.020 ms   tps = 15838
clients=64   latency average = 5.871 ms   tps = 10900

$ for c in 1 8 32 64; do pgbench -n -f spread.sql -c $c -j 2 -T 10; done
clients=1    latency average = 0.066 ms   tps = 15238
clients=8    latency average = 0.190 ms   tps = 42089
clients=32   latency average = 0.932 ms   tps = 34339
clients=64   latency average = 2.080 ms   tps = 30774

Both workloads saturate two cores by eight clients, so the interesting comparison is what happens after that. The spread workload holds three quarters of its peak at sixty-four clients. The hot-row workload loses two thirds of its peak and its latency grows ninety-fold. A snapshot of pg_stat_activity during the sixty-four-client hot-row run shows where the time went.

SELECT wait_event_type,
       wait_event,
       COUNT(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND query LIKE 'UPDATE inventory%'
GROUP BY 1, 2
ORDER BY 3 DESC;

 wait_event_type |  wait_event   | count
-----------------+---------------+-------
 Lock            | tuple         |    38
 LWLock          | BufferContent |     7
 LWLock          | LockManager   |     6
 Lock            | transactionid |     6
                 |               |     5
 Client          | ClientRead    |     2

Figure 2. Wait events under hot-row contention, the first measurement behind polyglot persistence.

Fifty of sixty-four sessions are queued on the tuple lock or the transaction ID of the session holding it. Nothing here is a bug, a missing index or a tuning gap. The engine is serialising updates to one row because you asked it to guarantee that no decrement is lost. Sharding does not change the arithmetic for a single hot key; it only spreads the keys that are not hot. This is the first fundamental limit that forces polyglot persistence: an ACID row store's throughput on a contended key is bounded by the serial critical section, and that is exactly what a consumer flash sale, a viral post's like counter or a global leaderboard produces.

What the consumer internet changed

Enterprise applications of the previous era had a bounded number of users, a business-hours load curve and a tolerance for a few hundred milliseconds. Consumer applications have none of those properties. The traffic is spiky and global, the read-to-write ratio is often a thousand to one, and the product team measures latency at the 99th percentile in a region the database was never deployed in.

Amazon documented the consequences in the Dynamo paper. When a network partition happens, and it will, a system can keep accepting writes or it can keep every replica in agreement, but not both. A shopping cart that refuses to accept an item because a replica is unreachable costs more than a cart that briefly shows a stale item. Cassandra, DynamoDB, Riak and the key-value tier of most large platforms descend from that decision, and polyglot persistence at internet scale starts with it. The write contract becomes "this will converge", enforced by quorum arithmetic and a conflict rule, rather than "this is true now", enforced by a lock.

The trade is explicit and tunable. In Cassandra, a read at LOCAL_QUORUM against a replication factor of three touches two of three replicas in the local datacenter and returns the newest timestamped value it sees; the consistency documentation spells out the read-plus-write-greater-than-replication-factor rule that makes that read see the latest acknowledged write. What you give up is any notion of a multi-row transaction, a join or an ad hoc query, which is why the data model is designed backwards from the queries.

CREATE KEYSPACE consumer
WITH replication = {
    'class'      : 'NetworkTopologyStrategy',
    'us-east-1'  : 3,
    'ap-south-1' : 3
};

-- One partition per user, newest activity first, bounded by TTL.
CREATE TABLE consumer.activity_feed_by_user (
    user_id      UUID,
    event_ts     TIMEUUID,
    event_type   TEXT,
    payload      TEXT,
    PRIMARY KEY ((user_id), event_ts)
)
WITH CLUSTERING ORDER BY (event_ts DESC)
 AND default_time_to_live = 2592000
 AND compaction = {
    'class'                : 'TimeWindowCompactionStrategy',
    'compaction_window_unit': 'DAYS',
    'compaction_window_size': 1
 };

-- The application reads at LOCAL_QUORUM and writes at LOCAL_QUORUM:
-- 2 + 2 > 3, so a read observes the latest acknowledged write in-region.
CONSISTENCY LOCAL_QUORUM;
SELECT event_ts, event_type, payload
FROM consumer.activity_feed_by_user
WHERE user_id = 7a3f1c2e-4d5b-4e6f-8a9b-0c1d2e3f4a5b
LIMIT 50;

Notice what is missing. There is no foreign key to a users table, no join to the events catalog, no way to ask "which users had the most events this week" without a full cluster scan or a second table maintained by the application. That is the mirror image of the OLTP limit. An eventually consistent platform buys availability and horizontal write scale by refusing every feature that would require global coordination. Polyglot persistence is what happens when you stop pretending one of these contracts can substitute for the other.

Analytics is columnar because the questions are columnar

An analytical question is a function of a few columns over a very large number of rows. A row store answers it by reading every byte of every row, because the tuple is the unit of storage. A column store keeps each column in its own file, so a scan opens only the columns the query names, and compresses each column with a codec suited to that column's distribution. This is the second fundamental limit behind polyglot persistence, and it is easy to measure.

The lab table is a 5-million-row, 24-column orders table with realistic width: two free-text addresses, a UUID session ID, a referrer URL, optional notes. It was generated in PostgreSQL 16.13, exported to CSV, and loaded unchanged into ClickHouse 26.7 through chdb, so both engines hold identical data. The question is the one every product dashboard asks first: revenue and order count by region and channel for the last ninety days.

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT region,
       channel,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
WHERE order_ts >= now() - interval '90 days'
GROUP BY region, channel
ORDER BY revenue DESC;

 Sort (actual rows=20 loops=1)
   Sort Key: (sum(amount)) DESC
   Buffers: shared hit=123593 read=105740
   I/O Timings: shared read=219.788
   ->  Finalize GroupAggregate (actual rows=20 loops=1)
         ->  Gather Merge (actual rows=60 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ...
               ->  Partial HashAggregate (actual rows=20 loops=3)
                     ->  Parallel Seq Scan on orders (actual rows=408674 loops=3)
                           Filter: (order_ts >= (now() - '90 days'::interval))
                           Rows Removed by Filter: 1257993
                           Buffers: shared hit=123574 read=105740
 Execution Time: 674.381 ms

-- pg_class.relpages for orders: 229314  (229314 x 8 KB = 1.79 GB heap)

The planner did nothing wrong. There is an index on order_ts, but a quarter of the table matches, so a parallel sequential scan is the right plan, and it is the right plan in every row store. The cost is structural: to sum one NUMERIC column across 1.2 million qualifying rows, the executor pulled all 229,314 heap pages through shared buffers, 105,740 of them from the operating system, because ship_address, session_id and notes live in the same 8 KB pages as amount.

CREATE TABLE lab.orders
(
    order_id        UInt64,
    customer_id     UInt64,
    order_ts        DateTime64(6, 'UTC'),
    region          LowCardinality(String),
    channel         LowCardinality(String),
    status          LowCardinality(String),
    currency        FixedString(3),
    amount          Decimal(12, 2),
    -- ... 16 further columns identical to the PostgreSQL table
    carrier         LowCardinality(String),
    updated_at      DateTime64(6, 'UTC')
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(order_ts)
ORDER BY (region, order_ts, order_id)
SETTINGS index_granularity = 8192;

EXPLAIN indexes = 1
SELECT region,
       channel,
       count()     AS orders,
       sum(amount) AS revenue
FROM lab.orders
WHERE order_ts >= now() - INTERVAL 90 DAY
GROUP BY region, channel
ORDER BY revenue DESC;

Aggregating
   Keys: region, channel
   Aggregates: count(), sum(amount)
   ReadFromMergeTree (lab.orders)
      Parts: 4 | Granules: 166
      Output: region, channel, amount
      Prewhere filter column: order_ts >= '2026-06-07 04:05:17'
      Indexes:
         Partition   Parts: 4/4    Granules: 173/173
         Min-Max     Parts: 4/13   Granules: 173/652
         PrimaryKey  Parts: 4/4    Granules: 166/173

-- 20 rows returned in 0.032 s on the same 2-vCPU sandbox

SELECT column,
       formatReadableSize(sum(column_data_compressed_bytes))   AS compressed,
       formatReadableSize(sum(column_data_uncompressed_bytes)) AS uncompressed
FROM system.parts_columns
WHERE database = 'lab' AND table = 'orders' AND active
  AND column IN ('region','channel','amount','order_ts','ship_address','session_id')
GROUP BY column
ORDER BY sum(column_data_compressed_bytes) DESC;

   ┌─column───────┬─compressed─┬─uncompressed─┐
1. │ ship_address │ 185.17 MiB │ 290.35 MiB   │
2. │ session_id   │ 76.61 MiB  │ 76.29 MiB    │
3. │ amount       │ 19.25 MiB  │ 38.15 MiB    │
4. │ channel      │ 2.73 MiB   │ 4.78 MiB     │
5. │ order_ts     │ 190.29 KiB │ 38.15 MiB    │
6. │ region       │ 26.85 KiB  │ 4.78 MiB     │
   └──────────────┴────────────┴──────────────┘

Figure 3. Row store versus column store for one aggregate, the second measurement behind polyglot persistence.

The four columns the query needs compress to about 22 MiB for the entire table, and the partition, min-max and primary-key indexes cut that to 166 of 652 granules before a byte of amount is decoded. The twenty columns the query does not need, including the 185 MiB of addresses, are never opened. Thirty milliseconds versus seven hundred is not ClickHouse being clever; it is the MergeTree storage layout being shaped like the question. The same layout is why ClickHouse is a poor system of record: a single-row UPDATE is a mutation that rewrites parts, and there is no row lock to serialise two of them.

Cloud-native data platforms changed the unit of scale

The third shift in polyglot persistence is not a data model but an operating model. Aurora, AlloyDB, Spanner, Snowflake, BigQuery and ClickHouse Cloud separate compute from storage, put the storage on a replicated log or an object store, and let capacity change in minutes. For a founder the argument is time to market: a team of four can stand up a multi-AZ PostgreSQL-compatible cluster with automated failover before lunch. For a CFO the argument is that capacity becomes an operating expense that tracks demand.

The trade-offs are just as concrete, and a vendor-neutral practice has to name them. Managed services diverge from the open-source engine they are compatible with: extension allow-lists, superuser removal, version lag behind community releases, and storage layers whose performance characteristics (Aurora's quorum writes, Spanner's TrueTime commit wait) differ from the engine's documentation.

Egress and cross-region replication are priced per byte, which matters precisely when the polyglot persistence topology is moving change streams between stores. And the exit cost is asymmetric: getting a terabyte in is a weekend, getting it out with zero downtime is a project. None of this argues against cloud platforms. It argues for choosing them per workload, which is polyglot persistence applied to the operating model, with the same rigour as the engine itself.

Vector platforms and RAG are a different kind of object

A relational index answers "is this key present" exactly. A B-tree, a hash, a bitmap: the answer is deterministic and complete. A vector index answers "which stored vectors are nearest to this one" approximately, because exact nearest-neighbour search in a thousand dimensions is a full scan. HNSW and IVF indexes trade recall for latency with explicit knobs, and a retrieval-augmented generation pipeline lives or dies on that trade plus the metadata filters applied before or after the approximate search.

That makes vector search a distinct workload class in any polyglot persistence design rather than a feature bolted onto an existing engine, even when it ships inside one. pgvector 0.8 on PostgreSQL 16+ is an excellent choice while the corpus fits in memory, the filter predicates are selective and the embedding refresh rate is modest, because it keeps the vectors transactionally next to the rows they describe. A dedicated platform such as Milvus 2.6 earns its place when index build and query serving need to scale independently, when collections reach hundreds of millions of vectors, or when the workload needs GPU indexes, tiered storage and multi-tenant isolation that a general-purpose engine will not prioritise.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE support_chunks (
    chunk_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id   INT          NOT NULL,
    ticket_id   BIGINT       NOT NULL,
    chunk_text  TEXT         NOT NULL,
    embedding   VECTOR(1024) NOT NULL,
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
);

-- Recall/latency trade-off is explicit: m and ef_construction at build time,
-- hnsw.ef_search at query time. Test on your corpus before fixing these.
CREATE INDEX idx_support_chunks_embedding_hnsw
    ON support_chunks
 USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 128);

SET hnsw.ef_search = 80;
SELECT chunk_id,
       ticket_id,
       1 - (embedding <=> $1::vector) AS cosine_similarity
FROM support_chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1::vector
LIMIT 8;

import psycopg
from openai import OpenAI   # any embedding provider; keep it in-VPC for regulated data

EMBED_MODEL = "text-embedding-3-large"
client = OpenAI()

def embed(text: str) -> list[float]:
    return client.embeddings.create(model=EMBED_MODEL, input=text).data[0].embedding

def retrieve(conn: psycopg.Connection, tenant_id: int, question: str, k: int = 8):
    qvec = embed(question)
    with conn.cursor() as cur:
        cur.execute("SET LOCAL hnsw.ef_search = 80")
        cur.execute(
            """
            SELECT chunk_id, ticket_id, chunk_text,
                   1 - (embedding <=> %s::vector) AS score
            FROM support_chunks
            WHERE tenant_id = %s
            ORDER BY embedding <=> %s::vector
            LIMIT %s
            """,
            (qvec, tenant_id, qvec, k),
        )
        return cur.fetchall()

# The ticket text itself is owned by the OLTP schema; this table is rebuilt
# from it when the embedding model changes. Treat it like a materialised view.

Two operational facts follow for any polyglot persistence estate. First, an embedding model change invalidates every vector, so the vector store must be rebuildable from the system of record; it is a derived index, not a database of record. Second, recall is a measured quantity. A RAG pipeline that has never had its retrieval recall measured against a labelled set is not in production, whatever the dashboard says.

The polyglot persistence decision frame

Executives do not need to memorise storage internals. They need a frame that turns "which database" into a question about the workload, so that the answer can be checked against measurement. These are the dimensions that decide it in practice.

Read the rows, not the columns. If a workload needs two cells that live in different columns, that is a second engine, and the honest polyglot persistence question is how the data gets from the first to the second and how stale it is allowed to be on arrival.

What a polyglot persistence topology looks like when it works

Figure 4. Reference polyglot persistence topology with one system of record and derived stores.

The polyglot persistence pattern that survives contact with production has one rule: exactly one store owns each fact, and every other store holds a derived, purpose-shaped copy that can be rebuilt from the owner. The system of record stays an ACID engine, usually PostgreSQL or MySQL with proper HA, because ledgers, orders and inventory are the facts a regulator will ask about. Change data capture through logical decoding and Kafka fans those facts out. The analytics store receives them into a ReplacingMergeTree keyed by the primary key so that CDC updates collapse on merge. The vector store receives the subset that needs embedding. The lakehouse receives everything for retention and model training.

CREATE TABLE analytics.orders_cdc
(
    order_id     UInt64,
    customer_id  UInt64,
    order_ts     DateTime64(6, 'UTC'),
    region       LowCardinality(String),
    channel      LowCardinality(String),
    status       LowCardinality(String),
    amount       Decimal(12, 2),
    _version     UInt64,          -- Debezium source.lsn or ts_ms
    _deleted     UInt8            -- 1 when op = 'd'
)
ENGINE = ReplicatedReplacingMergeTree(
    '/clickhouse/tables/{shard}/analytics/orders_cdc',
    '{replica}',
    _version,
    _deleted
)
PARTITION BY toYYYYMM(order_ts)
ORDER BY (region, order_ts, order_id)
SETTINGS index_granularity = 8192;

-- Dashboards read with FINAL or through a materialised view that
-- pre-aggregates; the raw table is never the source of truth.
SELECT region,
       channel,
       sum(amount) AS revenue
FROM analytics.orders_cdc FINAL
WHERE order_ts >= now() - INTERVAL 90 DAY
  AND _deleted = 0
GROUP BY region, channel
ORDER BY revenue DESC;

The cost of this architecture is real and should be stated to a board plainly. Every arrow is a replication lag to monitor, a schema contract to version, a backfill procedure to rehearse and an on-call surface to staff. Polyglot persistence multiplies the number of things that can be wrong at three in the morning, which is why the number of stores must be the smallest that the workload contradictions force, and never one more.

Questions a CIO, a founder or an investor should ask about polyglot persistence

The technical argument for polyglot persistence above reduces to a handful of questions that a non-specialist can put to any engineering team or any vendor, and that a diligence process should insist on getting answered with evidence.

Which store owns each fact, and can every other store be rebuilt from it without downtime? What is the measured replication lag between the system of record and the analytics store at peak, and who is paged when it exceeds the agreed staleness? What is the hot-key throughput ceiling of the transactional engine on its current hardware, measured the way we measured it above, and how far is peak traffic from that ceiling? What does the cloud bill look like per workload class, including egress between stores, and what is the exit plan for each managed service? What is the recall of the retrieval pipeline against a labelled set, and when was it last measured?

A team that can answer those with catalog views, system.* tables and dated measurements is running polyglot persistence deliberately. A team that answers with a vendor's architecture slide is running it by accident, and the accident is usually discovered during a growth spike, an audit or an acquisition.

Why a polyglot persistence partner has to be vendor-neutral

Every engine in a polyglot persistence estate is sold by a company whose revenue depends on you choosing it for as much of your estate as possible. The transactional vendor will add columnar indexes and vector types and tell you the second and third engines are unnecessary. The columnar vendor will add row-level updates and tell you it can be the system of record. The cloud vendor will bundle all of them and tell you the topology question is solved. Each claim is partly true, and each is a conflict of interest, because the vendor is paid for the engine and not for the outcome.

MinervaDB is paid for the outcome. We are data platform practitioners rather than a product company: our engineers have run PostgreSQL, MySQL, SQL Server, MongoDB, Cassandra, Redis and Valkey, ClickHouse, Milvus and the managed cloud editions of all of them in production, across more than 900 enterprises, and we have no licence to sell.

That independence is what lets us tell a client that pgvector is enough for their corpus, that their Cassandra cluster should be a PostgreSQL partition, or that the columnar migration they were sold will not fix a hot-row problem. The measurements in this post are the kind of evidence we bring to every architecture review, and the polyglot persistence decision frame above is the one we apply.

If you are designing, funding or acquiring a business that runs on a consumer-facing data platform, the polyglot persistence conversation is the one to have before the growth spike rather than after it. Talk to MinervaDB about an independent architecture review of your data platform, from the system of record to the retrieval layer. As always: test every change on your own workload before it reaches production, and keep a rehearsed disaster-recovery posture for every store in the topology, derived copies included.

Frequently asked questions about polyglot persistence

Is polyglot persistence just over-engineering for a startup? Usually, at first. A single well-run PostgreSQL with a cache in front of it carries most products to meaningful scale. Polyglot persistence becomes necessary when a specific measurement, such as the hot-row ceiling or the analytics scan cost shown above, contradicts a specific product requirement. Add the second store when the measurement says so, not when a slide does.

Can a multi-model database replace polyglot persistence? A multi-model engine reduces the operational surface of polyglot persistence, which is valuable, but it does not change the physics. A row store with a columnar index still reads tuples for transactional work and still serialises on a hot key; a columnar engine with row updates still performs mutations. Evaluate multi-model features by measuring the specific workload against a dedicated engine and pricing the difference.

Does the cloud make the polyglot persistence decision for me? No. Cloud platforms make each engine easier to provision and scale, and they make moving data between engines more expensive. The workload contradictions are the same on-premises and in the cloud; what changes is the operating model and the cost structure of the arrows between stores.

Where does the vector database sit in a polyglot persistence design? As a derived, rebuildable index over facts owned by the system of record. Embedding model changes and recall regressions are routine, so the pipeline that rebuilds the vector store must be as tested as the backup that restores the ledger.

Lab notes for the polyglot persistence measurements: PostgreSQL 16.13 (Ubuntu build), shared_buffers 1 GB, synchronous_commit off, two vCPUs, 7 GB RAM; ClickHouse 26.7.2 embedded via chdb on the same host; 5,000,000 rows generated with generate_series and exported unchanged between engines; pgbench 10-second runs; all outputs are verbatim with trims marked. Figures are mechanism demonstrations from a sandbox and are not vendor benchmarks. Reproduce on your own hardware before drawing capacity conclusions.