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