Большинство этих проблем связано с масштабируемостью. Это то, что не повлияет на вас, пока база данных мала. Но если когда-нибудь вам захочется, чтобы база данных перестала быть маленькой, о таких вещах стоит подумать заранее. В противном случае они нанесут вам удар исподтишка, возможно, в самый неудобный момент. Плюс во многих случаях работы потребуется меньше, если делать всё правильно с самого начала, чем менять уже работающую систему.

1. Не меняйте значение по умолчанию для work_mem

Самая большая ошибка, которую я совершил при первом развёртывании Postgres в продакшене, заключалась в том, что я не обновил значение по умолчанию для work_mem. Этот параметр определяет, сколько памяти доступно каждой операции запроса, прежде чем ей придётся начать записывать данные во временные файлы на диске; это может иметь огромное влияние на производительность.

Если вы не знаете об этом, то попасть в эту ловушку очень легко, потому что все запросы при локальной разработке обычно работают идеально. Возможно, так поначалу будет и в продакшене, у вас не возникнет никаких проблем. Но с ростом приложения и объёмы данных, и сложность запросов увеличатся. И только тогда вы начнёте сталкиваться с проблемами: классический сценарий «но на моей машине всё работало».

Когда использование памяти выходит за пределы work_mem, вы начнёте наблюдать всплески задержек, потому что данные будут сохраняться и загружаться из файлов, сильно замедляя операции с хэш-таблицами и сортировку. Снижение производительности будет огромным, а в некоторых случаях, в зависимости от инфраструктуры приложения, оно даже может приводить к полномасштабным отключениям.

Правильный подбор значения зависит от множества факторов: размеров вашего инстанса Postgres, частоты и сложности запросов, количества одновременных подключений. За этим аспектом всегда нужно следить.

Один из способов отслеживания тревожных признаков — это обработка логов в pgbadger. Ещё один способ — применение автоматизированной сторонней системы, отправляющей уведомления ещё до того, как это станет проблемой, например, pganalyze (примечание: я никак не связан с pganalyze, просто один из очень довольных пользователей).

Возможно, вы зададитесь вопросом, есть ли какая-то волшебная формула, позволяющая подобрать правильное значение work_mem. Её придумал не я, мне передали её седобородые мудрецы:

work_mem = ($YOUR_INSTANCE_MEMORY * 0.8 - shared_buffers) / $YOUR_ACTIVE_CONNECTION_COUNT

2. Перенесите всю логику приложения в функции и процедуры Postgres

У Postgres есть удобные абстракции для процедурного кода, и у вас может возникнуть искушение частично или полностью перенести логику приложения в слой базы данных. В конце концов, это устраняет задержку между кодом и данными, а таким образом снизится задержка и для пользователей, так ведь? Вообще-то нет.

Функции и процедуры в Postgres — это абстракции, имеющие ненулевую стоимость, она вычитается из общего бюджета производительности. Если вы тратите память и CPU на управление стеком вызовов, то этих ресурсов остаётся меньше на само выполнение запросов. В особо серьёзных случаях это может проявляться довольно удивительным образом, например, как необъяснимые всплески задержек и лаг репликации.

Простые функции вполне можно использовать, особенно если вы пометите их как IMMUTABLE или STABLE. Но каждый раз, когда у вас есть вложенные функции или рекурсия, стоит задуматься о том, можно ли перенести эту логику обратно в слой приложения.

И, разумеется, гораздо проще масштабировать узлы приложения, чем масштабировать базу данных. Возможно, стоит откладывать размышления о масштабировании базы данных как можно дольше, это позволяет консервативно относиться к использованию ресурсов.

3. Используйте много триггеров

Триггеры — это ещё одна возможность, которую легко использовать неверно.

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

Во-вторых, триггеры обычно стимулируют мышление, ориентированное на события. Как вы знаете, в SQL хорошей практикой стало объединение связанных запросов INSERT или UPDATE, чтобы блокировать таблицу единожды и записывать все данные за раз. Возможно, это делается в вашем коде автоматически и об этом не стоит даже задумываться. Однако триггеры могут превратиться в «слепое пятно».

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

Полезно здесь ограничить себя одним триггером BEFORE и одним триггером AFTER для каждой из таблиц. Давайте функциям триггеров обобщённые имена наподобие before_foo и after_foo, а затем храните всю логику встроенной внутрь одной функции. Используйте TG_OP, чтобы отличать операции триггеров. Если функция становится длинной, то разбейте её на части и дополните комментариями, но сдерживайте себя от искушения рефакторинга на более мелкие функции. Таким образом будет проще гарантировать эффективную реализацию операций записи, к тому же это ограничивает лишнюю трату ресурсов на управление расширенным стеком вызовов в Postgres.

4. Активно используйте NOTIFY

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

Если вы генерируете много событий, то ресурсов, потраченных на уведомление слушателей, может не хватать где-то ещё. Проблема может обостриться, если слушателям требуются дополнительные данные для обработки полезной нагрузки событий. Тогда вы будете расплачиваться за каждое событие NOTIFY плюс за каждую последующую операцию чтения в логике обработчика. Как и в случае с триггерами, это может стать «слепым пятном», скрывающим возможность объединения этих операций чтения и снижения нагрузки на базу данных.

Вместо применения NOTIFY задумайтесь о записи событий в таблицу FIFO и о потреблении их пакетно с регулярной периодичностью. Правильный подбор периодичности зависит от приложения, иногда это несколько секунд, а иногда можно обойтись и несколькими минутами. В любом случае, это уменьшит нагрузку, оставляя больше ресурсов CPU и памяти для других задач.

Возможная схема для таблицы очереди событий может выглядеть так:

CREATE TABLE event_queue (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type text NOT NULL,
  data jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  occurred_at timestamptz NOT NULL,
  acquired_at timestamptz,
  failed_at timestamptz
);

События из очереди можно получать вот так:

UPDATE event_queue
SET acquired_at = now()
WHERE id IN (
  SELECT id
  FROM event_queue
  WHERE acquired_at IS NULL
  ORDER BY occurred_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1000 -- Это ограничение следует задавать согласно сценарию вашего использования
)
RETURNING *;

Задание acquired_at при чтении и использование FOR UPDATE SKIP LOCKED гарантирует, что каждое событие будет обрабатываться только один раз. После их обработки полученные события тоже можно удалять пакетно (для постоянного хранения исторических событий данных неограниченного размера есть решения получше, чем Postgres).

5. Не используйте EXPLAIN ANALYZE с реальными данными

EXPLAIN — это базовый инструмент в наборе каждого бэкенд-разработчика. Я уверен, что вы уже старательно проверяете свои планы запросов на наличие зловещего Seq Scan. Однако Postgres может возвращать более точные данные планов, если вы используете EXPLAIN ANALYZE, потому что при этом действительно исполняется запрос. Разумеется, этого не стоит делать в продакшене. Поэтому чтобы правильно использовать EXPLAIN ANALYZE, стоит сначала выполнить несколько шагов.

Любой план запросов настолько хорош, насколько хороши данные, для которых он выполняется. Нет никакого смысла выполнять EXPLAIN для локальной базы данных, имеющей лишь несколько строк в каждой таблице. Возможно, вам повезло, и у вас есть исчерпывающий генерирующий скрипт, который заполняет локальный инстанс реалистичными данными, но даже в этом случае существует вариант получше.

Очень полезно бывает настроить отдельный инстанс-песочницу наряду с инфраструктурой продакшена, регулярно восстанавливаемый последним бэкапом с продакшена, в частности, для выполнения EXPLAIN ANALYZE для всех новых запросов, находящихся в разработке. Сделайте инстанс-песочницу меньше, чем инстанс продакшена, чтобы он был более ограниченным, чем продакшен. Благодаря этому EXPLAIN ANALYZE может дать вам уверенность в производительности запросов после их развёртывания. Если они хорошо проявляют себя в песочнице, то вас не должно ждать неприятных сюрпризов, когда они доберутся до продакшена.

6. Предпочитайте CTE вместо вложенных запросов

Примечание: благодарю Randommaggy и Ecksters за указание на то, что рекомендация использования вложенных запросов (subquery) в этом разделе устарела. С версии 12 Postgres стала гораздо лучше оптимизировать CTE и часто просто заменяет CTE вложенным запросом. Я оставил этот раздел, потому что более широкий смысл сравнения подходов при помощи EXPLAIN по-прежнему актуален, а документация по «CTE Materialization» всё ещё стоит прочтения.

Если вы регулярно используете EXPLAIN, то эта проблема, вероятно, у вас не возникнет, но она возникала у меня раньше, поэтому я хочу её упомянуть.

Многие разработчики мыслят снизу вверх, а CTE (например, запросы WITH) — это естественный способ выражения мышления снизу вверх. Но они могут и не быть самым производительным способом.

Я выяснил, что вложенные запросы часто исполняются гораздо быстрее. Разумеется, это полностью зависит от конкретного запроса, поэтому я не буду обобщать; скажу только, что нужно изучить оба подхода при помощи EXPLAIN для ваших сложных запросов.

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

7. Используйте CTE для критичных по времени запросов

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

Полезно здесь будет задуматься о том, как выглядит трафик приложения с точки зрения соотношения операций чтения и записи. Обычно операций чтения бывает гораздо больше, чем записи, и в этом случае следует подумать о денормализации графа в материализованное представление или таблицу, которая лучше оптимизирована для чтения. Если можно хранить каждый доступный для запроса подграф в его собственной строке, в том числе все соответствующие столбцы, необходимые для запросов, то чтение превращается в простой (и быстрый) SELECT. Разумеется, расплачиваться при этом придётся производительностью операций записи, но такой компромисс часто себя оправдывает.

8. Не добавляйте индексы для внешних ключей

Postgres не создаёт автоматически индексы для внешних ключей (foreign key).
Это может оказаться для вас неожиданностью, если вы больше знакомы с MySQL, поэтому обратите внимание на последствия, ведь это может различным образом навредить вам.

Наиболее очевидное следствие из этого заключается в производительности join, использующих внешний ключ. Но их легко выявить при помощи EXPLAIN, так что они вряд ли застанут вас врасплох.

Менее очевидное последствие — это производительность поведений ON DELETE и ON UPDATE. Если ваша схема зависит от каскадных удалений, то вам, возможно, удастся серьёзно улучшить производительность, добавив индексы для внешних ключей.

9. Сравнивайте индексированные столбцы при помощи IS NOT DISTINCT FROM

При использовании обычных операторов сравнения с NULL результатом тоже будет NULL, а не булево значение, как вы могли ожидать. Один из способов обойти это — заменить <> на IS DISTINCT FROM и заменить = на IS NOT DISTINCT FROM. Эти операторы обрабатывают NULL как обычное значение и всегда возвращают булевы значения.

Однако хотя = обычно приводит к тому, что планировщик запросов при наличии индекса использует его, IS NOT DISTINCT FROM пропускает индекс и с большой вероятностью выполнит Seq Scan. Это может сбить с толку, когда вы впервые увидите эту информацию в выводе EXPLAIN.

Если такое происходит и вы хотите принудительно заставить запрос использовать индекс, то можно сделать проверку на null явной, а затем использовать = для случая без null.

Иными словами, если у вас есть такой запрос:

SELECT * FROM foo
WHERE bar IS NOT DISTINCT FROM baz;

То можно сделать так:

SELECT * FROM foo
WHERE (bar IS NULL AND baz IS NULL)
OR bar = baz;