Skip to main content
Definition Website Computer and Technology

Columnar Database

Subject: Computer and Technology noun /kəˈlʌm.nər ˈdeɪ.təˌbeɪs/ 1 definition

  1. Definition (noun)

    Entry #234 · Updated

    A Columnar Database is a database management system that stores table values contiguously by column rather than by row. Because all values within a single column share the same data type and often repeat, this layout achieves high compression ratios through techniques such as dictionary and run-length encoding while allowing analytical queries to read only the columns they reference. Unlike row-oriented databases optimized for single-record transactions, a columnar architecture trades slower per-row writes and point updates for rapid full-table scans, aggregations, and vectorized execution over large datasets.

    Common Types

    • Disk-Based Column Stores: Persist each column in separate compressed blocks on disk and use late materialization to reconstruct rows only after filtering predicates (e.g., ClickHouse, Amazon Redshift).

    • In-Memory Columnar Engines: Hold compressed column arrays directly in RAM to exploit CPU caches and SIMD vectorized instructions for sub-second ad-hoc analytics (e.g., SAP HANA, DuckDB).

    • Hybrid Columnar (HTAP) Databases: Absorb incoming inserts and updates into a fast row-oriented delta buffer before asynchronously compacting records into read-optimized columnar segments (e.g., SingleStore, Apache Doris).

    • Cloud-Native & Lakehouse Engines: Decouple compute from durable object storage by organizing tables into compressed columnar blocks or open formats like Apache Parquet and Apache ORC for elastic scaling (e.g., Snowflake, BigQuery, Trino).

    Example

    An e-commerce analytics team queries a 50-billion-row orders table with 40 columns to calculate quarterly revenue by region (SELECT region, SUM(amount) FROM orders WHERE order_date >= '2026-07-01' GROUP BY region). Instead of scanning every attribute of all 50 billion records, the columnar database reads only the compressed order_date, region, and amount column segments—skipping over 90% of the stored data and returning the result in under two seconds.