Данный материал был создан на основе одноимённого доклада на PGConf.Online, вошедшего в число самых популярных выступлений конференции. Поскольку тема ограничений по-прежнему сохраняет свою актуальность, а смотреть видео с мероприятий любят не все, появилась эта статья.
Концепция “тупого хранилища”
В последние годы разработчики ПО всё чаще утверждают, что база в их проекте “всего лишь тупое хранилище, и поэтому никакой логики в ней нет”. Откуда такой подход? Обычно он объясняется сложностями миграции, развёртывания, неудобствами при работе с системами контроля исходного кода. Не стоит списывать со счетов и простую человеческую лень: раз всё и так нормально, зачем связываться с логикой в СУБД? Создали таблицы (или, ещё лучше, пусть ORM их создаст!), и всё отлично.
NoSQL для документов
Случай с NoSQL ещё проще – не надо ничего создавать, контролировать и напрягать мозги, всё уже автоматизировано, оно само работает. Этого вполне достаточно, если из базы нужно просто доставать документы по идентификатору, но если требуется решать задачи посложнее, то всё-таки выбирают SQL СУБД. Их использование, однако, ограничивается созданием таблиц и индексов, логика на стороне СУБД и в этом случае видится избыточной.
СУБД: не только технология, но и бизнес-инструмент
Такой подход является очень распространённым (люди вообще ленивы!). Тем не менее, крайне наивно дистанцироваться от хороших возможностей только из-за нежелания заморачиваться и приобретать новые навыки. СУБД – это очень изощрённая система хранения (чтобы понять это, достаточно почитать про уровни изоляции или процедуры резервного копирования). СУБД помогает синхронизировать бизнес-процессы и избежать реальных убытков, иногда в очень крупном размере.
Ecommerce: проблемы в системах учёта
Конечно, если у вас на складе товар в одном экземпляре, а вы его продали сразу двум покупателям, ваш бизнес от этого не сильно пострадает. В самом худшем случае вы из-за этой ситуации потеряете одного клиента-физлицо. Но что будет, если случатся более серьёзные неприятности? Осенью 2020 года я слышал о скандале внутри крупного российского онлайн-ритейлера. Тогда на складе обнаружили целые залежи товаров, ожидающих отправки покупателям ещё с весны. Виноваты, как всегда, были все сразу и никто конкретно.
“Сюрпризы” в коде
Концепция “СУБД – тупое хранилище” не может предотвратить такие случаи. Более того, она создаёт для них предпосылки. Это всего лишь вопрос времени, когда случится подобное. Даже лучший в мире разработчик рано или поздно совершит ошибку. Проект может существовать с этой ошибкой годами, до момента, пока не будут созданы условия, в которых она проявится. Тушить такой внезапный пожар всегда проблематично. Необходимо иметь в виду, что даже давно проверенный старый код однажды может преподнести неприятные сюрпризы.
Почему “умная” база лучше?
Как же уберечься от подобного рода неприятностей или хотя бы уменьшить вероятность их возникновения? Стоит вернуться к логике в СУБД, сделать её “умным” хранилищем. С помощью ограничений (constraints) можно сделать так, чтобы СУБД не допускала перевод себя в явно некорректное состояние. Конечно, определить все некорректные состояния раз и навсегда вряд ли получится. Однако, можно хотя бы составить список условий, в которых база точно никогда не должна находиться.
Когда ограничения стоят денег
Однажды знакомый разработчик ошибся при расчёте обменного курса криптовалют и отправил нескольким получателям по триста тысяч долларов каждому вместо ожидаемых трёхсот. Это могла бы предотвратить обычная нудная проверка, если бы она была. “Для такой-то пары значение отправленной суммы должно быть не больше 1/5 от значения полученной”, - если бы такое ограничение было вовремя установлено, можно было бы сберечь время и нервы:
check(case when in_ticker='BTC' and out_ticker='ETH'
then out_amt/in_amt>5
else true
end)Намного приятнее и спокойнее разбираться, почему платёж не ушёл, чем выяснять, почему он ушёл не туда или с неправильной суммой.
Дело не только в производительности
Таких жизненных историй я знаю очень много, поэтому стандартные возражения против ограничений целостности кажутся мне нелепыми. Естественно, ограничения снижают производительность, зато никому не придется продавать почку, если что-то пойдет не так. (Вряд ли кому-то захочется иметь дело с взысканием миллионного ущерба на работе.) Знаю, что многие молодые разработчики искренне верят в надежность своего кода. C годами это проходит, но зачем же ждать так долго?
Двойная защита для любых ценностей
Мой опыт подсказывает, что в реальном приложении, работающем с деньгами или другими материальными ценностями, любая проверка должна дублироваться. При этом желательно, чтобы код для каждой из проверок писали разные люди, которые не знакомы друг с другом и думают по-разному. Если вернуться к вышеупомянутому примеру с криптовалютами, одна проверка должна быть для грубой оценки – “не более 1/5”, а вторая – проверять, чтобы вычисленный курс не отличался от реального более чем на 2%:
(select abs(1-(select (out_amt/in_amt)/r.rate from rates r
where r.ask_ticker='TCKR1' and r.bid_ticker='TCKR2'))<0.02)Зачем дублировать проверки? Рано или поздно какая-то из этих проверок сломается, и в отсутствие “запасной” проверки придётся работать без ограничений, а это чревато убытками.
Паранойя или управление рисками?
Расскажу ещё об одном случае из моей практики. Мне нужно было убедиться, что заказы не размещаются повторно. Для этого я создал уникальный индекс по идентификатору товара плюс предусмотрел простенькую проверку того, не был ли такой же товар добавлен в таблицу ранее. Казалось бы, паранойя на грани сумасшествия: зачем проверять то, что и так проверяется, причем не строго корректным образом (корректная реализация проверки на уникальность хоть и несложна, но, тем не менее, немного нетривиальна)? Тем не менее, в процессе очередной реорганизации уникальный индекс по недосмотру был заменен на обычный. Двойные продажи товаров начались бы, если бы не резервная проверка. Замена индекса обнаружилась только дней десять спустя, тем не менее, проблемных ситуаций не возникло. Так что у паранойи тоже есть практичная сторона. Конечно, такие дублирующиеся проверки подходят не для всех случаев. Но если нарушение бизнес-процессов грозит заметными потерями, благоразумно иметь две проверки или даже больше.
В наиболее критичных ситуациях отдельные разработчики рекомендуют ставить не менее трех серверов и выполнять операцию только если минимум два из них приходят к консенсусу (замечание в сторону: именно такое поведение автоматически получается в случае использования блокчейна).
Почему гибкость не всегда хороша
Ещё одно распространённое возражение против ограничений – отсутствие гибкости. Разумеется, база данных станет негибкой! Но при применении ограничений в этом и состоит наша цель. Так, например, при описании таблицы с примыкающими диапазонами дат (“с -infinty по 2020-01-01, с 2020-01-01 по 2020-06-01, с 2020-06-01 по 2021-01-01, с 2021-01-01 по infinity”) вставить в середину отдельный диапазон не так-то просто. Для этого потребуются либо отложенные (deferrable) проверки и не просто вставка нужной строки, но и изменение соседних, либо придётся обновить таблицу целиком.
create constraint trigger check_daterange_consistency_iu
after insert or update on fees deferrable initially deferred
for each row execute procedure check_fees_table_consistency();
create or replace function wb.check_fees_table_consistency()
returns trigger as
$code$
begin
perform pg_advisory_xact_lock(hashtext('fees'),
hash_record(row(new.period_start, new.period_end)));
if new.period_start<>'-infinity'
and not exists(select * from fees f where f.period_end=new.period_start)
then
raise sqlstate '23Q01' using message=format('Invalid period_start:%s',new);
end if;
if new.period_end<>'infinity'
and not exists(select * from fees f where f.period_start=new.period_end)
then
raise sqlstate '23Q01' using message=format('Invalid period_start:%s',new);
end if;
if exists(select * from fees f
where (f.period_start,f.period_end)overlaps(new.period_start, new.period_end)
and f.period_start<>new.period_start
and f.period_end<>new.period_end )
then
raise sqlstate '23Q01' using message=format('Invalid date range:%s',new);
end if;
end
$code$
language plpgsqlНо ведь именно это и является целью ограничений – ни в коем случае не допустить непримыкающих диапазонов дат. Нам необходимо гарантировать, что диапазоны, во-первых, примыкают к друг другу и, во-вторых, не перекрываются.
В реальной жизни настолько жесткие ограничения встречаются не столь часто, как хотелось бы. Если бы большее число компаний и организаций пользовались возможностями СУБД в полном объёме, мы реже слышали бы о гигантских убытках и недовольных пользователях.
Осиротевшие строки и неопределённость
Какова же стандартная практика? Обычно у каждой таблицы есть первичный ключ, хотя встречаются – и даже слишком часто – таблицы и без него. Реже, но всё же довольно часто применяют ограничение not null. Что касается внешних ключей (foreign keys), вполне можно наблюдать базы без них. А если в базе нет внешних ключей, и она уже достаточно стара и велика, есть 100% вероятности, что в её дочерних таблицах есть осиротевшие строки. Это плохо само по себе, плюс закономерно предположить, что если родитель для одной строки потерян, то и для другой он может быть указан некорректно.
“Экзотические” ограничения и ecommerce
Некоторые ограничения практически не встречаются. Например, EXCLUDE позволяет не допускать перекрывающихся диапазонов. Проверка JSON или XML на соответствие схеме также довольно экзотична. Совсем редки проверки в триггерах, хотя последние предназначены в том числе и для нестандартных проверок. Так, стандартными средствами СУБД невозможно обеспечить ограничение целостности вроде “каждый заказчик может иметь не более трех неоплаченных заказов” или “сумма неоплаченных заказов не должна превышать определенного значения”. Для реализации этого ограничения сначала потребуется написать триггер на добавление либо обновление удаление строки в таблице “Заказы”. Потом в нём обязательно нужно использовать блокировку, чтобы предотвратить обновление строки пользователя в транзакции, либо придётся использовать advisory-блокировку, используя хеш от идентификатора пользователя в качестве ключа блокировки. (Пример можно видеть выше в тексте триггерной функции:
pg_advisory_xact_lock(hashtext('fees'),
hash_record(row(new.period_start, new.period_end)));Использование advisory-блокировки является отступлением от стандарта, но при большом количестве операций модификации она даёт выигрыш по производительности.
Триггеры и финтех
Не получится обойтись без триггеров и в случае проверки значения. Например, в России рублевый счет коммерческой нефинансовой организации в банке обязательно должен начинаться на 40702810. Проверку можно описать как CHECK, но в более сложных случаях придется обратиться к таблице-справочнику (40701 – финансовые организации, 40703 – некоммерческие, 408 – частные лица и т.п.). Правда, подобный триггер обычно имеет достаточно простой вид:
if not exists(select * from account_chart ac where ac.prefix=substring(new.account from 1 for 5)) then
raise sqlstate ‘23Q03’ using message=’Invalid account for …’;
end if;ASSERTION: круто, но не реализовано
В стандарте SQL описано такое полезное ограничение уровня БД, как ASSERTION. Будь оно реализовано, оно позволяло бы делать замечательные вещи. Так, ограничение на количество неоплаченных заказов было бы совершенно тривиальным. Ограничения по суммам продаж, по датам отгрузки тоже не представляли бы никакой проблемы. К сожалению, это ограничение не реализовано в Postgres. В оправдание Postgres можно сказать, что оно вообще не реализовано ни в одной из популярных СУБД. Честно говоря, не очень понятно, как его вообще можно эффективно реализовать.
Зачем писать триггеры?
Таким образом, если требуются достаточно нестандартные ограничения, придётся создавать триггеры или регулярно запускать скрипты проверки. (Это справедливо для ситуаций вида “не больше трех неоплаченных заказов”, “оплаченный заказ не может оставаться неотгруженным более суток” и им подобных.) Почему требуются какие-то дополнительные скрипты? Потому что со временем вполне корректные состояния могут оказываться некорректными. Например, товар, предназначенный для отправки, не может находиться на складе позднее какой-то даты:
for r in select * from goods g
where g.shipment_date is null
and g.received_date>current_date-1
loop
perform send_notification_to_manager_on_stalled_delivery(r);
end loop;Так как штатных триггеров по времени в СУБД не предусмотрено, придётся эмулировать их самостоятельно. Можно даже создать целый фреймворк для этого, но это уже тема другого разговора.
Основные тезисы
Какие основные тезисы этой статьи стоит запомнить? Я бы остановился на нескольких:
База данных – это не “тупое хранилище”.
База данных не должна допускать явно некорректные операции.
База данных должна обнаруживать некорректные состояния.
Вывод
Понятно, что реализация подхода “база данных – умное хранилище” требует дополнительных усилий. Тем не менее, без этих усилий обойтись не удастся, если данные в СУБД являются частью сколько-нибудь серьезного бизнеса. Даже если проверки и другие ограничения не решат всех проблем, они позволят обнаруживать их раньше. А время – деньги.
Комментарии (51)
guryanov
06.09.2022 16:26Согласен со статьей, пришлось взять себе кусок проекта, который писали по принципу тупого хранилища, очень сложно было привести к нормальному виду. Несмотря на все заверения, что лучше все проверки делать в коде на практике получается куча мусора в базе, от которого очень тяжело избавиться без потери полезных данных.
Gmugra
06.09.2022 16:41+3Люто поддерживаю!
Гарантия того, что ерунду в базу не получится вписать, кто бы и как бы не пытался это сделать - бесценна.
Сonstraints реально могут от больших проблем спасти.Например, проверки в коде не спасут от ситуаций когда вносят изменения в базу "вручную" или каким-то, на коленке сляпанным, скриптом.
Сколько раз за свою карьеру подобные истории разгребал - не счесть...
Mingun
06.09.2022 17:17-2Если их так вносят, значит уже существующие проверки не помогли и как-то исправлять ситуацию надо. Что вы будите делать, если и здесь заткнете возможности что-то исправить — в хекс редакторе править файлы БД?
me21
07.09.2022 21:15А зачем в базу вносить данные, которых там быть не должно?
Mingun
07.09.2022 21:32+1А что вы у меня-то спрашиваете? Я же как раз и спрашиваю, как вы их удалять оттуда будете, когда они там появятся, а всякие "умные" проверки на самом нижнем уровне не дадут этого сделать. Не говоря уже о том, что вон там ниже реальные случаи выбросов приводят. 640Kb хватит всем...
me21
07.09.2022 21:52Если проверки будут - неверные данные там не появятся.
Mingun
07.09.2022 22:45Если проверки будут на уровне выше, в приложении, работающем с базой — неверные данные также не появятся.
me21
08.09.2022 00:11+1Появятся, если их случайно внесут не через приложение, а, например, скриптом.
Mingun
08.09.2022 11:43А зачем давать доступ кому попало вносить что-то скриптом? Вам не кажется, что если вы вносите что-то скриптами, то вы уже вышли за рамки изначальных допущений и применять к этой ситуации те же критерии, что и к штатной работе, как-то некорректно.
Но даже в этом случае остается возможность все исправить обратно, выполнив еще один скрипт.
me21
08.09.2022 13:04Не кому попало, а администратору БД.
Но мне не кажется, что администратор БД должен иметь возможность случайно внести в БД мусор из-за бага в скрипте. Проверки должны быть.
Если они мешают выполнить скрипт, надо проверить скрипт на баги, и если уж точно надо - временно отключить проверки.
Это как sudo в командной строке.
Jsty
06.09.2022 16:51+1Объясните пример с "Когда ограничения стоят денег" и почему именно 1/5?
plumqqz Автор
06.09.2022 18:27В данном случае соотношение BTC/ETH, чтобы значения в паре были более-менее в рамках здравого смысла. Рост человека не больше 2.5м, одинокий ребенок не может быть ответственным квартиросъемщиком и т.п.
aenigmatista
06.09.2022 22:11+3Рост человека не больше 2.5м
Не подумайте, что издеваюсь, но:
Согласно «Книге мировых рекордов Гиннесса», самым высоким человеком на земле на данный момент является турецкий фермер Султан Кесен, его рост – 251 см.
Mingun
06.09.2022 17:15+3Всегда думал, что констрейнты — это и есть глупая база, а умная — это когда на птичьих языках внутри нее чего-то писать пытаются
nomhoi
06.09.2022 19:18Мне нужно было убедиться, что заказы не размещаются повторно.
Есть такой вариант использования версий:
https://github.com/cosmicpython/book/blob/master/chapter_07_aggregate.asciidoc#optimistic-concurrency-with-version-numbersplumqqz Автор
06.09.2022 19:29-1Честно говоря, мне показалось, что решение переусложнено. Автор там и сам пишет, что можно было бы обойтись select for update или уровнем serializable; да, при высокой нагрузке это действительно может вызвать проблемы, но, как мне кажется, бизнес от подобных проблем (продажи идут с такой скоростью, что СУБД не успевает их размещать) будет только счастлив. Ну и, опять же, в случае постгреса это обходится процедурой/триггером и advisory блокировкой.
yar
06.09.2022 21:23-3Я столкнулся с тем, что когда в таблицах по нескольку десятков миллионов записей и сайт обрабатывает более 100 запросов в секунду, то при наличии в БД foreign key на этих больших таблицах сайт не может держать таких нагрузок. Поэтому я уже около 10 лет вообще не использую foreign key в postgres, а за консистентностью БД и каскадным удалением записей по зависимостям следит hibernate, и нет никакого мусора в БД.
Когда я еще использовал foreign key, при массивных апдейтах или удалениях приходилось дропать констрейнты, так как при их наличии апдейты выполнялись часами, я просто не дожидался их выполнения, а после апдейта снова накатывать констрейнты. Помучившись так, я просто решил от них отказаться вообще, и ни разу не пожалел.
В общем, в высоко нагруженных приложениях с большими БД надо понимать, что foreign key будет очень дорого стоить по производительности.
Ну, это мой личный опыт. Больше я никого не знаю кто вообще без foreign key работает с БД.
akhkmed
07.09.2022 01:54В этом примере что-то недосказанно. На производительность чтения FK плохо не влияет, но позволяет точнее оценить количество строк при join по FK. На скорость изменений данных влияет, но только если менять значения столбцов FK или PK/UK. Есть случаи, когда блокировки в БД должны сильно распространиться по связанным по FK таблицам, но этот эффект можно минимизировать.
В той системе запросы написаны руками или ORM? Вы просто пишете, что ORM следит за консистентностью при каскадных операциях, но если не принять нужных мер (индексы для FK), то и ORM тоже начнёт тормозить.
yar
07.09.2022 03:11+1Есть много апдейтов коллекций (типа по связи one-to-many), это транслируется в delete и insert SQL запросы тут то FK и тормозит. При чтении из БД, конечно же, FK вообще не используется.
Для настройки ORM чтобы работал каскад не надо FK, надо просто прописать в конфиге, как будет работать каскад (типа cascade="all" или cascade="delete-orphan" и тп), для скорости достаточно индексов, для джойнов FK вообще не нужен.
И postgres совсем по другому выполняет каскад чем ORM. Posgres делает кучу локов и часто можно увидеть что процесс waiting for lock, когда много параллельных обновлений идёт, а ORM просто разбивает эти апдейты и удаления записей на несколько SQL запросов и не делает никаких локов, за счёт этого и выигрыш.
Если чего то не договариваю, то спрашивайте, я дополню.
yar
07.09.2022 03:15+1но позволяет точнее оценить количество строк при join по FK
можно узнать откуда эта инфа?
akhkmed
07.09.2022 10:52-1Вообще оценить число строк при join очень непросто, а знание, что есть связь по FK, сильно упрощает. Если две таблицы связаны по FK, то кардинальность пересечения множеств известна заранее. Проверил, похоже в postgres нет этой оптимизации.
yar
07.09.2022 10:58А в какой СУБД, например, это есть?
Мне просто сам принцип непонятен, как FK может помочь при джойне. Я понимаю индексы - это да, я понимаю что в СУБД индексы могут автоматически создаваться при создании FK, но чтобы FK без индекса как-то помогал такого не знаю.
plumqqz Автор
07.09.2022 11:46Ну подумайте - при соединении можно утверждать, что для вот этой строки точно найдется другая и причем только одна.
yar
07.09.2022 11:50Но чтобы эту "только одну" быстро найти нужен именно индекс, а не FK. В этом можно убедиться если посмотреть план выполнения запроса, что используется именно индекс, а не FK. Или нет?
plumqqz Автор
07.09.2022 11:55Причем тут индекс вообще? Это нужно для оценки кардинальности соединения, до индексов еще далеко.
yar
07.09.2022 12:01Ну, про индекс написано в плане выполнения запроса, если он есть то будет index scan и будет быстро, если его нет будет seq scan и будет медленно. А вот наличие или отсутствие FK ни как не влияет на быстро/медленно. Или я не прав?
plumqqz Автор
07.09.2022 12:07Прежде чем будет выбран какой-то план, необходимо построить все возможные планы (ну там сложнее, конечно, в общем случае) и оценить их стоимость.
Так что это влияет на точность оценки количества строк при соединении; а это уже влияет на точность выбора оптимального плана оптимизатором; так что да, влияет.
Разумеется, это не гарантия того, что все будет отлично или даже что оптимизатор выберет действительно оптимальный план, но тем не менее.yar
07.09.2022 12:11Если эта информация не Ваш домысел, а из документации Postgres, то скиньте ссылку пожалуйста, просвещусь. Я реально не знаю о том и не понимаю, как наличие FK может ускорить join.
yar
07.09.2022 12:24А зачем тогда это нужно "оценить количество строк при join по FK"? Это число (количество строк) потом как используется? Я понял, что для производительности, чтобы план более оптимальный, например, выбрать. Если я неправильно понял, то поясните зачем это число нужно.
NickNal
08.09.2022 00:50+1Есть статья на тему роли FK при планировании join, но PG там не в лидерах
https://blog.jooq.org/join-elimination-an-essential-optimiser-feature-for-advanced-sql-usage/
Хотя FK не для этого нужны, конечно
plumqqz Автор
08.09.2022 00:58Да что далеко ходить, есть цикл статей моего коллеги, там и это рассматривается, причем прямо здесь
https://habr.com/ru/company/postgrespro/blog/579024/Отдельно очень рекомендую его книжку, https://postgrespro.ru/education/books/internals
Tatikoma
07.09.2022 03:40+1Вероятно вам не хватало индексов. Скорее всего вы удаляете запись на которую есть внешний ключ - в таблицах с внешним ключом на эту запись должен быть индекс по ключевой колонке, чтобы база данных могла быстро проверить консистентность операции.
Ресурсы для проведения проверок требуются, но по моему опыту они не дают даже удвоения нагрузки на CPU (в среднем по больнице).
Иногда, вероятно, действительно можно столкнуться с ситуацией, когда отключение проверок дает значительный выигрыш. В этом случае можно отключить проверку целостности на время проведения транзакции (нет нужды удалять FK, достаточно дать команду ALTER TABLE tablename DISABLE TRIGGER ALL): такой код нужно писать особенно внимательно и наиболее широко покрывать тестами.
Я в целом предпочитаю таких конструкций избегать. Лучше добавить побольше серверов, а такие хаки применять только если не осталось иных решений.
yar
07.09.2022 03:58-2Не, с индексами всё в порядке. Если бы не хватало индексов, то тормозили бы запросы select.
Тормозит такой кейс, например, я удаляю топик на форуме, а в нем 1000 комментов, они тоже должны удалиться. Если каскад обработает БД это будет очень медленно по сравнению с двумя запросами (delete from topics where id=10, и delete from comments where topic_id=10).
Я активно использовал FK пока не начал использвать ORM. На мой взгляд, ORM позволяет жить спойно и без FK.
Tatikoma
07.09.2022 05:17+5Давайте попробуем собрать воспроизводимый тест-кейс! Каскад БД или два запроса - не имеет значения, с точки зрения производительности - у нас внешний ключ либо проверяется, либо нет. Соответственно нужно смотреть на производительность внешнего ключа.
В первую очередь - создадим таблицы, внешние ключи и индекс.
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% было потрачено на проверку внешнего ключа.
Можно ли сказать что оно тормозит и невозможно дождаться выполнения запроса? - Думаю нет. Впрочем каждый имеет право на свою позицию. Если у вас аналогичный запрос выполняется часами - то проблема не во внешнем ключе. Я в этой ситуации выбираю доплатить за более мощный процессор.
Tatikoma
07.09.2022 05:23Отдельным комментарием отмечу - что снижение последовательной скорости выполнения запроса не отменяет возможности оптимизации даже в случае если запрос объективно должен выполняться долго:
Возможность отключать триггеры, например для проведения экстренного технического обслуживания.
Возможность делить на меньше батчи и запускать их параллельно, например для проведения планового обслуживания.
То есть даже в случае длительных запросов - избавляться от внешних ключей полностью не обязательно.
yar
07.09.2022 05:511) Приведенный Вами пример, отличается от того о чем говорю я. Я говорю о ситуации когда, например, в таблицу 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% во время дампа или вакуума, а так все ядра загружены не сильно.
Tatikoma
07.09.2022 15:02+1Блокировки у вас будут в любом случае. 100 запросов в секунду - ничто. По защите - советую ограничить количество регистраций. Вы знаете что у вас за ночь не может зарегистрироваться скажем больше 10 000 пользователей - ровно столько и разрешайте, сверх лимита регистрация только инвайтам.
-
Потому что перебрать 5% записей быстрее таким способом, а не по индексу. Он несущественен в сумме исполнения запроса.
3 и 4 пункты - то есть у вас как раз достаточно свободных ресурсов, чтобы не отключать проверку FK.
Tatikoma
07.09.2022 05:35+1И еще отдельный комментарий. Каким образом при использовании ORM вы гарантируете отсутствие race condition ?
Как я понимаю в вашем случае возможна следующая ситуация - идет два параллельных процесса:
Пользователь отправляет пост в топик.
Пользователь удаляет топик тот же самый.
Соответственно предполагаю что без внешних ключей базы опираясь только на триггеры ORM мы можем получить следующую ситуацию:
Пришел запрос на добавление нового поста.
Проверяем что указанный в посте топик существует.
Пришел запрос на удаление топика.
Удаляем все посты для выбранного топика.
Завершаем процедуру добавления поста - записываем его в базу.
Завершаем процедуру удаления топика - удаляем его из базы (при наличии FK тут случилась бы ошибка, но у нас её нет).
В зависимости от порядка действий как повезет с их исполнением - FK выдал бы ошибку как при удалении топика (указано в примере), так и аналогично может не пройти процесс записи поста. В результате такого стечения обстоятельств у вас окажется пост без связанного топика.
Как у вас решена данная проблема? Можете ли вы сказать, что ваше решение носит системный характер и ни один из программистов большой команды не забудет написать все нужные проверки? Стали бы вы использовать такой подход при разработке ПО связанного с подсчетом финансов?
yar
07.09.2022 06:09-3select 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% надежно.
plumqqz Автор
07.09.2022 12:10+1Я правильно понимаю, что вы утвеждаете "если вам не важна целостность данных, то констрейнтами можно не пользоваться"?
yar
07.09.2022 12:19-1Формально база не консистентна, с этим согласен. Но тут есть важный момент, консистентность ради консистентности нужна разве что студентам, чтобы лабу сдать. Так же как и производительность ради производительности. В моем случае пользователь никогда не узнает об этой неконсистентности, так как вытащить коменты можно только при наличии топика, нет топика, значит нет доступа к комментам удаленного топика. К какой то значимой утечке диского пространства это тоже не приводит.
В рамках моей бизнес логики база "условно консистентна". Это очень удачный компромис между целостностью и производительностью.
А понимаете Вы меня неправильно! Я утверждаю другое.
plumqqz Автор
07.09.2022 12:23-2Да все я правильно понимаю.
Консистентность или есть, или нет. У вас ее нет.
Про нужды студентов будете на суде рассказывать, хорошо если как свидетель, когда сотрудник сопрет средства клиента и объявит это "имманентной неконсистетностью".
Отдельно замечу, что судья в этих косистентностях вообще не разбирается.
korva
07.09.2022 15:20А вы, случайно, не участвовали в разработке системы "Арсенал"? Там этих отключений столько, что гипотетически многопользовательская система стала однопользовательской.
yar
07.09.2022 06:40-2За что минусуют не пойму. Я же не топлю за то что FK - это однозначное зло. С академической точки зрения FK вещь красивая и полезная, но когда разработчикам надо больше производительности приходится чем то жертвовать, так и появляются всякие NoSQL, дроп констрейнтов и т.п. Я просто делюсь своим опытом, и считаю его успешным, может кому то пригодится такой лайф хак.
toxicdream
08.09.2022 08:50Вам в комментариях уже ответили что если вы не умеете настраивать ограничения чтобы они не сильно влияли на производительность, то это не вина ограничении, а скорее всего вы не умеете их готовить, и что помимо ключей существуют еще индексы, которые при умелой настройке решают большой класс проблем, и скорее всего в числе этих проблем находится и та на которую вы жалуетесь в своем исходном комментарии.
Потому и минусуют вашу "рекомендацию" отключить ограничения.
firehacker
09.09.2022 07:30+1В 2012 меня пригласили в один околомедицинский стартар из долины. Хотя я был приглашён сделать один весьма низкоуровневый механизм, которым стали бы пользоваться люди, занимающиеся бизнес-логикой, и оставить им его, я сам остался в проекте и тоже стал заниматься бизнес-логикой.
В проекте БД использовалась именно как «тупое хранилище». Все проверки, обеспечивающие консистентность и поддерживающие благоразумие, были в PHP-коде бэкенда. Я стал агитировать за и планомерно собственноручно проводить трансформацию БД в умное хранилище. Потому что существовавшие проверки очень дурно пахли, а кроме того, как оказалось, в коде проекта совершенно не придерживались DRY-идеологии, так что одна и та же сущность в БД могла порождаться не из одного, а из множества разных мест, разбросанных по коду, и эти места даже не всегда были результатом копи-паста, так что в каждом месте был свой способ и набор проверок, не всегда тождественных
В какой-то момент в проект вернулся человек, который у руководителя проекта имел репутацию очень крутого программиста, и который в данном проекте был родоначальником кодовой базы, потом был тимлидом, когда у него появились подчинённые программисты, а потом оставил проект. Когда он, вернувшись, увидел мои нововведения в плане БД, он был в бешенстве. Руководитель проекта к моим новшествам относился скептически, этот бывший вернувшийся тимлид — резко негативно. Ох, сколько агрессивных баталий пришлось пройти, чтобы отстоять точку зрения, что СУБД должна сопротивляться попыткам запихнуть в неё ахинею. Чего я только не наслушался: что я отстал от жизни, что изобретаю велосипед, что это не соответствует современным канонам и портит весь проект... И, мол, тебя ведь пригласили писать низкоуровневые вещи и под дизассемблером реверсить то, что нам нужно — зачем ты суёшься в дизайн БД.
И это медицинский продукт, Карл! Одна забытая или неправильно проведённая проверка — и вот одна и та же машина скорой помощи с одним и тем же экипажем в одно и то же время должна ехать к двум совершенно разным пациентам в разных концах города. А там все проверки (те что на уровне PHP-бэкенда и в WET-духе при этом) были сделаны так, будто мир однопоточен и параллельно обрабатываемых бэкендом запросов и быть не может.
Falseclock
09.09.2022 07:47+2Когда кто-то начинает жаловаться, что таблицы работают медленно уже при 30 миллионах записей, меня начинает колотить.
Я понимаю, когда записей 3 миллиарда, да, начинает слегка подтормаживать, но если у вас на 3 миллионах уже начинаются сомнения, то сомнения должны быть в первую очередь к себе, к архитектуре и правильности составления запросов...
Полно же всяких партиционирований, отложенных джоинов, промежуточных коммитов в функциях, отложенных триггеров и т.д. и т.д.
Vvzh1
Как говорил начальник нашего управления по разработке в 2001 году - что ты так долго базу проектируешь ? У меня ее студенты за 1 день распишут всю ! А ты тут копаешься уже месяц !