Skip site navigation (1) Skip section navigation (2)

Re: BUG #1257: add support for 1-byte integer and 4-bit integer

From: stig erikson <stigerikson_nospam_(at)yahoo(dot)se>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1257: add support for 1-byte integer and 4-bit integer
Date: 2004-09-18 07:15:36
Message-ID: cignan$1c3m$ (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
Peter Eisentraut wrote:
> PostgreSQL Bugs List wrote:
>>why is this feature important?
>>having in mind the development of datawarehouses with huge amount of
>>data (hundreds of millions, or billions of rows in fact tables) every
>>byte is of importance.
> Yet how many applications could make use of the limited range that a 
> 1-byte integer type would provide?  Compared to the mess this would 
> introduce into the resolution of numeric literals and functions, this 
> seems to be of limited use.  I suggest that you implement this as an 
> extension module for your own use.
> 4-bit integers are out of the question.  The architecture doesn't allow 
> it.

Thanks for your answer, as Tom suggested i will try the "char"-type.

I would like to share some light on this. First you are right, there are 
not many applications that could make use of a 1-byte integer.

In a data warehouse (OLAP), it might be a good idea.
Consider a data warehouse where the time dimension (fact dimensions 
could benefit aswell) contains all kins of data about the time the fact 
record describes, this could include:

   NAME AND TYPE                                TYPICAL VALUES REQUIRE
   dateid int8                                             8 bytes
   dateyearmonthday date NOT NULL,                         8 bytes
   dateyearmonth date NOT NULL,                            8 bytes
   "year" int2 NOT NULL,                        2004       2 bytes
   monthinyear int2 NOT NULL,                   1-12       1 byte
   dayinmonth int2 NOT NULL,                    1-31       1 byte
   dayinyear int2 NOT NULL,                     365        2 bytes
   dayinweek int2 NOT NULL,                     1-7        1 byte
   calendarweeknumberinyear int2 NOT NULL,      1-52       1 byte
   calendarquarter int2 NOT NULL,               1-4        1 byte
   fiscalmonthinyear int2 NOT NULL,             1-12       1 byte
   fiscaldayinmonth int2 NOT NULL,              1-31       1 byte
   fiscaldayinyear int2 NOT NULL,               365        2 bytes
   fiscaldayinweek int2 NOT NULL,               1-7        1 byte
   fiscalcalendarweeknumberinyear int2 NOT NULL, 1-52      1 byte
   fiscalcalendarquarter int2 NOT NULL,         1-4        1 byte

as you can see in this limited example, if int2 is used all along we end 
up with 50 bytes.
if there was an 1-byte integer we could get down to use only 38 bytes.
24% save that is. it is not neglectable when you have many millions of 
rows. data warehouses often have billions rows (10^9) or more.

in the example one could argue that using all the various ints instead 
of just date is stupid, however it fills a function in data warehouses 
(not explained here).

think of the analogy, in your wallet you have bank notes and coins.
However, you dont throw the coins away saying "I'll stick to bank notes, 
  the coins are worthless anyway". Of course we could argue that with a 
few coins you can't buy much, but think of the loss you would make if 
you always paid with a banknote and left the change behind, no big deal 
if you shop a few times, but if you have millions of transaction every 
coin is valuable. The coins fill a function, not always, but in some 
applications they are important. as is the 1-byte int.

if the current architecture does not allow it, i agree, stick with the 
current. but please give it a thought for the future.

by the way, can i enable table compression in some way?
i could not find it in the manual, found only compression for backups 
and EXTENDED column storage, but nothing for integers.

kind regards

In response to


pgsql-bugs by date

Next:From: Peter EisentrautDate: 2004-09-18 14:07:20
Subject: Re: BUG #1257: add support for 1-byte integer and 4-bit integer
Previous:From: Tom LaneDate: 2004-09-18 01:24:53
Subject: Re: BUG #1258: backend memory leak after massive 'CREATE/DROP USER'

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group