Техники оптимизации PostgreSQL, которые я применяю на продакшн-базах. EXPLAIN ANALYZE, стратегии индексирования, переписывание запросов и конкретные изменения, которые сократили время запросов с секунд до микросекунд.
В прошлом месяце мне пришло сообщение в Slack в 2 часа ночи: «Дашборд таймаутится». Я зашёл по SSH на продакшн-сервер, открыл pg_stat_activity и нашёл один запрос, сканирующий 14 миллионов строк, чтобы вернуть 12 результатов. Фикс был — частичный индекс, на создание которого ушло 30 секунд. Запрос перешёл с 4.2 секунд на 0.3 миллисекунды.
Вот в чём дело с производительностью PostgreSQL. Проблемы почти никогда не экзотичные. Это пропущенные индексы, устаревшая статистика, запросы, которые имели смысл, когда в таблице было 10 000 строк, а теперь их 10 миллионов. Фиксы обычно простые — когда знаешь, куда смотреть.
Этот пост — всё, что я узнал об оптимизации запросов PostgreSQL при работе с продакшн-базами данных. Никакой теории без практики. Каждая техника здесь сэкономила мне реальное время на реальных системах.
Самая большая ошибка, которую я вижу у разработчиков с медленными запросами — угадывание. «Может, нам нужен индекс на эту колонку». «Может, JOIN медленный». «Может, нужно добавить больше RAM».
Перестань гадать. У PostgreSQL один из лучших анализаторов запросов среди всех баз данных. Используй его.
Обычный EXPLAIN показывает, что PostgreSQL планирует сделать, не выполняя запрос:
AkousaEXPLAIN SELECT * FROM orders WHERE customer_id = 42; QUERY PLAN
---------------------------------------------------------------------------
Index Scan using idx_orders_customer_id on orders (cost=0.43..8.45 rows=1 width=128)
Index Cond: (customer_id = 42)
Это говорит план — index scan — и предполагаемую стоимость. Но это прогноз. PostgreSQL может быть совершенно неправ в количестве строк. Я видел оценки «1 строка», когда реальный результат был 50 000 строк. Такая ошибка оценки каскадирует в ужасные решения планировщика.
Используй обычный EXPLAIN, когда хочешь быстро посмотреть план, не выполняя запрос. Это важно, когда анализируешь DELETE, который изменит данные, или запрос, выполняющийся 30 минут.
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42; QUERY PLAN
---------------------------------------------------------------------------
Index Scan using idx_orders_customer_id on orders (cost=0.43..8.45 rows=1 width=128)
(actual time=0.021..0.023 rows=1 loops=1)
Index Cond: (customer_id = 42)
Planning Time: 0.085 ms
Execution Time: 0.041 ms
Теперь ты видишь реальное время выполнения и реальное количество строк. Именно здесь происходит настоящая отладка. Сравни rows=1 (оценка) с rows=1 (реальность) — они совпадают, значит планировщик принял хорошее решение. Когда не совпадают — это первая подсказка.
Предупреждение: EXPLAIN ANALYZE действительно выполняет запрос. Если анализируешь UPDATE или DELETE, оберни в транзакцию:
BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;Вот что я реально использую в 90% случаев:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
AND o.status = 'completed'; QUERY PLAN
---------------------------------------------------------------------------
Hash Join (cost=425.00..1893.25 rows=2150 width=48)
(actual time=2.341..15.892 rows=2347 loops=1)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=312 read=45
-> Bitmap Heap Scan on orders o (cost=12.50..1450.75 rows=2150 width=32)
(actual time=0.512..12.105 rows=2347 loops=1)
Recheck Cond: (created_at > '2026-01-01'::date)
Filter: (status = 'completed')
Rows Removed by Filter: 893
Heap Blocks: exact=189
Buffers: shared hit=195 read=45
-> Bitmap Index Scan on idx_orders_created_at (cost=0.00..11.96 rows=3043 width=0)
(actual time=0.287..0.287 rows=3240 loops=1)
Index Cond: (created_at > '2026-01-01'::date)
Buffers: shared hit=12
-> Hash (cost=287.50..287.50 rows=10000 width=24)
(actual time=1.753..1.753 rows=10000 loops=1)
Buffers: shared hit=117
-> Seq Scan on customers c (cost=0.00..287.50 rows=10000 width=24)
(actual time=0.008..0.892 rows=10000 loops=1)
Buffers: shared hit=117
Planning Time: 0.215 ms
Execution Time: 16.147 ms
Вывод BUFFERS критически важен. shared hit=312 означает, что 312 страниц пришли из буферного кэша (RAM). read=45 означает, что 45 страниц пришлось прочитать с диска. Если видишь много read относительно hit, возможно, shared_buffers слишком мал, или таблица слишком велика, чтобы помещаться в кэше.
Я также использую FORMAT JSON, когда нужно вставить план в explain.dalibo.com для визуализации. Древовидный вид значительно упрощает чтение сложных планов:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;Вот полный диагностический запрос, который я запускаю первым при любом расследовании медленного запроса:
EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ...;TIMING — тайминг по каждому узлу (включён по умолчанию с ANALYZE, но я указываю явно)VERBOSE — показывает списки выходных колонок и имена таблиц с указанием схемыКаждый план запроса — это дерево. PostgreSQL читает его снизу вверх: самые глубоко вложенные узлы выполняются первыми, передавая результаты наверх. Понимание типов сканирования — это фундамент всего остального.
Seq Scan on orders (cost=0.00..25000.00 rows=1000000 width=128)
Последовательное сканирование читает каждую строку в таблице. Для миллионной таблицы — это каждая страница на диске.
Но вот нюанс: Seq Scan — это не всегда плохо. Если ты выбираешь 30% или более таблицы, последовательное сканирование реально быстрее, чем сканирование по индексу, потому что последовательный ввод-вывод значительно быстрее случайного. PostgreSQL это знает. Если он выбирает Seq Scan при наличии индекса, проверь, насколько на самом деле селективно твоё условие WHERE.
Когда это проблема: когда выбираешь крошечную долю строк из большой таблицы, а индекса нет.
Index Scan using idx_orders_customer_id on orders (cost=0.43..8.45 rows=5 width=128)
Index Cond: (customer_id = 42)
Обращается к B-tree индексу, находит подходящие записи, затем извлекает реальные строки из кучи таблицы. Каждое извлечение строки — это операция случайного ввода-вывода к куче. Отлично для высокоселективных запросов, но каждое обращение к куче имеет стоимость.
Index Only Scan using idx_orders_customer_total on orders (cost=0.43..4.15 rows=5 width=12)
Index Cond: (customer_id = 42)
Heap Fetches: 0
Это самый быстрый тип сканирования. PostgreSQL получает всё необходимое только из индекса, не обращаясь к куче таблицы. Ты видишь это, когда все колонки в SELECT и WHERE содержатся в индексе.
Подвох: Heap Fetches: 0 означает, что карта видимости актуальна. Если в таблице много мёртвых кортежей (не было вакуума недавно), PostgreSQL всё равно должен проверить кучу для подтверждения видимости строк. Это одна из причин, почему autovacuum важен для производительности, а не только для дискового пространства.
Bitmap Heap Scan on orders (cost=45.00..1250.00 rows=5000 width=128)
Recheck Cond: (created_at > '2026-01-01')
-> Bitmap Index Scan on idx_orders_created_at (cost=0.00..43.75 rows=5000 width=0)
Index Cond: (created_at > '2026-01-01')
Bitmap-сканирование — это ответ PostgreSQL на проблему «слишком много строк для index scan, слишком мало для seq scan». Оно строит битовую карту страниц с подходящими строками, сортирует их по физическому расположению, затем извлекает по порядку. Это превращает случайный ввод-вывод в последовательный.
Часто bitmap-сканирование встречается, когда комбинируются два или более индексов:
Bitmap Heap Scan on orders
Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
-> BitmapAnd
-> Bitmap Index Scan on idx_orders_status
Index Cond: (status = 'completed')
-> Bitmap Index Scan on idx_orders_created_at
Index Cond: (created_at > '2026-01-01')
Этот BitmapAnd комбинирует два отдельных индекса. PostgreSQL говорит: «У меня нет составного индекса, но я могу объединить эти два одноколоночных индекса». Это работает, но правильный составной индекс был бы быстрее.
Первое, на что я смотрю в плане запроса — несовпадающие оценки:
Nested Loop (cost=0.43..50.12 rows=1 width=128)
(actual time=0.025..4521.337 rows=48753 loops=1)
Планировщик оценил 1 строку. Реальный результат — 48 753 строки. Это ошибка оценки на пять порядков. Планировщик выбрал Nested Loop, потому что думал, что джойнит к 1 строке. С 48 753 строками Hash Join был бы на порядки быстрее.
Распространённые причины плохих оценок:
ANALYZE на таблицеstatus = 'shipped' и created_at > '2026-01-01' коррелированы (большинство недавних заказов отправлены), планировщик недооценивает комбинированную селективностьКогда видишь плохие оценки, фикс обычно один из: запустить ANALYZE, создать расширенную статистику, переписать запрос или использовать CTE как забор оптимизации.
-- Создаём расширенную статистику для коррелированных колонок
CREATE STATISTICS orders_status_date (dependencies)
ON status, created_at FROM orders;
ANALYZE orders;Индексы не бесплатны. Каждый индекс замедляет записи, занимает дисковое пространство и требует обслуживания. Цель не «индексировать всё» — а «индексировать именно то, что нужно».
B-tree — индекс по умолчанию, покрывающий подавляющее большинство случаев. Он поддерживает операторы равенства и диапазона (=, <, >, <=, >=, BETWEEN, IN, IS NULL).
CREATE INDEX idx_orders_created_at ON orders (created_at);Порядок колонок в B-tree индексе критически важен для составных индексов. Крайняя левая колонка — это первичная сортировка, затем следующая и так далее. Индекс на (a, b, c) может эффективно ответить на:
WHERE a = 1WHERE a = 1 AND b = 2WHERE a = 1 AND b = 2 AND c = 3WHERE a = 1 AND b > 5WHERE a = 1 ORDER BY bОн не может эффективно ответить на:
WHERE b = 2 (пропускает первую колонку)WHERE c = 3 (пропускает первые две колонки)WHERE a = 1 AND c = 3 (пробел в середине — условие по a используется, условие по c требует фильтрации)Думай об этом как о телефонной книге, отсортированной по фамилии, затем по имени. Ты можешь быстро найти все записи «Иванов» или конкретно «Иванов, Пётр». Но ты не можешь быстро найти всех «Пётр» без сканирования всей книги.
Правило: ставь условия равенства первыми, затем условия диапазона, затем колонки сортировки.
-- Запрос: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
-- Хорошо: равенство первым, затем диапазон, затем сортировка
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
-- Плохо: диапазон перед равенством
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);PostgreSQL 11+ позволяет добавлять неключевые колонки в индекс с INCLUDE. Эти колонки хранятся в листовых страницах, но не являются частью структуры B-tree. Они обеспечивают index-only сканирование, не раздувая дерево:
-- Мы часто выполняем:
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);Теперь PostgreSQL может удовлетворить весь запрос только из индекса. Без INCLUDE ему нужно было бы извлечь total и status из кучи. Разница на холодном кэше может быть огромной — я видел, как запросы переходили с 50мс на 0.2мс просто от добавления колонок INCLUDE.
Это моя единственная самая любимая фича PostgreSQL и та, о которой большинство разработчиков не знают.
-- Только 2% заказов имеют статус 'pending', но мы запрашиваем их постоянно
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';Этот индекс крошечный по сравнению с полным индексом на created_at. Он содержит только строки, где status = 'pending'. Запросы, соответствующие условию WHERE, используют этот маленький, быстрый индекс. Запросы, не соответствующие — игнорируют его.
Реальный пример из продакшена: у меня была таблица sessions с 50 миллионами строк. Только около 200 000 были активными (не истёкшими). Полный индекс на user_id занимал 1.2 ГБ. Частичный индекс WHERE expires_at > now() занимал 8 МБ. Запрос перешёл с 12мс на 0.1мс, потому что весь индекс помещался в кэше.
-- Ещё один частый паттерн: мягкое удаление
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
-- Уникальное ограничение только на активные записи
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;Последний невероятно полезен. Он позволяет иметь ограничение уникальности email для активных пользователей, при этом разрешая тот же email в удалённых записях.
GIN (Generalized Inverted Index) — ответ, когда нужно искать внутри значений — массивы, JSONB-документы или векторы полнотекстового поиска.
-- Запросы на содержание JSONB
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
-- Теперь это быстро:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
-- Полнотекстовый поиск
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));GIN-индексы большие и медленно строятся, но молниеносно быстрые при запросах. Они стоят того для нагрузок с преобладанием чтения и сложными запросами на содержание.
Для JSONB есть также jsonb_path_ops, который меньше и быстрее для запросов на содержание @>, но не поддерживает операторы существования (?, ?|, ?&):
CREATE INDEX idx_products_metadata_pathops
ON products USING GIN (metadata jsonb_path_ops);GiST (Generalized Search Tree) обрабатывает перекрывающиеся типы данных: геометрические фигуры, диапазоны, полнотекстовый поиск (альтернатива GIN, меньше, но медленнее).
-- Поиск по IP-диапазонам
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
-- Временные диапазоны (конфликты бронирования)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
tstzrange(start_time, end_time)
);
-- Найти пересекающиеся бронирования:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');Hash-индексы полезны только для сравнения на равенство. Начиная с PostgreSQL 10 они записываются в WAL и безопасны при сбоях. Они меньше B-tree для широких колонок и немного быстрее для чистого равенства:
-- Хорошо для поиска только по равенству на больших текстовых колонках
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
-- Это быстро:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
-- Это НЕ МОЖЕТ использовать hash-индекс:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';На практике я редко использую hash-индексы. B-tree прекрасно обрабатывает равенство, а невозможность запросов по диапазону или сортировки делает hash-индексы слишком негибкими для большинства реальных случаев.
Каждый индекс имеет стоимость:
Я регулярно провожу аудит индексов на продакшн-таблицах:
-- Найти неиспользуемые индексы
SELECT
schemaname, tablename, indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelid NOT IN (
SELECT conindid FROM pg_constraint
WHERE contype IN ('p', 'u') -- исключаем первичные ключи и ограничения уникальности
)
ORDER BY pg_relation_size(indexrelid) DESC;Я находил многогигабайтные индексы, созданные во время одноразовой миграции и больше никогда не использовавшиеся. Их удаление заметно ускорило записи.
-- Найти дублирующиеся индексы (те же колонки, тот же порядок)
SELECT
a.indexrelid::regclass AS index1,
b.indexrelid::regclass AS index2,
a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
AND a.indexrelid < b.indexrelid
AND a.indkey = b.indkey;Каждый ORM-разработчик рано или поздно с этим столкнётся. Это самая распространённая проблема производительности, которую я отлаживаю.
# Python / SQLAlchemy — классическая ловушка
orders = session.query(Order).filter_by(status='pending').all()
for order in orders:
# Это вызывает отдельный запрос для КАЖДОГО заказа
print(f"Customer: {order.customer.name}")Это генерирует:
-- Запрос 1: Получить все ожидающие заказы
SELECT * FROM orders WHERE status = 'pending';
-- Запрос 2: Получить клиента для заказа 1
SELECT * FROM customers WHERE id = 101;
-- Запрос 3: Получить клиента для заказа 2
SELECT * FROM customers WHERE id = 102;
-- ...Запрос 502: Получить клиента для заказа 500
SELECT * FROM customers WHERE id = 600;501 запрос вместо 1 или 2. Каждый запрос быстрый по отдельности, может 0.5мс. Но 501 из них суммируется в 250мс только времени базы данных, плюс сетевая задержка на каждый round-trip.
Самый быстрый способ поймать N+1 запросы — временно включить логирование запросов:
-- Логируем все запросы, которые выполняются дольше 0мс (т.е. все запросы)
SET log_min_duration_statement = 0;
-- Лучше для продакшена: логируем только медленные запросы
ALTER SYSTEM SET log_min_duration_statement = 50; -- порог 50мс
SELECT pg_reload_conf();Затем смотри логи. N+1 невозможно не заметить — ты увидишь сотни идентичных запросов с разными значениями параметров в быстрой последовательности.
Более точный подход для разработки:
-- Включаем auto_explain для медленных запросов
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100; -- 100мс
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;Это логирует полный вывод EXPLAIN ANALYZE для любого запроса свыше 100мс, включая запросы внутри функций.
ORM-подход — скажи ORM загрузить связанные данные заранее:
# SQLAlchemy — joinedload загружает клиентов в том же запросе
from sqlalchemy.orm import joinedload
orders = (
session.query(Order)
.options(joinedload(Order.customer))
.filter_by(status='pending')
.all()
)Подход на чистом SQL — просто используй JOIN:
SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';Один запрос. Готово.
Здесь есть обоснованная дискуссия. Иногда два запроса лучше, чем JOIN:
-- Подход 1: Один JOIN (может дублировать данные при one-to-many)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
-- Подход 2: Два запроса (меньше передачи данных, если у заказов много колонок)
SELECT * FROM orders WHERE status = 'pending';
-- приложение собирает order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);Подход с JOIN дублирует данные заказа для каждого товара. Если в каждом заказе 20 товаров, а строка заказа широкая — это 20-кратная передача данных. Подход с двумя запросами отправляет каждый заказ ровно один раз.
Моё правило: используй JOIN'ы для связей один-к-одному, рассматривай отдельные запросы для один-ко-многим, когда сторона «один» широкая. Но всегда делай бенчмарк — задержка сетевого round-trip для второго запроса часто стоит дороже, чем дублированные данные.
Некоторые запросы медленные не из-за отсутствующих индексов, а из-за того, как они написаны. Планировщик PostgreSQL хорош, но не волшебник.
Эти три подхода могут создавать очень разные планы:
-- Подзапрос в WHERE — часто нормально, иногда ужасно
SELECT * FROM orders
WHERE customer_id IN (
SELECT id FROM customers WHERE country = 'US'
);
-- JOIN — обычно лучший выбор
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
-- CTE — материализуется в PG 11 и ниже, оптимизируется в PG 12+
WITH us_customers AS (
SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;В PostgreSQL 12+ CTE обычно инлайнятся (планировщик обрабатывает их как подзапросы), поэтому производительность идентична. Но в PostgreSQL 11 и ниже CTE являются заборами оптимизации — планировщик материализует их и не может протолкнуть предикаты через них. Если ты всё ещё на PG 11, избегай CTE для критичных по производительности запросов.
Это всплывает постоянно:
-- EXISTS — останавливается на первом совпадении, отлично для проверки существования
SELECT * FROM orders o
WHERE EXISTS (
SELECT 1 FROM returns r WHERE r.order_id = o.id
);
-- IN с подзапросом — сначала строит полный набор результатов
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
-- JOIN — может создать дубликаты, если returns имеет несколько строк на заказ
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;Для больших таблиц EXISTS часто побеждает, потому что замыкается на первом совпадении. Версия с IN должна построить весь список order_id возвратов перед фильтрацией. Версия с JOIN может создать дубликаты, требуя DISTINCT, который добавляет шаг сортировки или хеширования.
Мой стандарт: используй EXISTS, когда проверяешь существование связанных строк. Это самый семантически ясный и обычно самый быстрый вариант.
Но есть контрпример. Если набор результатов подзапроса маленький и нужен для нескольких условий:
-- Если подзапрос возвращает мало строк, IN вполне нормален
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, 4, 5);Это не просто предпочтение в стиле кода. Это имеет реальные последствия для производительности:
-- Плохо: извлекает все 25 колонок, включая TEXT-колонку с килобайтами данных
SELECT * FROM products WHERE category = 'electronics';
-- Хорошо: извлекает только то, что нужно
SELECT id, name, price FROM products WHERE category = 'electronics';С SELECT *:
Оконные функции — одна из самых мощных фич PostgreSQL и почти всегда превосходят коррелированные подзапросы:
-- Медленно: коррелированный подзапрос выполняется один раз на строку
SELECT
o.*,
(SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
(SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
-- Быстро: оконные функции вычисляют за один проход
SELECT
o.*,
COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';Ещё один частый паттерн — получение последней строки на группу:
-- Медленно: коррелированный подзапрос
SELECT * FROM notifications n
WHERE n.created_at = (
SELECT MAX(created_at) FROM notifications
WHERE user_id = n.user_id
);
-- Быстро: DISTINCT ON (специфика PostgreSQL, крайне полезно)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
-- Тоже быстро: оконная функция ROW_NUMBER
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM notifications
) sub
WHERE rn = 1;DISTINCT ON — мой основной инструмент для этого паттерна. Он лаконичный, читаемый, и PostgreSQL хорошо его оптимизирует с правильным индексом:
CREATE INDEX idx_notifications_user_latest
ON notifications (user_id, created_at DESC);OFFSET — ловушка для больших наборов данных:
-- Страница 1: быстро
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
-- Страница 500: медленно — PostgreSQL должен просканировать и отбросить 9 980 строк
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
-- Страница 5000: очень медленно — сканирует 99 980 строк, чтобы вернуть 20
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;Решение — пагинация по ключу (также называемая курсорной пагинацией):
-- Первая страница
SELECT * FROM products ORDER BY id LIMIT 20;
-- Следующая страница: используем последний id с предыдущей страницы
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;Это всегда быстро, потому что использует индекс для прыжка прямо на нужную позицию, независимо от того, на какой «странице» ты находишься. Компромисс — ты не можешь прыгнуть на произвольный номер страницы, но для бесконечной прокрутки или UI «следующая страница» пагинация по ключу строго превосходит.
Для сложных порядков сортировки:
-- Пагинация по ключу с несколькими колонками сортировки
SELECT * FROM products
WHERE (price, id) > (29.99, 12345)
ORDER BY price, id
LIMIT 20;Это использует сравнение кортежей строк, которое PostgreSQL эффективно обрабатывает с составным индексом на (price, id).
Планировщик запросов PostgreSQL принимает решения на основе статистики о данных. Плохая статистика ведёт к плохим планам. Всё просто.
-- Анализ одной таблицы
ANALYZE orders;
-- Анализ всей базы данных
ANALYZE;
-- Просмотр текущей статистики для колонки
SELECT
tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';Значение correlation особенно интересно. Оно варьируется от -1 до 1 и измеряет, насколько хорошо физический порядок строк совпадает с логическим порядком колонки. Корреляция, близкая к 1 или -1, означает, что данные физически отсортированы по этой колонке, делая сканирование по диапазону очень эффективным (последовательный ввод-вывод). Корреляция около 0 означает случайный ввод-вывод для запросов по диапазону.
-- Увеличиваем цель статистики для колонок с неравномерным распределением
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;Цель статистики по умолчанию — 100, то есть PostgreSQL сэмплирует 300 * 100 = 30 000 строк. Для колонок с множеством различных значений или неравномерным распределением увеличение до 500 или 1000 даёт планировщику лучшие данные ценой немного более долгого ANALYZE.
Autovacuum делает две вещи: освобождает место от мёртвых кортежей (удалённых или обновлённых строк) и обновляет статистику. На нагруженных таблицах настройки autovacuum по умолчанию часто слишком консервативные.
-- Проверка статуса autovacuum
SELECT
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;Если видишь таблицы с миллионами мёртвых кортежей, а последний vacuum был часы назад — autovacuum не поспевает.
Для высоконагруженных таблиц (вроде сессий, очередей задач или метрик) я устанавливаю индивидуальные настройки autovacuum:
-- Более агрессивный autovacuum для таблиц с интенсивной записью
ALTER TABLE sessions SET (
autovacuum_vacuum_scale_factor = 0.01, -- vacuum при 1% мёртвых кортежей (по умолчанию 20%)
autovacuum_analyze_scale_factor = 0.005, -- analyze при 0.5% изменений (по умолчанию 10%)
autovacuum_vacuum_cost_delay = 2 -- быстрее vacuum (по умолчанию 2мс в новых PG)
);Когда PostgreSQL обновляет строку, он не изменяет её на месте — создаёт новую версию и помечает старую как мёртвую. Vacuum освобождает мёртвые строки, но пространство используется повторно только этой таблицей. Файл таблицы на диске не уменьшается.
Со временем активно обновляемая таблица может значительно раздуться — таблица на диске гораздо больше, чем требуют живые данные. Это означает больше страниц для сканирования, больше ввода-вывода, больше нагрузки на кэш.
-- Оценка раздутия таблицы (упрощённая версия)
SELECT
current_database(),
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
pg_size_pretty(
pg_total_relation_size(schemaname || '.' || tablename) -
pg_relation_size(schemaname || '.' || tablename)
) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;Для сильно раздутых таблиц ядерный вариант — VACUUM FULL — он перезаписывает всю таблицу. Но берёт эксклюзивную блокировку, поэтому нельзя делать на живой продакшн-таблице без даунтайма. Лучший подход — pg_repack, который делает то же самое без блокировки:
pg_repack --table orders --no-kill-backend -d mydbВот что удивляет многих разработчиков: соединения PostgreSQL дорогие. Каждое соединение порождает новый процесс (не поток), потребляет около 5-10 МБ памяти и имеет нетривиальные накладные расходы на fork.
По умолчанию max_connections равен 100. Если у тебя сервер приложений с 20 воркерами, каждый из которых открывает 5 соединений — ты уже на лимите. Добавь обработчик фоновых задач, инструмент мониторинга и миграцию, работающую где-то — и ты в беде.
Без пулера, если приложению нужно обрабатывать 500 одновременных запросов — нужно 500 соединений к PostgreSQL. Это 5 ГБ памяти только на накладные расходы соединений, и производительность PostgreSQL значительно деградирует при более чем нескольких сотнях соединений из-за накладных расходов на управление процессами.
С PgBouncer перед PostgreSQL эти 500 соединений приложения маппятся на, скажем, 20 реальных соединений PostgreSQL. Пулер ставит запросы в очередь, когда все соединения к базе заняты.
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
; Режим пула
pool_mode = transaction
; Размер пула
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
; Таймауты
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30pool_mode = transaction): соединения возвращаются в пул после каждой транзакции. Это то, что нужно в 95% случаев. Максимальное переиспользование соединений.pool_mode = session): соединения удерживаются на всю клиентскую сессию. Используй, если нужны prepared statements, команды SET, LISTEN/NOTIFY или другие фичи уровня сессии.pool_mode = statement): соединения возвращаются после каждого запроса. Слишком ограничительно для большинства приложений — нельзя даже использовать явные транзакции.Подвох транзакционного режима: нельзя использовать prepared statements (это состояние уровня сессии), нельзя использовать SET для сессионных переменных, и LISTEN/NOTIFY не будет работать ожидаемо. У большинства ORM есть способ отключить prepared statements.
Для Node.js с драйвером pg:
const pool = new Pool({
host: "127.0.0.1",
port: 6432, // порт PgBouncer, не PostgreSQL
database: "mydb",
max: 20, // совпадает с default_pool_size PgBouncer
statement_timeout: 30000,
// Отключаем prepared statements для транзакционного режима PgBouncer
prepare: false,
});Есть общая формула для оптимального размера пула соединений PostgreSQL:
optimal_connections = (core_count * 2) + effective_spindle_count
Для современного сервера с 4 ядрами и SSD (1 эффективный шпиндель):
optimal = (4 * 2) + 1 = 9
Это кажется контринтуитивно низким. Но PostgreSQL ограничен CPU на большинстве современного оборудования (SSD достаточно быстрые, что ввод-вывод редко является узким местом). Больше соединений приводит к накладным расходам на переключение контекста, которые реально замедляют работу.
На практике я обычно устанавливаю default_pool_size в 2-3 раза от этого числа для обработки пиковой нагрузки, с пониманием, что в пик некоторые запросы будут ждать в очереди PgBouncer, а не все одновременно нагружать PostgreSQL.
Вот мой реальный процесс, когда я получаю отчёт «этот запрос медленный». Я следую этим шагам в этом порядке каждый раз.
Не «эндпоинт тормозит» — реальный SQL. Если используешь ORM, включи логирование запросов:
-- Временно логируем все запросы свыше 100мс
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();Или проверь pg_stat_statements на топ запросов по общему времени:
-- Топ-10 запросов по общему времени выполнения
SELECT
queryid,
calls,
round(total_exec_time::numeric, 2) AS total_time_ms,
round(mean_exec_time::numeric, 2) AS avg_time_ms,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;pg_stat_statements — самое ценное расширение для производительности PostgreSQL. Если оно не установлено, установи сейчас:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <медленный запрос>;Ищи:
work_mem или добавь индекс для сортировки-- Когда статистика обновлялась последний раз?
SELECT
relname,
last_analyze,
last_autoanalyze,
n_live_tup,
n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';Если last_analyze давно или n_dead_tup высоко относительно n_live_tup, запусти:
ANALYZE orders;Затем перезапусти EXPLAIN ANALYZE. Если план изменился — проблема была в устаревшей статистике.
-- Какие индексы есть на этой таблице?
SELECT
indexname,
indexdef,
idx_scan,
idx_tup_read,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;Может, индекс существует, но не используется. Может, порядок колонок неправильный для твоего запроса.
На основе плана запроса создай подходящий индекс. Проверь EXPLAIN ANALYZE до и после.
-- Создаём индекс конкурентно (не блокирует таблицу)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
-- Проверяем, что индекс используется
EXPLAIN (ANALYZE, BUFFERS) <медленный запрос>;Всегда используй CONCURRENTLY на продакшн-таблицах. Обычный CREATE INDEX берёт полную блокировку таблицы, которая блокирует все записи.
Если индекс есть и статистика свежая, но запрос всё ещё медленный — смотри на сам запрос:
OFFSET вызывает проблемы? Переходи на пагинацию по ключуДля определённых паттернов запросов настройки сервера важны:
-- work_mem: память для сортировок и hash join'ов (на операцию, не на соединение)
-- По умолчанию 4МБ, что слишком мало для сложных запросов
SET work_mem = '64MB'; -- попробуй и перезапусти EXPLAIN ANALYZE
-- effective_cache_size: говорит планировщику, сколько дискового кэша ожидать
-- Устанавливай ~75% от общей RAM
SHOW effective_cache_size;
-- random_page_cost: соотношение случайного ввода-вывода к последовательному
-- По умолчанию 4.0, для SSD ставь 1.1-1.5
SHOW random_page_cost;Если изменение work_mem исправляет проблему вытеснения сортировки на диск, рассмотри увеличение глобально. Но будь осторожен — это на операцию, не на соединение. Сложный запрос с 10 операциями сортировки и work_mem = 256MB может использовать 2.5 ГБ для одного запроса.
Не просто чини и забывай. Убедись, что фикс держится:
-- Сбрасываем pg_stat_statements, чтобы видеть свежие данные после изменений
SELECT pg_stat_statements_reset();
-- Проверь через час/день
SELECT
queryid,
calls,
round(mean_exec_time::numeric, 2) AS avg_time_ms,
round(max_exec_time::numeric, 2) AS max_time_ms,
query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;Вот настройки и практики, которые я применяю к каждой PostgreSQL базе данных, которую настраиваю, до появления любых проблем с производительностью:
-- 1. Установить pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 2. Включить логирование медленных запросов (50мс — мой порог)
ALTER SYSTEM SET log_min_duration_statement = 50;
-- 3. Настройки для SSD
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
-- 4. Увеличить work_mem с анемичного значения по умолчанию
ALTER SYSTEM SET work_mem = '32MB';
-- 5. Установить effective_cache_size в 75% от RAM
-- Для сервера с 16ГБ:
ALTER SYSTEM SET effective_cache_size = '12GB';
-- 6. Установить shared_buffers в 25% от RAM
-- Для сервера с 16ГБ:
ALTER SYSTEM SET shared_buffers = '4GB';
-- 7. Улучшить отзывчивость autovacuum
ALTER SYSTEM SET autovacuum_naptime = '10s';
-- Применить изменения
SELECT pg_reload_conf();
-- Примечание: shared_buffers требует перезапускаИ запрос мониторинга, который я запускаю еженедельно:
-- Таблицы, требующие внимания
SELECT
schemaname || '.' || relname AS table,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0
THEN round(100.0 * n_dead_tup / n_live_tup, 1)
ELSE 0
END AS dead_pct,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;Оптимизация запросов PostgreSQL — не тёмное искусство. Это систематический процесс:
Разница между запросом, который выполняется 4 секунды, и тем, который выполняется 0.3 миллисекунды — это редко оборудование. Это почти всегда знания — знание того, куда смотреть и что менять. И теперь ты знаешь, куда смотреть.