Issue
You are concerned about data integrity after you noticed that an id sequence for one or more of your tables is non-contiguous.
Resolution
Gaps in your sequence ids do not imply issues relating to data integrity. Id sequences in Postgres are always ascending, but they are not guaranteed to be contiguous. Here are some common reasons that values could be skipped:
- A row or rows were inserted, but the inserts failed or the transaction was rolled back. Sequences are non-transactional to avoid concurrent locking contention.
- A maintenance operation, such as a changeover to a new database instance occurs. This can happen if there is an underlying hardware or operating system issue.
There are techniques to create a contiguous sequence, but they can incur a performance penalty because they require locking to ensure that the sequence behaves transactionally. There is also some added complexity in their implementation since they do not use the built-in sequences and require some stored procedure code.