Re: storage size of "bit" data type..

"Decibel!" <decibel(at)decibel(dot)org> writes:

> On Dec 5, 2007, at 7:23 PM, Michael Glaesemann wrote:
>> On Dec 5, 2007, at 14:19 , Alex Mayrhofer wrote:
>>> i'm trying to find out the storage size for bit(n) data. My initial
>>> assumption would be that for any 8 bits, one byte of storage is required.
>>
>> select pg_column_size(B'1') as "1bit",
>> pg_column_size(B'1111') as "4bits",
>> pg_column_size(B'11111111') as "1byte",
>> pg_column_size(B'111111111111') as "12bits",
>> pg_column_size(B'1111111111111111') as "2bytes",
>> pg_column_size(B'11111111111111111') as "17bits",
>> pg_column_size(B'111111111111111111111111') as "3bytes";
>> 1bit | 4bits | 1byte | 12bits | 2bytes | 17bits | 3bytes
>> ------+-------+-------+--------+--------+--------+--------
>> 9 | 9 | 9 | 10 | 10 | 11 | 11
>> (1 row)
>>
>> Looks like there's 8 bytes of overhead as well, probably because a bit
>> string is a varlena type.
>
> Wow, that's screwed up... that's a lot more than varlena overhead:

It needs to store the number of bits present as well. Otherwise it wouldn't be
able to tell apart B'1' and B'01' ... B'00000001'

> select pg_column_size('a'::text), pg_column_size(1::numeric),
> pg_column_size(3111234::numeric);
> pg_column_size | pg_column_size | pg_column_size
> ----------------+----------------+----------------
> 5 | 10 | 12
>
> Apparently it's something related to numeric.

Only in the sense that numeric also has to store some meta data as well like
the weight and display precision.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning