We throw around the term “cardinality” frequently in database discussions, but we rarely start from the basics and explain why it matters when it comes to dealing with massive volumes of data. Cardinality is the number of things in a set, and a set is a collection of unique elements. When we talk about cardinality in databases, we’re referring to the number of unique elements in a column. When we say “high cardinality,” we’re using shorthand for “high cardinality column.” And when we work with big data, we need to use unique strategies to make that data meaningful.
This post will start by exploring the challenge and cost of extracting insights from high-cardinality data, then cover what we’ve found is the best solution: transforming columns that are high cardinality to medium cardinality. With this approach, you don’t lose any of the fidelity of the data, your data is more manageable to work with, and best of all, you’ll get more useful insights.
The Challenge of High-Cardinality Data
High cardinality columns create challenges from three perspectives:
- Query performance
- Storage compression
- Practical analysis
Understanding these impacts is key to working effectively with big data and managing cardinality.
Query Performance
First, let’s look at query performance.
High-cardinality columns aren’t inherently problematic if you’re not analyzing them.
With Hydrolix, which uses columnar storage, if you don’t include high-cardinality columns in your query, there’s minimal query impact. The query will only grab specific byte ranges from specific partitions, and it won’t have to scan high-cardinality columns to retrieve that data. High cardinality does result in more partitions being created in storage, but it’s not a problem from a query perspective as long as the high-cardinality columns aren’t being used in analytics. At the very least, columnar storage allows you to make targeted queries that avoid performance issues due to high cardinality.
The real problem emerges when you want to analyze high-cardinality columns using certain query types. Let’s take a look at a specific example: GROUP BY queries. When you perform a GROUP BY query, you’re creating a hash table. For each row, every time you see a new value, you must store it in memory, along with an in-progress summary of what you’ve seen so far (“Aggregate Intermediate State”). Let’s say you’re getting the average price grouped by name. The first row comes in with the name “Fed”—that’s a new value, so you create an entry for “Fed” in your hash table, along with a sum of the prices you’ve seen and a count of how many rows you’ve seen. Two more rows come in with “Fed,” so you’re just updating the summary for that existing entry. The average number doesn’t change much in terms of memory space, and you already have “Fed” stored.
The problem occurs when you have Fed1, Fed2, Fed3… all the way up to a billion different names. Now you have an entry for every single name in memory, plus whatever calculations you’re performing. Hash tables must be performed in memory to be fast. While disk spill capability exists and can be used occasionally, it makes queries significantly slower. This is why dashboards trying to compute averages on high-cardinality columns face performance problems—the hash tables blow up the memory on any query peer. (There are workarounds, like spilling the aggregation to disk, and these certainly help!)
The problem isn’t just high cardinality alone. If you have a very high-cardinality string but it’s only one byte, it’s not a major problem because it doesn’t take up much memory. The real problems are high cardinality combined with long strings. The absolute worst cases are binary strings. These could be serialized images or videos encoded as binary strings, where every row is unique and the string itself can be a megabyte or more. At Hydrolix, the processing engine uses three different tiers of hash tables. When you exceed certain cardinality thresholds, you move between small, medium, and large hash table types, and switching between hash table types also incurs a performance penalty.
Practical Analysis
However, there’s a more fundamental issue with high-cardinality columns—they’re often impossible to analyze in a meaningful way. . Think about what happens at both extremes. A global average—like the average response time across your entire website—is too broad to surface anything actionable. Regional anomalies get buried when the majority of data looks normal. That’s too low cardinality for the question you’re asking.
But go too far in the other direction and you hit a different problem. When you’re grouping by something like request_id or session_token, you’re now looking at averages of one or two data points. The average of three requests isn’t a pattern—it’s just… those three requests. Your analysis stops being about trends and starts being about hunting for a specific needle in a haystack. And that needle isn’t generalizable—it’s not something you can put on a dashboard and expect to stay relevant tomorrow.
This is the sweet spot problem. Medium-cardinality columns—country instead of global, shopping cart traffic instead of all website traffic—tend to produce the most useful analysis. You get enough data points per group to identify real patterns, but enough groups to surface meaningful differences. You’re comparing apples to apples, not staring at a single apple wondering if it’s normal.
Storage
From the storage perspective, high cardinality impacts compression. While we can’t discuss proprietary Hydrolix compression schemes here, one approach worth understanding is dictionary encoding. Every unique value has one entry in the dictionary. Imagine you have a million rows and they all have the name “Fed.” Instead of storing “FED” a million times, you create one dictionary where “FED” is the value and the key is, for example, the number 1. Instead of using three bytes for “FED” a million times, you store one byte (the number 1) a million times. On top of that, you can apply run-length encoding: now you just have the number 1, comma, one million. With this approach, you’ve defined a million rows with just two bytes. Dictionary encoding is used to store compressed Hydrolix partitions. As soon as you have really high cardinality, those dictionaries don’t work anymore. You have to store the raw values instead, which means worse compression rates with very high-cardinality columns. You still get the cost-effectiveness of object storage, but not the benefits of additional compression.
Solving the Problem of Cardinality with Domain Knowledge and SQL Transforms
The secret to handling high-cardinality columns isn’t better hardware or smarter compression. It’s knowing what actually matters in your data.
Request paths in CDN logs are a good example. A raw URL might look like /assets/campaigns/2026/spring-sale/hero-banner-v3-final-FINAL-1770154131.pdf—highly unique, impossible to compress well, and honestly not that useful for most analysis. But if you know that what you actually care about is the file extension, the problem changes dramatically. Suddenly instead of dealing with millions of unique paths, you’re dealing with a small handful of extensions such as: .pdf, .mp4, .m3u8, and .js.
That’s the power of domain knowledge. It lets you extract the signal and discard the noise before it ever hits storage.
This isn’t downsampling or lowering the granularity of your data. Instead, it’s about creating categories and organizing your data to have medium cardinality while providing high-quality insights. This is really only possible when you have an ETL process like Hydrolix does, since this transformation needs to happen before writing data to storage.
This is much more useful from the user’s perspective because a medium-cardinality column is ideal for analysis. You can use SQL transforms to create columns with lower cardinality and build summary tables (that display aggregated metrics) based on these columns. The dashboards can show generalized analysis based on summary tables. Once you’ve narrowed an issue or a possible insight to a few rows, that’s when you need access to the high-cardinality columns. When there are far fewer rows and you’ve honed in on a real problem or insight, you might need data from high-cardinality columns like requestId to correlate with other systems.
Hydrolix excels at “needle in a haystack” queries, in part because all columns are indexed. By narrowing an issue down to a small amount of data, you can use extensive query filtering to ensure that queries on high-cardinality columns only scan and retrieve a small amount of data.
With this approach, you get the best of both worlds. Use medium-cardinality columns for the majority of your analytics, not just because they are more performant but because they provide more helpful insights. Then, when you have enough information to make a targeted query, you can retrieve a narrow-byte range of data from high-cardinality columns in storage.
To get the most out of this approach, follow these steps:
- Use SQL transforms to make low- and medium-cardinality columns that have the high-level insights you need. An example might be part of a URL that indicates the request was made in an e-commerce site shopping cart.
- Create summary tables based on these columns. Continuing the example, aggregations might include average request time, total requests, and total errors in the shopping cart.
- Use dashboards for generalized analytics on summary tables. They will give you the majority of the information you need. Because they are pulling data from summary tables, they will be performant both in terms of latency and compute.
- When there’s an issue, you can use data links in the dashboard to go into the underlying raw table where high-cardinality columns become crucial for detailed investigation.
Next Steps
Interested in learning more about Hydrolix?
- Check out the platform page.
- Dive into the documentation.
- Request a demo.
