Why do transactional emails get lost or duplicated in a monolith?
Most monoliths start by sending email directly in the request handler. A user signs up, and the code inserts the row and then calls the email provider's API. This works until the provider gets slow or returns errors. Then your signup endpoint inherits the provider's latency and failure rate. Worse, if the API call fails after the insert, you have to decide whether to roll back a valid signup because a welcome email bounced.
The usual next step is to send after commit. The user row is safe, and the email goes out once the transaction succeeds. But a deploy, OOM kill or crash between COMMIT and the API call loses the email silently. Nothing records that it was ever owed.
Publishing to an external broker instead swaps one problem for another. The database commit and the broker publish are two separate writes with no shared transaction. Either one can succeed while the other fails, so the two systems drift apart.
| Approach | Request latency tied to provider | Lost on crash | Duplicates or divergence |
|---|---|---|---|
| Send inline | Yes | Possible if rollback is skipped | Retried requests resend |
| Send after commit | Often | Yes, between commit and send | Rare |
| Publish to broker | No | Publish can fail after commit | Broker and DB disagree |
| Outbox job row | No | No, the job commits with the data | Handled by worker idempotency |
The fix is the transactional outbox. You insert an email job row in the same transaction as the domain change. Then both commit or neither does, and a worker sends the email later. This fits monoliths already running Postgres with modest email volume, where adding another piece of infrastructure isn't worth it.
How does a Postgres job queue for email work?
The flow has three steps, and only the first runs inside the user's request.
- Enqueue. The request handler inserts the domain row (an order, a password reset token) and an
email_jobsrow with statuspendingin the same transaction. If the transaction rolls back, neither row exists, so no email can describe something that never happened. - Claim. A worker runs
SELECT ... FOR UPDATE SKIP LOCKEDagainst pending rows, ordered by creation time and limited to a small batch. It marks those rows as in progress and commits. The claim transaction stays short, so the worker holds no row locks while waiting on the network. - Send and record. The worker calls the email provider and writes the outcome back to the job row:
sent, orpendingagain with a retry time, orfailed.
SKIP LOCKED is what lets several workers share one table. With plain FOR UPDATE, every worker targets the same oldest rows. The first worker locks them, and the others block until it commits. Then they recheck those rows, often find them already claimed, and come back with nothing. Adding workers adds waiting, not throughput. With SKIP LOCKED, a worker passes over rows another transaction has locked and takes the next unlocked ones. Workers never wait on each other and never claim the same row at the same time.
The resulting guarantee: enqueueing is atomic with the business change, and delivery is at-least-once. A worker can crash after the provider accepts the message but before it records sent, so a retry may send the email again. You get this from the database you already run, with no separate broker to deploy, monitor, or keep consistent with Postgres.
(1) ENQUEUE: inside the user's request, one transaction
+-----------------------------------------------------+
| Request handler |
| BEGIN |
| INSERT INTO orders ... (domain row) |
| INSERT INTO email_jobs ... (status pending) |
| COMMIT -> both rows exist, or neither |
+-----------------------------------------------------+
|
v
+-----------------------------------------------------+
| Postgres: email_jobs, oldest first |
| job 1 locked by A's claim tx B skips it |
| job 2 locked by A's claim tx B skips it |
| job 3 locked by B's claim tx A skips it |
| job 4 pending, unlocked next claim |
+-----------------------------------------------------+
^ ^ ^ ^
(2) claim (3) record (2) claim (3) record
| | | |
+-------------------------+ +-------------------------+
| Worker A | | Worker B |
| short claim transaction:| | short claim transaction:|
| SELECT ... FOR UPDATE | | SELECT ... FOR UPDATE |
| SKIP LOCKED LIMIT n | | SKIP LOCKED LIMIT n |
| mark in progress | | mark in progress |
| COMMIT | | COMMIT |
+-------------------------+ +-------------------------+
| |
v send (no locks held) v
+-----------------------------------------------------+
| Email provider API |
+-----------------------------------------------------+
The request commits the domain row and a pending email_jobs row together, workers claim disjoint batches with FOR UPDATE SKIP LOCKED in short transactions, call the provider without holding locks, and write back sent, pending with a retry time, or failed.
Designing the job table and enqueueing in the business transaction
Start with one table, email_jobs. Each column exists so a worker or an operator can answer a specific question without guessing:
id: abigintidentity primary key.kind: the template name, such aspassword_reset.payload:jsonbholding the template inputs. Store IDs and values, not rendered HTML, so you can fix a template and still re-render queued jobs.status:pending,running,sent, orfailed.attemptsandmax_attempts: retry bookkeeping.run_at: the earliest time the job may run. It handles both delayed sends and backoff.locked_atandlocked_by: identify a worker that crashed mid-send.last_error: the most recent failure, kept for debugging.created_at: when the job was enqueued.idempotency_key: text with aUNIQUEconstraint, built from the business event, for exampleorder_confirmation:<order_id>.
Completed rows pile up quickly, and the claim query only cares about pending work. Add a partial index on (run_at) with WHERE status = 'pending'. The index then stays roughly the size of your backlog rather than your history. The planner can use it only when the claim query's predicate matches the index's WHERE clause.
Enqueue in the same transaction that changes domain state. Insert the order, then run INSERT INTO email_jobs … ON CONFLICT (idempotency_key) DO NOTHING. If the transaction rolls back, the job never existed. If a request handler retries and runs the same transaction twice, the unique constraint absorbs the duplicate.
To avoid polling latency, issue NOTIFY email_jobs in that transaction as well. Postgres delivers notifications only after commit, so a listening worker never wakes up to a row it can't see yet. Notifications are lost if no worker is listening at that moment, so treat them as a hint and keep polling as the fallback.
CREATE TABLE email_jobs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
kind text NOT NULL, -- template name
payload jsonb NOT NULL, -- template inputs (IDs/values), not rendered HTML
status text NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'running', 'sent', 'failed')),
attempts int NOT NULL DEFAULT 0,
max_attempts int NOT NULL DEFAULT 5,
run_at timestamptz NOT NULL DEFAULT now(), -- delayed sends and backoff
locked_at timestamptz, -- used to spot crashed workers
locked_by text,
last_error text,
created_at timestamptz NOT NULL DEFAULT now(),
idempotency_key text NOT NULL UNIQUE -- e.g. 'order_confirmation:<order_id>'
);
-- Index covers only pending rows, so it tracks the backlog, not the history.
-- The claim query must include WHERE status = 'pending' for the planner to use it.
CREATE INDEX email_jobs_pending_run_at
ON email_jobs (run_at)
WHERE status = 'pending';
-- Enqueue in the same transaction as the domain change.
BEGIN;
INSERT INTO orders (id, customer_id, total_cents)
VALUES (4821, 117, 5999);
-- A retried request produces the same key; the duplicate is silently skipped.
INSERT INTO email_jobs (kind, payload, idempotency_key)
VALUES ('order_confirmation',
jsonb_build_object('order_id', 4821),
'order_confirmation:4821')
ON CONFLICT (idempotency_key) DO NOTHING;
-- Queued now, delivered to listeners only if the transaction commits.
NOTIFY email_jobs;
COMMIT;
Defines the email_jobs table with a unique idempotency key and a partial index on pending jobs, then enqueues an order confirmation and a NOTIFY in the same transaction as the order insert.
How should workers claim and send jobs with SKIP LOCKED?
Each worker claims a batch with one query: rows where status = 'pending' and run_at <= now(), ORDER BY run_at, LIMIT the batch size, FOR UPDATE SKIP LOCKED. SKIP LOCKED lets concurrent workers pass over rows another transaction holds instead of waiting on them, so several workers get disjoint batches without any coordinator. What a worker does after claiming determines how it fails.
| Pattern A: hold the lock | Pattern B: mark and release | |
|---|---|---|
| Flow | Claim, call provider, update status, commit | Set processing, locked_at, locked_by, commit, send, finalize |
| Transaction length | Spans network I/O | Short, no I/O inside |
| Worker crash | Lock released, row is pending again | Row stuck until lease expires |
| Vacuum impact | Long transactions hold back cleanup | Minimal |
| Extra logic | None | Reaper for expired leases |
Pattern B depends on the lease timeout. If the lease is too short, a slow but healthy send gets reclaimed and the email goes out twice. If it is too long, jobs from a crashed worker sit idle. Set the lease well above the provider's request timeout.
Polling every second or two is the baseline for waking workers. LISTEN/NOTIFY reduces latency, and polling stays as the fallback. LISTEN is session state, so it breaks behind a pooler in transaction mode: each transaction can land on a different backend, and notifications stop arriving without any error. Give listeners a dedicated direct connection.
Ordering is not strict. Locked rows get skipped and retries move jobs later, so a newer email can go out before an older one. If order matters for each recipient, claim by recipient: select the oldest pending job only for recipients that have no row in processing.
-- Pattern B, step 1: claim a batch and mark it in one short transaction.
-- SKIP LOCKED makes concurrent workers pass over rows another claim holds.
UPDATE email_jobs
SET status = 'processing',
locked_at = now(),
locked_by = $1 -- worker id, e.g. 'worker-a1:4312'
WHERE id IN (
SELECT id
FROM email_jobs
WHERE status = 'pending'
AND run_at <= now()
ORDER BY run_at
LIMIT 50 -- batch size
FOR UPDATE SKIP LOCKED
)
RETURNING id, recipient, payload;
-- Commit here, then call the provider outside any transaction.
-- Step 2: finalize. The locked_by check stops a worker whose lease was
-- reclaimed from overwriting the row now owned by another worker.
UPDATE email_jobs
SET status = 'sent', locked_at = NULL, locked_by = NULL
WHERE id = $2
AND status = 'processing'
AND locked_by = $1;
-- Reaper: return rows from crashed workers to the queue.
-- Keep the lease well above the provider request timeout so a slow
-- but healthy send is not reclaimed and sent twice.
UPDATE email_jobs
SET status = 'pending', locked_at = NULL, locked_by = NULL
WHERE status = 'processing'
AND locked_at < now() - interval '5 minutes';
Pattern B in SQL: claim a batch with FOR UPDATE SKIP LOCKED and mark it processing, finalize only if this worker still owns the row, and reclaim rows whose lease has expired.
How do you handle retries, failures and duplicate emails?
Delivery is at-least-once. If a worker hands the message to the provider and then crashes before committing status = 'sent', the row lock is released. Another worker then claims the job and sends it again. You cannot get exactly-once delivery to an external SMTP or HTTP provider, because the provider's acceptance and your commit are two separate systems that cannot share a transaction. You can only make duplicates rare and detectable.
Idempotency
Derive the idempotency key from the business event, such as order_confirmation:{order_id}, instead of generating it randomly at enqueue time. Put a unique constraint on it so a retried request cannot enqueue the job twice. If the provider accepts an idempotency key, pass this value so the provider can drop the resend. Store the provider's message id on the row. That gives you evidence when someone reports a double email.
Retries and failures
Classify each error before deciding what to do:
- Transient errors (timeouts, 5xx responses, rate limits): increment
attemptsand pushrun_atforward with exponential backoff plus random jitter. Jitter stops a provider outage from producing synchronized retry waves. - Permanent errors (invalid address, rejected template): retrying cannot succeed, so mark the job
deadimmediately. - Exhausted retries: also mark the job
deadonce it reachesmax_attempts.
Operators need two queries. The first selects dead jobs grouped by error. The second requeues selected ids by setting status = 'pending', attempts = 0 and run_at = now().
Rate limiting
Throttle sending by capping worker concurrency and batch size, and keep the cap below the provider's quota.
-- Success: record the provider's message id as evidence of delivery.
UPDATE email_jobs
SET status = 'sent', provider_message_id = $2, sent_at = now()
WHERE id = $1;
-- Transient failure (timeout, 5xx, rate limit): back off exponentially
-- (30s, 60s, 120s ... capped at 1h), scaled by jitter in [0.5, 1.5).
-- Moves to 'dead' once max_attempts is reached.
-- Column references on the right-hand side read the pre-update values.
UPDATE email_jobs
SET attempts = attempts + 1,
last_error = $2,
status = CASE WHEN attempts + 1 >= max_attempts
THEN 'dead' ELSE 'pending' END,
run_at = now() + least(interval '1 hour',
interval '30 seconds' * power(2, attempts))
* (0.5 + random())
WHERE id = $1;
-- Permanent failure (invalid address, rejected template): retrying cannot succeed.
UPDATE email_jobs
SET attempts = attempts + 1, last_error = $2, status = 'dead'
WHERE id = $1;
-- Operator query 1: inspect dead jobs grouped by error.
SELECT last_error, count(*) AS jobs, min(run_at) AS oldest, max(run_at) AS newest
FROM email_jobs
WHERE status = 'dead'
GROUP BY last_error
ORDER BY jobs DESC;
-- Operator query 2: requeue selected dead jobs after fixing the cause.
UPDATE email_jobs
SET status = 'pending', attempts = 0, run_at = now(), last_error = NULL
WHERE id = ANY($1::bigint[]) AND status = 'dead';
SQL a worker runs to record success, retry transient failures with capped exponential backoff and jitter, and mark permanent or exhausted failures dead, plus two operator queries that inspect dead jobs and requeue them.
What does running the queue in Postgres cost in operations and engineering time?
Housekeeping
Every status change writes a new row version, so a busy job table produces dead tuples all the time. When autovacuum falls behind, the table and its indexes bloat, and the claim query scans dead entries before it finds live ones. Delete or archive completed rows in batches of a few thousand per transaction so each delete stays short. Lower the table's per-table autovacuum scale factor so vacuum runs after a fixed number of changes rather than a percentage of a growing table. At high volume, partition by time and drop old partitions. Dropping a partition removes the rows without creating any dead tuples.
A long-running transaction anywhere in the database holds back the cleanup horizon, and vacuum cannot reclaim the rows it might still see. Alert on transaction age, not only on queue metrics.
Watch: queue depth, age of the oldest pending job, attempt counts, and dead-letter count.
Illustrative estimate (assumed numbers, not a benchmark)
- Polling cost. Assume 4 workers polling an idle queue every 2 s. Added latency is about interval ÷ 2, which gives 1 s. Query load is workers ÷ interval, which gives 2 queries/s. A 0.5 s interval lowers latency to 0.25 s but raises load to 8 queries/s.
- Lock duration. If rows stay locked while they send, lock time ≈ batch size × send time. With 50 jobs at 200 ms each, rows stay locked for 10 s. Claim the batch and commit first, then send, to keep locks short.
| You save | You own |
|---|---|
| No broker to deploy or monitor | Retry and backoff logic |
| One transactional boundary | Dead-letter tooling |
| Familiar backups and access control | Queue observability |
| Careful migrations on a hot table |
When should you avoid a Postgres email queue, and what are the alternatives?
The job table puts its load on the same primary that serves your business writes. Each email produces an insert, at least one status update and an eventual delete. Every one of those writes WAL and leaves dead tuples behind. At high send volumes this churn competes with application traffic, causes table bloat, and needs aggressive autovacuum tuning on a hot table. Avoid the table in these cases:
- Churn would load the primary. If WAL volume, replication lag or vacuum pressure from the queue becomes noticeable, the queue is costing your main workload.
- You need ordering, priority fairness or per-tenant isolation.
SKIP LOCKEDgives you concurrency, not fairness. Strict per-recipient ordering, weighted priorities, or stopping one tenant's bulk send from starving password resets all need scheduling logic you would have to build yourself. - Email must outlive a database outage. If Postgres is down, nothing is enqueued and nothing is sent.
| Option | Strength | Trade-off |
|---|---|---|
| Established Postgres job library | Retries, backoff, scheduling and dashboards already built and tested | Its schema and conventions become a dependency |
| Transactional outbox plus message broker | Keeps atomic enqueue and moves fan-out and throughput off the primary | A relay process and a broker to run |
| Send inline or after commit | No queue at all | Mail is lost on a crash or provider error |
Sending after commit is acceptable for low-stakes mail such as digests or optional notices.
Decision checklist
Build the in-house table only if all of these are true:
- Send volume is a small fraction of your write traffic.
- Each email is independent, so ordering does not matter.
- No mature job library exists for your stack, or none fits.
- Email can stop while the database is down.
- Someone owns vacuum health and dead-letter review for the table.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to postgres job queue email, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- Consumers learn about a breaking change at review time rather than from production errors.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.