Counting things seems simple, but at scale, how you count becomes an architecture decision. Here’s a quick write-up on the need for columnar systems…
Not counting like “how many signups today?” Counting like “for every user, across every product, across billions of usage events this month, what exact amount do we owe them?” The answer has to be right every single time. No approximations.
Most systems start with a row-based database like MySQL or Postgres.
Data is stored row by row, so each record contains all its fields together. This works well for transactional workloads. You insert an event, update a record, fetch a user - all efficient because everything you need is in one place.
But aggregation is different.
If you want to compute something like “total usage per user this month”, a row-based system has to scan full records across the dataset, even though only a few columns are relevant. As data grows, this becomes increasingly expensive.
Columnar databases take a different approach.
Instead of storing data row by row, they store it column by column. All values for a single field are stored together. So when you run an aggregation like “sum usage grouped by user”, the system scans only the required columns, not entire rows.
This unlocks a few important advantages:
- less data read from disk
- better compression (similar values stored together)
- vectorised execution over batches of values
- easier parallelisation across large datasets
The result is much faster performance for large-scale analytical queries.
The tradeoff is that columnar systems are not optimised for row-by-row operations. Inserting or updating individual records is less efficient compared to row-based systems. So the distinction is simple:
- Row-based databases are built for transactions.
- Columnar databases are built for aggregations.
Hope this helps.