Очередь на SKIP LOCKED и воркер, умерший посреди транзакции

Зависит от того, как он умер. Случая два, и со стороны очереди они выглядят совсем по-разному.

Убит, соединение закрыто. Серверный процесс ждал следующей команды, видит, что соединение пропало, и откатывает открытую транзакцию. Блокировка строки уходит вместе с ней. status остаётся 'queued', и следующий опрос любого воркера берёт строку. Одна задача, два запуска.

Завис, соединение живо. Сеанс стоит в состоянии idle in transaction, строка заперта, и SKIP LOCKED у остальных воркеров обходит её на каждом опросе, пока жив сеанс. Итог - одна задача застряла и не видна, а при N зависших воркерах таких задач N. Для этого есть idle_in_transaction_session_timeout, по умолчанию он выключен.

Так что считайте, что задача может выполниться больше одного раза. Её внешний эффект должен переживать повтор.

С kill -9 всё, как ты сказал. Соединение рвётся, откат, на следующем опросе строку забирает Go-воркер, задача отрабатывает дважды.

С kill -STOP всё гаже. Строка заперта, сеанс висит в pg_stat_activity как idle in transaction. С idle_in_transaction_session_timeout сервер прибивает сеанс, Go-воркер берёт строку и выполняет задачу. Потом kill -CONT. Остановленный воркер просыпается, доделывает работу, отправляет свой UPDATE и только тогда получает ошибку, что соединения больше нет. Внешний эффект к тому моменту уже случился дважды.

Таймаут остаётся, строку он освобождает. Старого воркера он не останавливает, так что следующим шагом у каждой задачи будет ключ идемпотентности.

Когда в таблице накопятся выполненные задачи, запрос опроса начнёт тормозить, и по его тексту не понять почему. Если индекс есть только по id, типичный план идёт по нему с ORDER BY id LIMIT 1 и отбрасывает строки со status = 'done' одну за другой, и чем длиннее история, тем длиннее этот путь. Частичный индекс WHERE status = 'queued' его укорачивает, если в запросе стоит тот же литерал.

Остаётся вопрос, на который у меня нет хорошего ответа. Выполненные строки вы удаляете в той же транзакции, переносите в архивную таблицу или чистите пачками потом?

Со стороны Go дайте транзакции воркера дедлайн. Когда контекст из BeginTx истекает, database/sql сам откатывает транзакцию, даже если run всё ещё висит.

ctx, cancel := context.WithTimeout(ctx, 30*time.Second)
defer cancel()
tx, err := db.BeginTx(ctx, nil)
if err != nil {
	return err
}
defer tx.Rollback()
// poll, run(ctx, payload), mark done
return tx.Commit()

От kill -STOP не спасает. Остановленный процесс ничего не выполняет, дедлайны тоже.

Вот этот случай меня беспокоит больше всего. Старый воркер не может узнать, что его сеанса нет, пока снова не обратится к базе, а к тому моменту работа уже сделана. Пусть внешняя запись несёт id задачи, а принимающая сторона отказывает второй.

Удаляйте. Забирайте задачу удалением строки, и проблемы с историей просто не возникнет.

DELETE FROM jobs
WHERE id = (
  SELECT id FROM jobs
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;

Работу делайте в той же транзакции и фиксируйте в конце. В таблице лежат только ожидающие задачи, так что ни частичного индекса, ни архивной задачи. Да, запись о том, что выполнялось, пропадает. Если кому-то она нужна, пишите её в отдельную таблицу журнала и держите подальше от пути опроса.