Bitfields are a trap

I recently came across this video on Youtube and while the author makes some convincing arguments for using bitfields. I’d like to share my experiences having worked with them in a few applications. A long time ago, I also thought that bitfields were neat. However, in the intervening years, my opinion has shifted. While the prospect of simplifying your schema and being able to pack multiple values into a single column through seems attractive; the complexity cost isn’t worth it.

What are bitfields?

Bitfields are generally stored in integer columns. Inside the integer you use each bit of the field to store status/flag values. For example, within a 32bit integer you can store 31 values. You interact with the bits using bitwise operators to ‘mask’ each bit. For example you could store multiple statuses for an article using 4 bits:

When you filter or update records you use bitwise operators to filter and mutate values. If you’d like to learn more about bitfields either my previous article or the youtube video I linked are great resources.

Harder to evolve over time

One of the attractive qualities of bitfields is that they simplify schema management as you don’t need to change your schema as often. Instead of having to add and remove columns, you can use new bits as you add flags. Eventually, you’ll stop using bits, or need to reuse a bit. Finding and clearing unused bits requires a backfill operation across every record in a table to clear out stale flags. If you don’t clean up unused flags, you’re faced with increased cognitive load from junk data. Either way, your quick win turns into long term complexity.

More complex to operate than boolean flags

Working with bitfields requires using the bit mask operators |, &, ^, and ~. While all of these operators are well documented and consistently used in most programming languages, they are more complex to read, understand, and operate over the alternatives. As a quick example, which bits are set in 384 that are not set in 192? Even if you can figure that out, what do those bits mean in any given context? I found that answering these questions required memorizing lookup tables, or having to cross reference the bit values each time I needed to interact with a bitfield. Let’s look at some code and SQL to see the additional complexity in action.

While this example is reasonably simple, it is more complex than manipulating boolean attributes:

Turning on fields doesn’t seem to rough, but turning fields off is harder:

The additional complexity required for bitfields presents itself more in query logic. Lets filter subscriptions where one of two flags is enabled. Compare using bitfields:

And now with using boolean attributes:

Queries where you want to filter out records that have flags disabled are also more complex:

Compared with:

While we’ve only looked at basic SQL queries, the complexity in manipulating bits often spills out into application logic as your ORM might not provide high-level abstractions for these operations.

Difficult to query efficiently

As you get more flags in your bitfield, you’re going to want to use them for filtering results. This will work great for a while, however you cannot index individual bits in the field. The best you can do is index the flags field, which won’t always improve query performance. You’re quite limited in the options you have for making bitfields efficient. You’ll always need to read the full index in order to match any row. With boolean flags you can index your ‘hot’ column tuples. You can make as many indexes as you have storage for and make all your queries fast.

Training is harder

Finally, most folks that join your team in the future will be familiar with boolean values. The same cannot be said of bitfield and bit masking. You’ll need to teach each and every employee the ropes about how bitfields work, what the landmines are and how to use the operators in the DSL your framework or application exposes work costing your team time.

While 15 years ago, I thought bitfields were pretty neat, and clearly people still do. I think bitfields are an attractive trap and hope that you avoid them. Having worked with bitfields for many years, they are a trap. Instead, I recommend using discrete columns for boolean flags and string enums for enums. While they consume a more in storage space, they make working with data in adhoc query tools, application debuggers and tests much easier as you don’t have to memorize as much. Discrete columns are also easier to maintain, evolve and optimize as necessary.