PostgreSQL Outbox Publisher: Polling, Retries, and Crash-Recovery Limits

Several publishing workers can poll the same outbox while Kafka is slow or unavailable. They need to claim different rows and delay failed sends without repeatedly selecting the same failing record.

I combined fetchAndLockBatch with explicit READY and INPROGRESS states, a persisted next-attempt time, and bounded retries. That handled concurrent polling and reported send failures; a worker that stopped mid-attempt needed a separate recovery policy.

Start with committed event intent

Assume the API has already saved a business change and an immutable event ID plus payload in one PostgreSQL transaction. The publisher's job is to get that committed intent into Kafka and keep it recoverable until publication is recorded. It must reuse the event ID on every retry.

This local transaction does not make the Kafka send atomic. Kafka's delivery semantics distinguish guarantees within Kafka from coordination with an external database. The separate outbox architecture article explains the API boundary and worker roles.

A status diagram is also a recovery contract

The publisher owned the first part of a shared six-state lifecycle:

READY (0)
  -> fetchAndLockBatch -> INPROGRESS (1)
       -> Kafka send succeeds -> SENT (2)
       -> send fails, attempts remain -> READY (0), next_attempt_at advanced
       -> attempt limit reached -> FAILED (3)

SENT (2) -> consumer CAS -> CONSUMING (5) -> CONSUMED (4)

SENT meant publication to Kafka, not completed notification delivery. Keeping that distinction made it possible to tell a publisher backlog from downstream processing. The project stored explicit numeric status codes through a Kotlin enum converter, avoiding a dependency on enum declaration order.

The selection query combined eligibility, priority, and row locking:

SELECT * FROM outbox
WHERE send_status = 0
  AND next_attempt_at <= NOW()
ORDER BY priority DESC, id ASC
LIMIT :batchSize
FOR UPDATE SKIP LOCKED;

:batchSize is an application binding. next_attempt_at prevents an ordinary poll from immediately reclaiming a delayed retry; priority selects urgent work first, with ID as a tie-breaker.

The lock and transition to INPROGRESS belong to one transaction. If the transaction ended between selection and marking, the lock would no longer protect that claim. PostgreSQL documents SKIP LOCKED as useful for queue-like access in its SELECT reference. It avoids waiting on rows another poller currently locks; it does not provide end-to-end duplicate suppression.

Separate a send attempt from its recorded outcome

The worker marked a successful publication SENT; a failed attempt returned to READY with a later attempt time or became FAILED at the limit. Calling the send API is not itself a success result. Spring Kafka's sending reference describes the future returned by KafkaTemplate.send and its completion callbacks.

The database and broker still have separate commit points. If Kafka accepts a record and the final database update fails, retry can publish the event again. Preserve the event identity across attempts so the consumer can recognize that logical work.

The shared status also creates an ordering boundary: Kafka may deliver the record before the publisher records SENT. A consumer whose CAS requires SENT must treat that case as not yet eligible, rather than as a completed duplicate. A late publisher update must likewise avoid overwriting consumer progress.

Retry without confusing delay, failure, and abandonment

The implementation used increasing retry delays with a cap and a maximum attempt count. This is preferable to immediately selecting the same failing row in a tight loop. The retry schedule must be persisted: sleeping in a worker does not survive process replacement.

When a publish fails, the owning attempt either returns the row to READY with a future eligibility time or moves it to FAILED. A FAILED row is still an unresolved obligation. It needs an alert, an explanation safe to retain, and an explicit repair or replay path. It must not disappear into routine cleanup.

The retry function in the project capped the exponent:

private fun retryDelaySeconds(base: Long, attempts: Int): Long {
    val factor = 1L shl attempts.coerceAtMost(3)
    return (base * factor).coerceAtMost(base * 10)
}

For nonnegative attempt indexes, the multiplier is 1, 2, 4, then 8. With an illustrative base of five seconds and a zero-based index, that produces 5, 10, 20, and 40 seconds. These are calculations from the function, not measured delivery times. The exponent cap means the final base * 10 bound is never reached for ordinary positive configuration values. The caller's increment convention determines which delay applies to the first failure.

The useful behavior is a persisted delay and a finite retry budget. Immediate retry would consume worker capacity during a broker outage; unlimited retry would hide records that need intervention. Priority polling has its own cost: sustained high-priority traffic can delay older low-priority work, so oldest pending age matters alongside queue length.

Extension: recover a worker that never reaches its catch block

The implemented retry path covers a reported send failure. A hard stop after persisting INPROGRESS is different: no catch block runs to restore READY. A restart alone does not change the row's status.

A possible extension is to add a lease and claim generation. A lease makes an abandoned claim eligible after a deadline. A generation identifies its current owner so an older worker cannot finalize a newer claim. The same ownership predicate must protect success, retry, and permanent-failure updates.

That extension adds timeout selection, renewal, and recovery scheduling. It still cannot stop a stale process from sending to Kafka after losing its database claim. Duplicate publication remains possible. Separating publication state from per-consumer completion records is another option if the shared lifecycle becomes difficult to coordinate, at the cost of extra storage and reconciliation.

Validate state transitions and interruption boundaries

Use the following scenarios when validating a publisher with this state model:

Use PostgreSQL and Kafka to check the actual transaction and acknowledgment boundaries. A mock proving that two methods were invoked cannot establish row-lock behavior or broker acceptance. For the optional lease design, also pause an old worker, reclaim with a new worker, and confirm that the old owner cannot finalize the new attempt.

Ordering and cleanup are part of the contract

A stable aggregate key routes related records to the same Kafka partition, but concurrent pollers can send them in a different order from business intent. If order matters, serialize publication per aggregate or carry an aggregate sequence that the consumer validates. A creation-time sort does not establish transaction commit order.

Blocked earlier events need an explicit decision too. Publishing later events may be acceptable for independent notifications but wrong for a sequence of state changes. Neither increasing the batch size nor adding more workers answers that domain question.

Delete only completed publication rows after an intentional recovery and investigation window. If consumer delivery receipts refer to those rows, do not let publisher cleanup remove the evidence consumers need. Separating publication and delivery records makes their different retention policies visible, at the cost of maintaining both.

When to own the poller

An application-owned poller fits when a local relational database already exists and retry scheduling must remain under application control. It avoids requiring a CDC platform, but the application owns claim recovery, indexes, backlog limits, failed events, replay, and cleanup.

CDC can remove the repeated polling loop, while introducing connector and replication-log operations. It still cannot make an external consumer effect atomic with publication. Choose on operational ownership and measured workload, not on an assumption that either relay eliminates duplicates.

The implemented publisher combined durable intent, concurrent batch claims, and bounded retries. Hard-crash reclamation is a separate responsibility from normal send-failure handling. The next boundary is different: deciding when a consumer may safely advance its offset. Recovering Kafka notification consumers examines that problem independently.