Кейс OLTP на базе 735 МБ: восемь находок по одной — p95 с 224 до 13 мс дали два индекса, остальные семь шагов хвост не улучшили

На базе 735 МБ при 60 транзакциях в секунду p95 стоит на 224 мс, а p99 — на 1,0 с. В базу заложены восемь находок, из тех, что находит трёхдневный аудит: простаивающая транзакция, checkpoint раз в две минуты, умолчания автовакуума, внешние ключи без индексов, random_page_cost = 4, возможный generic-план, отсутствие пула и раздутая горячая таблица. Каждая исправлена отдельно, после каждой — по три прогона pgbench --rate с одним темпом и перцентили по логу транзакций, как в статье про честное нагрузочное тестирование. Сдвинул хвост только один шаг: p95 упал до 13 мс после двух индексов. Остальные семь не улучшили ничего, а один (пул) ухудшил p99.

Масштаб здесь не промышленный: 735 МБ, а не терабайты, и все числа относятся к этой базе. Машина общая: во время прогонов на ней шли чужие контейнеры (load average хоста от 1,6 до 10,6), поэтому разброс между повторами включает посторонний шум, и вывод «шаг не помог» означает «эффект меньше этого разброса».

Стенд и ограничения

Один контейнер, ограничения те же, что у читателя, который повторит опыт: PostgreSQL, pgbench, PgBouncer и pg_repack внутри одного postgres:18.

docker run -d --name dba-oltp-db --memory 2g --cpus 4 -e POSTGRES_PASSWORD=pg postgres:18 \
  -c shared_buffers=256MB -c max_connections=300 -c jit=off -c track_io_timing=on -c log_checkpoints=on \
  -c max_parallel_workers_per_gather=0 -c shared_preload_libraries=pg_stat_statements
docker exec dba-oltp-db sh -c 'apt-get update -qq; apt-get install -y -qq pgbouncer postgresql-18-repack'
docker exec dba-oltp-db psql -U postgres -c "ALTER SYSTEM SET checkpoint_timeout = '2min'" -c "select pg_reload_conf()"

Параллельные запросы выключены: в контейнере по умолчанию 64 МБ /dev/shm, и сотня клиентов с параллельными воркерами падала с could not resize shared memory segment. База 735 МБ больше shared_buffers, но целиком помещается в кэш ядра внутри лимита 2 ГБ, поэтому чтения идут из кэша ядра, и всё, что зависит от скорости диска, здесь не проверено. checkpoint_timeout задан через ALTER SYSTEM, а не флагом -c: параметр командной строки сильнее postgresql.auto.conf, и в первом заходе ALTER SYSTEM молча не сработал: select name, setting, source from pg_settings показал checkpoint_timeout|120|command line при max_wal_size|4096|configuration file. Этот заход выброшен, все числа ниже из чистого повтора.

Генератор данных

CREATE EXTENSION pgstattuple; CREATE EXTENSION pg_stat_statements; CREATE EXTENSION pg_repack;

CREATE TABLE customers (id int PRIMARY KEY, region int NOT NULL, name text NOT NULL);
INSERT INTO customers SELECT g, g % 50, 'customer ' || g FROM generate_series(1, 200000) g;

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers (id),
  status text NOT NULL,
  created_at timestamptz NOT NULL,
  total numeric(10,2) NOT NULL,
  note text
);
INSERT INTO orders (customer_id, status, created_at, total, note)
SELECT CASE WHEN g % 10 = 0 THEN 1 ELSE 2 + (random() * 199997)::int END,
       CASE WHEN random() < 0.97 THEN 'paid' ELSE 'new' END,
       timestamptz '2026-01-01' + random() * 2000000 * interval '1 second',
       (random() * 500)::numeric(10,2), repeat('x', 40)
FROM generate_series(1, 2000000) g;
CREATE INDEX orders_created_idx ON orders (created_at);

CREATE TABLE order_items (
  id bigserial PRIMARY KEY,
  order_id bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  product_id int NOT NULL, qty int NOT NULL, price numeric(10,2) NOT NULL
);
INSERT INTO order_items (order_id, product_id, qty, price)
SELECT o, 1 + (random() * 199999)::int, 1 + (random() * 4)::int, (random() * 100)::numeric(10,2)
FROM generate_series(1, 2000000) o, generate_series(1, 2) k;

CREATE TABLE stock (
  product_id int PRIMARY KEY, warehouse int NOT NULL, qty int NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now(), pad text NOT NULL
) WITH (autovacuum_enabled = false);
INSERT INTO stock SELECT g, g / 1000, 1000, now(), repeat('p', 100) FROM generate_series(1, 200000) g;
CREATE INDEX stock_warehouse_idx ON stock (warehouse);
UPDATE stock SET qty = qty - 1;
UPDATE stock SET qty = qty - 1;
VACUUM stock;
ALTER TABLE stock RESET (autovacuum_enabled);

ANALYZE;
CREATE ROLE app LOGIN;
GRANT ALL ON ALL TABLES IN SCHEMA public TO app; GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO app;

Находки заложены самой схемой: у orders.customer_id и order_items.order_id нет индексов, хотя внешние ключи есть; клиент 1 владеет 10 % заказов (200 000 из 2 млн) при остальных примерно по 9, то есть перекос под generic-план; stock дважды обновлена без автовакуума и после VACUUM не сжата. Осталось в настройках: checkpoint_timeout = 2min, random_page_cost = 4, умолчания автовакуума, 100 клиентов напрямую в PostgreSQL. Простаивающую транзакцию создаёт отдельная сессия: (echo 'BEGIN; SELECT txid_current();'; sleep 7200) | psql -U postgres -h 127.0.0.1 держит горизонт xmin (причины и держатели).

Нагрузка и протокол

Шесть скриптов pgbench с весами; summary и recent — запросы по клиенту, которым нужны индексы, neworder пишет в три таблицы, cancel удаляет заказ вместе с позициями.

-- summary.sql (@8): в 20 % вызовов клиент 1
\set c random(1, 200000)
\if random(1, 100) <= 20
\set c 1
\endif
SELECT count(*), sum(i.qty * i.price) FROM orders o JOIN order_items i ON i.order_id = o.id WHERE o.customer_id = :c;
-- recent.sql (@12)
\set c random(1, 200000)
SELECT id, total, status, created_at FROM orders WHERE customer_id = :c ORDER BY created_at DESC LIMIT 10;
-- report.sql (@4): 1 % заказов по индексу created_at
\set d random(0, 1980000)
SELECT status, count(*), sum(total) FROM orders
WHERE created_at >= timestamptz '2026-01-01' + :d * interval '1 second'
  AND created_at < timestamptz '2026-01-01' + (:d + 20000) * interval '1 second' GROUP BY status;
-- neworder.sql (@40)
\set c random(1, 200000)
\set p random(1, 199999)
BEGIN;
INSERT INTO orders (customer_id, status, created_at, total, note) VALUES (:c, 'new', now(), 10, 'x') RETURNING id AS oid \gset
INSERT INTO order_items (order_id, product_id, qty, price) VALUES (:oid, :p, 1, 5), (:oid, :p + 1, 2, 5);
UPDATE stock SET qty = qty - 1, updated_at = now() WHERE product_id = :p;
COMMIT;
-- cancel.sql (@1)
\set o random(1, 2000000)
DELETE FROM orders WHERE id = :o;
-- stockread.sql (@35)
\set w random(0, 199)
SELECT sum(qty) FROM stock WHERE warehouse = :w;

Запуск: 100 клиентов, подготовленные запросы, открытая модель на 60 tx/s; на каждый шаг — 60 секунд прогрева (результат отбрасывается) и три прогона по 120 секунд. Число checkpoint за прогон — разность num_timed + num_requested в pg_stat_checkpointer, перцентили считает stats.py (третья колонка лога — микросекунды):

pgbench -h 127.0.0.1 -p $PORT -U app -n -M prepared -c 100 -j 4 -T 120 --rate=60 -l \
  -f summary.sql@8 -f recent.sql@12 -f report.sql@4 -f neworder.sql@40 -f cancel.sql@1 -f stockread.sql@35 postgres
cat pgbench_log.* | python3 -I stats.py

import sys
v = sorted(int(f[2]) for f in map(str.split, sys.stdin) if len(f) > 2 and f[2].isdigit())
p = lambda q: v[-(-len(v) * q // 100) - 1] / 1000
print(f"n={len(v)} mean={sum(v)/len(v)/1000:.1f} p50={p(50):.1f} p95={p(95):.1f} p99={p(99):.1f} max={v[-1]/1000:.1f} мс")

Темп 60 выбран по пробе: при 90 tx/s база без исправлений не успевала (66 tx/s, p99 34 с), при 60 держит заданный темп.

Восемь находок: порядок и риск

Порядок — от изменений, которые применяются перезагрузкой конфигурации и откатываются мгновенно, к тем, что требуют правки приложения или блокируют таблицу. Индексы стоят перед random_page_cost: пока их нет, стоимость случайного чтения ни на что не влияет.

Результат по шагам

Медиана трёх прогонов и (минимум–максимум); медианы TPS на всех шагах 59,6–60,6: заданный темп сервер выдерживал.

Шаг 1. Таймаут на уже открытую сессию не действует: через 45 секунд после reload (idle_in_transaction_session_timeout = 30000) запрос count(*) filter (where state = 'idle in transaction') всё ещё возвращал 1 и стал 0 только после pg_terminate_backend. За три базовых прогона у stock накопилось 9 977 мёртвых строк, которые автовакуум не мог убрать, но p99 не изменился (1023 → 1082, в разбросе): при 60 tx/s и кэше, вмещающем базу, задержанный горизонт ещё не бьёт по запросам.

Шаг 2. pg_stat_checkpointer показал один checkpoint за 120-секундный прогон на базе и ноль после (в одном прогоне шага 3 — один). p99 не улучшился, а в одном прогоне вырос (максимум 2974): этот разброс принадлежит шумной машине, а не checkpoint, которого в прогоне не было. Разбор механики — в статье про всплески ввода-вывода при checkpoint.

Шаг 3. Порог автовакуума по умолчанию для stock — 50 + 0,2 × 200 000 = 40 050 мёртвых строк, до него дело не доходило; после шага порог 4 050, и n_dead_tup у stock упал с 10 929 до 464. Механизм изменился, p99 нет (1090 → 1049). Формулы — в настройке автовакуума.

Шаг 4. Единственный выход за разброс. Запрос summary для обычного клиента:

 Hash Join  (actual rows=6.00 loops=1)
   ->  Seq Scan on order_items i (actual rows=4000000.00 loops=1)
 Execution Time: 252.479 ms          -- до
 Nested Loop (actual rows=6.00 loops=1)
   ->  Index Scan using order_items_order_id_idx on order_items i (actual rows=2.00 loops=3)
 Execution Time: 0.156 ms            -- после

Каждый такой вызов читал все 4 млн позиций. Удаление заказа делает то же самое: проверка внешнего ключа в RI_FKey_cascade_del строит запрос по столбцу дочерней таблицы.

		/* ----------
		 * The query string built is
		 *	DELETE FROM [ONLY] <fktable> WHERE $1 = fkatt1 [AND ...]
		 * The type id's for the $ parameters are those of the
		 * corresponding PK attributes.
		 * ----------
		 */

Без индекса по fkatt этот DELETE сканирует таблицу: EXPLAIN (ANALYZE) одного DELETE FROM orders в транзакции с откатом занял 0,392 мс с индексом и 109,736 мс после DROP INDEX в той же транзакции. p50 упал с 1,8 до 0,7 мс, p95 — с 236 до 12,8 мс. Откуда берутся такие ключи без индексов, показывает статья про ограничения как инструмент DBA.

Шаг 5. random_page_cost = 1.1 поменял Bitmap Heap Scan на Index Scan по orders.customer_id, но шесть обычных клиентов в пробе остались в тех же 0,09–0,35 мс (до: 0,16–0,35). На p99 это не видно: 716 → 675, диапазоны 679–773 и 667–694 пересекаются. Модель стоимости описана в статье про планировщик.

Шаг 6. Механизм generic-плана воспроизводится: после пяти EXECUTE шестой переводит prepared statement на generic (pg_prepared_statements: generic_plans | custom_plans = 1 | 5). Но на этом объёме он быстрее custom: для клиента 1 generic-план с вложенными циклами занял 449,768 мс, custom-план с хеш-соединением на шаге 4 — 940,215 мс, а force_custom_plan на шаге 6 вернул 759,874 мс. Регрессия, описанная в статье про generic-планы, требует иного распределения; здесь находка не подтвердилась, а p99 остался в разбросе (675 → 703).

Шаг 7. transaction-пул на 20 серверных соединений вместо 100 прямых (pgbouncer.ini: pool_mode = transaction, default_pool_size = 20, max_client_conn = 300, max_prepared_statements = 200). Хвост ухудшился: 703 (697–728) → 853 (775–915), p50 0,7 → 0,8 мс, p95 в разбросе. Причина не выделена. Когда пул окупается, объясняет статья про соединения и PgBouncer; на 100 клиентах и 60 tx/s он латентность не улучшил.

Шаг 8. Перед шагом bloat stock восстановлен тем же скриптом (два UPDATE без автовакуума и VACUUM), потом pg_repack -t stock:

pgstattuple('stock') до: 90 МБ, живых 32 %, свободных 66 %; после: 30 МБ, живых 95 %, свободных 1 %. Таблица сжалась втрое, p99 не изменился (853 → 852): запросы к stock идут по индексу, и stockread даже на раздутой таблице держит p99 0,8 мс. Как измерить раздутие и убрать его — в статье про bloat и pg_repack.

Что осталось от p99

Хвост после четвёртого шага задаёт один запрос. Разбивка последнего прогона по скриптам (мс):

 summary    n=551   p50=0.7  p95=919.2  p99=981.7
 neworder   n=2892  p50=2.0  p95=3.3    p99=4.0
 cancel     n=69    p50=1.9  p95=3.3    p99=39.8

У остальных трёх скриптов (recent, report, stockread) p99 не выше 17,0 мс.

summary для клиента 1 читает 200 000 заказов и 400 000 позиций: 8 % вызовов × 20 % клиента 1 = 1,6 % всех транзакций, больше 1 %, поэтому p99 равен его латентности. Индексы его не ускоряют, ему нужен предагрегат или материализованное представление, а это уже не находка аудита, а правка запроса. Так же ограничено и всё остальное: шаги 1–3 и 8 здесь ничего не показали потому, что стенд не создаёт условий, в которых они вредят (мало записи, кэш вмещает базу, темп ниже ёмкости), а не потому, что они бесполезны.

Что из этого следует

• Находка и эффект — разные вещи: из восьми только индексы под внешние ключи вывели p95 из разброса. Остальные семь показывают, что аудит находит риски, а ценность каждого определяется нагрузкой и масштабом.
• Две самые рискованные правки (пул и pg_repack) на этом объёме ничего не дали, а пул ухудшил хвост: порядок «от низкого риска к высокому» помог ничего не сломать, но не отсортировал шаги по пользе.
• Таймаут простаивающих транзакций защищает от будущих утечек: уже открытую сессию надо завершить руками.
• Параметр из командной строки контейнера не меняется через ALTER SYSTEM; проверяй source в pg_settings после каждой правки.
• Перцентиль считается по всем транзакциям сразу, поэтому хвост задаёт самый тяжёлый запрос, если его доля выше 1 %; смотри перцентили по скриптам.

Чек-лист

1. Перед каждым шагом фиксируй состояние (pg_settings.source, число сессий idle in transaction, n_dead_tup) и меняй ровно одно.
2. Меряй открытой моделью с одним темпом, три повтора на шаг, и пиши медиану с минимумом и максимумом.
3. Шаг без эффекта больше разброса записывай как «не подтверждено на этом масштабе», а не «помогло на 5 %».
4. Раскладывай p99 по скриптам: если хвост — один запрос, чини его, а не настройки. Контейнер и том убери одной командой: docker rm -f -v dba-oltp-db.