{"title":"Columnar Storage","description":"","content":"Traditional row-based databases read entire rows from disk. To find your two columns, the database loads all 50 columns for every single row. That's 48 columns of wasted I/O, repeated millions of times.\n\n**Columnar storage** flips this model. Instead of storing rows together, it stores each column separately. Now your query reads only the two columns it needs, skipping the other 48 entirely.\n\nThis isn't a minor optimization. It's the reason modern data warehouses like BigQuery, Redshift, and Snowflake can query petabytes in seconds. It's why analytics file formats like Parquet and ORC have become the standard for data lakes. \n\n---\n\n# 1. Row Storage vs Column Storage\n\nThe fundamental difference between row and columnar storage is how data is physically organized on disk.\n\n### 1.1 Row-Oriented Storage\n\nTraditional databases store data row by row. All values for a single record are stored contiguously.\n\n\n```shell\nRow Storage Layout (on disk):\n\nRow 1: [id=1, name=\"Alice\", age=30, city=\"NYC\", salary=75000]\nRow 2: [id=2, name=\"Bob\", age=25, city=\"SF\", salary=85000]\nRow 3: [id=3, name=\"Carol\", age=35, city=\"NYC\", salary=95000]\nRow 4: [id=4, name=\"Dave\", age=28, city=\"LA\", salary=70000]\n\nPhysical bytes on disk:\n[1|Alice|30|NYC|75000][2|Bob|25|SF|85000][3|Carol|35|NYC|95000]...\n```\n\n\n\n```mermaid\nflowchart TB\n subgraph disk[\"Disk Layout (Row Storage)\"]\n R1[\"Row 1: id=1, name=Alice, age=30, city=NYC, salary=75000\"]:::green\n R2[\"Row 2: id=2, name=Bob, age=25, city=SF, salary=85000\"]:::green\n R3[\"Row 3: id=3, name=Carol, age=35, city=NYC, salary=95000\"]:::green\n R4[\"Row 4: id=4, name=Dave, age=28, city=LA, salary=70000\"]:::green\n end\n\n R1 --> R2 --> R3 --> R4\n\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n**How row storage handles queries:**\n\n\n```sql\n-- Query: Get average salary\nSELECT AVG(salary) FROM employees;\n```\n\n\n\n```shell\nRow storage must:\n1. Read Row 1 entirely → extract salary (75000)\n2. Read Row 2 entirely → extract salary (85000)\n3. Read Row 3 entirely → extract salary (95000)\n4. Read Row 4 entirely → extract salary (70000)\n\nData read: 4 rows × 5 columns = 20 values\nData needed: 4 values (just salary column)\nEfficiency: 20% (80% wasted I/O)\n```\n\n\n### 1.2 Column-Oriented Storage\n\nColumnar storage stores data column by column. All values for a single column are stored contiguously.\n\n\n```shell\nColumn Storage Layout (on disk):\n\nid column: [1, 2, 3, 4]\nname column: [Alice, Bob, Carol, Dave]\nage column: [30, 25, 35, 28]\ncity column: [NYC, SF, NYC, LA]\nsalary column: [75000, 85000, 95000, 70000]\n\nPhysical bytes on disk:\n[1|2|3|4][Alice|Bob|Carol|Dave][30|25|35|28][NYC|SF|NYC|LA][75000|85000|95000|70000]\n```\n\n\n\n```mermaid\nflowchart LR\n subgraph disk[\"Disk Layout (Column Storage)\"]\n C1[\"id: 1, 2, 3, 4\"]:::primary\n C2[\"name: Alice, Bob, Carol, Dave\"]:::orange\n C3[\"age: 30, 25, 35, 28\"]:::yellow\n C4[\"city: NYC, SF, NYC, LA\"]:::green\n C5[\"salary: 75000, 85000, 95000, 70000\"]:::purple\n end\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef yellow fill:#ffd43b,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n```\n\n\n**How columnar storage handles the same query:**\n\n\n```sql\n-- Query: Get average salary\nSELECT AVG(salary) FROM employees;\n```\n\n\n\n```shell\nColumn storage:\n1. Read only the salary column → [75000, 85000, 95000, 70000]\n2. Calculate average\n\nData read: 4 values (just salary column)\nData needed: 4 values\nEfficiency: 100% (no wasted I/O)\n```\n\n\n### 1.3 The I/O Difference\n\nThe efficiency gap grows dramatically with table width and row count:\n\n\n| Table | Row Storage Reads | Column Storage Reads | Savings |\n|-------|------------------|---------------------|---------|\n| 5 columns, 1M rows, query 1 col | 5M values | 1M values | 80% |\n| 50 columns, 1M rows, query 2 cols | 50M values | 2M values | 96% |\n| 200 columns, 100M rows, query 5 cols | 20B values | 500M values | 97.5% |\n\n\nThis is why columnar storage dominates analytics. Wide tables with selective column access see 10-100x performance improvements.\n\n---\n\n# 2. Why Columnar Storage Excels at Analytics\n\nColumnar storage isn't just about skipping columns. Several factors combine to make it exceptionally efficient for analytical workloads.\n\n### 2.1 Column Pruning\n\nThe most obvious benefit: read only the columns you need.\n\n\n```mermaid\nflowchart TB\n subgraph query[\"Query: SELECT city, AVG(salary)\"]\n Q[Query Processor]:::primary\n end\n\n subgraph cols[\"Column Files\"]\n C1[\"id\"]:::red\n C2[\"name\"]:::red\n C3[\"age\"]:::red\n C4[\"city\"]:::green\n C5[\"salary\"]:::green\n end\n\n Q -->|\"Read\"| C4\n Q -->|\"Read\"| C5\n Q -.->|\"Skip\"| C1\n Q -.->|\"Skip\"| C2\n Q -.->|\"Skip\"| C3\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef red fill:#ff8787,stroke:#000,color:#000\n```\n\n\n\n```shell\nTypical analytics query patterns:\n- Dashboards: 5-10 columns out of 100+\n- Reports: 10-20 columns out of 200+\n- Aggregations: 2-5 columns for GROUP BY and metrics\n\nColumn pruning alone provides 80-95% I/O reduction for most analytics.\n```\n\n\n### 2.2 Better Compression\n\nValues in a column share the same data type and often have similar values. This creates excellent compression opportunities.\n\n\n```shell\nRow storage compression:\n[1|Alice|30|NYC|75000][2|Bob|25|SF|85000]...\n- Mixed types: integers, strings, dates\n- No patterns to exploit\n- Typical compression: 2-3x\n\nColumn storage compression:\ncity column: [NYC, SF, NYC, LA, NYC, NYC, SF, NYC...]\n- Same type throughout\n- Repeated values (many NYCs)\n- Dictionary encoding: NYC=1, SF=2, LA=3\n- Becomes: [1, 2, 1, 3, 1, 1, 2, 1...]\n- Typical compression: 10-20x\n```\n\n\n\n```mermaid\nflowchart LR\n subgraph original[\"Original City Column\"]\n O[\"NYC, SF, NYC, LA, NYC, NYC, SF, NYC
8 strings × ~3 bytes = 24 bytes\"]:::orange\n end\n\n subgraph encoded[\"Dictionary Encoded\"]\n D[\"Dictionary: NYC=1, SF=2, LA=3\"]:::green\n V[\"Values: 1, 2, 1, 3, 1, 1, 2, 1
8 integers × 1 byte = 8 bytes\"]:::green\n end\n\n original -->|\"Encode\"| encoded\n\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n### 2.3 Vectorized Processing\n\nColumnar data enables SIMD (Single Instruction, Multiple Data) operations. Modern CPUs can process multiple values simultaneously.\n\n\n```shell\nRow processing (scalar):\nfor each row:\n load row from memory\n extract salary field\n add to sum\n\nOperations: N loads, N extractions, N additions\n\nColumn processing (vectorized):\nload salary column chunk (1024 values)\nSIMD add all 1024 values in ~4 CPU cycles\n\nOperations: 1 load, 1 SIMD operation\nSpeedup: 100-1000x for numerical operations\n```\n\n\n\n```mermaid\nflowchart TB\n subgraph scalar[\"Scalar Processing (Row)\"]\n S1[\"Load row 1\"]:::orange --> S2[\"Extract salary\"]:::orange --> S3[\"Add to sum\"]:::orange\n S4[\"Load row 2\"]:::orange --> S5[\"Extract salary\"]:::orange --> S6[\"Add to sum\"]:::orange\n S7[\"...repeat N times...\"]:::orange\n end\n\n subgraph vector[\"Vectorized Processing (Column)\"]\n V1[\"Load 1024 salaries\"]:::green --> V2[\"SIMD sum all 1024\"]:::green\n V3[\"Load next 1024\"]:::green --> V4[\"SIMD sum all 1024\"]:::green\n end\n\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n### 2.4 Late Materialization\n\nColumnar engines can delay reconstructing full rows until absolutely necessary.\n\n\n```sql\nSELECT name, salary\nFROM employees\nWHERE department = 'Engineering'\n AND salary > 100000;\n```\n\n\n\n```shell\nRow storage:\n1. Scan all rows\n2. For each row, check department and salary\n3. Return matching rows\n\nColumn storage (late materialization):\n1. Scan department column → get matching row positions [2, 5, 8, 12, ...]\n2. Scan salary column at those positions → filter further [5, 12, ...]\n3. Only NOW read name column for positions [5, 12]\n4. Reconstruct rows only for final results\n\nBenefit: Only read name column for rows that passed all filters\n```\n\n\n### 2.5 Predicate Pushdown with Statistics\n\nColumnar formats store min/max statistics per column chunk. Entire chunks can be skipped without reading.\n\n\n```shell\nParquet file structure:\n\nRow Group 1:\n - salary column: min=45000, max=75000\n - (other columns...)\n\nRow Group 2:\n - salary column: min=70000, max=120000\n - (other columns...)\n\nRow Group 3:\n - salary column: min=50000, max=80000\n - (other columns...)\n\nQuery: WHERE salary > 100000\n\nRow Group 1: max=75000 < 100000 → SKIP ENTIRELY\nRow Group 2: max=120000 > 100000 → MUST SCAN\nRow Group 3: max=80000 < 100000 → SKIP ENTIRELY\n\nResult: Read only 1/3 of the data\n```\n\n\n---\n\n# 3. How Columnar Storage Works Internally\n\nLet's look at how columnar storage is physically organized, using Parquet as an example.\n\n### 3.1 File Structure\n\nColumnar files organize data into row groups, columns, and pages.\n\n\n```mermaid\nflowchart TB\n subgraph file[\"Parquet File\"]\n subgraph rg2[\"Row Group 2\"]\n C2A[\"Column A
Pages\"]:::orange\n C2B[\"Column B
Pages\"]:::orange\n C2C[\"Column C
Pages\"]:::orange\n end\t\n\t\n subgraph rg1[\"Row Group 1 (128 MB default)\"]\n C1A[\"Column A
Pages\"]:::primary\n C1B[\"Column B
Pages\"]:::primary\n C1C[\"Column C
Pages\"]:::primary\n end\n F[\"Footer
Schema + Statistics + Offsets\"]:::green\n end\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n\n```shell\nParquet File Anatomy:\n\n┌─────────────────────────────────────┐\n│ Row Group 1 │\n│ ┌─────────┬─────────┬─────────┐ │\n│ │ Col A │ Col B │ Col C │ │\n│ │ Pages │ Pages │ Pages │ │\n│ └─────────┴─────────┴─────────┘ │\n├─────────────────────────────────────┤\n│ Row Group 2 │\n│ ┌─────────┬─────────┬─────────┐ │\n│ │ Col A │ Col B │ Col C │ │\n│ │ Pages │ Pages │ Pages │ │\n│ └─────────┴─────────┴─────────┘ │\n├─────────────────────────────────────┤\n│ Footer │\n│ - Schema definition │\n│ - Row group metadata │\n│ - Column chunk offsets │\n│ - Min/max statistics per column │\n│ - Encoding information │\n└─────────────────────────────────────┘\n\nKey sizes:\n- Row Group: 128 MB - 1 GB (configurable)\n- Page: 1 MB default\n- File: Any size (often 128 MB - 1 GB for optimal parallelism)\n```\n\n\n## 3.2 Row Groups\n\nRow groups are the unit of parallelism. Each row group contains a subset of rows, with all columns for those rows.\n\n\n```shell\nWhy row groups?\n\n1. Parallel Processing\n - Each row group can be processed independently\n - Spark/Presto assign different row groups to different workers\n\n2. Memory Management\n - Read one row group at a time\n - Don't need entire file in memory\n\n3. Predicate Pushdown Granularity\n - Statistics stored per row group\n - Skip row groups that don't match filters\n\nSizing row groups:\n- Too small: Overhead from metadata, poor compression\n- Too large: Less parallelism, more memory needed\n- Sweet spot: 128 MB - 512 MB\n```\n\n\n## 3.3 Pages and Encoding\n\nWithin each column chunk, data is organized into pages with specific encodings.\n\n\n```mermaid\nflowchart TB\n subgraph column[\"Column Chunk\"]\n subgraph page1[\"Page 1\"]\n H1[\"Header: encoding, count\"]:::orange\n D1[\"Encoded Data\"]:::green\n end\n subgraph page2[\"Page 2\"]\n H2[\"Header\"]:::orange\n D2[\"Encoded Data\"]:::green\n end\n subgraph page3[\"Page 3\"]\n H3[\"Header\"]:::orange\n D3[\"Encoded Data\"]:::green\n end\n end\n\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n**Common encodings:**\n\n\n| Encoding | Description | Best For |\n|----------|-------------|----------|\n| Plain | Raw values, no encoding | Random data |\n| Dictionary | Map values to integers | Low cardinality (city, status) |\n| Run-Length (RLE) | Compress repeated values | Sorted columns, many repeats |\n| Delta | Store differences between values | Timestamps, sequential IDs |\n| Bit-Packing | Use minimum bits needed | Small integers |\n\n\n\n```shell\nDictionary Encoding Example:\n\nOriginal values: [\"NYC\", \"SF\", \"NYC\", \"NYC\", \"LA\", \"SF\", \"NYC\"]\n\nDictionary:\n 0 → \"NYC\"\n 1 → \"SF\"\n 2 → \"LA\"\n\nEncoded: [0, 1, 0, 0, 2, 1, 0]\n\nStorage:\n Dictionary: 3 strings = ~9 bytes\n Values: 7 integers × 2 bits = ~2 bytes\n Total: ~11 bytes\n\nOriginal: 7 strings × ~3 bytes = ~21 bytes\nCompression ratio: ~2x (just from dictionary encoding)\n```\n\n\n## 3.4 Reading Columnar Data\n\n\n```mermaid\nsequenceDiagram\n participant Q as Query Engine\n participant F as File Footer\n participant RG as Row Groups\n participant C as Column Chunks\n\n Q->>F: 1. Read footer (schema, statistics)\n F-->>Q: Schema + row group metadata\n\n Q->>Q: 2. Identify needed columns\n Q->>Q: 3. Check statistics, skip row groups\n\n loop For each matching row group\n Q->>RG: 4. Get column chunk offsets\n RG-->>Q: Offsets for needed columns only\n\n Q->>C: 5. Read column chunks (skip others)\n C-->>Q: Compressed column data\n\n Q->>Q: 6. Decompress and decode\n Q->>Q: 7. Apply filters, aggregate\n end\n```\n\n\n\n```shell\nRead Process:\n\n1. Read footer (small, at end of file)\n - Get schema definition\n - Get row group locations and statistics\n\n2. Determine which row groups to read\n - Check min/max against query predicates\n - Skip row groups that can't match\n\n3. For each relevant row group:\n - Read only needed column chunks\n - Decompress and decode\n - Apply remaining filters\n - Produce results\n\nKey optimization: Footer is read FIRST (even though at end of file)\nThis allows skipping most of the file for selective queries.\n```\n\n\n---\n\n# 4. Compression: The Hidden Superpower\n\nCompression is where columnar storage really shines. Same-type values compress dramatically better than mixed rows.\n\n## 4.1 Why Columns Compress Better\n\n\n```shell\nRow data (mixed types):\n[1, \"Alice\", 30, \"NYC\", 75000, 2, \"Bob\", 25, \"SF\", 85000, ...]\n\n- Integer, String, Integer, String, Integer pattern\n- No predictable sequence\n- Compression algorithm sees noise\n\nColumn data (same type):\nages: [30, 25, 35, 28, 32, 29, 31, 27, ...]\n\n- All integers\n- Similar values (adults are 20-60)\n- Delta encoding: [30, -5, +10, -7, +4, -3, +2, -4, ...]\n- Deltas are small, compress well\n```\n\n\n## 4.2 Compression Techniques\n\n\n```mermaid\nflowchart TB\n subgraph techniques[\"Columnar Compression Stack\"]\n E[\"Encoding Layer
Dictionary, RLE, Delta\"]:::orange\n G[\"General Compression
Snappy, Zstd, LZ4, Gzip\"]:::green\n end\n\n D[Raw Column Data]:::primary --> E --> G --> C[Compressed Bytes]:::purple\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n```\n\n\n**Encoding + Compression Example:**\n\n\n```shell\nOriginal salary column (1000 values):\n[75000, 85000, 75000, 95000, 85000, 75000, ...]\n\nStep 1: Dictionary Encoding\nDictionary: {75000: 0, 85000: 1, 95000: 2, ...}\nEncoded: [0, 1, 0, 2, 1, 0, ...] (1000 small integers)\n\nStep 2: Bit-Packing\nOnly 10 unique salaries → need 4 bits per value\n1000 values × 4 bits = 500 bytes\n\nStep 3: General Compression (Snappy)\nSnappy compression → ~400 bytes\n\nOriginal size: 1000 × 4 bytes = 4000 bytes\nFinal size: ~400 bytes\nCompression ratio: 10x\n```\n\n\n## 4.3 Compression Codec Comparison\n\n\n| Codec | Compression Ratio | Speed | CPU Usage | Best For |\n|-------|------------------|-------|-----------|----------|\n| Snappy | Low-Medium (2-4x) | Very Fast | Low | Hot data, frequent queries |\n| LZ4 | Low-Medium (2-4x) | Very Fast | Low | Real-time analytics |\n| Zstd | High (4-8x) | Fast | Medium | Balanced workloads |\n| Gzip | High (5-10x) | Slow | High | Cold data, archival |\n\n\n\n```shell\nChoosing compression:\n\nQuery-heavy workloads (data warehouse):\n- Use Snappy or LZ4\n- Faster decompression = faster queries\n- Storage is cheaper than compute\n\nStorage-heavy workloads (data lake archive):\n- Use Zstd or Gzip\n- Better compression = lower storage cost\n- Queries are infrequent\n```\n\n\n## 4.4 Real-World Compression Results\n\n\n```shell\nTypical compression ratios for columnar formats:\n\n| Data Type | Row Storage | Column Storage |\n|-----------|-------------|----------------|\n| Transaction logs | 2-3x | 10-15x |\n| Time series | 2-3x | 15-25x |\n| Event data | 2-4x | 8-12x |\n| Dimension tables | 2-3x | 5-8x |\n\nExample: 1 TB raw data\n- Row storage (gzip): ~400 GB\n- Columnar (Parquet + Snappy): ~100 GB\n- Columnar (Parquet + Zstd): ~70 GB\n\nStorage cost at $0.023/GB/month (S3):\n- Row: $9.20/month\n- Parquet/Snappy: $2.30/month\n- Parquet/Zstd: $1.61/month\n```\n\n\n---\n\n# 5. Popular Columnar Formats\n\nSeveral columnar file formats dominate the data engineering landscape.\n\n## 5.1 Apache Parquet\n\nThe most widely used columnar format, especially in cloud data lakes.\n\n\n```shell\nParquet Characteristics:\n\n- Origin: Twitter and Cloudera (2013)\n- Ecosystem: Spark, Hive, Presto, AWS, GCP, Azure\n- Schema: Self-describing (schema in footer)\n- Nested Data: Full support for complex types\n- Encodings: Dictionary, RLE, Delta, Bit-packing\n- Compression: Snappy, Gzip, LZ4, Zstd\n\nTypical Use Cases:\n- Data lake storage (S3, GCS, ADLS)\n- Spark/Presto/Hive tables\n- ML training datasets\n- Data warehouse staging\n```\n\n\n**Parquet file example (Python):**\n\n\n```python\nimport pyarrow as pa\nimport pyarrow.parquet as pq\n\n# Create sample data\ntable = pa.table({\n 'id': [1, 2, 3, 4],\n 'name': ['Alice', 'Bob', 'Carol', 'Dave'],\n 'salary': [75000, 85000, 95000, 70000],\n 'department': ['Eng', 'Eng', 'Sales', 'Eng']\n})\n\n# Write Parquet with compression\npq.write_table(\n table,\n 'employees.parquet',\n compression='snappy',\n row_group_size=100000\n)\n\n# Read specific columns (column pruning)\nresult = pq.read_table(\n 'employees.parquet',\n columns=['name', 'salary'] # Only reads these columns\n)\n```\n\n\n## 5.2 Apache ORC\n\nOptimized for Hive, common in Hadoop ecosystems.\n\n\n```shell\nORC Characteristics:\n\n- Origin: Hortonworks/Facebook (2013)\n- Ecosystem: Hive, Presto, Spark\n- Schema: Self-describing\n- Nested Data: Full support\n- Indexing: Built-in bloom filters and indexes\n- Compression: Zlib, Snappy, LZO, Zstd\n\nAdvantages over Parquet:\n- Better compression (typically 10-20% smaller)\n- Built-in indexes for faster filtering\n- ACID transaction support in Hive\n\nDisadvantages:\n- Smaller ecosystem outside Hadoop\n- Slower adoption in cloud-native tools\n```\n\n\n## 5.3 Apache Arrow\n\nIn-memory columnar format for zero-copy data exchange.\n\n\n```mermaid\nflowchart LR\n subgraph systems[\"Different Systems\"]\n S[Spark]:::orange\n P[Pandas]:::orange\n R[R]:::orange\n J[Java]:::orange\n end\n\n subgraph arrow[\"Arrow Memory Format\"]\n A[Columnar Memory Layout
Zero Copy Transfer]:::green\n end\n\n S <--> A\n P <--> A\n R <--> A\n J <--> A\n\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n\n```shell\nArrow Characteristics:\n\n- Purpose: In-memory analytics, not storage\n- Zero-copy: Share data between processes without serialization\n- Language support: Python, Java, C++, R, Rust, Go\n- IPC: Inter-process communication via shared memory\n\nUse Cases:\n- Data exchange between Spark and Python\n- High-performance analytics (DuckDB, Polars)\n- Streaming data pipelines\n- GPU analytics (RAPIDS)\n\nRelationship to Parquet:\n- Parquet: Disk format (compressed)\n- Arrow: Memory format (uncompressed, ready for processing)\n- Common pattern: Read Parquet → Convert to Arrow → Process\n```\n\n\n## 5.4 Format Comparison\n\n\n| Feature | Parquet | ORC | Arrow |\n|---------|---------|-----|-------|\n| Primary Use | Storage | Storage | Memory |\n| Compression | Yes | Yes | No (memory format) |\n| Self-describing | Yes | Yes | Yes |\n| Nested Types | Yes | Yes | Yes |\n| Ecosystem | Broadest | Hadoop-centric | Growing |\n| Cloud Support | Excellent | Good | N/A (not for storage) |\n| Best For | Data lakes | Hive workloads | Processing |\n\n\n---\n\n# 6. Columnar Databases\n\nBeyond file formats, several databases are built from the ground up around columnar storage.\n\n## 6.1 Data Warehouse Columnar Databases\n\n\n```mermaid\nflowchart TB\n subgraph warehouses[\"Columnar Data Warehouses\"]\n subgraph cloud[\"Cloud-Native\"]\n RS[Amazon Redshift]:::orange\n BQ[Google BigQuery]:::orange\n SF[Snowflake]:::orange\n end\n subgraph onprem[\"On-Premise / Open Source\"]\n CH[ClickHouse]:::green\n VT[Vertica]:::green\n GP[Greenplum]:::green\n end\n end\n\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n**Amazon Redshift:**\n\n\n```shell\nArchitecture:\n- Massively Parallel Processing (MPP)\n- Columnar storage with zone maps (min/max)\n- Distribution keys for data placement\n- Sort keys for optimized scans\n\nBest For:\n- AWS-native analytics\n- Structured data warehousing\n- BI and reporting workloads\n\nKey Features:\n- Automatic compression encoding\n- Result caching\n- Concurrency scaling\n- Spectrum (query S3 directly)\n```\n\n\n**Google BigQuery:**\n\n\n```shell\nArchitecture:\n- Serverless (no cluster management)\n- Capacitor columnar format\n- Dremel execution engine\n- Separated storage and compute\n\nBest For:\n- Serverless analytics\n- Unpredictable workloads\n- Petabyte-scale queries\n\nKey Features:\n- Pay per query (or flat rate)\n- Automatic optimization\n- Streaming inserts\n- ML built-in (BigQuery ML)\n```\n\n\n**ClickHouse:**\n\n\n```shell\nArchitecture:\n- Open-source, self-hosted (or cloud)\n- Extreme compression (often 10-20x)\n- Vectorized query execution\n- Real-time inserts\n\nBest For:\n- Real-time analytics\n- Time series and logs\n- High insert rates\n\nKey Features:\n- Fastest for analytical queries\n- Materialized views\n- Distributed joins\n- Excellent compression\n```\n\n\n## 6.2 How Columnar Databases Differ from Files\n\n\n```shell\nColumnar Files (Parquet/ORC):\n- Static: Write once, read many\n- No indexes beyond statistics\n- External query engine needed (Spark, Presto)\n- No transaction support\n\nColumnar Databases:\n- Dynamic: Insert, update, delete (with caveats)\n- Rich indexing (zone maps, bloom filters, secondary indexes)\n- Built-in query optimizer\n- Some transaction support (varies)\n- Built-in caching and optimization\n\nTrade-offs:\n- Files: Cheaper storage, more flexible\n- Databases: Faster queries, more features, higher cost\n```\n\n\n## 6.3 Hybrid Architectures\n\nModern systems often combine columnar files and databases:\n\n\n```mermaid\nflowchart TB\n subgraph ingest[\"Data Ingestion\"]\n S[Sources]:::primary\n K[Kafka]:::orange\n end\n\n subgraph lake[\"Data Lake (Parquet)\"]\n RAW[Raw Data]:::green\n PROC[Processed Data]:::green\n end\n\n subgraph warehouse[\"Data Warehouse\"]\n HOT[Hot Data
ClickHouse/Redshift]:::red\n end\n\n subgraph query[\"Query Layer\"]\n Q[Presto/Trino]:::purple\n end\n\n S --> K --> RAW\n RAW --> PROC\n PROC -->|\"Hot data\"| HOT\n Q --> PROC\n Q --> HOT\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef red fill:#ff8787,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n```\n\n\n\n```shell\nLakehouse Pattern:\n\n1. All data lands in data lake (Parquet on S3)\n - Cheap storage\n - Schema flexibility\n - Full history retained\n\n2. Hot/frequent data loaded to columnar database\n - Fast queries for dashboards\n - Materialized aggregates\n - Recent data (last 90 days)\n\n3. Query federation layer (Presto/Trino)\n - Query both lake and warehouse\n - Transparent to users\n - Cost-based routing\n```\n\n\n---\n\n# 7. When to Use Columnar vs Row Storage\n\nThe choice between columnar and row storage depends on your workload.\n\n## 7.1 Use Columnar Storage When\n\n\n```shell\n✓ Analytical queries (aggregations, GROUP BY)\n✓ Reading few columns from wide tables\n✓ Large table scans\n✓ Batch processing\n✓ Data warehousing and BI\n✓ Historical data analysis\n✓ Machine learning feature extraction\n```\n\n\n**Ideal columnar query patterns:**\n\n\n```sql\n-- Aggregations (perfect for columnar)\nSELECT region, AVG(revenue), COUNT(*)\nFROM sales\nGROUP BY region;\n\n-- Selective columns from wide table\nSELECT product_name, price\nFROM products; -- Table has 100+ columns\n\n-- Time-range analytics\nSELECT date_trunc('day', timestamp), SUM(amount)\nFROM transactions\nWHERE timestamp > '2024-01-01'\nGROUP BY 1;\n```\n\n\n## 7.2 Use Row Storage When\n\n\n```shell\n✓ Transactional workloads (OLTP)\n✓ Reading entire rows frequently\n✓ Point lookups by primary key\n✓ Frequent single-row updates\n✓ Real-time applications\n✓ Operational databases\n```\n\n\n**Ideal row query patterns:**\n\n\n```sql\n-- Point lookup (row storage faster)\nSELECT * FROM users WHERE id = 12345;\n\n-- Full row retrieval\nSELECT * FROM orders WHERE order_id = 'ABC123';\n\n-- Frequent updates\nUPDATE inventory SET quantity = quantity - 1 WHERE sku = 'XYZ';\n\n-- Insert single rows\nINSERT INTO events (user_id, event_type, timestamp) VALUES (...);\n```\n\n\n## 7.3 Decision Framework\n\n\n```mermaid\nflowchart TD\n A[What's your primary workload?]:::primary\n A --> B{Query pattern?}\n B -->|\"SELECT * (full rows)\"| C[Row Storage]:::green\n B -->|\"SELECT few columns\"| D{Read/Write ratio?}\n D -->|\"Write-heavy\"| E{Update pattern?}\n D -->|\"Read-heavy\"| F[Columnar Storage]:::orange\n E -->|\"Update in place\"| C\n E -->|\"Append only\"| F\n\n C --> G[\"PostgreSQL, MySQL
MongoDB, DynamoDB\"]:::green\n F --> H[\"Parquet, Redshift
BigQuery, ClickHouse\"]:::orange\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n```\n\n\n## 7.4 Hybrid Approaches\n\nMany systems use both:\n\n\n```shell\nPattern: OLTP + OLAP\n\n┌─────────────────────────────────────────────────┐\n│ Application │\n└───────────────────┬─────────────────────────────┘\n │\n ┌───────────┴───────────┐\n │ │\n ▼ ▼\n┌───────────────┐ ┌───────────────┐\n│ PostgreSQL │ ────► │ Redshift │\n│ (Row/OLTP) │ ETL │ (Column) │\n│ │ │ │\n│ Transactions │ │ Analytics │\n│ Point lookups │ │ Reports │\n│ Updates │ │ Dashboards │\n└───────────────┘ └───────────────┘\n\nReal-time ops here Analytics here\n```\n\n\n---\n\n# 8. Design Patterns and Best Practices\n\n## 8.1 Optimal File Sizing\n\n\n```shell\nParquet File Sizing:\n\nToo Small (< 64 MB):\n- Metadata overhead significant\n- Poor compression\n- Too many files to manage\n- Excessive S3 LIST operations\n\nToo Large (> 1 GB):\n- Less parallelism (fewer files to distribute)\n- Memory pressure during reads\n- Longer failure recovery\n\nSweet Spot: 128 MB - 512 MB\n- Good parallelism\n- Efficient compression\n- Manageable metadata\n\nRow Group Sizing:\n- Default: 128 MB\n- Adjust based on query patterns\n- Smaller = more predicate pushdown opportunities\n- Larger = better compression\n```\n\n\n## 8.2 Partitioning Strategy\n\n\n```mermaid\nflowchart TB\n subgraph lake[\"Data Lake Structure\"]\n subgraph part[\"Partitioned by date and region\"]\n P1[\"s3://data/year=2024/month=01/region=US/\"]:::green\n P2[\"s3://data/year=2024/month=01/region=EU/\"]:::green\n P3[\"s3://data/year=2024/month=02/region=US/\"]:::green\n end\n end\n\n Q[\"Query: WHERE year=2024
AND month=01 AND region=US\"]:::primary\n\n Q -->|\"Only scans\"| P1\n Q -.->|\"Skips\"| P2\n Q -.->|\"Skips\"| P3\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n\n```shell\nPartition Column Guidelines:\n\nGood partition columns:\n- Date/time (most queries filter by time)\n- Region/country (geographic isolation)\n- Tenant ID (multi-tenant systems)\n- Category (if frequently filtered)\n\nBad partition columns:\n- High cardinality (user_id = millions of partitions)\n- Frequently updated columns\n- Columns rarely used in WHERE clauses\n\nPartition granularity:\n- Too fine: Many small files\n- Too coarse: Large scans within partition\n- Rule of thumb: 100 MB - 1 GB per partition\n```\n\n\n## 8.3 Sort Order Optimization\n\n\n```shell\nSorting improves:\n1. Compression (similar values together)\n2. Predicate pushdown (tighter min/max)\n3. Range queries (sequential I/O)\n\nSort Key Strategy:\n\n-- Good: Sort by commonly filtered columns\nSORT BY (date, customer_id)\n\nQueries like \"WHERE date = '2024-01-15'\" will:\n- Have tight min/max per row group\n- Skip most row groups via statistics\n\n-- Also consider clustering for Hive/Spark:\nCLUSTERED BY (customer_id) SORTED BY (date) INTO 256 BUCKETS\n```\n\n\n## 8.4 Schema Evolution\n\n\n```shell\nColumnar formats handle schema changes:\n\nParquet Schema Evolution:\n✓ Add columns (new columns have NULL for old data)\n✓ Remove columns (old columns ignored in new reads)\n✗ Rename columns (breaks compatibility)\n✗ Change types (mostly incompatible)\n\nSafe schema changes:\nALTER TABLE ADD COLUMN new_field STRING;\n-- Old files: new_field = NULL\n-- New files: new_field = actual value\n\nUnsafe (avoid):\nALTER TABLE RENAME COLUMN old_name TO new_name;\n-- Breaks existing queries and files\n```\n\n\n## 8.5 Compaction\n\nOver time, many small files accumulate. Compaction merges them.\n\n\n```shell\nBefore Compaction:\ns3://data/date=2024-01-15/\n ├── part-00001.parquet (10 MB)\n ├── part-00002.parquet (15 MB)\n ├── part-00003.parquet (8 MB)\n ... (500 files, average 12 MB)\n\nProblem:\n- 500 files × metadata overhead\n- 500 S3 GET requests per query\n- Poor compression across files\n\nAfter Compaction:\ns3://data/date=2024-01-15/\n ├── part-00001.parquet (256 MB)\n ├── part-00002.parquet (256 MB)\n ├── part-00003.parquet (200 MB)\n\nBenefits:\n- 3 files vs 500\n- Better compression\n- Faster queries\n```\n\n\n---\n\n# 9. Interview Insights\n\nColumnar storage comes up frequently in system design interviews. Here's how to discuss it effectively.\n\n## 9.1 Common Interview Questions\n\n**Q: When would you use columnar vs row storage?**\n\n\n```shell\nUse columnar when:\n- Analytics workloads (aggregations, GROUP BY)\n- Wide tables with selective column access\n- Append-only or batch update patterns\n- Read-heavy workloads\n\nUse row when:\n- OLTP workloads (transactions)\n- Point lookups and full row retrieval\n- Frequent single-row updates\n- Low-latency requirements\n\nReal systems often use both:\n- PostgreSQL for transactions (row)\n- Redshift for analytics (columnar)\n- ETL moves data between them\n```\n\n\n**Q: Explain how columnar storage achieves better compression.**\n\n\n```shell\nThree key factors:\n\n1. Same-type values together\n - All integers, all strings, etc.\n - Algorithms optimized per type\n\n2. Similar values adjacent\n - Sorted data has minimal deltas\n - Repeated values run-length encoded\n\n3. Specialized encodings\n - Dictionary: Low-cardinality strings → integers\n - Delta: Sequential values → small differences\n - RLE: Repeated values → (value, count)\n\nExample:\n- Status column: [\"active\", \"active\", \"active\", \"inactive\", ...]\n- Dictionary: active=0, inactive=1\n- RLE: [(0, 1000), (1, 50), (0, 500), ...]\n- Result: 10x smaller than row storage\n```\n\n\n**Q: Design a data warehouse for an e-commerce analytics platform.**\n\n\n```shell\nArchitecture:\n\n1. Data Ingestion\n - Events from application → Kafka\n - Batch from databases → Airflow\n\n2. Data Lake (S3 + Parquet)\n - Raw layer: Original data\n - Processed layer: Cleaned, enriched\n - Partitioned by date and event_type\n\n3. Data Warehouse (Redshift or BigQuery)\n - Fact tables: orders, events, clicks\n - Dimension tables: users, products, dates\n - Columnar storage for fast aggregations\n\n4. Query Patterns\n - Dashboard: Last 24 hours → hot data in warehouse\n - Historical: Query Parquet via Athena/Presto\n - Ad-hoc: Federated queries across both\n\nKey Design Decisions:\n- Parquet with Snappy for S3 (query speed)\n- Partition by date (time-range queries)\n- Sort by user_id within partitions (user analysis)\n- Materialized views for common aggregations\n```\n\n\n## 9.2 Key Points to Demonstrate\n\n\n```shell\n1. Understand the I/O model\n - \"Columnar reads only needed columns\"\n - \"Row storage reads entire rows\"\n - Quantify: \"A query on 5 of 100 columns reads 95% less data\"\n\n2. Know compression benefits\n - \"Same-type values compress 5-10x better\"\n - \"Dictionary encoding for low-cardinality columns\"\n - \"This reduces both storage and I/O\"\n\n3. Understand predicate pushdown\n - \"Statistics in file footer enable skipping\"\n - \"Min/max per column per row group\"\n - \"Can skip 90%+ of data for selective queries\"\n\n4. Know the trade-offs\n - \"Columnar is slower for point lookups\"\n - \"Updates are expensive (rewrite files)\"\n - \"Not suitable for OLTP workloads\"\n\n5. Reference real systems\n - \"BigQuery and Redshift are columnar\"\n - \"Parquet is the standard format for data lakes\"\n - \"Most analytics systems use columnar internally\"\n```\n\n\n## 9.3 Quick Reference Card\n\n\n| Aspect | Row Storage | Columnar Storage |\n|--------|-------------|------------------|\n| Layout | Records together | Columns together |\n| Best for | OLTP, point queries | OLAP, analytics |\n| Compression | 2-4x typical | 10-20x typical |\n| Read pattern | Full rows | Selected columns |\n| Write pattern | Single rows | Batch/append |\n| Update support | In-place | Rewrite files |\n| Examples | PostgreSQL, MySQL | Parquet, Redshift, BigQuery |\n\n\n---\n\n# Summary\n\nColumnar storage is fundamental to modern analytics. Here are the key takeaways:\n\n1. **The core insight is simple.** Store columns together instead of rows. This lets queries read only the data they need.\n\n1. **I/O reduction is dramatic.** Analytical queries typically access 5-10% of columns. Columnar storage reduces I/O by 90-95%.\n\n1. **Compression compounds the benefit.** Same-type values compress 5-10x better than mixed rows. Combined with encoding, columnar files are often 10-20x smaller.\n\n1. **Vectorized processing adds speed.** Columnar layouts enable SIMD operations, processing thousands of values in parallel.\n\n1. **Parquet is the standard.** For data lakes, Parquet with Snappy compression is the default choice. ORC is common in Hadoop.\n\n1. **Modern warehouses are columnar.** BigQuery, Redshift, Snowflake, and ClickHouse all use columnar storage internally.\n\n1. **It's not for everything.** Transactional workloads, point lookups, and frequent updates still need row storage.\n\n1. **Hybrid architectures are common.** Use row storage for OLTP, columnar for analytics, and ETL to connect them.\n\nWhen designing analytical systems, columnar storage should be your default. The combination of reduced I/O, better compression, and vectorized processing makes it the foundation of every modern data platform.\n\n---\n\n## References\n\n- [Apache Parquet Documentation](undefined) - Official specification and format details\n- [The Design and Implementation of Modern Column-Oriented Database Systems](undefined) - Academic foundation of columnar databases\n- [Dremel: Interactive Analysis of Web-Scale Datasets](undefined) - Google's paper that inspired Parquet and BigQuery\n- [ClickHouse Documentation: Why ClickHouse is So Fast](undefined) - Deep dive into columnar performance\n- [Apache Arrow: A Cross-Language Development Platform](undefined) - In-memory columnar format\n- [Amazon Redshift: Columnar Storage](undefined) - AWS documentation on Redshift internals","pageType":"system-design"}

Columnar Storage

Ashish Pratap Singh

Get Premium