Rows and Columns on a Disk

6 / 34Machine translation

← →

1 · INTERNALS · DISC

RUN

row-oriented storage (PostgreSQL)

Columnar (ClickHouse, lab)

страница: [ts|event|player|match|mode|...]
          [ts|event|player|match|mode|...]
          [ts|event|player|match|mode|...]

SELECT count() FROM events
WHERE event_type = 'purchase';
-- читает строки целиком: все столбцы

файл столбца    на диске   сжатие
ts              25.46 MiB    ×1.5
player_id       16.44 MiB    ×1.2
event_type      23.13 KiB  ×211.5

тот же запрос читает один файл:
event_type — 23 КиБ из 70 МиБ

Read the relevant section

Compression

Price

A query on 3 columns out of 30 reads about one-tenth of the table, rather than the entire table.

Values of the same type are grouped together. Series of identical values are compressed by a factor of 100.

One line—to append to all column files. That's why people write in batches, and an UPDATE is expensive.

"Columnar" refers to physical storage, not a model.