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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, April 21, 2011 10:26 AM
To: Mike Pultz
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] smallserial / serial2

"Mike Pultz" < <mailto:mike(at)mikepultz(dot)com> mike(at)mikepultz(dot)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