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" <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