For a primary key of an integral type, why is it important to avoid gaps ?

Question

I am generating a surrogate key for a table & due to my hi/lo algorithm, everytime you reboot/restart the machine, gaps may appear.

T1: current hi = 10000000 
    (sequence being dished out .. 1 to 100)
       Assume that current sequence is 10000050

T2: restart system.

T3: System gives out the next_hi as 10000100 
    (sequence being dished out now ranges from 101 to 200)

T4: Next request for a key returns 100001001

From a primary key or indexing internals perspective, why is it important that there be no gaps in the sequences ? I'm asking this for a deeper understanding of mysql specifically.

Thanks

Answer 1

why is it important that there be no gaps in the sequences

It is not important. Gaps are fine. For performance reasons, gaps are tolerated.

What would be useful is a guarantee to have an strictly increasing sequence (i.e. the sequence has the same ordering as the row creation time). But even that is not guaranteed in a clustered configuration (with local counter caches).

Answer 2

From a primary key or indexing internals perspective, why is it important that there be no gaps in the sequences?

It's not important - what lead you to believe it was?
All that matters with the primary key is that it is unique to all the data in the table. Doesn't matter what the value is, or if the records before and after are sequencial values.

Answer 3

Who told you this? A surrogate key has no meaning at all, so there can't be any gap. What is a gap in something that has no meaning? We use UUID's for our keys, something like this: 6ba7b812-9dad-11d1-80b4-00c04fd430c8. What would be the "next" key? Nobody knows, nobody cares. As long as it is unique, it's fine.