Why does PostgreSQL skip ID when saving new records? (Heroku)

31 Mar 19:13

2 min. read

Post cover: Why does PostgreSQL skip ID when saving new records? (Heroku)

Table of contentsClick link to navigate to the desired location

Auto-increment field (serial /bigserial) and transactions Parallel requests Restart Dyno (Heroku) and sequence caching Deleted records How to check SEQUENCE?

This content has been automatically translated from Ukrainian.

View Original

Check the last ID in the database, and it is unexpectedly larger than the actual number of records. Why does this happen?

In Heroku (and PostgreSQL in general), ID values may not be consistent for several reasons:

Auto-increment field (serial /bigserial) and transactions

In Rails, the ID is usually a serial or a bigserial, using SEQUENCE to generate unique values.
If the transaction in which the ID was allocated is canceled (ROLLBACK), this value is no longer used, but SEQUENCE is incremented further. For example, if you created an object with id = 4 and then canceled the transaction, the next entry will receive id = 5 and 4 will remain skipped.

Parallel requests

Cloud environments like Heroku can have many competitive processes that write to the database at the same time.
If two requests create new records at the same time, they can get ID in different orders.

Restart Dyno (Heroku) and sequence caching

PostgreSQL caches SEQUENCE values, which helps improve performance.
If Dyno (Heroku server) restarts, the cached values may be lost and PostgreSQL jumps over multiple IDs.

Deleted records

If you delete entries from a table (DELETE), their IDs are not reused.
For example, if there were entries ID = 1, 2, 3, then you deleted 2, the next ID will still be 4.

How to check SEQUENCE?

Run in PostgreSQL Console:

SELECT last_value, is_called FROM your_table_id_seq;

Or manually reset the value:

SELECT setval('your_table_id_seq', (SELECT MAX(id) FROM your_table));

This will align SEQUENCE, but will not fill gaps in already created IDs. This is normal PostgreSQL behavior. If you critically need serial IDs, you can use ROW_NUMBER(), but this is not recommended for the main ID.