Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

If there are 20 bit columns (non-null boolean), is it better to explicitly hold them in an integer type (int32)? Or, will the underlying database merge them in a single integer?

I'm using PostgreSQL, though. Generally, is using bit-mask in SQL databases kind of anti-pattern?

Backgrounds: The usage of these bit columns are left to be defined by the end user, along with a few of other int, float, double, varchar columns, so as to make the table a little more extensible. I'm also not going to use one-to-many custom property table in practice. Add an integer column is cheap, but if the underlying database could store several bit columns in one int32 field, I don't have to explicit merge them myself.

share|improve this question

3 Answers 3

Follow the KISS principle and don't do it

While it seems to save a bit (hee hee) on storage you're introducing a lot of overhead processing and mental hoops for developers to jump through - "What bit mask should I apply to merge this property and that property?"

Just have a bunch of columns with appropriate types. You'll have more flexibility in the long run when someone wants a nullable boolean.

It is possible that some database implementations will spot this kind of column arrangement and automatically optimise it in their native storage format, but don't count on it.

share|improve this answer
1  
+1: Storage drops in price rapidly. Time spent optimizing storage (especially at the bit level) is totally wasted. Focus on business value and usability. Next quarter, storage prices will fall yet again. –  S.Lott May 6 '11 at 11:42

With maintainability in mind I would use a database representation that closely follows the concept handled in your application code. I.e. if the code works with a bitmask, use an integer column. If the code uses a set of booleans, use boolean columns.

That said: you cannot reply on the database to make this kind of optimisation for you. So if performance is an issue, or if extensibility is an issue (adding more booleans later without having to add columns) use an integer or long integer column. (And have the DAO layer map the booleans to bits and back if needed.)

share|improve this answer

Let the database handle memory management. I'm fairly sure some sort of optimization is done regardless of the fact that such columns are always presented as single bits. It seems only logical that if there are 1 to 32 non-null boolean columns, it gets assigned to a single integer field. Anything beyond gets stored in another 32 bit field, and so forth and so on. Obviously you don't see this field, and the columns get treated as individual entities but in reality it gets stored this way.

Most decent databases optimize queries as well. Even if you write a query pulling from multiple tables and you left join a larger table (less efficient than if you left joined a smaller table), prior to launching, it gets reinterpreted to a query which achieves the same results but can be executed much quicker.

Query results are often cached so that repeating the same query often undergoes a brief check for changes made to the table and if not, the same results are sent back.

In other words, there are all sorts of optimizations that databases do that we're not aware of. In a way, that's a big part of the work of a database. So my advice to you is to focus on optimizing your program rather than the database.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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