Re: SERIAL datatype

On Mon, 2008-08-25 at 11:02 -0600, Scott Marlowe wrote:
> Well, of course a 64 bit int is gonna be bigger than a 32 bit, but
> with alignment issues and on 64 bit hardware, I'm guessing the
> difference isn't exactly twice as slow / twice as much storage.  And
> it's way faster than a GUID which was what I think started this
> thread.

I took a slice of data from our dev box and generated a table using
integers and bigints.  For reference, the schema is:
bigint table:
 Type   | Modifiers 
--------+-----------
bigint  | 
date    | 
bigint  | 
bigint  | 
bigint  | 
bigint  | 
bigint  | 
date    | 
date    | 
bytea   | 
integer | 
integer | 
numeric | 
numeric | 
numeric | 
integer | 
integer | 
integer | 
integer | 
integer | 
integer | 
integer | 
bytea   | 

int table:
 Type   | Modifiers 
--------+-----------
bigint  | 
date    | 
integer | 
integer | 
integer | 
integer | 
integer | 
date    | 
date    | 
bytea   | 
integer | 
integer | 
numeric | 
numeric | 
numeric | 
integer | 
integer | 
integer | 
integer | 
integer | 
integer | 
integer | 
bytea   | 

The integer version is 599752704 bytes, and the bigint version is
673120256 bytes (a ~12% size increase).  When joining the table to
itself (keys = 1 date, 5 (big)ints, no indexes), the bigint version
performs a join to itself with an average of 44.1 sec, and the integer
version in 29.6 sec (a 48% performance hit).

While granted that it's not twice as big and twice as slow, I think it's
a fairly valid reason to want to stay within (small)int ranges.
Sometimes the initial performance hit on insert would really be worth
the continuing space/performance savings down the road.

Of course, this wasn't very scientific and the benchmarks aren't very
thorough (for instance I assumed that bigserial is implemented as a
bigint), but it should remain a valid point.

Of course, it probably has no bearing on the OP's problem.  So my advice
to the OP: have you considered not keying such a volatile table on a
serial value?

-Mark