paulserban.eu

Writing Edition

Paul Serban

Writing, snippets & book notes

Vol. 2026

August 15, 2026

GraphRAG at Scale: Keeping Postgres and Neo4j Consistent Without Two-Phase Commit

A practical architecture for dual-store consistency, idempotent graph writes, and guaranteed reconciliation in Postgres + Neo4j RAG systems

Introduction

Retrieval-augmented generation systems that combine a relational store with a graph database - commonly called GraphRAG architectures - have become popular because they let you pair structured, transactional data in Postgres with relationship-rich traversal in Neo4j. The relational side handles entities, documents, and metadata with strong guarantees. The graph side handles multi-hop reasoning: "what connects to what," "which entities co-occur," "what's the shortest path between these two concepts." Each store is good at what the other is bad at, which is exactly why teams reach for this combination.

The problem is that you now have two independent databases that both need to agree on the same underlying facts, and nothing forces them to agree automatically. Postgres doesn't know Neo4j exists. Neo4j has no visibility into Postgres transactions. The moment you introduce a second store, you've introduced a distributed systems problem, whether or not you intended to sign up for one. This article works through that problem concretely: what the naive implementation gets wrong, how to fix the write path so no change is ever silently lost, how to make graph writes safe to retry, and - the part most system design interviews and real postmortems actually care about - what your exact recovery plan is when the two stores drift apart.

The Problem: Why Naive Dual Writes Fail

The instinctive implementation looks like this: when a new entity or relationship is extracted from a document, write it to Postgres, then write the corresponding node or edge to Neo4j. If the Neo4j write fails, leave the triggering message unacknowledged so the queue redelivers it later. This feels reasonable at first glance - you're not losing the message, you're retrying it. But it quietly assumes two things that aren't true in a distributed system: that failures are rare and transient, and that retries are safe. Neither assumption holds for long.

Consider the actual failure surface. A process can crash between the Postgres commit and the Neo4j write, with no message in flight at all - nothing to retry, because nothing failed, the step just never happened. A network partition can make a write to Neo4j time out even though it actually succeeded on the server side, meaning a "failed" write and a "successful" write look identical from the caller's perspective. A retry can then double-apply an already-applied change, which is harmless for some operations (an idempotent MERGE) and catastrophic for others (a non-idempotent counter increment or relationship creation via CREATE). And if retries keep failing - because Neo4j is down for an extended maintenance window, or a bug in the handler throws on every attempt - you either retry forever, blocking the queue and starving every other message behind it, or you give up and silently lose the update, which is worse.

None of this is a Postgres or Neo4j problem specifically. It's the general shape of the dual-write problem: whenever you write to store A and then store B as two separate steps, there's a window where A has committed and B hasn't, and the system has no way to tell you, after the fact, whether that window closed cleanly. You can't close it by trying harder or adding more retries. You close it by changing the architecture so that the two stores are never required to be transactionally consistent in the first place - only eventually consistent, with a guaranteed, bounded path to convergence.

Architectural Framing: Source of Truth and Derived Projection

The first design decision determines everything downstream, so it's worth stating explicitly rather than discovering by accident: Postgres is the system of record, and Neo4j is a derived, rebuildable projection of that record. This isn't a demotion of Neo4j - the graph is still where multi-hop retrieval happens, and it's still a first-class part of the serving path. But it is not where facts originate or where the canonical state lives. If Neo4j disappeared entirely tomorrow, you could reconstruct it from Postgres. The reverse is not true, and it shouldn't be - you never want to be in a position where the only copy of a fact lives in the graph.

This framing matters because it changes the question you're trying to answer. You are no longer trying to achieve atomic commits across two heterogeneous stores, which would require something like two-phase commit (2PC) - a protocol that's operationally expensive, doesn't compose well with most managed database offerings, and still doesn't save you from a coordinator crash. Instead, you're trying to guarantee that every committed Postgres change is eventually reflected in Neo4j, that the graph write is safe to apply more than once, and that if the two ever do drift, you have a deterministic way to bring them back in sync. That's a much more tractable problem, and it's the one every real system in this space actually solves.

Fixing the Write Path: The Transactional Outbox

The root cause of the lost-update problem is that "write to Postgres" and "write to Neo4j" are two separate operations with no shared transaction boundary. The fix is the transactional outbox pattern: instead of writing to Postgres and then immediately trying to write to Neo4j, you write the business row and an outbox row describing the change in the same Postgres transaction. Because both writes are in one transaction, they either both commit or neither does - there is no window where one happened and the other didn't. The outbox row becomes the durable, atomic record that a change needs to propagate.

A relay process - either a simple poller or a change-data-capture (CDC) tool like Debezium reading the Postgres write-ahead log - picks up outbox rows and publishes them to a message broker or directly invokes the Neo4j writer. Because the outbox row only exists if the business transaction committed, the relay can crash, restart, or fall behind arbitrarily and it will still eventually publish every committed change exactly because the row is sitting there waiting. This is the core guarantee you're buying: not "the Neo4j write usually succeeds," but "every Postgres commit produces a durable intent that will eventually be delivered, no matter what happens to the delivery mechanism in between."

-- Single Postgres transaction: business write + outbox write
BEGIN;

INSERT INTO entities (id, name, entity_type, updated_at)
VALUES ($1, $2, $3, now())
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name,
    entity_type = EXCLUDED.entity_type,
    updated_at = EXCLUDED.updated_at;

INSERT INTO outbox (id, aggregate_type, aggregate_id, event_type, payload, created_at)
VALUES (
  gen_random_uuid(),
  'entity',
  $1,
  'entity.upserted',
  jsonb_build_object(
    'id', $1,
    'name', $2,
    'entity_type', $3,
    'version', extract(epoch from now())
  ),
  now()
);

COMMIT;

// Relay: polls the outbox table and publishes to the Neo4j write path.
// In production, prefer CDC (e.g. Debezium on the outbox table) over polling
// to reduce latency and avoid missed-polling-window edge cases.
import { Pool } from "pg";

async function relayOutbox(pool: Pool, publish: (evt: OutboxEvent) => Promise<void>) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const { rows } = await client.query(
      `SELECT * FROM outbox
       WHERE processed_at IS NULL
       ORDER BY created_at
       LIMIT 100
       FOR UPDATE SKIP LOCKED`
    );

    for (const row of rows) {
      await publish({
        id: row.id,
        eventType: row.event_type,
        aggregateId: row.aggregate_id,
        payload: row.payload,
      });
      await client.query(
        `UPDATE outbox SET processed_at = now() WHERE id = $1`,
        [row.id]
      );
    }
    await client.query("COMMIT");
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

Note the FOR UPDATE SKIP LOCKED clause - it lets multiple relay instances run concurrently without double-processing the same row, which matters once you need more than one worker for throughput.

Making the Neo4j Write Idempotent

The outbox guarantees delivery, but it does not guarantee delivery exactly once - it guarantees at least once. Messages will be redelivered after relay restarts, broker rebalances, or consumer timeouts, and they may arrive out of order relative to when the underlying Postgres rows were written. The Neo4j write handler has to be designed to tolerate both of these realities, not treat them as edge cases.

The first rule is to use MERGE on a stable business key rather than CREATE. CREATE blindly appends a new node or relationship every time it runs, so a redelivered event produces duplicate graph elements. MERGE matches on the key first and only creates if no match exists, which makes the write naturally repeatable. The second rule is to carry a monotonic version - a Postgres updated_at timestamp or a sequence number - in the event payload, and have the Neo4j write only apply if the incoming version is newer than what's already on the node. This single guard clause handles out-of-order delivery: if event version 5 arrives after version 7 already landed (which happens during retries and redeliveries), the stale write is a no-op instead of a regression. The third rule is that deletes need the same treatment as creates - a hard delete followed by a late-arriving upsert for the same key will resurrect data that should be gone, so deletes should be modeled as tombstones (a deleted_at property, versioned like everything else) rather than as node or relationship removal, or at minimum as version-guarded removals.

from neo4j import GraphDatabase

def apply_entity_upsert(tx, event: dict):
    """
    Idempotent, version-guarded upsert. Safe to call any number of times,
    in any order, for the same or overlapping events.
    """
    query = """
    MERGE (e:Entity {id: $id})
    ON CREATE SET e.name = $name,
                  e.entity_type = $entity_type,
                  e.version = $version,
                  e.created_at = datetime()
    ON MATCH SET e.name = CASE WHEN $version > e.version THEN $name ELSE e.name END,
                 e.entity_type = CASE WHEN $version > e.version THEN $entity_type ELSE e.entity_type END,
                 e.version = CASE WHEN $version > e.version THEN $version ELSE e.version END
    RETURN e.version AS current_version
    """
    result = tx.run(
        query,
        id=event["id"],
        name=event["payload"]["name"],
        entity_type=event["payload"]["entity_type"],
        version=event["payload"]["version"],
    )
    return result.single()["current_version"]

def apply_entity_tombstone(tx, event: dict):
    """
    Deletes are versioned too, so a late-arriving upsert from before
    the delete can't resurrect the node.
    """
    tx.run(
        """
        MATCH (e:Entity {id: $id})
        WHERE $version > e.version
        SET e.deleted_at = datetime(), e.version = $version
        """,
        id=event["id"],
        version=event["payload"]["version"],
    )

Applying this pattern consistently across every event type is tedious but non-negotiable - a single handler that uses CREATE instead of MERGE, or skips the version check "because this field never changes," is the kind of shortcut that produces a very confusing drift bug three months later.

What Happens When Retries Keep Failing

Idempotency answers "is it safe to retry," but it doesn't answer "what do I do when retrying stops working." Neo4j can be down for an extended window - a failover, a bad deploy, a resource exhaustion incident - and during that time the relay needs a policy, not an infinite loop.

The standard shape is exponential backoff with jitter and a retry cap: each failed attempt waits longer than the last, with randomized jitter to avoid synchronized retry storms across workers, up to a maximum number of attempts or a maximum elapsed time. Once that cap is exceeded, the event moves to a dead-letter queue (DLQ) with its full payload intact - not dropped, not logged-and-forgotten, but parked somewhere durable and inspectable. The DLQ is a holding area, not a trash can; nothing is actually lost because the original fact is still sitting safely in Postgres, and the outbox event payload still contains everything needed to reapply it later.

Two operational signals matter here and deserve dashboards and alerts, not just logs: DLQ depth, which tells you how many changes are currently un-reflected in the graph and growing, and consumer lag - the age of the oldest unprocessed outbox row - which tells you how stale the projection is right now. During a sustained Neo4j outage, it's also worth circuit-breaking the relay rather than letting every worker hammer a database that's already struggling to recover; the backlog accumulates safely in the outbox in the meantime, bounded only by your Postgres storage, which is a far better failure mode than cascading retry pressure making the outage longer. Once the underlying cause is fixed, the DLQ gets replayed - because the handlers are idempotent and version-guarded, replaying a backlog of possibly-redundant events is safe by construction, not something you have to reason about case by case.

# Example: replaying a DLQ backlog once Neo4j is healthy again.
# Assumes a DLQ topic/table where each entry is the original outbox payload.
kafka-console-consumer \
  --bootstrap-server broker:9092 \
  --topic graph-writes-dlq \
  --from-beginning \
  --timeout-ms 5000 \
  | while read -r event; do
      curl -s -X POST http://graph-writer:8080/replay \
        -H "Content-Type: application/json" \
        -d "$event"
    done

echo "DLQ replay complete. Check consumer lag and DLQ depth dashboards before closing the incident."

Partial Network Partitions and Write Ambiguity

Partial partitions deserve their own discussion because they produce a failure mode that's genuinely different from a clean outage: ambiguity about whether a write happened at all. A full outage is easy to reason about - the write definitely failed, retry it. A partial partition is harder: the relay sends a write to Neo4j, the network drops the response on the way back, and the relay sees a timeout. But the write may well have been applied on the Neo4j side before the response was lost. From the caller's point of view, a timed-out write and a failed write are indistinguishable, and treating them as "definitely failed, retry" is the only safe default.

This is precisely why the idempotency work from earlier isn't a nice-to-have - it's the thing that makes partition-induced ambiguity a non-issue instead of a data-corruption risk. If retrying a write that already landed is harmless (because MERGE plus version-guarding makes the operation naturally idempotent), then you don't need to resolve the ambiguity at all. You just retry, and either the write applies for the first time or it applies a second time with no effect. The entire category of "did that actually succeed" questions, which is normally one of the hardest things to reason about in distributed systems, gets absorbed by the idempotency layer instead of requiring its own handling logic at the call site.

Reconciliation: The Repair Loop You Design in From Day One

Even with an outbox and idempotent writes, assume drift will happen anyway - not because the architecture is wrong, but because real systems have bugs, manual hotfixes applied directly against one store, restored backups that only touch one side, and migrations that miss an edge case. The question that actually separates a production-ready design from a toy one is: what's the exact recovery plan when Postgres and Neo4j disagree? If the honest answer is "we'd notice eventually and write a one-off script," that's a gap worth closing before it becomes an incident.

A continuous reconciliation job is the first layer: a periodic process that compares Postgres and Neo4j per entity or per partition, using cheap comparison signals - row counts, max version per bucket, or hashes of a bucket's contents - rather than row-by-row diffing, which doesn't scale. When a bucket's hash or max-version doesn't match, the job re-emits the relevant events (either by replaying from the outbox table or from CDC history) for just that bucket, which keeps reconciliation cheap in the common case where almost everything already matches. A targeted reconciliation path complements this: the ability to replay outbox or CDC events for a specific time window or specific entity IDs on demand, for when you already know roughly where the drift is because an incident or a bug report pointed you there. And the nuclear option, which should be a designed, rehearsed capability rather than a last resort improvised under pressure, is a full rebuild: because Neo4j is a derived projection, you can always replay the entire Postgres dataset into a fresh Neo4j database and cut over to it once it's caught up. Teams that treat this as a real, tested runbook - not just a theoretical possibility - recover from severe drift in hours; teams that have never actually rebuilt the graph from scratch tend to discover, mid-incident, that the rebuild path has rotted.

# Simplified reconciliation job: compares per-partition content hashes
# between Postgres and Neo4j, and re-emits events for mismatched partitions.
import hashlib

def compute_postgres_bucket_hash(pg_conn, bucket_id: int) -> str:
    cur = pg_conn.cursor()
    cur.execute(
        """
        SELECT id, name, entity_type, updated_at
        FROM entities
        WHERE id %% 100 = %s
        ORDER BY id
        """,
        (bucket_id,),
    )
    rows = cur.fetchall()
    payload = "|".join(f"{r[0]}:{r[1]}:{r[2]}:{r[3]}" for r in rows)
    return hashlib.sha256(payload.encode()).hexdigest()

def compute_neo4j_bucket_hash(neo4j_session, bucket_id: int) -> str:
    result = neo4j_session.run(
        """
        MATCH (e:Entity)
        WHERE e.id % 100 = $bucket AND e.deleted_at IS NULL
        RETURN e.id AS id, e.name AS name, e.entity_type AS type, e.version AS version
        ORDER BY e.id
        """,
        bucket=bucket_id,
    )
    rows = [(r["id"], r["name"], r["type"], r["version"]) for r in result]
    payload = "|".join(f"{r[0]}:{r[1]}:{r[2]}:{r[3]}" for r in rows)
    return hashlib.sha256(payload.encode()).hexdigest()

def reconcile(pg_conn, neo4j_session, re_emit_fn, num_buckets=100):
    mismatches = []
    for bucket in range(num_buckets):
        pg_hash = compute_postgres_bucket_hash(pg_conn, bucket)
        neo4j_hash = compute_neo4j_bucket_hash(neo4j_session, bucket)
        if pg_hash != neo4j_hash:
            mismatches.append(bucket)
            re_emit_fn(bucket)  # re-publish outbox events for this bucket's rows
    return mismatches

Track drift as a first-class metric with its own SLO - a mismatch rate from the reconciliation job, or a projection-lag figure derived from outbox-age - the same way you'd track error rate or latency. Drift that's invisible until a user notices bad retrieval results is a monitoring gap, not an acceptable outcome.

The Consistency Trade-off, Stated Honestly

None of this makes Neo4j strongly consistent with Postgres, and pretending otherwise would undercut the whole design. Reads from Neo4j are eventually consistent by construction - there's always some window, however small under healthy operation, where Postgres has a fact that the graph hasn't absorbed yet. For most GraphRAG use cases this is a completely acceptable trade-off: a retrieval query asking "what entities relate to X" doesn't usually need to reflect a write that committed fifty milliseconds ago.

But some flows genuinely do need read-your-writes semantics - a user updates an entity and immediately expects a graph query to reflect that change. There are two honest ways to handle this, and neither of them is "add 2PC." The first is to simply read from Postgres for that specific flow instead of the graph, which works when the query doesn't actually need graph traversal, only the updated fact. The second is to have the client wait on a version watermark - the caller knows the version number of the write it just made, and the read path either polls Neo4j until that version is visible or blocks briefly on a signal that the projection has caught up to at least that version. If neither option is acceptable - if a query truly needs strong, real-time consistency across both stores simultaneously - that's not a signal to build a more complex consistency protocol. It's a signal that the query doesn't belong split across two stores in the first place, and you should reconsider whether that particular piece of logic should live in Postgres alone.

Best Practices for Running This in Production

A few practices separate a design that works on a whiteboard from one that survives real incidents. Keep the outbox table narrow and append-mostly - avoid storing large blobs in it, and archive or delete processed rows on a schedule so the table doesn't become a performance bottleneck in its own right. Prefer CDC (Debezium reading the Postgres logical replication stream) over polling once you're past early-stage scale, since polling introduces a latency floor and additional read load on the primary, while CDC reacts to commits with much lower lag and doesn't compete with application queries.

Instrument everything along the pipeline, not just the endpoints: outbox insert rate, relay publish rate, consumer processing rate, DLQ depth, and reconciliation mismatch rate should all be visible on one dashboard, because an incident usually shows up first as a change in the relationship between these numbers rather than a single metric crossing a threshold. Version every entity and relationship type consistently - don't let some event types carry a version field and others not, because the one handler you forgot is the one that eventually causes a confusing, hard-to-reproduce drift bug. Finally, actually rehearse the full-rebuild path before you need it: run it in staging on a realistic data volume, time it, and keep that number current, because "we can always rebuild from Postgres" is only true in practice if someone has verified it recently.

Key Takeaways

Analogies and Mental Models

It helps to think of Postgres and Neo4j less like two peers and more like a ledger and a read-optimized index built from that ledger - closer to how a search index like Elasticsearch relates to its system of record than to two databases that are supposed to be mirror images of each other. Nobody expects a search index to be transactionally consistent with the database it's indexing; they expect it to catch up quickly and to be fully rebuildable if it ever gets corrupted. That's the exact posture to take toward Neo4j in this architecture. The outbox, in this framing, is simply the durable queue of "index updates" waiting to be applied - the same pattern used in CDC-based search indexing pipelines, just pointed at a graph database instead of a full-text index.

The version-guarded MERGE is worth its own mental model too: think of it as optimistic concurrency control applied across a network boundary instead of within a single database. Within Postgres, a WHERE version = $expected clause on an update is a familiar way to prevent a stale write from clobbering a newer one. The cross-store version guard on the Neo4j write is the identical idea, just applied to protect the projection from stale or reordered events instead of protecting a row from a concurrent transaction. If you already understand optimistic locking, you already understand why this works.

The 80/20 of This Problem

If you can only implement a subset of this design, the outbox pattern and idempotent versioned writes deliver the large majority of the resilience. The outbox alone eliminates the "crash between two writes loses the update" failure mode entirely, and idempotent MERGE with a version guard eliminates the two other common sources of corruption - duplicate application from retries, and stale overwrites from out-of-order delivery. Together, these two changes turn "dual writes are fundamentally unsafe" into "dual writes are safe, just eventually consistent," which is the single biggest leap in this architecture.

Everything after that - DLQs, circuit breakers, reconciliation jobs, drift SLOs - is about operability and blast-radius containment rather than correctness. They matter enormously for running this in production with confidence, and skipping them means you'll eventually have an incident with no clean way to detect or recover from it. But if you're prioritizing under time pressure, get the outbox and idempotent writes right first; a system with those two things and nothing else is already far more correct than one with elaborate alerting sitting on top of a dual-write race condition.

Conclusion

The instinct to treat "commit to Postgres, then write to Neo4j, retry on failure" as sufficient is understandable - it looks like it handles the failure case. But it only handles the failure case where the second write fails cleanly and visibly, and distributed systems fail in far messier ways than that: processes crash mid-flow, networks partition partially, writes time out without you knowing whether they landed. None of that can be fixed by retrying harder. It has to be fixed by changing the contract - making Postgres the unambiguous source of truth, making Neo4j a projection that's allowed to lag and is designed to be rebuilt, guaranteeing delivery with an outbox, and making the graph writes themselves safe to repeat in any order.

The reconciliation story is what completes the picture, and it's the part worth being explicit about, because it's the question that exposes whether a design was actually thought through: what happens when the two stores drift anyway. The honest answer here is a layered one - continuous hash-based comparison to catch drift early, targeted replay to fix known issues quickly, and a tested full-rebuild path as the guaranteed backstop. None of this requires two-phase commit, distributed transactions, or anything exotic. It requires treating consistency as a property you design and monitor over time, not one you demand instantaneously and hope holds.

References