--- title: Faster String Aggregations with Dimension Tables | daily.dev description: When a GROUP BY targets long, repeated strings (station names, product names, etc.), DuckDB has to hash and compare full strings for every row, which is costly... canonical: https://daily.dev/posts/faster-string-aggregations-with-dimension-tables-eljteph2w twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: Faster String Aggregations with Dimension Tables | daily.dev og:description: When a GROUP BY targets long, repeated strings (station names, product names, etc.), DuckDB has to hash and compare full strings for every row, which is costly... og:url: https://daily.dev/posts/faster-string-aggregations-with-dimension-tables-eljteph2w og:image: https://api.daily.dev/og/posts/elJTEpH2W.png og:image:alt: Faster String Aggregations with Dimension Tables og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # Faster String Aggregations with Dimension Tables **[DuckDB](https://daily.dev/sources/duckdb)** · 15 min read · 0 upvotes · 0 comments ## Summary When a GROUP BY targets long, repeated strings (station names, product names, etc.), DuckDB has to hash and compare full strings for every row, which is costly for anything over 12 bytes. Replacing those strings with a small dimension table of sorted, narrow integer keys lets the aggregation run on cheap fixed-width integers, with the original strings joined back only at the end against the much smaller result set. The post walks through measuring cardinality, picking the narrowest unsigned integer type, building the dimension table with row_number() ordered by the string, rewriting the fact table, and querying it. It also compares the approach to DuckDB's built-in ENUM type, covers encoding multiple string columns, keeping dimension tables updated with new data, and explains why narrow keys don't help when the number of distinct groups is itself the bottleneck (citing a real GitHub issue with 9.2 billion rows). It closes with cases where the pattern isn't worth the added complexity. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### How do I speed up GROUP BY queries in DuckDB when grouping on long repeated strings like station or product names? Move the strings into a small dimension table with sorted, narrow unsigned integer keys (UTINYINT, USMALLINT, or UINTEGER sized to the distinct count), replace the string column in the fact table with that key, aggregate on the integer key, then join the original strings back only at the end against the smaller result set. This avoids hashing and comparing full strings for every row. _daily.dev surfaces practical DuckDB performance techniques like this for engineers tuning large aggregation queries._ ### When should I use a DuckDB ENUM type instead of building a manual dimension table for repeated strings? Use an ENUM when the set of distinct values is known upfront and rarely changes, since it gives dictionary-encoding benefits with no extra joins. Choose a dimension table instead when new values keep arriving (inserting an unknown ENUM value fails), when you need extra attributes per value, or when the data needs to be exported to Parquet or CSV for other tools. _developers weighing ENUM versus dimension tables in DuckDB can track such trade-offs on daily.dev._ ### Why does reducing thread count sometimes lower memory use in a high-cardinality DuckDB GROUP BY aggregation? DuckDB aggregates each thread's rows into its own thread-local hash table before combining results, so when nearly every group appears in nearly every thread (as with 320 million distinct groups spread across 9.2 billion rows on 8 threads), memory use approaches the thread count times the group count. Setting threads to 1 trades speed for memory in these out-of-memory or heavy-spill cases. _teams debugging memory blowups in distributed aggregations can follow this kind of DuckDB internals analysis on daily.dev._ ## Similar posts on daily.dev - [ClickHouse LowCardinality: When It Helps and When It Hurts](https://daily.dev/posts/clickhouse-lowcardinality-when-it-helps-and-when-it-hurts-8spfk4osb) · Last9 · 0 upvotes · 0 comments - [Joins are NOT Expensive\! - Raw Reading](https://daily.dev/posts/joins-are-not-expensive---raw-reading-09a0dx5zp) · Lobsters · 4 upvotes · 1 comments - [ClickHouse Power Tips: LowCardinality](https://daily.dev/posts/clickhouse-power-tips-lowcardinality-lplhezhj7) · BigData Boutique blog · 0 upvotes · 0 comments - [High Cardinality Database for Real-Time Analytics](https://daily.dev/posts/high-cardinality-database-for-real-time-analytics-07mnj0vg7) · CrateDB · 0 upvotes · 0 comments --- Tags: [#sql](https://daily.dev/tags/sql), [#data-warehouse](https://daily.dev/tags/data-warehouse), [#duckdb](https://daily.dev/tags/duckdb) [View this post on daily.dev](https://daily.dev/posts/faster-string-aggregations-with-dimension-tables-eljteph2w) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"Faster String Aggregations with Dimension Tables","url":"https://daily.dev/posts/faster-string-aggregations-with-dimension-tables-eljteph2w","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/faster-string-aggregations-with-dimension-tables-eljteph2w"},"datePublished":"2026-10-01T17:13:52.975Z","dateModified":"2026-10-01T17:34:45.089Z","description":"When a GROUP BY targets long, repeated strings (station names, product names, etc.), DuckDB has to hash and compare full strings for every row, which is costly...","image":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/165732dadedb78f4aeb8d5996556ad62?_a=AQAEuop","thumbnailUrl":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/165732dadedb78f4aeb8d5996556ad62?_a=AQAEuop","isAccessibleForFree":true,"articleSection":"DuckDB","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"DuckDB","logo":"https://media.daily.dev/image/upload/s--3luD3Wei--/f_auto/v1714800008/logos/duckdb","url":"https://daily.dev/sources/duckdb"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/faster-string-aggregations-with-dimension-tables-eljteph2w","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":0},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"sql,data-warehouse,duckdb","timeRequired":"PT15M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"DuckDB","item":"https://daily.dev/sources/duckdb"},{"@type":"ListItem","position":3,"name":"Faster String Aggregations with Dimension Tables"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/faster-string-aggregations-with-dimension-tables-eljteph2w#faq","mainEntity":[{"@type":"Question","name":"How do I speed up GROUP BY queries in DuckDB when grouping on long repeated strings like station or product names?","acceptedAnswer":{"@type":"Answer","text":"Move the strings into a small dimension table with sorted, narrow unsigned integer keys (UTINYINT, USMALLINT, or UINTEGER sized to the distinct count), replace the string column in the fact table with that key, aggregate on the integer key, then join the original strings back only at the end against the smaller result set. This avoids hashing and comparing full strings for every row. daily.dev surfaces practical DuckDB performance techniques like this for engineers tuning large aggregation queries."}},{"@type":"Question","name":"When should I use a DuckDB ENUM type instead of building a manual dimension table for repeated strings?","acceptedAnswer":{"@type":"Answer","text":"Use an ENUM when the set of distinct values is known upfront and rarely changes, since it gives dictionary-encoding benefits with no extra joins. Choose a dimension table instead when new values keep arriving (inserting an unknown ENUM value fails), when you need extra attributes per value, or when the data needs to be exported to Parquet or CSV for other tools. developers weighing ENUM versus dimension tables in DuckDB can track such trade-offs on daily.dev."}},{"@type":"Question","name":"Why does reducing thread count sometimes lower memory use in a high-cardinality DuckDB GROUP BY aggregation?","acceptedAnswer":{"@type":"Answer","text":"DuckDB aggregates each thread's rows into its own thread-local hash table before combining results, so when nearly every group appears in nearly every thread (as with 320 million distinct groups spread across 9.2 billion rows on 8 threads), memory use approaches the thread count times the group count. Setting threads to 1 trades speed for memory in these out-of-memory or heavy-spill cases. teams debugging memory blowups in distributed aggregations can follow this kind of DuckDB internals analysis on daily.dev."}}]} ```