Команда Python for Devs подготовила перевод статьи о том, как легко «сломать» внешние ключи в Django и что с этим делать. Если кратко, то unique_together больше не нужен, индексы на ForeignKey работают не так, как вы думаете, миграции могут блокировать продакшен, а правильный порядок операций и частичные индексы экономят гигабайты и спасают нервы.

Ограничения поддерживают целостность системы и не дают вам выстрелить себе в ногу. Внешние ключи — особый вид ограничений: в отличие от unique, check и первичных ключей, они затрагивают более одной таблицы. Поэтому их сложнее реализовать и проще сделать неправильно.

В этой статье я покажу типичные ошибки, возможные оптимизации и неявное поведение, связанное с внешними ключами.

Наивная реализация

Представьте простое приложение для управления каталогом товаров:

Первая таблица (или модель) в каталоге — модель Category:

class Category(models.Model):
    id: int = models.BigAutoField(primary_key=True)
    name: str = models.CharField(max_length=50)

Категории могут быть «товары для дома», «фрукты», «одежда» и так далее.

Далее — модель для хранения товаров:

class Product(models.Model):
    class Meta:
        unique_together = (
            ('category', 'category_sort_order'),
        )

    id = models.BigAutoField(primary_key=True)
    name = models.CharField(max_length=50)
    description = models.TextField()

    category = models.ForeignKey(to=Category, on_delete=models.PROTECT, related_name='products')
    category_sort_order = models.IntegerField()

    created_by = models.ForeignKey(to=User, on_delete=models.PROTECT, related_name='+')
    last_edited_by = models.ForeignKey(to=User, on_delete=models.PROTECT, related_name='+', null=True)

У товара есть название и описание, а с категорией он связывается внешним ключом.

Чтобы в интерфейсе показывать товары в правильном порядке, добавим поле сортировки внутри категории. Чтобы порядок был однозначным, навесим уникальное ограничение, не позволяющее товарам в одной категории иметь одинаковое значение сортировки.

Наконец, мы добавили два столбца, чтобы фиксировать пользователя, создавшего товар, и пользователя, который последним его редактировал. В основном это нужно для аудита.

Раз уж речь о внешних ключах, вот три внешних ключа, которые есть в этой очень простой таблице товаров:

category: внешний ключ к созданной нами модели каталога.
created_by: внешний ключ к модели пользователя.
last_edited_by: внешний ключ к модели пользователя. Может быть null.

Эта наивная реализация, как видно из названия, действительно наивна! Все не так просто, как кажется. В следующих разделах мы внесем правки и улучшим эту модель.

Улучшенная реализация

Говорят, что разработчики тратят больше времени на чтение кода, чем на его написание. Как человек, который немало времени проводил за ревью и разбором чужого кода, чтобы понять, как он устроен, могу подтвердить — это действительно так. Мы начали с наивной реализации, а теперь посмотрим на код внимательнее и сделаем его лучше.

Замена unique_together

Чтобы управлять порядком отображения товаров в интерфейсе, мы добавили каждому товару поле category_sort_order. Чтобы два товара в одной категории не имели одинакового значения, мы повесили уникальное ограничение на комбинацию category и category_sort_order:

class Product(models.Model):
    class Meta:
        unique_together = (
            ('category', 'category_sort_order'),
        )

В документации Django про unique_together есть важное примечание, которое стоит учитывать:

Вместо unique_together используйте UniqueConstraint с опцией constraints. UniqueConstraint предоставляет больше возможностей, чем unique_together. В будущем unique_together может быть удалён.

Поскольку unique_together устарел и не рекомендуется к использованию, заменим его на UniqueConstraint:

@@ -13,9 +13,15 @@ class Category(models.Model):

 class Product(models.Model):
     class Meta:
-        unique_together = (
-            ('category', 'category_sort_order', ),
-        )
+        constraints = [
+            models.UniqueConstraint(
+                name='product_category_sort_order_uk',
+                fields=(
+                    'category',
+                    'category_sort_order',
+                ),
+            ),
+        ]

Документация советует именно такой подход, так что будем придерживаться его всегда!

Вывод: не используйте unique_together, используйте UniqueConstraint.

Выявление дублирующихся индексов

Теперь, когда мы избавились от unique_together, посмотрим на схему:

catalog=# \d catalog_product
       Column        │         Type
─────────────────────┼───────────────────────
 id                  │ bigint
 name                │ character varying(50)
 category_sort_order │ integer
 category_id         │ bigint
 created_by_id       │ integer
 last_edited_by_id   │ integer
Indexes:
    "catalog_product_pkey" PRIMARY KEY, btree (id)
    "catalog_product_category_id_35bf920b" btree (category_id)
    "catalog_product_category_id_category_sort_order_b8206596_uniq" UNIQUE CONSTRAINT, btree (category_id, category_sort_order)
    "catalog_product_created_by_id_4e458b98" btree (created_by_id)
    "catalog_product_last_edited_by_id_05484fb6" btree (last_edited_by_id)
    -- ...

Легко потеряться в этом множестве информации, но обратите внимание — у нас есть два индекса, которые начинаются с category.

Первый индекс — это уникальное ограничение, которое мы только что добавили:

class Product(models.Model):
    class Meta:
        constraints = [
            models.UniqueConstraint(
                name='product_category_sort_order_uk',
                fields=('category', 'category_sort_order'),
            ),
        ]

Второй индекс не так очевиден:

class Product(models.Model):
    # ...
    category = models.ForeignKey(
        to=Category,
        on_delete=models.PROTECT,
        related_name='products',
    )

Где же он? Ответ скрыт в официальной документации по полю ForeignKey:

Для ForeignKey автоматически создается индекс в базе данных.

Когда вы определяете внешний ключ, Django неявно создает индекс «за кулисами». В большинстве случаев это полезно, но в нашем случае поле уже (достаточно) проиндексировано. Читаем дальше в документации:

Для ForeignKey автоматически создается индекс в базе данных. Вы можете отключить это, установив db_index=False.

Если мы считаем, что индекс не нужен, можно явно указать Django не создавать его, добавив db_index=False:

@@ -35,6 +35,8 @@ class Product(models.Model):
     category = models.ForeignKey(
         to=Category,
         on_delete=models.PROTECT,
         related_name='products',
+        # Indexed in unique constraint.
+        db_index=False,
     )

В этом случае запросы, использующие category, смогут работать через уникальный индекс.

Неявное поведение: поле ForeignKey по умолчанию создает индекс, если явно не указать db_index=False.

Чтобы реально убрать индекс, сначала нужно сгенерировать миграцию:

$ ./manage.py makemigrations
Migrations for 'catalog':
  demo/catalog/migrations/0003_alter_product_category.py
    ~ Alter field category on product

Но прежде чем мы перейдём к её применению, есть ещё одна небольшая деталь, о которой стоит позаботиться.

Выявление блокирующих миграций

Посмотрим на миграцию, которую мы только что сгенерировали для удаления индекса у внешнего ключа:

class Migration(migrations.Migration):
    dependencies = [
        ('catalog', '0002_alter_product_unique_together_and_more'),
    ]

    operations = [
        migrations.AlterField(
            model_name='product',
            name='category',
            field=models.ForeignKey(
                db_index=False,
                on_delete=django.db.models.deletion.PROTECT,
                related_name='products',
                to='catalog.category',
            ),
        ),
    ]

На первый взгляд миграция кажется безобидной. Но давайте копнём глубже и посмотрим на реальный SQL, который она генерирует:

$ ./manage.py sqlmigrate catalog 0003
BEGIN;
--
-- Alter field category on product
--
SET CONSTRAINTS "catalog_product_category_id_35bf920b_fk_catalog_category_id" IMMEDIATE;
ALTER TABLE "catalog_product" DROP CONSTRAINT "catalog_product_category_id_35bf920b_fk_catalog_category_id";
ALTER TABLE "catalog_product" ADD CONSTRAINT "catalog_product_category_id_35bf920b_fk_catalog_category_id"
    FOREIGN KEY ("category_id") REFERENCES "catalog_category" ("id") DEFERRABLE INITIALLY DEFERRED;
COMMIT;

Для неопытного взгляда всё может выглядеть нормально, но если присмотреться, становится ясно: здесь происходит кое-что очень опасное.

Вывод: всегда проверяйте SQL, который генерируют миграции.

Когда мы установили db_index=False, наша цель была — убрать индекс, но сохранить ограничение. К сожалению, Django не способен уловить такую тонкую разницу, поэтому вместо этого он пересоздаёт весь внешний ключ без индекса!

Пересоздание ограничения выполняется в два шага:

Первый шаг: удаление существующего ограничения

ALTER TABLE "catalog_product" 
DROP CONSTRAINT "catalog_product_category_id_35bf920b_fk_catalog_category_id";

База данных удаляет индекс для поля и прекращает проверку ограничения. При удалении индекса требуется блокировка таблицы, что может остановить некоторые операции над ней.

Второй шаг: создание нового ограничения

ALTER TABLE "catalog_product" 
ADD CONSTRAINT "catalog_product_category_id_35bf920b_fk_catalog_category_id"
FOREIGN KEY ("category_id") REFERENCES "catalog_category" ("id") 
DEFERRABLE INITIALLY DEFERRED;

Сначала база снова блокирует таблицу. Затем, чтобы убедиться в корректности ограничения, она должна проверить, что все значения в столбце имеют соответствующие записи в связанной таблице. В нашем случае — что у каждого продукта есть категория в таблице категорий. В зависимости от размеров таблиц это может занять значительное время и мешать выполняющимся операциям.

Неявное поведение: при изменении ForeignKey Django может неявно пересоздавать ограничение. Это требует долгих блокировок и может блокировать операции с таблицей.

Но всё это вовсе не нужно. У нас нет претензий к самому ограничению — нам нужно лишь сохранить его как есть и убрать только индекс.

Безопасная миграция внешнего ключа

Есть такие изменения во внешних ключах, которые Django (пока?) не умеет корректно определять. В итоге он может пересоздать ограничение в базе данных. Это приводит к долгим блокировкам и может повлиять на работу продакшн-системы. Чтобы этого избежать, нужно изменить подход к применению миграции. Чтобы понять как, сначала разберёмся, как Django вообще генерирует миграции:

Генерация миграций с помощью makemigrations:

Django формирует «состояние» на основе существующих миграций.
Сравнивает его с желаемым состоянием моделей в models.py.
Генерирует операции миграции, отражающие разницу.

Применение миграций с помощью migrate:

На основе операций миграции генерируется SQL (sqlmigrate).
Сформированный SQL применяется к базе данных.

Теперь разберём наш случай с изменением внешнего ключа:

Django замечает, что у ForeignKey изменился параметр: db_index с True → False.
Генерируется операция для «синхронизации» состояния: migrations.AlterField с новым определением ForeignKey.
Из этой операции Django формирует SQL: чтобы «синхронизировать» внешний ключ, он удаляет ограничение и пересоздаёт его.

То есть Django видит изменение в поле, но не умеет сгенерировать миграцию, которая просто уберёт индекс.

Если миграция и SQL, сгенерированные Django, не соответствуют тому, что нам действительно нужно, можно воспользоваться специальной операцией SeparateDatabaseAndState:

Это специализированная операция, позволяющая разделять изменения в схеме базы данных (database) и во внутреннем состоянии Django (state).

С её помощью мы можем задать один набор операций для внутреннего состояния Django, а другой — для самой базы данных.

@@ -11,9 +11,18 @@ class Migration(migrations.Migration):
     ]

     operations = [
-        migrations.AlterField(
-            model_name='product',
-            name='category',
-            field=models.ForeignKey(db_index=False, on_delete=django.db.models.deletion.PROTECT, related_name='products', to='catalog.category'),
-        ),
+        migrations.operations.SeparateDatabaseAndState(
+            state_operations=[
+                migrations.AlterField(
+                    model_name='product',
+                    name='category',
+                    field=models.ForeignKey(db_index=False, on_delete=django.db.models.deletion.PROTECT, related_name='products', to='catalog.category'),
+                ),
+            ],
+            database_operations=[
+                migrations.RunSQL(
+                    'DROP INDEX catalog_product_category_id_35bf920b',
+                ),
+            ],
+        )
     ]

Операция миграции SeparateDatabaseAndState принимает два аргумента:

state_operations: операции, выполняемые над внутренним состоянием при агрегировании изменений из миграций. В большинстве случаев это те операции, которые Django автоматически сгенерировал через makemigrations.
database_operations: операции, выполняемые в базе данных. Здесь мы точно контролируем, что именно будет выполнено в БД при применении миграции через migrate. В нашем случае мы используем RunSQL, чтобы выполнить команду DROP INDEX.

Для наглядности — вот что будет выполнено при применении этой миграции:

$ ./manage.py sqlmigrate catalog 0003
BEGIN;
--
-- Custom state/database change combination
--
DROP INDEX catalog_product_category_id_35bf920b;
COMMIT;

Именно то, что нам нужно!

Применяем миграцию:

$ ./manage.py migrate
Operations to perform:
  Apply all migrations: catalog
Running migrations:
  Applying catalog.0003_alter_product_category... OK

Так что… мы закончили?

Обратные операции миграций

Предположим, вы применили миграцию для удаления индекса, и всё прошло успешно. Проходит несколько минут — и вы понимаете, что допустили ужасную ошибку. В панике возвращаетесь к ноутбуку, чтобы откатить миграцию:

$ ./manage.py migrate catalog 0002
Rendering model states... DONE
Unapplying catalog.0003_alter_product_category...
django.db.migrations.exceptions.IrreversibleError:
Operation <RunSQL ''> in catalog.0003_alter_product_category is not reversible

"Operation in catalog.0003_alter_product_category is not reversible" О нет! Теперь придётся объясняться…

Причина в том, что команда RunSQL для удаления индекса не содержала обратной операции. Исправить это просто:

index 962c756..7ad45e0 100644
--- a/demo/catalog/migrations/0003_alter_product_category.py
+++ b/demo/catalog/migrations/0003_alter_product_category.py
@@ -22,6 +22,7 @@ class Migration(migrations.Migration):
             database_operations=[
                 migrations.RunSQL(
                     'DROP INDEX catalog_product_category_id_35bf920b',
+                    'CREATE INDEX "catalog_product_category_id_35bf920b" ON "catalog_product" ("category_id")',
                 ),
             ],
         )

У RunSQL есть второй аргумент — обратная операция, то есть то, что нужно выполнить, чтобы отменить миграцию. В нашем случае отмена удаления индекса означает его создание!

Но откуда взять SQL для этого? Обычно — из миграции, в которой индекс создавался. В нашем случае это самая первая миграция:

$ ./manage.py sqlmigrate catalog 0001
...
BEGIN;
...
--
-- Create model Product
--
...
CREATE INDEX "catalog_product_category_id_35bf920b" ON "catalog_product" ("category_id");
...

Теперь, если вы обнаружили серьёзную ошибку и хотите откатить миграцию:

$ ./manage.py migrate catalog 0002
Rendering model states... DONE
Unapplying catalog.0003_alter_product_category... OK

Отлично!

Вывод: всегда задавайте обратные операции, когда это возможно — вы никогда не знаете, когда они вам понадобятся.

Параллельные операции с индексами

Теперь мы убедились, что удаляем только индекс, не пересоздавая ограничение, и предусмотрели обратную операцию на случай ошибки. Отлично, но есть ещё один нюанс… Согласно документации PostgreSQL по DROP INDEX:

Обычный DROP INDEX накладывает блокировку уровня ACCESS EXCLUSIVE на таблицу, блокируя другие обращения, пока индекс не будет удалён.

Чтобы удалить индекс, PostgreSQL накладывает блокировку на таблицу, и это останавливает все операции с ней. Если индекс небольшой — это не проблема. Но что, если индекс огромный? Удаление большого индекса может занять время, а живую таблицу нельзя надолго блокировать.

PostgreSQL предлагает альтернативу — удаление индекса без жёсткой блокировки:

@@ -5,6 +5,7 @@ from django.db import migrations, models
 class Migration(migrations.Migration):
+    atomic = False

     dependencies = [
         ('catalog', '0002_alter_product_unique_together_and_more'),
@@ -21,7 +22,7 @@ class Migration(migrations.Migration):
             ],
             database_operations=[
                 migrations.RunSQL(
-                    'DROP INDEX catalog_product_category_id_35bf920b',
+                    'DROP INDEX CONCURRENTLY catalog_product_category_id_35bf920b',
-                    'CREATE INDEX "catalog_product_category_id_35bf920b" ON "catalog_product" ("category_id")',
+                    'CREATE INDEX CONCURRENTLY "catalog_product_category_id_35bf920b" ON "catalog_product" ("category_id")',
                 ),
             ],

Удаление индекса «параллельно» выполняется в два этапа. Сначала индекс помечается как «удалённый» в системной таблице. В это время текущие транзакции всё ещё могут им пользоваться. Затем индекс окончательно удаляется. Такой способ требует минимальной блокировки, но может занять чуть больше времени.

Вывод: используйте параллельные операции с индексами в нагруженных системах. Они выполняются дольше, но не блокируют работу таблицы.

Базы данных вроде PostgreSQL поддерживают транзакционный DDL, позволяющий выполнять команды CREATE, DROP и ALTER внутри транзакции. Это удобно, так как позволяет изменять схему атомарно (и даже делать необычные вещи вроде «невидимых» индексов). Но параллельные операции нельзя выполнить внутри транзакции. Это значит, что всю миграцию нужно пометить как неатомарную, установив atomic=False.

В атомарной миграции, если что-то ломается посередине, транзакция полностью откатывается, и система возвращается в исходное состояние, как будто миграция не запускалась. В неатомарной миграции при сбое можно оказаться в неполном и неконсистентном состоянии. Чтобы снизить риск «зависнуть» с наполовину применённой миграцией, операции вроде DROP INDEX CONCURRENTLY или CREATE INDEX CONCURRENTLY лучше вынести в отдельную миграцию.

Вывод: чтобы избежать неполных миграций, операции, которые нельзя выполнить атомарно, выносите в отдельную миграцию.

Итоговая миграция:

class Migration(migrations.Migration):
    atomic = False

    dependencies = [
        ('catalog', '0002_alter_product_unique_together_and_more'),
    ]

    operations = [
        migrations.operations.SeparateDatabaseAndState(
            state_operations=[
                migrations.AlterField(
                    model_name='product',
                    name='category',
                    field=models.ForeignKey(db_index=False, on_delete=django.db.models.deletion.PROTECT, related_name='products', to='catalog.category'),
                ),
            ],
            database_operations=[
                migrations.RunSQL(
                    'DROP INDEX CONCURRENTLY catalog_product_category_id_35bf920b',
                    'CREATE INDEX CONCURRENTLY "catalog_product_category_id_35bf920b" ON "catalog_product" ("category_id")',
                ),
            ],
        )
    ]

В этой миграции мы не позволили Django пересоздать весь внешний ключ, а удалили только индекс. Она также использует параллельные операции с индексами, поэтому её безопасно применять на живой системе, и, если вы ошиблись, её можно откатить.

Индексы на внешних ключах

До этого мы разобрались с внешним ключом category, теперь перейдём к следующему внешнему ключу в модели:

class Product(models.Model):
    #...
    created_by = models.ForeignKey(
        to=User,
        on_delete=models.PROTECT,
        related_name='+',
    )

Django по умолчанию создаёт индекс для поля ForeignKey, если явно не указано иное. Так как мы не прописали db_index=False для этого поля, Django автоматически создал индекс для created_by. Но действительно ли он нам нужен?

Чтобы ответить, нужно сначала понять, как используется это поле:

В основном для целей аудита.
Товары практически никогда не ищутся по пользователю, который их создал.

Исходя из этих двух случаев, похоже, что таблицу почти никогда не будут запрашивать по created_by, а значит, индекс здесь, скорее всего, не нужен. Но у этого индекса есть ещё одно, менее очевидное применение.

Для демонстрации создадим пользователя:

>>> haki = User.objects.create_user(
...    username='haki',
...    first_name='haki',
...    last_name='benita',
... )
<User: haki>

Теперь включим SQL-логирование и удалим этого пользователя:

>>> haki.delete()
(0.438) SELECT * FROM "catalog_product" WHERE "catalog_product"."created_by_id" IN (102); args=(102,)
(0.002) SELECT * FROM "catalog_product" WHERE "catalog_product"."last_edited_by_id" IN (102); args=(102,)
(0.000) BEGIN;
(0.002) DELETE FROM "django_admin_log" WHERE "django_admin_log"."user_id" IN (102); args=(102,)
(0.001) DELETE FROM "auth_user_groups" WHERE "auth_user_groups"."user_id" IN (102); args=(102,)
(0.001) DELETE FROM "auth_user_user_permissions" WHERE "auth_user_user_permissions"."user_id" IN (102); args=(102,)
(0.001) DELETE FROM "auth_user" WHERE "auth_user"."id" IN (102); args=(102,)
(0.368) COMMIT;
(1, {'auth.User': 1})

Тут происходит целая куча всего! Разберём пошагово:

Django проверяет, есть ли товары, которые были созданы или отредактированы этим пользователем.
Django удаляет все админские логи, членства в группах и права, связанные с этим пользователем.
Django фактически удаляет запись пользователя из таблицы пользователей.
Django коммитит транзакцию, и база данных повторяет все эти проверки!

И вот мы подходим к следующему, менее очевидному использованию индексов на внешних ключах — они помогают проверять ограничения внешних ключей.

Если внешний ключ определён с on_delete=PROTECT, индекс используется, чтобы убедиться, что на объект нет ссылок. В нашем случае — чтобы товары не ссылались на удаляемого пользователя.
Если внешний ключ определён с on_delete=CASCADE, индекс используется, чтобы удалить связанные объекты. В нашем случае удаление пользователя может повлечь удаление товаров, которые на него ссылаются.

Вы могли заметить, что метод delete() в Django возвращает структуру-счётчик, где хранится количество удалённых объектов каждого типа. Именно поэтому, несмотря на то, что база данных тоже выполняет все проверки, Django дублирует их на своей стороне. В итоге индексы на внешних ключах активно задействуются при удалениях.

Важно: удаление этих индексов может привести к неожиданным и трудным для диагностики проблемам с производительностью при удалении связанных объектов.

Раз мы выяснили, что индекс действительно нужен, мы явно задаём db_index для этого поля и добавляем комментарий, чтобы следующий разработчик понял, зачем он остался:

@@ -44,7 +44,8 @@
 class Product(models.Model):
     created_by = models.ForeignKey(
         to=User,
         on_delete=models.PROTECT,
         related_name='+',
+        # Used to speed up user deletion.
+        db_index=True,
     )

Так будущий разработчик (или вы сами через пару месяцев) не будет снова проходить весь этот путь рассуждений.

Вывод: всегда явно задавайте db_index у ForeignKey и добавляйте комментарий, объясняющий, зачем он используется.

Частичные индексы для внешних ключей

Мы уже разобрали два из трёх внешних ключей — category и created_by. Остался последний:

class Product(models.Model):
    [...]
    last_edited_by = models.ForeignKey(
        to=User,
        on_delete=models.PROTECT,
        related_name='+',
        null=True,
    )

Как и в случае с created_by, индекс на last_edited_by в основном нужен для аудита. Никто не будет искать товары по пользователю, который их последний раз редактировал. Однако мы уже знаем, что этот индекс используется при удалении пользователей, поэтому убирать его нельзя. Но кое-что с ним всё же можно сделать.

Для демонстрации добавим немного данных.

Создадим 100 пользователей:

from django.contrib.auth.models import User

users = [
    User.objects.create_user(
        username=f'user{i}',
        email=f'user{i}@email.com',
        first_name=f'User {i}',
    ) for i in range(100)
]

Создадим 50 категорий:

from catalog.models import Category

categories = [
    Category.objects.create(
        name=f'Category {i}',
    ) for i in range(50)
]

Создадим 1 000 000 товаров:

import random
from django.utils import lorem_ipsum
from catalog.models import Product

random.seed(8080)

Product.objects.bulk_create((
    Product(
        name=f'Product {i}',
        description=lorem_ipsum.words(100),
        category=random.choice(categories),
        category_sort_order=i,
        created_by=random.choice(users),
        last_edited_by=random.choice(users) if i % 1000 == 0 else None,
    ) for i in range(1_000_000)),
    batch_size=100_000,
)

Важно заметить: только 1 товар из 1 000 действительно был отредактирован.

Теперь посмотрим на индексы:

catalog=# \di+ *product*
 Schema │                  Name                        │  Size
────────┼──────────────────────────────────────────────┼─────────
 public │ catalog_product_created_by_id_4e458b98       │ 6440 kB
 public │ catalog_product_last_edited_by_id_05484fb6   │ 6320 kB
 public │ catalog_product_pkey                         │ 21 MB
 public │ product_category_sort_order_uk               │ 32 MB

И тут обнаруживается странность. Если вы не заметили её сразу — не страшно, большинство тоже не замечает.

Возьмём запрос, который проверяет количество пользователей в created_by и last_edited_by:

catalog=# SELECT
    COUNT(created_by_id) AS created_by,
    COUNT(last_edited_by_id) AS last_edited_by
FROM catalog_product;

 created_by │ last_edited_by
────────────┼────────────────
    1000000 │           1000

Из миллиона строк у всех товаров заполнен created_by, но только у тысячи — last_edited_by. То есть примерно 99,9% значений пустые! Тогда почему индексы для этих двух полей одинакового размера?

Оба занимают примерно 6 МБ, хотя один индекс содержит миллион значений, а другой — всего тысячу. Причина в том, что в PostgreSQL null-значения тоже индексируются!

Вывод: null-значения индексируются (во всех популярных СУБД, кроме Oracle).

Я начинал карьеру администратором Oracle, где null не индексируются. Ушло немало времени (и дорогого дискового пространства), прежде чем я понял, что в PostgreSQL null-значения попадают в индекс.

У нас есть внешний клю��, который в основном нужен для проверки ограничений, но он на 99,9% пустой. Что если индексировать только непустые значения? В документации PostgreSQL по «partial index» сказано:

Частичный индекс — это индекс, построенный над подмножеством таблицы, определяемым условием […]. В индекс попадают только те строки таблицы, которые удовлетворяют условию.

Именно то, что нужно! Заменим индекс на ForeignKey на частичный B-Tree индекс:

@@ -22,6 +22,13 @@ class Product(models.Model):
    class Meta:
+        indexes = (
+            models.Index(
+                name='product_last_edited_by_part_ix',
+                fields=('last_edited_by',),
+                condition=models.Q(last_edited_by__isnull=False),
+            ),
+        )
@@ -53,5 +60,7 @@ class Product(models.Model):
     last_edited_by: User | None = models.ForeignKey(
         on_delete=models.PROTECT,
         related_name='+',
         null=True,
+        # Indexed in Meta.
+        db_index=False,
     )

Для начала ставим db_index=False, чтобы указать Django, что стандартный индекс нам не нужен. Добавляем комментарий в Meta, поясняющий, что поле индексируется отдельно.

Далее добавляем новый индекс в Meta. Его частичность определяется условием в определении индекса: включать только строки, где значение не равно null:

models.Index(
    name='product_last_edited_by_part_ix',
    fields=('last_edited_by',),
    condition=models.Q(last_edited_by__isnull=False),
)

После генерации и применения миграции размеры индексов выглядят так:

catalog=# \di+ *product*
 Schema │                  Name                  │  Size
────────┼────────────────────────────────────────┼─────────
 public │ catalog_product_created_by_id_4e458b98 │ 6440 kB
 public │ product_last_edited_by_part_ix         │ 32 kB
 public │ catalog_product_pkey                   │ 21 MB
 public │ product_category_sort_order_uk         │ 32 MB

Полный индекс — ~6,4 МБ. Частичный индекс — всего 32 КБ. Экономия ~99,5%!

Вывод: используйте частичные индексы, когда нет необходимости индексировать все значения в столбце. Nullable-внешние ключи — отличные кандидаты.

Использование встроенных параллельных операций с индексами

В предыдущем разделе мы смогли неплохо сэкономить $$$, перейдя на частичный индекс. Но в азарте мы забыли одну крайне важную вещь — всегда проверяйте сгенерированный SQL перед применением миграций!

Вот наша миграция:

class Migration(migrations.Migration):
    dependencies = [
        ('catalog', '0005_alter_product_created_by'),
        migrations.swappable_dependency(settings.AUTH_USER_MODEL),
    ]

    operations = [
        migrations.AlterField(
            model_name='product',
            name='last_edited_by',
            field=models.ForeignKey(
                db_index=False,
                null=True,
                on_delete=django.db.models.deletion.PROTECT,
                related_name='+',
                to=settings.AUTH_USER_MODEL
            ),
        ),
        migrations.AddIndex(
            model_name='product',
            index=models.Index(
                condition=models.Q(('last_edited_by__isnull', False)),
                fields=['last_edited_by'],
                name='product_last_edited_by_part_ix',
            ),
        ),
    ]

Выглядит нормально. Но SQL:

$ ./manage.py sqlmigrate catalog 0006
BEGIN;
--
-- Alter field last_edited_by on product
--
SET CONSTRAINTS "catalog_product_last_edited_by_id_05484fb6_fk_auth_user_id" IMMEDIATE;
ALTER TABLE "catalog_product" DROP CONSTRAINT "catalog_product_last_edited_by_id_05484fb6_fk_auth_user_id";
ALTER TABLE "catalog_product" ADD CONSTRAINT "catalog_product_last_edited_by_id_05484fb6_fk_auth_user_id"
    FOREIGN KEY ("last_edited_by_id") REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED;
--
-- Create index product_last_edited_by_part_ix on field(s) last_edited_by of model product
--
DROP INDEX IF EXISTS "product_last_edited_by_part_ix";
CREATE INDEX "product_last_edited_by_part_ix" ON "catalog_product" ("last_edited_by_id")
    WHERE "last_edited_by_id" IS NOT NULL;
COMMIT;

О нет! Он снова пересоздаёт внешний ключ с нуля. Мы уже знаем, что делать — использовать SeparateDatabaseAndState, чтобы удалить только индекс:

@@ -13,10 +13,20 @@ class Migration(migrations.Migration):
operations = [
-     migrations.AlterField(
-         model_name='product',
-         name='last_edited_by',
-         field=models.ForeignKey(db_index=False, null=True, on_delete=django.db.models.deletion.PROTECT, # ...
+     migrations.operations.SeparateDatabaseAndState(
+         state_operations=[
+             migrations.AlterField(
+                 model_name='product',
+                 name='last_edited_by',
+                 field=models.ForeignKey(db_index=False, null=True, on_delete=django.db.models.deletion.PROTECT, # ...
+             ),
+         ],
+         database_operations=[
+             migrations.RunSQL(
+                 'DROP INDEX CONCURRENTLY catalog_product_last_edited_by_id_05484fb6;',
+                 'CREATE INDEX CONCURRENTLY catalog_product_last_edited_by_id_05484fb6 ON public.catalog_product USING btree (last_edited_by_id)',
+             ),
+         ],

Теперь миграция удалит индекс, а не будет пересоздавать ограничение.

Далее мы хотим создать частичный индекс параллельно, чтобы не мешать работе живой системы. До этого мы использовали RunSQL для параллельных операций, но в этот раз можно воспользоваться одной из встроенных операций Django:

@@ -3,9 +3,11 @@
+from django.contrib.postgres.operations import AddIndexConcurrently

 class Migration(migrations.Migration):
+    atomic = False

     dependencies = [
         ('catalog', '0005_alter_product_created_by'),
@@ -28,7 +30,7 @@ class Migration(migrations.Migration):
                 ),
             ],
         ),
-        migrations.AddIndex(
+        AddIndexConcurrently(
             model_name='product',
             index=models.Index(
                condition=models.Q(('last_edited_by__isnull', False)),
                fields=['last_edited_by'],
                name='product_last_edited_by_part_ix',
            ),
         ),

Django предлагает два специальных «drop-in» класса для параллельных операций с индексами в PostgreSQL:

AddIndex → AddIndexConcurrently
RemoveIndex → RemoveIndexConcurrently

Эти операции работают только с PostgreSQL. Для других баз по-прежнему придётся использовать RunSQL.

Почему мы не могли использовать их раньше для неявного индекса на ForeignKey? Потому что эти операции применимы только к индексам, определённым в Meta.indexes, а не к тем, что создаются Django неявно или вообще вне его контекста.

Упорядочивание операций миграции

Мы уже добились того, что миграция больше не пересоздаёт внешний ключ и все операции с индексами выполняются параллельно. Вот текущая миграция:

class Migration(migrations.Migration):
    atomic = False

    dependencies = [
        ('catalog', '0005_alter_product_created_by'),
        migrations.swappable_dependency(settings.AUTH_USER_MODEL),
    ]

    operations = [
        migrations.operations.SeparateDatabaseAndState(
            state_operations=[
                migrations.AlterField(model_name='product', name='last_edited_by', field=models.ForeignKey(db_index=False, null=True, on_delete=django.db.models.deletion. #...
            ],
            database_operations=[
                migrations.RunSQL(
                    'DROP INDEX catalog_product_last_edited_by_id_05484fb6;',
                    'CREATE INDEX catalog_product_last_edited_by_id_05484fb6 ON public.catalog_product USING btree (last_edited_by_id)',
                ),
            ],
        ),
        AddIndexConcurrently(
            model_name='product',
            index=models.Index(condition=models.Q(('last_edited_by__isnull', False)), fields=['last_edited_by'], # ...
        ),
    ]

Если присмотреться внимательнее, она всё ещё может повлиять на работу живой системы. Посмотрим на порядок операций:

Удаляется полный индекс.
Создаётся частичный индекс.

Между первым и вторым шагом система остаётся без индекса. И нужно помнить: мы используем параллельные операции, поэтому миграция стала неатомарной. Это значит, что изменения вступают в силу сразу, а не в конце миграции. Такой порядок может привести к двум последствиям:

С момента удаления полного индекса и до создания частичного система работает без индекса, что может замедлить запросы.
Если миграция упадёт между первым и вторым шагом, таблица останется без индекса до следующей попытки применить миграцию.

Решение этой головной боли простое: поменять порядок операций:

@@ -15,6 +15,10 @@ class Migration(migrations.Migration):
     operations = [
+        AddIndexConcurrently(
+            model_name='product',
+            index=models.Index(condition=models.Q(('last_edited_by__isnull', False)), fields=['last_edited_by'], #...
+        ),
         migrations.operations.SeparateDatabaseAndState(
             state_operations=[
                 migrations.AlterField(
@@ -30,8 +34,4 @@ class Migration(migrations.Migration):
                 ),
             ],
         ),
-        AddIndexConcurrently(
-            model_name='product',
-            index=models.Index(condition=models.Q(('last_edited_by__isnull', False)), fields=['last_edited_by'], #...
-        ),
     ]

Сначала создаём частичный индекс, а затем удаляем полный. Таким образом система никогда не остаётся без индекса, а если миграция оборвётся на любом этапе, у нас всё равно будет хотя бы один индекс.

Вывод: меняйте порядок операций миграции, чтобы снизить влияние на работающую систему. Обычно надёжнее сначала создавать, а потом удалять.

На этом часть про миграции завершается, переходим к бизнес-логике!

Блокировки между связями

Допустим, мы хотим добавить возможность редактировать товар с такими требованиями:

Пользователь может обновить название и описание товара.
Нужно сохранять информацию о пользователе, который последний редактировал товар.
Только суперпользователи могут редактировать товары, созданные другими суперпользователями.

Наивная реализация может выглядеть так:

class Product(models.Model):
    # ...
    def edit(self, *, name: str, description: str, edited_by: User) -> None:
        if self.created_by.is_superuser and not edited_by.is_superuser:
            # Only superusers can edit products created by other superusers.
            raise errors.NotAllowed()

        self.name = name
        self.description = description
        self.last_edited_by = edited_by
        self.save()

Функция редактирования реализуется как метод экземпляра модели Product. Она принимает новые название и описание, а также пользователя, выполняющего редактирование. Выполняется проверка прав, после чего изменяются поля и вызывается save().

В некоторых случаях этого достаточно, и можно на этом остановиться. Но бездумно работать с уже загруженным объектом не всегда правильно:

Копия объекта в памяти может быть устаревшей.
Такой подход не защищён от конкурентных изменений.

Более надёжное решение с пессимистическим подходом может выглядеть так:

from django.db import transaction

@classmethod
def edit(cls, id: int, *, name: str, description: str, edited_by: User) -> Self:
    with transaction.atomic():
        product = (
            cls.objects
            .select_for_update()
            .get(id=id)
        )

        if product.created_by.is_superuser and not edited_by.is_superuser:
            # Only superusers can edit products created by other superusers.
            raise errors.NotAllowed()

        product.name = name
        product.description = description
        product.last_edited_by = edited_by
        product.save()

    return product

Главные отличия:

Используем classmethod вместо instance method: с методом экземпляра мы работаем с уже загруженным объектом. Чтобы получить блокировку строки, нужно контролировать процесс её выборки, поэтому используем метод класса.
Блокировка строки: для получения блокировки нужно работать внутри транзакции. Чтобы реально её установить, применяем select_for_update, что превращается в SQL-запрос SELECT ... FOR UPDATE.

Вся эта история с блокировками может отвлекать настолько, что легко упустить самую очевидную оптимизацию в Django — ту, что есть в каждом списке «10 лучших оптимизаций». Речь, конечно, о могучем select_related!

Чтобы проверить права, мы обращаемся к пользователю, создавшему товар:

if product.created_by.is_superuser and not edited_by.is_superuser:
    # Only superusers can edit products created by other superusers.
    raise errors.NotAllowed()

Пользователь заранее не загружен, и при обращении Django делает отдельный запрос в базу. Но мы знаем заранее, что он нам нужен, и можем сказать Django загрузить его вместе с товаром с помощью select_related:

product = (
    cls.objects
    .select_for_update()
    .select_related('created_by')
    .get(id=id)
)

Это уменьшает количество запросов на один. Однако здесь скрыта тонкость, которую легко не заметить.

Рассмотрим ситуацию с двумя параллельными сессиями:

>>> # Session 1
>>> with transaction.atomic():
...     product = (
...          Product.objects
...         .select_for_update()
...         .select_related('created_by')
...         .get(id=1)
...     )
...
...
...     # transaction is still ongoing...

>>> # Session 2
>>>
>>>
>>>
>>>
>>>
>>> u = User.objects.get(id=97)
>>> u.is_active = False
>>> u.save()
# Blocked!

В сессии 1 идёт редактирование товара. В это время сессия 2 пытается обновить пользователя — и блокируется. Почему сессия 2, которая вовсе не редактировала товар, оказалась заблокированной?

Причина в том, что SELECT ... FOR UPDATE блокирует строки во всех таблицах, участвующих в запросе! В нашем случае при использовании select_related в запрос добавился JOIN, чтобы выбрать и товар, и его автора. В результате блокируются строки как из таблицы product, так и из таблицы user. Если кто-то попытается обновить пользователя, в то время как мы редактируем созданный им товар, он окажется заблокирован.

Неявное поведение: по умолчанию select_for_update блокирует строки из всех таблиц, участвующих в запросе.

Чтобы этого избежать, можно явно указать, какие таблицы нужно блокировать, используя select_for_update:

@@ -80,6 +80,7 @@ class Product(models.Model):
    product: Self = (
        cls.objects
        .select_related('created_by')
+       .select_for_update(of=('self', ))
        .get(id=id)
    )

self — это специальное ключевое слово, которое указывает на модель queryset-а, в нашем случае product.

Вывод: всегда явно указывайте, какие таблицы нужно блокировать при использовании select_for_update. Даже если сейчас у вас нет select_related, он может появиться в будущем.

Разрешающие блокировки без ключей

Ранее мы использовали select_for_update(of=('self', )), чтобы при редактировании блокировался только товар, а не пользователь, его создавший. Теперь рассмотрим другую ситуацию. На этот раз мы хотим добавить новый товар:

>>> Product.objects.create(
...     name='my product',
...     description='a lovely product',
...     category_id=1,
...     category_sort_order=999997,
...     created_by_id=1,
...     last_edited_by_id=None,
... )
<Product 1000001>

Это работает.

Теперь сделаем то же самое, но в момент, когда другая сессия обновляет пользователя с id=1:

>>> # Session 1
>>> with transaction.atomic():
...   user = (
...     User.objects
...     .select_for_update(of=('self',))
...     .get(id=1)
...   )
...   # transaction is still ongoing...

>>> # Session 2
>>>
>>>
>>>
>>>
>>>
>>>
>>> Product.objects.create(
...   name='my product',
...   description='a lovely product',
...   category_id=1,
...   category_sort_order=999998,
...   created_by_id=1,
...   last_edited_by_id=None,
... )
... # Blocked!

Сессия 1 блокирует пользователя 1 для обновления. В это же время сессия 2 пытается вставить новый товар, созданный этим пользователем, и… блокируется! Почему это происходит?

Представьте, что вы — база данных. Одна сессия блокирует пользователя, другая пытается на него сослаться. Как быть уверенным, что первая сессия не изменит первичный ключ этого пользователя? Как быть уверенным, что она не удалит пользователя? Если сессия 1 собирается сделать одно из этих действий, то сессия 2 должна завершиться с ошибкой. Именно поэтому база данных вынуждена заблокировать сессию 2 до завершения сессии 1.

Оба этих сценария теоретически возможны, но на практике случаются крайне редко. Как часто вы меняете первичный ключ объекта или уникальное поле, на которое ссылается внешний ключ? Практически никогда. В PostgreSQL есть способ сообщить об этом базе и получить более «мягкую» блокировку при выборке для обновления — с помощью FOR NO KEY UPDATE:

>>> # Session 1
>>> with transaction.atomic():
...   user = (
...     User.objects
...     .select_for_update(no_key=True, of=('self',))
...     .get(id=1)
...   )
...   # transaction is still ongoing...

>>> # Session 2
>>>
>>>
>>>
>>>
>>>
>>>
>>> Product.objects.create(
...   name='my product',
...   description='a lovely product',
...   category_id=1,
...   category_sort_order=999998,
...   created_by_id=1,
...   last_edited_by_id=None,
... )
... <Product 1000002>

Устанавливая no_key=True, мы говорим базе данных: мы не собираемся обновлять первичный ключ пользователя. Тогда база может применить менее строгую блокировку, и вторая сессия спокойно создаст товар.

Вывод: используйте select_for_update(no_key=True), если обновляете строку, но не меняете её первичный ключ или уникальные поля, на которые ссылаются внешние ключи. Это позволит базе применять более щадящую блокировку и избежать ненужных конфликтов при создании связанных объектов.

Вернёмся к нашей модели Product. Для редактирования товара мы использовали select_for_update, чтобы защититься от конкурентных изменений. Но вместе с этим мы невольно блокировали создание связанных объектов. В случае Product это может критически повлиять на систему.

Представьте, что у вас интернет-магазин на этом каталоге. Когда пользователь оформляет покупку, создаётся объект Order, который ссылается на товар. А теперь представьте, что каждый раз, когда кто-то обновляет товар, система не может создавать заказы. Это недопустимо!

Чтобы позволить системе создавать заказы, пока товар обновляется, добавляем no_key=True:

--- a/demo/catalog/models.py
+++ b/demo/catalog/models.py
@@ -80,7 +80,7 @@ class Product(models.Model):
             product: Self = (
                 cls.objects
                 .select_related('created_by')
-                .select_for_update(of=('self', ))
+                .select_for_update(of=('self', ), no_key=True)
                 .get(id=id)
             )

Теперь мы можем безопасно редактировать товар, не мешая работе живой системы.

Итоговая модель

Мы проделали немалый путь от наивной реализации, с которой начинали, и вот перед вами финальная версия модели:

class Product(models.Model):
    class Meta:
        constraints = [
            models.UniqueConstraint(
                name='product_category_sort_order_uk',
                fields=(
                    'category',
                    'category_sort_order',
                ),
            ),
        ]
        indexes = (
            models.Index(
                name='product_last_edited_by_part_ix',
                fields=('last_edited_by',),
                condition=models.Q(last_edited_by__isnull=False),
            ),
        )

    id = models.BigAutoField(
        primary_key=True,
    )
    name = models.CharField(
        max_length=50,
    )
    description = models.TextField()

    category = models.ForeignKey(
        to=Category,
        on_delete=models.PROTECT,
        related_name='products',
        # Indexed in unique constraint.
        db_index=False,
    )
    category_sort_order = models.IntegerField()

    created_by = models.ForeignKey(
        to=User,
        on_delete=models.PROTECT,
        related_name='+',
        # Used to speed up user deletion.
        db_index=True,
    )

    last_edited_by = models.ForeignKey(
        to=User,
        on_delete=models.PROTECT,
        related_name='+',
        null=True,
        # Indexed in Meta.
        db_index=False,
    )

    @classmethod
    def edit(
        cls,
        id: int,
        *,
        name: str,
        description: str,
        edited_by: User,
    ) -> Self:
        with db_transaction.atomic():
            product: Self = (
                cls.objects
                .select_related('created_by')
                .select_for_update(of=('self', ), no_key=True)
                .get(id=id)
            )

            if product.created_by.is_superuser and not edited_by.is_superuser:
                # Only superusers can edit products created by other superusers.
                raise errors.NotAllowed()

            product.name = name
            product.description = description
            product.last_edited_by = edited_by
            product.save()

        return product

Эта модель и сопутствующие миграции получились безопасными, устойчивыми и, что самое главное, готовыми к работе в продакшене!

Русскоязычное сообщество про Python

Друзья! Эту статью перевела команда Python, PyCharm и DevTools — канала, где каждый день выходят самые свежие и полезные материалы о Python и его экосистеме. Подписывайтесь, чтобы ничего не пропустить!

Итоги

Вот краткое резюме ключевых выводов из статьи:

Не используйте unique_together, вместо него используйте UniqueConstraint.
Всегда явно задавайте db_index и добавляйте комментарий.
Всегда проверяйте SQL, который генерируют миграции, прежде чем их применять.
По возможности указывайте обратные операции миграций.
В нагруженных системах используйте параллельные операции с индексами.
Индексы на внешних ключах косвенно используются при удалениях.
Используйте частичные индексы, когда это возможно.
Меняйте порядок операций миграции, чтобы снизить влияние на живую систему.
Явно указывайте, какие таблицы нужно блокировать при использовании select_for_update.
Используйте no_key=True при выборке строки для обновления, чтобы можно было создавать

Эволюция ForeignKey в Django: от наивной модели до продакшена