Columnar Compression

How Columnar Compression Actually Works

Column stores use lightweight encodings that are cheap to decode. DuckDB 61,228 tries several per segment and keeps the smallest: constant, RLE (value plus run length), dictionary (distinct values once, small codes per row), bit-packing with frame of reference (value - min in just enough bits), FSST for strings and ALP for floating point. This script stores the mart uncompressed, in order sequence and sorted by genre:

compress.py: the same table uncompressed, compressed, and compressed after sortingPython

import os, duckdb
for name, codec, order in (("uncompressed", "uncompressed", "order_id"),
                           ("by order", "auto", "order_id"), ("by genre", "auto", "genre")):
    path = f"c-{name.replace(' ', '-')}.duckdb"
    if os.path.exists(path): os.remove(path)
    con = duckdb.connect(path)
    con.execute(f"SET force_compression = '{codec}'")          # 'auto' lets DuckDB choose
    con.execute("ATTACH 'booknest.duckdb' AS b (READ_ONLY)")
    con.execute(f"CREATE TABLE sales AS FROM b.sales ORDER BY {order}, line_no")
    used = con.sql("""SELECT DISTINCT column_name || '=' || compression FROM
                      pragma_storage_info('sales') WHERE segment_type <> 'VALIDITY'
                      AND column_name IN ('book_id', 'genre', 'order_date') ORDER BY 1""")
    used = " ".join(row[0] for row in used.fetchall())
    con.close()                                                # writes a checkpoint
    print(f"{name:12} {os.path.getsize(path) / 2**20:5.2f} MiB  {used}")

Output

uncompressed 19.76 MiB  book_id=Uncompressed genre=Uncompressed order_date=Uncompressed
by order      1.76 MiB  book_id=BitPacking genre=Dictionary order_date=RLE
by genre      1.76 MiB  book_id=RLE genre=Dictionary order_date=BitPacking

Compression shrank the file 11 times, to a tenth of PostgreSQL 1,289 's 19.9 MB heap. In order sequence the dates form runs (RLE) and the six book IDs need 3 bits each; sorted by genre, the book IDs become runs and the dates lose theirs. Sort order decides the encodings, which is why ClickHouse 29,491 asks for an ORDER BY key (Tables and ORDER BY Keys).

MENU