Re: smallserial / serial2

Hey Tom,

 

I'm sure there are plenty of useful tables with <= 32k rows in them? I have
a table for customers that uses a smallint (since the customer id is
referenced all over the place)- due to the nature of our product, we're
never going to have more than 32k customers, but I still want the benefit of
the sequence.

 

And since serial4 and serial8 are simply pseudo-types- effectively there for
convenience, I'd argue that it should simply be there for completeness- just
because it may be less used, doesn't mean it shouldn't be convenient?

 

Also, in another case, I'm using it in a small table used to constrain a
bigger table- eg:

 

create table stuff (

       id serial2 unique

);

 

create table data (

       id serial8 unique,

       stuff smallint not null,

       foreign key(stuff) references stuff(id) on update cascade on delete
restrict

);

 

Where our "data" table has ~700 million rows right now.

 

And yes- I guess there's nothing to stop me from using a smallint in the
data table (thus getting the size savings), and reference a int in the stuff
table, but it seems like bad form to me to have a foreign key constraint
between two different types.

 

Mike

 

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: Thursday, April 21, 2011 10:26 AM
To: Mike Pultz
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] smallserial / serial2 

 

"Mike Pultz" < <mailto:mike@mikepultz.com> mike@mikepultz.com> writes:

> I use tables all the time that have sequences on smallint's; I'd like 

> to simplify my create files by not having to create the sequence 

> first, but I also don't want to give up those 2 bytes per column!

 

A sequence that can only go to 32K doesn't seem all that generally useful
...

 

Are you certain that you're really saving anything?  More likely than not,
the "saved" 2 bytes are going to disappear into alignment padding of a later
column or of the whole tuple.  Even if it really does help for your case,
that's another reason to doubt that it's generally useful.

 

                                                regards, tom lane