The Dual-Write Problem: Building a Scalable Transactional Outbox for Google Pub/Sub

If a service writes data and then publishes an event about it, there is a window where one succeeds and the other doesn't. That's the dual-write problem, and in a billing system it costs real money.

This is how we closed that window for a BigQuery → Pub/Sub pipeline, using a Postgres outbox and a generic Go dispatcher on GKE.

The setup

Imagine a billing platform. The source of truth is BigQuery, where the heavy analytical lifting happens, but downstream services need real-time updates through Google Pub/Sub to trigger invoices, emails and ledger updates.

The goal is simple: get data from BigQuery to Pub/Sub.

The naive pattern: "the direct shot"

Most teams start with a Cloud Run Job or a cron task:

1. Query BigQuery
2. Loop through the rows
3. Call pubsub.Publish()
4. Job finishes. Done.

The question that matters: what happens if the network flickers at step 3?

If the producer crashes, or the Pub/Sub API hiccups, you get a dual-write failure. The data was processed, but the message is gone. In a billing system, "we lost a few messages" means "we forgot to charge a client $50k."

Kafka and RabbitMQ users: the same architecture applies. The core challenge is distributed consistency, not the broker.

The solution: a transactional outbox

To sleep at night, we decoupled extraction from dispatching, with a Postgres-backed outbox as a reliable staging area.

Instead of one fragile step, there are two resilient ones:

1. The producer (Cloud Run Job) queries BigQuery and writes the serialized bytes into a pubsub_outbox table in Cloud SQL.
2. The dispatcher (GKE service) is a "dumb" pipe that picks up those bytes and makes sure they reach the broker.

Why not poll BigQuery directly? It's a FinOps problem

I'm often asked why the dispatcher doesn't just poll BigQuery. As a tech lead, I look at the bill.

• BigQuery is built for analysis. On-demand pricing charges per terabyte scanned. Polling every 10 seconds to ask "is there new data?" burns budget for nothing.
• Latency. BigQuery DML updates can take seconds. Postgres does it in milliseconds.
• Concurrency. BigQuery has no SELECT ... FOR UPDATE SKIP LOCKED. Run two producer instances and they fight over the same rows.

The key piece: SKIP LOCKED

The Go dispatcher lets Postgres handle concurrency. Each pod claims a batch of rows that no other pod can see, so the dispatcher scales horizontally on GKE without two pods ever claiming the same row.

-- Fetch 100 messages, lock them so no one else sees them,
-- and skip anything already being handled by another pod.
UPDATE pubsub_outbox
SET
    lock_id = :worker_id,
    locked_at = NOW()
WHERE id IN (
    SELECT id FROM pubsub_outbox
    WHERE completed_at IS NULL
      AND topic_name = :target_topic
      AND (lock_id IS NULL OR next_retry_at < NOW())
    ORDER BY created_at ASC
    LIMIT 100
    FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;

One thing worth being precise about: this gives at-least-once delivery, not exactly-once. If a pod publishes a message and crashes before marking the row complete, the row gets published again. That's the right trade-off for billing, since a duplicate is recoverable and a lost message isn't, but it means the consumer has to handle duplicates. That's part two of this series.

The generic advantage: infrastructure vs. logic

Because the payload is stored as BYTEA (raw bytes), the dispatcher becomes a universal delivery engine. It doesn't care where the data came from, only that it gets delivered.

Business logic, from any source:

• Analytical sources: a scheduled Cloud Run Job queries BigQuery and writes large datasets into the outbox.
• Operational sources: an upstream microservice (checkout, billing) inserts into the outbox as part of its own local database transaction, so the state change and the event are committed together.

Delivery infrastructure, zero-touch:

• Nobody touches the dispatcher code. To support a new data stream, deploy the same Docker image to GKE with a new TOPIC_NAME environment variable.

The result: the team stops reinventing the reliability wheel. Every new data stream gets at-least-once delivery, exponential backoff and dead-letter handling out of the box.

---

Next in the series: the consumer side — what happens when Pub/Sub delivers the same message twice, and how a transactional inbox makes that safe.