storage size of "bit" data type..

Hi,

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.

Is this assumption correct? I didn't find that information in the online
docs.

thanks,

Alex

On Dec 5, 2007, at 14:19 , Alex Mayrhofer wrote:

> Hi,
>
> 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.

Michael Glaesemann
grzm seespotcode net

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:

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.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

"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

> It needs to store the number of bits present as well

Couldn't that be reduced to 1 byte that'd say how many bits count in the
last byte?

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

Is it really necessary to store display precision when it can be taken from
the table column definition?

On Dec 6, 2007, at 5:19 PM, Vyacheslav Kalinin wrote:
> > It needs to store the number of bits present as well
>
> Couldn't that be reduced to 1 byte that'd say how many bits count
> in the last byte?
>
> > Only in the sense that numeric also has to store some meta data
> as well like
> the weight and display precision.
>
> Is it really necessary to store display precision when it can be
> taken from the table column definition?

Two problems...

1) CREATE TABLE n(n numeric);

2) The knowledge of extra type information (ie: the numbers in char()
or numeric()) don't extend deeply enough into the code. This is part
of why char() uses the exact same storage mechanism as varchar().
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

On Dec 6, 2007, at 5:10 PM, Gregory Stark wrote:
> 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'
...
> Only in the sense that numeric also has to store some meta data as
> well like
> the weight and display precision.

Hrm... perhaps that's another worthwhile target for the varvarlena
technique...
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Added to TODO:

* Reduce BIT data type overhead using short varlena headers

http://archives.postgresql.org/pgsql-general/2007-12/msg00273.php

---------------------------------------------------------------------------

Decibel! wrote:
> 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:
>
> 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.
> --
> Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
> Give your computer some brain candy! www.distributed.net Team #1828
>
>

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +