Asynchronous Write-Through: Solving Cross-Store Conflicts Without Two-Phase Commit

If you're building a serious AI stack, you've likely hit the wall: your relational data lives in Postgres, your embeddings live in Qdrant (or pgvector), and your agent state is split between Redis and a graph database. Every write must land in multiple stores. If one fails, you're in a world of hurt.

The textbook solution is two-phase commit (2PC). But 2PC is a distributed systems trap. It's slow, fragile, and often more trouble than it's worth. In this article, I'll show you a pragmatic alternative: asynchronous write-through with idempotent retries. It's simpler, faster, and gives you eventual consistency without the drama.

The Problem: Cross-Store Writes

Imagine you're building a RAG pipeline. When a user uploads a document, you need to:

1. Store the document metadata in Postgres.
2. Generate an embedding (via BGE-M3, say) and store it in Qdrant.
3. Update a graph edge in Neo4j or a vector-graph index.

If any step fails, you have a partial state. The document is in Postgres but not in Qdrant, so retrieval misses it. Or the reverse: it's in Qdrant but not Postgres, so you can't delete it later.

You could wrap all three in a distributed transaction. But 2PC has a nasty habit of blocking when a participant dies mid-commit. The coordinator waits forever, locks are held, and your system grinds to a halt. In a microservices world, 2PC is often a non-starter.

What's Wrong with Two-Phase Commit?

Let me count the ways:

• Latency: Every transaction requires an extra round-trip to each participant. For a write that touches three stores, you're looking at 3-4x the latency of a single write.
• Availability: If any participant is down, the whole transaction fails. Your write path is only as available as your weakest store.
• Coordinator failure: If the coordinator crashes after phase one, participants may hold locks indefinitely. You need a recovery protocol, which is complex and error-prone.
• Not all stores support it: Qdrant doesn't have a native 2PC. Redis doesn't either. You'd have to implement it yourself, which is a rabbit hole.

2PC is for when you absolutely need strong consistency across heterogeneous stores, and you can tolerate the cost. In most AI infrastructure, you don't. You need eventual consistency, and you need it fast.

Asynchronous Write-Through: The Pattern

Here's the idea: instead of trying to atomically commit to all stores at once, you write to a single source of truth (Postgres) and then asynchronously propagate changes to secondary stores (Qdrant, Redis, graph DBs). The key is to make the propagation idempotent and retryable, so that failures don't cause permanent inconsistencies.

The pattern is simple:

1. Write to primary: The write goes to Postgres (or whatever your source of truth is).
2. Enqueue a job: After the commit, you push a message to a queue (Redis, RabbitMQ, or just a Postgres table) with the change details.
3. Process the job: A worker picks up the message and applies the change to the secondary store.
4. Retry on failure: If the secondary store is down or the operation fails, retry with exponential backoff.

Because the operation is idempotent, you can retry it endlessly without side effects. The same update applied twice is the same as applied once.

Making It Idempotent

Idempotency is the crux. If you're upserting a vector into Qdrant, the operation is naturally idempotent: upsert with the same ID overwrites. Similarly, setting a Redis key is idempotent. But if you're doing a counter increment or appending to a list, you need to be careful.

For our RAG example, the write to Qdrant is an upsert by document ID. That's idempotent. The write to the graph store is an upsert of an edge, also idempotent. So we're good.

But what about deletes? If you delete a document, you need to delete the embedding. That's also idempotent. So the pattern holds.

The Outbox Pattern

A robust way to implement async write-through is the outbox pattern. Instead of a separate queue, you write the intent to a table in the same database as your primary data. This gives you atomicity: the data and the outbox entry are committed in the same transaction. If the transaction commits, the outbox entry is guaranteed to be there. If it fails, neither is.

Here's the schema:

CREATE TABLE outbox (
    id BIGSERIAL PRIMARY KEY,
    aggregate_type TEXT NOT NULL,
    aggregate_id TEXT NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    processed_at TIMESTAMPTZ
);

When you write a document, you also insert an outbox row:

BEGIN;
INSERT INTO documents (id, title, content) VALUES (1, 'Doc', '...');
INSERT INTO outbox (aggregate_type, aggregate_id, payload)
VALUES ('document', '1', '{"operation": "upsert", "store": "qdrant", "embedding": "..."}');
COMMIT;

Then a worker polls the outbox for unprocessed rows and applies them to the secondary stores. Once successful, it marks them as processed.

Handling Failures and Retries

What if the worker crashes after applying to Qdrant but before marking the outbox row as processed? Then the row will be processed again, but since the upsert is idempotent, it's fine. That's the beauty of idempotency.

What if Qdrant is down for an extended period? The worker will retry with backoff. You can set a max retry count and then move the row to a dead-letter queue for manual intervention. Or you can simply keep retrying indefinitely, as long as the operation is idempotent.

For the retry logic, you can use a library like pg-boss or graphile-worker for Postgres-based queues. They give you retries, backoff, and scheduling.

Ordering and Consistency

One concern is ordering. If you have two updates to the same document, you want them to be applied in the order they were made. With a single worker, you can process in FIFO order. With multiple workers, you might get out-of-order. To avoid that, you can use a single worker per aggregate ID, or you can include a version number in the payload and reject stale updates.

For most AI infrastructure, ordering is not critical. If an update arrives out of order, the final state is still consistent because the last write wins. But if you need strict ordering, you can partition the outbox by aggregate ID and ensure that each partition is processed by a single worker.

When to Use Sync Write-Through Instead

Async write-through is great for most cases, but sometimes you need the secondary store to be updated before you return a response. For example, if you need to serve the embedding immediately after a write, you can't wait for the async worker. In that case, you can do a synchronous write-through: write to both stores in the same request, but handle failure gracefully.

Here's a pattern: write to Postgres first. If that succeeds, write to Qdrant. If Qdrant fails, you still have the Postgres write, but the async worker will eventually catch up. But you have to make the Qdrant write idempotent, which it is.

This hybrid approach gives you low latency for reads that need freshness, while still being resilient.

Case Study: Replacing 2PC in a RAG Pipeline

Let me share a concrete example. At my previous company, we built a document search system. We had Postgres for metadata, Qdrant for embeddings, and Redis for caching. Originally, we used 2PC via a custom coordinator. It was a nightmare: frequent timeouts, deadlocks, and a system that could barely handle 100 writes per second.

We switched to async write-through with the outbox pattern. The change was dramatic:

• Latency: Write latency dropped from 120ms to 15ms (just the Postgres write).
• Throughput: We could handle 10x the write volume because we weren't blocked on the slowest store.
• Reliability: No more distributed deadlocks. The system self-healed via retries.

We had to handle one tricky case: deleting a document. The delete had to remove the embedding from Qdrant and the cache entry from Redis. We made both idempotent. The outbox worker processed deletes the same way as upserts. It worked flawlessly.

Tooling and Implementation

Here's a sample implementation using Node.js and Postgres. First, the outbox table (as above). Then, a worker using pg-boss:

const Boss = require('pg-boss');
const boss = new Boss('postgres://...');

async function processOutbox() {
  const job = await boss.fetch('outbox');
  if (job) {
    const { aggregate_type, aggregate_id, payload } = job.data;
    if (payload.store === 'qdrant') {
      await qdrantClient.upsert({
        collectionName: 'documents',
        points: [{ id: aggregate_id, vector: payload.embedding }]
      });
    }
    await boss.complete(job.id);
  }
}

setInterval(processOutbox, 1000);

For a more robust solution, you can use a dedicated queue like RabbitMQ. But starting with Postgres keeps your infrastructure simple.

Idempotency Keys for External APIs

If you're integrating with external APIs, like an embedding service, idempotency is trickier. Many APIs don't support idempotency keys. In that case, you can generate a unique request ID and store it in the outbox payload. When you call the API, you pass the request ID. If the call fails, you retry with the same ID. The API can deduplicate based on that ID if it supports it.

If the API doesn't support idempotency, you have to accept that you might get duplicate embeddings. But for most use cases, that's fine. Duplicate embeddings are just wasted storage, not a consistency issue.

When Not to Use Async Write-Through

Async write-through is not a silver bullet. There are cases where you need strong consistency across stores. For example, if you have a financial system where you need to deduct from one account and add to another atomically, you should use a real distributed transaction (or better, a single database).

But for AI infrastructure—embeddings, metadata, caches, graph edges—eventual consistency is perfectly acceptable. The reads are eventually consistent, and the window is usually milliseconds.

Conclusion

Two-phase commit is a sledgehammer. For most cross-store consistency problems in AI, async write-through with idempotent retries is the right tool. It's simple, fast, and resilient. You get the benefits of consistency without the headache of distributed transactions.

Implement it with the outbox pattern, make your secondary operations idempotent, and use a reliable queue. Your system will be more responsive, easier to debug, and less likely to fall over.

Now, go kill your 2PC coordinator. You won't miss it.