Очередь задач на Postgres: SKIP LOCKED + lease/heartbeat + backpressure (практический опыт)

[AlekseyPeregudov]

Вы изобретаете dbms_scheduler из Oracle. Не подумайте, ничего плохого в этом не вижу, даже наоборот. Если интересно - можете подсмотреть что там есть хорошего для вас.

Например - объединение заданий в классы: "эти 10 - закрытие дня", "эти 20 - отчетность" и так далее.

Выстаивание последовательности заданий: "эта задача стартует по завершению вот той" - выстаивается цепочка логически взаимосвязанных заданий и не нужно до минут высчитывать время выполнения каждой из них.

Приоритезация и окна выполнения: "с 23:00 до 6:00" должны быть выполнены эти 200 заданий, их приоритет такой-то.

Ну и так далее. Удачи.

[pel_mrk]

Спасибо! Возьму на заметку

[GerrAlt]

кажется тут advisory lock был бы более производительным вариантом по сравнению с таблицей heartbeat

[pel_mrk]

Спасибо за идею!

[ptr128]

воркер раз в N секунд “пингует” себя в БД

Если вместо (или кроме) абстрактного worker_id фиксировать в таблице pg_backend_pid(), то никакого heartbeat не требуется. Достаточно по pg_stat_activity проверять наличие pid и сравнивать backend_start с временем последней модификации задачи. Если backend_start больше времени модификации задачи или такого pid нет в pg_stat_activity, то процесс умер и блокировки нет.

[pel_mrk]

Классная идея, спасибо — да, pg_backend_pid() + pg_stat_activity могут заменить heartbeat, если воркер держит долгоживущий коннект и есть доступ к pg_stat_activity

Если же воркер использует пул и разные задачи идут через разные коннекты, то pg_backend_pid() будет меняться, и PID перестаёт быть надёжной “идентичностью воркера”

И ещё момент: “процесс/соединение живо” ≠ “воркер делает progress”. Он может зависнуть внутри (deadlock в коде, зависший внешний вызов, stuck event loop) — в этом случае backend в pg_stat_activity всё ещё может быть виден. Heartbeat решает именно это: “если давно не пинговал — считаем мёртвым”, даже если TCP/процесс жив.

[ptr128]

Если же воркер использует пул

Просто не возвращайте соединение в пул, продолжая его использовать. Излишние DISCARD ALL (по сути, последовательность из девяти команд) в данном случае только вредят.

“процесс/соединение живо” ≠ “воркер делает progress”

Зависит от настроек соединения. При правильных настройках для конкретного сценария использования соединение не долго будет висеть открытым.

Heartbeat решает именно это: “если давно не пинговал — считаем мёртвым”, даже если TCP/процесс жив.

Но создает транзакции, модифицирует БД и пишет в WAL, загружая не только сервер, но ещё и его реплики в кластере. Тогда как установив необходимый idle_session_timeout для своей сессии можно добиться того же самого, посылая команды не порождающие транзакции и модификации БД. Например, SHOW.

[Mapar]

Еще не вспомнили про FOR NO KEY UPDATE?!
Значит я первый. Почитайте, тут было пару статей в том числе и про паттерн очередей в PostgreSQL, и почему нужно использовать именно FOR NO KEY UPDATE, а не FOR UPDATE...

[pel_mrk]

Спасибо, хороший поинт. В моём случае при claim я обновляю только неключевые поля (is_occupied/worker_id/...), поэтому FOR NO KEY UPDATE действительно семантически корректнее и потенциально уменьшает лишние конфликты (в т.ч. с FOR KEY SHARE/FK). Поменяю в примере на FOR NO KEY UPDATE SKIP LOCKED