High cardinality data can be very difficult to compress, store, and analyze efficiently. This poses problems when you need to get a big picture view of your data. However, as many data scientists will tell you, the information stored in high-cardinality fields is often very valuable. So how do you maintain a balance between managing cardinality and getting the most value out of your data?
This post will cover how we helped a customer manage a high-cardinality column to reduce their costs and compute while getting the insights they needed. The customer had a use case where they were querying their highest-cardinality column over a period of 30 days, leading to an expensive, compute-heavy query. With help from the customer success team at Hydrolix, we were able to reduce the total query time, resulting in faster time to insights. The performance improvements included:
- 55% decrease in query time over a 1-hour time period
- 77% decrease in query time over a 7-day time period
Here are some techniques we use to achieve this performance improvement, increase overall efficiency, and reduce costs:
- Revisiting query structure
- Performing functions at ingest instead of query
- Circuit breakers
- Reduce cardinality of data
- Summary tables
Revisiting Query Structure
When making decisions on how to visualize and query your data, it’s important to first establish your core needs. What sort of situations or problems do you need to use data visualization for? What is not negotiable and what are you willing to compromise on to improve performance and reduce costs?
We evaluated the customer’s dashboards and found that the compute-intensive queries were retrieving the top 500 results of three high-cardinality columns. After discussing their core observability needs, we found that they really only needed the top 50 or 100 results from their data. This simple change to the queries brought immediate performance improvements.
Take the cardinality of columns into account, and use high cardinality fields sparingly. What kind of time ranges do you need to look at to solve your issues? Is there much of a benefit to using the top 500 over top 100? The goal here is to develop an in-depth understanding of your data and how to efficiently maximize its value.
Performing Functions at Ingest Instead of Query Time
Hydrolix is designed to transform data in real time when it is ingested, providing an opportunity to standardize data to improve performance. The customer was using regex functions at query time to pull data from high-cardinality columns. By moving the regex function directly into the transform, all of the parsing could happen at ingestion instead of query time. Rather than breaking the original column into multiple smaller columns at query time, a compute-intensive process, the customer could instead query the smaller columns (which were created at ingest time) directly.
Data transforms can be a great way to improve query efficiency. You can shape the data at ingest time so that it’s ready for fast, efficient queries. This requires a good understanding of how your data is structured and how you’re going to use it. Is there anything that you plan to do or are currently doing in your queries that you could do at ingest time instead?
Circuit Breakers
A circuit breaker stops a query from running if it exceeds a specified limit. In Hydrolix, you can set circuit breakers for limits such as maximum rows, maximum query size, or the maximum time range for a query. See the circuit breaker docs for more information.
Circuit breakers are an essential safety mechanism that every team querying big data should enable. Teams should be able to query extensively to better understand which types of queries are most performant and which return the most useful data. However, without circuit breakers, users can inadvertently make queries that use huge amounts of compute. The customer ended up running queries that consumed all of their available compute resources, impacting their ability to visualize their data. Adding circuit breakers to the customer’s query options solved this issue.
Learn more about how to set up circuit breakers and best practices.
Summary Tables
Summary tables are pretty much what the name implies: a summarization of the data in your table. They allow you to reshape your data to fit your exact needs. Summary tables are essentially a large SQL statement that overlays incoming data at ingest. Just like the transform in your normal table, summary tables work by using SQL logic to shape the incoming data.
This was the first approach we considered for our customer’s cardinality issue. Summary tables often tend to be the best route of attack when handling massive amounts of data (think many terabytes of data per day). Common practices with summary tables include using aggregate functions (especially on high-cardinality columns) like topK, quantiles, count(), and sum(). When testing whether a query is a good fit for a summary table, the best practice is to aim for a 95% reduction in data size. For example, if the slowest performing query shows the top 100 of a high-cardinality column, you can perform the same aggregation in a summary table instead, only storing the top 100. Now instead of searching through millions of rows for those top 100 values, queries can instead pull that column from the summary table. Our rule of thumb is to use summary tables wherever possible and to avoid unnecessary queries to large raw data tables.
The increased performance of summary tables comes with a trade off in granularity of data. The key to creating an effective summary table is to find a balance between the two. We could make a summary table that performs lighting fast queries over a 365-day timespan, but it won’t be as granular. Alternatively, we can create a summary table that gives you just as much granularity as your raw table, but it won’t perform much better. The balance between performance and granularity lies in the cardinality of the columns you are putting in your summary table. Even if your summary tables have less granularity, you can always still query the underlying raw data when needed.
Learn more about adding high-cardinality columns to summary tables.
Next Steps
Interested in learning more about Hydrolix? Try a proof of concept or demo.
