0

I use PostgreSQL. I have many database tables which contain data of the kind that is regularly "replaced entirely", as in, I first clear (delete) all existing records, then just insert all-new records.

Previously, I did this with:

DELETE FROM the_table;

However, this kept increasing the serial column (id) all the time, eventually leading to it reaching the maximum number. I'm still unsure what happens at that point; I hope that it just resets automatically, but I fear that it probably makes the table stop working and start spitting out errors whenever new records are attempted to be INSERTed.

To solve this for these tables, I have started doing this instead:

TRUNCATE TABLE the_table RESTART IDENTITY[ CASCADE];

This does the same thing as DELETE FROM, but also resets the serial column. Great. Exactly what I wanted.

However, I also have many tables where the data needs to be preserved, and cannot be cleared like the ones I described above. I obviously cannot run TRUNCATE TABLE on those tables, but they will also eventually reach a point where either the serial or bigserial maximum is reached.

It doesn't matter if this "probably will take years". Just knowing that this will/might one day happen is enough to give me constant stress. Is there something I can do to avoid that scenario? Does PG handle this automatically? Can it be told somehow how to behave when this inevitably happens?

Since many old records likely will have been deleted by that point, I would like it to start using those unused ids automatically. For example, if ids 0-1000 are empty when it reaches the maximum serial value, instead of failing, it would start using 0, 1, 2, 3, 4... until it gets to 1000 and then it looks ahead for any unused ids until it finds one.

If the above idea is stupid, I want to hear what you all do about this problem.

  • 1
    in 1000 years when you run out of bigserials postgres will have a wider serial type available. – Jasen Jan 21 at 3:49
  • @Jasen Many of my tables do NOT have bigserial but only serial, because the content it's supposed to keep is far less than an integer, but stuff is deleted and those ids need to be freed up. I don't always want to use bigserial just because they will run out. – Carl Jan 21 at 3:53
  • this is still a good question. because serials have to be shared across transactions. that means that wrapping them in functions is tricky. – Jasen Jan 21 at 3:58
  • "what you all do about this problem" - nothing. As Laurenz pointed out, bigints aren't goin to run out any time soon. The sequence behind a serial still emits bigint values - so if you are about to hit the limits of integer values, you can alter the columns to bigint and continue without any other change – a_horse_with_no_name Jan 21 at 8:06
0

If you consume 10000 sequence values per second, you'll have consumed 10000*3600*24*365 values per year, that is 3,1536E11. At that rate, it takes a couple of million years to exhaust a bignt sequence.

So you shouldn't lose any sleep over it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.