It’s a costly operation to find gaps so you can re-use primary key values.
It also creates a risk of false references. I.e. dependent data looking for id value ‘1234’ which is one that you deleted and then subsequently re-assigned. But the entity who now has that value is different from the one that was deleted. With nothing but the primary key to identify it, this creates a false reference. This can be a serious problem depending on the nature of the data. For example tax records, medical records, criminal records, etc.
It’s also a needless operation to try to make the primary key values contiguous because you’re worrid about running out. If you use an ordinary datatype like UNSIGNED INT for your AUTO_INCREMENT column, you have 4294967295 values. You can insert 1 row per second, 24 hours per day, for 136 years before you run out of values.
If that’s a problem, then use UNSIGNED BIGINT. That should handle 1000 new unique records per second, 24 hours per day, for 584 million years.
If that’s _still_ a problem, then make the primary key a two-column key.
