Adding a unique together constraint concurrently

Adding a unique_together constraint to a database model in Django is easy. Add it to the Meta class on your model, makemigrations, migrate, and voilà. This works well if you’re creating a new table, or if you have a low traffic application/low-volume table that won’t take long to migrate. However, in order to add a uniqueness constraint, an index must first be created (so that lookups to confirm if a duplicate already exists can be done quickly). This can be problematic. According to the Postgres docs:

…a standard index build locks out writes (but not reads) on the table until it’s done

This means that while your migration is running, no writes can be made to the table on which the constraint is being added, which, depending on the size of the table, can take some time. In order to avoid locking the table to ensure that user actions can continue to take place during the migration, we’ll have to create the index concurrently, which means we’ll have to do it with raw SQL, instead of using Django’s built-in migrations.

I’d still recommend adding the uniqueness constraint to the Meta class on your model and generating the migration file. You can then run the migration file by running python manage.py migrate <app> <migration_file> --fake. This doesn’t actually run the migration, but it does add it to your django_migrations table, which ensures that once you’re finished, your model definition will match what is in the database, and the next time you try to generate or run migrations, nothing related to this change will happen.

We’ll add the uniqueness constraint concurrently via SQL in two steps:

1. Add the index.
2. Add the uniqueness constraint, using the index we just created

Let’s jump into it! To add the index to your table, run:

CREATE UNIQUE INDEX CONCURRENTLY <constraint_name> ON <table_name> USING btree (<column_one>, <column_two>);

The key word there is CONCURRENTLY, which means:

PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table

(You can read up on other things you can do when creating indexes in Postgres here, or learn more specifics about building them concurrently here.)

It’s worth noting that if your database contains any records that violate the…