Part 4 of 6 · 2 min read
Then query patterns
Think of query patterns like choosing a tool from a toolbox. A hammer, a wrench, and a saw can all technically touch the same piece of wood. Only one of them is actually built for the specific cut needed.
In plain words: Once consistency and scale have narrowed the field, the deciding factor is usually the workload's dominant access pattern, matched against the storage families covered earlier in this subject. Flexible, ad hoc querying across many related entities fits a relational database. Self-contained, document-shaped entities read and written as a whole fit a document database. Simple, extremely fast lookup by a single exact key fits a key-value store. Deep, variable-depth relationship traversal fits a graph database. Wide, aggregate scans over a handful of columns across huge fact tables fit a column-oriented store. Picking a store whose native pattern does not match the actual dominant query either forces awkward workarounds in application code or quietly gives up the performance the specialized store was supposed to provide.
A worked example. The bookstore's "find everything connected to this author within two hops, co-authors, their other books, shared genres" feature is a deep relationship traversal, a natural fit for a graph database, while the bookstore's nightly revenue-by-genre report is a wide aggregate scan over a few columns across a huge sales history, a natural fit for a column-oriented warehouse table.
The key lines. No new SQL; this is a pattern-matching step.
deep relationship traversal -> graph database
wide aggregate scan, few columns -> column-oriented store
single exact-key lookup -> key-value store
flexible ad hoc querying -> relational database
Dominant access pattern
The dominant access pattern is the single query shape a workload performs most often or most urgently needs to be fast, used to match that workload against the storage family actually built for that shape.
Example: the bookstore's author-connection feature and its revenue report have different dominant access patterns, pointing at two entirely different storage families.