Когда вы разворачиваете веб-приложение, чаще всего у вас веб-сервер, бэкенд, база данных и авторизация оказываются на одном сервере. Первые пользователи, обычно тестировщики и менеджер, счастливы — все летает. Но потом приложение выходит в продакшн и начинается боль. Запросы тормозят и отвечают по пять секунд, CPU не загружен даже на треть, веб-сервер швыряет 504 Gateway Timeout и т. д. И вот вы сидите ночью и чините прод, потому что PostgreSQL — не просто «табличка с данными», а сложный инструмент с кэшем, индексами, буферами и планировщиком запросов.

Привет, Хабр! Меня зовут Александр Гришин, я руководитель по развитию продуктов хранения данных в Selectel и отвечаю за облачные баз данных и объектное S3 хранилище. В работе я часто сталкиваюсь с вопросами о производительности PostgreSQL, поэтому собрал практические советы для разработчиков, инженеров и архитекторов облачной инфраструктуры. В статье рассмотрим, как правильно использовать индексы, анализировать планы выполнения запросов и избегать типичных ошибок при проектировании схемы. Погнали!

Используйте навигацию, если не хотите читать текст целиком:
→ Выносите СУБД на отдельный сервер
→ Пишите запросы в ручную, ORM переоценен
→ Используйте EXPLAIN
→ Что значит Cost
→ Анти-паттерн SELECT *
→ Избегайте широких таблиц
→ Не избегайте JOIN
→ Что еще за индексы
→ Кластеризация таблиц
→ Статистика
→ Выводы

Выносите СУБД на отдельный сервер

Банально, но многие до сих пор держат все элементы системы на одной железке. Как только начинается серьезная нагрузка, появляется плохо прогнозируемая конкуренция за ресурсы сервера.

• PostgreSQL активно использует CPU, RAM и дисковую подсистему.
• Приложение — например, Django через Gunicorn — тоже хотят ресурсов.
• Redis, Celery, Nginx добавляют нагрузки.
• Со временем на сервере появляются агенты мониторинга.
• Логическая репликация передвигает данные между кластерами.
• А ночью включается полное резервное копирование.

В итоге мы имеем множество приложений и систем. У всех своя задача и специфика, а также свой профиль нагрузки на один общий сервер, который физически не сможет удовлетворить всех на должном уровне. Лучше выбрать для СУБД отдельный сервер со своими CPU, RAM и диском. Как правильно выбрать эти параметры, я рассказывал в прошлой статье.

Совет 1: выносите PostgreSQL на отдельный сервер или используйте готовый облачный сервис DBaaS, чтобы не конкурировать за ресурсы. Это дает прирост производительности и запас прочности системе.

Пишите запросы в ручную, ORM переоценен

ORM (Object-Relational Mapping, объектно-реляционное отображение) — это технология, которая позволяет разработчику работать с базой данных не напрямую через SQL-запросы, а через привычные объектно-ориентированные конструкции языка программирования.

Проще говоря, ORM — это прослойка между кодом и базой данных, которая:

• превращает строки из таблиц в объекты,
• позволяет писать классические методы и свойства вместо SQL,
• автоматически генерирует SQL-запросы под капотом.

Пример использования

Представим, что мы используем ORM в Python + Django вместо классического SELECT * FROM users:

# Модель в Django (ORM)
class User(models.Model):
    name = models.CharField(max_length=100)
    email = models.EmailField()
# Получение всех пользователей в Python без SQL
users = User.objects.all()

Да, ORM — это удобно, можно забыть об SQL на старте. До тех пор, пока user.objects.filter(...) не превращается в N+1 ад.

Но select_related и prefetch_related не спасут от неоптимальных джойнов. Логика работы BE становится «магической», разработчики теряют понимание того, что реально уходит в базу. Во время дебага инженеры не понимают, что происходит, получая странные артефакты в логах медленных запросов PostgreSQL.

Совет 2: не бойтесь писать сложные запросы на чистом SQL — это мощно и читаемо. Ценится сообществом за простоту дебага и оптимизации запросов в процессе эксплуатации приложения, например, через EXPLAIN ANALYZE.

Используйте EXPLAIN

Если запрос работает медленно, смотрите план выполнения. Это не магия, а обычная практика. PostgreSQL предоставляет инструмент для анализа SQL-запросов — EXPLAIN ANALYZE. Он показывает, как реально выполняется ваш запрос: какая ожидается нагрузка на CPU и диск, используются ли индексы, сколько строк будет прочитано, сколько времени тратится на каждый шаг. Это, возможно, самый мощный инструмент разработчика приложения при работе с PostgreSQL.

Пример использования

Предположим, у нас есть таблица:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT,
    created_at TIMESTAMP
);
CREATE INDEX idx_users_email ON users(email);

Теперь выполним запрос для анализа селекта полей id и name из нее для конкретного email:

EXPLAIN ANALYZE
SELECT id, name FROM users WHERE email = 'admin@example.com';

Допустим, мы получаем примерно такой вывод:

Index Scan using idx_users_email on users  (cost=0.29..8.30 rows=1 width=36)
  Index Cond: (email = 'admin@example.com')
  Actual time: 0.015..0.017 rows=1 loops=1
Planning Time: 0.083 ms
Execution Time: 0.028 ms

Что он значит? Давайте разберем построчно:

Index Scan using idx_users_email on users — PostgreSQL использует индекс idx_users_email. Если мы увидим, что PostgreSQL делает Seq Scan (последовательное чтение всей таблицы), то у нас нет индекса или он почему-то не используется.

cost=0.29..8.30 — это оценка стоимости выполнения:

• 0.29 — стоимость старта (подготовка к выполнению),
• 8.30 — стоимость полного выполнения.

Значения приведены не в миллисекундах. Это относительные единицы, используемые планировщиком для сравнения разных планов. Чуть подробнее расскажу в следующем разделе.

rows=1 width=36 — ожидается одна строка, ее ширина в байтах — 36.

Index Cond: (email = 'admin@example.com') — условие, по которому Postgres использует индекс.

Actual time: 0.015..0.017 — реальное время выполнения (в миллисекундах): от начала до конца этой операции.

rows=1 — реально возвращена одна строка. Если мы видим, что PostgreSQL обрабатывает намного больше строк, чем ожидалось, то статистика устарела и это нужно исправлять.

loops=1 — эта операция выполнялась один раз (если бы было в цикле, значение было бы больше). Если PostgreSQL делает много вложенных циклов (loops), возможна N+1 проблема или неэффективный JOIN. Анализируем наш запрос на предмет оптимизации.

Planning Time: 0.083 ms — сколько времени заняло построение плана выполнения.

Execution Time: 0.028 ms — общее время выполнения запроса.

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

Что значит Cost

Когда выше запускали EXPLAIN или EXPLAIN ANALYZE, мы видели загадочные числа для cost вроде 0.00..431.0:

Seq Scan on users  (cost=0.00..431.00 rows=10000 width=64)

• 0.00 — это стоимость начала выполнения узла. Например, инициализация сканирования.
• 431.00 — стоимость полного выполнения узла, включая чтение всех строк.
• rows=10000 — прогноз о том, сколько строк будет обработано.
• width=64 — средний размер строки в байтах.

Это оценочная стоимость выполнения плана запроса в условных единицах, рассчитанная PostgreSQL на основе количества строк, числа страниц, объема операций чтения с диска (I/O) и процессорной нагрузки (CPU).

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

cost = seq_page_cost × N_pages + cpu_tuple_cost × N_tuples

• seq_page_cost — стоимость чтения одной страницы с диска (по умолчанию 1.0).
• N_pages — число страниц таблицы, которые нужно прочитать.
• cpu_tuple_cost — стоимость обработки одной строки CPU (по умолчанию 0.01).
• N_tuples — количество обрабатываемых строк (tuples).

Пример расчета для запроса

Предположим, таблица «users» содержит 5 000 строк и 200 страниц по 8 КБ. Конфигурация кластера PostgreSQL по умолчанию:

• seq_page_cost = 1.0;
• cpu_tuple_cost = 0.01.

Тогда получаем:

cost = 1.0 × 200 + 0.01 × 5000 = 200 + 50 = 250.00

Эта стоимость отразится в EXPLAIN как total cost 250.00:

Seq Scan on users (cost=0.00..250.00 rows=5000 width=...)

Что дает нам это понимание

• Сравнение планов: оптимизатор выбирает план с минимальной стоимостью. Если вы хотите, чтобы использовался другой, надо повлиять на cost.
• Оценка эффективности: если cost огромный, значит запрос реально тяжелый, даже если сейчас он работает быстро (например, из-за кэширования).
• Быстрые диски очень важны для PostgreSQL: подробнее я разбирал вопрос в прошлой статье.

Совет 4: понимание того, откуда берется cost — ключ к предсказуемому управлению производительностью. Это честный расчет, который можно проверить формулой. И если ваш запрос стоит 10 000 единиц, это не Postgres тормозит, это вы просите его сделать очень много работы.

Анти-паттерн SELECT *

Если вы пишете SELECT *, то:

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

Будет особенно больно, если в таблице есть большие объекты: TEXT, JSONB, TOAST-поля и прочее.

Совет 5: запрашивайте только нужные вам для задачи поля SELECT id, name, price.

Избегайте широких таблиц

Широкие таблицы — это таблицы с десятками и сотнями колонок. В корпоративных хранилищах данных (КХД) или DWH легко встретить таблицы на 300-700 колонок: характеристики товара, анкета пользователя, мета-данные, флаги, события и прочее. Однако PostgreSQL не проектировалась для такой нагрузки. Что мы получаем в результате?

• Неоптимальная загрузка с диска: даже если вам нужно пять полей, Postgres все равно прочитает целую страницу данных.
• Кэш: чем шире строка, тем меньше строк помещается в буферной памяти;
• SELECT может случайно вытащить гигабайты данных.
• Обслуживание и сопровождение схемы становится болью, даже IDE будет тормозить при открытии такой таблицы.

Лучше делите данные на логически связанные части: часто используемые поля — в одну таблицу, редко используемые — в другую, связав их один к одному. Это не только ускорит запросы, но и облегчит понимание структуры данных.

Однако если вы действительно работаете с аналитикой, BI-отчетами, КХД, Data Lake и витринами данных, а ваши тысячи колонок — не баг, а фича, то рассмотрите использование другого инструмента вместо PostgreSQL. Например, ClickHouse.

Это колоночная аналитическая СУБД специально спроектированная под такие задачи. ClickHouse не заменит PostgreSQL, но идеально дополнит его, например для аналитики.

В архитектуре вашей информационной системы это может выглядеть так:

• PostgreSQL — основная OLTP база (транзакции, бизнес-логика);
• ClickHouse — отдельный аналитический слой (агрегации, отчеты, дашборды);
• синхронизация — через CDC, Materialized View, Kafka или batched ETL.

Совет 6: если широкие таблицы — случайная ошибка в архитектуре, нормализуйте схему данных. Но если это осознанный выбор, используйте более подходящий инструмент, например ClickHouse.

Не избегайте JOIN

«JOIN — это медленно!» — миф из 2007. PostgreSQL давно умеет эффективно соединять таблицы по запросу.

• Hash Join — когда хватает памяти,
• Merge Join — при наличии сортировки,
• Nested Loop — для маленьких выборок.

Совет 7: проектируйте нормализованную схему и не бойтесь джойнов. Главное — наличие индексов по ключам соединения.

Что еще за индексы

Индекс в БД — это структура данных, которая позволяет находить строки не перебором всех записей, а быстрым прыжком в нужное место. В PostgreSQL по умолчанию используется индекс типа B-Tree (сбалансированное дерево). Без индекса БД будет вынуждена последовательно читать всю таблицу, сравнивая каждую строку. С индексом она сначала ищет в B-Tree нужное значение, а уже потом извлекает строку по ссылке из таблицы.

Однако индексы — не панацея, поэтому не стоит просто пихать их всюду. Помните, что они:

• не бесплатны, занимают место и ресурсы на обновление;
• могут игнорироваться в определенных ситуациях — например, если запрос фильтрует по часто встречающемуся значению, будет использоваться последовательное чтение всей таблицы. А как иначе отфильтровать данные?

Пример оценки

Допустим, у нас есть все та же таблица users на 100 000 строк:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT
);

Сравним два варианта выполнения запроса SELECT * FROM users WHERE email = 'admin@example.com' с использованием индекса и без.

Без использования индекса:

EXPLAIN
SELECT * FROM users WHERE email = 'admin@example.com';
Seq Scan on users  (cost=0.00..1750.00 rows=1 width=64)
  Filter: (email = 'admin@example.com')

Видим, что PostgreSQL будет использовать Seq Scan — последовательное сканирование таблицы. А стоимость — 1 750 единиц, потому что нам придется прочитать все 100 000 строк.

Посмотрим, что будет с использованием индекса:

CREATE INDEX idx_users_email ON users(email);
EXPLAIN
SELECT * FROM users WHERE email = 'admin@example.com';
Index Scan using idx_users_email on users  (cost=0.29..8.30 rows=1 width=64)
  Index Cond: (email = 'mailto:admin@example.com')

Теперь PostgreSQL будет использовать Index Scan вместо Seq Scan. И это почти в 200 раз быстрее с оценочной стоимостью около восьми единиц. Однако само по себе наличие индекса не гарантирует, что он будет использоваться. PostgreSQL выбирает то, что по статистике и конфигурации выглядит дешевле.

Совет 8: индекс — это как оглавление в книге. Без него листаем всю книгу последовательно. С ним сначала читаем оглавление, потом открываем нужную страницу за секунду. Правильные индексы важны для производительности PostgreSQL. Но чтобы они работали, важно понимать, как именно Postgres их применяет.

Кластеризация таблиц

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

Когда вы создаете B-Tree индекс, PostgreSQL строит отдельную структуру, но сама таблица остается в хаотичном порядке. Допустим, после этого вы выполните:

CLUSTER table_name USING index_name;

PostgreSQL перезаписывает таблицу на диск так, чтобы строки лежали в том же порядке, что и в индексе. Стоит учитывать, что CLUSTER — это сложная ручная операция с данными, блокирующая вашу таблицу.

Что еще нужно знать о кластеризации

• Не поддерживается автоматически, после каждой INSERT/UPDATE порядок может снова стать хаотичным.
• Чтобы сохранить выгоду, нужно периодически повторять CLUSTER и использовать утилиту pg_repack.
• Таблица полностью блокируется на время кластеризации — не делайте это на проде без планирования.

Использование CLUSTER выгодно, когда вы точно понимаете, что делайте. Например, когда вы каждый день читаете заказы за последнюю неделю:

SELECT * FROM orders WHERE created_at >= now() - interval '7 days';

Если данные на диске идут подряд, PostgreSQL будет считывать одну страницу за другой, без прыжков по файлу. Это может ускорить чтение в несколько раз, что будет особенно заметно на больших объемах данных.

Совет 9: CLUSTER — это способ положить строки в таблице в том же порядке, что и в индексе. Это может существенно ускорить выборки по диапазонам и повторяющимся значениям. Но требует ручного контроля и аккуратности при использовании.

Статистика

Выше я описал, как PostgreSQL оценивает и выбирает тот или иной план выполнения запросов. Но откуда он берет данные для расчета?

PostgreSQL берет данные и принимает решение на основе статистики запросов, таблиц и колонок. Если статистика устарела или неполная, выбор плана может быть ошибочным. Это может приводить к неожиданным Seq Scan, игнорированию индексов и тормозам.

Статистика — это информация, которую PostgreSQL собирает о данных: количество строк в таблице, распределении значений, средняя длина строк, selectivity, и т. д. Эти данные используются планировщиком запросов (query planner) при оценке стоимости запроса.

Проблемы возникают, когда вы не обновляете статистику, но при этом:

• добавили или удалили много строк,
• поменяли распределение значений (например, 90% теперь имеют status = 'archived'),
• создали новые таблицы и загрузили в них данные,
• импортировали новые данные или реплицировали их из другого кластера,
• регулярно очищаете таблицы (например, партиции или логи).

Запросы рано или поздно будут планироваться и оцениваться неверно.

Примеры обновления статистики

Обновить статистику для одной таблицы users:

ANALYZE users;

Для всей базы:

ANALYZE

После массовых изменений для освобождения места:

VACUUM ANALYZE

PostgreSQL умеет сам запускать ANALYZE и VACUUM, если включен параметр autovacuum = on. Однако настройка этой части БД не ограничивается только этим параметром. И в сущности ситуация тут немного сложнее.


Параметры настройки autovacuum в базах данных Selectel.

Бывают ситуации, когда автоанализ не успевает срабатывать (слишком много изменений) или не видит необходимости, если пороги autovacuum_analyze_threshold и scale_factor настроены неоптимально. Увы, но раскрытие темы оптимальных настроек этих параметров тянет на отдельную статью.

Проверка актуальности статистики

Проверить дату последнего ANALYZE можно, используя следующую конструкцию:

SELECT relname, last_analyze, n_live_tup
FROM pg_stat_user_tables
ORDER BY last_analyze NULLS FIRST;

Совет 10: если планировщик запросов делает странные расчеты, в 50% случаев виновата устаревшая статистика. EXPLAIN и ANALYZE покажут, как он «видит» план, но только вы можете убедиться, что он видит реальность. Следите за статистикой — это эффективный способ ускорить запросы.

Выводы

PostgreSQL — мощная и гибкая СУБД. Но чтобы она работала действительно эффективно, нужно уделять внимание тому, как все устроено. Сегодня я по верхам разобрал ключевые аспекты, влияющие на производительность. Резюмирую.

• Не размещайте все на одном сервере. Вынос PostgreSQL в отдельный узел или использование DBaaS снижает конкуренцию за ресурсы и повышает стабильность.
• Не надейтесь слепо на ORM. Иногда проще и быстрее написать SQL-запрос руками, чем разбираться с магией select_related, prefetch_related и N+1 проблемами.
• Используйте EXPLAIN ANALYZE. Это основной инструмент для понимания того, что реально происходит при выполнении запроса.
• Разбирайтесь в cost. Эти числа отражают оценку ресурсоемкости. На основе cost PostgreSQL строит план запроса, а вы можете читать и понимать, как все устроено.
• SELECT * — зло. Особенно в больших таблицах с JSON и TEXT. Выбирайте только нужные поля.
• Широкие таблицы — не для OLTP. Если у вас аналитика с сотнями колонок, подумайте о ClickHouse как о дополнении к PostgreSQL. В иных случаях вы ошиблись с архитектурой, переработайте ее.
• JOIN — это не страшно. Главное — правильная схема и индексы по ключам.
• Индексы важны, но не всегда спасают. Понимание, как, когда и почему они применяются, важный навык инженера.
• CLUSTER может серьезно ускорить работу, особенно при выборках по диапазону. Но требует ручной работы и аккуратности.
• Актуальная статистика — залог правильных решений оптимизатора. Без нее даже хорошие запросы превращаются в медленные.

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

Совет 11: нет лучшего рецепта, чем найм DBA-инженера себе в штат. Очень рекомендую.

Что дальше

Проверяйте свои запросы. Читайте планы. Используйте инструменты PostgreSQL не только как хранилище данных, но и как аналитическую платформу. Кстати, не так давно мы представили первый в России сервис готовых облачных баз данных на выделенном сервере. Это ультимативный сервис, призванный предоставить максимально возможную производительность готовых СУБД для наших клиентов.

Спасибо, что дочитали! Если статья была полезна, поделитесь в комментариях своими кейсами оптимизации или болью, с которой вы сталкивались. С радостью продолжу тему в следующем материале.