Storing data for analytics
Warehouses and app databases both speak SQL, but inside they differ. Products that claim to do both (HTAP) now often run two engines behind one SQL interface.
Column-oriented layout
App databases store each row's values together (row-oriented). A warehouse fact table may have 100 columns while a query uses 4. A column store keeps each column's values together, so the query reads only its 4 columns. Every column keeps rows in the same order: row 7 is the 7th value of each column. Do not confuse this with "wide-column" databases, which still store data by row.
Bitmaps and run-length encoding
A column often has few distinct values. Make one bitmap per value: one bit per row, 1 if the row has that value. Mostly-zero bitmaps shrink with run-length encoding, which stores the lengths of runs instead of every bit. Filters become bit math: IN (a, b) is an OR of bitmaps, and x = 1 AND y = 2 is an AND.
Sort order in column stores
You cannot sort each column on its own, or rows stop lining up. Instead sort whole rows by chosen columns, like date, then product. A "last week" query then reads one small stretch. The first sort column gets long runs, so it compresses best.
Writing to column stores
Inserting one row into the middle of sorted, compressed columns would mean rewriting them. So new rows first go to a sorted in-memory store. When enough pile up, they are merged with the column files and written out as new files. Queries read both places, so new data shows up at once.
Cloud warehouse parts
Cloud warehouses keep data in object storage and run compute separately, so each can grow on its own. Open systems split into four parts: a query engine, a storage format for files, a table format that tracks which files form a table, and a catalog of tables, often a separate service that other tools can read.
Remember
- Read only the columns you need.
- Bitmaps plus run-length encoding make filters cheap.
- Sort whole rows; the first sort key compresses best.
- Buffer writes, then write new files in bulk.