Will unique RIDs ever "overflow" ?

Question

Hello, I am making an application to run on both SQL Server and PostgreSQL, so I am asking this question about both of them.

What happens whenever you create a unique primary key(using a sequence or auto-increment type thing) and you hit over 4 billion records(32 bits)? I'm not saying that our table will have 4 billion records but rather that 4 billion records have been created because the RID only increments. So even if I deleted 3.9 billion of these records, my RIDs are still in the 4 billion range. So what happens? Does it up the precision to 64 bit or does it roll over to 0 or just spit out a very critical error? Should I worry that even a 64 bit RID may eventually overflow also?

Also, how can I fight against this? Is there some kind of cleaning up option or tool? Do I just have to create my own thing to every year or so completely rebuild the table to get consistent RIDs? (and thus also touch a lot of other tables that use these RIDs are foreign keys)

Answer 1

On SQL Server: It depends on the type of the RID column. The internal IDENTITY can increment, but will fail to assign to the stoarge column:

CREATE TABLE [t1] (
[tid] int IDENTITY (2147483647, 1) NOT NULL
  , name varchar(1)
) ON [PRIMARY]
GO
insert into t1(name) values('1')
insert into t1(name) values('1')

this triggers error:

Msg 8115, Level 16, State 1, Line 2
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.

But a numeric column with enough storage will increment just fine:

CREATE TABLE [t1] (
[tid] numeric(38,0) IDENTITY (2147483647, 1) NOT NULL
  , name varchar(1)
) ON [PRIMARY]
GO
insert into t1(name) values('1')
insert into t1(name) values('1')

Similarly a bigint will overlfow at 2^^63-1:

CREATE TABLE [t1] (
[tid] bigint IDENTITY (9223372036854775807, 1) NOT NULL
  , name varchar(1)
) ON [PRIMARY]
GO
insert into t1(name) values('1')
insert into t1(name) values('1')

but a numeric column with sufficient storage will succeed:

CREATE TABLE [t1] (
[tid] numeric(38,0) IDENTITY (9223372036854775807, 1) NOT NULL
  , name varchar(1)
) ON [PRIMARY]
GO
insert into t1(name) values('1')
insert into t1(name) values('1')

Answer 2

PostgreSQL will, by default, error and not overflow:

# create sequence willyouwrap;
CREATE SEQUENCE
# select setval('willyouwrap', 9223372036854775807);
       setval        
---------------------
 9223372036854775807
(1 row)
# select nextval('willyouwrap');
ERROR:  nextval: reached maximum value of sequence "willyouwrap" (9223372036854775807)

From the docs:

Sequences are based on bigint arithmetic, so the range cannot exceed the range of an eight-byte integer (-9223372036854775808 to 9223372036854775807). On some older platforms, there might be no compiler support for eight-byte integers, in which case sequences use regular integer arithmetic (range -2147483648 to +2147483647).

However, you can make it cycle:

The CYCLE option allows the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively.

If NO CYCLE is specified, any calls to nextval after the sequence has reached its maximum value will return an error. If neither CYCLE or NO CYCLE are specified, NO CYCLE is the default.

Don't fight it. Spend the extra bytes and keep things simple. You're more likely to regret adding extra layers of complexity and/or maintenance tasks than having a larger keyspace.