Identity column re-seed: when it is necessary?

Question

During one of the last lessons at university (I'm a student), lecturer asked us to develop a database (MySQL Server if it matters) and tiny client app that would consume the database as data source.

One of requirements was that the identity column (which is the PK in every table) must be sequential, because it is a good practice (as per lecturer words). That is, when table row is deleted, it's PK must be reused in subsequent inserts. I have average knowledge in RDBMS, PKs and identity columns. From what I understand, that identity column is just a way to let DB to auto-generate PKs when inserting rows and nothing more. And identity column value shall not be related to row attributes in any way (as long as it is not natural key).

This requirement (strictly sequential identity column) was suspicious to me. I tried to ask the lecturer what is wrong if identity is not sequential (with gaps caused by deletions), but got very abstract answer like "it is convenient for users and useful for DB administrators who maintain the database". No specific examples. The argument "convenient for users" sounds silly, because it doesn't have any meaning in business domain.

Therefore I'm curious if these reasons are real? I can think only of one case when identity column reseed is required -- when identity space is exhausted. But this is more design issue when identity column type was chosen incorrectly, say simple int instead of bigint or uniqueidentifier when table contains billion rows. Suppose, an identity column is a clustered index: can gaps in identity column affect index performance? Maybe there are other real-world reasons for automatic identity column re-seed after each delete I'm not aware of?

Thanks in advance!

Answer 1

That is, when table row is deleted, it's PK must be reused in subsequent inserts.

What universe is your lecturer from??

That is grossly inefficient. If you try to do that, you will cut your performance prospects down by a factor of 10.

If you need gapless numbers for auditing reasons, build them explicitly, not directly from database tools. And never delete rows, but flag them as "deleted". This will add to the messiness of queries, since they will have to ignore such rows.

In MySQL, InnoDB requires the existence of a unique PRIMARY KEY for each table. But that is the extent of the requirement. The key can even be a string.

Gaps are a convenience to the users and DBAs, not an inconvenience.

I can think of one case where gapless would be convenient -- chunking into groups of 100 rows at a time. But there is a simple workaround using LIMIT 100,1.

Gaps have zero impact on performance. That includes non-numeric indexes. And non-unique indexes. And composite indexes.

Sure, you can run out of ids. I think I have seen it happen twice in nearly 2 decades of using MySQL. I may as well worry about being struck by an asteroid. It's low on my things-that-keep-me-awake-at-night list.

Gaps occur from (at least): INSERT IGNORE, IODKU, REPLACE, DELETE, ROLLBACK (explicit, or due to crash), Multi-master replication (including Galera and Group Replication). Do you really want to come up with workarounds for those?!

Feel free to have us sanity-check anything else that lecturer says that is suspicious.

Answer 2

Reusing an identity value, should in general be discouraged. Either the value is used entirely internally, in which case it’s actual value is immaterial, or it is also used externally in which case reusing the value is very likely going to lead to a misidentification.

Take the obvious case of an invoice or purchase order number, these might easily come from an identity column and be exposed externally, but you would never want to reuse them for precisely that reason. Both refer to specific transactions which you would not want to get confused.

Resolving such issues can be a big hassle when companies merge or are acquired. Creating such problems on purpose? Not wise.

Answer 3

The reuse of PK id values has problems and generally should be avoided.

First, the implementation of auto_increment columns doesn't provide the guarantee of being gapless. Indeed gaps will occur if you rollback an insert on an auto increment column.

Secondly the gap ID may refer to existing data that hasn't been deleted (due to missing FK constrains). If they translate to member numbers communicated outside the system then that poses potential business identity risks.

Thirdly, bigint unsigned won't run out of IDs for a significant time even given an extremely large insert rate.

The biggest pain with gaps is coming across auditors who insist its an audit flaw. For DBAs they know gaps exist and why.

Answer 4

I won't echo everyone else's comments that that reusing a PK is a bad idea but I have come across times where an identity column needed to be re-seeded.

Corruption of the PK index itself.

Granted this was using MS-SQL and many, many years ago but it still is relevant. Many years ago for the company that I work for, someone thought it would be a good idea to re-use PC's as servers in our 150+ remote locations after they were too old to be used by the clients and then stick them in a closet with no ventilation. When no Because we all know that a pile of junk 10 year old computer in a tiny room with temps of 120+ running mission critical databases could only result in good things. Like 40% failure rates and me rethinking my career choice. We would replicate the data back to the corp headquarters but more often than not, these failures would result in bad things happening to the databases. One of those things were the database having corrupted indexes which would seize up the database and the replication process. Twice in this great environment, the only solution to fix replication was to reseed the indexes and then reestablish replication. We did replace the servers later before ditching them completely.