Is there any plan to add unsigned integer types?
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.
On mån, 2011-09-26 at 19:41 +0900, crocket 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.
I believe there have been many discussions about this in the past,
outlining the various issues that would come with this project. A first
step would be to start implementing this in user space and see how much
breaks.
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);
merlin
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
On Mon, Sep 26, 2011 at 9:21 AM, Robert Haas <robertmhaas@gmail.com> wrote:
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. :-(
right -- exactly. most 'savings' in this vein are nothing but due to
padding and other factors such that (at least today) there is no
disadvantage to going to 64 bit in range constrained cases. also, I'd
submit history has been unkind to hardware dependent optimization
strategies in userland -- the engine should be dealing with this
problem. better to define your data the proper way and let the
assembly instruction counting gurus in -hackers worry about it :-).
compression is an interesting topic: the guys over at tokudb are
making some wild claims...i'm curious if they are real, and what the
real tradeoffs are.
merlin
compression is an interesting topic: the guys over at tokudb are
making some wild claims...i'm curious if they are real, and what the
real tradeoffs are.
I don't know how much of the performance they claim comes from
compression and how much from the different indexing technique they
use (see the my post here, where nobody answered...
http://archives.postgresql.org/pgsql-general/2011-09/msg00615.php
)