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

31 Mar 19:13

2 min. read

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

Table of contentsClick link to navigate to the desired location

Auto-increment field (serial / bigserial) and transactions Concurrent queries Dyno restart (Heroku) and SEQUENCE caching Deleted records How to check SEQUENCE?

This content has been automatically translated from Ukrainian.

View Original

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

In Heroku (and generally in PostgreSQL), ID values can be non-sequential for several reasons:

Auto-increment field (serial / bigserial) and transactions

In Rails, IDs are usually serial or bigserial, which use SEQUENCE to generate unique values.
If a transaction that allocated an ID is rolled back (ROLLBACK), that value is no longer used, but the SEQUENCE increments further. For example, if an object is created with id = 4, and then the transaction is rolled back, the next record will receive id = 5, and 4 will remain skipped.

Concurrent queries

In cloud environments like Heroku, there can be many concurrent processes writing to the database at the same time.
If two queries create new records simultaneously, they may receive IDs in different orders.

Dyno restart (Heroku) and SEQUENCE caching

PostgreSQL caches SEQUENCE values, which helps improve performance.
If a Dyno (Heroku server) restarts, cached values may be lost, and PostgreSQL will skip several IDs.

Deleted records

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

How to check SEQUENCE?

Execute in the 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 the SEQUENCE but will not fill in the gaps in already created IDs. This is normal behavior for PostgreSQL. If you critically need sequential IDs, you can use ROW_NUMBER(), but it is not recommended for primary IDs.