Django: PostgreSQL Transactions and Sequence Numbers

As soon as your application is writing multiple changes to your database at once, you want to consider using transactions to make sure that all of your changes are applied, or none at all. Again, Django makes these things tremendously easy. For example, one can use transaction.atomic as a decorator or context manager. Here an excerpt from the documentation:

from django.db import transaction

@transaction.atomic
def viewfunc(request):
    # This code executes inside a transaction.
    do_stuff()

However, this hides some details behind the curtains that you might not be aware of. Let's look at the following code. Here we create an object in a transaction and trigger an exception afterwards so that the transaction is rolled back.

@transaction.atomic
def my_view(request):
  Entry.objects.create(title="Django is awesome", content="…") # id: 1
  throw Exception('Oh no. Transaction is rolled back.')

One would think that this does not have any side-effects, because the transaction was rolled back. In truth, the creation of the entry object above leads to gaps in the sequence numbers of the primary key (id field).

For example, if the code above is executed for the first time and the entry is assigned id 1, the next successfully created entry object is assigned id 2.

The reason for this is the way new ids are generated. Under the hood a model's id is backed by a BigAutoField field. BigAutoField is an automatically incrementing sequence number, which uses PostgreSQL sequences to determine the next value to assign to the field.

The PostgreSQL documentation on sequences:

To avoid blocking concurrent transactions that obtain numbers from the same sequence, the value obtained by nextval is not reclaimed for re-use if the calling transaction later aborts. This means that transaction aborts or database crashes can result in gaps in the sequence of assigned values.

All in all this isn't really a problem, but it is something one should be aware of.

Keep in mind that this only applies to models that use auto increment fields, i.e. AutoField or BigAutoField. The latter of which is used as the default primary key for Django models. If your model's primary key is something else, for example a UUIDField, this problem does not exist.