PostgreSQL sequences (CREATE SEQUENCE) are limited to bigint (which is usually big enough). But in my case I need a bigger sequence. The table will not have this much records, it will be cleaned over time, but the numbers must never be reused. As I am planning this to be used for at least 20 years, there is the need to go bigger than bigint.

I am using NUMERIC(35).

How can I achieve this with less locking as possible?

Is creating two sequences and summing them a good idea?

  • Maybe UUID with custom sequence function? – Akina Oct 7 '19 at 11:35
  • 10
    If you use 10000 values per second 24/7 without any pause, it will take you 29.247.120 years to burn through a bigint sequence - if you allow negative values as well, then it's twice as long – a_horse_with_no_name Oct 7 '19 at 11:37
  • "Is creating two sequences and summing them a good idea?" Probably not. But instead you could have a "version" which is a tiny integer starting at 1, and then the sequence value. When the sequence overflow, you increment the version by 1, so you go to 2, and start the sequence over. Of course various queries will need to take this into account but it is easy in PostgreSQL as you can do (versionA, sequenceA) < (versionB, sequenceB) – Patrick Mevzek Jun 2 at 17:31

As pointed out by @a_horse_with_no_name, bigint is good enought. I think 29 million years is reasonable (hehe).

The math checks. Should have done it before.

| improve this answer | |

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.