hn.today

Rows vs. Columns: How different are they, really?

spiraldb.com3 points0 comments
Screenshot of Rows vs. Columns: How different are they, really?

Rows and columns are two physical ways to organize the same logical table, not different data models: SQL queries and results can remain identical. Row stores keep each record’s fields together, matching the needs of applications that create, update, or retrieve individual records; Postgres and MySQL are examples. Column stores group values by field, making them better suited to analytics, where queries scan many records but often use only a few columns. The central argument is that although people naturally think of data as complete records, analytical workloads benefit from processing it column by column.

The comparison makes the trade-off concrete using bike-ride data. To calculate average ride duration, a column store can scan only the duration values. In the example, a row layout makes the CPU load 48 bytes per ride to use 4 bytes, while a column layout loads just the needed data - about 48 MB versus 4 MB for a million rides. Retrieving every field for one ride reverses the advantage: its values are together in a row, whereas a column store must gather them from separate arrays. Columnar storage also enables vectorized execution: operators process batches of values rather than handling each value individually, reducing repeated query-engine bookkeeping and allowing data to remain in CPU cache between operations.

Read on spiraldb.com0 comments on Hacker News

Summary generated by AI from the linked article. hn.today is not affiliated with Hacker News or Y Combinator.

More in Programming

Solid 2.0

Solid 2.0

Solid 2.0 is now stable, five years after version 1.0, with a smaller API and diagnostics designed for AI agents. Its reactive graph now handles asynchronous computations, while built-in server functions support RPC calls and persistent Server-Sent Events connections. (solidjs.com)

The daily digest

Today's best Hacker News stories, summarized and screenshotted, one email a day.