Django migrations create index
Sometimes you need to add an index to already existing field within the table for performance gains. However, operation of adding index locks table by default, so on production workload you cannot afford such a thing as it might cause downtime. The bigger table you have, the longer it takes to create an index resulting in table unavailability. PostgreSQL supports building indexes without locking out writes, but let’s see how Django handles this migration.
Consider having this simple
from django.db import models
At some point you have decided you need efficient sort on
date_created field. Following the usual workflow you update the field like this
date_created = models.DateTimeField(default=timezone.now, db_index=True)
and create corresponding migration file
$ python manage.py makemigrations
Somewhat similar to this migration file should be produced
We can check the underlying SQL code and verify that index will be created with command
python manage.py sqlmigrate app 0002
Nonetheless, this is not exactly what we need as the operation is blocking and cannot be applied safely on the production environment. Surely, you can execute
CREATE INDEX CONCURRENTLY directly on the database, but it’s a really bad practice to diverge code and database state when using an ORM.
Here’s the correct way of applying such a migration. I’ll go over most important points below
from django.db import migrations, models
- We are using SeparateDatabaseAndState operation to make sure any custom modification on the database schema (
database_operations) has a corresponding change reflected within the model definition (
- We are using AddIndexConcurrently to leverage PostgreSQL feature of creating/dropping indexes without locking out writes.
- We are setting
atomic = Falseas concurrent option is not supported inside a transaction.
Note that we have no
COMMIT section when checking underlying SQL code
python manage.py sqlmigrate app 0002 (make sure to provide correct number for the migration)
Support for concurrent index operation has been added in Django 3.0 version, so in case you are using older version for some reason here’s a way you can achieve the same thing
Now the only thing left is to apply migrations the next time you deliver your code to production.
$ python manage.py migrate