Debian 13: PostgreSQL здається «випадково повільним» — 8 перевірок, які виявляють справжнє вузьке місце

«Випадкова повільність» — так кажуть, коли ще немає метрики, що пояснює проблему. Ваш застосунок добре працює годину, потім оформлення замовлення триває 12 секунд, а потім усе знову нормалізується. Ніхто нічого не змінював (окрім того, що точно змінилось). Ви на Debian 13, PostgreSQL «здоровий», але база даних ніби роздумує про сенс життя.

Це не проблема відчуттів. Зазвичай це одна з кількох чергових вузьких місць: латентність диска, навантаження на WAL, очікування блокувань, звільнення пам’яті, autovacuum, CPU steal, DNS-запити або сплески підключень. Трюк — перестати витріщатися на top і почати виконувати перевірки, які відокремлюють «повільний запит» від «повільної системи».

Швидкий план діагностики (перше/друге/третє)

Якщо ви на виклику, часу на філософські дискусії немає. Потрібна послідовність, що швидко звузить область пошуку, без випадкового «виправлення» симптомів перезапусками.

Перше: доведіть, чи це PostgreSQL, чи хост

1. Перевірте, чи сесії очікують. Якщо багато активності в wait_event, швидше за все це конкуренція або IO, а не «повільний SQL».
2. Перевірте латентність сховища прямо зараз. Якщо читання/записи стрибають у десятки або сотні мілісекунд, PostgreSQL виглядатиме «випадково повільним», незалежно від індексів.
3. Перевірте чергу виконання CPU та steal. Якщо вам не вистачає CPU (або ви на галасливому хості VM), запити гальмують навіть коли IO в порядку.

Друге: визначте категорію вузького місця

1. Блокування: накопичуються заблоковані сесії; одна погана транзакція тримає блок і псує роботу всім.
2. WAL / коміти: «простi оновлення повільні» з великою часткою часу в commit; латентність вказує на flush WAL/кеш пристрою.
3. Autovacuum: періодична повільна робота; зріст IO; активність vacuum/analyze збігається зі скаргами застосунку.
4. Підключення: стрибки у створенні з’єднань; зріст CPU/латентності; база витрачає час на accept/log/auth.

Третє: підтвердіть одним «заземленим» показником

• Для IO: await і завантаження в iostat, плюс pg_stat_io PostgreSQL якщо доступно.
• Для блокувань: pg_locks з мапуванням blocker/blocked.
• Для WAL: pg_stat_wal, контрольні точки та pg_stat_bgwriter; корелюйте з записами пристрою.
• Для autovacuum: pg_stat_progress_vacuum і індикатори bloat таблиць.

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

Цікаві факти та контекст (чому так відбувається)

• За замовчуванням налаштування надійності PostgreSQL консервативні. fsync=on і синхронні flush WAL означають, що брехня в сховищі перетворюється на вашу проблему латентності, а не на PostgreSQL.
• Linux охоче використовує «вільну» RAM для page cache. Це добре… поки тиск пам’яті не викликає reclaim, що може виглядати як випадкові затримки.
• Autovacuum існує через MVCC у PostgreSQL. Старі версії рядків не зникають, доки vacuum їх не прибере. Ігнорувати його — означає перетворити «випадкову» повільність на постійну.
• Тонке налаштування checkpoint має давню історію непорозумінь. Люди збільшують checkpoint_timeout, щоб «зменшити IO», і випадково створюють величезні сплески IO пізніше.
• Архітектура «підключення на запит» була звичною в ранніх веб-стеках. Вона досі з’являється в сучасних системах через неправильні пулі, і все ще шкідлива.
• PostgreSQL 9.6 ввів великі покращення автозвакууму. БД стала краща в самообслуговуванні, але все ще потребує здорового дизайну таблиць і моніторингу.
• Сучасні NVMe можуть бути швидкими й одночасно викликати затримки. Стрибки латентності через garbage collection прошивки або поведінку кешу записів можуть перетворити «швидке сховище» на «капризне сховище».
• За замовчуванням Debian робить вибір на користь стабільності. Це добре, щоб не ламатись; але означає, що для хоста бази даних слід свідомо обирати продуктивні налаштування.
• Стиснення WAL і контрольні суми — це компроміси. Вони можуть покращити IO-патерни або безпеку, але також змінюють характеристики CPU і латентності.

Цитата, яку варто пам’ятати, коли тягне здогадуватися: Надія — це не стратегія. — генерал Гордон Р. Салліван.

8 перевірок, що виявляють справжнє вузьке місце

Перевірка 1: Запити повільні чи вони очікують?

«Повільне» часто означає «очікування». PostgreSQL відокремлює активну роботу CPU від очікувань на блокування, IO, flush WAL, читання від клієнта та ще десяток речей. Найшвидший спосіб припинити здогадки — глянути на wait_event_type і wait_event.

Якщо більшість сесій active без очікувань, у вас проблеми з CPU або планами запитів. Якщо більшість чекає на Lock, — проблема в контенції блокувань. Якщо вони чекають на IO або WAL, — це поведінка сховища та шляху запису.

Перевірка 2: Хост показує стрибки латентності диска?

«Випадкова повільність» — природний опис варіації латентності. Бази даних ненавидять варіацію. Кілька записів по 200 мс не дивляться страшно на графіках пропускної здатності, але вони перетворюють коміти на таймаути і роблять додаток моторошним.

На Debian 13 використовуйте iostat для негайної видимості. Ви дивитесь на await і %util. Високе %util з ростом await означає насичення пристрою або сильну чергу. Низьке %util з високим await часто натякає на проблеми підґрунтя (віртуалізація, контролер, multipath, flush кешу записів).

Перевірка 3: Чи є WAL справжнім вузьким місцем (латентність коміту)?

Багато команд ганяються за «повільними запитами», коли реальна проблема — коміти. Якщо навантаження багато записове, кожна транзакція потребує WAL. Якщо латентність flush WAL стрибає, усе, що комітиться, стає повільним — навіть крихітні оновлення.

Симптоми: вставки/оновлення, що зазвичай тривають мілісекунди, інколи тривають секунди, а читання менш зачеплені. Це вказує від планів запитів до шляху запису.

Перевірка 4: Чи сплески IO викликані контрольними точками та background writer?

Контрольні точки потрібні. Водночас вони часто стають джерелом періодичних затримок при неправильному налаштуванні або коли сховище не витримує сплесків. PostgreSQL намагається розтягнути IO контрольної точки, але під тиском воно все одно може збиратися.

Якщо бачите регулярні «кожні N хвилин усе гальмує», підозрюйте поведінку контрольних точок, autovacuum або зовнішні завдання (бекапи, scrub, log rotation). Не оптимізуйте всліпу. Вимірюйте.

Перевірка 5: Чи накопичуються блокування за однією транзакцією?

Контенція блокувань — най «випадково-здавальна» база для уповільнень, бо вона залежить від таймінгу. Довга транзакція, що тримає блок 30 секунд, може не бути проблемою… поки не потрапить у невірні 30 секунд.

Мета — знайти блокувальника і список жертв, а потім вирішити: вбити блокувальника, виправити кодову доріжку або змінити ізоляцію/поведінку блокувань.

Перевірка 6: Чи конкурує autovacuum з вашою роботою (або тихо відмовляє)?

Autovacuum — як винос сміття: ви помічаєте його, тільки коли ніхто цього не робив. Коли воно занадто агресивне, воно конкурує з вашим навантаженням за IO і CPU. Коли воно занадто слабке, росте bloat, індекси роздуваються і продуктивність деградує повільно.

На завантаженій системі неправильно налаштований autovacuum може викликати періодичні IO-сплески, що виглядають випадковими для команд, але дуже регулярні для тих, хто графує.

Перевірка 7: Чи тиск пам’яті викликає reclaim або swap?

PostgreSQL разом із Linux page cache та «ще одним сайдкаром» може штовхнути хост у reclaim. Ви побачите використання CPU, але це не корисний CPU; це ядро намагається знайти пам’ять. База «випадково гальмує», бо буквально чекає, поки сторінки пам’яті стануть доступними.

Якщо ви коли-небудь бачите активність swap на хості PostgreSQL і не планували цього навмисно, тримайте це як сигнал тривоги. Це може не бути пожежею, але її ігнорувати не можна.

Перевірка 8: Чи впроваджує латентність мережа/auth/DNS?

Так, DNS може сповільнити PostgreSQL. Так само зворотні DNS-пошуки в логуванні, затримки LDAP-аутентифікації або втрата пакетів між додатком і БД. База може бути в порядку, а підключення гальмувати або аутентифікація призупинятись. Для застосунку — «випадкова повільність», для мережі — абсолютно послідовна поведінка.

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

Жарт №1: «Випадкова повільність» — це те, що система каже, коли хоче, щоб ви встановили моніторинг, а ви продовжуєте давати їй позитивні вібрації.

Практичні завдання: команди, що означає вивід, рішення

Нижче — практичні завдання, які ви можете виконати на Debian 13 і в PostgreSQL. Кожне містить: команду, що означає її вивід, і рішення, яке ви з цього приймаєте. Запускайте їх під час сповільнення, якщо можете; інакше — зараз, щоб встановити базові показники.

Завдання 1: Дізнайтесь, хто чекає і на що

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT now(), state, wait_event_type, wait_event, count(*) FROM pg_stat_activity GROUP BY 1,2,3,4 ORDER BY count(*) DESC;"
              now              | state  | wait_event_type | wait_event | count
-------------------------------+--------+-----------------+------------+-------
 2025-12-29 10:14:02.12345+00 | active |                 |            |    12
 2025-12-29 10:14:02.12345+00 | active | IO              | DataFileRead |     7
 2025-12-29 10:14:02.12345+00 | active | Lock            | relation   |     5
 2025-12-29 10:14:02.12345+00 | idle   | Client          | ClientRead |    30

Що це означає: Ви категоризуєте біль. IO/DataFileRead вказує на латентність або промахи кешу. Lock/relation вказує на конкуренцію. Багато ClientRead означає, що клієнти в стані idle, а не БД.

Рішення: Якщо очікування концентруються в одному типі, переходьте безпосередньо до відповідної перевірки (IO, блокування, WAL). Якщо більшість active без очікувань, зосередьтесь на CPU та планах запитів.

Завдання 2: Визначте найгірший поточний запит

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT pid, now()-query_start AS runtime, state, wait_event_type, wait_event, left(query, 120) AS query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY runtime DESC LIMIT 5;"
 pid  |  runtime  | state  | wait_event_type |  wait_event  |                         query
------+-----------+--------+-----------------+--------------+--------------------------------------------------------
 8421 | 00:02:31  | active | Lock            | transactionid| UPDATE orders SET status = $1 WHERE id = $2
 9112 | 00:01:04  | active | IO              | DataFileRead | SELECT ... FROM order_items WHERE order_id = $1

Що це означає: Якщо топовий за часом виконання запит чекає на блокування — це не «повільний SQL», а блокування. Якщо він чекає на IO — ви займаєтесь сховищем або кешем.

Рішення: Для очікувань блокувань знайдіть блокувальника (Завдання 7). Для IO — перевірте латентність пристрою (Завдання 4) і співвідношення кеш-хітів (Завдання 10).

Завдання 3: Швидка перевірка завантаження хоста і черги виконання

cr0x@server:~$ uptime
 10:14:10 up 38 days,  2:11,  3 users,  load average: 18.42, 17.90, 16.12

Що це означає: Load average — це не використання CPU; це runnable + uninterruptible tasks. Load 18 на 8-ядерній машині зазвичай означає чергу на CPU або блокування через IO.

Рішення: Корелюйте з Завданням 4 (IO) та Завданням 5 (CPU). Високий load + високе IO wait вказує на сховище. Високий load + високий CPU — на обчислення або плани запитів.

Завдання 4: Виміряйте латентність диска та насичення за допомогою iostat

cr0x@server:~$ sudo iostat -xz 1 5
Linux 6.12.0-debian (db01) 	12/29/2025 	_x86_64_	(16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          22.11    0.00    6.90   18.30    0.00   52.69

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   w_await wareq-sz  aqu-sz  %util
nvme0n1         820.0  55200.0     0.0    0.0   12.4    67.3     610.0   48800.0   45.8    80.0    42.3  98.7

Що це означає: w_await близько 46ms з %util ≈99% — поганий знак. Коміти та контрольні точки PostgreSQL відчуватимуть уповільнення. Також високе %iowait.

Рішення: Розглядайте сховище як головного підозрюваного. Наступні кроки: перевірити, чи це пристрій з PGDATA, підтвердити налаштування кешу записів, розслідувати конкуренцію IO та перевірити опції монтування файлової системи.

Завдання 5: Швидка перевірка насичення CPU vs IO wait

cr0x@server:~$ mpstat -P ALL 1 3
Linux 6.12.0-debian (db01) 	12/29/2025 	_x86_64_	(16 CPU)

12:14:20 PM  CPU   %usr  %sys  %iowait  %steal  %idle
12:14:21 PM  all   28.0   7.0    2.0     0.0    63.0
12:14:21 PM   3   92.0   5.0    0.0     0.0     3.0
12:14:21 PM   7   88.0   6.0    0.0     0.0     6.0

Що це означає: Декілька «гарячих» CPU можуть свідчити про однонитковий вузький переріз (часто один запит, побудова індексу, worker autovacuum або однією ниткою WAL writer). Наявність %steal ненульового на VM вказує на галасливих сусідів.

Рішення: Якщо CPU завантажений при низькому IO wait — дивіться топ-запити і плани. Якщо high steal — підключайте команду віртуалізації/платформи.

Завдання 6: Піймайте тиск пам’яті та swapping

cr0x@server:~$ free -h
               total        used        free      shared  buff/cache   available
Mem:            64Gi        51Gi       1.2Gi       1.1Gi        12Gi        7.8Gi
Swap:            8Gi       2.5Gi       5.5Gi

Що це означає: Використання swap на хості БД — це податок на продуктивність. Це може бути історичне (одноразово відswapано, потім не підвантажувалось) або активний thrash.

Рішення: Підтвердіть активність swap за допомогою vmstat (Завдання 12). Якщо відбувається активний swapping — зменшіть тиск пам’яті: знизьте shared_buffers, обмежте кількість підключень, припиніть сумісні навантаження на тому ж хості або додайте RAM.

Завдання 7: Знайдіть блокувальників і жертв (контенція блокувань)

cr0x@server:~$ sudo -u postgres psql -X -c "
WITH locked AS (
  SELECT pid, locktype, relation::regclass AS rel, transactionid, mode, granted
  FROM pg_locks
),
activity AS (
  SELECT pid, usename, application_name, client_addr, state, now()-xact_start AS xact_age, left(query,120) AS query
  FROM pg_stat_activity
)
SELECT a.pid AS blocked_pid, a.usename, a.application_name, a.xact_age, a.query,
       b.pid AS blocker_pid, b.usename AS blocker_user, b.application_name AS blocker_app, b.xact_age AS blocker_xact_age, b.query AS blocker_query
FROM locked l1
JOIN locked l2 ON l1.locktype = l2.locktype
  AND coalesce(l1.rel::text,'') = coalesce(l2.rel::text,'')
  AND coalesce(l1.transactionid::text,'') = coalesce(l2.transactionid::text,'')
  AND l1.pid <> l2.pid
JOIN activity a ON a.pid = l1.pid
JOIN activity b ON b.pid = l2.pid
WHERE NOT l1.granted AND l2.granted
ORDER BY a.xact_age DESC;
"
 blocked_pid | usename | application_name | xact_age |                        query                        | blocker_pid | blocker_user | blocker_app | blocker_xact_age |           blocker_query
------------+---------+------------------+----------+-----------------------------------------------------+------------+--------------+-------------+------------------+----------------------------------
       8421 | app     | api              | 00:02:31 | UPDATE orders SET status = $1 WHERE id = $2         |       7710 | app          | worker      | 00:18:09         | UPDATE orders SET ... WHERE ...

Що це означає: Одна довга транзакція блокує інших. Блокувальник у транзакції вже 18 хвилин. Це рідко нормально у OLTP.

Рішення: Вирішіть, чи завершувати блокувальника (увага: він відкотиться), чи змінити поведінку аплікації (відсутній індекс, занадто широкий контекст транзакції, очікування зовнішнього виклику всередині транзакції).

Завдання 8: Проінспектуйте стан WAL і контрольних точок

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT * FROM pg_stat_bgwriter;"
 checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_backend_fsync | buffers_alloc
------------------+-----------------+-----------------------+----------------------+--------------------+---------------+------------------+----------------+-----------------------+--------------
             1289 |             412 |               8123456 |               923456 |           73456789 |       1234567 |             9876 |        2345678 |                 12345 |     987654321

Що це означає: Високе checkpoints_req натякає, що ви змушуєте контрольні точки через обсяг WAL, а не по розкладу. Високі times для запису/синхронізації контрольної точки корелюють з IO-затримками.

Рішення: Якщо вимушені контрольні точки часті, розгляньте збільшення max_wal_size і перевірте checkpoint_completion_target. Але не «налаштовуйте» всліпу — спочатку підтвердіть пропускну здатність IO і поведінку пристрою WAL.

Завдання 9: Подивіться статистику WAL (якщо доступно)

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full, wal_write, wal_sync, stats_reset FROM pg_stat_wal;"
 wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync |        stats_reset
------------+---------+-----------+------------------+-----------+----------+-------------------------------
   88234567 |  123456 | 98 GB     |             4212 |     91234 |    45678 | 2025-12-27 00:00:00+00

Що це означає: Часті wal_buffers_full натякають на тиск буфера WAL. Високі лічильники write/sync самі по собі не погані; корелюйте їх з латентністю пристрою і часами комітів.

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

Завдання 10: Перевірте коефіцієнт попадань у кеш (з попередженням)

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT datname, blks_hit, blks_read, round(100.0*blks_hit/GREATEST(blks_hit+blks_read,1),2) AS hit_pct FROM pg_stat_database ORDER BY hit_pct ASC;"
 datname  | blks_hit  | blks_read | hit_pct
----------+-----------+-----------+---------
 appdb    | 987654321 | 45678901  | 95.59

Що це означає: Коефіцієнт хітів — не KPI, якому слід поклонятись; це підказка. 95% може бути добре або погано залежно від навантаження й сховища. Раптовий спад під час інцидентів важливіший за абсолютне число.

Рішення: Якщо hit ratio падає під час сповільнення і зростає латентність IO, ви звертаєтесь на диск. Досліджуйте пам’ять, розмір робочого набору та зміну шаблонів запитів.

Завдання 11: Знайдіть найбільших «часових споживачів» з pg_stat_statements

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT queryid, calls, round(total_exec_time::numeric,1) AS total_ms, round(mean_exec_time::numeric,2) AS mean_ms, rows, left(query,120) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;"
 queryid  | calls  | total_ms | mean_ms | rows  |                          query
----------+--------+----------+---------+-------+--------------------------------------------------------
 12345678 | 998765 | 8123456.0|   8.13  | 998765| SELECT * FROM users WHERE email = $1
 88776655 |  12345 | 6234567.0| 505.12  |  12345| SELECT ... FROM orders JOIN ... WHERE created_at > $1

Що це означає: total_exec_time показує найбільшу сумарну вартість. Запити з високим mean — вбивці латентності; з високим total — вбивачі ємності.

Рішення: Для запитів з високим mean запустіть EXPLAIN (ANALYZE, BUFFERS) у безпечному середовищі. Для високого total розгляньте кешування, індекси або зміну запиту. Якщо час запиту зростає тільки під час інцидентів — спочатку корелюйте з wait-подіями і метриками хоста.

Завдання 12: Підтвердіть активний swap або reclaim за допомогою vmstat

cr0x@server:~$ vmstat 1 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 5  2 262144 312000  80000 9000000  12   38   120   890  900 2400 18  6 55 21
 7  1 262200 298000  76000 8800000   0   52    80  1200 1100 2700 20  7 48 25

Що це означає: Ненульові si/so означають підвантаження/вивантаження у swap. Це не «можливо». Це — «ваша база платить оренду за swap». Також зверніть увагу на b (blocked) і wa (IO wait).

Рішення: Якщо swapping активний — припиніть налаштовування SQL і виправляйте тиск пам’яті: зменшіть паралелізм, додайте RAM, перемістіть навантаження або знайдіть і зупиніть процеси, що йдуть у розряд runaway.

Завдання 13: Перевірте файлову систему та опції монтування для PGDATA

cr0x@server:~$ findmnt -no SOURCE,FSTYPE,OPTIONS /var/lib/postgresql
/dev/mapper/vg0-pgdata ext4 rw,relatime,errors=remount-ro

Що це означає: Ви хочете знати, на якій файловій системі ви і чи опції монтування дивні. Для PostgreSQL «кмітливі» опції монтування зазвичай закінчуються інцидентами.

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

Завдання 14: Перевірте, чи PostgreSQL не обмежують cgroups

cr0x@server:~$ systemctl show postgresql --property=CPUQuota,MemoryMax,IOReadBandwidthMax,IOWriteBandwidthMax
CPUQuota=
MemoryMax=infinity
IOReadBandwidthMax=
IOWriteBandwidthMax=

Що це означає: Якщо встановлені квоти (особливо обмеження пропускної здатності IO), ви можете самі собі створити «випадкову» повільність, коли навантаження зростає і ліміти вмикаються.

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

Завдання 15: Перегляньте логи PostgreSQL на предмет контрольних точок, autovacuum і сплесків duration

cr0x@server:~$ sudo journalctl -u postgresql@15-main -S -2h | egrep -i "checkpoint|autovacuum|duration|could not|timeout" | tail -n 15
Dec 29 09:02:10 db01 postgres[2210]: LOG:  checkpoint complete: wrote 32145 buffers (1.9%); 0 WAL file(s) added, 2 removed, 1 recycled; write=78.123 s, sync=1.992 s, total=80.256 s
Dec 29 09:10:44 db01 postgres[2210]: LOG:  automatic vacuum of table "app.public.orders": index scans: 1 pages: 0 removed, 123456 remain, 12000 scanned (9.72% of total) tuples: 0 removed, 500000 remain
Dec 29 09:11:02 db01 postgres[2210]: LOG:  duration: 2412.889 ms  execute <unnamed>: UPDATE orders SET ...

Що це означає: Контрольна точка, що триває 80 секунд, — це не дрібниця. Autovacuum поруч з вікнами інцидентів може бути причиною або просто корелювати; все одно потрібні IO-метрики.

Рішення: Якщо часи контрольних точок величезні — пріоритет для продуктивності сховища і налаштування checkpoint. Якщо сплески duration збігаються з очікуваннями блокувань — вирішуйте блокування. Якщо autovacuum активно, налаштуйте його для великих таблиць і перевірте bloat.

Завдання 16: Підтвердіть «збурення» підключень (занадто багато нових з’єднань)

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit FROM pg_stat_database;"
 datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit
--------+-------------+-------------+---------------+----------+----------
 appdb  |         240 |   987654321 |        123456 | 45678901 | 987654321

Що це означає: numbackends — це поточні підключення. Сплески тут, особливо з високим CPU і контекстними переключеннями, часто означають зломаний пул або несподівану зміну трафіку.

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

Жарт №2: «Тимчасова» налагоджувальна опція в продакшені має той самий період напіврозпаду, що й пляма від кави на білій сорочці.

Три короткі корпоративні історії (що пішло не так, що спрацювало)

Міні-історія 1: Інцидент через неправильне припущення

Середня SaaS-компанія мігрувала свій головний хост бази з старих SATA SSD на блискучі NVMe. Вікно змін пройшло гладко. Бенчмарки були чудові. Усі чекали менше інцидентів.

Через два тижні служба підтримки почала фіксувати «випадкову повільність» у пікові періоди: таймаути оформлення замовлень, повтори фонового коду і іноді API 504. Метрики бази даних не кричали. CPU не був завантажений. Мережа була в порядку. Стандартне пояснення команди стало «Postgres дивний під навантаженням», що еквівалентно знизуванню плечей у таблиці Excel.

Неправильне припущення: «NVMe швидке, отже сховище не може бути вузьким місцем». Вони дивились на пропускну здатність, а не на варіацію латентності. Під тривалим записовим навантаженням пристрій інколи піднімав латентність запису до сотень мілісекунд через внутрішню поведінку. PostgreSQL не переймається середнім throughput; його турбує, що один fsync зайняв 400ms і заблокував купу комітів за собою.

Вони довели це, захопивши iostat -xz 1 під час інциденту і скорелювавши з логами повільних транзакцій. Сплески збіглися ідеально. Після переміщення WAL на виділений пристрій і налаштування checkpoint/WAL для зменшення сплесків, «випадкова повільність» перетворилася на нудну передбачуваність.

Висновок: перестаньте трактувати сховище бінарно (швидке/повільне). Для баз даних важлива саме варіація.

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

Фінтех-платформа мала періодичні паузи кожні 15 хвилин. Хтось помітив контрольні точки в логах і зробив те, що часто радить інтернет: різко збільшив checkpoint_timeout і max_wal_size, щоб «зменшити частоту контрольних точок». Це спрацювало — частково.

Контрольні точки стали рідше, тож графіки на деякий час стали гладшими. Але коли контрольні точки запускалися, вони були величезні. IO-підсистема не розрахована на такі сплески. Під час тих контрольних точок латентність росла вертикально, а аплікація накопичувала повтори, що створювало ще більше тиску на запис. Інцидент став рідшим, але гіршим і важчим для відтворення в стейджингу. Чудово.

Коли вони нарешті виміряли потрібні речі — латентність пристрою і checkpoint_write_time — патерн став очевидним. «Оптимізація» переразподілила біль у менші часті вибухи в рідкі великі вибухи. Вони повернули checkpoint_timeout на менше значення, збільшили checkpoint_completion_target для зглажування IO і підібрали WAL так, щоб контрольні точки не змушувались через WAL churn.

Також знайшли другорядну проблему: щотижневий аналітичний джоб робив величезні оновлення без батчування, створюючи WAL-сплески, що змушували контрольні точки незалежно від таймауту. Виправлення цього джоба зменшило IO і обсяг WAL більше, ніж будь-який регулятор.

Висновок: «менше контрольних точок» не завжди «краще». Мета — стабільний IO, а не рідкісні катастрофи.

Міні-історія 3: Нудна, але правильна практика, що врятувала день

Команда внутрішньої платформи великої компанії тримала PostgreSQL для кількох підрозділів. Нічого гламурного: інтеграції зарплат, HR-процеси, закупівлі. Їхня найкраща риса — інциденти були рідкі і короткі.

Що вони робили не було магією. Вони мали писаний рукопис з швидкою послідовністю діагностики: перевірити waits, перевірити латентність IO, перевірити блокування, autovacuum, тиск пам’яті, потім вирішувати. Також були базові показники: «нормальний» iostat latency, типовий count підключень і очікувані часи контрольних точок. Щоквартально вони проводили 30-хвилинні вправи, де хтось симулював повільність бази, і всі тренувались збирати докази без перезапусків сервісів.

Коли оновлення агента вендора почало гризти диск логами, команда не сперечалась про чию то провину. Вони побачили зріст латентності IO, підтвердили очікування PostgreSQL на IO, ідентифікували процес з високим IO по статистиці на процес і обмежили агента. Бізнес цього майже не відчув, окрім того, що статусна сторінка залишилась нудною.

Висновок: нудна дисципліна перемагає героїчне налаштування. Базові показники плюс відпрацьований план перетворюють «випадкову повільність» на «тікет з доказами».

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

Цей розділ навмисно конкретний. Якщо ви впізнали симптом — не дискутуйте на нараді, а проведіть перевірку корінної причини.

1) Симптом: «Усе повільно, але CPU низький»

• Корінна причина: IO-waits (стрибки латентності сховища) або контенція блокувань.
• Виправлення: Перевірте pg_stat_activity waits і iostat -xz. Якщо IO — зменшіть конкуренцію IO, перенесіть WAL, перевірте здоров’я сховища. Якщо блокування — знайдіть і виправте блокувальника; скоротіть тривалість транзакцій.

2) Симптом: Записи повільні; читання в порядку

• Корінна причина: Латентність flush WAL, контрольні точки або очікування синхронної реплікації (якщо налаштовано).
• Виправлення: Перевірте pg_stat_wal, статистику контрольних точок і латентність запису пристрою. Переконайтесь, що WAL на швидкому, стабільному сховищі. Не вимикайте надійність як «фікс», якщо не готові прийняти втрату даних.

3) Симптом: Періодичні уповільнення за регулярними інтервалами

• Корінна причина: Контрольні точки, цикли autovacuum, cron-задачі, scrubs, бекапи, ротація логів або пакетні навантаження.
• Виправлення: Корелюйте часові вікна з логами (journalctl) і метриками. Згладьте IO контрольних точок; налаштуйте autovacuum на рівні таблиць; перенесіть пакетні роботи; ізолюйте бекап IO.

4) Симптом: Стрибки числа підключень і латентності під час піков

• Корінна причина: Злом пулу підключень або новий клієнтський реліз, що відкриває забагато сесій.
• Виправлення: Введіть пулінг і обмежте max_connections відповідно до доступної RAM. Використовуйте пулер, але тестуйте семантику транзакцій і поведінку підготовлених запитів.

5) Симптом: «Випадкові» таймаути, але лише для певних ендпоінтів

• Корінна причина: План запиту інколи переключається між index scan і seq scan, або план чутливий до параметрів.
• Виправлення: Використовуйте pg_stat_statements щоб знайти винуватця і запускайте EXPLAIN (ANALYZE, BUFFERS) з репрезентативними параметрами. Розгляньте обережно plan_cache_mode або перепишіть запит для стабільного плану.

6) Симптом: Vacuum постійно виконується, але продуктивність все одно падає

• Корінна причина: Autovacuum не встигає, або bloat вже великий; довгі транзакції перешкоджають прибиранню.
• Виправлення: Знайдіть довгі транзакції, скоротіть їх тривалість, налаштуйте autovacuum для великих таблиць. При серйозному bloat плануйте технічні роботи (наприклад, VACUUM (FULL) або стратегії онлайн-ребілду) з чітким планом.

7) Симптом: Після оновлення Debian PostgreSQL працює повільніше на тому ж навантаженні

• Корінна причина: Зміни в ядрі/планувальнику IO, інші значення за замовчуванням, налаштування cgroup, або змінена поведінка файлової системи; іноді це просто виявлення нового вузького місця.
• Виправлення: Порівняйте базові показники: латентність IO, CPU steal, reclaim пам’яті і PostgreSQL wait-події. Переконайтесь, що не з’явилися нові обмеження сервісів і що великі сторінки, THP або governor не змінили поведінку.

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

Крок за кроком: під час активного уповільнення (10 хвилин, без геройства)

1. Захопіть waits PostgreSQL: запустіть Завдання 1 і збережіть вивід.
2. Захопіть найгірші активні запити: запустіть Завдання 2.
3. Захопіть латентність IO: запустіть Завдання 4.
4. Захопіть насичення CPU: запустіть Завдання 5.
5. Захопіть тиск пам’яті: запустіть Завдання 6 і Завдання 12.
6. Якщо з’явились блокування: запустіть Завдання 7 і вирішіть, чи вбивати блокувальника, чи чекати.
7. Якщо підозра на WAL/checkpoint: запустіть Завдання 8 і Завдання 9; корелюйте з записами IO і логами (Завдання 15).
8. Якщо підключень багато: запустіть Завдання 16; перевірте стан пулу і зміни деплою аплікації.
9. Запишіть часові межі: «повільно з 10:12:40 до 10:16:10». Кореляція потребує міток часу.

Крок за кроком: після інциденту (те, що команди пропускають і потім повторюють інциденти)

1. Класифікуйте інцидент: IO-bound, lock-bound, CPU-bound, memory-bound, WAL-bound, connection-bound або network/auth-bound.
2. Виберіть одну основну метрику для алерта: наприклад, disk await вище порогу, кількість wait-ів блокувань, час checkpoint, активність swap.
3. Додайте одну панель на дашборді, що зробила б це очевидним. Не дванадцять. Одна.
4. Зробіть одну зміну в коді/конфігурації з планом відкату. Не «настрой усе підряд».
5. Оновіть рукопис. Якщо фікс вимагав племінного знання, ви маєте reliability-баг.

Питання та відповіді

1) Чому PostgreSQL здається повільним, навіть коли CPU низький?

Бо він часто чекає, а не обчислює. Очікування на блокування і IO не спалюють CPU. Дивіться wait_event_type в pg_stat_activity і латентність диска за допомогою iostat.

2) Які значення «await» в iostat вважати поганими?

Для OLTP стійкі однозначні мілісекунди зазвичай нормальні; стійкі десятки мс — проблема; стрибки в сотні мс будуть помітні користувачам. Точний поріг залежить від SLO, але «await росте при %util близько 100%» — класичний сигнал насичення.

3) Чи варто збільшувати shared_buffers, щоб виправити повільність?

Не як рефлекс. Надто велике shared_buffers може позбавити OS page cache і штовхнути систему в reclaim або swap. Розмір обирайте навмисно і перевіряйте тиск пам’яті за допомогою vmstat і free.

4) Чи autovacuum має викликати падіння продуктивності?

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

5) Чи контрольні точки можуть викликати «кожні N хвилин усе гальмує»?

Так. Контрольні точки створюють сплески записів. Якщо сховище не витримує сплеск — латентність зростає. Дивіться часи контрольних точок в логах і pg_stat_bgwriter, потім корелюйте з латентністю пристрою.

6) Чи встановлення synchronous_commit=off — хороший фікс для випадкової повільності?

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

7) Як зрозуміти, чи проблема в блокуваннях?

Якщо багато сесій чекають на Lock і ви можете знайти блокувальника, що тримає блоки довго (Завдання 7) — це блокування. Виправляйте довгі транзакції та гарячі рядки.

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

Кожне підключення обходиться пам’яттю, CPU і часто викликає аутентифікацію/логування. У режимі сплеску база витрачає ресурси на управління підключеннями замість виконання запитів. Виправляйте на клієнті через пулінг і розумні ліміти.

9) Чи Debian 13 змінює щось, що може вплинути на латентність PostgreSQL?

Оновлення ОС може змінити поведінку ядра, планувальник IO і налаштування за замовчуванням сервісів і cgroups. Розглядайте це як нову базу: перевіряйте латентність IO, CPU steal і reclaim пам’яті під репрезентативним навантаженням замість припущення «те ж саме, але новіше».

10) Яке розширення PostgreSQL найкорисніше для триажу продуктивності?

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

Висновок: наступні кроки, які можна зробити сьогодні

PostgreSQL не «випадково повільний». Ваша система — так. База даних просто місце, де користувачі це відчувають першими.

1. Реалізуйте швидкий план. Зробіть «waits, IO latency, locks» першими трьома перевірками. Відпрацюйте його один раз.
2. Встановіть базові показники хоста. Запустіть iostat, vmstat і підрахунок підключень під нормальним навантаженням і збережіть числа.
3. Перетворіть найбільшу невідомість у метрику. Якщо ви не знаєте, чи інциденти IO-bound чи lock-bound — у вас не план реагування, а забобон.
4. Виправляйте одне вузьке місце за раз. Найшвидший спосіб продовжити простій — змінити п’ять регуляторів і потім сперечатись, який допоміг.

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