Reduce Columnar Database Costs by Analyzing Cardinality

Did you know that the key to reducing your cloud storage costs could be hidden in plain sight? By understanding how your data is shaped, you can increase your query performance and also open up possibilities for significant cost savings.

Platforms like Hydrolix internally store and track the number of unique values for each given column in a table. You can use this information to make better use of your cloud storage. This process is known as cardinality analysis, and with just one simple Hydrolix query, you can gain insights that lead to up to 3.75x in additional storage savings.

This post will cover:

• What is data cardinality?
• How cardinality can affect storage
• Finding high-cardinality columns with SQL
• Reducing the storage footprint of high-cardinality columns

What is Data Cardinality?

Data cardinality refers to the uniqueness of data values in a column, and it’s a crucial concept in database management. High-cardinality columns have many unique values, while low-cardinality columns have many repeated values (or just fewer values). The impact of cardinality on performance and storage varies depending on whether the database is row-oriented or column-oriented.

In row-oriented databases, high-cardinality columns require effective indexing to enhance query performance. Indexing accelerates searches and filters by reducing the need to scan each row for matches. Without efficient indexing, operations in high-cardinality columns can be slow.

In column-oriented databases, the cardinality of a column not only directly impacts query performance but also data compression. High-cardinality columns are less compressible, leading to larger storage requirements and potentially slower query speeds. Conversely, low-cardinality columns can be highly compressed, improving storage efficiency and query performance.

For example, the following table column is much more capable of being compressed in column-oriented databases than in row-oriented ones.

This table stores three different colors — red, green, and blue (denoted by the letters R, G and B respectively) — with some repeated values. Red appears twice, green appears three times, while blue appears just once. 

In row-oriented databases, each column is stored next to the other in disk (often in groups called pages). The following diagram shows a representation of these columns.

All columns are next to each other in the same page. Every new column added will be inside each of these six rows. This is great for transactional queries requiring many columns at once, but it’s not ideal for compressing them.

In a column-oriented database system, the same table is stored like this:

Each of the columns is separated, which means that every new column added to the table will be next to other columns, as opposed to being inside existing groups.

You may have noticed a pattern here. In this column-oriented approach, the values for color are all stored next to each other, and these values get repeated. The example above has two consecutive values for red, three for green, and one for blue.

What if instead of having each value, you kept track of the values along with the amount of times each value appears? This not only simplifes the column, but also involves using less space for the same amount of data. This process is called run-length encoding (RLE), and it’s used with many popular column-oriented (and some row-oriented) database systems.

In the above example, the values for red, green and blue are stored as R2G3B1 on storage, using 50% less storage space. A query can read this data in the exact same way as it would read the uncompressed values.

Hydrolix is based on ClickHouse, which follows the columnar storage model, so many popular data compression techniques that ClickHouse uses can be applied to Hydrolix as well.

This table is rather simple and small, but the principles also apply for larger, more complex tables. Let’s see how we can apply the same techniques to real-world use cases and save storage space (and money) in the process.

How Cardinality Can Affect Your Storage

Consider the following example. Say you have a column named clientIP that stores request IPs . This clientIP column will be updated each time a request is received, storing the original IPv4 address from the client it came from.

Let’s imagine you are expecting a lot of traffic for your application on Black Friday. On this day, you may anticipate millions of requests from all around the world, which could lead to a diverse set of IP addresses.

Each time a request is made, the clientIP column is updated with the new IP addresses and a row is added to it. With such a high volume of traffic, the storage requirements for this column can quickly escalate.

Data platforms like Hydrolix are optimized to compress large volumes of data into smaller data footprints and handle high-cardinality data. But having very high cardinality is actually disadvantageous and makes it harder to compress this data.

How Hydrolix Stores Data Internally Using Dictionaries

Hydrolix uses a technique called dictionary encoding to compress column values from certain types of data. This encoding technique consists of storing each unique value of a column along with its position in a data structure that is stored on disk, called a dictionary. For a large range of values, you could easily end up with a big dictionary.

Finding High-Cardinality Columns With SQL

In Hydrolix, you can directly query a table’s metadata to find columns that have the highest cardinality along with their respective dictionary sizes and unique values. Let’s take a look at the query.

SELECT
    current_column_name,
    formatReadableSize(dict_comp) AS dict_compressed_size,
    formatReadableSize(dict_uncomp) AS dict_uncompressed_size,
    formatReadableQuantity(uniq_val) AS uniq_val
FROM
(
    SELECT
        current_column_name,
        sum(dict_compressed_bytes) AS dict_comp,
        sum(dict_uncompressed_bytes) AS dict_uncomp,
        sum(column_unique_values) AS uniq_val
    FROM project.table#.metadata
    WHERE min_timestamp > (now() - toIntervalMinute(5))
    GROUP BY current_column_name
)
ORDER BY dict_comp DESC
LIMIT 50

In Hydrolix, the metadata view is a special Hydrolix view that contains information about each of the columns in the table including:

For example, given a table database.logs, database.logs#.metadata will store things like column names, column types, whether a column is indexed or not (hint: it probably is), and what really concerns us here: column cardinality. The following table shows the compressed and uncompressed dictionary sizes of five columns along with the total number of unique values (cardinality) of each of those columns.

See the Catalog Metadata documentation for more information.

In this example, the cliIp column (client IP) contains 185.73 million unique values. That’s a huge number—greater than the population of Bangladesh, the 8th largest country in the world.

Reducing the Storage Footprint of a High-Cardinality Column

Now let’s get creative. Given that cliIp represents an IP address as a plain string, and that it contains lots of different values, is there a way to represent this column as a data type that takes less space? In these situations, ClickHouse’s SQL functions can provide the answer—specifically IPv4StringToNum(). This function can convert cliIp (an IP encoded as string) to a number.

Storing the IP address as a number instead of a string provides a huge storage improvement. An IPv4 IP is in the following format: A.B.C.D, where A, B, C, and D are each a byte (0-255). This takes up to 15 bytes as a string (four 1-, 2- or 3-digit numbers, plus three dots).

On the other hand, the IPv4StringToNum() function in ClickHouse returns a UInt32 number, which always takes up 4 bytes regardless of the specific IP address. This saves an  estimated 1.75x to 3.75x on storage for this column depending on the original IP addresses.

Here’s an example of the cliIP column in integer form:

Note that if cliIp (or any IP encoded as string) could contain default or invalid values, you should use IPv4StringToNumOrDefault() instead.

By transforming the data shape for the column, the customer saved on storage and we also opened lots of doors for potential query optimizations when querying this column. By using less space, our queries will end up processing less data (for both unindexed and indexed reads), resulting in efficient query patterns.

Not every high-cardinality column can easily be optimized (and sometimes they can’t be optimized at all), but it’s still a good idea to understand which columns have high cardinality and to look for optimizations where possible.

Conclusion

The cardinality of a column can significantly impact your storage requirements and costs. Columns with high cardinality typically require more storage space because they store a wide variety of unique values. On the other hand, columns with many repeated values can be optimized to use less storage space by employing techniques such as data compression or changing the data type to one that is more efficient.

By running simple Hydrolix queries, you can get insights about the shape of your data and potentially speed up future queries and save cloud space (and money!) in the process.

Next Steps

If you’re not using Hydrolix yet and would like to learn more, contact Hydrolix about a proof of concept or demo.