Red Flags of High Cardinality in Databases

By Katy Farmer / Product, Use Cases, Developer
Sep 03, 2019

High cardinality describes databases with distinct values. For instance, if every line item had a unique ID number, description, email address, etc. A column with many repeated values would be described as having low cardinality. Not everyone calculates cardinality in the same way, so it’s important to analyze your methodology before implementing.

Let’s face facts: cardinality is not an easy concept. There’s a reason we see many different definitions for it across the world wide web — just like bursting into song in public, context is important. Not everyone wants to be in a musical, and not everyone computes cardinality in the same way. Regardless of how difficult it is to understand, some of us still have to work with and around cardinality, especially if, like me, you work in the land of databases. Let’s untangle the basics of cardinality.

What is cardinality?

On the most basic level, cardinality is the number of unique sets of data in a database. For example, I have two dogs — Bear and Freddie — and at any given time, they are doing one of three things: sleeping, barking, or chewing.

In Figure 1, we have a list of dogs and their associated status. In this case, there are six unique combinations in my pet data: 2 dogs each associated with 3 statuses. The cardinality of this data set is 6 (2 dogs x 3 statuses).

When we put data into a database, we create relationships between different aspects of data. With a cardinality of 6, my dogs and their activities have pretty straightforward relationships. Let’s add a little more context. It would look something like this:

We still have two dogs and three possible statuses, but we have 5 rooms in the house.

2 dogs x 3 statuses x 5 locations = 30

This is an overestimation of the cardinality because each dog isn’t associated with each status and the math to be more precise is a little (a lot?) more complicated, but it’s a close estimation for our small example.

To put it in the most straightforward terms possible, cardinality meaning in terms of databases really comes down to two things. Data cardinality is the one that is most relative to query performance. As stated, this is an examination of how many unique values are present in a column.

More importantly, we can see how adding more unique attributes increases the total number of unique combinations. If I went by the shelter and adopted another dog today, the cardinality would jump to 45 (3 dogs x 3 statuses x 5 locations).

The unlisted status: frolicking

Now imagine that instead of dogs, we’re tracking thousands of satellites all around the world, each sending back a status, location, sensor data, and a timestamp. We could easily hit millions or even a billion cardinality.

Another example of this would take the form of a credit card company with two tables. The first shows a person who has a credit card, and the second shows the card individually. If a person can only have one credit card, obviously this would be a standard one-to-one relationship. If that person is allowed to sign up for multiple cards, it would be a one-to-many relationship because they would be connected to many different entries on the other table.

Do I need to worry about cardinality?

Most of the time, we don’t have to compute cardinality ourselves — and that’s a good thing! There are lots of articles about how cardinality is computed if you’re into that sort of math (it’s called set theory!), and those calculations are built into how databases work. We don’t need to spend time calculating cardinality, but we do need to be aware of the relationships we build between data because eventually, cardinality can affect the performance and stability of our database.

The more complex our data is, the more expensive it is to write, store, and retrieve it from our database. There are two easy steps to find out if the cardinality is an issue in your database:

1. Find out what is considered high cardinality for your database. Go to the community forums and docs!
2. Use your database's tools to find out the cardinality of your data (here's an example of how to find cardinality in InfluxDB)

What is the cardinality of a set?

The most widely used and accepted data cardinality definition involves how many values are in a set. Within the larger context of databases, this refers to the total number of unique values in a table column as compared to the number of rows in the same table. Note that for the purposes of this discussion, repeated values are not something to concern yourself with.

It’s equally important to understand that cardinality database status is never really expressed as a number — it’s not like you’re looking at a value on a scale of 1 to 10 or anything like that. To keep things as straightforward as possible, people simply talk about “low” or “high” cardinality. Low cardinality refers to a database that has a lot of repeated values like status flags, Boolean values, or gender. In contrast, high cardinality refers to a database that has a large number of distinct values such as ID numbers, user names or email addresses.

All of this is important to know, as cardinality ultimately influences the query execution plan of the database. Different plans may be used to try to unlock the best performance depending on whether high or low cardinality is present.

How to find the cardinality of a set

For as complicated as the topic of cardinality is, thankfully the process of how to find cardinality really couldn’t be more straightforward. It’s also one that you can repeat for any finite set of elements that you’re working with.

All you need to do is count the total number of values in the set and identify this as your prime, cardinal number. Then, you can use data cardinality and other processes to further define the relationships between those values in the set — but that’s largely a different matter altogether.

Note that the order of values that appear in the set does not impact the cardinality in any way. They can be arranged in literally any order and it wouldn’t impact the cardinality of the set at all. Likewise, it’s important to understand that two different sets may have identical cardinality but that doesn’t mean they’re equal. They can have the same number of values and still be different if they don’t have identical values present between them. It all depends heavily on the databases in question and the information that you’re currently working with.

What do I do if I have high cardinality?

It’s worth saying that even if you have high cardinality, you might not need to do anything. Having high cardinality data isn’t a bad thing, and knowing that our data is complex can help us find issues specifically tied to this. If you have performance or stability issues in your database, then it’s worth trying to lower the cardinality to fix those problems.

The first question you can answer is: do you need every unique value that you’re storing? For example, you might be able to insert data every minute instead of every 5 seconds without losing the patterns in your data. Another option is to expire data after a specified window of time to keep the dataset smaller.

If neither of those is an option, and your data is always going to be complex, make sure you’re using a database that is made for high cardinality data.

Summary

We’ve been talking about cardinality generally, but there’s one more factor to think about: the way data is organized in the database can affect cardinality. The problem is that the way data is organized changes depending on the database, which makes it hard to cover all of the ways it can help.

Hopefully, this explanation is enough to get you started learning about cardinality. Yes, it’s complicated, but it’s not unknowable. We don’t have to be database architects to understand the concept or why it matters. Cardinality is a way to measure the complexity of our data so that we can better understand the relationships between different aspects of data. This helps us build smarter relationships and design more stable systems. Go out into the internet and read more about cardinality!