I was curious about how long I could use a bigserial sequence before having it rollover, so I decided to investigate it.
As a reminder, the range of the bigserial datatype is 1 to 9,223,372,036,854,775,807 (that’s nine quintillion two hundred twenty-three quadrillion three hundred seventy-two trillion thirty-six billion eight hundred fifty-four million seven hundred seventy-five thousand eight hundred seven) or, roughly, 1018.96. Given the following:
- Number of inserts per second: 1,000
- Number of seconds per year: 365 * 24 * 60 * 60 (365 days per year, 24 hours per day, 60 minutes per hour, 60 seconds per minute) = 31,536,000
It would take:
9,223,372,036,854,775,807 / 1,000 / 31,536,000 = 292,471,208.678 years
Loosely translated, that’s 292.5 million years. If this were going to happen to your database today, you would have had to begin your 1,000 inserts per second sometime between the time when the plants, vertebrates, and invertebrates had all left the oceans and began living on land and when the dinosaurs inhabited the planet.
If you’re worried about exhausting your bigserial sequence, I hope this information will put your mind at-ease.