Debian 13: PostgreSQL шторм підключень — pooler проти налаштування: що справді працює (випадок №37)

Що таке шторм підключень насправді (і чому він шкодить)

«Шторм підключень» у PostgreSQL — це не просто «багато підключень». Це патологічний момент, коли швидкість нових підключень
або спроб повторного підключення перевантажує якусь частину системи: створення backend-процесів Postgres, аутентифікацію, TLS-рукопотискання,
планувальник CPU, обмеження ядра, затримку диска або просто здатність сервера впоратися з перемиканням контекстів.

Postgres використовує модель «процес на підключення» (не «потік на підключення»). Це архітектурне рішення з очевидними перевагами:
ізоляція, простіше відлагодження, прогнозоване обмеження наслідків помилок. Але це також означає, що кожне підключення має реальні накладні витрати:
backend-процес, споживання пам’яті (work_mem — не єдине; є стан на рівні бекенду) і витрати на планування.

Шторми зазвичай починаються вгорі стека:

• Деплой додатку скидає connection pool.
• Автоскейлінг додає поди, що всі підключаються одночасно.
• Неправильно налаштовані health check-і балансувальника перетворюються на DOS при логіні.
• Мережевий флап викликає повторні спроби в щільних циклах.
• Поганий запит підвищує час відгуку; клієнти таймаутять і перепідключаються, множачи навантаження.

Режим відмови підступний, бо нелінійний. База може бути «в порядку» при 200 стабільних підключеннях, але впасти під
навантаженням у 2000 спроб підключень на секунду, навіть якщо одночасно активних лише 200.

Практичне правило: коли проблема — churn підключень, підвищення max_connections — це як розширювати двері в палаючій будівлі.
Ви просто занесете більше диму.

Жарт №1: Шторм підключень — це як безкоштовна піца в офісі — ніхто її «не потребує», але раптом всі дуже мотивовані з’явитися.

Факти та контекст, що змінюють ваш погляд

• Модель Postgres «процес на підключення» сягає десятиліть і досі є базовим архітектурним вибором; вона віддає перевагу надійності над сирим числом підключень.
• pg_stat_activity існує в тій чи іншій формі давно; це перше місце, куди слід дивитися, коли реальність не збігається з дашбордами.
• TLS повсюдно змінило математику: те, що раніше було дешевим «connect/auth» трафіком, тепер включає важчі криптографічні рукопотискання, якщо ви завершуєте TLS на Postgres.
• Адаптація SCRAM-SHA-256 покращила безпеку паролів, але сильніша аутентифікація може зробити шторми більш чутливими до CPU, ніж старі MD5-схеми при екстремальному churn.
• Linux cgroup і systemd-ліміти стали тихим джерелом інцидентів «працювало на старій ОС»; systemd-юнити в Debian 13 роблять ці ліміти помітнішими (і виконуваними).
• Популярність PgBouncer у транзакційному пулінгу зросла не через те, що Postgres «поганий», а тому, що клієнти часто неправильно використовують або надто створюють підключення.
• «Idle in transaction» — відома пастка Postgres: вона утримує блокування і ризик bloat, виглядаючи «неактивною», що ускладнює реагування на інциденти.
• Обсервабельність покращилась: сучасний Postgres показує wait events (pg_stat_activity.wait_event), тож ви можете припинити гадати, чи ви пов’язані CPU, блокуваннями чи IO.

Одна перефразована ідея варта збереження в голові від спеціаліста з надійності:
перефразована ідея — John Allspaw: «Під час інцидентів система має сенс для людей всередині неї; виправляйте умови, а не шукайте винних.»

План швидкої діагностики

Коли ви на чергуванні, часу на філософські дебати про pooler немає. Потрібно знайти вузьке місце за хвилини,
а не в постмортемі. Ось порядок тріажу, що працює на Debian 13 з Postgres у реальному світі.

Спочатку: визначте, чи проблема в «прийнятті підключень», чи в «обслуговуванні запитів»

• Якщо клієнти взагалі не можуть підключитися: перевірте listen backlog, файлові дескриптори, спайки CPU під аутентифікацією/TLS та ліміти процесів.
• Якщо клієнти підключаються, але таймаутять запити: перевірте блокування, IO, насичення CPU та повільні запити, що викликають шторм повторних спроб.

По-друге: класифікуйте шторм

• Churn storm: багато підключень/відключень, короткі сесії, pg_stat_activity заповнений новими сесіями.
• Idle storm: підключення накопичуються й «застрягають», багато сесій у стані idle, ймовірно проблеми з пулінгом додатка.
• «Idle in transaction» storm: менше сесій, але вони блокують ресурси і викликають накопичення.
• Retry storm: латентність запитів призводить до таймаутів, клієнтські повторні спроби підсилюють навантаження; часто в корені не проблема «підключень».

По-третє: оберіть важіль

• Використовуйте pooler, коли потрібно обмежити кількість підключень або загасити churn.
• Налаштуйте Postgres, коли сервер недостатньо ресурсний, неправильно сконфігурований або заблокований (блокування/IO), і підключення — лише симптом.
• Починіть додаток, коли він створює підключення на запит, не повторно їх використовує або має поламані політики повторних спроб/бекофу.

Pooler проти налаштування: логіка прийняття рішення, а не ідеологія

Ви можете «вирішити» шторм трьома шляхами: дозволити Postgres приймати більше підключень, зменшити кількість підключень або зменшити потребу в повторних спробах.
Пастка — вважаєте, що вони взаємозамінні. Вони — ні.

Що може налаштування (і чого не може)

Налаштування допомагає, коли Postgres марнує ресурси або заблокований. Приклади:

• Блокування: погана гігієна транзакцій створює черги блокувань; налаштування не «вилікує» це, але моніторинг і таймаути зменшать площу ураження.
• Пам’ять: невірні значення shared_buffers і неконтрольований work_mem можуть перетворити нормальне навантаження у своп-мороку.
• IO: повільне сховище, спайки чекпоінтів або невдалий autovacuum можуть підвищити латентність до рівня, що викликає повторні спроби.
• CPU: дорога аутентифікація (TLS/SCRAM) плюс високий churn можуть домінувати над CPU; налаштування може перемістити частини (наприклад, вивантаження TLS), але не змінить сам churn.

Чого налаштування не зробить: змусить 10 000 клієнтів вести себе відповідально. Якщо вони літають як комахи на заході сонця, вам потрібна москітна сітка.

Що змінює pooler

Pooler (зазвичай PgBouncer) сидить між клієнтами й Postgres, стискаючи багато клієнтських підключень у меншу кількість серверних.
Ви отримуєте:

• Жорсткі обмеження на серверні підключення навіть при клієнтських спайках.
• Швидше встановлення клієнтських підключень (особливо якщо pooler розгорнуто близько до додатку і він тримає «теплі» серверні з’єднання).
• Захист від деплой-шторми: перезапуски додатків не перетворюються на backend-churn у Postgres.

Але pooler також знімає деякі гарантії:

• Транзакційний пулінг руйнує стан сесії: тимчасові таблиці, підготовлені запити, змінні сесії, advisory locks — поводьтеся обережно.
• Видимість змінюється: потрібно моніторити метрики pooler-а, а не лише Postgres.
• Неправильні налаштування можуть бути гіршими за відсутність pooler-а: неправильні розміри пулів та таймаути можуть спричинити самонанесені черги.

Якщо ваша робота — вебоподібна (багато коротких транзакцій), транзакційний пулінг зазвичай перемагає. Якщо ж робота сесійно-важка
(багато стану сесії, довгі транзакції), ви все ще можете використовувати pooler — але можливо потрібен сесійний пулінг або рефакторинг додатка.

Жарт №2: Підняття max_connections у відповідь на шторм — це як купувати більше стільців, коли проблема у порядку зустрічі.

Практичні завдання (команди, виходи, рішення)

Ось завдання, які я запускаю під час інцидентів і під час сесій «потрібно виправити це, перш ніж повториться».
Кожне завдання включає: команду, приклад виходу, що це означає і яке рішення прийняти.

Завдання 1: Підтвердіть, чи досягаєте ліміту підключень (зі сторони Postgres)

cr0x@server:~$ sudo -u postgres psql -XAtc "SHOW max_connections; SHOW superuser_reserved_connections;"
200
3

Значення: Ефективно доступні лише 197 не-суперкористувацьких підключень.

Рішення: Якщо ви бачите помилки типу «too many clients already», не підвищуйте це одразу. Спочатку знайдіть, чому підключення спливають або «залипають».

Завдання 2: Подивіться поточну кількість підключень і стани

cr0x@server:~$ sudo -u postgres psql -Xc "SELECT state, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC;"
   state   | count
-----------+-------
 idle      |   160
 active    |    25
           |     3
 idle in transaction | 9
(4 rows)

Значення: Багато idle-сесій. Декілька «idle in transaction» — червоні прапорці.

Рішення: Якщо домінує idle і кількість підключень близька до максимуму, зосередьтесь на пулінгу додатка й таймаутах для idle; pooler часто найшвидше обмежує проблему.

Завдання 3: Визначте топ джерел клієнтів (шторми часто приходять з одного місця)

cr0x@server:~$ sudo -u postgres psql -Xc "SELECT client_addr, usename, count(*) AS conns FROM pg_stat_activity GROUP BY 1,2 ORDER BY conns DESC LIMIT 10;"
 client_addr | usename | conns
-------------+---------+-------
 10.42.7.19  | appuser |   120
 10.42.8.11  | appuser |    95
 10.42.9.02  | appuser |    70
(3 rows)

Значення: Кілька нод додатку домінують. Це хороша новина: можна виправити або обмежити невелику кількість порушників.

Рішення: Якщо одна адреса клієнта вибухає, ізолюйте той деплой, сайдкар або job runner.

Завдання 4: Виміряйте симптоми накладних витрат підключення/аутентифікації через wait events Postgres

cr0x@server:~$ sudo -u postgres psql -Xc "SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity WHERE state='active' GROUP BY 1,2 ORDER BY 3 DESC;"
 wait_event_type |     wait_event      | count
-----------------+---------------------+-------
 Lock            | transactionid        |    12
 IO              | DataFileRead         |     5
 CPU             |                     |     3
(3 rows)

Значення: Ваш «шторм підключень» може бути наслідком шторму блокувань, що викликає таймаути і повторні спроби.

Рішення: Якщо домінують Lock waits, припиніть думати про pooler і почніть думати про блокуючу транзакцію й таймаути.

Завдання 5: Швидко знайдіть блокувальників (блокування викликають шторм повторних спроб)

cr0x@server:~$ sudo -u postgres psql -Xc "SELECT blocked.pid AS blocked_pid, blocker.pid AS blocker_pid, blocked.query AS blocked_query, blocker.query AS blocker_query FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked ON blocked.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocker_locks ON blocker_locks.locktype = blocked_locks.locktype AND blocker_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocker_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocker_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocker_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocker_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocker_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocker_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocker_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocker_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocker_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocker ON blocker.pid = blocker_locks.pid WHERE NOT blocked_locks.granted;"
 blocked_pid | blocker_pid |        blocked_query         |        blocker_query
------------+-------------+------------------------------+-----------------------------
      24811 |       20777 | UPDATE accounts SET ...      | ALTER TABLE accounts ...
(1 row)

Значення: DDL-операція блокує оновлення. Це може спричинити каскад таймаутів і перепідключень у додатку.

Рішення: Завершіть або відкладіть блокуючий процес, якщо це безпечно, і впровадьте безпечніші практики міграцій (lock timeouts, online migration patterns).

Завдання 6: Перевірте межу файлових дескрипторів на рівні ОС (класична причина «помилок підключення»)

cr0x@server:~$ cat /proc/$(pidof postgres | awk '{print $1}')/limits | grep -E "Max open files"
Max open files            1024                 1048576              files

Значення: Soft limit — 1024 для головного PID. Це небезпечно мало для завантаженої бази даних.

Рішення: Виправте systemd unit ліміти (Завдання 7). Якщо ви працюєте з низькими soft limit-ами, під навантаженням отримаєте випадкові відмови.

Завдання 7: Перевірте systemd LimitNOFILE для PostgreSQL

cr0x@server:~$ systemctl show postgresql --property=LimitNOFILE
LimitNOFILE=1024

Значення: Сервіс PostgreSQL обмежено 1024 відкритими файлами. Це включає сокети й файлові дескриптори даних.

Рішення: Встановіть розумне значення через drop-in. Потім перезапустіть у вікні технічного обслуговування.

Завдання 8: Застосуйте systemd drop-in для вищих лімітів файлів

cr0x@server:~$ sudo systemctl edit postgresql
# (editor opens)
# add:
# [Service]
# LimitNOFILE=1048576

cr0x@server:~$ sudo systemctl daemon-reload
cr0x@server:~$ sudo systemctl restart postgresql
cr0x@server:~$ systemctl show postgresql --property=LimitNOFILE
LimitNOFILE=1048576

Значення: PostgreSQL тепер успадковує високий ліміт файлових дескрипторів.

Рішення: Якщо помилки підключення зникли, ви знайшли принаймні одну жорстку стелю. Все одно розслідуйте, чому підключення спливають.

Завдання 9: Перевірте відсіювання listen backlog (проблеми на рівні ядра з підключеннями)

cr0x@server:~$ ss -ltn sport = :5432
State  Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 64     4096   0.0.0.0:5432      0.0.0.0:*

Значення: Recv-Q показує чергу підключень. Якщо вона часто близька до Send-Q (backlog), ви втрачаєте або затримуєте встановлення підключень.

Рішення: Якщо Recv-Q піднімається під час штормів, розгляньте pooler поблизу клієнтів і перегляньте налаштування backlog ядра (somaxconn) та стратегію Postgres для listen_addresses/tcp_keepalives.

Завдання 10: Перевірте backlog ядра і обробку SYN

cr0x@server:~$ sysctl net.core.somaxconn net.ipv4.tcp_max_syn_backlog
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096

Значення: Ці значення досить великі. Якщо вони низькі (128/256), шторми можуть переповнити чергу TCP-рукопотискання.

Рішення: Якщо значення низькі і ви бачите відмови підключень, обережно підвищуйте їх і тестуйте. Але пам’ятайте: настройка backlog допомагає симптомам; pooler вирішує поведінку.

Завдання 11: Визначте, чи дорога аутентифікація (підказки в pg_hba.conf)

cr0x@server:~$ sudo -u postgres psql -Xc "SELECT name, setting FROM pg_settings WHERE name IN ('password_encryption','ssl');"
        name         | setting
---------------------+---------
 password_encryption | scram-sha-256
 ssl                 | on
(2 rows)

Значення: SCRAM + TLS — це безпечно; при масовому churn це також важче для CPU.

Рішення: Не послаблюйте аутентифікацію як першу реакцію. Краще використовувати pooler, щоб зменшити обсяг рукопотискань, або термінувати TLS раніше, якщо політика дозволяє.

Завдання 12: Проінспектуйте швидкість churn підключень у статистиці Postgres

cr0x@server:~$ sudo -u postgres psql -Xc "SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit FROM pg_stat_database ORDER BY numbackends DESC;"
  datname  | numbackends | xact_commit | xact_rollback | blks_read | blks_hit
-----------+-------------+-------------+---------------+-----------+----------
 appdb     |         190 |     9921330 |         12344 |   1209932 |  99881233
 postgres  |           3 |        1200 |             0 |       120 |     22000
(2 rows)

Значення: Високий numbackends, близький до вашого ефективного максимуму. У поєднанні з низькою активною роботою — це кричить «проблема управління підключеннями».

Рішення: Обмежте через pooler або строгі ліміти на стороні додатка; потім виправте поведінку повторних спроб і дисципліну пулінгу.

Завдання 13: Перевірте «idle in transaction» і застосуйте таймаути

cr0x@server:~$ sudo -u postgres psql -Xc "SHOW idle_in_transaction_session_timeout;"
idle_in_transaction_session_timeout
-----------------------------------
0
(1 row)

Значення: Таймаут вимкнено. «Idle in transaction» сесії можуть тримати блокування довго.

Рішення: Встановіть розумне значення (зазвичай 1–5 хвилин для OLTP), щоб зменшити ризик «хвостів» інциденту.

Завдання 14: Застосуйте таймаути (безпечні значення краще за героїчне відлагодження)

cr0x@server:~$ sudo -u postgres psql -Xc "ALTER SYSTEM SET idle_in_transaction_session_timeout = '2min';"
ALTER SYSTEM
cr0x@server:~$ sudo -u postgres psql -Xc "SELECT pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)

Значення: Конфіг перезавантажено, нові сесії успадкують таймаут.

Рішення: Якщо у вас справді є довгі «idle-in-tx» сесії (рідко і зазвичай невірно), виправте цей кодовий шлях; не вимикайте запобіжник.

Завдання 15: Перевірте, що клієнтський пулінг не «безкінечний» (приклад: psql показує application_name)

cr0x@server:~$ sudo -u postgres psql -Xc "SELECT application_name, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC LIMIT 10;"
 application_name | count
------------------+-------
 myapi            |   180
 migration-job    |     9
 psql             |     1
(3 rows)

Значення: API — головний споживач. Потрібно перевірити його налаштування пула й поведінку деплою.

Рішення: Обмежте пули на екземпляр, додайте псевдовипадковість (jitter) при старті та забезпечте експоненційний backoff при повторних спробах.

Завдання 16: Перевірте ризик пам’яті перед підвищенням max_connections (пастка)

cr0x@server:~$ sudo -u postgres psql -Xc "SHOW shared_buffers; SHOW work_mem; SHOW maintenance_work_mem; SHOW max_connections;"
shared_buffers
--------------
4GB
(1 row)

work_mem
--------
16MB
(1 row)

maintenance_work_mem
--------------------
512MB
(1 row)

max_connections
---------------
200
(1 row)

Значення: Якщо ви стрибнете до 800 підключень, залишивши work_mem 16MB, ви запрошуєте пам’ятне навантаження. Не всі підключення використовують повний work_mem, але інциденти люблять обчислення за гіршим сценарієм.

Рішення: Якщо потрібно збільшити max_connections, перегляньте стратегію пам’яті і розгляньте pooler перед цим. Краще: уникати потреби в підвищенні зовсім.

Завдання 17: Встановіть і базово перевірте PgBouncer на Debian 13

cr0x@server:~$ sudo apt-get update
cr0x@server:~$ sudo apt-get install -y pgbouncer
Reading package lists... Done
...
Setting up pgbouncer ...

Значення: Pooler встановлено. Тепер його потрібно навмисно сконфігурувати; значення за замовчуванням — не план для продакшену.

Рішення: Розмістіть PgBouncer якнайближче до додатків, якщо можливо (на тому ж вузлі/кластері), щоб зменшити мережеві накладні та ізолювати шторми.

Завдання 18: Мінімальні перевірки конфігурації PgBouncer, що запобігають самонанесенню шкоди

cr0x@server:~$ sudo grep -E "^(listen_addr|listen_port|pool_mode|max_client_conn|default_pool_size|server_reset_query|ignore_startup_parameters)" /etc/pgbouncer/pgbouncer.ini
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 50
server_reset_query = DISCARD ALL
ignore_startup_parameters = extra_float_digits

Значення: Транзакційний пулінг із розумним розміром пулу може поглинути клієнтські сплески. DISCARD ALL безпечніший, хоча має накладні витрати.

Рішення: Якщо ваш додаток залежить від стану сесії, транзакційний пулінг його зламає. Переробіть або використайте сесійний пулінг для цих робочих навантажень.

Завдання 19: Спостерігайте черги PgBouncer (щоб знати, чи просто переносите біль)

cr0x@server:~$ psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"
 database | user    | cl_active | cl_waiting | sv_active | sv_idle | sv_used | maxwait
----------+---------+-----------+------------+----------+---------+---------+---------
 appdb    | appuser |       120 |         80 |       50 |       0 |       50 |     12
(1 row)

Значення: Клієнти чекають. Серверні підключення обмежені 50 і повністю зайняті. Це очікувано під час сплесків.

Рішення: Якщо maxwait росте і затримка запитів страждає, обережно налаштуйте розмір пулу і, що важливіше, зменшіть конкурентність клієнтів і виправте повільні запити.

Завдання 20: Підтвердіть, що додаток дійсно використовує pooler

cr0x@server:~$ sudo -u postgres psql -Xc "SELECT client_addr, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC;"
 client_addr | count
-------------+-------
 127.0.0.1   |    52
(1 row)

Значення: Postgres бачить PgBouncer як клієнта (loopback). Добре; фан-аут перемістився в pooler.

Рішення: Якщо ви все ще бачите багато IP додатків, що підключаються напряму, у вас проблема з rollout/config drift, а не з налаштуванням БД.

Три міні-історії з реального світу

Міні-історія №1: Інцидент через неправильне припущення (міф «підключення — це дешево»)

Середня компанія тримала customer API на Debian, Postgres на потужному VM і сервіс-меш з mTLS скрізь.
Хтось порахував CPU й IO, побачив запас і оголосив базу «надмірно забезпеченою».
Найбільше команда боялася повільних запитів. Ніхто не думав про підключення.

Рутинний деплой розгорнувся на кілька сотень контейнерів. Кожен контейнер стартував, робив health check і встановлював нове підключення до бази,
щоб перевірити, що міграції «в порядку». Той health check запускався кожні кілька секунд, бо «швидке виявлення — добре», і відкривав нове підключення,
бо код використовував простого одноразового клієнта.

Postgres одразу не впав. Він просто поступово став менш відзивчивим. Обсяг аутентифікації і TLS-рукопотискань зріс.
Backend-процеси множилися. CPU VM здавався помірним, бо справжні вороги були в планувальнику і обліку ядра.

Виправлення не було героїчним. Вони припинили робити connect-per-healthcheck, додали невеликий sidecar pooler для API-подів
і обмежили частоту readiness checks. Також перестали вважати «підключення дешево» у TLS-світі.
Інцидент більше не повернувся.

Міні-історія №2: Оптимізація, що відбилася назад (підняття max_connections)

Інша організація мала класичну сторінку «too many clients already». Розумний інженер підняв max_connections з кількох сотень до понад тисячі.
Зміна зайняла хвилини. Сторінка зникла. Усі повернулися до роботи.

Через два тижні скарги на латентність повернулися — але з нюансом. База вже не відмовляла у підключеннях; вона їх приймала і тихо тонула.
Час відгуку погіршився по всьому фронту. Autovacuum відстав, чекпоінти стали стрибкоподібними.

Постмортем був незручний, бо не було одного «винного» запиту. Проблема — конкуренція та пам’ять.
Більше бекендів означало більше накладних витрат пам’яті, більше переключень контексту і більше одночасного споживання work_mem під час сплесків.
Система працювала нормально, поки не перетнула поріг. Потім поводилася як стомлений касир у супермаркеті під закриття.

Остаточне виправлення: відкат max_connections, впровадження PgBouncer у режимі transaction pooling і обмеження пулів на стороні додатка.
Також ввели кращий jitter для повторних спроб, щоб таймаути не синхронізувались. Урок: підняття лімітів підключень може перетворити явну відмову на невидимий крах продуктивності.

Міні-історія №3: Нудна, але правильна практика, що врятувала день (таймаути й запобіжники)

Фінансово пов’язана компанія тримала Postgres зі строгим процесом змін. Інженери бурчали, як це зазвичай буває.
Але їхній DBA наполягав на кількох «нудних» значеннях за замовчуванням: statement_timeout для певних ролей, idle_in_transaction_session_timeout
і консервативні таймаути блокувань для міграцій.

Одного дня міграція, яка була безпечною в staging, у продакшені зіткнулася з batch job-ом і взяла сильніші блокування, ніж очікували.
Без запобіжників міграція б сиділа нескінченно, захоплюючи всіх у заручники.

Натомість міграція влучила в таймаут блокування і швидко зафейлилася. Додаток продовжив працювати. Декілька запитів повторилися і пройшли.
Тикет інциденту став незначною подією: «міграція провалилася, перезапустити пізніше». Ніяких шторми, паніки чи дзвінків о 3 ранку.

Оце і є сила нудних контролів: їх не видно, поки вони не врятують ваш часовий пояс від зайвого досвіду з пейджером.

Поширені помилки: симптом → корінна причина → виправлення

1) Симптом: «too many clients already» під час деплоїв

Корінна причина: екземпляри додатка стартують одночасно і кожен відкриває повний пул; немає jitter; немає обмежень пулу.

Виправлення: обмежте пули на екземпляр; додайте startup jitter; додайте PgBouncer для зменшення фан-ауту; переконайтеся, що повторні спроби використовують експоненційний backoff з jitter.

2) Симптом: Postgres приймає підключення, але запити таймаутять

Корінна причина: контеншен блокувань або довгі транзакції, часто спричинені DDL під час пікового трафіку.

Виправлення: ідентифікуйте блокувальників; впровадьте lock timeouts; використовуйте безпечніші патерни міграцій; під час інциденту завершіть або відкладіть блокуючу сесію.

3) Симптом: Підключення періодично відмовляються під навантаженням

Корінна причина: ліміти файлових дескрипторів (systemd LimitNOFILE) або обмеження backlog ядра.

Виправлення: підвищте ліміти сервісу через systemd drop-in; перевірте через /proc/PID/limits; перегляньте sysctl, що стосуються backlog.

4) Симптом: Багато «idle in transaction» сесій

Корінна причина: додаток відкриває транзакцію і чекає на введення користувача, фонову роботу або мережевий виклик.

Виправлення: встановіть idle_in_transaction_session_timeout; виправте межі транзакцій в додатку; використовуйте коротші транзакції і явні блокування лише коли потрібно.

5) Симптом: Після додавання PgBouncer деякі фічі ламаються (тимчасові таблиці, prepared statements)

Корінна причина: транзакційний пулінг скасовує стан сесії.

Виправлення: перенесіть такі ворклоади в сесійний пулінг або рефакторіть додаток, щоб уникнути стану сесії; обережно використовуйте серверні prepared statements або вимкніть їх у клієнтах.

6) Симптом: PgBouncer «вирішив» шторми, але латентність стала стрибкоподібною

Корінна причина: черга утворилася в PgBouncer; серверний пул занадто малий або запити повільні; конкуренція перевищує пропускну здатність БД.

Виправлення: обережно налаштуйте розміри пулів; зменшіть конкурентність додатка; оптимізуйте повільні запити й IO; розглядайте чергу PgBouncer як індикатор перевантаження, а не проблему, яку варто приховати.

7) Симптом: Спайки CPU під час штормів, хоча навантаження запитів здається низьким

Корінна причина: TLS-рукопотискання й вартість аутентифікації домінують при churn; також накладні витрати на створення процесів.

Виправлення: зменшіть churn за допомогою pooler-а; забезпечте keepalives; уникайте connect-per-request; розгляньте стратегію термінації TLS, якщо політика дозволяє.

Чек-листи / покроковий план

Чек-лист A: Перша година стримування (зупинити кровотечу)

1. Підтвердьте, чи це відмова підключення або таймаути запитів. Використовуйте pg_stat_activity, помилки додатка та перевірки backlog через ss.
2. Знайдіть домінантне джерело клієнта. Якщо одна група додатків некоректна, ізолюйте її (scale down, rollback або throttle).
3. Шукайте блокування і «idle in transaction». Вбивайте блокувальника тільки якщо розумієте вплив.
4. Перевірте жорсткі ліміти ОС. Файлові дескриптори і ліміти systemd — швидкі перемоги.
5. Зменшіть швидкість повторних підключень. Виправте retry loops і додайте backoff/jitter; тимчасово підвищте клієнтські таймаути, щоб зменшити треш.

Чек-лист B: Двохденне виправлення (щоб шторми траплялись рідше)

1. Впровадьте PgBouncer для веб-типових навантажень і встановіть transaction pooling, якщо немає сильних причин інакше.
2. Встановіть запобіжники: idle_in_transaction_session_timeout, role-based statement_timeout і таймаути блокувань для міграцій.
3. Обмежте пули на додатку на екземпляр і документуйте математику: інстанси × розмір пулу не повинні перевищувати те, що Postgres може обслуговувати.
4. Інструментуйте churn: відстежуйте швидкість підключень, а не лише кількість підключень.
5. Запустіть контрольований тест-шторм у staging: rolling restart флоту додатків + навантаження, спостерігайте backlog, CPU аутентифікації і черги PgBouncer.

Чек-лист C: Довготривала гігієна (щоб не вчитися заново)

1. Стандартизувати налаштування клієнтів (таймаути, keepalives, retry backoff) як бібліотеку, а не як усна традиція.
2. Зробити міграції банальними: примусити безпечні патерни і розділити зміну схеми від бекафілу даних.
3. Планувати потужність за «корисною роботою», а не за max connections: вимірюйте пропускну здатність, SLO по латентності і headroom по IO; розглядайте підключення як plane керування.
4. Проводити тренування інцидентів з фокусом на contention блокувань і churn підключень — саме вони дивують людей найчастіше.

Часті запитання

1) Чи треба завжди розгортати PgBouncer на Debian 13 для Postgres?

Для більшості OLTP/веб-навантажень: так, це практичний дефолт. Не тому, що Postgres слабкий, а тому, що клієнти брудні.
Якщо у вас сесійно-важкі навантаження, ви все ще можете використовувати PgBouncer у session pooling або ізолювати такі клієнти.

2) Чи іноді підняття max_connections правильний крок?

Іноді — коли ви впевнені, що маєте запас пам’яті, ваша робота справді потребує більшої конкурентності, і churn підключень не є проблемою.
Рідко це найкраща перша реакція на шторм. Вимірюйте накладні витрати на бекенд і уважно слідкуйте за swap.

3) Чому моя база стала повільнішою після «виправлення» помилок підключення?

Ви ймовірно перетворили відмову в підключенні на проблему чергування. Більше бекендів — більше накладних витрат планувальника і більше ризику по пам’яті.
Якщо ви не зменшили фактичний попит (клієнтську конкурентність або повільні запити), система все ще не зможе впоратися — просто відмовляє інакше.

4) Який режим pool-а обирати в PgBouncer?

Транзакційний пулінг для типового API-трафіку. Сесійний пулінг — лише коли вам потрібен стан сесії (тимчасові таблиці, session GUCs,
advisory locks, що тримаються між транзакціями, деякі патерни prepared statements).

5) Як зрозуміти, що мій «шторм підключень» — це насправді блокування?

Дивіться на wait events у pg_stat_activity і графи блокувань. Якщо багато сесій чекають на Lock events і ви можете ідентифікувати блокувальника,
ви в lock-інциденті, що може запускати повторні спроби. Виправляйте причину блокування; не додавайте лише пулінг.

6) Чи може PgBouncer приховати повільні запити?

Він може приховати ранні сигнали, буферизуючи клієнтів. Це корисно для стримування. Але черга буде рости і латентність стрибне.
Використовуйте метрики PgBouncer (waiting clients, maxwait) як індикатор того, що база насичена або заблокована.

7) Який найменш помітний, але недооцінений параметр для зменшення площі ураження шторму?

idle_in_transaction_session_timeout. Він не дозволяє невеликій кількості поламаних запитів тримати блокування вічно і перетворювати ваш день на археологію.

8) Чи потрібно налаштовувати параметри ядра для шторма підключень Postgres?

Іноді. Якщо ви бачите насичення backlog або проблеми з SYN-чергою, налаштування somaxconn і tcp_max_syn_backlog допомагає.
Але якщо ваш додаток підключається як барабанне соло, налаштування ядра — не бізнес-план. Виправляйте churn пулінгом і дисципліною повторних спроб.

9) Чи має сенс розміщувати PgBouncer на хості бази даних?

Може мати, і це поширено. Але розміщення його ближче до клієнтів (на вузлі, як sidecar або в кластерах) часто зменшує мережеві накладні
і робить шторми менш здатними поширюватися між зонами впливу. Обирайте, виходячи з простоти операцій та областей відмови.

10) Яке найшвидше «достатньо добре» покращення, якщо я поки не можу розгорнути pooler?

Обмежте пули на стороні додатка, додайте експоненційний backoff з jitter і встановіть таймаути (idle_in_transaction_session_timeout, lock timeouts для міграцій).
Також перевірте, чи systemd file limits не саботують вас.

Наступні кроки, які ви можете зробити цього тижня

Якщо ви зараз гасяте пожежі зі шторми, спочатку зосередьтесь на стримуванні: ідентифікуйте домінантного клієнта, зупиніть потік повторних спроб і переконайтеся,
що ви не упираєтесь у ліміти ОС або блокування. Потім оберіть одне структурне виправлення і впровадьте його.

1. Додайте PgBouncer (transaction pooling) для основного робочого навантаження додатка і перевірте використання, спостерігаючи, як адреси клієнтів у Postgres згортаються до pooler-а.
2. Встановіть запобіжники: увімкніть idle_in_transaction_session_timeout і додайте role-based таймаути там, де доречно.
3. Виправте поведінку підключень: обмежте пули на екземпляр і впровадьте jittered backoff для повторних спроб. Якщо ви не можете описати свою політику повторних спроб — її немає.
4. Проведіть репетицію шторми у staging: rolling restart флоту додатків і спостереження за backlog через ss, чергами PgBouncer і wait events у Postgres.
5. Перестаньте використовувати max_connections як заспокійливе. Утримуйте його відповідно до пам’яті, CPU і тієї кількості конкурентності, яку Postgres може якісно обслуговувати.

Сенс не в тому, щоб «уникати помилок». Сенс в тому, щоб база продовжувала робити корисну роботу, коли решта системи має шумний день.
Pooler, тюнінг і розумна поведінка клієнтів кожен вирішують різні аспекти цієї задачі. Використовуйте правильний інструмент — і ваш пейджер знову стане нудним.