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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
