storage size of "bit" data type..

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@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

"Decibel!" <decibel@decibel.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@decibel.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@decibel.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@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

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