Group on integers, join the strings later

Analytical tables repeat the same strings endlessly: station names, product names, user agents, country labels. The DuckDB team has written up the standard remedy, borrowed from data warehousing, and added it to the schema section of its performance guide: move the strings into a small dimension table keyed by narrow integers, aggregate on the keys, and join the strings back only at the end.

Their example is a public dataset of Dutch train stops. It holds 380,959 rows but only 537 distinct station names, and Amsterdam Centraal, 18 bytes long, appears in 7,591 of them. Grouping on the name means hashing and comparing those bytes for every row. In DuckDB a string value is a 16-byte structure, with strings up to 12 bytes stored inline and longer ones stored elsewhere, so the cost grows with length.

Why the keys are sorted

The width of the key follows from the cardinality: UTINYINT covers 255 distinct values, USMALLINT 65,535, UINTEGER about 4.3 billion, so 537 stations need two bytes. Assigning the keys in alphabetical order buys two things. Sorting by the integer produces the same order as sorting by the name, and rebuilding the dimension table from the same data yields the same keys. With a small, dense key range the optimiser can also switch to a perfect hash aggregate and skip hashing altogether.

The post is unusually candid about the payoff. On the 380,000-row sample the difference is small, and it grows with row counts and string lengths, so the advice is to measure both forms on your own data rather than adopt the pattern on faith. It also points at the simpler alternative: DuckDB's ENUM type is dictionary encoding built into the type system, and where the set of values is known up front and rarely changes, it gives most of the benefit without the extra joins.