Dictionary encoding
Dictionary encoding is a compression technique that replaces repeated column values with small integer codes, storing the distinct values once in a separate lookup table.
Overview
Dictionary encoding is especially effective on columns with relatively few distinct values repeated across many rows — think status codes, country names, product categories, or any low-cardinality string column. Instead of storing the full string value in every row, the column stores a compact integer code, and a separate dictionary maps each code back to its original value.
How It Works
Given a column of values like ['active', 'active', 'inactive', 'active', 'pending'], dictionary encoding builds a dictionary — {0: 'active', 1: 'inactive', 2: 'pending'} — and stores the column as the much smaller sequence of codes [0, 0, 1, 0, 2]. This shrinks storage, and because the codes are fixed-width integers, they're also faster to compare than variable-length strings.
Trade-offs
Dictionary encoding works best on low-to-moderate cardinality columns. If a column has close to as many distinct values as rows (like a UUID column), the dictionary itself becomes almost as large as the original data, eliminating most of the benefit. Databases typically evaluate cardinality per block or column chunk and fall back to a different encoding when dictionary encoding wouldn't help.
DuckDB's Dictionary Encoding
DuckDB automatically applies dictionary encoding to low-cardinality column chunks as one of its lightweight compression schemes, alongside run-length encoding, bit packing, and FSST for strings — chosen per chunk without any manual configuration. Dictionary-encoded columns also speed up operations like GROUP BY, since comparing and hashing small integer codes is cheaper than comparing full strings. DuckDB uses the same technique when writing Parquet files, and dictionary-encoded Parquet column chunks are also what makes DuckDB's Parquet Bloom filter support possible during reads.
Related terms
Run-length encoding (RLE) is a compression technique that replaces consecutive repeated values with a single (value, count) pair, shrinking runs of identical data.
Data compression →Data compression encodes data using fewer bits than its original representation, reducing storage footprint and the amount of I/O needed to read it.
Columnar storage →Columnar storage is a data layout that groups values from the same column together on disk, rather than storing complete rows contiguously, which speeds up analytical scans and compression.
Cardinality →Cardinality refers to the number of distinct values in a column or dataset, or, in data modeling, the nature of a relationship between two tables (such as one-to-many).
Compression codecs →Compression codecs are algorithms used to reduce the size of data on disk or over the network — common examples include Snappy, Gzip, Zstandard (zstd), and LZ4 — trading off compression ratio against CPU cost and speed.
DISTINCT →DISTINCT removes duplicate rows from a query's result set, returning only unique combinations of the selected columns.
FAQS
On very high-cardinality columns, such as unique IDs, the dictionary itself grows nearly as large as the original data, so the compression benefit largely disappears.
