Backend Interview: How Do You Solve the Database-and-Message-Broker Dual-Write Problem?

Prompt and Applicable Context

An order service must do two things for one command:

1. write an order to PostgreSQL; and
2. publish an OrderCreated event to a message broker.

The business contract is stricter than “try both calls.” If the database transaction rolls back, no event may describe that nonexistent order. If the transaction commits, the intent to publish must survive a process crash and eventually reach the broker. Events for the same order must remain in order, while global ordering is unnecessary. The relay and consumers may crash at any point, and the broker may redeliver. Distributed two-phase commit is unavailable.

This is the transactional outbox problem. It appears whenever one request changes transactional state and must reliably trigger work in another system: inventory reservation, billing, search indexing, emails, webhooks, or analytics. The goal is not magical exactly-once delivery. The goal is to identify the atomic boundary that is available, make the cross-boundary intent durable, and make retries safe.

What the Interviewer Evaluates

The first signal is failure-window reasoning. “Write the database, then publish” loses the event if the process crashes after commit. “Publish, then commit” exposes an event even if the database later rolls back. An in-memory after-commit callback still disappears with the process. A strong answer names these windows before proposing a pattern.

The second signal is an exact guarantee. The business row and an outbox row can commit atomically in one local database transaction. The broker publish occurs later. This guarantees a durable event intent for every committed mutation; it does not make the database and broker one transaction, and it does not promise exactly-once delivery.

The third signal is end-to-end retry safety. If the broker accepts an event and the relay crashes before recording success, the event will be published again. The relay therefore provides at-least-once publication, and each consumer must make its business effect idempotent. A good answer also distinguishes a consumer's local database effect from an external side effect such as charging a card.

The final signals are ordering and operability: per-aggregate sequence numbers, partition keys, concurrent relay ownership, poison events, retry policy, cleanup, replay retention, lag metrics, and fault-injection tests. Naming the pattern without these boundaries is incomplete.

Questions to Clarify Before Answering

• What is the required guarantee? Is at-least-once publication with exactly-once business effect

sufficient, or is synchronous confirmation required before responding to the caller?

• Which mutation and event belong together? One order mutation may create one event, or a single

transaction may create several events that need consecutive per-order sequence numbers.

• What ordering is required? This design assumes ordering per order, not one global total order

across all orders.

• What can the broker guarantee? Ask about acknowledgements, redelivery, partition ordering,

retention, and producer idempotency. None of them removes the database-to-broker handoff gap by itself.

• How quickly must an event appear? The latency target affects polling interval, database load,

and whether change data capture is justified.

• What does the consumer do? A local database update can share a transaction with an inbox row;

an external payment or email needs a downstream idempotency key or another durable handoff.

• How long must replay remain possible? Cleanup of outbox and consumer-deduplication records must

preserve the required retry and replay horizon.

• Can both resources participate in two-phase commit? The prompt says no. If a real system truly

requires synchronous cross-resource atomicity and both resources support it, its availability and coupling costs should still be evaluated instead of declaring it universally impossible.

30-Second Answer Framework

“I would write the order and an immutable outbox event in the same PostgreSQL transaction. A separate relay claims committed outbox rows, publishes them, and marks them published only after a broker acknowledgement. If the relay dies before publish, the row remains pending; if it dies after the broker accepts but before the mark, it republishes, so delivery is at least once. Every event has a stable ID, and the consumer inserts that ID into a deduplication table in the same transaction as its business update. I would allocate a per-order sequence, use the order ID as the broker partition key, prevent later events from overtaking an earlier pending event, and monitor oldest pending age. Then I would inject crashes at every commit, publish, acknowledgement, and consumer boundary to verify the invariants.”

Step-by-Step Deep Dive

Start by proving why the obvious call orders fail. In a database-first flow, the database can commit at time T1 and the process can stop before the broker accepts at T2; the order exists but no event does. Retrying the HTTP request is not a complete repair because the client may not retry, and a retry can duplicate the order unless the command itself is idempotent. In a broker-first flow, consumers can observe an event before the order transaction fails. Reversing the calls only reverses the inconsistency.

Move the durable intent inside the one atomic boundary the service owns. In a single PostgreSQL transaction, validate the command, mutate the order, allocate the next sequence for that order, and insert an immutable outbox row. Either both rows commit or neither does. A representative schema is:

CREATE TABLE outbox_events (
  event_id uuid PRIMARY KEY,
  aggregate_type text NOT NULL,
  aggregate_id text NOT NULL,
  aggregate_sequence bigint NOT NULL,
  event_type text NOT NULL,
  schema_version integer NOT NULL,
  payload jsonb NOT NULL,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  available_at timestamptz NOT NULL DEFAULT now(),
  claimed_by text,
  claim_until timestamptz,
  published_at timestamptz,
  attempt_count integer NOT NULL DEFAULT 0,
  last_error text,
  UNIQUE (aggregate_type, aggregate_id, aggregate_sequence)
);

CREATE INDEX outbox_dispatch_idx
ON outbox_events (available_at, occurred_at)
WHERE published_at IS NULL;

eventid remains stable through every retry. schemaversion makes payload evolution explicit. The unique aggregate sequence prevents two events from occupying the same logical position. The sequence must be allocated under the same transaction and locking rule as the aggregate; a timestamp or relay processing order is not a safe substitute. If one transaction emits multiple events, allocate consecutive sequence values in their intended order.

A polling relay should claim a small batch in a short transaction. It can select rows with FOR UPDATE SKIP LOCKED, update claimedby and claimuntil, and then commit; the persisted lease prevents other workers from intentionally processing the same row after the row lock is released. It should publish outside a long-held database lock and mark the row published only after the broker acknowledges it. Lease expiry permits recovery when a worker dies. Backoff and available_at prevent a failing destination from creating a tight retry loop. Holding a database transaction open across network publication increases contention and still does not create atomicity with the broker.

There is an unavoidable acknowledgement gap. The broker may durably accept event E, after which the relay can crash before setting published_at. On recovery, E is published again. Marking it before publication would create the opposite, lossy gap. Therefore the relay must choose the safe side—possible duplicates—and consumers must deduplicate.

For a consumer whose business effect is in a database, store processed event IDs in that same transaction:

CREATE TABLE processed_events (
  consumer_name text NOT NULL,
  event_id uuid NOT NULL,
  processed_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (consumer_name, event_id)
);

The consumer begins a transaction and uses INSERT ... ON CONFLICT DO NOTHING RETURNING for (consumername, eventid). It applies the business change only when the insert returns a row, then commits. No returned row means the event was already applied, so the duplicate can be acknowledged without repeating the change. Writing the deduplication row in one transaction and the business effect in another merely creates a new dual-write problem. The deduplication record must also be retained for at least as long as an old event can be replayed.

This local inbox does not atomically cover a nontransactional external effect. For a payment API, pass event_id as the provider's idempotency key. If the destination has no idempotency support, introduce another durable command/outbox plus reconciliation, or accept a documented duplicate risk. The same warning applies to email, webhooks, and other irreversible calls.

Ordering should match the business boundary. Assign a monotonic sequence per order, do not let sequence k + 1 overtake an unpublished k, and use aggregate_id as the broker partition key. The claim query can select only the lowest unpublished sequence for each aggregate, or relay ownership can be partitioned by a stable hash of aggregate_id; either choice must ensure one ordered publishing lane per aggregate. Consumers can reject, buffer, or reconcile a sequence gap according to the domain. Requiring one global sequence would serialize unrelated orders and reduce availability without helping a per-order invariant.

Polling is the simplest portable relay and makes ownership visible in the application database, but its interval trades latency for query load. The pending index, small batches, leases, and bounded cleanup matter as volume grows. Change data capture can tail the database log and route inserted outbox rows with lower polling pressure and often lower latency. It adds connector offsets, database-log retention, deployment, and recovery to the operational boundary. Capturing arbitrary business-table changes also exposes storage mutations rather than intentional domain events; an explicit outbox keeps the contract stable.

Operations complete the design. Monitor pending row count, age of the oldest unpublished row, dispatch throughput and failures, attempt counts, expired claims, broker acknowledgement latency, consumer deduplication counts, quarantined events, and table growth. Archive or delete published rows in bounded batches only after the replay and audit horizon. A poison event needs a deliberate policy: retry, quarantine, or repair. Skipping it may violate per-order ordering, so later events for that aggregate cannot silently continue.

Verification should target boundaries, not only the happy path. Inject failure before database commit, after commit but before response, during relay claim, before publish, after broker acceptance but before published_at, after the consumer business commit but before acknowledgement, and during cleanup. The tests should establish four invariants:

1. every committed business mutation has exactly one durable outbox intent;
2. every rolled-back mutation has no outbox intent;
3. every durable intent is eventually published at least once after recovery; and
4. duplicate deliveries apply the consumer-visible business effect once.

Also stop the relay long enough to build a backlog, restart it, and verify lag recovery, per-order sequence, bounded database load, and alert behavior. Exercise a poison event, payload-version evolution, old-event replay, and cleanup around the retention boundary.

High-Quality Sample Answer

“The database and broker do not share an atomic commit, so I would first make the event intent part of the database transaction. The order row and an immutable outbox row commit together. If the transaction rolls back, neither exists. If it commits and the process dies immediately, another process can still see the outbox row.

A relay claims pending rows with short database transactions and an expiring lease, publishes them, and sets published_at only after broker acknowledgement. I would not hold a database lock while waiting on the network. There is still a crash window after the broker accepts and before the status update, so the relay can publish a duplicate. That is the correct failure bias: a duplicate is recoverable, while a lost event is not.

Each event has a stable UUID. A database consumer inserts that UUID into a table keyed by consumer name in the same transaction as its business update. A duplicate conflicts and becomes a no-op. If the consumer calls a payment or email provider, it must pass the event UUID as an idempotency key or use another durable handoff, because the local deduplication transaction cannot include that remote effect.

For ordering, I allocate a sequence under the order transaction, publish with order ID as the partition key, and block a later sequence from overtaking an earlier pending event for that order. I do not impose global ordering. I would start with polling unless the latency and load targets justify CDC, then monitor oldest pending age, retries, expired claims, duplicate rate, poison events, and table growth.

Finally, I would kill processes at every boundary. The required results are: rollback produces no intent, commit always leaves an intent, recovery publishes every intent at least once, and duplicate delivery changes consumer state once. The outbox solves reliable handoff; request idempotency, consumer idempotency, schema evolution, and reconciliation remain explicit parts of the system.”

Common Mistakes

• Calling the database and broker sequentially → either call can succeed alone → **Commit the

business mutation and event intent in one local database transaction.**

• Calling an in-memory publisher after commit → a crash loses the callback and its state →

Persist the intent before returning.

• Claiming the outbox gives exactly-once delivery → the broker-accepted/status-not-recorded gap

creates duplicates → State at-least-once publication and design exactly-once business effect.

• Marking a row published before broker acknowledgement → a crash can permanently lose the

event → Record success only after acknowledgement and tolerate republishing.

• Writing deduplication state separately from the consumer effect → the consumer recreates the

same dual-write gap → Put both in one local transaction.

• Treating a local inbox as protection for a remote charge → the remote effect cannot join the

transaction → Use a downstream idempotency key, durable handoff, and reconciliation.

• Using timestamps as ordering → clock and concurrency behavior do not allocate a unique causal

position → **Allocate a transactional per-aggregate sequence and use the aggregate as partition key.**

• Running many pollers without claims or leases → workers intentionally race on the same rows →

Use short claims, expiration, small batches, and a pending-row index.

• Deleting published and deduplication rows immediately → delayed retries and replay can repeat

old effects → Set cleanup from the documented replay and audit horizon.

• Testing only successful publication → the design's guarantees live in crash windows → **Inject

failure before and after every durable boundary and assert invariants.**

Follow-Up Questions and Responses

Follow-up 1: What if the destination is an external API rather than a broker?

The source transaction can still write an outbox command. A worker calls the API with event_id as an idempotency key and records the response. A timeout is ambiguous—the remote service may have completed the call—so retry only with the same key. If the API provides neither idempotency nor a queryable operation status, exactly-once effect cannot be guaranteed; add reconciliation or expose the duplicate risk in the business contract.

Follow-up 2: What if a workflow spans several services and databases?

An outbox reliably publishes each service's local state transition; it does not atomically commit an entire multi-service workflow. Model the workflow as a saga with explicit forward steps, idempotency, persisted state, and compensating actions. Each saga step can use its own local transaction plus outbox. Define what happens when compensation also fails instead of describing it as a rollback of all databases.

Follow-up 3: What if the interviewer requires strict global event order?

Clarify why independent aggregates need one order and what throughput or availability may be traded for it. A single sequencer or one broker partition can establish a total order, but it becomes a serialization and failure bottleneck. Most order workflows need causal order only within one order, which a transactional aggregate sequence and aggregate partition key provide more cheaply.

Follow-up 4: How does the design recover from a CDC connector outage?

The committed outbox rows remain the source of truth. Alert on connector lag and database-log retention headroom, persist connector offsets durably, and test restart from the last acknowledged offset. The database must retain the log long enough for the outage objective; otherwise a snapshot or controlled backfill is required. Consumer deduplication makes replaying an overlapping range safe.

Follow-up 5: When should you avoid the transactional outbox?

Use the simpler design when the event is explicitly best effort, such as disposable telemetry, or when the downstream system can safely poll the source of truth and the latency target permits it. If both resources genuinely support two-phase commit and synchronous atomicity is mandatory, evaluate that option with its coupling and availability costs. Event sourcing is another alternative, but it changes the source-of-truth model and should not be introduced merely to avoid one handoff.