Database + Queue: Getting the Semantics Right

We often have a pattern of database writes combined with a message queue. But ensuring correctness is hard.

We encountered this in real work. In our setup, we wrap API operations to the database in a transaction and publish a message to SQS. Sometimes we see a race condition: the worker receives the message and queries the database, but fails to get the expected content. Why? The message was published before the transaction committed, so the worker is reading data that doesn't exist yet.

Such semantics of SQL database updates combined with a queue for time-consuming work is very common. How do we properly handle this?

Option A: Transactional Outbox Pattern

The API doesn't just write changes to the original table—it also appends rows to an outbox table representing pending work. All of this is wrapped in a single transaction. Workers either poll the outbox table or a separate process reads the outbox and publishes messages to the queue. Since the outbox write is part of the same transaction, you're guaranteed consistency.

Option B: Database CDC

This is more advanced. Change Data Capture makes all consistency guarantees at the database level. Based on the WAL (write-ahead log), a CDC job (e.g., Debezium) pushes messages to the queue. The correctness has a very clean mental model—the queue becomes a derivative of database state. But it's probably too much infrastructure to set up for many teams.

Option C: Publish After Commit with Sweeper

Publish the message after the transaction commits. Yes, there's a small window where the API service could crash after commit but before publish, leaving orphaned work. In practice, this happens rarely. Less elegant than outbox or CDC, but simpler operationally.

The Non-Negotiable: Idempotent, Retriable Work

Bottom line though—the work itself must be idempotent and retriable. At the end of execution, workers themselves can crash or fail before updating the database for completed tasks. The system must be able to retry. And when it does, the system must remain consistent even with retried work. No pattern saves you if your workers aren't idempotent.