Rows, Columns and the Separator Problem

Last timeDates, Times, and the Day It Moved

A table can be written row by row or column by column, and the choice decides how much has to be read to answer a question. Separators decide whether it can be read at all.

Rows first or columns first

A table has two dimensions and a file has one, so writing the table down means

choosing an order to flatten it in. There are two sensible choices.

Row by row puts the whole first record, then the whole second, and so on. It is

what a spreadsheet export looks like and what most databases do, and it suits

work that wants complete records: fetch this customer, show this order,

append a new row at the end.

Column by column puts every value of the first field, then every value of the

second. It looks strange written out and it suits work that wants a few fields

of everything: the average of this column, the count of rows where that column

is above a threshold, a sum grouped by a third.

The reason the choice matters is that storage is read in large contiguous

chunks, not one value at a time. Values that sit next to each other arrive

together whether they were wanted or not, so the layout decides how much of what

arrives is useful.

FIG 1The same four rows, flattened two ways

	values read to sum one c	values read to fetch one	cheap to append one row
row by row	16	4	1
column by column	4	4	0

A table of four rows and four columns, so sixteen values in total. Coded 1 for yes and 0 for no in the last column. The first column of the figure is the whole argument: summing one field row by row drags in every other field with it, whereas column by column reads exactly the four values wanted. The second column shows there is no free lunch, since a whole row is scattered across four places in a column layout, and the third shows the other cost, because appending a row means touching every column's run.

What a column layout buys

The saving from a column layout is the proportion of columns the query does not

touch, which makes it worth putting into arithmetic.

FIG 2How many columns a query can touch for a given amount read

: the total size of the table in bytes
: how many columns it has
: the bytes the query is willing to read
: how many columns it can afford to touch

The relation being rearranged is that reading k of m columns costs that fraction of the whole, so B equals k over m times T. A table of a hundred gigabytes in fifty columns, with ten gigabytes of reading affordable, supports a query over five columns. Row by row the same query reads all hundred gigabytes, because the five wanted values sit between the forty five unwanted ones. This is the entire reason analytical stores are column oriented, and it also says when the choice does not matter, since a table with three columns has almost nothing to skip.

There is a second effect that compounds the first. A column holds values of one

kind, often with few distinct values and long runs of repetition, which makes it

compress far better than a row where every adjacent value is a different sort of

thing. The next lesson is about why that is, and the two effects together are

why a column layout commonly reads a tenth of what a row layout does.

The lesson stops here

4 more paragraphs to go

You have read the opening. The rest of the argument, the problems that check whether it landed, and the lines worth keeping at the end all come with a plan.

The first lesson of every course in the library reads the whole way through, free, so you can see exactly what the rest of them are.

See the plan The contents

This is the reading half

Starting the course gives you your own copy of it. Every idea on every page has problems standing under it, marked with a reason rather than a tick, and any sentence you do not believe can be opened and argued with. None of that can happen on a page nobody owns.

The contents

Rows, Columns and the Separator Problem

Rows first or columns first

What a column layout buys

4 more paragraphs to go

This is the reading half

Read alongside