Re: SERIAL datatype

On Thu, Aug 21, 2008 at 1:08 PM, Peter Billen <peter(at)clueless(dot)be> wrote:
> Hi all,
>
> I would like to ask a question about the serial datatype. Say I have a field
> of type serial, and say for the sake of example that the range of a serial
> goes from 1 to 5 (inclusive). I insert 5 (ed) entries into the table, so the
> table is 'full':
>
> INSERT INTO my_table (my_serial) VALUES (DEFAULT);
> INSERT INTO my_table (my_serial) VALUES (DEFAULT);
> INSERT INTO my_table (my_serial) VALUES (DEFAULT);
> INSERT INTO my_table (my_serial) VALUES (DEFAULT);
>
> Next I delete a random entry, say the one with value 3:
>
> DELETE FROM my_table WHERE my_serial = 3;
>
> Is it possible to insert a new entry? Will the serial sequence somehow be
> able to find the gap (3)?

No, sequences do not fill in gaps.

> The reason why I am asking is because I have a table in which constantly
> entries are being deleted and inserted. What happens if the serial sequence
> is exhausted? If it is not able to go the the next gap, how is it possible
> to keep inserting and deleting entries once the serial sequence has been
> exhausted? I can't find this anywhere in docs.

Regular SERIAL type is limited to a 32 bit int. BIGSERIAL uses a 64
bit int. That will give you an upper limit of 2^63, assuming positive
values only in the sequence. If you run out of that many values
you're running a seriously busy database over a very long time.

My rough guesstimate is that at 2000 inserts per second, it would take
approximately 145,865,043 years to exhaust a BIGSERIAL. I might be
off by a factor of ten or so there. But I don't think I am. Note
that an insert rate of 2000 per second would exhaust a regular SERIAL
type (2^31 size) in 12 days.

> To me, it is perfectly possible that there is only one entry in the table,
> with a serial value equal to its upper limit.

That's fine too. If you need gapless sequences, be prepared to pay
more in terms of overhead costs. If you don't need gapless sequences
(and usually you don't) then use either SERIAL or BIGSERIAL.