CODEWITHSUNDEEP
djangopostgresqlauto-increment
Oli
Oli

Reputation: 239810

Why isn't model ID always the next incremented number in Postgres/Django?

I have a Booking model in Django and its ID is the build-in id column that Django creates. Here's how it looks at database level in Postgres:

integer   not null default nextval('bookings_booking_id_seq'::regclass)

The client has called up today worried that these numbers seem to be sequential but are not always contiguous. For example, the sequence goes:

..2, ..3, ..4, ..6, ..8, ..9

They are worried that the system is dropping or deleting bookings. Where is ..5 and ..7? We log [practically] everything that can delete a booking so I'm fairly confident this isn't a system issue. Programmers' intuition has also taught me not to expect contiguity from Auto-IDs but intuition alone isn't good enough for an explanation to a client.

Is there a technical reason Postgres would be skipping numbers when it assigns these IDs?

Upvotes: 5

Views: 1067

Answers (1)

Sayse
Sayse

Reputation: 43300

From the postgres docs:

Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still "used up" even if a row containing that value is never successfully inserted into the table column. This may happen, for example, if the inserting transaction rolls back. See nextval() in Section 9.16 for details.

and also from the docs for nextval

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Such cases will leave unused "holes" in the sequence of assigned values. Thus, PostgreSQL sequence objects cannot be used to obtain "gapless" sequences.

Upvotes: 6

Related Questions