--- title: "PostgreSQL Transactional Outbox: Reliable Events, Retries and Recovery" author: "Acadify Engineering Team" date: "October 14, 2026" description: "Build a PostgreSQL transactional outbox with SQL claims, worker leases, retries, idempotent consumers, monitoring, failure recovery, and testing." categories: ["Product Engineering"] --- Canonical URL: https://acadifysolution.com/blogs/post/postgresql-transactional-outbox-pattern-retries-recovery # PostgreSQL Transactional Outbox: Reliable Events, Retries and Recovery By **Acadify Engineering Team** on October 14, 2026 A transactional outbox stores a business change and the event describing it in one PostgreSQL transaction. A separate publisher reads committed events and delivers them to a broker or downstream service. This removes the classic dual-write gap, but delivery is still at least once: consumers must handle duplicates. ## Direct answer Use a PostgreSQL transactional outbox when a service must persist business state and reliably emit an event without a distributed transaction. Insert both records in one transaction, publish asynchronously with worker claims and bounded retries, and make consumers idempotent. Monitor backlog and failures. The pattern guarantees neither exactly-once delivery nor global event ordering. ## Audience, prerequisites and scope This implementation guide is for founders evaluating integration risk, engineering leaders designing reliability controls, and software teams operating PostgreSQL-backed services. It assumes PostgreSQL, a transaction-capable application layer, a message transport, migration tooling, and a worker runtime. The examples cover a single database, polling publishers, and downstream deduplication. They do not implement a message broker or claim production-ready distributed coordination. ## Why dual writes fail Imagine an order service that commits an order and then publishes an OrderCreated event. If the process crashes between the database commit and the network publish, the order exists but the event is lost. Reversing the sequence creates the opposite failure: a consumer can observe an order that never committed. Retrying both calls without a durable coordination record cannot remove the uncertainty. The outbox stores the event in the same transaction as the order. Committed rows remain available to a worker after application crashes. The publisher may send an event twice if it crashes after delivery but before acknowledging the row. Design for that duplicate explicitly. ## Reference architecture The request handler validates and authorizes an operation, writes domain data and an outbox row, then commits. A polling worker claims due outbox rows, serializes their immutable payloads, sends them to the broker, and records success or retry. Consumers use event identifiers to deduplicate business effects. Observability covers oldest pending age, retry counts, throughput and permanently failed messages. Keep transport credentials in the worker environment, not in the outbox payload. Avoid storing secrets or unnecessary personal data in event bodies. Record schema versions and stable aggregate identifiers for future compatibility. ## 1. Create the tables The following is PostgreSQL SQL. Execute in a migration against a test database before production use. ``` CREATE TABLE orders ( id uuid PRIMARY KEY, account_id uuid NOT NULL, status text NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE outbox_events ( id uuid PRIMARY KEY, aggregate_type text NOT NULL, aggregate_id uuid NOT NULL, event_type text NOT NULL, schema_version integer NOT NULL DEFAULT 1, payload jsonb NOT NULL, status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','processing','sent','failed')), attempts integer NOT NULL DEFAULT 0, available_at timestamptz NOT NULL DEFAULT now(), lease_until timestamptz, lease_token uuid, created_at timestamptz NOT NULL DEFAULT now(), sent_at timestamptz, last_error text ); CREATE INDEX outbox_due_idx ON outbox_events (available_at, created_at) WHERE status IN ('pending','processing'); ``` The partial index helps find due work. A UUID event ID identifies a logical event across retries. Retain payload immutability: if the business fact changes, emit another event rather than editing a committed one. Establish retention and archival policies before the table grows indefinitely. ## 2. Commit the business action and event atomically The following SQL illustrates the transaction boundary; application code must generate stable UUIDs, validate the actor's permission, and bind values using prepared statements. ``` BEGIN; INSERT INTO orders (id, account_id, status) VALUES ($1, $2, 'created'); INSERT INTO outbox_events (id, aggregate_type, aggregate_id, event_type, payload) VALUES ($3, 'order', $1, 'OrderCreated', jsonb_build_object('order_id', $1, 'account_id', $2)); COMMIT; ``` If either insert fails, roll back both. A client timeout around COMMIT leaves the caller uncertain whether it committed; use an application request idempotency key to make command retries safe. The outbox alone does not deduplicate incoming commands. Never execute the domain insert and outbox insert on different database connections with independent transactions. ## 3. Claim due events safely Multiple publishers can compete for work using row locks and SKIP LOCKED. A short claim transaction prevents long-lived locks while the network call runs. The following query claims up to 50 due events with a fresh worker-supplied lease token. ``` WITH due AS ( SELECT id FROM outbox_events WHERE (status = 'pending' AND available_at <= now()) OR (status = 'processing' AND lease_until < now()) ORDER BY created_at, id FOR UPDATE SKIP LOCKED LIMIT 50 ) UPDATE outbox_events o SET status='processing', lease_until=now() + interval '2 minutes', lease_token=$1, attempts=attempts+1 FROM due WHERE o.id=due.id RETURNING o.*; ``` Commit immediately after claiming. SKIP LOCKED provides queue-like concurrent access, not a globally consistent ordered view. If publishing takes longer than the lease, another worker may reclaim the event. Use a lease longer than normal delivery latency, renew carefully if necessary, and make duplicate sends harmless. ## 4. Publish, acknowledge and retry The following is integration pseudocode, not runnable application code. The broker API, timeout semantics, error classification and authentication depend on your transport. ``` for event in claimed_events: try: broker.publish( key=event.aggregate_id, event_id=event.id, schema_version=event.schema_version, payload=event.payload, timeout=bounded_timeout ) mark_sent_if_lease_matches(event.id, event.lease_token) except TransientError as error: schedule_retry_if_lease_matches( event.id, event.lease_token, bounded_backoff(event.attempts) ) except PermanentError as error: mark_failed_if_lease_matches(event.id, event.lease_token) ``` Success means the broker's documented durability acknowledgment was received, not merely that a local send call returned. If the acknowledgment is lost, a retry may duplicate the message. Apply exponential backoff with jitter and a cap. After a configured retry budget, move the event into an explicit failed state for operator review rather than silently dropping it. Use a conditional acknowledgment so an expired worker cannot overwrite a newer lease: ``` UPDATE outbox_events SET status='sent', sent_at=now(), lease_until=NULL, lease_token=NULL, last_error=NULL WHERE id=$1 AND status='processing' AND lease_token=$2; ``` Check the affected row count. Zero means the worker no longer owns the lease. For retries, update only rows matching the same lease token, set status to pending, clear lease fields and set available_at to a future timestamp. For permanent failures, retain a sanitized error summary and alert an operator. ## 5. Make consumers idempotent An outbox publisher can send twice, so consumers must protect their side effects. A consumer that writes to PostgreSQL can insert the event ID into a uniqueness-constrained inbox in the same transaction as its business update. ``` CREATE TABLE consumed_events ( consumer_name text NOT NULL, event_id uuid NOT NULL, consumed_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY (consumer_name, event_id) ); BEGIN; INSERT INTO consumed_events (consumer_name, event_id) VALUES ($1, $2) ON CONFLICT DO NOTHING RETURNING event_id; -- Apply the business mutation only if the INSERT returned a row. COMMIT; ``` The comment is a required application decision, not executable conditional SQL. If the insert returned no row, acknowledge the duplicate without applying the mutation. If the downstream effect is an external payment or email API, its own idempotency mechanism is still needed; a local inbox transaction cannot atomically commit an external side effect. ## 6. Ordering, schema evolution and delivery contracts Do not promise total ordering across concurrent workers. If a single aggregate needs ordering, assign a monotonic per-aggregate version inside the domain transaction and route events by aggregate key to an ordered partition. Consumers should reject, buffer or reconcile gaps according to business requirements. Even then, retries and partition changes require explicit design. Version event schemas and document compatibility. Additive fields are often easier to roll out than renamed or deleted fields, but each consumer must be tested against the actual change. Preserve event IDs and business identifiers during replays. Never generate new IDs for retries of the same logical event. ## 7. Operations, security and capacity Track pending count, oldest unsent event age, publish success rate, retry distribution, lease expirations, failed-event count and consumer deduplication rate. Alert on service-level objectives tied to business needs rather than arbitrary thresholds. A growing oldest-event age is often more actionable than raw queue depth. Limit publisher database permissions to the outbox operations it requires. Encrypt connections to PostgreSQL and the broker. Rotate credentials through your secret manager. Restrict event payload fields, classify sensitive data, define deletion and retention requirements, and redact exception messages before persistence. Protect operator replay tools with authentication, authorization, audit logs and rate limits. Choose batch size based on measured database contention, broker throughput and event size. Backpressure should slow publishers safely rather than overwhelm the database. Consider partitioning or archiving after measuring growth. Purge sent rows only after your retention and replay requirements are satisfied. ## 8. Troubleshooting and recovery ### Events are stuck in processing Check whether leases expired, workers are healthy and database clocks are synchronized. A reclaimed event can be published again, so confirm consumer deduplication before replaying. Do not blindly reset every row. ### Backlog grows despite healthy workers Measure broker latency, database query plans, lock waits, event sizes and per-worker throughput. Increase concurrency only after confirming the bottleneck is not shared database contention or downstream rate limiting. ### Duplicate business effects occur Trace the event ID end to end. Verify the consumer records its inbox key and business mutation in one transaction. For external side effects, verify the downstream provider's idempotency-key semantics. ### Events appear out of order Review aggregate versions, publisher concurrency, transport partitions and consumer processing. Treat ordering as an explicit contract rather than an accidental property of created_at. ## 9. Validation checklist - Confirm a failed domain transaction creates no outbox event. - Confirm a successful commit creates both records. - Kill the request process immediately after commit and verify eventual publication. - Kill a publisher after broker acknowledgment but before database acknowledgment; verify safe duplicate handling. - Run multiple publishers and verify lease ownership checks. - Simulate broker downtime, exponential retries and terminal failure review. - Verify duplicate consumer delivery changes business state only once. - Exercise schema changes and aggregate ordering assumptions. - Inspect permissions, payload redaction, metrics and replay audit logs. - Load-test with representative payload sizes and failure rates before production rollout. These are tests to execute in your environment; no production performance results are asserted here. ## 10. Tradeoffs and alternatives Polling is straightforward and operationally transparent, but adds database queries and publish latency. Change data capture can stream committed changes through logical decoding, reducing polling while adding replication-slot, connector and retention operations. Direct publish is simpler but reintroduces the dual-write gap unless the business can tolerate missed events. Distributed transactions are possible in some stacks but increase coordination complexity and are not universally supported. Start with polling when throughput and latency requirements allow it. Reassess with measured backlog, database load and recovery objectives. CDC does not eliminate the need for consumer idempotency or monitoring. ## Key definitions Transactional outboxA database table containing events committed with business data.Dual writeTwo separate writes whose outcomes cannot be committed atomically.LeaseTemporary ownership of an event claim by a publisher.At-least-once deliveryA delivery contract in which retries may produce duplicates.Idempotent consumerA handler whose repeated processing of one logical event does not repeat its business effect.CDCChange data capture, often using database transaction logs. ## Frequently asked questions ### 1. Does the transactional outbox guarantee exactly-once delivery? No. It makes the local business update and event record atomic, but publisher crashes can cause the same event to be delivered again. Consumers need deduplication or idempotent side effects. ### 2. Why not publish directly inside the database transaction? A network publish can succeed while the database later rolls back, and holding locks during remote calls increases contention. The outbox separates durable local commitment from external delivery. ### 3. What happens when PostgreSQL commits but the application crashes? The committed outbox row remains. A healthy publisher can later discover and deliver it, subject to worker availability and retention configuration. ### 4. What happens if the publisher crashes after sending? The event may be reclaimed after its lease expires and sent again. Keep its original event ID and rely on downstream duplicate protection. ### 5. Is SKIP LOCKED enough to prevent duplicate messages? No. It reduces simultaneous row claims, but expired leases and lost acknowledgments can still produce duplicates. Conditional lease tokens and idempotent consumers are necessary. ### 6. How should retries be scheduled? Use bounded exponential backoff with jitter for transient errors, distinguish permanent failures, and retain failed events for controlled review and replay. ### 7. Can an outbox preserve event order? It can support per-aggregate ordering with explicit versions and partitioning, but concurrent publishers do not guarantee global ordering. Document the actual contract. ### 8. When should a team choose CDC instead of polling? Consider CDC when measured polling overhead or latency is unacceptable and the team can operate logical replication, connectors, offsets and replication-slot retention safely. ### 9. How long should sent events be retained? Set retention according to audit, replay, compliance and storage requirements. Archive or purge with a documented policy and ensure it does not undermine recovery needs. ### 10. How can we test the design before launch? Use crash injection at commit, publish and acknowledgment boundaries; duplicate deliveries; worker concurrency; broker outages; schema upgrades; and replay drills. Validate the observed effects, not just HTTP status codes. ## Further reading and related Acadify guides For integration launch controls, see [API Integration Release Checklist](https://acadifysolution.com/blogs/post/api-integration-release-checklist-authentication-timeouts-retries-recovery). For queue operations, see [Scaling Background Jobs](https://acadifysolution.com/blogs/post/scaling-background-jobs-queues-retries-backpressure). For inbound authenticity, see [Webhook Signature Verification](https://acadifysolution.com/blogs/post/webhook-signature-verification-secure-nodejs). Explore [Acadify Solution](https://acadifysolution.com/) for software engineering and [Acadify AI](https://ai.acadifysolution.com/) for AI evaluation and reliability. ## Primary references - [AWS Prescriptive Guidance: Transactional outbox pattern](https://docs.aws.amazon.com/prescriptive-guidance/latest/cloud-design-patterns/transactional-outbox.html) - [PostgreSQL SELECT and SKIP LOCKED](https://www.postgresql.org/docs/current/sql-select.html) - [PostgreSQL UPDATE](https://www.postgresql.org/docs/current/sql-update.html) - [PostgreSQL INSERT and ON CONFLICT](https://www.postgresql.org/docs/current/sql-insert.html) - [PostgreSQL logical decoding](https://www.postgresql.org/docs/current/logicaldecoding-explanation.html) --- ### About the Author **Acadify Engineering Team** The editorial team publishes practical guides about software development and AI evaluation.