Choosing a Database & Polyglot Persistence

Given a feature, pick a store and defend it

This is the synthesis lesson. Strong candidates do not memorize "use NoSQL for scale." They reason from decision drivers to a storage family, then defend against the runner-up.

The drivers, roughly in the order they decide things: access patterns (what queries do you actually run, and by what key), read/write ratio and volume (QPS now and in two years), consistency needs (does a stale read cause a real bug or just a cosmetic one), scale (does the working set fit one big node or not), latency target (p99 budget), and query complexity (joins, aggregations, ad hoc filters, full-text, geospatial). Two more sit underneath: operational cost (managed vs self-hosted, and does your team already run it) and transactions (do you need multi-row ACID).

Drivers to families

• Relational (Postgres, MySQL): rich queries, joins, ACID transactions, strong consistency. The correct default for most features. A single well-indexed Postgres box comfortably serves tens of thousands of QPS.
• Key-value (Redis, DynamoDB): always access by a known key, single-digit-ms latency, millions of ops/sec. Sessions, carts, flags, counters. Weak at ad hoc queries.
• Document (MongoDB): self-contained JSON documents, flexible schema, query by fields inside the document. Catalogs and content where the aggregate loads whole.
• Wide-column (Cassandra, Bigtable, HBase): massive write throughput, huge datasets, queries known in advance and modeled as partitions. Weak at joins and ad hoc filters.
• Graph (Neo4j): the value is in relationships and multi-hop traversals.
• Time-series (InfluxDB, TimescaleDB, Prometheus): append-heavy timestamped metrics with rollups and retention.
• Vector (pgvector, Pinecone, Milvus): nearest-neighbor search over embeddings.
• Columnar / OLAP (Snowflake, BigQuery, ClickHouse): large analytical scans and aggregations, kept separate from your OLTP store.

NewSQL: the family people miss

NewSQL / distributed SQL (Spanner, CockroachDB, TiDB) gives you horizontal scale plus ACID and SQL by auto-sharding data across nodes and using consensus (Raft/Paxos) to keep replicas consistent. The tradeoff versus a single Postgres is higher write latency per transaction (a commit needs a cross-node quorum) and operational complexity. So: choose NewSQL when you have genuinely outgrown one node and still need transactions and SQL, because the alternative is app-level sharding of MySQL/Postgres, where you hand-roll routing, cross-shard joins, resharding, and distributed transactions in application code. That is a large, permanent tax. NewSQL buys back most of that pain at the cost of latency and money.

Polyglot persistence means using several stores, each for what it is best at, and syncing between them: Postgres as the system of record, Redis for a hot cache, Elasticsearch for full-text, S3 for blobs, a warehouse for analytics via CDC. The cost is operational surface area and keeping derived data in sync, so you justify each store, you do not collect them.

Interview nuance: Reason with PACELC, not a CAP one-liner. CAP only speaks about behavior during a partition; PACELC adds the normal case: even when there is no partition (Else), you still trade Latency against Consistency. Spanner chooses consistency and pays latency; Dynamo chooses availability and latency and gives you eventual consistency. Naming PACELC signals you know CAP is not the whole story.

Recap: Drive from access pattern, consistency, scale, and query shape to a family, default to boring well-indexed relational, reach for NewSQL only when you have outgrown one node yet still need SQL and ACID (versus hand-rolled sharding), and treat polyglot persistence as a justified set of specialized stores, not a collection.