How Row and Columnar Formats Actually Work

Row Based Physical Layout: A table is broken into pages or blocks, typically 4 KB to 16 KB in size. Each page holds many complete rows for a given range of primary keys. The system maintains indexes that point into these pages. When you query a user by user_id, the index lookup finds the relevant page, the engine reads it once from disk or cache, and the CPU scans within the page to locate the row. Updates in place are straightforward. If the new row still fits on the page, the engine overwrites the old version or appends a new version and updates internal pointers. This makes row stores ideal for workloads with frequent small updates touching multiple fields in the same record. Columnar Physical Layout: Data is organized into row groups or stripes, where each group might cover 1 million rows. Within each group, the system stores separate column chunks. For a table with 50 columns and 1 billion rows divided into 1,000 row groups, you'd have 50,000 total column chunks. Each chunk stores an encoded array of values. At query time, the execution engine reads only chunks for referenced columns. If your query filters on event_date between two dates, the engine checks chunk statistics and skips entire row groups where the date range doesn't overlap. This is data skipping. Write Handling Differences: Columnar systems typically batch writes. Small writes accumulate in memory or a write optimized structure, then flush as new row groups. Background compaction periodically merges small row groups into larger ones, removes obsolete versions, and rewrites encodings. A system processing 100,000 events per second might buffer for 10 seconds (1 million events) before writing a row group, then compact every hour. This is why columnar systems favor append heavy workloads with periodic batch updates rather than constant random updates hitting individual records.