Why would someone choose to increment a unique key with a value higher than 1?

indexunique-constraint

Why would someone choose to increment a unique key with a value higher than 1?

For example instead of unique index going from (1, 2, 3, 4, 5)

It goes(8, 15, 22, 29, 36), starting at 8 and then each increment goes +7.

Does it have any security improvements?

Best Answer

One reason would be an optimization strategy used by the software to reduce the frequency it needs to talk to the database for a new key value. For example, an object-relational mapping library called Hibernate allows this:

Most of the Hibernate generators that separately obtain identifier values from database structures support the use of pluggable optimizers. Optimizers help manage the number of times Hibernate has to talk to the database in order to generate identifier values. For example, with no optimizer applied to a sequence-generator, every time the application asked Hibernate to generate an identifier it would need to grab the next sequence value from the database. But if we can minimize the number of times we need to communicate with the database here, the application will be able to perform better.

The pooled-lo optimizer works on the principle that the increment-value is encoded into the database table/sequence structure. In sequence-terms, this means that the sequence is defined with a greater-than-1 increment size.

For example, consider a brand new sequence defined as create sequence m_sequence start with 1 increment by 20. This sequence essentially defines a "pool" of 20 usable id values each and every time we ask it for its next-value. The pooled-lo optimizer interprets the next-value as the low end of that pool.

So when we first ask it for next-value, we’d get 1. We then assume that the valid pool would be the values from 1-20 inclusive.

The next call to the sequence would result in 21, which would define 21-40 as the valid range. And so on. The "lo" part of the name indicates that the value from the database table/sequence is interpreted as the pool lo(w) end.

Depending on how your particular version were implemented, it could be you usually see the gap, but not when multiple records are inserted in the same transaction or something like that.

This would apply when using a sequence or special table for new key values ahead of time, not an identity column since that's done by the database on insert with no additional round trip, and therefore no optimization benefit.

There could also be some security effect by reducing guessing, but very little because it's so easy to script a loop to guess 100, 1000, or more sequential values looking for a match, so I doubt that's the reason.