Scaling Reads medium
Why are Columnar Databases (like Redshift/Snowflake) faster for Analytics (OLAP) than Row-based Databases (like PostgreSQL)?
- A They use faster disks.
- B Analytics queries usually aggregate a few columns (e.g., `SUM(Revenue)`). Columnar storage allows reading *only* the 'Revenue' data from disk, skipping all other user data.
- C They don't use SQL.
- D They cache everything in RAM.
Answer: B. Analytics queries usually aggregate a few columns (e.g., `SUM(Revenue)`). Columnar storage allows reading *only* the 'Revenue' data from disk, skipping all other user data.
Row-based DBs must read the entire row (Name, Address, Phone...) just to get the 'Revenue'. Columnar DBs read contiguous blocks of just the column needed. This is 10x-100x more I/O efficient for aggregation.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- In a caching system, what is a 'Cache Miss'?
- Why is `SELECT * FROM Users` generally discouraged in high-performance applications compared to `SELECT name, email FROM Users`?
- You have users all over the world downloading large images from your server in New York. Users in Australia complain it is slow. What solves this?
- What is the main downside of reading from a Read Replica instead of the Primary database?
- You have maximized the RAM on your single database server, but it's still too slow. You decide to add 10 more servers to share the load. What is this called?
- You have 5 Read Replicas. How does the application know which one to ask for data?