From:To:
11¶ # Исходная
22
33 Реальная ситуация с боевого сервера:
44
55 - запросы к базе — от 3 до 8 секунд;
66 - PostgreSQL занимает 20 МБ из 3.8 ГБ доступной памяти;
77 - `shared_buffers` — 128 МБ, то есть значение по умолчанию;
88 - кэш есть, но с коротким временем жизни;
99 - пула подключений нет.
1010
1111 Составленный тогда план выглядел так: поднять время жизни кэша, потом настройки PostgreSQL, потом пул подключений. Обещанный итог — улучшение на 99%.
1212
13− План рабочий, но в нᑑм пропущен первый шаг и есть одна правка, которая не работает вообще. Разберᑑм по порядку.
13+ План рабочий, но в нём пропущен первый шаг и есть одна правка, которая не работает вообще. Разберём по порядку.
1414## Сначала измерить
15151. Найти, какой именно запрос медленный
1616 Перед любой правкой надо знать конкретный запрос. Включи лог медленных запросов:
1717
1818 ```sql
19− -- логировать всᑑ, что дольше секунды
19+ -- логировать всё, что дольше секунды
2020 ALTER SYSTEM SET log_min_duration_statement = 1000;
2121 SELECT pg_reload_conf();
2222 ```
2323
2424 Либо ставь расширение `pg_stat_statements` и смотри топ по суммарному времени:
2525
2626 ```sql
2727 SELECT calls, mean_exec_time, total_exec_time, query
2828 FROM pg_stat_statements
2929 ORDER BY total_exec_time DESC
3030 LIMIT 10;
3131 ```
3232
3333 Колонка `total_exec_time` важнее, чем `mean_exec_time`: запрос на 50 мс, выполняемый десять тысяч раз, съедает больше, чем один на четыре секунды.
3434 $ SELECT calls, mean_exec_time, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
3535 why: Оптимизация без измерения — это угадывание. За общим «база медленная» обычно стоит один-два конкретных запроса, а остальное работает нормально.
3636 - [ ] Известен конкретный текст медленного запроса
3737 - [ ] Ты смотрел на суммарное время, а не только на среднее
3838 → pg_stat_statements — статистика по запросам — https://www.postgresql.org/docs/current/pgstatstatements.html
3939 → Чеклист по медленным запросам — https://wiki.postgresql.org/wiki/Slow_Query_Questions
40402. Посмотреть план выполнения
4141 ```sql
4242 EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
4343 ```
4444
4545 Что искать в выводе:
4646
4747 | Строка в плане | Что значит |
4848 |---|---|
4949 | `Seq Scan` по большой таблице | нет нужного индекса |
5050 | `rows=1` в плане, `actual rows=50000` | статистика устарела, нужен `ANALYZE` |
5151 | тот же узел с `loops=200` | классическая проблема N+1 |
5252 | `Sort Method: external merge Disk` | не хватает `work_mem` |
5353
5454 Вывод удобно разбирать в визуализаторе — ссылка ниже.
5555 $ EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
56− why: Этого шага в исходном плане не было вообще — и это главная его слабость. Запрос на четыре секунды при небольшом объᑑме данных почти никогда не лечится памятью. Обычно это отсутствующий индекс или N+1 — и тогда одна строка даᑑт выигрыш в сотни раз, а не на 20%.
56+ why: Этого шага в исходном плане не было вообще — и это главная его слабость. Запрос на четыре секунды при небольшом объёме данных почти никогда не лечится памятью. Обычно это отсутствующий индекс или N+1 — и тогда одна строка даёт выигрыш в сотни раз, а не на 20%.
5757 - [ ] Ты знаешь, есть ли Seq Scan по большой таблице
5858 - [ ] Проверил, не N+1 ли это
5959 → Как читать EXPLAIN — https://www.postgresql.org/docs/current/using-explain.html
6060 → Визуализатор планов запросов — https://explain.dalibo.com/
6161 → Индексы в PostgreSQL — https://www.postgresql.org/docs/current/indexes.html
6262¶ ## Почему порядок важен
6363
6464 Исходный план ранжировал работы по соотношению эффекта к затраченному времени — это правильный подход:
6565
6666 | Шаг | Время | Заявленный эффект |
6767 |---|---|---|
6868 | Поднять время жизни кэша | 5 мин | минус 99% нагрузки на базу |
6969 | Настройки PostgreSQL | 15 мин | 20–30% |
7070 | Пул подключений | 30 мин | 10–20% |
7171
7272 Но с двумя оговорками.
7373
7474 **Первая.** «Минус 99% нагрузки» верно только для повторных чтений редко меняющихся данных: настройки сайта, контакты, баннеры. На корзине, остатках товара или личном кабинете такой цифры не будет никогда. Кэш убирает повторные чтения, а не медленный запрос.
7575
76− **Вторая, важнее.** Первый запрос после истечения кэша всᑑ равно идᑑт четыре секунды. Кэш не починил базу — он **спрятал проблему** и отложил её до дня, когда кэш упадᑑт, и все запросы разом уйдут в медленную базу.
76+ **Вторая, важнее.** Первый запрос после истечения кэша всё равно идёт четыре секунды. Кэш не починил базу — он **спрятал проблему** и отложил её до дня, когда кэш упадёт, и все запросы разом уйдут в медленную базу.
7777
7878 Поэтому правильный порядок: **сначала EXPLAIN и индексы, потом кэш.** Кэш ставят поверх быстрого запроса, а не вместо него.
7979## Правки
80803. Настройки PostgreSQL — и главная ловушка
81− Значения по умолчанию не зависят от объᑑма памяти машины: `shared_buffers` всегда 128 МБ, `work_mem` — 4 МБ. На сервере с 4 ГБ это мало.
81+ Значения по умолчанию не зависят от объёма памяти машины: `shared_buffers` всегда 128 МБ, `work_mem` — 4 МБ. На сервере с 4 ГБ это мало.
8282
8383 **Вот как НЕ работает** — именно так было написано в исходном плане:
8484
8585 ```yaml
8686 environment:
8787 POSTGRES_SHARED_BUFFERS: 1GB # такой переменной не существует
8888 POSTGRES_WORK_MEM: 16MB # и такой тоже
8989 ```
9090
91− Официальный образ postgres знает только `POSTGRES_PASSWORD`, `POSTGRES_USER`, `POSTGRES_DB`, `POSTGRES_INITDB_ARGS`, `POSTGRES_HOST_AUTH_METHOD` и `PGDATA`. Всᑑ остальное он просто игнорирует — **без единого предупреждения**.
91+ Официальный образ postgres знает только `POSTGRES_PASSWORD`, `POSTGRES_USER`, `POSTGRES_DB`, `POSTGRES_INITDB_ARGS`, `POSTGRES_HOST_AUTH_METHOD` и `PGDATA`. Всё остальное он просто игнорирует — **без единого предупреждения**.
9292
9393 **Вот как работает:**
9494
9595 ```yaml
9696 postgres:
9797 image: postgres:16-alpine
9898 command: >
9999 postgres
100100 -c shared_buffers=1GB
101101 -c effective_cache_size=3GB
102102 -c work_mem=16MB
103103 -c maintenance_work_mem=256MB
104104 ```
105105
106− Отправная точка для расчᑑта: `shared_buffers` около 25% от памяти, `effective_cache_size` — 50–75%.
106+ Отправная точка для расчёта: `shared_buffers` около 25% от памяти, `effective_cache_size` — 50–75%.
107107 $ docker exec -it <контейнер> psql -U postgres -c "SHOW shared_buffers;"
108108 why: Это самый опасный вид ошибки: правка внесена, контейнер поднялся, ошибок нет — и ничего не изменилось. Задача закрыта, галочка стоит, проблема на месте.
109109 - [ ] Настройки заданы через command с -c или файлом конфига
110110 - [ ] SHOW shared_buffers показывает новое значение, а не 128MB
111111 → Какие переменные понимает образ postgres — https://hub.docker.com/_/postgres
112112 → Настройки памяти PostgreSQL — https://www.postgresql.org/docs/current/runtime-config-resource.html
1131134. Кэш: поднять время жизни и сразу же продумать сброс
114114 Для редко меняющихся данных время жизни в 10 минут действительно мало:
115115
116116 ```python
117117 # было везде 600 секунд
118118 SEO_TTL = 3600 # час
119119 ANALYTICS_TTL = 7200 # два часа
120120 CONTACTS_TTL = 1800 # полчаса
121121 ```
122122
123123 **Но вместе с этим обязательно сброс при записи:**
124124
125125 ```python
126126 async def update_contacts(self, data):
127127 result = await self.repo.update(data)
128128 await self.cache.delete("site_content:contacts") # без этой строки
129129 return result # админ будет
130130 # в ярости
131131 ```
132132
133133 Без сброса время жизни в два часа означает буквально следующее: администратор меняет телефон на сайте, обновляет страницу и видит старый. Два часа подряд.
134134
135135 Проверить, что кэш работает:
136136
137137 ```bash
138138 docker exec -it <redis> redis-cli
139139 > KEYS site_content:*
140140 > TTL site_content:contacts
141141 ```
142142 $ docker exec -it <redis> redis-cli TTL site_content:contacts
143− why: Кэш — это размен: скорость в обмен на актуальность. Чем больше время жизни, тем больше платишь. Поднять время жизни без сброса при записи — значит перенести жалобы с медленного сайта на «я всᑑ поменял, а ничего не поменялось».
143+ why: Кэш — это размен: скорость в обмен на актуальность. Чем больше время жизни, тем больше платишь. Поднять время жизни без сброса при записи — значит перенести жалобы с медленного сайта на «я всё поменял, а ничего не поменялось».
144144 - [ ] Время жизни поднято только там, где данные редко меняются
145145 - [ ] Каждый метод записи сбрасывает свой ключ
146146 - [ ] Проверено вручную: поменял через админку — сразу видно на сайте
1471475. Пул подключений — когда он действительно нужен [optional]
148148 Каждое подключение к PostgreSQL — это отдельный процесс на сервере базы. Сотня подключений — сотня процессов.
149149
150150 Но сначала проверь, есть ли проблема:
151151
152152 ```sql
153153 SELECT count(*), state FROM pg_stat_activity GROUP BY state;
154154 SHOW max_connections;
155155 ```
156156
157157 Если подключений десяток из ста возможных — пул не нужен, это не узкое место.
158158
159159 Если нужен — есть два уровня:
160160
161161 1. **Пул в самом приложении.** У SQLAlchemy он включён по умолчанию — проверь `pool_size` и `max_overflow` прежде, чем ставить что-то ещё.
162162 2. **Внешний пул (PgBouncer).** Нужен, когда к базе ходят несколько разных приложений или много копий одного.
163163
164164 У PgBouncer есть важное ограничение: в режиме `transaction` не работают подготовленные выражения и сеансовые настройки. Для asyncpg это значит, что нужно явно отключать кэш выражений, иначе получишь плавающие ошибки под нагрузкой.
165165 $ SELECT count(*), state FROM pg_stat_activity GROUP BY state;
166− why: Пул — самый дорогой из трᑑх шагов и самый слабый по эффекту. Ставить его первым — верный способ потратить день и получить новый источник странных ошибок.
166+ why: Пул — самый дорогой из трёх шагов и самый слабый по эффекту. Ставить его первым — верный способ потратить день и получить новый источник странных ошибок.
167167 - [ ] Проверено, что подключения действительно заканчиваются
168168 - [ ] Сначала посмотрели пул самого приложения
169169 → PgBouncer — документация — https://www.pgbouncer.org/
170170¶ ## Правильный порядок целиком
171171
172172 1. **Измерить.** Какой запрос, сколько раз, сколько времени суммарно.
173− 2. **EXPLAIN ANALYZE.** Индекс, N+1 или устаревшая статистика — самый дешᑑвый и самый большой выигрыш.
173+ 2. **EXPLAIN ANALYZE.** Индекс, N+1 или устаревшая статистика — самый дешёвый и самый большой выигрыш.
174174 3. **Настройки базы.** Через `command: postgres -c ...`, с проверкой через `SHOW`.
175175 4. **Кэш.** Поверх уже быстрого запроса, со сбросом при записи.
176176 5. **Пул подключений.** Только если измерения показали, что он нужен.
177177
178178 **После каждого шага — измерить снова.** Иначе через неделю ты не сможешь сказать, какая из пяти правок сработала, а какая просто добавила сложности.
179179
180180 И отдельно про цифры в планах. Когда видишь «улучшение на 99.9%» — спроси, на каком сценарии. Обычно это сто одинаковых запросов к одному эндпоинту подряд — то есть идеальный для кэша и не очень похожий на живой трафик.
181181¶
182182¶
183183¶