Внешние ключи - это ключевая функция реляционных баз данных, обеспечивающая целостность и согласованность данных. Они позволяют выполнять транзакции НА КАСКАДЕ, что означает, что также применяются изменения первичного ключа / уникального ограничения, на которое они ссылаются. Это дает много преимуществ по мере роста сложности базы данных.

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

В этом посте мы собираемся сравнить различные альтернативы ограничению ON CASCADE и их производительность.

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

CREATE TABLE public.users ( 
  user_email varchar PRIMARY KEY, 
  name varchar, 
  last_name varchar, 
  city varchar 
); 
CREATE TABLE public.pictures ( 
  id_picture serial PRIMARY KEY, 
  user_email varchar REFERENCES public.users(user_email) ON UPDATE CASCADE, 
  filename varchar, 
  title varchar 
);

Предположим, у нас уже 10 пользователей, и каждый из них загрузил 200 тысяч изображений. Затем один пользователь хочет изменить адрес электронной почты (с [email protected] на [email protected]). Поскольку user_email является ПЕРВИЧНЫМ КЛЮЧОМ, на который ссылаются, для обновления электронной почты нам необходимо выполнять одновременные обновления, а мы просто не можем:

UPDATE users -- or pictures 
SET user_email = '[email protected]' 
WHERE user_email = '[email protected]'

У нас есть несколько альтернатив:

  1. Используйте запросы WITH.
  2. Используйте вспомогательный новый поддельный адрес электронной почты.
  3. ON UPDATE CASCADE и просто запустите вышеуказанный запрос.

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

Можно подумать, что это всего лишь две таблицы, но представьте, что у вас есть другие таблицы с тем же внешним ключом (например, комментарии, реакции, связи и т. Д.). Вам нужно будет обновить каждую таблицу. Более того, для каждой новой таблицы вам нужно будет добавлять ее в обновлениях. С другой стороны, объявив ON UPDATE CASCADE во внешнем ключе, вы можете забыть об этом и просто выполнить обновление в таблице пользователей.

Но как насчет производительности? Мы провели несколько тестов с разным количеством изображений (200К, 1М, 5М):

200K 1M 5M с 2,5 с 18,2 с 93 с ДОПОЛНИТЕЛЬНАЯ ПОЧТА 6,1 с 31,4 с 187 с КАСКАД 2,6 с 16 с 86 с

Как и ожидалось, использование вспомогательной почты занимает слишком много времени, потому что мы применяем дважды обновление, поэтому этот параметр не используется. Можно сказать, что нет существенных различий между запросами with и ON CASCADE, но использование последнего проще и позволяет адаптировать код каждый раз, когда мы добавляем новые таблицы.

Проверьте код здесь!

By Andreu Jiménez