List Issues Suggestions1 Discussions Insights

Compare versions

From:To:

+11−11

# ИсходнаяAdded

Исходная

Реальная ситуация с боевого сервера:

запросы к базе — от 3 до 8 секунд;
PostgreSQL занимает 20 МБ из 3.8 ГБ доступной памяти;
shared_buffers — 128 МБ, то есть значение по умолчанию;
кэш есть, но с коротким временем жизни;
пула подключений нет.

Составленный тогда план выглядел так: поднять время жизни кэша, потом настройки PostgreSQL, потом пул подключений. Обещанный итог — улучшение на 99%.

План рабочий, но в нём пропущен первый шаг и есть одна правка, которая не работает вообще. Разберём по порядку.

Найти, какой именно запрос медленныйAdded

Перед любой правкой надо знать конкретный запрос. Включи лог медленных запросов:

sql

1-- логировать всё, что дольше секунды

2ALTER SYSTEM SET log_min_duration_statement = 1000;

3SELECT pg_reload_conf();

Либо ставь расширение pg_stat_statements и смотри топ по суммарному времени:

sql

1SELECT calls, mean_exec_time, total_exec_time, query

2FROM pg_stat_statements

3ORDER BY total_exec_time DESC

4LIMIT 10;

Колонка total_exec_time важнее, чем mean_exec_time: запрос на 50 мс, выполняемый десять тысяч раз, съедает больше, чем один на четыре секунды.

SELECT calls, mean_exec_time, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

pg_stat_statements — статистика по запросам Чеклист по медленным запросам

Посмотреть план выполненияAdded

sql

1EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Что искать в выводе:

Строка в плане	Что значит
`Seq Scan` по большой таблице	нет нужного индекса
`rows=1` в плане, `actual rows=50000`	статистика устарела, нужен `ANALYZE`
тот же узел с `loops=200`	классическая проблема N+1
`Sort Method: external merge Disk`	не хватает `work_mem`

Вывод удобно разбирать в визуализаторе — ссылка ниже.

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Как читать EXPLAIN Визуализатор планов запросов Индексы в PostgreSQL

## Почему порядок важенAdded

Почему порядок важен

Исходный план ранжировал работы по соотношению эффекта к затраченному времени — это правильный подход:

Шаг	Время	Заявленный эффект
Поднять время жизни кэша	5 мин	минус 99% нагрузки на базу
Настройки PostgreSQL	15 мин	20–30%
Пул подключений	30 мин	10–20%

Но с двумя оговорками.

Первая. «Минус 99% нагрузки» верно только для повторных чтений редко меняющихся данных: настройки сайта, контакты, баннеры. На корзине, остатках товара или личном кабинете такой цифры не будет никогда. Кэш убирает повторные чтения, а не медленный запрос.

Вторая, важнее. Первый запрос после истечения кэша всё равно идёт четыре секунды. Кэш не починил базу — он спрятал проблему и отложил её до дня, когда кэш упадёт, и все запросы разом уйдут в медленную базу.

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

Настройки PostgreSQL — и главная ловушкаAdded

Значения по умолчанию не зависят от объёма памяти машины: shared_buffers всегда 128 МБ, work_mem — 4 МБ. На сервере с 4 ГБ это мало.

Вот как НЕ работает — именно так было написано в исходном плане:

yaml

1environment:

2 POSTGRES_SHARED_BUFFERS: 1GB # такой переменной не существует

3 POSTGRES_WORK_MEM: 16MB # и такой тоже

Официальный образ postgres знает только POSTGRES_PASSWORD, POSTGRES_USER, POSTGRES_DB, POSTGRES_INITDB_ARGS, POSTGRES_HOST_AUTH_METHOD и PGDATA. Всё остальное он просто игнорирует — без единого предупреждения.

Вот как работает:

yaml

1postgres:

2 image: postgres:16-alpine

3 command: >

4 postgres

5 -c shared_buffers=1GB

6 -c effective_cache_size=3GB

7 -c work_mem=16MB

8 -c maintenance_work_mem=256MB

Отправная точка для расчёта: shared_buffers около 25% от памяти, effective_cache_size — 50–75%.

docker exec -it <контейнер> psql -U postgres -c "SHOW shared_buffers;"

Какие переменные понимает образ postgres Настройки памяти PostgreSQL

Кэш: поднять время жизни и сразу же продумать сбросAdded

Для редко меняющихся данных время жизни в 10 минут действительно мало:

python

1# было везде 600 секунд

2SEO_TTL = 3600 # час

3ANALYTICS_TTL = 7200 # два часа

4CONTACTS_TTL = 1800 # полчаса

Но вместе с этим обязательно сброс при записи:

python

1async def update_contacts(self, data):

2 result = await self.repo.update(data)

3 await self.cache.delete("site_content:contacts") # без этой строки

4 return result # админ будет

5 # в ярости

Без сброса время жизни в два часа означает буквально следующее: администратор меняет телефон на сайте, обновляет страницу и видит старый. Два часа подряд.

Проверить, что кэш работает:

bash

1docker exec -it <redis> redis-cli

2> KEYS site_content:*

3> TTL site_content:contacts

docker exec -it <redis> redis-cli TTL site_content:contacts

Пул подключений — когда он действительно нуженOptionalAdded

Каждое подключение к PostgreSQL — это отдельный процесс на сервере базы. Сотня подключений — сотня процессов.

Но сначала проверь, есть ли проблема:

sql

1SELECT count(*), state FROM pg_stat_activity GROUP BY state;

2SHOW max_connections;

Если подключений десяток из ста возможных — пул не нужен, это не узкое место.

Если нужен — есть два уровня:

Пул в самом приложении. У SQLAlchemy он включён по умолчанию — проверь pool_size и max_overflow прежде, чем ставить что-то ещё.
Внешний пул (PgBouncer). Нужен, когда к базе ходят несколько разных приложений или много копий одного.

У PgBouncer есть важное ограничение: в режиме transaction не работают подготовленные выражения и сеансовые настройки. Для asyncpg это значит, что нужно явно отключать кэш выражений, иначе получишь плавающие ошибки под нагрузкой.

SELECT count(*), state FROM pg_stat_activity GROUP BY state;

PgBouncer — документация

## Правильный порядок целикомAdded

Правильный порядок целиком

Измерить. Какой запрос, сколько раз, сколько времени суммарно.
EXPLAIN ANALYZE. Индекс, N+1 или устаревшая статистика — самый дешёвый и самый большой выигрыш.
Настройки базы. Через command: postgres -c ..., с проверкой через SHOW.
Кэш. Поверх уже быстрого запроса, со сбросом при записи.
Пул подключений. Только если измерения показали, что он нужен.

После каждого шага — измерить снова. Иначе через неделю ты не сможешь сказать, какая из пяти правок сработала, а какая просто добавила сложности.

И отдельно про цифры в планах. Когда видишь «улучшение на 99.9%» — спроси, на каком сценарии. Обычно это сто одинаковых запросов к одному эндпоинту подряд — то есть идеальный для кэша и не очень похожий на живой трафик.

Added

# ИсходнаяRemoved

# Исходная Реальная ситуация с боевого сервера: - запросы к базе — от 3 до 8 секунд; - PostgreSQL занимает 20 МБ из 3.8 ГБ доступной памяти; - `shared_buffers` — 128 МБ, то есть значение по умолчанию; - кэш есть, но с коротким временем жизни; - пула подключений нет. Составленный тогда план выглядел так: поднять время жизни кэша, потом настройки PostgreSQL, потом пул подключений. Обещанный итог — улучшение на 99%. План рабочий, но в нᑑм пропущен первый шаг и есть одна правка, которая не работает вообще. Разберᑑм по порядку.

Найти, какой именно запрос медленныйRemoved

Перед любой правкой надо знать конкретный запрос. Включи лог медленных запросов: ```sql -- логировать всᑑ, что дольше секунды ALTER SYSTEM SET log_min_duration_statement = 1000; SELECT pg_reload_conf(); ``` Либо ставь расширение `pg_stat_statements` и смотри топ по суммарному времени: ```sql SELECT calls, mean_exec_time, total_exec_time, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; ``` Колонка `total_exec_time` важнее, чем `mean_exec_time`: запрос на 50 мс, выполняемый десять тысяч раз, съедает больше, чем один на четыре секунды.

Посмотреть план выполненияRemoved

```sql EXPLAIN (ANALYZE, BUFFERS) SELECT ...; ``` Что искать в выводе: | Строка в плане | Что значит | |---|---| | `Seq Scan` по большой таблице | нет нужного индекса | | `rows=1` в плане, `actual rows=50000` | статистика устарела, нужен `ANALYZE` | | тот же узел с `loops=200` | классическая проблема N+1 | | `Sort Method: external merge Disk` | не хватает `work_mem` | Вывод удобно разбирать в визуализаторе — ссылка ниже.

## Почему порядок важенRemoved

## Почему порядок важен Исходный план ранжировал работы по соотношению эффекта к затраченному времени — это правильный подход: | Шаг | Время | Заявленный эффект | |---|---|---| | Поднять время жизни кэша | 5 мин | минус 99% нагрузки на базу | | Настройки PostgreSQL | 15 мин | 20–30% | | Пул подключений | 30 мин | 10–20% | Но с двумя оговорками. **Первая.** «Минус 99% нагрузки» верно только для повторных чтений редко меняющихся данных: настройки сайта, контакты, баннеры. На корзине, остатках товара или личном кабинете такой цифры не будет никогда. Кэш убирает повторные чтения, а не медленный запрос. **Вторая, важнее.** Первый запрос после истечения кэша всᑑ равно идᑑт четыре секунды. Кэш не починил базу — он **спрятал проблему** и отложил её до дня, когда кэш упадᑑт, и все запросы разом уйдут в медленную базу. Поэтому правильный порядок: **сначала EXPLAIN и индексы, потом кэш.** Кэш ставят поверх быстрого запроса, а не вместо него.

Настройки PostgreSQL — и главная ловушкаRemoved

Значения по умолчанию не зависят от объᑑма памяти машины: `shared_buffers` всегда 128 МБ, `work_mem` — 4 МБ. На сервере с 4 ГБ это мало. **Вот как НЕ работает** — именно так было написано в исходном плане: ```yaml environment: POSTGRES_SHARED_BUFFERS: 1GB # такой переменной не существует POSTGRES_WORK_MEM: 16MB # и такой тоже ``` Официальный образ postgres знает только `POSTGRES_PASSWORD`, `POSTGRES_USER`, `POSTGRES_DB`, `POSTGRES_INITDB_ARGS`, `POSTGRES_HOST_AUTH_METHOD` и `PGDATA`. Всᑑ остальное он просто игнорирует — **без единого предупреждения**. **Вот как работает:** ```yaml postgres: image: postgres:16-alpine command: > postgres -c shared_buffers=1GB -c effective_cache_size=3GB -c work_mem=16MB -c maintenance_work_mem=256MB ``` Отправная точка для расчᑑта: `shared_buffers` около 25% от памяти, `effective_cache_size` — 50–75%.

Кэш: поднять время жизни и сразу же продумать сбросRemoved

Для редко меняющихся данных время жизни в 10 минут действительно мало: ```python # было везде 600 секунд SEO_TTL = 3600 # час ANALYTICS_TTL = 7200 # два часа CONTACTS_TTL = 1800 # полчаса ``` **Но вместе с этим обязательно сброс при записи:** ```python async def update_contacts(self, data): result = await self.repo.update(data) await self.cache.delete("site_content:contacts") # без этой строки return result # админ будет # в ярости ``` Без сброса время жизни в два часа означает буквально следующее: администратор меняет телефон на сайте, обновляет страницу и видит старый. Два часа подряд. Проверить, что кэш работает: ```bash docker exec -it <redis> redis-cli > KEYS site_content:* > TTL site_content:contacts ```

Пул подключений — когда он действительно нуженOptionalRemoved

Каждое подключение к PostgreSQL — это отдельный процесс на сервере базы. Сотня подключений — сотня процессов. Но сначала проверь, есть ли проблема: ```sql SELECT count(*), state FROM pg_stat_activity GROUP BY state; SHOW max_connections; ``` Если подключений десяток из ста возможных — пул не нужен, это не узкое место. Если нужен — есть два уровня: 1. **Пул в самом приложении.** У SQLAlchemy он включён по умолчанию — проверь `pool_size` и `max_overflow` прежде, чем ставить что-то ещё. 2. **Внешний пул (PgBouncer).** Нужен, когда к базе ходят несколько разных приложений или много копий одного. У PgBouncer есть важное ограничение: в режиме `transaction` не работают подготовленные выражения и сеансовые настройки. Для asyncpg это значит, что нужно явно отключать кэш выражений, иначе получишь плавающие ошибки под нагрузкой.

## Правильный порядок целикомRemoved

## Правильный порядок целиком 1. **Измерить.** Какой запрос, сколько раз, сколько времени суммарно. 2. **EXPLAIN ANALYZE.** Индекс, N+1 или устаревшая статистика — самый дешᑑвый и самый большой выигрыш. 3. **Настройки базы.** Через `command: postgres -c ...`, с проверкой через `SHOW`. 4. **Кэш.** Поверх уже быстрого запроса, со сбросом при записи. 5. **Пул подключений.** Только если измерения показали, что он нужен. **После каждого шага — измерить снова.** Иначе через неделю ты не сможешь сказать, какая из пяти правок сработала, а какая просто добавила сложности. И отдельно про цифры в планах. Когда видишь «улучшение на 99.9%» — спроси, на каком сценарии. Обычно это сто одинаковых запросов к одному эндпоинту подряд — то есть идеальный для кэша и не очень похожий на живой трафик.

Removed