Specialized data stores

General-purpose stores handle most workloads. A specialized store earns its place only when one access pattern is slow on the general store. These stores are usually fed from the source of truth, not used as the source themselves.

The big idea

A kitchen has one oven. You buy a pizza oven only when pizza is the bottleneck. Specialists are the same. Use one when a measured pattern needs it, and keep the general store for the rest.

Walking through it

Why columnar wins at analytics. A row store reads every column of every row it scans. Take 1 billion orders with 30 columns, and a query SUM(amount) GROUP BY category. It needs 2 columns.

Row store:      reads all 30 columns                   = 30 units
Columnar store: reads 2 columns, compressed 5x          = 2 / 5 = 0.4 units
Ratio:          30 / 0.4 = 75 times less I/O

The compression figure of 5x is the middle of the 5 to 10 times range that columnar data often reaches. Vectorized execution makes the scan faster still.

When graphs earn their place. A fixed two-level question like "my friends' posts" is fine in SQL with two indexed joins. A graph database pays off when depth varies: "are accounts A and B connected within six hops through shared devices, cards or addresses?"

Common mistakes

• Using a search index as the source of truth. It can lose acknowledged writes in some failures, so keep the primary copy in a durable store.
• Adding a specialist before measuring. Each new store is one more system to back up, secure and monitor.
• Running heavy analytics on the primary. Load the data into a columnar store instead.

Check yourself

1. In the columnar example, why is the answer 75 times less I/O? The row store reads 30 units. The columnar store reads 2 columns at a 5x compression, which is 0.4 units. 30 divided by 0.4 is 75.
2. When is plain SQL enough for "friends of friends"? When the depth is fixed at two, because two indexed joins answer it well.

Key takeaways

• Specialized stores win one access pattern each.
• Keep the source of truth in a durable store, and feed the specialists from it.
• Columnar storage reads only the columns a query needs, and compresses well.
• Graph databases help when the number of hops varies.

Further learning

For the search side in more depth, see search systems.

For embeddings and retrieval, see vector databases.