Что может пойти не так с очередью на postgres

Очереди задач на БД — вещь объективно удобная. Тут и транзакционность, и возможность сделать очередь с приоритетами, и возможность настроить кастомные политики ретраев. И что важно — нет необходимости тянуть еще одну зависимость в виде внешней очереди

---

С точки зрения БД это write-intensive таблица, в которую летят примерно такие запросы:

1. Поллинг запланированных задач

select * from tasks
where status = 'scheduled'
and scheduled_ts < now()
order by scheduled_ts
limit 100

2. Обновления задач по id (смена статусов, кол-ва ретраев и т.д.)

update tasks
set ...
where id = 123

Конкретные реализации могут использовать for update skip locked либо heartbeat pattern, но сейчас это не суть вопроса

Энивей, логично сделать как минимум два индекса
1. Для первого запроса — на (scheduled_ts) where status = 'scheduled'
2. Для второго запроса — pk на (id)

---

Далее представим, что в нашей базе начинается долгая транзакция

Из поста про bloat мы знаем, что долгие транзакции держат горизонт базы и не позволяют autovacuum-у чистить dead tuples, которые "старше" этого горизонта

Посмотрим, как это заафектит наш индекс на (scheduled_ts). На самом нижнем уровне индекса есть leaf pages, которые между собой образуют двусвязный список

[row1, row2, row3] <-> [row4, row5, row6] <-> [...] 

В ходе выполнения первого запроса для выборки задач у нас есть сортировка по scheduled_ts, поэтому мы
1. Спускаемся по btree в самую левую часть
2. Идем в отсортированном порядке по двусвязному списку

Затем мы проставляем этим задачам другой статус, например, 'running'. Эти апдейты создают новые версии строк, а старые помечают удаленными

Но если autovacuum не работает, то удаленные версии строк не будут чиститься, и в левой части индекса начнут копиться dead tuples

Изначальное состояние индекса по scheduled_ts:

[row1, row2, row3] <-> [row4, row5, row6] <-> [...] 

Сделали выборку, апдейтнули статусы

[dead, dead, dead] <-> [row4, row5, row6] <-> [...] 

Сделали выборку, апдейтнули статусы

[dead, dead, dead] <-> [dead, dead, dead] <-> [...] 

И так далее

Если подождать часок, то может образоваться "полоска" из нескольких сотен тысяч dead tuples. И чтобы сделать очередную выборку, нам сначала нужно будет вручную пробежаться по этой "полоске" из тысяч dead tuples и только после этого взять живые записи

Это может привести к тому, что скорость выборки деградирует на несколько порядков, например, с 1мс до 200мс

А если таких выборок в секунду происходит штук 50, то чисто на выборки нам придется тратить 50 * 0.2 = 10 cpu cores

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

---

Мораль сей басни такова: сочетание долгих транзакций и queue-like ворклоада на постгресе — вещь крайне опасная

Но иногда долгих транзакций просто не избежать. Один из самых частых примеров: создание индекса на большой таблице. Да, даже create index concurrently под собой удерживает долгую транзакцию

Суммаризируя, рекомендации здесь весьма банальные:
- Стараться избегать долгих транзакций
- Если все-таки надо, то снижать нагрузку на время обслуживания
- Не допускать, чтобы таблицы сильно разрастались. Этого можно добиться с помощью партицирования, шардирования и введения retention-а (например, хранить данные только за последнюю неделю) — это позволит выполнять создание индекса на таблице сильно быстрее