Making Unique Ids
A sequence is a database object that hands out increasing numbers, often used to generate primary keys. Auto increment columns are a friendly wrapper that pulls the next sequence value on each insert.
Why Gaps Appear
Sequence values are not transactional. If a transaction asks for the next value and then rolls back, that number is not reused, so the id space has gaps. Sequences also hand out values in caches of several numbers per connection for speed, which can leave more gaps. This is normal and expected.
Practical Notes
- Treat sequence ids as unique identifiers, never as a gap free count of rows.
- Sequences avoid the locking that a max plus one query would need under concurrency.
- A single sequence can become a write hotspot at very high insert rates, which is why some systems prefer random ids like UUIDs.
Key idea
Sequences and auto increment generate unique ids without locking, but values are non transactional so gaps are normal and ids must not be treated as a row count.