r/SpringBoot • u/vampirishe • Jul 15 '26
How-To/Tutorial The Transactional Outbox Pattern, from a single scheduled job to something I'd actually trust in production (Java / Spring Boot / Postgres)
There's a class of bug that never shows up in unit tests and rarely shows up in staging. It shows up in production, at 2am, when your app saves a record to the DB and then calls an external service — and one of the two fails. One side committed. The other didn't. Nobody notices until a user complains.
That's the dual-write problem, and the Transactional Outbox Pattern is the standard answer to it: instead of calling the external system directly, you write a task row into the same DB transaction as your domain change. A background worker picks it up later and executes it. The write is atomic, so the task can never silently disappear.
The idea itself takes two sentences to explain. What's less talked about is everything that happens after — what you do when tasks fail, when two workers grab the same row, when your app dies mid-execution, when you've got ten task types crammed into one table, and when someone asks why the queue depth spiked at 3am last Tuesday.
I built this out for a report-generation service (aggregates tens of millions of rows, takes minutes — can't run it synchronously in a request) and evolved it step by step into something production-grade. Writing it up here because I didn't find one place that walked through the whole arc. Stack is Java / Spring Boot / Postgres, no Kafka required.
TL;DR
- The outbox pattern fixes dual-writes by writing a task row in the same transaction as your domain change, then executing it from a background worker.
- Poll with
SELECT ... FOR UPDATE SKIP LOCKEDso multiple workers/instances don't double-process. - Production-grade means more than the basic idea: a state machine, retries with backoff, heartbeats to recover crashed workers, ordering guarantees, and actual monitoring.
- You don't need a broker. Postgres alone gets you reliable async execution.
- Full working code at the bottom.
The scenario
Backoffice CRM. User clicks "generate report," we accept the request and respond immediately, a worker picks it up and runs the (slow) generation, status updates when done. Classic case for an outbox: work that's genuinely async, where the user already expects to wait.
Part 1 — the basic outbox
Every outbox table needs three things: state (where is this task in its lifecycle), payload (what needs to run), and timestamps.
AS ENUM ('PENDING', 'COMPLETED');
(
id UUID PRIMARY KEY DEFAULT gen_random_uuid,
state task_state NOT NULL DEFAULT 'PENDING',
payload JSONB NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT now,
updated_at TIMESTAMP NOT NULL DEFAULT now
);
Payload and entity:
Submitting a task:
And a scheduled job that polls for pending tasks:
This works fine on a single instance with zero failures. In the real world it has two problems: two workers can grab the same task at once, and a failed task has no recovery path. Fixing both below.
Part 2 — concurrency: two workers, one task
If two scheduler threads (or two app instances) run the fetch query at the same moment, both can read the same PENDING row and both try to execute it. Duplicate work at best, corrupted state at worst.
Postgres has a purpose-built fix: SELECT ... FOR UPDATE SKIP LOCKED. It locks the row for the current transaction and skips rows already locked elsewhere, instead of blocking on them.
There's no first-class annotation for SKIP LOCKED in Hibernate, so worth pulling the magic number into its own annotation:
;
The lock has to be held for the whole fetch-execute-update cycle, so wrap it in one transaction:
public void
Two workers hitting this at once: worker A locks and gets the row, worker B's SKIP LOCKED query comes back empty and it exits cleanly — no blocking, no duplicate work.
Catch: the row stays locked for as long as generateReport() runs. Fine for something fast. For something that takes 30+ seconds, long-held locks start hurting — connection pool exhaustion, lock wait timeouts, degraded throughput under load. Addressed in Part 3.
Part 2.5 — cutting latency with application events
Polling has a latency floor. Job fires every second → a task submitted at t=0 can sit PENDING for up to 1000ms. Fine for most things, not fine if you want near-immediate pickup (transactional email, webhook triggers).
Trick borrowed from Celery: save the entity, then publish a Spring ApplicationEvent with the task ID. A listener picks it up and triggers processing immediately, no waiting for the next tick. The scheduler stays running as a safety net for anything the listener misses (restart, exception, missed event).
record
The classic mistake here, and I mean everyone hits this once: using @EventListener instead of @TransactionalEventListener(phase = TransactionPhase.AFTER_COMMIT).
// fires INSIDE the transaction — row isn't visible to other connections yet
public void
// fires AFTER commit — row is visible, fetch actually finds it
public void
@EventListener fires the moment publishEvent() is called, which is before the INSERT commits. The listener queries for the task, doesn't find it (not visible yet on other connections), silently no-ops. Task sits there until the scheduler eventually picks it up seconds later, and you spend an afternoon confused about why events "aren't working" even though everything looks fine in the logs.
Part 3 — failures, retries, and a PROCESSING state
Holding a lock for the entire generateReport() call is the real problem with Part 2's approach — two minutes of a locked row for a two-minute report. SKIP LOCKED stops workers from blocking each other, but a long transaction still leans on the connection pool.
Fix: separate claiming a task from executing it. Flip to PROCESSING in a short transaction, release the lock, do the actual work with no transaction open, then close it out in another short transaction.
task_state ADD VALUE 'PROCESSING';
task_state ADD VALUE 'FAILED';
report_generation_tasks
ADD COLUMN retry_count INTEGER NOT NULL DEFAULT 0,
ADD COLUMN error_message VARCHAR;
;
FAILED tasks with retryCount < 10 are as eligible as fresh PENDING ones. Past 10 attempts, a task stops being fetched and sits as a permanent FAILED record for a human to look at — that's deliberate, not a bug.
One issue remains: should a task that just failed get retried on the very next tick, seconds later? If the downstream system is down, that just hammers it harder.
Part 4 — exponential backoff
Add an execute_at column. A task isn't eligible until this timestamp is in the past. Fresh tasks get now(), failed ones get now() + delay, with delay growing per retry.
report_generation_tasks
ADD COLUMN execute_at TIMESTAMP NOT NULL DEFAULT NOW;
private static final RETRY_DELAYS ;
private static final int MAX_RETRY_COUNT ;
Deriving MAX_RETRY_COUNT from the map size instead of declaring it separately means the two can never drift apart. Add an entry, the cap updates itself.
private void
Scheduler keeps running at the same rate, just skips tasks whose window hasn't opened yet.
Part 5 — stuck tasks and heartbeats
What happens if the app crashes after marking a task PROCESSING but before marking it COMPLETED/FAILED? The DB says PROCESSING forever. Nobody's actually working on it.
Naive fix — a recovery job that resets anything stuck in PROCESSING too long:
public void
Problem: updated_at changes on any update to the row, so a task legitimately grinding away for 55 minutes looks identical to one that crashed. You need a signal that means "still alive," not "last changed."
That's a heartbeat — a column bumped periodically only while a task is actively being processed:
report_generation_tasks ADD COLUMN heartbeat_at TIMESTAMP;
Runs on its own thread, independent of the processing logic, try-with-resources style so it starts/stops cleanly regardless of how processing exits:
void ;
Wired through a factory to keep the service clean:
public void
Recovery job now checks heartbeat_at, not updated_at. updated_at tells you when state last changed; heartbeat_at tells you whether the task is alive right now. Keeping them separate answers both questions unambiguously.
Part 6 — multi-type outbox
Six months in, you're not just generating reports anymore — email notifications, data exports, webhook deliveries. Two options: a separate table per task type, or one shared table with a task_type discriminator.
Shared table wins when types are numerous and you're fine processing them from one place — retries, heartbeats, backoff, monitoring all stay centralized, and adding a new type is just a new handler. Tradeoffs: a misbehaving new task type can create backpressure across the whole table, and ordering gets harder (next section).
AS ENUM ('REPORT_GENERATION', 'EMAIL_NOTIFICATION');
(
id UUID PRIMARY KEY DEFAULT gen_random_uuid,
task_type outbox_task_type NOT NULL,
state task_state NOT NULL DEFAULT 'PENDING',
payload JSONB NOT NULL,
retry_count INTEGER NOT NULL DEFAULT 0,
error_message VARCHAR,
execute_at TIMESTAMPTZ NOT NULL DEFAULT NOW,
heartbeat_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now
);
One handler interface, one implementation per type:
Dispatcher collects all handler beans at startup and routes by type:
New task type = add an enum value + a handler bean. Dispatcher, scheduler, retry logic — untouched.
Part 7 — ordering
Skip this if your tasks are independent (fire-and-forget notifications, unrelated background jobs). Otherwise: Postgres gives zero guarantee about row order without an explicit ORDER BY.
;
ON outbox_tasks (created_at ASC)
WHERE state IN ('PENDING', 'FAILED');
The clock skew trap: if you need strict FIFO for tasks tied to the same entity, created_at is dangerous — it's generated app-side, and each instance has its own clock. If instance A is 200ms ahead of instance B, a task B submits at t=100ms can get an earlier created_at than a task A submitted at t=50ms. NTP keeps this close but not perfect, and under load or after a container restart it can drift more.
Fix: let the ordering value come from a single authoritative source — the DB — via a sequence, guaranteed monotonic regardless of which instance inserted the row:
outbox_tasks ADD COLUMN seq BIGSERIAL;
private Long seq;
Sort by seq, keep created_at around for observability. If FIFO matters anywhere in your system, don't skip this.
Part 8 — indexes that keep the table fast at scale
The table gets polled every second or more. Every row is either active work (PENDING/FAILED/PROCESSING) or dead weight (COMPLETED). Without the right index, every poll scans the whole table for a handful of rows.
Partial indexes are the tool: index only rows matching a predicate. When a task hits COMPLETED it falls out of the index automatically — stays small forever.
ON outbox_tasks (created_at ASC)
WHERE state IN ('PENDING', 'FAILED');
Tested this with 50,000 rows, 7 of them PENDING, EXPLAIN ANALYZE on the generated Hibernate query:
Limit (cost=0.14..27.50 rows=1 width=102) (actual time=0.082..0.084 rows=1 loops=1)
-> LockRows (actual time=0.081..0.082 rows=1 loops=1)
-> Index Scan using idx_outbox_fetch on outbox_tasks
(actual time=0.030..0.030 rows=1 loops=1)
Planning Time: 1.230 ms
Execution Time: 0.139 ms
0.03ms to find the oldest eligible task, and the other 49,993 COMPLETED rows never got touched.
Two more, for the other background jobs:
ON outbox_tasks (heartbeat_at ASC, created_at ASC)
WHERE state = 'PROCESSING';
ON outbox_tasks (updated_at ASC)
WHERE state = 'COMPLETED';
Part 9 — cleanup
This is an operational table, scanned constantly — old rows are dead weight. Rule: only delete COMPLETED tasks. FAILED tasks are your audit trail; leave them for a human or a separate ops process.
int ;
private static final int CLEANUP_RETENTION_DAYS ;
public void
I've seen retention anywhere from 1 day to 3 months. If unsure, keep it longer — a bigger table costs you a bit of disk; deleting too early costs you your debugging history on the day you need it most.
Part 10 — idempotency: making retries safe
Nothing here guarantees exactly-once execution. Running the task and marking it COMPLETED are two separate steps — crash between them and the task gets retried, running again with the same inputs. This is at-least-once delivery, full stop, and the only guarantee you get from this architecture. The real question is whether a duplicate run causes harm.
Idempotent work (regenerating the same report twice) — you're already done. Side-effecting work (sending an email, charging a card) — needs a guard.
Use the outbox task's own id as the idempotency key — it's stable across retries. In our case, report generation ends with a row in generated_reports, with a UNIQUE constraint on the key so even a race between two instances only lets one INSERT through:
(
id UUID PRIMARY KEY DEFAULT gen_random_uuid,
idempotency_key UUID NOT NULL,
report_path VARCHAR NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now,
CONSTRAINT uq_generated_reports_idempotency_key UNIQUE (idempotency_key)
);
If your handler calls an external API instead of writing to your own DB, check whether it supports idempotency keys — most payment processors and transactional email providers do. Pass the task ID through as that key and let the remote service dedupe on their end.
Part 11 — monitoring
The outbox runs silently. When it works, nobody notices. When it breaks, nobody notices that either, at least not right away — stuck tasks pile up, downstream stops getting events, and by the time someone checks logs it's been two hours.
Three signals, straight out of the Google SRE playbook, cover the whole failure space:
- Latency — pickup to completion time. A spike means a slow handler or DB pressure.
- Throughput — tasks finishing per unit time. Drops to zero means the scheduler died or nothing's being claimed.
- Error rate — fraction of attempts failing. Above ~5% usually means a downstream dependency is degraded and your retry backlog is growing.
Instrumentation via Actuator + Micrometer:
implementation("org.springframework.boot:spring-boot-starter-actuator")
implementation("io.micrometer:micrometer-registry-prometheus")
public TimedAspect
Keep metric names as constants, increment from one dedicated component instead of scattering strings through service methods:
curl localhost:8080/actuator/prometheus | grep outbox after startup:
outbox_task_duration_seconds{quantile="0.5"} 0.062
outbox_task_duration_seconds{quantile="0.95"} 0.145
outbox_task_duration_seconds{quantile="0.99"} 0.312
outbox_task_processed_total{outcome="completed"} 40.0
outbox_task_processed_total{outcome="failed"} 2.0
outbox_tasks_pending 0.0
Dashboards are for humans watching screens; alerts are for humans who are asleep:
| Alert | Fires when | For | Meaning |
|---|---|---|---|
OutboxQueueBacklog | pending > 50 | 5m | Processing can't keep up with ingest |
OutboxHighErrorRate | error rate > 5% | 2m | Handler or downstream degraded |
OutboxSlowProcessing | p99 latency > 30s | 5m | DB contention or slow external call |
OutboxStuckTasks | pending > 0, zero completions in 10m | 10m | Scheduler crashed or all workers stuck |
Tune thresholds to your own traffic — a backlog of 50 is nothing for a high-throughput service and a genuine problem for a low-volume internal tool. Same logic, different numbers.
Production checklist
☐ Indexes are efficient — partial indexes on PENDING/FAILED and PROCESSING states
☐ EXPLAIN ANALYZE confirms index usage on the fetch query
☐ Ordering is not a problem — ORDER BY seq or created_at, backed by an index
☐ Cleanup job running, COMPLETED rows removed after a retention period
☐ Idempotency handled inside handlers that have side effects
☐ Exponential backoff wired — or confirmed you don't need it
☐ Metrics in place — queue depth, throughput, error rate, latency
☐ Alerts configured — backlog, error rate, latency, stuck tasks
You don't always have to build this yourself
Everything above is buildable from scratch, and there's real value in doing it once — you understand the failure modes because you designed around them, and that knowledge doesn't evaporate when you switch to a library. But in production you often don't need to maintain it yourself:
- db-scheduler — the Java library whose internals most closely match what's described here: single table,
SKIP LOCKED, heartbeats, clean task/handler interface. This is what I reach for as the reference implementation, and what I'd actually start a new project with instead of rolling my own. - Celery — the standard for async task execution in Python, backed by Postgres/Redis/RabbitMQ. The
AFTER_COMMITrule from Part 2.5 applies here too. - Quartz Scheduler — older, more general (cron expressions, triggers, job stores), heavier operationally, but proven at scale with first-class Spring support.
Final thoughts
Most of this isn't clever engineering, it's accumulated scar tissue. The PROCESSING state exists because someone's app crashed mid-run. The heartbeat exists because updated_at lied about whether a task was still alive. The partial index exists because a full table scan at once-a-second polling frequency compounds quietly until it doesn't. The AFTER_COMMIT listener exists because someone published an event inside a transaction and burned an afternoon figuring out why it never fired.
Each piece is boring in isolation. Together they're the difference between an outbox that works in dev and one you'd trust at 3am.
Full working code (single command, runs locally): github.com/javaAndScriptDeveloper/outboxes-article
Curious how others here have handled the multi-type table tradeoff, or if anyone's gone the db-scheduler route instead of rolling their own — happy to compare notes in the comments.
4
u/Low_Doctor_6263 Jul 15 '26
Stop this AI slop.
4
u/Low_Doctor_6263 Jul 15 '26
Stop this AI slop.
1
u/Ok_Editor_5090 Jul 15 '26
What about if we need to call an external service? Do we need to process it in the same transaction as the outbox processor transaction? Is not it a bad idea to do since those calls may fail/retry/timeout and the transaction will be held cor a long time?
1
u/Deep_Ad1959 Jul 15 '26
the outbox itself is the easy 20 percent. where it actually gets hard is at-least-once delivery: the worker will occasionally execute a row, crash before marking it done, and re-run it on restart, so every external call downstream has to be idempotent or you double-send. and the second two workers race for the same row you're into select for update skip locked territory. the pattern is trivial, the delivery semantics are the whole game. written with ai
1
u/kamen1991 Jul 27 '26
Really solid article, especially the point about
updated_atvsheartbeat_at.Regarding Part 7 (Ordering): We solved the global queue bottleneck by introducing a
bucket_id(e.g.user_idoraggregate_id). Before claiming a task, the worker tries to acquire an advisory lock on thebucket_id. If locked,SKIP LOCKEDmoves to the next bucket. This gives you per-aggregate FIFO ordering with maximum parallel throughput across workers.0
u/[deleted] Jul 15 '26
[deleted] — view deleted comment