Skip to content

Як знайти й прискорити повільні запити PostgreSQL у продакшн-застосунку на Node.js: pg_stat_statements, читання EXPLAIN (ANALYZE, BUFFERS), складені, часткові й покривні індекси, N+1, keyset-пагінація, пул з'єднань і безпечне створення індексів. Усі плани — реальні, з PostgreSQL 18.6.

Опубліковано
Читання
16 хв
Автор
Yaroslav Tsarenko
–переглядів
Темний рельєф, крізь який заплутана лінія пошуку перетворюється на коротку пряму стежку до світлої точки

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

Це практичний гайд для команд, у яких бекенд на Node.js працює з PostgreSQL. Розберемо, як знайти запити, що справді гальмують, як читати план виконання, які індекси ставити під які запити, як прибрати N+1 і повільну пагінацію, як налаштувати пул з'єднань і як створити індекс у продакшені, нічого не заблокувавши.

Усі плани в статті — реальні. Я запускав їх на PostgreSQL 18.6 у Docker із Node.js 25 і pg (node-postgres) 8.23 на синтетичній базі: 2 млн замовлень, 200 тенантів, 50 тисяч клієнтів і 3 млн позицій замовлень. Час у мілісекундах залежить від заліза й кешу, тож дивіться на порядок величин і на кількість прочитаних буферів: вони переносяться на вашу базу краще, ніж секундомір.

Чому швидкий запит стає повільним

Майже завжди причина в одному з чотирьох:

  • Дані виросли. Послідовний перегляд 20 тисяч рядків непомітний, а 2 мільйонів уже ні. Запит без індексу був «нормальним» лише тому, що таблиця була малою.
  • План змінився. Планувальник оцінює кількість рядків за статистикою. Коли розподіл даних зсувається, а ANALYZE давно не виконувався, оцінка помиляється в сотні разів, і план, оптимальний для 100 рядків, запускається на 100 тисячах.
  • Запитів забагато. Кожен окремо швидкий, але ендпоінт робить їх 200. Це N+1, і в профайлері бази воно виглядає як тисячі дрібних викликів.
  • Чекає не запит, а з'єднання. Пул вичерпано, запити стоять у черзі в Node.js, а база при цьому відпочиває.

Перші два пункти лікуються індексами і переписаними запитами, третій — батчингом, четвертий — налаштуванням пулу. Але спочатку треба знати, з чим маєте справу.

Як знайти повільні запити

pg_stat_statements: що забирає час бази

Розширення pg_stat_statements агрегує статистику за нормалізованими запитами: однаковий текст з різними параметрами — один рядок. За документацією, модуль треба додати в shared_preload_libraries і перезапустити сервер. На керованих базах модуль часто вже завантажено провайдером, і лишається виконати CREATE EXTENSION; перевірте це в документації свого хостингу.

sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT left(query, 72) AS query,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       shared_blks_read AS blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 4;

Вивід на тестовій базі після кількох прогонів API:

text
                                  query                                   | calls | total_ms | mean_ms | blks_read
--------------------------------------------------------------------------+-------+----------+---------+-----------
 select count(*) from orders where total_cents > $1 and status=$2         |     1 |    178.8 |  178.80 |     14652
 select order_id, sku, qty from order_items where order_id = $1           |   500 |     11.7 |    0.02 |         0
 select id, total_cents from orders where tenant_id = $1 order by created |    20 |      3.4 |    0.17 |         0
 select order_id, sku, qty from order_items where order_id = any($1::bigi |    10 |      2.5 |    0.25 |         0

Сортуйте за total_exec_time, а не за mean_exec_time. Запит на 2 мс, який викликають мільйон разів на день, важить більше за звіт на 3 секунди раз на тиждень. І дивіться на calls: другий рядок викликано 500 разів, а запит списку замовлень — лише 20. Співвідношення 25 до 1 — типовий відбиток N+1, до нього ще повернемося.

Логи і auto_explain: окремі повільні виконання

pg_stat_statements показує середнє, але не конкретне повільне виконання з конкретними параметрами. Для цього є log_min_duration_statement: усі запити, довші за поріг, потрапляють у лог разом із текстом. Модуль auto_explain додає до лога ще й план:

sql
ALTER SYSTEM SET log_min_duration_statement = '500ms';
ALTER SYSTEM SET session_preload_libraries = 'auto_explain';
ALTER SYSTEM SET auto_explain.log_min_duration = '500ms';
SELECT pg_reload_conf();

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

Таймінги з боку Node.js

APM (Datadog, Sentry, New Relic, OpenTelemetry) пов'язує повільний запит з ендпоінтом, і саме це потрібно для пріоритизації. Якщо APM ще немає, мінімальна обгортка над пулом дає 80% користі:

ts
import pg from "pg";

export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: Number(process.env.PG_POOL_MAX ?? 5),
  idleTimeoutMillis: 10_000,
  connectionTimeoutMillis: 3_000,
  statement_timeout: 5_000,
  application_name: "api",
});

pool.on("error", (error) => console.error("idle client error", error.message));

const slowMs = Number(process.env.SLOW_QUERY_MS ?? 200);

export async function query<T extends pg.QueryResultRow>(name: string, text: string, values: unknown[] = []) {
  const start = performance.now();
  try {
    return await pool.query<T>(text, values);
  } finally {
    const ms = performance.now() - start;
    if (ms > slowMs) console.warn(JSON.stringify({ msg: "slow query", name, ms: Math.round(ms) }));
  }
}

Із SLOW_QUERY_MS=50 і запитом pg_sleep(6) вивід такий:

text
{"msg":"slow query","name":"orders.count.open.big","ms":139}
{"msg":"slow query","name":"sleep","ms":5012}
canceling statement due to statement timeout

Ім'я запиту в лозі одразу каже, який код шукати. А statement_timeout обриває запит через 5 секунд на боці сервера, замість того щоб тримати з'єднання з пулу хвилинами.

Як читати EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN показує план і оцінки, EXPLAIN ANALYZE виконує запит і додає факт. З PostgreSQL 18 документація прямо каже, що дані про буфери автоматично додаються, коли використано ANALYZE; на 17 і старших пишіть BUFFERS явно. Запускайте план на реальних параметрах із продакшену: план для тенанта з десятком замовлень і для тенанта з мільйоном — різні плани.

Типовий запит списку: відкриті замовлення тенанта, нові зверху.

sql
EXPLAIN (ANALYZE, COSTS OFF)
SELECT id, customer_id, status, total_cents, created_at
FROM orders
WHERE tenant_id = 42 AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;
text
 Limit (actual time=45.234..48.014 rows=20.00 loops=1)
   Buffers: shared hit=9732 read=9036
   ->  Gather Merge (actual time=45.233..48.011 rows=20.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort (actual time=43.253..43.254 rows=15.33 loops=3)
               Sort Key: created_at DESC
               Sort Method: top-N heapsort  Memory: 27kB
               ->  Parallel Seq Scan on orders (actual time=0.923..43.110 rows=172.33 loops=3)
                     Filter: ((tenant_id = 42) AND (status = 'open'::text))
                     Rows Removed by Filter: 666494
                     Buffers: shared hit=9656 read=9036
 Planning Time: 0.390 ms
 Execution Time: 48.082 ms
Читайте план знизу вгоруLimit20 рядків, 48 мсSort created_at DESCtop-N heapsort, loops=3Parallel Seq Scan ordersRows Removed by Filter: 666 494напрямок читанняSort перед Limitіндекс не дає порядку,база сортує самаloops=3час і рядки вказанона один циклRows Removed by Filter×3 процеси: майже всятаблиця заради 20 рядківBuffers: 18 768≈ 147 МБ сторінокна один запит списку

Читайте план знизу вгору і шукайте чотири речі:

  1. Rows Removed by Filter у вузлі сканування. Тут три процеси прочитали по 666 тисяч рядків, щоб знайти по 172. Це майже вся таблиця заради 20 рядків.
  2. Buffers. hit — сторінки з кешу PostgreSQL, read — з диска або кешу ОС. 18 768 сторінок по 8 КБ — це близько 147 МБ прочитаних даних на один запит списку.
  3. Оцінка проти факту. З увімкненими COSTS план показує rows= у дужках оцінки і actual ... rows=. Розбіжність у 10 і більше разів означає, що проблема в статистиці, а не в індексі: спочатку ANALYZE orders.
  4. loops. Час і рядки у вузлі вказано на один цикл. Вузол із loops=5000 усередині Nested Loop може коштувати більше, ніж здається з першого погляду.

48 мс здаються дрібницею, доки цей запит не виконується 50 разів на секунду. Тоді це безперервний послідовний перегляд таблиці, який витісняє з кешу корисні дані для всіх інших запитів.

Індекси: складені, часткові, покривні

Складений індекс: порядок колонок

Правило для B-tree: спочатку колонки, за якими фільтруєте через рівність, потім колонка сортування. Тоді індекс одразу віддає рядки в потрібному порядку, і LIMIT 20 зупиняється на двадцятому.

sql
CREATE INDEX CONCURRENTLY orders_tenant_created_idx
  ON orders (tenant_id, created_at DESC);
text
 Limit (actual time=0.701..5.387 rows=20.00 loops=1)
   Buffers: shared hit=142 read=401
   ->  Index Scan using orders_tenant_created_idx on orders (actual time=0.701..5.375 rows=20.00 loops=1)
         Index Cond: (tenant_id = 42)
         Filter: (status = 'open'::text)
         Rows Removed by Filter: 518
         Index Searches: 1
 Execution Time: 5.424 ms

Сортування зникло, буферів стало 543 замість 18 768. Але Filter: status лишився: індекс знає тенанта, а статус перевіряється вже в таблиці, тому 518 рядків прочитано даремно.

Частковий індекс: лише гарячі рядки

Якщо більшість читань стосуються відкритих замовлень (у тестовій базі їх близько 5%), індекс можна обмежити цими рядками:

sql
CREATE INDEX CONCURRENTLY orders_open_tenant_created_idx
  ON orders (tenant_id, created_at DESC)
  WHERE status = 'open';
text
 Limit (actual time=0.029..0.092 rows=20.00 loops=1)
   Buffers: shared hit=23
   ->  Index Scan using orders_open_tenant_created_idx on orders (actual time=0.028..0.086 rows=20.00 loops=1)
         Index Cond: (tenant_id = 42)
 Execution Time: 0.117 ms

23 буфери і 0,1 мс. Розмір цього індексу — 3 МБ проти 60 МБ у повного складеного, тож він завжди в пам'яті. Умова одна: частковий індекс працює, лише коли планувальник може довести, що WHERE запиту з нього випливає. status = 'open' у запиті — так, status = $1 з параметром у загальному плані — не завжди.

Той самий список замовлень, різні індексибуфери на запит, логарифмічна шкала1101001 00010 000100 000Без індексуSeq Scan18 76848,1 мсСкладений(tenant_id, created_at)5435,4 мсЧастковийWHERE status = 'open'230,12 мсПокривний*INCLUDE, Index Only Scan70,08 мс* інший запит: 50 замовлень одного клієнта. PostgreSQL 18.6, 2 млн рядків.

Покривний індекс: Index Only Scan

Коли запит читає кілька вузьких колонок за ключем, їх можна покласти в індекс через INCLUDE. Тоді PostgreSQL не ходить у таблицю взагалі:

sql
CREATE INDEX CONCURRENTLY orders_customer_created_idx
  ON orders (customer_id, created_at DESC)
  INCLUDE (total_cents, status);

EXPLAIN (ANALYZE, COSTS OFF)
SELECT created_at, total_cents, status
FROM orders
WHERE customer_id = 1234
ORDER BY created_at DESC
LIMIT 50;
text
 Limit (actual time=0.057..0.064 rows=31.00 loops=1)
   Buffers: shared hit=7
   ->  Index Only Scan using orders_customer_created_idx on orders (actual time=0.056..0.059 rows=31.00 loops=1)
         Index Cond: (customer_id = 1234)
         Heap Fetches: 0
 Execution Time: 0.080 ms

Heap Fetches: 0 означає, що жодної сторінки таблиці не прочитано. Це працює, доки VACUUM встигає за змінами в таблиці: Index Only Scan покладається на карту видимості, і після масових оновлень без VACUUM Heap Fetches зростає. Ціна теж є: документація CREATE INDEX попереджає, що неключові колонки дублюють дані, збільшують індекс, а дедуплікація B-tree для таких індексів не застосовується.

Тип індексу

  • Складений B-treeспершу колонки з рівністю, потім сортування
  • Частковий (WHERE)лише «гарячі» рядки, у рази менший
  • Покривний (INCLUDE)Index Only Scan без звернення до таблиці

Коли допомагає

  • Фільтр + ORDER BY + LIMITсписки замовлень, стрічки, кабінети
  • Більшість читань — один статусopen, pending, active
  • Вузький SELECT по ключукілька колонок, часті читання

Ціна

  • Порядок колонокнеправильний — і сортування повертається
  • Предикат має збігатисяінакше планувальник його не візьме
  • Більший розмірбез дедуплікації B-tree
Який індекс під який запит

Кожен індекс сповільнює записи і займає пам'ять. Раз на квартал перевіряйте, що ніколи не використовувалось:

sql
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

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

Запити, які ховаються від індексу

Індекс є, а план усе одно сканує таблицю. Найчастіші причини:

  • Функція над колонкою. WHERE created_at::date = '2026-10-01' не може використати індекс по created_at. На тестовій базі це 47 мс і 7 845 буферів. Той самий фільтр діапазоном created_at >= '2026-10-01' AND created_at < '2026-10-02' — 0,14 мс і 20 буферів. Для timestamptz приведення до date ще й залежить від часового поясу сесії, тож індекс за виразом на ньому не створити.
  • lower(email) = $1 без індексу за виразом (lower(email)) або без типу citext.
  • LIKE '%foo%' з відсотком на початку. B-tree не допоможе, потрібен pg_trgm з GIN-індексом.
  • Невідповідність типів. Наприклад, порівняння bigint-колонки з numeric-параметром змушує приводити колонку, а не параметр.
  • NOT IN з підзапитом. До PostgreSQL 19 це класична пастка; у 19 планувальник перетворює його на anti join, якщо колонки NOT NULL. Деталі — у гайді з оновлення на PostgreSQL 19.
  • Застаріла статистика. Після масового імпорту чи видалення запустіть ANALYZE для таблиці, не чекаючи autovacuum.

Коли планувальник помиляється в оцінках

Буває, що індекс правильний, запит чистий, а план усе одно поганий. Тоді порівнюйте оцінку з фактом у кожному вузлі: rows=12 в оцінці і actual rows=48000 — це не проблема індексу, це планувальник, який не знає ваших даних.

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

Друга — пов'язані колонки. За замовчуванням PostgreSQL вважає умови незалежними і перемножує їхню вибірковість. Якщо city = 'Kyiv' і country = 'UA' завжди трапляються разом, планувальник недооцінить кількість рядків у рази і може обрати Nested Loop там, де потрібен Hash Join. Для таких випадків є розширена статистика:

sql
CREATE STATISTICS customers_city_country (dependencies, ndistinct)
  ON city, country FROM customers;
ANALYZE customers;

Третя — нерівномірний розподіл. Коли один тенант має мільйон рядків, а решта — по сотні, стандартної гістограми може не вистачити. ALTER TABLE orders ALTER COLUMN tenant_id SET STATISTICS 1000 збільшує вибірку для однієї колонки, не чіпаючи глобальний default_statistics_target.

Після кожної такої зміни повторіть EXPLAIN ANALYZE і переконайтеся, що оцінки наблизилися до факту. Підказки планувальнику в стилі hints PostgreSQL не підтримує з коробки, і це на краще: виправлена статистика лікує всі запити до таблиці, а не один.

N+1 у Node.js і ORM

Найпоширеніший «повільний запит» у Node.js-бекенді — це не один запит, а цикл:

ts
import pg from "pg";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 5 });

type Order = { id: string; total_cents: number };
type Item = { order_id: string; sku: string; qty: number };

async function nPlusOne(tenantId: number) {
  const { rows: orders } = await pool.query<Order>(
    "select id, total_cents from orders where tenant_id = $1 order by created_at desc, id desc limit 50",
    [tenantId],
  );
  for (const order of orders) {
    const { rows } = await pool.query<Item>("select order_id, sku, qty from order_items where order_id = $1", [order.id]);
    Object.assign(order, { items: rows });
  }
  return orders;
}

async function batched(tenantId: number) {
  const { rows: orders } = await pool.query<Order>(
    "select id, total_cents from orders where tenant_id = $1 order by created_at desc, id desc limit 50",
    [tenantId],
  );
  const { rows: items } = await pool.query<Item>(
    "select order_id, sku, qty from order_items where order_id = any($1::bigint[])",
    [orders.map((o) => o.id)],
  );
  const byOrder = Map.groupBy(items, (i) => i.order_id);
  return orders.map((o) => ({ ...o, items: byOrder.get(o.id) ?? [] }));
}

Я заміряв обидві функції на 50 замовленнях, рахуючи виклики pool.query:

text
n+1      orders=50 queries=51 time=22.5 ms
batched  orders=50 queries=2 time=1.3 ms

Тут база стоїть у Docker на тому ж ноутбуці, тож мережевої затримки майже немає. У продакшені застосунок і база стоять на різних машинах, і кожен запит додає мережеву затримку туди й назад, і 51 послідовний виклик множить її на 51. node-postgres сам перетворює JavaScript-масив на масив PostgreSQL, тому any($1::bigint[]) — найпростіший спосіб батчингу без складання SQL рядками.

N+1: запит у цикліNode.jsPostgreSQLсписок замовлень51 запит · 22,5 мс… ще 46 запитівБатчинг: any($1::bigint[])Node.jsPostgreSQLсписок замовленьусі позиції разом2 запити · 1,3 мсЛокально, без мережі. У продакшені кожен запит ще й додає мережеву затримку.

В ORM N+1 зазвичай ховається в лінивих зв'язках або в await всередині map. Prisma (include), Drizzle (relational queries), TypeORM (relations) завантажують зв'язок одним або кількома запитами на рівень, а не на кожен рядок, якщо попросити їх про це явно. Перевіряти слід не код, а лог запитів: увімкніть логування SQL в ORM локально і порахуйте, скільки запитів робить один виклик ендпоінта.

Ще одна пастка поруч із N+1: зовнішні ключі в PostgreSQL не створюють індекс автоматично. У тестовій базі order_items.order_id має REFERENCES orders(id), але без окремого CREATE INDEX кожен запит позицій замовлення переглядав би 3 млн рядків.

Саме з цих двох інструментів я почав у SimplyDepo, американській платформі управління замовленнями: профілював найповільніші ендпоінти, а точкові індекси і переписані N+1-запити знизили P95 на списках замовлень. Важкі речі, як-от генерацію великих PDF-звітів, я виніс у фонові задачі; як це влаштувати, описав у гайді з черг задач у Node.js.

Пагінація, яка не деградує

OFFSET здається безневинним, доки користувач або краулер не дійде до 450-ї сторінки:

sql
EXPLAIN (ANALYZE, COSTS OFF)
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
ORDER BY created_at DESC, id DESC
OFFSET 9000 LIMIT 20;
text
 Limit (actual time=18.469..18.477 rows=20.00 loops=1)
   Buffers: shared hit=6835 read=1026
   ->  Sort (actual time=17.589..18.211 rows=9020.00 loops=1)
         Sort Key: created_at DESC, id DESC
         Sort Method: quicksort  Memory: 1090kB
         ->  Bitmap Heap Scan on orders (actual time=2.358..15.764 rows=10035.00 loops=1)
               Recheck Cond: (tenant_id = 42)
 Execution Time: 18.612 ms

Щоб віддати 20 рядків, база прочитала всі 10 035 замовлень тенанта і відсортувала 9 020. Чим глибша сторінка, тим дорожче. Keyset-пагінація передає значення останнього рядка попередньої сторінки, а id у ключі робить порядок однозначним, коли created_at збігається:

sql
CREATE INDEX CONCURRENTLY orders_tenant_created_id_idx
  ON orders (tenant_id, created_at DESC, id DESC);

EXPLAIN (ANALYZE, COSTS OFF)
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
  AND (created_at, id) < ('2025-01-16 23:35:08.364315+00', 1911870)
ORDER BY created_at DESC, id DESC
LIMIT 20;
text
 Limit (actual time=0.032..0.155 rows=20.00 loops=1)
   Buffers: shared hit=23
   ->  Index Scan using orders_tenant_created_id_idx on orders (actual time=0.032..0.150 rows=20.00 loops=1)
         Index Cond: ((tenant_id = 42) AND (ROW(created_at, id) < ROW('2025-01-16 23:35:08.364315+00'::timestamp with time zone, 1911870)))
 Execution Time: 0.202 ms

23 буфери — стільки ж, скільки на першій сторінці. Порівняння рядків (created_at, id) < (...) працює з індексом, лише якщо напрямок сортування обох колонок однаковий. На API курсор зручно віддавати як непрозорий рядок (base64 від пари значень), щоб клієнт не залежав від схеми; як оформити це в контракті, я описав у правилах дизайну REST API.

OFFSET 9000 LIMIT 20Keyset: (created_at, id) < курсор9 000рядків відкинутоіз 10 035 прочитаних20 рядківкурсоріндекс одразуведе сюди20 рядків18,6 мс · 7 861 буфер0,2 мс · 23 буфериЦіна OFFSET росте з глибиною сторінки, ціна keyset — ні.

Keyset не дає перейти одразу на сторінку 37, і для адмінських таблиць з номерами сторінок OFFSET інколи лишається. Тоді обмежте максимальну глибину і не рахуйте count(*) по всій таблиці на кожен запит.

Пул з'єднань: Node.js, serverless і PgBouncer

Кожне з'єднання PostgreSQL — окремий процес на сервері, а max_connections за замовчуванням дорівнює 100. За документацією node-postgres, pg.Pool за замовчуванням тримає до 10 клієнтів, закриває простійних через 10 секунд, а connectionTimeoutMillis дорівнює 0, тобто запит чекатиме вільного з'єднання без обмеження. Останнє варто змінити: краще швидка помилка 503, ніж запити, що висять хвилину.

Рахуйте суму, а не налаштування одного процесу: 8 подів по max: 10 — це 80 з'єднань, і після деплою, коли старі й нові поди працюють одночасно, — 160. У serverless кожен екземпляр функції має свій пул, тому max тримають на рівні 1–5, а перед базою ставлять пулер: PgBouncer, Supavisor у Supabase або пулер провайдера.

У режимі transaction PgBouncer видає серверне з'єднання на одну транзакцію, тому сесійні речі ламаються: SET без LOCAL, advisory-блокування на рівні сесії, LISTEN. Протокольні prepared statements PgBouncer підтримує з версії 1.21 через параметр max_prepared_statements, а SQL-команди PREPARE/EXECUTE він не відстежує. Налаштування на кшталт statement_timeout надійніше задати на ролі (ALTER ROLE api SET statement_timeout = '5s'), ніж командою SET після підключення.

Безпечне створення індексу в продакшені

Звичайний CREATE INDEX блокує записи в таблицю на весь час побудови. На таблиці з мільйонами рядків це хвилини, протягом яких усі INSERT і UPDATE стоять у черзі. CREATE INDEX CONCURRENTLY будує індекс без блокування записів, але має три особливості:

  1. Не працює в транзакції. Більшість інструментів міграцій загортають файл у транзакцію, і ви отримаєте ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Таку міграцію треба позначити як нетранзакційну (у кожного інструмента свій прапорець) і тримати один оператор на файл.
  2. Помилка залишає невалідний індекс. Він не використовується для читання, але сповільнює записи. Я навмисно створив унікальний індекс на колонці з дублікатами:
text
ERROR:  could not create unique index "orders_customer_uniq"
DETAIL:  Key (customer_id)=(21943) is duplicated.
sql
SELECT indexrelid::regclass AS index, indrelid::regclass AS table
FROM pg_index
WHERE NOT indisvalid;
text
        index         | table
----------------------+--------
 orders_customer_uniq | orders

Документація радить видалити такий індекс і створити знову, або перебудувати через REINDEX INDEX CONCURRENTLY.

  1. Він чекає на довгі транзакції. CONCURRENTLY очікує завершення транзакцій, які бачать таблицю. Транзакція, забута відкритою в psql або в аналітичному скрипті, затримає побудову на години. Перед стартом перевірте в pg_stat_activity, чи немає транзакцій зі старим xact_start.

Для решти змін схеми ставте SET lock_timeout = '5s': якщо міграція не отримала блокування за 5 секунд, краще впасти і повторити, ніж вишикувати за собою чергу з усіх запитів до таблиці. Додавання NOT NULL-колонки, видалення колонок і зміну типів робіть у кілька релізів: спочатку код перестає залежати від старого, потім змінюється схема.

Чекліст перед оптимізацією запиту

  1. Крок 1
    Знайтиpg_stat_statements, логи, таймінги в застосунку
  2. Крок 2
    Прочитати планEXPLAIN (ANALYZE, BUFFERS) на реальних параметрах
  3. Крок 3
    Змінити однеіндекс, переписаний запит або батчинг
  4. Крок 4
    Викотити безпечноCONCURRENTLY, lock_timeout, перевірка indisvalid
  5. Крок 5
    Перевірититой самий запит у pg_stat_statements і P95 ендпоінта
Цикл роботи з повільним запитом
Перевірка Як Тривожний сигнал
Що забирає час бази pg_stat_statements за total_exec_time один запит понад 20% сумарного часу
N+1 calls у pg_stat_statements, лог SQL в ORM запит з = $1 викликається в десятки разів частіше за батьківський
План EXPLAIN (ANALYZE, BUFFERS) на реальних параметрах Seq Scan на великій таблиці, тисячі Rows Removed by Filter
Статистика оцінка rows проти actual rows розбіжність у 10 і більше разів
Індекс під запит рівність → сортування, WHERE для часткового Sort перед Limit
Пагінація пошук OFFSET у коді OFFSET із довільної глибини
Пул max × кількість інстансів проти max_connections сума перевищує 80% ліміту
Таймаути statement_timeout, connectionTimeoutMillis 0 або не задано
Міграції CONCURRENTLY, lock_timeout, indisvalid CREATE INDEX без CONCURRENTLY на великій таблиці
Непотрібні індекси pg_stat_user_indexes, idx_scan = 0 гігабайти індексів, яких ніхто не читає

З чого почати цього тижня

Оптимізація запитів — це цикл, а не талант: знайти запит, прочитати план, змінити одну річ, перевірити той самий запит знову. Почніть з pg_stat_statements, візьміть три запити зверху і пройдіть із кожним цей цикл. Зазвичай одного правильного індексу, одного прибраного N+1 і заміни OFFSET на keyset вистачає, щоб найповільніший ендпоінт перестав бути проблемою.

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

І ще одне: повільна база рідко є приводом переписувати продукт. Індекси й прибрані N+1 — це рефакторинг, після якого застосунок працює далі; коли ж переписування справді виправдане, я пояснив у статті переписати чи рефакторити застосунок.

Якщо ваш застосунок на Node.js і PostgreSQL помітно сповільнився і незрозуміло, з чого почати, я можу розібрати найважчі ендпоінти разом з вами — від профілювання до безпечного викочування індексів. Детальніше про те, як я працюю з такими проєктами, — на сторінці розробки на Next.js і Node.js.

Було корисно?

– вподобань · – переглядів

LinkedInXTelegramFacebook
Потрібно таке для вашого продукту?Розробка на Next.js і Node.js — від бази даних до інтерфейсуДетальніше →

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

Як знайти найповільніші запити в PostgreSQL?

Увімкніть розширення pg_stat_statements (через shared_preload_libraries і перезапуск сервера) і відсортуйте запити за total_exec_time. Так ви побачите запити, які сумарно забирають найбільше часу бази, а не лише поодинокі повільні. Окремі повільні виконання ловить log_min_duration_statement, а плани таких запитів — модуль auto_explain.

Чим EXPLAIN відрізняється від EXPLAIN ANALYZE?

EXPLAIN показує план і оцінки планувальника, не виконуючи запит. EXPLAIN ANALYZE справді виконує запит і додає фактичний час, кількість рядків і цикли. Починаючи з PostgreSQL 18, ANALYZE автоматично показує і BUFFERS — скільки сторінок прочитано з кешу і з диска. Для INSERT, UPDATE і DELETE запускайте ANALYZE всередині транзакції з ROLLBACK.

Як створити індекс у продакшені без блокування таблиці?

Використовуйте CREATE INDEX CONCURRENTLY. Він не блокує записи, але працює довше, не може виконуватися всередині транзакції і в разі помилки залишає невалідний індекс. Такий індекс треба знайти через pg_index.indisvalid, видалити і створити знову.

Чому OFFSET-пагінація повільна на великих таблицях?

Щоб віддати сторінку з OFFSET 9000, база мусить знайти, відсортувати і відкинути 9000 рядків перед потрібними двадцятьма. Keyset-пагінація передає значення останнього рядка (наприклад, created_at і id) і читає лише потрібну сторінку з індексу, тому її вартість не росте з глибиною.

Скільки з'єднань має бути в пулі node-postgres?

За замовчуванням pg.Pool відкриває до 10 з'єднань. Важлива не цифра для одного процесу, а сума: кількість інстансів, помножена на max, має вкладатися в max_connections бази із запасом. Для serverless тримайте max на рівні 1–5 і ставте перед базою пулер, наприклад PgBouncer у режимі transaction.