Комментарии 51
Как говорил начальник нашего управления по разработке в 2001 году - что ты так долго базу проектируешь ? У меня ее студенты за 1 день распишут всю ! А ты тут копаешься уже месяц !
Согласен со статьей, пришлось взять себе кусок проекта, который писали по принципу тупого хранилища, очень сложно было привести к нормальному виду. Несмотря на все заверения, что лучше все проверки делать в коде на практике получается куча мусора в базе, от которого очень тяжело избавиться без потери полезных данных.
Люто поддерживаю!
Гарантия того, что ерунду в базу не получится вписать, кто бы и как бы не пытался это сделать - бесценна.
Сonstraints реально могут от больших проблем спасти.
Например, проверки в коде не спасут от ситуаций когда вносят изменения в базу "вручную" или каким-то, на коленке сляпанным, скриптом.
Сколько раз за свою карьеру подобные истории разгребал - не счесть...
Если их так вносят, значит уже существующие проверки не помогли и как-то исправлять ситуацию надо. Что вы будите делать, если и здесь заткнете возможности что-то исправить — в хекс редакторе править файлы БД?
А зачем в базу вносить данные, которых там быть не должно?
А что вы у меня-то спрашиваете? Я же как раз и спрашиваю, как вы их удалять оттуда будете, когда они там появятся, а всякие "умные" проверки на самом нижнем уровне не дадут этого сделать. Не говоря уже о том, что вон там ниже реальные случаи выбросов приводят. 640Kb хватит всем...
Если проверки будут - неверные данные там не появятся.
Если проверки будут на уровне выше, в приложении, работающем с базой — неверные данные также не появятся.
Появятся, если их случайно внесут не через приложение, а, например, скриптом.
А зачем давать доступ кому попало вносить что-то скриптом? Вам не кажется, что если вы вносите что-то скриптами, то вы уже вышли за рамки изначальных допущений и применять к этой ситуации те же критерии, что и к штатной работе, как-то некорректно.
Но даже в этом случае остается возможность все исправить обратно, выполнив еще один скрипт.
Не кому попало, а администратору БД.
Но мне не кажется, что администратор БД должен иметь возможность случайно внести в БД мусор из-за бага в скрипте. Проверки должны быть.
Если они мешают выполнить скрипт, надо проверить скрипт на баги, и если уж точно надо - временно отключить проверки.
Это как sudo в командной строке.
Объясните пример с "Когда ограничения стоят денег" и почему именно 1/5?
Всегда думал, что констрейнты — это и есть глупая база, а умная — это когда на птичьих языках внутри нее чего-то писать пытаются
Мне нужно было убедиться, что заказы не размещаются повторно.
Есть такой вариант использования версий:
https://github.com/cosmicpython/book/blob/master/chapter_07_aggregate.asciidoc#optimistic-concurrency-with-version-numbers
Честно говоря, мне показалось, что решение переусложнено. Автор там и сам пишет, что можно было бы обойтись select for update или уровнем serializable; да, при высокой нагрузке это действительно может вызвать проблемы, но, как мне кажется, бизнес от подобных проблем (продажи идут с такой скоростью, что СУБД не успевает их размещать) будет только счастлив. Ну и, опять же, в случае постгреса это обходится процедурой/триггером и advisory блокировкой.
Я столкнулся с тем, что когда в таблицах по нескольку десятков миллионов записей и сайт обрабатывает более 100 запросов в секунду, то при наличии в БД foreign key на этих больших таблицах сайт не может держать таких нагрузок. Поэтому я уже около 10 лет вообще не использую foreign key в postgres, а за консистентностью БД и каскадным удалением записей по зависимостям следит hibernate, и нет никакого мусора в БД.
Когда я еще использовал foreign key, при массивных апдейтах или удалениях приходилось дропать констрейнты, так как при их наличии апдейты выполнялись часами, я просто не дожидался их выполнения, а после апдейта снова накатывать констрейнты. Помучившись так, я просто решил от них отказаться вообще, и ни разу не пожалел.
В общем, в высоко нагруженных приложениях с большими БД надо понимать, что foreign key будет очень дорого стоить по производительности.
Ну, это мой личный опыт. Больше я никого не знаю кто вообще без foreign key работает с БД.
В этом примере что-то недосказанно. На производительность чтения FK плохо не влияет, но позволяет точнее оценить количество строк при join по FK. На скорость изменений данных влияет, но только если менять значения столбцов FK или PK/UK. Есть случаи, когда блокировки в БД должны сильно распространиться по связанным по FK таблицам, но этот эффект можно минимизировать.
В той системе запросы написаны руками или ORM? Вы просто пишете, что ORM следит за консистентностью при каскадных операциях, но если не принять нужных мер (индексы для FK), то и ORM тоже начнёт тормозить.
Есть много апдейтов коллекций (типа по связи one-to-many), это транслируется в delete и insert SQL запросы тут то FK и тормозит. При чтении из БД, конечно же, FK вообще не используется.
Для настройки ORM чтобы работал каскад не надо FK, надо просто прописать в конфиге, как будет работать каскад (типа cascade="all" или cascade="delete-orphan" и тп), для скорости достаточно индексов, для джойнов FK вообще не нужен.
И postgres совсем по другому выполняет каскад чем ORM. Posgres делает кучу локов и часто можно увидеть что процесс waiting for lock, когда много параллельных обновлений идёт, а ORM просто разбивает эти апдейты и удаления записей на несколько SQL запросов и не делает никаких локов, за счёт этого и выигрыш.
Если чего то не договариваю, то спрашивайте, я дополню.
но позволяет точнее оценить количество строк при join по FK
можно узнать откуда эта инфа?
Вообще оценить число строк при join очень непросто, а знание, что есть связь по FK, сильно упрощает. Если две таблицы связаны по FK, то кардинальность пересечения множеств известна заранее. Проверил, похоже в postgres нет этой оптимизации.
А в какой СУБД, например, это есть?
Мне просто сам принцип непонятен, как FK может помочь при джойне. Я понимаю индексы - это да, я понимаю что в СУБД индексы могут автоматически создаваться при создании FK, но чтобы FK без индекса как-то помогал такого не знаю.
Ну подумайте - при соединении можно утверждать, что для вот этой строки точно найдется другая и причем только одна.
Но чтобы эту "только одну" быстро найти нужен именно индекс, а не FK. В этом можно убедиться если посмотреть план выполнения запроса, что используется именно индекс, а не FK. Или нет?
Причем тут индекс вообще? Это нужно для оценки кардинальности соединения, до индексов еще далеко.
Ну, про индекс написано в плане выполнения запроса, если он есть то будет index scan и будет быстро, если его нет будет seq scan и будет медленно. А вот наличие или отсутствие FK ни как не влияет на быстро/медленно. Или я не прав?
Прежде чем будет выбран какой-то план, необходимо построить все возможные планы (ну там сложнее, конечно, в общем случае) и оценить их стоимость.
Так что это влияет на точность оценки количества строк при соединении; а это уже влияет на точность выбора оптимального плана оптимизатором; так что да, влияет.
Разумеется, это не гарантия того, что все будет отлично или даже что оптимизатор выберет действительно оптимальный план, но тем не менее.
Если эта информация не Ваш домысел, а из документации Postgres, то скиньте ссылку пожалуйста, просвещусь. Я реально не знаю о том и не понимаю, как наличие FK может ускорить join.
Не "ускорить join". Кажется, Вы не понимаете, о чем речь.
Есть статья на тему роли FK при планировании join, но PG там не в лидерах
https://blog.jooq.org/join-elimination-an-essential-optimiser-feature-for-advanced-sql-usage/
Хотя FK не для этого нужны, конечно
Да что далеко ходить, есть цикл статей моего коллеги, там и это рассматривается, причем прямо здесь
https://habr.com/ru/company/postgrespro/blog/579024/
Отдельно очень рекомендую его книжку, https://postgrespro.ru/education/books/internals
Вероятно вам не хватало индексов. Скорее всего вы удаляете запись на которую есть внешний ключ - в таблицах с внешним ключом на эту запись должен быть индекс по ключевой колонке, чтобы база данных могла быстро проверить консистентность операции.
Ресурсы для проведения проверок требуются, но по моему опыту они не дают даже удвоения нагрузки на CPU (в среднем по больнице).
Иногда, вероятно, действительно можно столкнуться с ситуацией, когда отключение проверок дает значительный выигрыш. В этом случае можно отключить проверку целостности на время проведения транзакции (нет нужды удалять FK, достаточно дать команду ALTER TABLE tablename DISABLE TRIGGER ALL): такой код нужно писать особенно внимательно и наиболее широко покрывать тестами.
Я в целом предпочитаю таких конструкций избегать. Лучше добавить побольше серверов, а такие хаки применять только если не осталось иных решений.
Не, с индексами всё в порядке. Если бы не хватало индексов, то тормозили бы запросы select.
Тормозит такой кейс, например, я удаляю топик на форуме, а в нем 1000 комментов, они тоже должны удалиться. Если каскад обработает БД это будет очень медленно по сравнению с двумя запросами (delete from topics where id=10, и delete from comments where topic_id=10).
Я активно использовал FK пока не начал использвать ORM. На мой взгляд, ORM позволяет жить спойно и без FK.
Давайте попробуем собрать воспроизводимый тест-кейс! Каскад БД или два запроса - не имеет значения, с точки зрения производительности - у нас внешний ключ либо проверяется, либо нет. Соответственно нужно смотреть на производительность внешнего ключа.
В первую очередь - создадим таблицы, внешние ключи и индекс.
CREATE TABLE public.topic
(
topic_id integer NOT NULL,
PRIMARY KEY (topic_id)
);
CREATE TABLE public.post
(
post_id integer NOT NULL,
topic_id integer NOT NULL,
PRIMARY KEY (post_id),
FOREIGN KEY (topic_id)
REFERENCES public.topic (topic_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID
);
CREATE INDEX post_topic_id_idx
ON public.post USING btree
(topic_id ASC NULLS LAST)
;Наполним таблицы данными - создадим 100 000 топиков, в каждом из которых по 100 постов, итого 10 000 000 постов.
INSERT INTO topic(topic_id)
SELECT gs FROM generate_series(1,100000) gs
;
INSERT INTO post (post_id, topic_id)
SELECT gs1 * 100 + gs2, gs1
FROM generate_series(1,100000) gs1
JOIN generate_series(1,100) gs2 ON(1=1)
;Далее посмотрим анализ планов исполнения запроса:
время удаления записей из таблицы post
время удаления записей из таблицы topic
время проверки внешнего ключа
Для удобства анализа - будем удалять каждый пятый топик, чтобы снизить погрешности измерений. При создании внешнего ключа я не использовал опцию CASCADE, т.к. постгрес не показывает подробного плана для триггера, а только сумму.
EXPLAIN ANALYZE DELETE
FROM post
WHERE topic_id % 100 < 5
;
EXPLAIN ANALYZE DELETE
FROM topic
WHERE topic_id % 100 < 5
;В ответ на это мы получим два плана запроса, один из которых помимо удаления будет содержать в себе стоимость триггеров - это ровно один триггер, проверка внешнего включа.
QUERY PLAN
Delete on post (cost=0.00..194247.65 rows=0 width=0) (actual time=1100.873..1100.874 rows=0 loops=1)
-> Seq Scan on post (cost=0.00..194247.65 rows=3333326 width=6) (actual time=2.279..654.248 rows=500000 loops=1)
Filter: ((topic_id % 100) < 5)
Rows Removed by Filter: 9500000
Planning Time: 0.078 ms
JIT:
Functions: 3
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.433 ms, Inlining 0.000 ms, Optimization 0.278 ms, Emission 1.903 ms, Total 2.613 ms
Execution Time: 1101.387 msQUERY PLAN
Delete on topic (cost=0.00..1943.00 rows=0 width=0) (actual time=10.511..10.511 rows=0 loops=1)
-> Seq Scan on topic (cost=0.00..1943.00 rows=33333 width=6) (actual time=0.025..6.259 rows=5000 loops=1)
Filter: ((topic_id % 100) < 5)
Rows Removed by Filter: 95000
Planning Time: 0.037 ms
Trigger for constraint post_topic_id_fkey: time=485.559 calls=5000
Execution Time: 496.423 msИтого: 1101 + 10 + 485 = 1596 мс на удаление записей с проверкой внешнего ключа, из них 485 / 1596 * 100% = 30% было потрачено на проверку внешнего ключа.
Можно ли сказать что оно тормозит и невозможно дождаться выполнения запроса? - Думаю нет. Впрочем каждый имеет право на свою позицию. Если у вас аналогичный запрос выполняется часами - то проблема не во внешнем ключе. Я в этой ситуации выбираю доплатить за более мощный процессор.
Отдельным комментарием отмечу - что снижение последовательной скорости выполнения запроса не отменяет возможности оптимизации даже в случае если запрос объективно должен выполняться долго:
Возможность отключать триггеры, например для проведения экстренного технического обслуживания.
Возможность делить на меньше батчи и запускать их параллельно, например для проведения планового обслуживания.
То есть даже в случае длительных запросов - избавляться от внешних ключей полностью не обязательно.
1) Приведенный Вами пример, отличается от того о чем говорю я. Я говорю о ситуации когда, например, в таблицу post параллельно идут по 100 insert/update/delete в секунду. Возникают локи. План их не покажет, а при выполнении запроса он будет выполняться совсем другое время чем в плане предсказано. Я привел немного синтетический пример с форумом, но для того чтобы суть передать. У меня структуры сложнее и одна таблица связана примерно с 20 другими таблицами (это Player с его предметами, ачивками, друзьями и тд и тп), и при удалении юзера из БД начинается вакханалия, если использовать FK, а удалять юзеров приходится огромными массами, потому что иногда случаются DOS атаки на наши игры, и за ночь могут создать несколько миллионов новых юзеров. Понятно, мы защищаемся от атак, но все равно это было много раз.
2) Вижу у Вас в плане seq scan, все таки FK без индексов обычно не используют.
3) Есть такой факт: мы сначала несколько лет разрабатывали с использованием FK, оптимизировали и всё такое, а потом в один день решили дропнуть все FK (они были не cascade, а restrict в основном), дропнули, ничего не поломалось, load avarage на сервере упал с 10-20 до 3-4. То есть при нагрузке FK может жрать не 31%, а примерно 300%-400%.
4) Процессор на сервере норм - 18 ядер, загружен на 15% - 25%. Одно ядро обычно загружается на 100% во время дампа или вакуума, а так все ядра загружены не сильно.
Блокировки у вас будут в любом случае. 100 запросов в секунду - ничто. По защите - советую ограничить количество регистраций. Вы знаете что у вас за ночь не может зарегистрироваться скажем больше 10 000 пользователей - ровно столько и разрешайте, сверх лимита регистрация только инвайтам.
Потому что перебрать 5% записей быстрее таким способом, а не по индексу. Он несущественен в сумме исполнения запроса.
3 и 4 пункты - то есть у вас как раз достаточно свободных ресурсов, чтобы не отключать проверку FK.
И еще отдельный комментарий. Каким образом при использовании ORM вы гарантируете отсутствие race condition ?
Как я понимаю в вашем случае возможна следующая ситуация - идет два параллельных процесса:
Пользователь отправляет пост в топик.
Пользователь удаляет топик тот же самый.
Соответственно предполагаю что без внешних ключей базы опираясь только на триггеры ORM мы можем получить следующую ситуацию:
Пришел запрос на добавление нового поста.
Проверяем что указанный в посте топик существует.
Пришел запрос на удаление топика.
Удаляем все посты для выбранного топика.
Завершаем процедуру добавления поста - записываем его в базу.
Завершаем процедуру удаления топика - удаляем его из базы (при наличии FK тут случилась бы ошибка, но у нас её нет).
В зависимости от порядка действий как повезет с их исполнением - FK выдал бы ошибку как при удалении топика (указано в примере), так и аналогично может не пройти процесс записи поста. В результате такого стечения обстоятельств у вас окажется пост без связанного топика.
Как у вас решена данная проблема? Можете ли вы сказать, что ваше решение носит системный характер и ни один из программистов большой команды не забудет написать все нужные проверки? Стали бы вы использовать такой подход при разработке ПО связанного с подсчетом финансов?
select c.id, c.topic from fr_forum_comment c left outer join fr_forum_topic t on c.topic=t.id where t.id is null;
id | topic
----------+--------
16735044 | 330589
16990300 | 343702
(2 rows)
За 10 лет накопилось 2 таких мусорных записи. Это приемлемо.
Так же используем optimistic-lock местами, а местами и select for update, там где требуется повышенная надежность.
В банковском софте, зависит от ситуации. Можно сделать архитектуру что и без FK будет на 100% надежно.
Я правильно понимаю, что вы утвеждаете "если вам не важна целостность данных, то констрейнтами можно не пользоваться"?
Формально база не консистентна, с этим согласен. Но тут есть важный момент, консистентность ради консистентности нужна разве что студентам, чтобы лабу сдать. Так же как и производительность ради производительности. В моем случае пользователь никогда не узнает об этой неконсистентности, так как вытащить коменты можно только при наличии топика, нет топика, значит нет доступа к комментам удаленного топика. К какой то значимой утечке диского пространства это тоже не приводит.
В рамках моей бизнес логики база "условно консистентна". Это очень удачный компромис между целостностью и производительностью.
А понимаете Вы меня неправильно! Я утверждаю другое.
Да все я правильно понимаю.
Консистентность или есть, или нет. У вас ее нет.
Про нужды студентов будете на суде рассказывать, хорошо если как свидетель, когда сотрудник сопрет средства клиента и объявит это "имманентной неконсистетностью".
Отдельно замечу, что судья в этих косистентностях вообще не разбирается.
А вы, случайно, не участвовали в разработке системы "Арсенал"? Там этих отключений столько, что гипотетически многопользовательская система стала однопользовательской.
За что минусуют не пойму. Я же не топлю за то что FK - это однозначное зло. С академической точки зрения FK вещь красивая и полезная, но когда разработчикам надо больше производительности приходится чем то жертвовать, так и появляются всякие NoSQL, дроп констрейнтов и т.п. Я просто делюсь своим опытом, и считаю его успешным, может кому то пригодится такой лайф хак.
Вам в комментариях уже ответили что если вы не умеете настраивать ограничения чтобы они не сильно влияли на производительность, то это не вина ограничении, а скорее всего вы не умеете их готовить, и что помимо ключей существуют еще индексы, которые при умелой настройке решают большой класс проблем, и скорее всего в числе этих проблем находится и та на которую вы жалуетесь в своем исходном комментарии.
Потому и минусуют вашу "рекомендацию" отключить ограничения.
В 2012 меня пригласили в один околомедицинский стартар из долины. Хотя я был приглашён сделать один весьма низкоуровневый механизм, которым стали бы пользоваться люди, занимающиеся бизнес-логикой, и оставить им его, я сам остался в проекте и тоже стал заниматься бизнес-логикой.
В проекте БД использовалась именно как «тупое хранилище». Все проверки, обеспечивающие консистентность и поддерживающие благоразумие, были в PHP-коде бэкенда. Я стал агитировать за и планомерно собственноручно проводить трансформацию БД в умное хранилище. Потому что существовавшие проверки очень дурно пахли, а кроме того, как оказалось, в коде проекта совершенно не придерживались DRY-идеологии, так что одна и та же сущность в БД могла порождаться не из одного, а из множества разных мест, разбросанных по коду, и эти места даже не всегда были результатом копи-паста, так что в каждом месте был свой способ и набор проверок, не всегда тождественных
В какой-то момент в проект вернулся человек, который у руководителя проекта имел репутацию очень крутого программиста, и который в данном проекте был родоначальником кодовой базы, потом был тимлидом, когда у него появились подчинённые программисты, а потом оставил проект. Когда он, вернувшись, увидел мои нововведения в плане БД, он был в бешенстве. Руководитель проекта к моим новшествам относился скептически, этот бывший вернувшийся тимлид — резко негативно. Ох, сколько агрессивных баталий пришлось пройти, чтобы отстоять точку зрения, что СУБД должна сопротивляться попыткам запихнуть в неё ахинею. Чего я только не наслушался: что я отстал от жизни, что изобретаю велосипед, что это не соответствует современным канонам и портит весь проект... И, мол, тебя ведь пригласили писать низкоуровневые вещи и под дизассемблером реверсить то, что нам нужно — зачем ты суёшься в дизайн БД.
И это медицинский продукт, Карл! Одна забытая или неправильно проведённая проверка — и вот одна и та же машина скорой помощи с одним и тем же экипажем в одно и то же время должна ехать к двум совершенно разным пациентам в разных концах города. А там все проверки (те что на уровне PHP-бэкенда и в WET-духе при этом) были сделаны так, будто мир однопоточен и параллельно обрабатываемых бэкендом запросов и быть не может.
Когда кто-то начинает жаловаться, что таблицы работают медленно уже при 30 миллионах записей, меня начинает колотить.
Я понимаю, когда записей 3 миллиарда, да, начинает слегка подтормаживать, но если у вас на 3 миллионах уже начинаются сомнения, то сомнения должны быть в первую очередь к себе, к архитектуре и правильности составления запросов...
Полно же всяких партиционирований, отложенных джоинов, промежуточных коммитов в функциях, отложенных триггеров и т.д. и т.д.
Constraints в PostgreSQL, или о том, как попытаться спокойно жить