On Mon, Sep 26, 2011 at 10:02 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Mon, Sep 26, 2011 at 5:41 AM, crocket <crockabiscuit@gmail.com> wrote:
>> MySQL already has unsigned INT type, and it has double the range of
>> signed INT type.
>> It's not just the bigger range that UINT type brings.
>> If unsigned INT type exists, I wouldn't have to execute "create domain
>> UINT" in every database.
>>
>> If "INT unsigned" and "SERIAL unsigned" exist, PostgreSQL would bring
>> more convenience to users.
>
> This comes up now and then. The problem is the benefit gained is not
> really worth the pain. In today's 64 bit world, choosing a 64 bit int
> nails the cases where you need the extra range and you have the
> ability to use constraints (if necessary, through a domain) to enforce
> correctness.
>
> On the downside, you have to add a lot of casts, overloads, etc.
> Figuring out the casting rules is non trivial and could lead to
> surprising behaviors...inferring the type of 'unknown' strings is bad
> enough as it is.
>
> TBH, what I'd greatly prefer to see is to have domains be finished up
> so that you don't have to carefully consider their use (for example,
> you can't make arrays out of them). Then an unsigned int could simply
> be:
>
> create domain uint as bigint check (value >= 0);
Even if we did that, there might still be cases where people would
want unsigned integers as a means of reducing storage. 4 extra bytes
may not seem like that much, but if you have billions of rows, it adds
up - not just in terms of actual storage space, but also in terms of
disk and memory bandwidth requirements when you want to do anything
with that data. I have seen some recent data (which is not entirely
conclusive) that suggests that memory bandwidth can be a huge problem
for PostgreSQL performance on large boxes; and I think Greg Smith has
made similar comments in the past (correct me if I'm wrong, Greg).
I think, though, that if we choose to attack that problem in the first
instance by adding support for unsigned integers, we're probably going
to be only nibbling around the edges of the problem. Reducing
alignment padding and adding block-level compression would benefit a
much larger number of workloads. Those are not easy projects, but
unfortunately, due to the constraints of our type system, neither is
this. :-(
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company