Re: BUG #1257: add support for 1-byte integer and 4-bit integer - Mailing list 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
stig