Thanks. I thought it was a bit counter-intuitive to have a BIGSERIAL
while I will only have a few thousands of entries, which are updated (by
DELETE and INSERT) constantly.
Thanks Scott,
Peter
Scott Marlowe schreef:
On Thu, Aug 21, 2008 at 1:08 PM, Peter Billen <peter@xxxxxxxxxxx> 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.
