Оптимизация запросов и пул соединений (PgBouncer)
↑ 2.1 PostgreSQL: индексы, транзакции, оптимизация · ← Предыдущая · → Следующая
Зачем это на собесе
Практический навык: методичный поиск узкого места и пул соединений под нагрузкой.
Процесс оптимизации
- Найти дорогие запросы:
pg_stat_statements(поtotal_exec_time,mean_exec_time,calls), логи (log_min_duration_statement), APM. - Проанализировать
EXPLAIN (ANALYZE, BUFFERS). - Гипотеза: индекс, переписывание запроса, статистика, схема.
- Изменить и измерить повторно на реалистичных данных.
- Зафиксировать (миграция индекса, тест, мониторинг).
SELECT calls, round(total_exec_time::numeric, 0) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms,
rows, left(query, 80) AS q
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;Типичные приёмы
| Проблема | Решение |
|---|---|
| Seq Scan по большой таблице | подходящий индекс (составной, частичный) |
| N+1 запросов из приложения | JOIN / WHERE id = ANY($1) / пакетная загрузка |
Большой OFFSET | keyset-пагинация |
SELECT * | нужные столбцы, covering-индекс |
count(*) по большой таблице | оценка (reltuples), кэш счётчиков, приблизительные значения |
OR по разным столбцам | UNION ALL, отдельные индексы |
| Функция в условии | функциональный индекс или переписать |
| Коррелированный подзапрос | JOIN / LATERAL / окно |
| Сортировка на диске | индекс под ORDER BY, work_mem |
| Медленная вставка | пакетная вставка, COPY, отключить лишние индексы на время загрузки |
| Тяжёлый отчёт | materialized view, реплика, ClickHouse |
Настройки под нагрузку
shared_buffers, work_mem (осторожно), effective_cache_size, random_page_cost (для SSD ~1.1), max_parallel_workers_per_gather, jit (для OLTP часто выключают), default_statistics_target.
Пул соединений
Проблема: процесс на соединение → сотни клиентов превращаются в перегрузку по памяти и переключению контекста. Оптимально держать серверных соединений порядка cores × 2..4.
PgBouncer — лёгкий пулер.
| Режим | Соединение возвращается в пул | Особенности |
|---|---|---|
| session | после закрытия клиентской сессии | совместим со всем, меньше выигрыш |
| transaction | после завершения транзакции | максимальное мультиплексирование |
| statement | после каждого оператора | нет многооператорных транзакций |
Ограничения transaction-режима: нельзя полагаться на состояние сессии (SET, LISTEN/NOTIFY, advisory-lock на сессию, временные таблицы, prepared statements — есть поддержка в новых версиях max_prepared_statements).
[databases]
app = host=pg1 port=5432 dbname=app
[pgbouncer]
pool_mode = transaction
default_pool_size = 20
max_client_conn = 2000
server_idle_timeout = 600
auth_type = scram-sha-256Также: Pgpool-II (балансировка, кэш), Odyssey, Supavisor. Пул на стороне приложения (Npgsql, HikariCP) тоже нужен: он уменьшает накладные на установку соединений; но при множестве экземпляров сервиса суммарное число соединений требует внешнего пулера.
В .NET
Npgsql: строка подключения с Maximum Pool Size, Minimum Pool Size, Connection Idle Lifetime; при PgBouncer transaction mode — No Reset On Close=true и осторожность с prepared statements (Max Auto Prepare).
Диагностика соединений
SELECT state, count(*) FROM pg_stat_activity GROUP BY 1;
SHOW max_connections;Много idle in transaction — утечка или долгие транзакции в приложении.
Вопросы с ответами
С чего начать оптимизацию медленной системы?
Найти реально дорогие запросы по
pg_stat_statements(суммарное время, частота), затемEXPLAIN ANALYZE, а не оптимизировать наугад.
Зачем PgBouncer при наличии пула в приложении?
Пул приложения ограничивает соединения одного экземпляра. При десятках экземпляров, серверлесс и микросервисах суммарное число соединений велико; PgBouncer держит малое число серверных соединений.
Какие ограничения у transaction pooling?
Нельзя использовать состояние сессии: session-level
SET, advisory-locks на сессию,LISTEN, временные таблицы; prepared statements требуют поддержки в версии пулера.