Привет, Хабр! Решил написать небольшую техническую статью о том, как мы ускорили запрос в таблицу, до которой не доходил autovacuum из-за большой нагрузки на БД примерно в 200 раз, а разгребание outbox очереди — ещё примерно в 3 раза.

Дисклеймер

Для понимания статьи понадобится кое-какое знание PostgreSql и микро-сервисов, углубляться в это я не могу: из-за этого статья разрастётся до неприличных масштабов, но приложу краткий список ссылок на материалы, где всё объяснено подробно:

1. мощный цикл статей с примерами на понимание, того, что такое: изоляция транзакций, MVCC, VACUUM

2. что такое паттерн outbox и для чего он нужен.

Базовый сценарий

К нам на вход приходит какое-то событие со статусом заказа, который необходимо опрокинуть куда-то вовне и при этом обработать у нас. Соответственно, делаем мы это в транзакции, кладём событие в БД, после чего оно подтягивает какой-то воркер и обрабатывает.

Требования

• Статусы у заказа нужно обработать в правильном порядке: от старых к новым.

• Параллельно статусы одного заказа обрабатывать нельзя.

• Необходимо держать от 300 RPS постоянной нагрузки. Звучит как немного, но был легаси нюанс, о чём далее :)

• Естественно, разгребание очереди должно параллелиться на несколько воркеров.

Начальная реализация

• Хранятся события в таблице events в PG, причём это не отдельный инстанс, БД используется сервисом, который сам по себе очень нагруженный (то самое легаси). База не шардирована.

• Рядом стоят воркеры, которые подключаются к БД и разгребают очередь.

• Для партиционирования по заказу лок берётся в Redis-е, причём это не отдельный кластер для наших воркеров, а общесервисный кластер (легаси).

Схема таблицы events:

create table events (
    id bigserial primary key,
    order_id bigserial,
    status text not null, -- какой-то статус, неважно: 1, 2, 3
    created_at timestamptz not null default now()
);

create index events_created_at_idx
on events using btree (created_at);

Запрос get_event:

select *
from events e
order by created_at asc -- обрабатываем сперва старые
limit 1
for update -- блокируем запись в транзакции, чтобы её не взял другой воркер
skip locked -- заблокированные ранее записи нас не интересуют
;

Проблемы

1. Первая проблема — таблица events находится в уже нагруженной и большой БД. Нагрузка на БД настолько большая, что autovacuum просто не успевает дойти до нашей таблицы и очистить от мёртвых слепков (dead tuples), отчего производительность падает колоссально. Чтобы понять, как на запросе и схеме выше будет отрабатывать нечищеная таблица, можете побаловаться вот с таким:

create table events (
    id bigserial primary key,
    order_id bigserial,
    status text not null, -- какой-то статус, неважно: 1, 2, 3
    created_at timestamptz not null default now()
);

create index events_created_at_idx
on events using btree (created_at);

— Отключаем автовакуум, тем самым имитируем сценарий,
— когда БД неспособна это сделать из-за большой нагрузки.
alter table events set (autovacuum_enabled = off);

-- Добавляем 1 миллион записей в таблицу.
insert into events (order_id, status, created_at)
select id,
       (ceil(random() * 5 + 1))::text,
       now()
from generate_series(1, 1000000) as id;

-- Смотрим план исполнения.
explain analyze verbose
select *
from events e
order by created_at asc -- обрабатываем сперва старые
limit 1
for update -- блокируем запись в транзакции, чтобы её не взял другой воркер
skip locked -- заблокированные ранее записи нас не интересуют
;

-- 1 миллион записей, время исполнения запроса = ~0.180ms
-- 10 миллионов записей, время исполнения запроса = ~0.180ms
-- Почти нет разницы, что естественно, индекс отсортирован,
-- вытаскиваем всего 1 запись.

-- А теперь удаляем все данные из таблицы.
delete from events where 1=1;

-- После чего снова запускаем explain запрос.
-- 0 страниц в таблице, запрос исполняется с прогретым кешем ~5ms
-- что ~ в 25 раз медленнее того, когда в базе было 10 миллионов записей.
-- Магия MVCC PostgreSql, если интересно подробнее почему так, выше,
-- в начале, в ссылке есть статья на соответствующую тему.
-- Если повторить трюк с добавлением + удалением 10млн записей ещё раз,
-- получим следующее время запроса ~68ms, что уже примерно в 350 раз
-- более медленнее, чем до удаления записей.

-- Так можно посмотреть, что таблица на самом деле не пуста и что там находятся
-- миллионы мертволежащих слепков от удалённых ранее страниц. Причем лежат
-- они таким образом, что ascending сортировка работает максимально не оптимально.
select
	relname AS ObjectName
	,pg_stat_get_live_tuples(c.oid) AS LiveTuples
	,pg_stat_get_dead_tuples(c.oid) AS DeadTuples
from pg_class c
where relname = 'events';

2. Берем блок на order_id в загруженном Redis. Дело было давно, но, если мне не изменяет память, это занимало порядка 150мс, что никуда не годится, но там было много наслоений легаси + сам кластер постоянно стремился упереться в CPU.

3. Много холостых запросов в БД, причем таких было чуть ли не больше тех, что реально брали событие в работу. Проблема сохранялась даже с учетом внедрения дрожания между итерациями воркера.

4. Защита от wraparound failure, БД просто иногда отказывалась что-либо сохранять или менять в данных. Но тут, к сожалению, вариантов немного: либо ставить отдельный инстанс базы, либо искать самые нагруженные места и оптимизировать их в существующей базе. Тут выбрали второй подход, на это уже стояли таски, RND. Также опущу момент с администраторскими настройками вакуума и самой PG, а то статья, опасаюсь, будет слишком большой. Расскажу только, что был сделан костыль, БД ставилась на профилактику, время от времени.

Решения

1. Первым делом начали искать, можно ли как-то, не делая ничего с БД, ускорить запрос? Оказалось, что можно. Самая зависающая часть в запросе — это order by. Из-за природы хранения слепков (как и почему так — отдельная большая тема) у базы начинаются сложности именно с ascending сортировкой. Справились с проблемой довольно просто:

-- Делаем дополнительный индекс на order_id.
create index events_order_id_idx
on events using btree (order_id);

-- Меняем запрос get_event таким образом.
select *
from events e
where order_id = (
	-- Уменьшаем выборку для сортировки,
	-- то есть вернутся статусы только по
	-- конкретному заказу, а это на порядки меньше,
	-- чем эвентов в общем.
	select order_id
	from events
	order by id desc
	limit 1
)
-- Из-зв where клозы выше отсортировать нужно единицы записей.
order by created_at desc
limit 1
for update
skip locked;

-- Время исполнения 0.150ms, что даже быстрее, чем первоначальный
-- запрос, при этом ему еще и обилие мертвых слепков нипочём.

Внимательный читатель заметит тут единственный костыль, а именно order by id desc на строке 15. Дело все в том, что, если сортировать asceding , сталкиваемся с той же проблемой, что и раньше. Чтобы отдать последнюю запись, descending сортировка, нужно просто обратиться один раз в конец btree, а в случае asc придется идти по дереву, проверяя по пути все мертвые слепки.

То есть раньше работало так:
- Достаем самое старое событие.

Сейчас же:
- Достаем самое старое событие для последнего, добавленного order_id

Но поскольку (тем более с последующими оптимизациями) очередь разгребается очень быстро, при этом со значительно большим RPS чем 300, то пришлось смириться. На это (разница между временем добавлением события в очередь и фактической обработкой), естественно, необходима отдельная метрика ;)

2. Тут решение оказалось проще, чем мы думали: просто отказались от Redis. Решаем сразу 2 проблемы: походы в Redis и холостые запросы. PG и сама за нас почти бесплатно возьмет блокировку, да еще и заботливо её отпустит на commit\rollback. За нас все это сделаетpg_try_advisory_xact_lock. Запрос теперь выглядит вот так:

select *
from events e
where order_id = (
	select order_id
	from events
	-- Блочим последний идентификатор,
	-- если не получилось, берем предпоследний.
	-- Выборка order by отрабатывает за линейное время,
	-- никак существенно не влияя на скорость исполнения.
	where pg_try_advisory_xact_lock(order_id)
	order by id desc
	limit 1
)
order by created_at desc
limit 1;

-- "for update skip locked" больше не требуется,
-- другой статус для заблокированного order_id взять не получится.

Теперь схема выглядит в разы проще:

Резюмирую

Можно было бы, конечно, поставить новый инстанс базы, но на самом деле схема таблицы events больше и там много внешних ключей и связей, переезд был бы довольно болезненным. Вместо этого пошли по пути экспериментов, смотрели на решения по типу PGQ, под капотом там, по факту, — прямая работа с PG через SQL, ничего магического. Пробовали различные комбинации btree индексов, по-разному отсортированных btree индексы, hash индексы (который работал значительно медленнее в данной ситуации). Но все эксперименты не заняли много времени и отняли в разы меньше человеко-часов, чем если бы мы стали как-то глобально менять архитектуру, а это, как мне кажется, один из самых главных компромиссов в деле разработчика. Идеальных решений не бывает, но часто вещи можно значительно улучшить, причём сравнительно небольшими усилиями.