Concrete scenario
A product needs transactions for orders but also ingests a very high-volume append-only device stream.
Mechanics
SQL versus NoSQL is not B-tree versus LSM. PostgreSQL uses heap tables with indexes; InnoDB is a MySQL engine with a clustered B+ tree; some relational and non-relational products use LSM trees. Choose per workload: access patterns, transactions, consistency, latency, geography, scale, and operating skill.
Failure mode
Moving to a store without matching transaction or query semantics can create dual-write inconsistency and expensive application joins. Conversely, keeping an unbounded write stream in a poorly configured single write domain can miss capacity targets.
Trade-off
Relational systems offer flexible queries and constraints; specialized stores may offer simpler partitioned access or write scaling. Capacity is one reason to change, not the only one, and category labels do not determine guarantees.
Worked example: one product, three stores
Consider an IoT platform with 50k customers, orders and billing, and 2M devices each sending a reading every 10 s.
- Orders and billing: about 50 writes/s that need multi-row transactions, foreign keys and ad-hoc finance queries. This belongs in PostgreSQL. One primary plus replicas is plenty.
- Device telemetry: 2M / 10 s is 200k writes/s, append-only, queried as "device X, last 24 h". At about 100 bytes per reading that is roughly 1.7 TB/day. This fits a wide-column or time-series store (Cassandra, ScyllaDB, Bigtable, TimescaleDB) partitioned by
(device_id, day). - Fleet analytics: "p95 temperature by region last month" scans billions of rows, which belongs in a columnar store (ClickHouse, BigQuery) fed by a stream.
The mistake would be to force all three into one system. The opposite mistake is to add all three on day one when telemetry is 2k writes/s, which a single Postgres (partitioned by time) handles easily.
Decision table
| If you need... | Lean towards | Watch out for |
|---|---|---|
| Multi-entity transactions, constraints, ad-hoc queries | Relational (Postgres, MySQL) | Single-writer limits before sharding |
| Relational semantics plus horizontal writes plus multi-region | Distributed SQL (Spanner, CockroachDB, YugabyteDB) | Higher per-write latency (consensus), cost |
| Predictable single-digit-ms access by key at any scale | Key-value / document (DynamoDB, MongoDB) | Query flexibility; design around access patterns up front |
| Massive append-heavy writes, queried by partition and time | Wide-column (Cassandra, ScyllaDB, Bigtable) | No joins, limited secondary indexes, tombstones |
| Aggregations over billions of rows | Columnar OLAP (ClickHouse, BigQuery, Snowflake) | Poor for point updates and high-QPS OLTP |
| Deep, variable-length relationship traversal | Graph (Neo4j, Neptune) | Sharding connected data; another query language |
| Sub-millisecond ephemeral state | In-memory (Redis, Memcached) | Durability and memory cost |
Interview checklist
- State the top 2-3 queries and their QPS before naming any product.
- State the consistency requirement per entity (for example, balance must be strongly consistent, feed can be eventual).
- Estimate data size and growth, and whether it fits on one node for 2-3 years.
- Name what you give up with the chosen store and how you compensate (for example, no joins, so denormalize at write time).