Two different purposes.
WHAT TRANSACTIONAL SYSTEMS OPTIMISE FOR
Many small reads and writes Strong consistency Low latency per operation Row-level access
HOW THEY STORE DATA
By row, so a whole record is read together.
WHAT ANALYTICAL SYSTEMS OPTIMISE FOR
Scanning large volumes Aggregating across many rows Reading few columns from wide tables
HOW THEY STORE DATA
By column, so only the columns queried are read.
WHAT COLUMNAR STORAGE PROVIDES
Reading only what is needed Excellent compression, since similar values sit together Vectorised processing
WHAT IT COSTS
Poor performance on single-row operations Updates and deletes being expensive
WHAT THAT MEANS
Analytical stores suit append-heavy workloads.
WHY RUNNING ANALYTICS ON THE APPLICATION DATABASE IS A PROBLEM
Large scans compete with the application, and the storage layout is wrong for it.
WHAT TO DO INSTEAD
Replicate to an analytical store.
WHAT A READ REPLICA SOLVES
Load, but not the storage layout.
WHAT IT DOES NOT SOLVE
Query performance on analytical patterns.
