Перейти до вмісту
·20 хв читання

Оптимізація запитів PostgreSQL: Від повільних до субмілісекундних

Техніки оптимізації PostgreSQL, які я використовую на продакшн-базах. EXPLAIN ANALYZE, стратегії індексів, переписування запитів та конкретні зміни, що скоротили час запитів із секунд до мікросекунд.

postgresqldatabaseperformancebackend
Поділитися:X / TwitterLinkedIn

Минулого місяця о 2 годині ночі я отримав повідомлення у Slack: "Дашборд тайм-аутить." Я підключився по SSH до продакшн-серверу, відкрив pg_stat_activity і знайшов один запит, що сканував 14 мільйонів рядків, щоб повернути 12 результатів. Виправлення — partial index, який створюється за 30 секунд. Запит пішов із 4.2 секунд до 0.3 мілісекунд.

У цьому й суть продуктивності PostgreSQL. Проблеми майже ніколи не екзотичні. Це пропущені індекси, погана статистика, запити, що мали сенс, коли в таблиці було 10 000 рядків, але тепер їх 10 мільйонів. Виправлення зазвичай прості — коли знаєш, де шукати.

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

Мислення дебагера: не гадай, вимірюй#

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

Припини вгадувати. PostgreSQL має один із найкращих аналізаторів запитів серед усіх баз даних. Використовуй його.

EXPLAIN — Креслення#

Звичайний EXPLAIN показує, що PostgreSQL планує робити, без фактичного виконання запиту:

sql
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
   Index Cond: (customer_id = 42)

Це показує план — index scan — та оціночну вартість. Але це прогноз. PostgreSQL може повністю помилитися щодо кількості рядків. Я бачив оцінки в "1 рядок", коли фактичний результат був 50 000 рядків. Така помилка каскадом тягне за собою жахливі рішення планувальника.

Використовуй звичайний EXPLAIN, коли хочеш швидко глянути план, не виконуючи запит. Це важливо, коли аналізуєш DELETE, що змінив би дані, або запит, що працює 30 хвилин.

EXPLAIN ANALYZE — Правда#

sql
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
                                                    (actual time=0.021..0.023 rows=1 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.085 ms
 Execution Time: 0.041 ms

Тепер ти бачиш фактичний час виконання та фактичну кількість рядків. Ось де відбувається справжній дебагінг. Порівняй rows=1 (оціночна) з rows=1 (фактична) — вони збігаються, тож планувальник прийняв гарне рішення. Коли вони не збігаються — це твоя перша зачіпка.

Попередження: EXPLAIN ANALYZE насправді виконує запит. Якщо аналізуєш UPDATE або DELETE, обгорни його в транзакцію:

sql
BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

EXPLAIN (ANALYZE, BUFFERS) — Повна картина#

Ось що я фактично використовую 90% часу:

sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
  AND o.status = 'completed';
                                    QUERY PLAN
---------------------------------------------------------------------------
 Hash Join  (cost=425.00..1893.25 rows=2150 width=48)
            (actual time=2.341..15.892 rows=2347 loops=1)
   Hash Cond: (o.customer_id = c.id)
   Buffers: shared hit=312 read=45
   ->  Bitmap Heap Scan on orders o  (cost=12.50..1450.75 rows=2150 width=32)
                                     (actual time=0.512..12.105 rows=2347 loops=1)
         Recheck Cond: (created_at > '2026-01-01'::date)
         Filter: (status = 'completed')
         Rows Removed by Filter: 893
         Heap Blocks: exact=189
         Buffers: shared hit=195 read=45
         ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..11.96 rows=3043 width=0)
                                                         (actual time=0.287..0.287 rows=3240 loops=1)
               Index Cond: (created_at > '2026-01-01'::date)
               Buffers: shared hit=12
   ->  Hash  (cost=287.50..287.50 rows=10000 width=24)
             (actual time=1.753..1.753 rows=10000 loops=1)
         Buffers: shared hit=117
         ->  Seq Scan on customers c  (cost=0.00..287.50 rows=10000 width=24)
                                      (actual time=0.008..0.892 rows=10000 loops=1)
               Buffers: shared hit=117
 Planning Time: 0.215 ms
 Execution Time: 16.147 ms

Вивід BUFFERS критично важливий. shared hit=312 означає, що 312 сторінок прийшли з buffer cache (RAM). read=45 означає, що 45 сторінок довелося читати з диска. Якщо бачиш багато read відносно hit, твій shared_buffers може бути занадто малим, або таблиця завелика для кешування.

Також я використовую FORMAT JSON, коли потрібно вставити план у explain.dalibo.com для візуалізації. Деревоподібний вигляд робить складні плани набагато легшими для читання:

sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

Опції EXPLAIN, які я реально використовую#

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

sql
EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ...;
  • TIMING — тайминг для кожного вузла (увімкнено за замовчуванням з ANALYZE, але я вказую явно)
  • VERBOSE — показує списки вихідних стовпців та повні імена таблиць зі схемою

Читай плани запитів як детектив#

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

Seq Scan — Повне сканування таблиці#

Seq Scan on orders  (cost=0.00..25000.00 rows=1000000 width=128)

Послідовне сканування читає кожен рядок у таблиці. Для таблиці з мільйоном рядків це кожна сторінка на диску.

Але ось нюанс: Seq Scan — це не завжди погано. Якщо ти вибираєш 30% або більше таблиці, послідовне сканування насправді швидше за index scan, тому що послідовне вводу/виводу набагато швидше за довільне. PostgreSQL знає це. Якщо він обирає Seq Scan при наявності індексу, перевір, наскільки селективна твоя умова WHERE.

Коли це проблема: коли ти вибираєш крихітну частку рядків із великої таблиці і немає індексу.

Index Scan — Цільовий пошук#

Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=5 width=128)
  Index Cond: (customer_id = 42)

Йде до B-tree індексу, знаходить відповідні записи, потім вибирає фактичні рядки з heap таблиці. Кожна вибірка рядка — це довільна операція вводу/виводу до heap. Чудово для високоселективних запитів, але кожна вибірка з heap має свою вартість.

Index Only Scan — Святий ґрааль#

Index Only Scan using idx_orders_customer_total on orders  (cost=0.43..4.15 rows=5 width=12)
  Index Cond: (customer_id = 42)
  Heap Fetches: 0

Це найшвидший тип сканування. PostgreSQL отримує все необхідне лише з індексу, ніколи не торкаючись heap таблиці. Ти бачиш це, коли всі стовпці, які ти SELECT та WHERE, є в індексі.

Підводний камінь: Heap Fetches: 0 означає, що visibility map актуальна. Якщо таблиця має багато мертвих рядків (давно не робився vacuum), PostgreSQL все одно мусить перевіряти heap для підтвердження видимості рядків. Це одна з причин, чому autovacuum важливий для продуктивності, а не тільки для дискового простору.

Bitmap Scan — Золота середина#

Bitmap Heap Scan on orders  (cost=45.00..1250.00 rows=5000 width=128)
  Recheck Cond: (created_at > '2026-01-01')
  ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..43.75 rows=5000 width=0)
        Index Cond: (created_at > '2026-01-01')

Bitmap scans — це відповідь PostgreSQL на проблему "забагато рядків для index scan, замало для seq scan". Він будує бітову карту сторінок, що містять відповідні рядки, сортує їх за фізичним розташуванням, потім вибирає їх по порядку. Це перетворює довільний ввід/вивід у послідовний.

Часто бачиш bitmap scans, коли два або більше індекси комбінуються:

Bitmap Heap Scan on orders
  Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
  ->  BitmapAnd
        ->  Bitmap Index Scan on idx_orders_status
              Index Cond: (status = 'completed')
        ->  Bitmap Index Scan on idx_orders_created_at
              Index Cond: (created_at > '2026-01-01')

Цей BitmapAnd комбінує два окремі індекси. PostgreSQL каже: "У мене немає складеного індексу, але я можу поєднати ці два однолстовпцеві індекси." Працює, але правильний складений індекс був би швидшим.

Виявлення поганих оцінок#

Перше, що я шукаю в плані запиту — це невідповідність оцінок:

Nested Loop  (cost=0.43..50.12 rows=1 width=128)
             (actual time=0.025..4521.337 rows=48753 loops=1)

Планувальник оцінив 1 рядок. Фактичний результат — 48 753 рядки. Це помилка на п'ять порядків. Планувальник обрав Nested Loop, бо думав, що з'єднує з 1 рядком. З 48 753 рядками Hash Join був би на порядки швидшим.

Типові причини поганих оцінок:

  • Застаріла статистика: Запусти ANALYZE на таблиці
  • Корельовані стовпці: Планувальник вважає значення стовпців незалежними. Якщо status = 'shipped' та created_at > '2026-01-01' корельовані (більшість нещодавніх замовлень відправлені), планувальник недооцінює комбіновану селективність
  • Кастомні функції у WHERE: Планувальник використовує оцінку селективності за замовчуванням (зазвичай 0.5% для рівності, 33% для діапазону), коли не може проаналізувати функцію
  • Параметризовані запити з generic plans: Після 5 виконань PostgreSQL може переключитися на generic plan, що не враховує фактичне значення параметра

Коли бачиш погані оцінки, виправлення зазвичай одне з: запустити ANALYZE, створити розширену статистику, переписати запит або використати CTE як бар'єр оптимізації.

sql
-- Create extended statistics for correlated columns
CREATE STATISTICS orders_status_date (dependencies)
ON status, created_at FROM orders;
ANALYZE orders;

Стратегія індексів: правильний індекс для правильної задачі#

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

B-tree — Робоча конячка за замовчуванням#

B-tree — це тип за замовчуванням, що покриває переважну більшість випадків. Він підтримує оператори рівності та діапазону (=, <, >, <=, >=, BETWEEN, IN, IS NULL).

sql
CREATE INDEX idx_orders_created_at ON orders (created_at);

Порядок стовпців B-tree індексу надзвичайно важливий для складених індексів. Найлівіший стовпець — це первинне сортування, потім наступний, і так далі. Індекс на (a, b, c) може ефективно відповісти на:

  • WHERE a = 1
  • WHERE a = 1 AND b = 2
  • WHERE a = 1 AND b = 2 AND c = 3
  • WHERE a = 1 AND b > 5
  • WHERE a = 1 ORDER BY b

Він не може ефективно відповісти на:

  • WHERE b = 2 (пропускає перший стовпець)
  • WHERE c = 3 (пропускає перші два стовпці)
  • WHERE a = 1 AND c = 3 (розрив у середині — умова a використовується, умова c вимагає фільтрації)

Думай про це як про телефонну книгу, відсортовану за прізвищем, потім за ім'ям. Ти можеш швидко знайти всіх "Коваленко" або конкретно "Коваленко, Олександр". Але не можеш швидко знайти всіх "Олександрів" без перегляду всієї книги.

Правило: ставити умови рівності першими, потім умови діапазону, потім стовпці сортування.

sql
-- Query: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
 
-- Good: equality first, then range, then sort
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
 
-- Bad: range before equality
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);

Покриваючі індекси з INCLUDE#

PostgreSQL 11+ дозволяє додавати неключові стовпці до індексу за допомогою INCLUDE. Ці стовпці зберігаються на leaf-сторінках, але не є частиною B-tree структури. Вони дозволяють index-only scans без роздування дерева:

sql
-- We frequently run:
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
 
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);

Тепер PostgreSQL може задовольнити весь запит тільки з індексу. Без INCLUDE йому довелося б вибирати total та status з heap. Різниця на холодному кеші може бути драматичною — я бачив, як запити йшли з 50ms до 0.2ms тільки від додавання стовпців INCLUDE.

Partial Indexes — Індексуй лише те, що має значення#

Це моя улюблена фіча PostgreSQL і та, про яку більшість розробників не знає.

sql
-- Only 2% of orders are 'pending', but we query them constantly
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

Цей індекс крихітний у порівнянні з повним індексом на created_at. Він містить лише рядки, де status = 'pending'. Запити, що відповідають умові WHERE, використовують цей маленький, швидкий індекс. Запити, що не відповідають, ігнорують його.

Реальний приклад з продакшену: у мене була таблиця sessions з 50 мільйонами рядків. Лише близько 200 000 були активними (не прострочені). Повний індекс на user_id займав 1.2 ГБ. Partial index WHERE expires_at > now() займав 8 МБ. Запит пішов із 12ms до 0.1ms, тому що весь індекс вміщувався в кеш.

sql
-- Another common pattern: soft deletes
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
 
-- Unique constraint only on active records
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;

Останній надзвичайно корисний. Він дозволяє мати обмеження унікальності email на активних користувачах, водночас дозволяючи той самий email у видалених записах.

GIN-індекси — Повнотекстовий пошук та JSONB#

GIN (Generalized Inverted Index) — це відповідь, коли потрібно шукати всередині значень — масиви, JSONB-документи або вектори повнотекстового пошуку.

sql
-- JSONB containment queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 
-- Now this is fast:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
 
-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));

GIN-індекси великі та повільні для побудови, але блискавично швидкі для запитів. Вони варті того для навантажень з переважанням читання зі складними запитами на включення.

Для JSONB також є jsonb_path_ops, який менший та швидший для запитів на включення @>, але не підтримує оператори існування (?, ?|, ?&):

sql
CREATE INDEX idx_products_metadata_pathops
ON products USING GIN (metadata jsonb_path_ops);

GiST-індекси — Геометричні та діапазонні типи#

GiST (Generalized Search Tree) обробляє типи даних з перекриттям: геометричні фігури, діапазони, повнотекстовий пошук (альтернатива GIN, менший, але повільніший).

sql
-- IP range lookups
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
 
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
 
-- Temporal ranges (scheduling conflicts)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
  tstzrange(start_time, end_time)
);
 
-- Find overlapping bookings:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');

Hash-індекси — Нішеві, але корисні#

Hash-індекси корисні лише для порівнянь на рівність. Починаючи з PostgreSQL 10 вони записуються в WAL і стійкі до збоїв. Вони менші за B-tree для широких стовпців і трохи швидші для чистої рівності:

sql
-- Good for equality-only lookups on large text columns
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
 
-- This is fast:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
 
-- This CANNOT use the hash index:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';

На практиці я рідко використовую hash-індекси. B-tree чудово справляється з рівністю, а неможливість діапазонних запитів або сортування робить hash-індекси занадто негнучкими для більшості реальних випадків.

Коли індекси шкодять#

Кожен індекс має вартість:

  • Підсилення записів: Кожен INSERT оновлює кожен індекс на таблиці. Таблиця з 8 індексами означає 8 додаткових записів на кожен INSERT
  • Блокування HOT-оновлень: Heap-Only Tuple (HOT) оновлення — це важлива оптимізація, де PostgreSQL може оновити рядок без оновлення індексів, але лише якщо жоден індексований стовпець не змінився. Більше індексів = більше шансів заблокувати HOT-оновлення
  • Накладні витрати vacuum: Більше індексів означає vacuum працює довше
  • Накладні витрати планувальника: Більше індексів означає більше варіантів для оцінки планувальником

Я регулярно проводжу аудит індексів на продакшн-таблицях:

sql
-- Find unused indexes
SELECT
  schemaname, tablename, indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
    WHERE contype IN ('p', 'u')  -- exclude primary key and unique constraints
  )
ORDER BY pg_relation_size(indexrelid) DESC;

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

sql
-- Find duplicate indexes (same columns, same order)
SELECT
  a.indexrelid::regclass AS index1,
  b.indexrelid::regclass AS index2,
  a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
  AND a.indexrelid < b.indexrelid
  AND a.indkey = b.indkey;

Проблема N+1 запитів#

Кожен ORM-розробник з нею стикається рано чи пізно. Це найпоширеніша проблема продуктивності, яку я дебажу.

Як виглядає N+1#

python
# Python / SQLAlchemy — the classic trap
orders = session.query(Order).filter_by(status='pending').all()
 
for order in orders:
    # This triggers a separate query for EACH order
    print(f"Customer: {order.customer.name}")

Це генерує:

sql
-- Query 1: Get all pending orders
SELECT * FROM orders WHERE status = 'pending';
 
-- Query 2: Get customer for order 1
SELECT * FROM customers WHERE id = 101;
 
-- Query 3: Get customer for order 2
SELECT * FROM customers WHERE id = 102;
 
-- ...Query 502: Get customer for order 500
SELECT * FROM customers WHERE id = 600;

501 запит замість 1 або 2. Кожен запит швидкий окремо, може 0.5ms. Але 501 з них — це вже 250ms чистого часу бази даних, плюс затримка мережевого round-trip для кожного.

Виявлення N+1 у логах#

Найшвидший спосіб виловити N+1 запити — тимчасово увімкнути логування запитів:

sql
-- Log all queries that take more than 0ms (i.e., all queries)
SET log_min_duration_statement = 0;
 
-- Better for production: log only slow queries
ALTER SYSTEM SET log_min_duration_statement = 50;  -- 50ms threshold
SELECT pg_reload_conf();

Потім переглянь логи. N+1 безпомилковий — ти побачиш сотні ідентичних запитів з різними значеннями параметрів у швидкій послідовності.

Більш цілеспрямований підхід для розробки:

sql
-- Enable auto_explain for slow queries
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100;  -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;

Це логує повний вивід EXPLAIN ANALYZE для будь-якого запиту понад 100ms, включаючи запити всередині функцій.

Виправлення: Eager Loading або JOIN#

ORM-підхід — скажи ORM завантажувати пов'язані дані заздалегідь:

python
# SQLAlchemy — joinedload fetches customers in the same query
from sqlalchemy.orm import joinedload
 
orders = (
    session.query(Order)
    .options(joinedload(Order.customer))
    .filter_by(status='pending')
    .all()
)

Підхід сирого SQL — просто використай JOIN:

sql
SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

Один запит. Готово.

JOIN проти кількох запитів#

Тут є обґрунтована дискусія. Іноді два запити кращі за JOIN:

sql
-- Approach 1: Single JOIN (might produce duplicate data if one-to-many)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
 
-- Approach 2: Two queries (less data transfer if orders have many columns)
SELECT * FROM orders WHERE status = 'pending';
-- application collects order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);

JOIN-підхід дублює дані замовлення для кожного товару. Якщо кожне замовлення має 20 товарів, а рядок замовлення широкий — це 20-кратна передача даних. Підхід з двома запитами передає кожне замовлення рівно один раз.

Моє правило: використовуй JOIN для зв'язків один-до-одного, розглядай окремі запити для один-до-багатьох, коли сторона "один" широка. Але завжди бенчмарк — мережевий round-trip другого запиту часто коштує більше, ніж дубльовані дані.

Типові переписування запитів#

Деякі запити повільні не через відсутність індексів, а через те, як вони написані. Планувальник PostgreSQL гарний, але він не магія.

Підзапит vs JOIN vs CTE#

Ці три підходи можуть створити дуже різні плани:

sql
-- Subquery in WHERE — often fine, sometimes terrible
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);
 
-- JOIN — usually the best choice
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
 
-- CTE — materialized in PG 11 and below, optimized in PG 12+
WITH us_customers AS (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;

У PostgreSQL 12+ CTE зазвичай вбудовуються (планувальник трактує їх як підзапити), тож продуктивність ідентична. Але в PostgreSQL 11 і нижче CTE є бар'єрами оптимізації — планувальник матеріалізує їх і не може проштовхувати предикати через них. Якщо ти ще на PG 11, уникай CTE для критичних до продуктивності запитів.

EXISTS vs IN vs JOIN#

Це виникає постійно:

sql
-- EXISTS — stops at first match, great for checking existence
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM returns r WHERE r.order_id = o.id
);
 
-- IN with subquery — builds full result set first
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
 
-- JOIN — can produce duplicates if returns has multiple rows per order
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;

Для великих таблиць EXISTS часто виграє, бо він замикається. Версія з IN мусить побудувати весь список order_id повернень перед фільтрацією. Версія з JOIN може створити дублікати, вимагаючи DISTINCT, що додає крок сортування або хешування.

Мій вибір за замовчуванням: використовуй EXISTS для перевірки існування пов'язаних рядків. Це найбільш семантично ясно і зазвичай найшвидше.

Але є контрприклад. Якщо результат підзапиту малий і потрібен для кількох умов:

sql
-- If the subquery returns few rows, IN is perfectly fine
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, 4, 5);

Уникай SELECT *#

Це не просто питання стилю коду. Це має реальні наслідки для продуктивності:

sql
-- Bad: fetches all 25 columns including a TEXT column with kilobytes of data
SELECT * FROM products WHERE category = 'electronics';
 
-- Good: fetches only what you need
SELECT id, name, price FROM products WHERE category = 'electronics';

З SELECT *:

  • Не можна використати index-only scans (усі стовпці мали б бути в індексі)
  • Передається більше даних через мережу
  • Використовується більше пам'яті для сортування та хешування
  • Якщо хтось додасть стовпець BYTEA на 10MB пізніше, твої існуючі запити тихо сповільняться

Віконні функції vs Підзапити#

Віконні функції — одна з найпотужніших можливостей PostgreSQL і майже завжди перевершують корельовані підзапити:

sql
-- Slow: correlated subquery runs once per row
SELECT
  o.*,
  (SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
  (SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
 
-- Fast: window functions compute in a single pass
SELECT
  o.*,
  COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
  SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';

Ще один поширений патерн — отримання останнього рядка на групу:

sql
-- Slow: correlated subquery
SELECT * FROM notifications n
WHERE n.created_at = (
  SELECT MAX(created_at) FROM notifications
  WHERE user_id = n.user_id
);
 
-- Fast: DISTINCT ON (PostgreSQL-specific, extremely useful)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
 
-- Also fast: ROW_NUMBER window function
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM notifications
) sub
WHERE rn = 1;

DISTINCT ON — це мій стандартний вибір для цього патерну. Він лаконічний, читабельний, і PostgreSQL добре його оптимізує з правильним індексом:

sql
CREATE INDEX idx_notifications_user_latest
ON notifications (user_id, created_at DESC);

Пагінація правильно#

OFFSET — це пастка для великих наборів даних:

sql
-- Page 1: fast
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
 
-- Page 500: slow — PostgreSQL must scan and discard 9,980 rows
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
 
-- Page 5000: very slow — scans 99,980 rows to return 20
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;

Виправлення — keyset-пагінація (також відома як курсорна пагінація):

sql
-- First page
SELECT * FROM products ORDER BY id LIMIT 20;
 
-- Next page: use the last id from the previous page
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;

Це завжди швидко, тому що використовує індекс для переходу безпосередньо на потрібну позицію, незалежно від того, на якій ти "сторінці". Компроміс — ти не можеш перейти на довільну сторінку за номером, але для нескінченної прокрутки або UI "наступна сторінка" keyset-пагінація суворо краща.

Для складних порядків сортування:

sql
-- Keyset pagination with multiple sort columns
SELECT * FROM products
WHERE (price, id) > (29.99, 12345)
ORDER BY price, id
LIMIT 20;

Це використовує порівняння рядків-значень, яке PostgreSQL ефективно обробляє з складеним індексом на (price, id).

Статистика таблиць та Vacuuming#

Планувальник запитів PostgreSQL приймає рішення на основі статистики про твої дані. Погана статистика веде до поганих планів. Ось і все.

ANALYZE: Оновлення статистики#

sql
-- Analyze a single table
ANALYZE orders;
 
-- Analyze the entire database
ANALYZE;
 
-- See current statistics for a column
SELECT
  tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

Значення correlation особливо цікаве. Воно коливається від -1 до 1 і вимірює, наскільки фізичний порядок рядків відповідає логічному порядку стовпця. Кореляція, близька до 1 або -1, означає, що дані фізично відсортовані за цим стовпцем, роблячи range scans дуже ефективними (послідовний ввід/вивід). Кореляція близько 0 означає довільний ввід/вивід для діапазонних запитів.

sql
-- Increase statistics target for columns with skewed distributions
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Target статистики за замовчуванням — 100, тобто PostgreSQL семплює 300 * 100 = 30 000 рядків. Для стовпців з багатьма унікальними значеннями або перекошеними розподілами, збільшення до 500 або 1000 дає планувальнику кращі дані ціною трохи довшого часу ANALYZE.

Налаштування Autovacuum#

Autovacuum робить дві речі: відновлює простір від мертвих рядків (видалених або оновлених) та оновлює статистику. На навантажених таблицях налаштування autovacuum за замовчуванням часто занадто консервативні.

sql
-- Check autovacuum status
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Якщо бачиш таблиці з мільйонами мертвих рядків і останній vacuum був годинами тому — твій autovacuum відстає.

Для таблиць з високою зміною даних (як sessions, черги задач або метрики) я встановлюю попередні налаштування autovacuum:

sql
-- More aggressive autovacuum for high-write tables
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- vacuum at 1% dead tuples (default 20%)
  autovacuum_analyze_scale_factor = 0.005,   -- analyze at 0.5% changes (default 10%)
  autovacuum_vacuum_cost_delay = 2           -- faster vacuum (default 2ms in newer PG)
);

Роздування таблиць#

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

З часом таблиця з інтенсивними оновленнями може мати значне роздування — таблиця набагато більша на диску, ніж потребують живі дані. Це означає більше сторінок для сканування, більше вводу/виводу, більше навантаження на кеш.

sql
-- Estimate table bloat (simplified version)
SELECT
  current_database(),
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)
  ) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

Для сильно роздутих таблиць ядерний варіант — VACUUM FULL — він перезаписує всю таблицю. Але він бере ексклюзивний lock, тому не можна робити на живій продакшн-таблиці без простою. Кращий підхід — pg_repack, який робить те саме без блокування:

bash
pg_repack --table orders --no-kill-backend -d mydb

Пулінг з'єднань#

Ось щось, що дивує багатьох розробників: з'єднання PostgreSQL дорогі. Кожне з'єднання породжує новий процес (не потік), споживає приблизно 5-10 МБ пам'яті та має нетривіальний overhead fork.

max_connections за замовчуванням — 100. Якщо маєш сервер додатків з 20 worker'ами, кожен з яких відкриває 5 з'єднань, ти вже на ліміті. Додай обробник фонових задач, інструмент моніторингу та міграцію, що десь працює, і ти в біді.

Навіщо потрібен пулер з'єднань#

Без пулера, якщо твій додаток потребує обробляти 500 одночасних запитів, тобі потрібно 500 з'єднань PostgreSQL. Це 5 ГБ пам'яті тільки на overhead з'єднань, і продуктивність PostgreSQL значно деградує понад кілька сотень з'єднань через overhead управління процесами.

З PgBouncer перед PostgreSQL ці 500 з'єднань додатку маплять, можливо, на 20 фактичних з'єднань PostgreSQL. Пулер ставить запити в чергу, коли всі з'єднання до бази зайняті.

Конфігурація PgBouncer#

ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
; Pool mode
pool_mode = transaction
 
; Pool sizing
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
 
; Timeouts
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30

Transaction vs Session Mode#

  • Transaction mode (pool_mode = transaction): З'єднання повертаються в пул після кожної транзакції. Це те, що потрібно в 95% випадків. Максимальне перевикористання з'єднань.
  • Session mode (pool_mode = session): З'єднання утримуються протягом усієї клієнтської сесії. Використовуй, якщо потрібні prepared statements, команди SET, LISTEN/NOTIFY або інші можливості рівня сесії.
  • Statement mode (pool_mode = statement): З'єднання повертаються після кожного запиту. Занадто обмежувально для більшості додатків — ти навіть не можеш використовувати явні транзакції.

Підводний камінь з transaction mode: не можна використовувати prepared statements (вони є станом рівня сесії), не можна використовувати SET для сесійних змінних, і LISTEN/NOTIFY не працюватиме як очікується. Більшість ORM мають спосіб вимкнути prepared statements.

Для Node.js з драйвером pg:

javascript
const pool = new Pool({
  host: '127.0.0.1',
  port: 6432,  // PgBouncer port, not PostgreSQL port
  database: 'mydb',
  max: 20,     // matches PgBouncer default_pool_size
  statement_timeout: 30000,
  // Disable prepared statements for PgBouncer transaction mode
  prepare: false,
});

Формула розміру пулу#

Є відома формула для оптимального розміру пулу з'єднань PostgreSQL:

optimal_connections = (core_count * 2) + effective_spindle_count

Для сучасного серверу з 4 ядрами та SSD (1 ефективний шпиндель):

optimal = (4 * 2) + 1 = 9

Це виглядає контрінтуїтивно мало. Але PostgreSQL обмежений CPU на більшості сучасного обладнання (SSD достатньо швидкі, щоб ввід/вивід рідко був вузьким місцем). Більше з'єднань веде до overhead перемикання контексту, що фактично сповільнює.

На практиці я зазвичай встановлюю default_pool_size на 2-3x від цього числа для обробки пікового трафіку, розуміючи, що при максимальному навантаженні деякі запити чекатимуть у черзі PgBouncer, а не всі одночасно потрапляти в PostgreSQL.

Практичний чекліст: точні кроки для кожного повільного запиту#

Ось мій фактичний процес, коли я отримую повідомлення "цей запит повільний". Я слідую цим крокам у цьому порядку, кожного разу.

Крок 1: Отримай фактичний запит#

Не "ендпоінт повільний" — фактичний SQL. Якщо використовуєш ORM, увімкни логування запитів:

sql
-- Temporarily log all queries over 100ms
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();

Або перевір pg_stat_statements для топ-запитів за загальним часом:

sql
-- Top 10 queries by total execution time
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

pg_stat_statements — це найцінніше розширення для продуктивності PostgreSQL. Якщо ти його не використовуєш, встанови зараз:

sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Крок 2: Запусти EXPLAIN (ANALYZE, BUFFERS)#

sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <the slow query>;

Шукай:

  1. Погані оцінки рядків — фактичні рядки сильно відрізняються від оцінених
  2. Seq Scans на великих таблицях — потенційно відсутній індекс
  3. Nested Loops з багатьма рядками — має бути Hash Join або Merge Join
  4. Велике читання буферів — холодний кеш або занадто велика таблиця
  5. Операції сортування, що виливаються на диск — збільш work_mem або додай індекс для сортування

Крок 3: Перевір статистику таблиці#

sql
-- When were stats last updated?
SELECT
  relname,
  last_analyze,
  last_autoanalyze,
  n_live_tup,
  n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

Якщо last_analyze застарів або n_dead_tup високий відносно n_live_tup, запусти:

sql
ANALYZE orders;

Потім перезапусти EXPLAIN ANALYZE. Якщо план змінився — причиною була застаріла статистика.

Крок 4: Перевір існуючі індекси#

sql
-- What indexes exist on this table?
SELECT
  indexname,
  indexdef,
  idx_scan,
  idx_tup_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

Можливо, індекс існує, але не використовується. Можливо, порядок стовпців неправильний для твого запиту.

Крок 5: Створи або зміни індекси#

На основі плану запиту створи відповідний індекс. Тестуй з EXPLAIN ANALYZE до та після.

sql
-- Create the index concurrently (doesn't lock the table)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
 
-- Verify the index is used
EXPLAIN (ANALYZE, BUFFERS) <the slow query>;

Завжди використовуй CONCURRENTLY на продакшн-таблицях. Звичайний CREATE INDEX бере повний table lock, що блокує всі записи.

Крок 6: Розглянь переписування запитів#

Якщо індекс існує і статистика свіжа, але запит все ще повільний, подивись на сам запит:

  • Чи можна підзапит переписати як JOIN?
  • Чи OFFSET спричиняє проблеми? Переключись на keyset-пагінацію
  • Чи ти вибираєш більше стовпців, ніж потрібно?
  • Чи може корельований підзапит стати віконною функцією?
  • Чи CTE заважає планувальнику оптимізувати?

Крок 7: Перевір налаштування сервера#

Для специфічних патернів запитів налаштування сервера мають значення:

sql
-- work_mem: memory for sorts and hash joins (per operation, not per connection)
-- Default is 4MB, which is too low for complex queries
SET work_mem = '64MB';  -- try this and re-run EXPLAIN ANALYZE
 
-- effective_cache_size: tells planner how much disk cache to expect
-- Set to ~75% of total RAM
SHOW effective_cache_size;
 
-- random_page_cost: ratio of random I/O to sequential I/O
-- Default is 4.0, set to 1.1-1.5 for SSDs
SHOW random_page_cost;

Якщо зміна work_mem виправляє проблему з сортуванням на диск, розглянь збільшення глобально. Але будь обережний — це per-operation, не per-connection. Складний запит з 10 операціями сортування та work_mem = 256MB може використати 2.5 ГБ для одного запиту.

Крок 8: Моніторинг після виправлення#

Не просто виправ і забудь. Перевір, що виправлення тримається:

sql
-- Reset pg_stat_statements to see fresh data after your changes
SELECT pg_stat_statements_reset();
 
-- Check back in an hour/day
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round(max_exec_time::numeric, 2) AS max_time_ms,
  query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;

Бонус: Швидкі перемоги для кожної нової бази даних#

Це налаштування та практики, які я застосовую до кожної бази даних PostgreSQL, яку налаштовую, ще до будь-яких проблем з продуктивністю:

sql
-- 1. Install pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- 2. Enable slow query logging (50ms is my threshold)
ALTER SYSTEM SET log_min_duration_statement = 50;
 
-- 3. SSD-appropriate settings
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
 
-- 4. Increase work_mem from the anemic default
ALTER SYSTEM SET work_mem = '32MB';
 
-- 5. Set effective_cache_size to 75% of RAM
-- For a 16GB server:
ALTER SYSTEM SET effective_cache_size = '12GB';
 
-- 6. Set shared_buffers to 25% of RAM
-- For a 16GB server:
ALTER SYSTEM SET shared_buffers = '4GB';
 
-- 7. Improve autovacuum responsiveness
ALTER SYSTEM SET autovacuum_naptime = '10s';
 
-- Apply changes
SELECT pg_reload_conf();
-- Note: shared_buffers requires a restart

І моніторинговий запит, який я запускаю щотижня:

sql
-- Tables that need attention
SELECT
  schemaname || '.' || relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 1)
    ELSE 0
  END AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Фінальні думки#

Оптимізація запитів PostgreSQL — це не чорна магія. Це систематичний процес:

  1. Вимірюй — не гадай. EXPLAIN (ANALYZE, BUFFERS) — твій найкращий друг.
  2. Розумій план — навчися читати типи сканування, типи з'єднань та оцінки рядків.
  3. Індексуй стратегічно — правильний індекс на правильних стовпцях, у правильному порядку. Partial indexes та covering indexes — це недооцінені суперсили.
  4. Пиши кращі запити — EXISTS замість IN для перевірки існування, keyset-пагінація замість OFFSET, віконні функції замість корельованих підзапитів.
  5. Обслуговуй базу — налаштування autovacuum, оновлення статистики, пулінг з'єднань.
  6. Моніторь постійно — pg_stat_statements говорить, де твоя база витрачає час. Перевіряй регулярно.

Різниця між запитом, що займає 4 секунди, і тим, що займає 0.3 мілісекунди, рідко в обладнанні. Це майже завжди знання — знати, де шукати і що змінювати. І тепер ти знаєш, де шукати.

Схожі записи