Thread: Bigger sequences (int8)

Can I make a sequence use an int8 instead of int4?

I have an application where, over a few years, it's quite possible to hit
the ~2 billion limit.  (~4 billion if I start the sequence at -2
billion.)

There won't be that many records in the table, but there will be that many
inserts.  In other words, there will be many deletes, as well.

If I CYCLE, old record could still be lingering, and I have the overhead
of checking every NEXTVAL to make sure it's not already being used.  :-(

Any other ideas?  I could use two int4's together as primary key, and do
some crazy math to increment, or jump through other hoops (besides CYCLE)
to intelligently reuse keys ... but then I have some ugly overhead, as
well.

I really want the sequence to just be an int8.  Are we talking about a
heinous amount of work in the source code to allow this option?  I
wouldn't want to mess with the "CREATE SEQUENCE" syntax; it would seem
more appropriate as a compile-time option.


Paul Caskey
Software Engineer
New Mexico Software

> Can I make a sequence use an int8 instead of int4?
>
> I have an application where, over a few years, it's quite possible to hit
> the ~2 billion limit.  (~4 billion if I start the sequence at -2
> billion.)
>
> There won't be that many records in the table, but there will be that many
> inserts.  In other words, there will be many deletes, as well.
>
> If I CYCLE, old record could still be lingering, and I have the overhead
> of checking every NEXTVAL to make sure it's not already being used.  :-(
>
> Any other ideas?  I could use two int4's together as primary key, and do
> some crazy math to increment, or jump through other hoops (besides CYCLE)
> to intelligently reuse keys ... but then I have some ugly overhead, as
> well.
>
> I really want the sequence to just be an int8.  Are we talking about a
> heinous amount of work in the source code to allow this option?  I
> wouldn't want to mess with the "CREATE SEQUENCE" syntax; it would seem
> more appropriate as a compile-time option.

I'm no expert on the backend but it seems to me you would not even have to
change the syntax.  The maxval defaults to 2 billion.  For an int8 sequence
just specify max val greater than that.  Actually it may make the most sence
to always use 64 bit values for the sequence.  Just leave the default ranges
in place for compatibility.