Model

What columnar storage buys a scan

These effects multiply with the projection benefit. They do not add to it.

reduction = \frac{n}{k} \times c, point read in a column store: \frac{B}{w} amplification, n I/Os

$n$: Columns; columns in the table
$k$: Columns read; columns the query touches
$c$: Compression ratio; the compression ratio
$B$: Access unit; the storage access unit in bytes
$w$: Value width; mean value width in bytes

Trade-off: Read-optimized vs. write-optimized

How it behaves

One input on the horizontal axis, the others held fixed.

projection only (c = 1)with compression c = 4

Data table

Bytes a scan avoids, by columns read (n = 25 columns)
Columns read k	series	Reduction against a row store
1	projection only (c = 1)	25×
1.3	projection only (c = 1)	19.2×
1.6	projection only (c = 1)	15.6×
1.9	projection only (c = 1)	13.2×
2.2	projection only (c = 1)	11.4×
2.5	projection only (c = 1)	10×
2.8	projection only (c = 1)	8.93×
3.1	projection only (c = 1)	8.06×
3.4	projection only (c = 1)	7.35×
3.7	projection only (c = 1)	6.76×
4	projection only (c = 1)	6.25×
4.3	projection only (c = 1)	5.81×
4.6	projection only (c = 1)	5.43×
4.9	projection only (c = 1)	5.1×
5.2	projection only (c = 1)	4.81×
5.5	projection only (c = 1)	4.55×
5.8	projection only (c = 1)	4.31×
6.1	projection only (c = 1)	4.1×
6.4	projection only (c = 1)	3.91×
6.7	projection only (c = 1)	3.73×
7	projection only (c = 1)	3.57×
7.3	projection only (c = 1)	3.42×
7.6	projection only (c = 1)	3.29×
7.9	projection only (c = 1)	3.16×
8.2	projection only (c = 1)	3.05×
8.5	projection only (c = 1)	2.94×
8.8	projection only (c = 1)	2.84×
9.1	projection only (c = 1)	2.75×
9.4	projection only (c = 1)	2.66×
9.7	projection only (c = 1)	2.58×
10	projection only (c = 1)	2.5×
10.3	projection only (c = 1)	2.43×
10.6	projection only (c = 1)	2.36×
10.9	projection only (c = 1)	2.29×
11.2	projection only (c = 1)	2.23×
11.5	projection only (c = 1)	2.17×
11.8	projection only (c = 1)	2.12×
12.1	projection only (c = 1)	2.07×
12.4	projection only (c = 1)	2.02×
12.7	projection only (c = 1)	1.97×
13	projection only (c = 1)	1.92×
13.3	projection only (c = 1)	1.88×
13.6	projection only (c = 1)	1.84×
13.9	projection only (c = 1)	1.8×
14.2	projection only (c = 1)	1.76×
14.5	projection only (c = 1)	1.72×
14.8	projection only (c = 1)	1.69×
15.1	projection only (c = 1)	1.66×
15.4	projection only (c = 1)	1.62×
15.7	projection only (c = 1)	1.59×
16	projection only (c = 1)	1.56×
16.3	projection only (c = 1)	1.53×
16.6	projection only (c = 1)	1.51×
16.9	projection only (c = 1)	1.48×
17.2	projection only (c = 1)	1.45×
17.5	projection only (c = 1)	1.43×
17.8	projection only (c = 1)	1.4×
18.1	projection only (c = 1)	1.38×
18.4	projection only (c = 1)	1.36×
18.7	projection only (c = 1)	1.34×
19	projection only (c = 1)	1.32×
19.3	projection only (c = 1)	1.3×
19.6	projection only (c = 1)	1.28×
19.9	projection only (c = 1)	1.26×
20.2	projection only (c = 1)	1.24×
20.5	projection only (c = 1)	1.22×
20.8	projection only (c = 1)	1.2×
21.1	projection only (c = 1)	1.18×
21.4	projection only (c = 1)	1.17×
21.7	projection only (c = 1)	1.15×
22	projection only (c = 1)	1.14×
22.3	projection only (c = 1)	1.12×
22.6	projection only (c = 1)	1.11×
22.9	projection only (c = 1)	1.09×
23.2	projection only (c = 1)	1.08×
23.5	projection only (c = 1)	1.06×
23.8	projection only (c = 1)	1.05×
24.1	projection only (c = 1)	1.04×
24.4	projection only (c = 1)	1.02×
24.7	projection only (c = 1)	1.01×
25	projection only (c = 1)	1×
1	with compression c = 4	100×
1.3	with compression c = 4	76.9×
1.6	with compression c = 4	62.5×
1.9	with compression c = 4	52.6×
2.2	with compression c = 4	45.5×
2.5	with compression c = 4	40×
2.8	with compression c = 4	35.7×
3.1	with compression c = 4	32.3×
3.4	with compression c = 4	29.4×
3.7	with compression c = 4	27×
4	with compression c = 4	25×
4.3	with compression c = 4	23.3×
4.6	with compression c = 4	21.7×
4.9	with compression c = 4	20.4×
5.2	with compression c = 4	19.2×
5.5	with compression c = 4	18.2×
5.8	with compression c = 4	17.2×
6.1	with compression c = 4	16.4×
6.4	with compression c = 4	15.6×
6.7	with compression c = 4	14.9×
7	with compression c = 4	14.3×
7.3	with compression c = 4	13.7×
7.6	with compression c = 4	13.2×
7.9	with compression c = 4	12.7×
8.2	with compression c = 4	12.2×
8.5	with compression c = 4	11.8×
8.8	with compression c = 4	11.4×
9.1	with compression c = 4	11×
9.4	with compression c = 4	10.6×
9.7	with compression c = 4	10.3×
10	with compression c = 4	10×
10.3	with compression c = 4	9.71×
10.6	with compression c = 4	9.43×
10.9	with compression c = 4	9.17×
11.2	with compression c = 4	8.93×
11.5	with compression c = 4	8.7×
11.8	with compression c = 4	8.47×
12.1	with compression c = 4	8.26×
12.4	with compression c = 4	8.06×
12.7	with compression c = 4	7.87×
13	with compression c = 4	7.69×
13.3	with compression c = 4	7.52×
13.6	with compression c = 4	7.35×
13.9	with compression c = 4	7.19×
14.2	with compression c = 4	7.04×
14.5	with compression c = 4	6.9×
14.8	with compression c = 4	6.76×
15.1	with compression c = 4	6.62×
15.4	with compression c = 4	6.49×
15.7	with compression c = 4	6.37×
16	with compression c = 4	6.25×
16.3	with compression c = 4	6.13×
16.6	with compression c = 4	6.02×
16.9	with compression c = 4	5.92×
17.2	with compression c = 4	5.81×
17.5	with compression c = 4	5.71×
17.8	with compression c = 4	5.62×
18.1	with compression c = 4	5.52×
18.4	with compression c = 4	5.43×
18.7	with compression c = 4	5.35×
19	with compression c = 4	5.26×
19.3	with compression c = 4	5.18×
19.6	with compression c = 4	5.1×
19.9	with compression c = 4	5.03×
20.2	with compression c = 4	4.95×
20.5	with compression c = 4	4.88×
20.8	with compression c = 4	4.81×
21.1	with compression c = 4	4.74×
21.4	with compression c = 4	4.67×
21.7	with compression c = 4	4.61×
22	with compression c = 4	4.55×
22.3	with compression c = 4	4.48×
22.6	with compression c = 4	4.42×
22.9	with compression c = 4	4.37×
23.2	with compression c = 4	4.31×
23.5	with compression c = 4	4.26×
23.8	with compression c = 4	4.2×
24.1	with compression c = 4	4.15×
24.4	with compression c = 4	4.1×
24.7	with compression c = 4	4.05×
25	with compression c = 4	4×

Worked examples, recomputed

Every number the mechanisms quote for this relationship is recomputed from it, so none of them disagrees with it.

Where it appears

Each mechanism states it in its own names. The general symbol each name stands for is shown with it.

Storage: Physical Organization
What should sit next to what, given that I must decide at write time and find out at read time?
$(n / k) \times c$
Read it in place →