Most Real Systems Aren’t “SQL or NoSQL”
Once an application grows past a certain size, teams often end up running more than one database type — not out of indecision, but because different parts of the system genuinely have different data shapes and access patterns. This is called polyglot persistence, and done deliberately, it’s a sign of good architecture, not over-engineering.
A Common Pattern: PostgreSQL + Redis + Elasticsearch
- PostgreSQL — the source of truth for transactional data (orders, users, billing)
- Redis — caching, sessions, and rate limiting
- Elasticsearch — full-text search across product catalogs or documents
Keeping Multiple Stores in Sync
The hardest part of polyglot persistence isn’t picking the databases — it’s keeping them consistent. A common approach uses change data capture to propagate writes:
// After a successful write to PostgreSQL
async function updateProduct(id, data) {
await db.query('UPDATE products SET name = $1, price = $2 WHERE id = $3',
[data.name, data.price, id]);
// Propagate to Elasticsearch for search
await esClient.update({
index: 'products',
id: String(id),
doc: { name: data.name, price: data.price },
});
// Invalidate Redis cache
await redisClient.del(`product:${id}`);
}A More Robust Approach: Outbox Pattern
Direct dual-writes (as above) can fail halfway — the SQL write succeeds but the Elasticsearch update doesn’t. The outbox pattern writes an event to the same transaction as the primary write, then a separate worker propagates it reliably:
BEGIN;
UPDATE products SET name = 'New Name' WHERE id = 42;
INSERT INTO outbox_events (event_type, payload) VALUES ('product.updated', '{"id": 42}');
COMMIT;// Worker polls outbox_events and propagates reliably
async function processOutbox() {
const events = await db.query('SELECT * FROM outbox_events WHERE processed = false LIMIT 100');
for (const event of events.rows) {
await propagateToSearchIndex(event);
await db.query('UPDATE outbox_events SET processed = true WHERE id = $1', [event.id]);
}
}When to Add a Second Database
- Full-text search performance on your primary database is genuinely degrading — not just “might be nice”
- You need sub-millisecond reads on high-traffic lookups that a relational query can’t reliably hit
- A specific data shape (graph relationships, time-series) is awkward to model relationally at your scale
The Cost You’re Signing Up For
Every additional datastore adds operational surface area: another system to monitor, back up, secure, and reason about during incidents. Don’t add a second database speculatively — add it when a specific, measured problem justifies the added complexity.
Conclusion
Polyglot persistence works well when each database has one clear job and there’s a reliable mechanism (like the outbox pattern) keeping them in sync. Start with a single relational database, and introduce additional stores only when a concrete access pattern demands it.