Ключи и триггеры postgresql. Что для чего лучше использовать?
Спрашиваю как нуб в posgresql, пришедший из лесов mysql.
Есть 2 таблицы:
[conversation диалоги]
id
last_message_id
message_counter
[message сообщения]
id
conversation_id
При удалении диалога, должны удаляться сообщения - это я полагаю, проще всего и выглядит как FOREIGN KEY (conversation_id) REFERENCES conversation(id) с CASCADE на ON DELETE
При добавлении/удалении сообщения, должен меняться last_message_id - вероятно, триггер? или можно сделать как-то автообновление через представления(view)?
При добавлении увеличиваться (именно увеличиваться, а не пересчитываться - при удалении не меняется) message_counter - вероятно, триггер?
По моему, триггеры - зло! Если уж и пользовать триггеры, то только для валидации данных. Иначе, при больших нагрузках, можете получить непредсказуемое поведение системы, дидлоки, деградацию производительности.
В вашем же случае поля last_message_id и message_counter можно вычислять дополнительными запросами и в таблице им вообще нечего делать, просто сделайте индекс.
Готов к дискуссии!
Дело в том, что counter не нужно вычислять, это количество сообщений за все время, в том числе удаленных - это должен быть простой инкремент на каждую вставку сообщения.
А last_message_id должен быть готов всегда - он участвует во всех выборках по conversation. Иначе join либо десяток лишних запросов по ключу. Я предпочитаю кеш в отдельное поле.
1) удалять сообщения не нужно, их нужно не покпзывать. Делаем отдельное полк deleted
2) select count(tatata) делает это по индексу, где и так он хранится.
3) lastmessage так вообще нужно хранить не в базе, а в памяти
Ну и собственно, когда у нас есть кеширование или ORM, то во многих случаях в базу не нужно будет лазить вообще.
Проблема с разными тригерами одна, они нагружают базу, иногда очень и очень сильно, особенно при операциях удаления, а еще, про них забываешь и они иногда могут творить чудеса... Я не настаиваю, я через хранимки и тригера прошел и если их можно не делать, их делать не нужно. Особенно начинает бесить, когда через два-три года работы базы неработающий до этого тригер вдруг начинает отрабатывать по какому-то условию, при этом и код и данные и условия уже давно не те. Вот тут и начинается зоопарк с откатом стогигабайтных баз, транзакционных логов, перезаливкой архивных данных, поиском проблемы и прочими батхертами и оверхедами с бдениями в праздничные ночи.
С хранимками ситуация ничуть не лучше, особенно, когда у вас распределенная система и некоторые таблицы пошардены на кучу инстансов.
Ах, да!!! Как раз все эти навороты перестают работать в распределенных системах. Представим, что сообщений столько, что принимается решение разделить таблицу на несколько серверов например по алфавиту, вот здесь вам и придется все эти тригеры и хранимки убирать и внедрять в код, это и так не всегда тривиальная задача, а тут еще и тригеры и хранимки...
С рапределенными системами не знаю, но понятно почему начинаются проблемы на больших объемах - из-за вызова триггеров на каждый ряд при массовых изменениях. В этом случае обычно удаляют триггеры и ключи, а затем перестраивают их заново.
Модификация таблиц будет происходить несколько реже чтения, поэтому лишний апдейт поля с фиксированной длиной в диалоге по ключу на вставку сообщения - не слишком большая нагрузка по сравнению с кучей лишних запросов и проще, чем дополнительный слой кеша, который будет делать те же запросы, но чуть реже.
Тем не менее, я, пожалуй, напишу сервис на nodejs, который будет предоставлять интерфейс к этим таблицам и выполнять эти операции заместо триггеров.
Средний
