Database / DuckDB Interview questions
What is the difference between row-oriented and columnar storage for analytical queries?
Row-oriented storage keeps all fields of a single record contiguous on disk, which is efficient for retrieving or modifying one complete record at a time, the dominant access pattern for transactional workloads. Columnar storage keeps each column's values contiguous instead, which is efficient for reading a subset of columns across many rows, the dominant access pattern for analytical workloads.
| Row-oriented | Columnar |
| Fast to read/write one complete record. | Fast to read a subset of columns across many rows. |
| Poor compression for mixed-type rows. | Strong compression, since column values tend to be similar. |
| Wastes I/O reading unneeded columns for an analytical query. | Wastes I/O reading unneeded rows for a single-record lookup. |
Running an analytical aggregation query, like computing average order value, against a row-oriented database means reading every column of every row even though only one or two columns are actually needed, wasted I/O that columnar storage avoids by only touching the specific columns a query references. This is the fundamental storage-layer reason DuckDB, and analytical databases generally, choose columnar storage as their foundation.
More Related questions...
