paulserban.eu

Writing Edition

Paul Serban

Writing, snippets & book notes

Vol. 2026

August 16, 2026

GraphRAG at Scale: Architecting Postgres + Neo4j for Consistent, Low-Latency Retrieval

Taming dual-store consistency and vector index tuning when your retrieval layer spans 10 million objects and 40 million chunks

Introduction

GraphRAG - retrieval-augmented generation layered on top of a knowledge graph - has moved from research demo to production pattern faster than most teams' infrastructure has caught up. The appeal is obvious: vector search alone retrieves semantically similar chunks, but it has no concept of relationships. A graph store captures entities, hierarchies, and multi-hop connections that a flat embedding space simply cannot represent. Combining the two - Postgres with pgvector for dense retrieval, Neo4j for structural and relational reasoning - gives you the best of both worlds, but it also gives you two sources of truth that must stay synchronized under load.

This article focuses on two problems that surface the moment a GraphRAG system leaves the prototype stage: how do you keep a Postgres vector store and a Neo4j graph store consistent when they are updated independently, and how do you tune and partition a pgvector index so that search latency holds under a 250ms p95 budget at 10 million source objects and 20-40 million chunks. These are not independent problems - the way you shard and partition your vector store directly affects how hard consistency becomes, and vice versa. We'll walk through both with concrete configuration guidance and code.

Why Dual-Store GraphRAG Diverges

A single-store RAG system has one simple consistency model: a row is either committed or it isn't. The moment you split retrieval across Postgres and Neo4j, you introduce a distributed systems problem disguised as a search feature. Document ingestion typically writes chunk embeddings and metadata into Postgres, then derives entities and relationships that get written into Neo4j as nodes and edges. These two writes are not atomic. If the pipeline crashes between them, or if the Neo4j write succeeds but a retry duplicates the Postgres write, your graph and your vector index tell two different stories about the same document.

The divergence gets worse with updates and deletes. Re-ingesting a changed document means updating or deleting chunks in Postgres and updating the corresponding entity relationships in Neo4j. If a chunk is deleted from Postgres but its entity mentions remain in Neo4j, your retrieval pipeline can return graph context for content that no longer exists - a particularly insidious failure mode because it doesn't throw an error, it just quietly returns wrong context to the LLM. In a production GraphRAG system ingesting documents continuously, this kind of silent drift compounds over weeks if there's no reconciliation mechanism.

The root cause is architectural: you have two independent transactional boundaries. Postgres gives you ACID guarantees within itself; Neo4j gives you ACID guarantees within itself. Nothing gives you ACID guarantees across both unless you build it. Teams that treat this as an afterthought typically discover it the hard way - a demo works perfectly because test data is static, and then three months into production someone notices that entity counts in Neo4j don't match document counts in Postgres, with no clear audit trail of when or why.

Index Scaling: Tuning pgvector for 10M Objects and 40M Chunks

At the scale described in the problem - 10 million source objects producing 20 to 40 million chunks - you are well past the range where naive CREATE INDEX defaults will hold a 250ms p95. The first decision is which index type to use. pgvector supports both IVFFlat and HNSW; for this scale, HNSW is the right default because it offers better recall-per-latency and doesn't require periodic retraining of cluster centroids the way IVFFlat's lists parameter does as the dataset grows. HNSW (Hierarchical Navigable Small World), introduced by Malkov and Yashunin, builds a multi-layer proximity graph that supports logarithmic-ish search complexity, and pgvector has supported it since version 0.5.0.

The two build-time parameters that matter most are m and ef_construction. m controls the maximum number of connections per node per layer - higher values improve recall at the cost of memory and build time, with 16 as a reasonable default and 24-32 worth testing if recall is the bottleneck rather than latency. ef_construction controls how wide the candidate list is during index construction; typical production values sit between 64 and 200. At 40 million vectors with a 1536-dimension embedding (OpenAI's text-embedding-3-small or similar), raising ef_construction much above 100 produces diminishing recall gains while adding hours to build time, so most teams land in the 80-128 range and validate empirically against a recall benchmark rather than guessing.

Build time and memory are where this scale actually bites. An HNSW index on tens of millions of high-dimensional vectors needs maintenance_work_mem set well above the Postgres default - commonly 8-16GB or more during index builds - and pgvector 0.6+ combined with PostgreSQL 16's parallel index build support lets you use max_parallel_maintenance_workers to cut build time from many hours to a more tractable window. Expect multi-hour builds at this scale on reasonably provisioned hardware; this is not something you want running against a live index without planning for it, which is why most teams build on a replica or during a maintenance window and swap via CONCURRENTLY where supported.

At query time, the parameter that actually controls your latency/recall tradeoff is hnsw.ef_search, set per-session or per-query. The default of 40 is often too low for high-recall GraphRAG retrieval; raising it to 100-200 improves recall but adds latency. The practical approach is to treat ef_search as a tunable dial you set based on observed p95 under realistic concurrent load, not a value you pick once from documentation:

-- Index build: run on a replica or during a maintenance window
SET maintenance_work_mem = '16GB';
SET max_parallel_maintenance_workers = 7;

CREATE INDEX CONCURRENTLY idx_chunks_embedding_hnsw
ON chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 96);

-- Query time: tune ef_search based on observed p95, not defaults
SET hnsw.ef_search = 120;

SELECT id, document_id, content,
       embedding <=> $1 AS distance
FROM chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 20;

One detail teams miss: a filtered query like the one above (WHERE tenant_id = $2) can defeat the HNSW index's efficiency if the filter is highly selective, because HNSW traversal happens before the filter is applied in most pgvector versions, wasting candidate slots on rows that get discarded. For multi-tenant GraphRAG systems with highly skewed tenant sizes, this is often the actual cause of p95 blowouts rather than the index configuration itself - which is exactly why partitioning strategy and index tuning have to be designed together rather than sequentially.

Partitioning Without Degrading Search Latency

Partitioning a vector table at this scale is less about data volume per se and more about keeping each partition's working set small enough that its HNSW graph stays cache-resident and query-local. The most common and effective strategy for multi-tenant GraphRAG is to partition by tenant_id using PostgreSQL's native declarative partitioning (hash or list partitioning), then build a separate HNSW index per partition. This turns a single 40-million-row index into N smaller indexes, each sized to its tenant's data, which keeps build times manageable per partition and lets you rebuild one tenant's index without touching others.

The tradeoff is query routing complexity. PostgreSQL's partition pruning works well when the partition key appears in the WHERE clause, which it naturally does in a tenant-scoped query - the planner only touches the relevant partition's index, so a single-tenant query against a 40-million-row partitioned table behaves like a query against that tenant's much smaller slice. Where this breaks down is cross-tenant or global search, which requires fanning out across partitions and merging results, adding real latency. If your product requires frequent cross-tenant search, partitioning by tenant works against you and a different key (such as time-based partitioning for recency-biased retrieval, or no partitioning combined with a distributed Postgres layer like Citus) is worth evaluating instead.

For object counts beyond what a single Postgres instance comfortably handles, horizontal sharding via Citus (now part of the Postgres ecosystem and available as an extension) distributes both the table and its HNSW indexes across worker nodes, with pgvector operations supported on Citus-distributed tables as of recent Citus releases. This adds operational complexity - you're now running a distributed database - but it's the correct next step when a single-node partitioned table stops being enough, rather than trying to force one very large machine to hold everything.

A second, often underestimated lever is simply reducing what has to be searched. Pre-filtering by recency, document type, or a coarse category before the vector search - using a B-tree index on a metadata column combined with the vector index - can shrink the effective search space dramatically. Combined with partition pruning, this is usually more impactful on p95 latency than any single HNSW parameter tweak, because it reduces the problem size rather than just searching the same problem faster.

Implementation: Reconciliation as a First-Class Process

Given that atomic cross-store writes aren't achievable without a distributed transaction coordinator (which most teams correctly avoid for performance reasons), the practical answer is an outbox pattern combined with asynchronous reconciliation. The ingestion service writes chunks and an "ingestion event" to Postgres in a single transaction. A separate consumer - using Debezium's Postgres connector for change data capture, or a simpler polling worker for lower-scale deployments - reads that event and performs the corresponding Neo4j write, retrying on failure with idempotent upserts (MERGE in Cypher) rather than blind inserts.

import asyncio
from neo4j import AsyncGraphDatabase
import asyncpg

async def reconcile_batch(pg_pool, neo4j_driver, batch_size: int = 500):
    """
    Polls the outbox table for unprocessed graph-sync events
    and applies them idempotently to Neo4j.
    """
    async with pg_pool.acquire() as conn:
        events = await conn.fetch(
            """
            SELECT id, document_id, entity_payload
            FROM graph_sync_outbox
            WHERE processed_at IS NULL
            ORDER BY created_at
            LIMIT $1
            FOR UPDATE SKIP LOCKED
            """,
            batch_size,
        )

    async with neo4j_driver.session() as session:
        for event in events:
            await session.execute_write(
                _apply_entity_merge, event["document_id"], event["entity_payload"]
            )
            async with pg_pool.acquire() as conn:
                await conn.execute(
                    "UPDATE graph_sync_outbox SET processed_at = now() WHERE id = $1",
                    event["id"],
                )

async def _apply_entity_merge(tx, document_id, payload):
    await tx.run(
        """
        MERGE (d:Document {id: $document_id})
        WITH d
        UNWIND $entities AS entity
        MERGE (e:Entity {name: entity.name, type: entity.type})
        MERGE (d)-[:MENTIONS]->(e)
        """,
        document_id=document_id,
        entities=payload["entities"],
    )

FOR UPDATE SKIP LOCKED matters here - it lets multiple reconciliation workers run concurrently without stepping on each other, which becomes necessary once ingestion volume grows beyond what a single worker can drain. The MERGE statements in Cypher make the Neo4j side idempotent, so a replayed event after a crash doesn't create duplicate nodes or edges - the single most important property for any retry-based consistency mechanism.

On top of the outbox-driven sync, a scheduled drift-detection job should periodically compare document counts and chunk-to-entity ratios between the two stores and alert when they diverge beyond an expected tolerance, since outbox processing can silently stall (a stuck consumer, a Neo4j connection pool exhaustion) without raising an obvious error elsewhere in the system.

Trade-offs and Pitfalls

The biggest trade-off in this architecture is between consistency strength and system complexity. Full synchronous two-phase commit across Postgres and Neo4j is technically possible but rarely worth it - it couples the availability of your vector search to the availability of your graph store, which defeats much of the benefit of having two specialized systems in the first place. Eventual consistency via the outbox pattern accepts a small window where the graph lags behind the vector store, in exchange for each store being independently scalable and independently recoverable from failure. For GraphRAG specifically, this is almost always the right trade, because a few seconds of graph lag rarely changes the correctness of an answer, whereas coupling the two stores' availability does.

A second pitfall is over-indexing on HNSW parameter tuning while ignoring the shape of the query workload. Teams often chase recall benchmarks on ef_construction and ef_search without first establishing whether their actual latency problem comes from the vector search step at all - in GraphRAG pipelines, multi-hop Cypher traversals in Neo4j can easily dominate end-to-end latency, especially with unbounded path lengths or missing relationship-type indexes. Measuring the full retrieval path with distributed tracing before optimizing either store in isolation avoids a common and expensive mistake: spending a sprint hand-tuning pgvector when the actual p95 offender is an unindexed graph traversal.

Best Practices for Production GraphRAG

Treat the outbox table itself as a capacity-planned component, not an afterthought - set a retention and archival policy for processed events, and monitor queue depth as a leading indicator of sync lag rather than waiting for a downstream consistency check to catch it. Teams that skip this discover, usually during an incident, that their outbox table has grown to tens of millions of rows and is itself causing write amplification on the primary ingestion path.

Benchmark HNSW parameters against your actual embedding model and query distribution rather than published defaults, since recall/latency curves shift meaningfully with embedding dimensionality and the semantic density of your corpus. Run build-time and query-time parameter sweeps on a representative subset - a few million chunks is usually enough to extrapolate behavior at 40 million - before committing to a production configuration, and re-validate whenever you change embedding models, since different models produce different vector geometries that shift optimal m and ef_construction values.

Finally, design the reconciliation job to be re-runnable from scratch, not just incremental. At some point - a schema migration, a corrupted Neo4j backup restore, a bulk re-ingestion - you will need to rebuild the graph store's state entirely from Postgres as the source of truth. If your sync mechanism can only apply incremental deltas, a full rebuild becomes a bespoke one-off script written under pressure. If it's built as an idempotent replay of the same MERGE-based logic from the start, a full rebuild is just running the reconciliation job against the entire chunk history instead of only new events.

Key Takeaways

Conclusion

GraphRAG's value comes precisely from combining two fundamentally different retrieval models, but that combination is also where its operational difficulty lives. Index tuning and partitioning in Postgres solve the vector search half of the latency budget, while an idempotent, asynchronous reconciliation process solves the consistency half - and neither can be treated as solved in isolation, because the partitioning strategy you choose shapes how hard consistency becomes, and the consistency mechanism you choose shapes how safely you can rebuild or repartition later. Teams that plan for eventual consistency and benchmark-driven index tuning from the start tend to scale smoothly; teams that treat dual-store sync as a detail to fix later tend to discover the divergence in production, usually at the worst possible time.

References