Outboxer.Storage.Postgres (Outboxer v0.1.0)

Copy Markdown View Source

Postgres storage. Safe to run on any number of nodes.

How a batch is delivered

BEGIN
SELECT pg_try_advisory_xact_lock(outbox_key, partition)   -- or give up: another node has it
SELECT ... WHERE status = 'pending' ... ORDER BY id LIMIT batch_size
-- relay delivers the batch
DELETE ...                                                  -- or mark delivered / failed
COMMIT

The advisory lock is scoped to the transaction, so it is released when the transaction ends for any reason, including the worker or the node dying. Nothing has to expire and no row is ever "claimed" with an UPDATE, which is why a delivered message costs one INSERT and one DELETE.

Transaction-scoped locks also work behind PgBouncer in transaction mode.

Things to know

  • The transaction stays open while the relay talks to the broker, so each busy partition holds one connection from the repo pool. Size the pool for sum of partitions on top of your normal load, and keep batch_size and relay timeouts modest.
  • The repo must use the default READ COMMITTED isolation level.
  • With notify: true, publishing issues pg_notify, which Postgres delivers only if and when the publishing transaction commits.