A platform must choose between a relational database and a wide-column store for its highest-volume data. What decides it, and what is the common error?
Show the full answer Hide the answer
What decides it
The access pattern and the partition key, not the volume.
A wide-column store fits when: writes are append-mostly, the dominant read is a range scan within one partition, there are no cross-entity transactions, and horizontal scale is a requirement rather than an aspiration. Chat messages fit this exactly — insert-heavy, read as "the last N messages in this channel", no joins.
A relational database fits when: queries are varied and not known in advance, transactions span entities, strong consistency is required across related data, and the working set is manageable on a single primary with replicas.
The common error
Choosing by volume rather than by shape. Large volume with relational access patterns is a case for partitioning a relational database, not for abandoning it. Modest volume with a strictly partition-scoped access pattern may still suit a wide-column store, because the operational model is simpler at scale.
The second common error is choosing the store correctly and the partition key badly, which is the more expensive mistake. In a wide-column store the partition key determines what scales, what becomes hot, what can be queried and what is irreversible — an unbounded partition is not a performance problem to tune later, it is a design defect that worsens daily.
The evidence to gather before deciding
- The dominant queries, written down. If they all filter by one key, that is a strong signal.
- Whether any invariant spans entities and must be enforced atomically.
- Partition size growth over time, and whether it is bounded by anything.
- Operational familiarity. A team that can run one of these well and not the other should weight that heavily; the best database is frequently the one you can operate at 3 a.m.
The pragmatic default
Start relational unless the access pattern clearly demands otherwise, because it accommodates unforeseen queries, enforces invariants, and is understood by everyone. Move specific high-volume, partition-scoped workloads out when the evidence is measured rather than anticipated.
And when you do, compose the key — entity plus a time bucket — so partition growth is bounded regardless of activity.