Комментарии 12
У OLTP все же скорость вставки вторична. Жертвовать контролем целостности можно только если вы ну очень хорошо понимаете что делаете
Скорее, зависит от факта, так ли уж ценна эта самая целостность.
То есть при всем желании, никакой FK не сможет запретить разработчику "перепутать" значение ключевого поля и банально привязать заказ не к тому клиенту. Хотя сработка ошибки по FK, безусловно, может иногда помочь заметить подобный баг "извне".
в одной очень крупной компании, в которой я работал, FK вообще были запрещены регламентом.
Спасибо за интересный материал.
Добавлю, что пришёл к выводу, что внешние ключи хороши только для информирования кто на что ссылается, но вредны при вставках и изменениях данных по этим полям, а также при репликациях (конфликты обменов) и удалении.
По этому поводу лучше целостность проверять на серверном слое всего решения ПО или сервиса, а внешние ключи делать, но выключенными, только для понимания кто на что ссылается. Также в качестве полей для первичных и вторичных ключей в OLTP лучше брать искуственные, а не естественные данные (целое число или гуид), значения которых последовательно растут даже с учётом распределённого решения (разделение диапазонов или добавление поля-идентификатора экземпляра БД/СУБД). Индексы строятся по нужным запросам как по естественным полям, так и по системным. И напомню, что кластерный индекс не одно и тоже, что первичный ключ. Т е при необходимости кластерный индекс можно сделать по естественным полям для скорости, а не кластерный по системным для связи.
Физическое удаление данных лучше проводить отложено, а сначала делать логическое удаление, помечая данные на физическое удаление. И потом в фоне удалить ненужные данные с архивации или без по ситуации и по требованию хранения и обработки данных.
Тема общая для реляционных БД. Создавать индексы на внешние ключи должно быть железным правилом разработчика БД, имхо. Впрочем как и отключение констрейнтов и триггеров при необходимости быстрой вставки и гарантировнной валидности данных либо возможности отложенной проверки после их заливки.
Честно говоря, практически всегда, поскольку таблицы вяжутся в запросах по этим полям, да зачастую эти поля в условии where участвуют. Да и полстатьи собственно ведь о том, как работает запрос с внешним ключом без индекса. Никак.
Краткая выжимка статьи: Внешние ключи - зло (нужны лишь для удобства человека). Триггеры - зло (нет, они вам не нужны). Индексы - хорошо (в меру, конечно). Postgre не создаёт индексы на внешних ключах. Инструменты Тензор имеют прикольные визуализации и расшифровки планов запросов (можно не прокачивать себе черный пояс по postgre, чтобы понимать, что творится с твоими запросами).
То есть при вставке каждой нашей записи сервер идет в основную таблицу, находит запись с искомым ключом (по индексу, конечно) и вешает на нее FOR KEY SHARE-блокировку, чтобы никто в ней не успел сменить значение первичного ключа, пока мы тут вставляем остальное.
Интересно, зачем тут вообще делать блокировку (по крайней мере, если дело идет на уровне изоляции транзакций SNAPSHOT)? Изменение первичного ключа произойдет в другой копии, между транзакциями получится отложенный конфликт из-за нарушения ссылочной целостности, а вот как он будет разрешен конкретно — это от порядка закрытия транзакций зависит: короче, какая-то из них не сможет завершиться и будет откачена.
Насколько я помню, в древнем Interbase, где хранилище тоже поддерживало многоверсионность записей, это было бы именно так, и что мешает сделать это в Posgtres — как-то мне непонятно. Единственно, что приходит в голову — длительное время закрытия при FK без индекса (у Interbase индекс для FK создавался автоматом).
Интересно, зачем тут вообще делать блокировку (по крайней мере, если дело идет на уровне изоляции транзакций SNAPSHOT)?
Однако, режим по умолчанию - READ COMMITTED, поведение при котором и показано, допускает неповторяемое чтение.
А SNAPSHOT соответствует уже более высокому REPEATABLE READ:
Для реализации уровня изоляции Repeatable Read применяется подход, который называется в академической литературе по базам данных и в других СУБД Изоляция снимков (Snapshot Isolation). По сравнению с системами, использующими традиционный метод блокировок, затрудняющий параллельное выполнение, при этом подходе наблюдается другое поведение и другая производительность. В некоторых СУБД могут существовать даже два отдельных уровня Repeatable Read и Snapshot Isolation с различным поведением.
Возможно, установка блокировки на значение первичного ключа в Postgres — это наследие тех времен, когда его хранилище ещё не поддерживало многоверсионность (она там появилась далеко не сразу).
Так или иначе, но знать поведение реальной СУБД полезно само по себе. Благодарю за статью.
PostgreSQL Antipatterns: когда мешает внешний ключ