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

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