Total size of multiple Bit(1) fields in PostgreSQL

Question

What are the size implications of storing many booleans in one Postgres table? I'd like to be able to filter on these fields without doing bit manipulation, so I want them to be separate fields.

I found a related question about SQL Server, and the answer was that 8 bit fields indeed only take 1 byte of space (nullability aside). However, the PostgreSQL documentation says:

Writing bit without a length is equivalent to bit(1)

and:

A bit string value requires 1 byte for each group of 8 bits, plus 5 or 8 bytes overhead depending on the length of the string

Does this mean a table with 8 separate BIT columns will take up 40 or more bytes per row? Or will it also only take a single byte for all 8?

CREATE TABLE SomeBits (
    b1 BIT,
    b2 BIT,
    b3 BIT,
    b4 BIT,
    b5 BIT,
    b6 BIT,
    b7 BIT,
    b8 BIT
)

A bit is not a boolean - what is it exactly you are trying to store? A bit or a boolean? — user1822
– user1822, Commented Sep 7, 2022 at 19:44
@a_horse_with_no_name A bit(1) is conceptually equivalent to a Boolean. They are isomorphic, etc. Why do you say they're not? — JounceCracklePop
– JounceCracklePop, Commented Sep 7, 2022 at 19:44
@a_horse_with_no_name You're saying they are distinct data types in Postgres; that makes sense. Thank you for that comment -- I was not aware of the BOOLEAN data type. — JounceCracklePop
– JounceCracklePop, Commented Sep 7, 2022 at 19:54
In SQL those are two different things. The result of 6 > 5 is a boolean expression, not a "bit" — user1822
– user1822, Commented Sep 7, 2022 at 19:59

JounceCracklePop · Accepted Answer · 2022-09-07 19:53:49Z

Using the technique from this answer on StackOverflow, I created a table with 8 BIT(1) columns and checked its size. It was indeed 48 bytes.

In a comment, a_horse_with_no_name pointed out that PostgreSQL has explicit support for the BOOLEAN data type, which is better for storing booleans than BIT(1). A table with 8 BOOLEANS appears to take 8 bytes per row, which is a little better at least.

Testing table sizes:

CREATE TABLE SomeBools (
    b1 BOOLEAN,
    b2 BOOLEAN,
    b3 BOOLEAN,
    b4 BOOLEAN,
    b5 BOOLEAN,
    b6 BOOLEAN,
    b7 BOOLEAN,
    b8 BOOLEAN
);

INSERT INTO SomeBools(b1, b2, b3, b4, b5, b6, b7, b8)
VALUES (TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, TRUE);

SELECT pg_column_size(SomeBools) - 24 from SomeBools;

8

CREATE TABLE SomeBits (
    b1 BIT,
    b2 BIT,
    b3 BIT,
    b4 BIT,
    b5 BIT,
    b6 BIT,
    b7 BIT,
    b8 BIT
);

INSERT INTO SomeBits(b1, b2, b3, b4, b5, b6, b7, b8)
VALUES (B'0', B'0', B'1', B'1', B'0', B'1', B'0', B'1');

SELECT pg_column_size(SomeBits) - 24 from SomeBits;

48

Another option is to use a single column with bit(8) which is slightly smaller than 8 booleans. But it might be more complicated to work with e.g. you can just write where b4 but you need to use some bit operators to extract the one bit you are after. — user1822
– user1822, Commented Sep 7, 2022 at 19:59

Stack Exchange Network

Total size of multiple Bit(1) fields in PostgreSQL

1 Answer 1

Your Answer

Linked

Hot Network Questions

Total size of multiple Bit(1) fields in PostgreSQL

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions