# Columnar storage > Columnar storage groups values from the same column together instead of storing each complete row together. It makes analytical queries cheaper by reading fewer fields and compressing similar values well, but it is usually a worse fit for frequent single-record updates and full-row lookups. Source: https://sysround.com/concepts/columnar-storage Updated: 2026-11-05 ## How columnar storage works Consider an events table with timestamp, customer_id, country, event_type, and dozens of other fields. A row-oriented layout stores each event's fields together; a column-oriented layout stores timestamp values together, country values together, and so on, usually within bounded groups of rows. For a query such as 'count purchases by country yesterday,' the engine can read timestamp, event_type, and country without reading unrelated fields. This column projection reduces data read, especially for wide tables where queries need only a few columns. Values within a column share a type and often repeat or change gradually. Dictionary encoding can represent repeated country names with small codes, while delta encoding can represent nearby timestamps as differences. Compression benefits depend on the data and its ordering. Files or storage segments commonly contain row groups with separate column chunks and metadata such as minimum and maximum values. Date partitions can exclude entire days, and block statistics can skip additional chunks when their value ranges cannot match a filter. Neither mechanism guarantees that every selective filter avoids a large scan. Many analytical engines process batches of column values at once, reducing per-row execution overhead. Ingestion often buffers events and writes larger batches into columnar segments; background merging keeps small segments from accumulating. Columnar databases provide query and ingestion services around this layout, while formats such as Parquet provide columnar files that a separate engine can query. ## Worked example: Querying a web analytics event history Suppose a platform retains 100 million events per day with 40 fields per event. A dashboard asks for purchase counts by country over seven days. Store events in a columnar analytical database, partitioned by event date, with a sort order chosen for common tenant and time filters. The query needs timestamp, tenant_id, event_type, and country rather than all 40 fields. Date partitioning excludes data outside the requested period; sorting and block statistics may prune more. Do not claim a tenfold speedup from reading four of 40 fields: field widths, compression, and execution costs differ. If repeated dashboard scans still miss the latency target, add hourly aggregates and retain raw events for corrections and drill-down. ## Trade-offs - **Less data read for analytical queries.** Queries that scan many records but use few columns benefit most; fetching most fields from one record may favor row-oriented storage. - **Compression versus processing cost.** Column-specific encodings reduce storage and I/O, but encoding, decompression, and execution still consume CPU. - **Batch efficiency versus freshness.** Larger batches produce more efficient segments, while smaller batches improve visibility latency but can increase ingestion and merge overhead. - **Updates and deletions need extra work.** Many implementations use replacement versions, delete markers, or segment rewrites rather than cheap in-place changes, so explain visibility and physical cleanup separately. - **Physical layout favors particular queries.** Partitioning and sorting improve pruning for chosen access patterns but add maintenance costs and cannot optimize every filter. - **Fast scans do not eliminate expensive queries.** High-cardinality grouping, large joins, and exact distinct counts can still require substantial memory, CPU, and network shuffle. ## When to use it in a system design interview - The design includes dashboards or reports that aggregate large event histories across a small subset of fields. - Most data arrives as append-heavy events, and frequent transactional changes are not the main workload. - You need flexible filtering and grouping beyond a fixed set of precomputed counters. - Historical scans are becoming expensive in the transactional database, and analytics can use a separate serving path. - The requirements include querying large datasets in object storage through columnar files and an analytical query engine. ## How to bring it up in the interview - Start with a representative query and identify which columns it reads, how many rows it touches, and its latency target; defend the layout using that access pattern. - Separate the transactional and analytical paths when their needs differ, then explain how events or change data capture reach the analytical store and how much lag is acceptable. - Propose partition and sort keys based on common predicates, not just ingestion convenience; expect questions about tenant skew, cardinality, and queries that miss those keys. - Explain batching, replay-safe ingestion, late data, and small-segment merging; interviewers will probe whether fast reads come at the expense of freshness or correctness. - Describe when raw columnar scans stop meeting the target and introduce materialized aggregates where needed, while retaining raw data for drill-down and reconciliation. ## Common mistakes - Confusing columnar storage with wide-column databases: grouping columns into families does not by itself imply a columnar analytical layout. - Saying columnar storage is always faster without distinguishing analytical scans from point reads and transactional updates. - Assuming a filter automatically skips data; effective pruning depends on partitioning, sorting, metadata, and the distribution of values. - Treating columnar storage as an alternative to SQL, even though many columnar databases expose SQL and support relational queries. - Ignoring duplicate events, late arrivals, and correction semantics because the storage format makes aggregation fast. ## Interview questions where it comes up - [Design Google Analytics](https://sysround.com/questions/design-google-analytics): Columnar storage supports flexible event filtering, funnel analysis, and distinct-user queries over large histories. - [Design an ad click aggregator](https://sysround.com/questions/design-ad-click-aggregator): A columnar analytical store supports time-window reporting and reconciliation between detailed click events and aggregated billing totals. - [Design a metrics monitoring system like Datadog](https://sysround.com/questions/design-metrics-monitoring-system): Columnar layouts and compression help serve historical metric scans and rollups, with cardinality and physical ordering shaping query cost. - [Design an A/B testing platform](https://sysround.com/questions/design-ab-testing-platform): Analyzing exposure and outcome histories requires efficient scans, grouping, and joins across large analytical datasets. ## FAQ ### What is the difference between row-oriented and column-oriented storage? Row-oriented storage keeps a record's fields together, which suits fetching and changing individual records. Column-oriented storage keeps values from each column together, which suits scanning selected fields across many records. The right choice depends on access patterns, not simply dataset size. ### Is a columnar database SQL or NoSQL? Columnar describes the physical storage layout, not the query language or data model. Many columnar databases support SQL, joins, and aggregations. Wide-column NoSQL databases are a different category and should not be treated as equivalent. ### Can columnar databases handle real-time analytics? Yes, some accept continuous ingestion and make new data queryable quickly, often using buffers or small segments before background merging. Freshness, ingestion throughput, and query efficiency still trade off against each other. Specify the required event-to-query delay and verify how duplicates, updates, and late events become visible. ## Related concepts - [Partitioning strategies](https://sysround.com/concepts/data-partitioning) - [Materialized views](https://sysround.com/concepts/materialized-views) - [Data lakes and warehouses](https://sysround.com/concepts/data-lake) - [Time-series databases](https://sysround.com/concepts/time-series-databases) --- SysRound (https://sysround.com) is an AI practice tool for system design interviews: a voice or typed AI interviewer, a shared whiteboard, then feedback on every skill and a model answer. Its feedback is AI-generated and can be wrong.