Row Stores vs. Column Stores: Why Database Design Shapes Performance
Databases organize data physically in two fundamentally different ways, despite presenting identical logical structures to users. Row stores group all values from individual records together—like a CSV file—the approach used by Postgres and MySQL. Column stores do the opposite, grouping all values from each column together, as in DuckDB and ClickHouse. This is a pure physical storage choice, but it determines how efficiently queries run.
The performance difference stems from how modern CPUs fetch memory. Analytics queries often need just one or two columns across millions of records. With row storage, the database must read entire records into memory and discard most values, wasting CPU effort. CPUs fetch memory in fixed-size cache lines (typically 64 bytes), so reading a 48-byte row to access 4 bytes of data is 12 times less efficient than necessary. Column stores avoid this waste by storing values from the same column contiguously, letting queries read only what they need. Single-record lookups that need all fields work better with row storage, since the data sits together in one location.
Row stores power online transaction processing (OLTP), where applications frequently modify individual records—the common pattern in production systems. Column stores serve online analytical processing (OLAP), where analytics workloads scan large datasets to answer aggregate questions. This architectural choice affects not only memory efficiency but also CPU instruction throughput and data compression effectiveness.
Read the full article
Continue reading at Hacker News →This is an AI-generated summary. Read the original for the full story.