6 / 6 OLTP, OLAP & Analytical Stores

Writes Against the Grain

An analytics column store happily ingests 50,000 new event rows per second in bulk, yet scattered single-row updates to month-old events cost orders of magnitude more per row. What explains the difference?

A

Compression in these systems is applied per row, so touching a single row forces the engine to decompress and recompress the entire row group holding all of its neighbors

B

One row's values live in every column's separately compressed sorted segment, so engines buffer writes in a small row-oriented store and merge them into column segments in the background

C

Column stores keep no primary key or row identifier at all, so a single-row update has to rewrite the entire table once just to work out which ordinal position holds the row it should change

D

Updates are forwarded to a row-oriented replica and the result is copied back into the column store afterwards, so every single-row change costs two full network round trips