Does anyone ever use database sequences that count down from 2^64-1?

Question

Does anyone ever use database sequences that count down from 2^64-1, rather than up from 1, for assigning ids to rows?

The motivation for doing this would be to ensure that any application consuming these ids does not encounter an overflow bug at some bit width < 64, the most obvious being trying to use a 32-bit int to hold the id. If you start at the top, problems should arise sooner during development.

Answer 1

Never seen it done, certainly not for that reason.

Speaking for SQL Server, it would work fine with a Bigint Identity column. But you'd have to ask whether it's worth the confusion it could cause, and the need to remember to define the clustered index descending to prevent fragmentation, etc.

Answer 2

Never seen it done.

• In MySQL, increment of auto_increment column must be positive.

• In PostgreSQL, there are no unsigned integer types, so you can only use 63 bits. But you can do:

  CREATE SEQUENCE s 
     INCREMENT BY -1
         MINVALUE 1
         MAXVALUE 9223372036854775807 
       START WITH 9223372036854775807
         NO CYCLE;
  
  CREATE TABLE t (
    id BIGINT UNIQUE DEFAULT NEXTVAL('s'),
    data TEXT
  );
  

  And it works! If you manually insert an id, when the sequence reaches it, the UNIQUE constraint will prevent insertion, but a subsequent call to NEXTVAL('s') will return a value already past the failed one. You (re)set the sequence with, e.g.,

  SELECT SETVAL('s', 1000);
  

  and in this way you can reserve an area for your manual insertions.

To learn all you want to know (and what you’d rather not know) about sequences in PostgreSQL, read the docs.