Databases at Scale
Polyglot Persistence
Using different databases for different parts of the same system, each chosen for the workload it actually serves — a relational store for transactions, a search index for full-text queries, a cache for hot reads — instead of forcing one database to do everything adequately.
- The gain is real: a search engine does full-text search far better than SQL
LIKE, and a cache serves hot reads at a latency no disk-backed store can match - The cost is also real: data now lives in N places that can drift out of sync, and the system needs an explicit mechanism to keep them close enough
- One system should be the source of truth; every other store is a derived, rebuildable view of it
- Change Data Capture (CDC) — streaming a database's write-ahead log as events — is the standard way to propagate changes to secondary stores without dual writes
- Dual writes (writing to two stores directly from application code) are a classic reliability trap: a crash between the two writes leaves them permanently inconsistent
The system that gets this wrong writes to Postgres and then, in the same request handler, writes to Elasticsearch too — two writes, no shared transaction between them. The moment the process crashes, or the second write times out, or a deploy race reorders things, the two stores disagree and nothing detects it until a customer notices search returning stale results. CDC turns this into a single write plus an asynchronous, replayable, at-least-once propagation pipeline — the kind of problem Event Driven Architecture and Exactly Once And Idempotency already have good answers for.
| Store | Role | Why not just use the primary database |
|---|---|---|
| Relational (Postgres) | Source of truth, transactional writes | — |
| Search index (Elasticsearch) | Full-text and faceted search | SQL LIKE does not rank or tokenize text well |
| Cache (Redis) | Sub-millisecond hot reads | Disk-backed reads cannot match in-memory latency |
| Column store (warehouse) | Analytics over historical data | OLTP schemas and indexes are wrong for OLAP scans |