Red Flags of High Cardinality in Databases

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.

Overview

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.

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.

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 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.

The way that we organize data in the database can also affect cardinality. The way data is organized changes depending on the database,

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!