19

I've been learning about the bit string data type lately, and I am quite curious about:

  1. At the bottom of this doc page there is the sentence:

    ... plus 5 or 8 bytes overhead depending on the length of the string

  2. How are bit strings handled in other languages such as PHP, Java, C#, C++, etc., through drivers like Npgsql, ODBC, etc.

For question #1, using smallint or bigint will be much more storage efficient, and would perhaps offer a performance gain since integers are supported everywhere. Most programming languages handle bit operations on integers with ease. If that is the case, what is the point of introducing the bit-string data type? Is it Only for cases that need a large amount of bit masks? Bit field indexing maybe? I'm more curious about how bit field indexing is done in PostgreSQL.

For #2, I'm confused, more than curious. For example, what if I store week day bit masks in a bit(7) field, one bit for a day, with lowest bit representing Monday. Then I query for the value in PHP and C++. What will I get? The documentation says I'll have a bit string, however a bit string is not something I can use directly - as with integers. Then in this case, should I give up on bit field?

Can anyone elaborate why and when I should use bit or bit varying?

Improve this question
  • You may be interested in this related answer on SO. – Erwin Brandstetter Sep 28 '12 at 12:51
  • 2
    Erwin's answer on SO is great (and if you don't mind copying it over @Erwin, it would be useful to have here), but I'd like to add my own caution: in most cases you wouldn't contemplate storing information in bit strings on an RDBMS - using separate boolean columns in the normal solution regardless of storage 'efficiency'. – Jack Douglas Sep 28 '12 at 13:39
  • @JackDouglas: I wouldn't mind copying my answer. I wonder, though: is duplicating an answer across SE sites a good idea? – Erwin Brandstetter Sep 28 '12 at 14:00
  • @Erwin I don't see why not - there is some overlap between the sites and they are both supposed to stand alone (so for instance we wouldn't - and anyway couldn't - close a question here as a duplicate if there was an identical question on SO). Our focus is more on 'expert' issues, but IMO your answer fits that category as it stands :) – Jack Douglas Sep 28 '12 at 14:25
  • @JackDouglas: Well, makes sense. And how could I possibly disagree after the praise you slipped in, anyway? ;) – Erwin Brandstetter Sep 28 '12 at 14:32
19

If you only have a few variables I would consider keeping separate boolean columns.

  • Indexing is easy. In particular, indexes on expressions are easy.
  • Conditions for queries and partial indexing are easy to write and read and meaningful.
  • A boolean column occupies 1 byte. For only a few variables this occupies the least space.
  • Unlike the other options boolean columns allow NULL values for individual bits if you should need that. You can always define columns NOT NULL if you don't.

Optimizing storage

If you have more than a hand full variables but less than 33, an integer column may serve you best. (Or a bigint for up to 64 variables.)

  • Occupies 4 bytes on disk.
  • Very fast indexing for exact matches (= operator).
  • Handling individual values may be slower / less convenient than with bit string or boolean.

With even more variables, or if you want to manipulate the values a lot, or if you don't have huge tables and disk space / RAM is no issue, or if you are not sure what to pick, I would consider bit(n) or bit varying(n).

Examples

For just 3 bits of information, individual boolean columns get by with 3 bytes, an integer needs 4 bytes and a bit string 6 bytes (5 + 1).

For 32 bits of information, an integer still needs 4 bytes, a bit string occupies 9 bytes for the same (5 + 4) and boolean columns occupy 32 bytes.

Further reading

Improve this answer
  • Yes, I agree with you. Currently, I'm using samllint to store weekdays' bit mask. It suited the case, storage efficiency / performance wide. However, if I would have some more indexing/filtering on bit masks, it'll fail, due to low performance. – Jackey Cheung Sep 29 '12 at 4:19
3

All PostgreSQL types are useful for some things and less useful for others. In general, you get more out of worrying about functionality first and performance later. PostgreSQL has a large number of functions for manipulating various kinds of data types and these are no exception.

I would expect on the application layer, unless your db driver handles it through some sort of type conversion, you'd get a string representation and have to handle this. So it may or may not be useful in that capacity.

Where it is likely useful is when you want to select records based on bitwise operations, such as a bitwise or or a bitwise and, or otherwise manipulate the data in SQL queries. Unless you are doing this, many of the more esoteric features of PostgreSQL are less helpful.

Note also for longer strings of binary information there is a large object interface which allows you to do streaming etc. and a bytea interface which allows a more compact string representation.

tl; dr: If you need it you will know it. Otherwise file it away in the "reserved for future use" section of your mind.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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