Not so long ago I talked to my colleague and he was definitely against using bit masks because it is hard to understand all the values that are stored in the database. In my opinion it is not always a bad idea to use them, for example to determine the roles of the current user. Otherwise you need to store it in a separate table, which will cause one more JOIN. Can you please tell me if I am wrong? Any other side-effects, advantages/disadvantages of using bit masks?

  • 2
    It may make more sense to have the database create bit masks internally and present the bits as separate columns to you. Your requirements may change. – Simon Richter Jun 15 '16 at 15:16
  • 1
    If you don't use joins you're not using your relational database the way it's intended. – Pieter B Jun 16 '16 at 8:37

I work with an application that uses bitmasks to store user role assignments. It's a pain in the butt. If this makes me biased, guilty as charged.

If you're already using a relational database, it is an anti-pattern that violates most relational theory and all the normalization rules. When you build your own data storage, it may not be such a bad idea.

There is such a thing as too many tables being joined, but relational databases are built to handle this. Many have additional features if performance becomes an issue: indexes, indexed views, etc. Even if the values you're looking up don't change very often, which is an advantage for Bitmask, the over-head of having to manage indexing is pretty easy on the database.

Although database do a good job of aggregating data, they can get sluggish when you start introducing things like complex formulas or Scalar Functions into datasets. You can do the bitwise in your app, but if all you're doing is getting related data (looking up a user's role(s)), you're not taking advantage of what your data storage does best.

My last argument against it would be simplicity for other developers. You have users, roles and assignments. It's a many-to-many relation set (because there's more than one relationship) that is so common, it should be easy to manage. It's just CRUD stuff.

| improve this answer | |
  • 9
    A relational database is about the worst place for a bitmask. Storage costs aren't so bad anymore that a few joins and an extra table should break you. It certainly makes everything harder to reason about. Store the permissions as bits (1/0) in the database in their own table and represent them in code with but flags. Seems pretty appropriate and feasible. Developers get simple flags and dbas have normalized tables. Everyone is happy. – Mike McMahon Jun 15 '16 at 19:04
  • 3
    Agreed, I used to support an application that used bit masks for user roles and privileges in its database. It was a nightmare. Using a 32 bit int, we ran out of bits, so somebody had the great idea to add more bit masks, and then with overlaps, so bit 4 in one column meant bit 8 in this other column, and they got out of sync. Aye aye aye. It was difficult to index because indexes store discrete column values, not the individual bits in them, so you cannot search for rows where some_bit_mask & 12 > 0 without a row-by-row scan. – Brandon Jun 15 '16 at 21:26
  • At the end of the day, a many-to-many user_role_map or user_priv_map table would have sufficed. – Brandon Jun 15 '16 at 21:27
  • @MikeMcMahon, could you please dive deeper in table design and how should I map it in code in order to achieve result you are talking about? – Alex Ovechkin Jun 16 '16 at 0:56
  • 2
    @usr - Never say never. Sure you can use bitmasks, but I wouldn't use them in an application that uses a relational database. There are probably some edge cases when dealing with legacy data or a super need for speed. – JeffO Jun 16 '16 at 21:03

You have already named the relevant pros and cons:

  • Bit fields save space.
  • They store data in the record itself, so you don't need JOINs to find them. (But individual flag fields in the record would do the same.)
  • They are badly readable if you want to work productively with raw SQL output.

Deciding what to do requires more info:

  • Just how scarce is disk space for your use case?
  • Do you actually read user roles so often that the time to JOIN them is a bottleneck?
  • Are you going to read SQL output and make decisions based on that - or is an unreadable data base record immaterial, just like that fact that the machine code of your system is unreadable?

So what you have to do is gather the risk factors and then weight them, to see whether the pros outweigh the cons.

| improve this answer | |
  • Thank you for your answer, totally agree with your thoughts, but in general is this anti-pattern or not? And do you use masks in your projects? – Alex Ovechkin Jun 15 '16 at 7:56
  • 12
    @Alex There is no such thing as a "best practice" that can decide what to do in your case. If you are extremely short on space, using bit fields is best practice. If you want to use SQL output in reports to the CEO, using speaking names is best practice. But you are the only one who knows these circumstances, therefore the community cannot give you an prescription that is always valid. – Kilian Foth Jun 15 '16 at 11:25
  • Taking the space argument as a "gimme". The question of whether to use a bit mask stands or falls on whether it infers any benefit over and above this. – Robbie Dee Jun 15 '16 at 12:26
  • Also do you EVERY need to process the information in the database, or is it always read into an application before using it. – Ian Jun 15 '16 at 13:48
  • 1
    "Are you going to read SQL output and make decisions based on that - or is an unreadable data base record immaterial, just like that fact that the machine code of your system is unreadable?" I guess I can't speak for all developers, but when I'm developing, it's extremely common for me to start selecting data from the DB to understand or check something. So I would argue that usually, the answer to this is, "Yes, someone will." – jpmc26 Jun 15 '16 at 23:08

If you're really, really, really strapped for disk space, then you might consider bitmaps for user permissions. If performance is your worry, then forget about them altogether, because picking them apart will actually be slower. You can't index a bitmapped field meaningfully, resulting in database table scans, which are [almost] always a performance killer.

Unless you're Amazon or Netflix, the amount of data involved in user permissions will be negligible compared to everything else you're holding.

Any serious DBMS can handle that "extra join" without even blinking.

| improve this answer | |
  • 7
    +1: The good relational databases are developed by people who are really, really, really good at what they do. Anyone at the level of needing to wring out the last bit of performance you might get by using bit fields wouldn't need to be asking the question. Model the data, then find the parts that don't perform. – Blrfl Jun 15 '16 at 12:23
  • Having the join will make the application code more complex, so a lot comes down to WHERE the roles are processed. – Ian Jun 15 '16 at 13:50
  • 4
    @Ian having the join doesn't seem more complex than needing to know how to decipher the bitmasked permissions. – Brad Jun 15 '16 at 15:55
  • @Brad, Think of an enum that is a set of flag in C#, with its value stored “as is” in the database, the C# cold cannot get any simpler. If a join is used, then the C# code has to cope with a “1 to many” relationship. – Ian Jun 15 '16 at 17:42
  • I should also add that if you have multiple boolean columns in a table, most databases will figure out how to squash them into as little space as possible and will take care of the bit-twiddling for you. – Blrfl Jun 16 '16 at 19:01

Back when storage was expensive, the boon with bit masks was that they saved space. In the days of big data, this isn't the issue it once was.

Taking the example you cite - having roles stored as a bit mask would be something of a code smell from a database design point of view as it would violate first normal form. In this sense, they're an anti-pattern.

All this being said, it doesn't have to be one or the other. You could store the data as a bit mask and then have a view that can pull the user roles on the fly. You'd also then have the benefit of checking at a glance which users had the same roles.

| improve this answer | |

The only advantage to using bitmasks is if the bit fields' meaning is not static. Relational tables only work well if you know ahead of time what each field is on a record: you have to identify the fields in the CREATE TABLE DDL statement after all.

If the meaning of each bit field is configurable at runtime, or otherwise not known ahead of time, then it might make sense to store booleans as a bit field. Even then, it is possible to define a table with arbitrary fields: field_1, field_2, etc. This gives you a cleaner relational design, although still not ideal. Whether this is preferential to a bit field is largely a matter of opinion, since neither solution is ideal.

If you know what the bits represent during development, then create fields for each bit and give them meaningful names.

Just be careful of the inner platform effect. If you end up defining arbitrary but well-typed fields that is one thing, but if you go too much farther than that you will be reinventing a relational database... inside of a relational database.

| improve this answer | |

I am ambivalent about bitmasks. I find most of their detractors do not understand binary and hexadecimal. For clarity, use good mnemonics.

An advantage not mentioned above is the ability to add new meaning to bit masks without the potentially time-consuming addition of a new column. Our db designers (that preceded me) have them in a table that now gets 5 million new records daily. Adding a new column to represent a new behavior would take a long time, while defining a new bit (we've consumed 33 of 64) requires no table rebuild.

No, bit masks cannot be indexed but building 33 indexes would be ridiculous and would slow insertions to a crawl. Table searches use the dates & record "owners" indexes, hence indexes on this bit mask, if possible, would never be used.

| improve this answer | |
  • It's an interesting case. I suppose you could achieve the same in a kosher and explicit fashion, by defining "spare" columns on the table, and then bringing these into use as needed. You can then at least index these columns selectively, should you choose to do so. – Steve Jul 15 '19 at 17:36

If the goal is just to save some disk space, I think it's a bad idea:

  • look at the cost of the GB today,
  • compare it to the cost of the time of those who write reports and querries and have to figure out what's in the field, and how to address a specific bit, the cost/benefit comparison might end on the wrong side.
  • if you're working with an SQL database, the additional bit access operations required in many querries might also consume more computing time than necessary

However there are some cases, that can jusitfiy the use of bit fields :

  • if your bits represent a complex set of flags that you always handle together as a whole,
  • even more if you need to apply some pattern matching algorithms on these sets,
  • and especially if this data is not amongst the most frequently used selection criteria.
| improve this answer | |

I used to work with a database when bitmask flags were used. The database stored information about call records (CDR) and contained more than 500 millions records in calls table. A call itself had a few attributes like incoming/outgoing, internal/external, etc. So for these attributes we used bitmask flags. In that case it saved space but these flags were actively used in reports so it was really hard to read.

Another example is a project I'm working on right now. I store about billion URLs in PostgreSQL database and I use URL normalization to avoid duplicates and save some space. For example, I don't store http:// or https:// prefix and it saves a few bytes per every URL. Also, I remove trailing slash from the URL. I save information about how URL was normalized in bitmask smallint field. Otherwise I would have to create about 10 boolean columns in my table.

So my point is that sometimes it can be reasonable but, of course should be well documented.

P.S. If you try to save some space by storing values in bit flags please don't forget that some RDBMS (like PostgreSQL) use alignment when storing data in tuples and you should consider column order to make this work.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.