Внешние ключи (foreign keys) - мощный и удобный механизм контроля логической целостности данных в базе. Но он бывает не только лишь полезен, и может неплохо пригрузить вашу БД.

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

Традиционно, начнем с самой простой ситуации - пара табличек, одна на другую ссылается:

CREATE TABLE tblpk(
  k
    integer
      PRIMARY KEY
);

CREATE TABLE tblfk(
  k
    integer
      REFERENCES tblpk    -- эквивалентно tblpk(k), поскольку k - PK
        ON DELETE CASCADE -- "ничейные" записи нам не нужны
, v
    integer
);

Наполним их некоторыми данными:

INSERT INTO tblpk(k)
SELECT
  generate_series(1, 1e3); -- [1..1000]

INSERT INTO tblfk(k, v)
SELECT
  (random() * (1e3 - 1))::integer + 1 -- random = [0..1]
, (random() * 1e6)::integer
FROM
  generate_series(1, 1e6);

Медленный SELECT

А теперь попробуем самым примитивным запросом, через JOIN, для каждой записи tblpk из первого десятка найти максимальное значение tblfk.v:

SELECT
  k
, max(v)
FROM
  tblpk
JOIN
  tblfk
    USING(k)
WHERE
  k <= 10
GROUP BY
  k;

И... Parallel Seq Scan по миллиону записей tblfk - это совсем не то, чего бы хотелось:

Вычитываем миллион записей
Вычитываем миллион записей

К счастью, теперь, в продолжение темы про подсказки об упущенных индексах, наш сервис визуализации explain.tensor.ru научился различать не только условия на самом узле чтения (Seq Scan), но и стоящем выше него Hash Join.

В нашем примере каждый из 3 параллельных воркеров "свои" 333333 записи tblfk превращал в Hash Join в 3164 результирующую запись:

->  Hash Join (actual time=1.635..57.796 rows=3164 loops=3)
    Hash Cond: (tblfk.k = tblpk.k)
    Buffers: shared hit=8867
    ->  Parallel Seq Scan on tblfk (actual time=1.506..24.588 rows=333333 loops=3)
        Buffers: shared hit=8850

А если условие фильтрации для tblfk у нас получается известно (tblfk.k = tblpk.k), то нам ничто не мешает порекомендовать создать подходящий индекс:

Hash Join + Seq Scan = index
Hash Join + Seq Scan = index

Нам рекомендовано создать индекс:

CREATE INDEX CONCURRENTLY "~tblfk-67ed26fd"
  ON tblfk(k);

Запомним это, но пока не будем его накатывать.

А заодно запомним, что PostgreSQL не создает автоматически индексы для внешних ключей.

UPDATE/DELETE "тупит"... на триггере?

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

Но вот нам захотелось удалить (или даже обновить, если речь идет о старых версиях PostgreSQL) запись из основной таблицы:

DELETE FROM
  tblpk
WHERE
  k = 1000;
Удаление по foreign key
Удаление по foreign key

Оу... подсказка сразу акцентирует наше внимание, что 99.9% всего времени ушло вовсе не на выполнение запроса, а на Trigger for constraint tblfk_k_fkey.

Помните ON DELETE CASCADE в начале? Вот это он и есть - отработка внешнего ключа через триггер.

Давайте включим auto_explain и пристально посмотрим в лог сервера на аналогичном запросе:

LOAD 'auto_explain';
SET auto_explain.log_analyze = 'on';
SET auto_explain.log_buffers = 'on';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_nested_statements = 'on';
SET auto_explain.log_timing = 'on';
SET auto_explain.log_triggers = 'on';

DELETE FROM
  tblpk
WHERE
  k = 999;
2022-05-11 15:02:44.196 MSK [17696] LOG:  duration: 264.759 ms  plan:
  Query Text: DELETE FROM ONLY "public"."tblfk" WHERE $1 OPERATOR(pg_catalog.=) "k"
  Delete on tblfk  (cost=0.00..16925.00 rows=996 width=6) (actual time=264.757..264.757 rows=0 loops=1)
    Buffers: shared hit=6252 dirtied=1279
    ->  Seq Scan on tblfk  (cost=0.00..16925.00 rows=996 width=6) (actual time=0.181..143.802 rows=1016 loops=1)
          Filter: (999 = k)
          Rows Removed by Filter: 998497
          Buffers: shared hit=4425 dirtied=467
2022-05-11 15:02:44.196 MSK [17696] CONTEXT:  SQL statement "DELETE FROM ONLY "public"."tblfk" WHERE $1 OPERATOR(pg_catalog.=) "k""

Собственно, вот он и есть - виновник наших тормозов - "Seq Scan на миллион":

При удалении читаем миллион записей
При удалении читаем миллион записей

Логично, что к нему рекомендовано создание того же индекса - таки создадим же его:

CREATE INDEX CONCURRENTLY "~tblfk-67ed26fd"
  ON tblfk(k);

Ну, как там наше удаление теперь?

DELETE FROM
  tblpk
WHERE
  k = 1;
Удаление по индексированному FK
Удаление по индексированному FK

А вот теперь стало все отлично - 7ms вместо 95ms, поскольку удаление из tblfk теперь пользуется нашим индексом:

2022-05-11 15:13:16.566 MSK [17696] LOG:  duration: 3.166 ms  plan:
  Query Text: DELETE FROM ONLY "public"."tblfk" WHERE $1 OPERATOR(pg_catalog.=) "k"
  Delete on tblfk  (cost=12.14..2399.04 rows=996 width=6) (actual time=3.151..3.151 rows=0 loops=1)
    Buffers: shared hit=1550 dirtied=291
    ->  Bitmap Heap Scan on tblfk  (cost=12.14..2399.04 rows=996 width=6) (actual time=0.149..0.760 rows=531 loops=1)
          Recheck Cond: (1 = k)
          Heap Blocks: exact=508
          Buffers: shared hit=511
          ->  Bitmap Index Scan on "~tblfk-67ed26fd"  (cost=0.00..11.89 rows=996 width=0) (actual time=0.092..0.092 rows=531 loops=1)
                Index Cond: (k = 1)
                Buffers: shared hit=3
2022-05-11 15:13:16.566 MSK [17696] CONTEXT:  SQL statement "DELETE FROM ONLY "public"."tblfk" WHERE $1 OPERATOR(pg_catalog.=) "k""

INSERT совсем небыстр

Теперь-то у нас все хорошо? С индексом SELECT работает по нашим таблицам теперь быстро, UPDATE/DELETE - тоже, а как там поживает INSERT?

Восстановим удаленные нами записи в основной таблице:

INSERT INTO tblpk VALUES(1),(998),(999),(1000);

И докинем еще тысячу записей в дополнительную:

INSERT INTO tblfk(k, v)
SELECT
  (random() * (1e3 - 1))::integer + 1
, (random() * 1e6)::integer
FROM
  generate_series(1, 1e3);

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

2022-05-11 15:23:00.005 MSK [17696] LOG:  duration: 0.296 ms  plan:
  Query Text: SELECT 1 FROM ONLY "public"."tblpk" x WHERE "k" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
  LockRows  (cost=0.28..8.30 rows=1 width=10) (actual time=0.292..0.292 rows=1 loops=1)
    Buffers: shared hit=5 dirtied=1
    ->  Index Scan using tblpk_pkey on tblpk x  (cost=0.28..8.29 rows=1 width=10) (actual time=0.017..0.017 rows=1 loops=1)
          Index Cond: (k = 361)
          Buffers: shared hit=3
2022-05-11 15:23:00.005 MSK [17696] CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."tblpk" x WHERE "k" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"

То есть при вставке каждой нашей записи сервер идет в основную таблицу, находит запись с искомым ключом (по индексу, конечно) и вешает на нее FOR KEY SHARE-блокировку, чтобы никто в ней не успел сменить значение первичного ключа, пока мы тут вставляем остальное.

Неудивительно, что в плане запроса мы увидим все тот же несчастный триггер, обслуживающий foreign key, который и занял 90% всего времени:

Вставка с проверкой и блокировкой foreign key
Вставка с проверкой и блокировкой foreign key

Отсюда вывод: если вам необходимо вставлять много и быстро в PostgreSQL, то это вполне реально, но внешними ключами придется пожертвовать.

Подробнее о способах оптимизации записи в PostgreSQL можно почитать в статье "Пишем в PostgreSQL на субсветовой: 1 host, 1 day, 1TB" или расшифровке моего доклада "Массовая оптимизация запросов PostgreSQL".

Комментарии (12)


  1. Kwisatz
    11.05.2022 16:12
    +1

    Если нужно вставлять много и быстро то есть OLAP решения вроде TimescaleDB.
    У OLTP все же скорость вставки вторична. Жертвовать контролем целостности можно только если вы ну очень хорошо понимаете что делаете


    1. Kilor Автор
      11.05.2022 16:48

      Скорее, зависит от факта, так ли уж ценна эта самая целостность.

      То есть при всем желании, никакой FK не сможет запретить разработчику "перепутать" значение ключевого поля и банально привязать заказ не к тому клиенту. Хотя сработка ошибки по FK, безусловно, может иногда помочь заметить подобный баг "извне".


    1. Giperoglif
      13.05.2022 03:45
      +1

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


  1. jobgemws
    11.05.2022 20:24
    +1

    Спасибо за интересный материал.

    Добавлю, что пришёл к выводу, что внешние ключи хороши только для информирования кто на что ссылается, но вредны при вставках и изменениях данных по этим полям, а также при репликациях (конфликты обменов) и удалении.

    По этому поводу лучше целостность проверять на серверном слое всего решения ПО или сервиса, а внешние ключи делать, но выключенными, только для понимания кто на что ссылается. Также в качестве полей для первичных и вторичных ключей в OLTP лучше брать искуственные, а не естественные данные (целое число или гуид), значения которых последовательно растут даже с учётом распределённого решения (разделение диапазонов или добавление поля-идентификатора экземпляра БД/СУБД). Индексы строятся по нужным запросам как по естественным полям, так и по системным. И напомню, что кластерный индекс не одно и тоже, что первичный ключ. Т е при необходимости кластерный индекс можно сделать по естественным полям для скорости, а не кластерный по системным для связи.

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


  1. Valerdos_UA
    11.05.2022 22:02
    +3

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


    1. jobgemws
      11.05.2022 22:34
      -3

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


      1. Kilor Автор
        11.05.2022 22:52
        +3

        Иногда вместо индекса конкретно под FK можно использовать индекс, имеющий некоторую прикладную нагрузку.

        В первом примере для поиска максимумов v можно сделать индекс tblfk(k, v) - и для FK(k) он тоже подойдет.


  1. Valerdos_UA
    11.05.2022 22:59
    +2

    Честно говоря, практически всегда, поскольку таблицы вяжутся в запросах по этим полям, да зачастую эти поля в условии where участвуют. Да и полстатьи собственно ведь о том, как работает запрос с внешним ключом без индекса. Никак.


  1. TimsTims
    12.05.2022 10:49

    Краткая выжимка статьи: Внешние ключи - зло (нужны лишь для удобства человека). Триггеры - зло (нет, они вам не нужны). Индексы - хорошо (в меру, конечно). Postgre не создаёт индексы на внешних ключах. Инструменты Тензор имеют прикольные визуализации и расшифровки планов запросов (можно не прокачивать себе черный пояс по postgre, чтобы понимать, что творится с твоими запросами).


  1. mvv-rus
    13.05.2022 04:49

    То есть при вставке каждой нашей записи сервер идет в основную таблицу, находит запись с искомым ключом (по индексу, конечно) и вешает на нее FOR KEY SHARE-блокировку, чтобы никто в ней не успел сменить значение первичного ключа, пока мы тут вставляем остальное.

    Интересно, зачем тут вообще делать блокировку (по крайней мере, если дело идет на уровне изоляции транзакций SNAPSHOT)? Изменение первичного ключа произойдет в другой копии, между транзакциями получится отложенный конфликт из-за нарушения ссылочной целостности, а вот как он будет разрешен конкретно — это от порядка закрытия транзакций зависит: короче, какая-то из них не сможет завершиться и будет откачена.
    Насколько я помню, в древнем Interbase, где хранилище тоже поддерживало многоверсионность записей, это было бы именно так, и что мешает сделать это в Posgtres — как-то мне непонятно. Единственно, что приходит в голову — длительное время закрытия при FK без индекса (у Interbase индекс для FK создавался автоматом).


    1. Kilor Автор
      13.05.2022 08:19

      Интересно, зачем тут вообще делать блокировку (по крайней мере, если дело идет на уровне изоляции транзакций SNAPSHOT)?

      Однако, режим по умолчанию - READ COMMITTED, поведение при котором и показано, допускает неповторяемое чтение.

      А SNAPSHOT соответствует уже более высокому REPEATABLE READ:

      Для реализации уровня изоляции Repeatable Read применяется подход, который называется в академической литературе по базам данных и в других СУБД Изоляция снимков (Snapshot Isolation). По сравнению с системами, использующими традиционный метод блокировок, затрудняющий параллельное выполнение, при этом подходе наблюдается другое поведение и другая производительность. В некоторых СУБД могут существовать даже два отдельных уровня Repeatable Read и Snapshot Isolation с различным поведением.

      https://postgrespro.ru/docs/postgresql/14/transaction-iso


      1. mvv-rus
        13.05.2022 16:39

        READ COMMITED тоже вполне реализуется на базе снимков. В Interbase он точно был, а блокировок там ЕМНИП при нем не было.
        Возможно, установка блокировки на значение первичного ключа в Postgres — это наследие тех времен, когда его хранилище ещё не поддерживало многоверсионность (она там появилась далеко не сразу).

        Так или иначе, но знать поведение реальной СУБД полезно само по себе. Благодарю за статью.