Попробуем аргументированно согласится или не согласится с автором.
work_mem по умолчанию.
work_mem - максимальный объем используемой памяти для выполнения запроса.
Если мы делаем JOINы (hash/merge join), CTE или сортировки, то значение work_mem важно для эффективного выполнения запроса.
Посмотреть текущее значение можно: SHOW work_mem;.
Для текущей сессии можно задать значение таким образом: SET [ SESSION | LOCAL ] work_mem TO '16MB';.
SET SESSION устанавливает значение параметра для текущей сессии, LOCAL для транзакции.
Но как понять какое значение устнавливать для work_mem?
Для этого нужно проверять вывод EXPLAIN (ANALYSE, BUFFERS):
если будет hash join, то вы увидите размер hash-таблицы для мержа (нужно искать "Memory usage").
Так, опытным путем станет понятно достаточно ли дефолтных 4Мб или это значение нужно увеличить.
Дефолтные 4Мб - это вполне достаточно для старта. Т.е. можно вполне запускаться в продакшн с этим дефолтом. Важно не упускать мониторинг за медленными запросами, в чем может помочь, например, pgBadger.
На самом деле это вполне себе неплохая идея. Я даже работал с биллинг системой в телекоме, где на хранимых процедурах было построено чуть больше чем все. Также хранимые процедуры можно писать не только на динамических ЯП вроде Python 3, TCL, Perl, но и на, например, Rust.
Преимущества: нет сетевых издержек. Недостаток: меньшая гибкость (в коде внести правки проще), проблемы с версионированием (если надо, чтобы работала как старая логика, так и новая).
В этом вопросе я расхожусь с мнением автора. Хранимые процедуры - отличные инструмент, который может прийтись в пору на орпеделенном этапе развития проекта.
Начинаем с простых ограничений (constraints): CHECK, NOT NULL, UNIQUE, PK, FK, EXCLUDE. Если необходимо вставить калькулированные данные в колонку, то лучше рассмотреть возможность использования сгенерированных колонок (GENERATED COLUMNS) или, если нужно формировать больше данных, на основе существующих, то обратиться к материализованным представлениям (MATERIALIZED VIEWS). Кстати, обновлять данные материализованных представлений можно с помощью REFRESH и делать это в самом PostgreSQL с использованием расширения pg_cron. Если эти средства исчерпаны, а цель не достигнута, то можно рассмотреть триггеры. Например, если надо убедиться в целостности данных, когда речь идет о других таблицах.
Триггеры не стоит рассматривать как модель событийно-ориентированной разработки. Например, не стоит логгировать в таблицу из триггеров, когда данные были добавлены в какую-то таблицу. Лучше предпочесть принцип "явное лучше неявного" и писать явно лог-таблицу в соседнем запросе.
Если нужно немедленно сообщить об успешном завершении транзакции, то можно использовать NOTIFY.
Это решение хорошо тем, что уведомления транзакционные.
Все слушающие стороны принимают просто строку.
Строку можно оформить в виде JSON-объекта, например, используя row_to_json функцию.
Проблема с этим решением в том, что все равно нужно где-то хранить payload уведомления на случай, если по какой-либо причине не было ни одного активного слушателя (LISTEN). Я поступаю так: делаю очередь прямо тут, в PostgreSQL (если это позволяют требования) и в добавок, как элемент усложнения, добавляю NOTIFY.
Для организации очереди создаем простую табличку, в которой по крону выбираем задачи.
Не забываем, что выборка может случится конкурентная, поэтому полезно использовать в SELECT-запросе
FOR UPDATE SKIP LOCKED.
Полностью согласен, что инструмент крайне полезный.
Чем больше и сложнее проект, тем чаще нужно анализировать ваши запросы с помощью EXPLAIN ANALYZE.
Там можно увидеть какие индексы используются, сколько потребляется памяти, как быстро выполняется запрос и т.д.
Особенно полезно прибегать к EXPLAIN для анализа запросов к большим таблицам или при слиянии таблиц (JOIN).
Чем хороши CTE? Читаемость, переиспользуемость (и рекурсии; с ними у меня очень мало опыта)! Особенно это удобно, когда у вас есть серия запросов, где один зависит от результатов предыдущего.
Итак, до 12 версии PostgreSQL, все CTE были MATERIALIZED.
Это значит, что результаты работы выражения сохранялись в оперативной памяти вроде как таблица,
у которой естественно нет индексов и любые операции с этими данными означала Seq Scan в EXPLAIN.
С 12 версии все запросы стали NOT MATERIALIZED.
Т.е. если планировщик посчитает, что можно превратить CTE в подзапрос, то он это сделает.
work_mem и тут о себе напоминает!
Промежуточные результаты работы CTE хранятся как раз в этой памяти.
Итого, можно вполне спокойно использовать CTE. Не забываем про п. 5 и проверяем эффективность выполнения запросов.
Все, что требует рекурсии в СУБД скорее всего может быть денормализовано так, чтобы рекурсия не была нужна. В случае древовидных структур есть nested set или полная денормализация графа. Да, во втором случае долгая запись, но зато очень быстрое чтение!
В MySQL индексы создаются автоматически для колонки, которая является FK. В PostgreSQL это не так. Без индексов каждый JOIN по FK становится ударом по ресурсам. Не забываем, что даже если мы не делаем JOIN по FK, но у нас есть каскадное удаление или обновление FK (ON DELETE, ON UPDATE) то индексы тоже нужны! Если не пренебрегать EXPLAIN, то вы точно не пропустите отсутствие индекса.
От себя я еще добавлю, что имеет смысл периодически обновлять PostgreSQL до более свежих версий. Как и со всеми обновлениями торопиться не стоит. В новых версиях как правило улучшается производительность, улучшается планировщик и т.д.