Two stores that earned their place
The hotel identity data lives in a relational table: around 1.9 million rows, about 46 supplier-ID columns, one row per hotel the platform knows about. The question the business asks of it sounds simple — is supplier A's hotel 123 the same building as supplier B's hotel 456? — but identity is transitive. A matches B, B matches C, therefore A matches C, through a chain whose length you do not know in advance. Model that as a pairwise join table and every transitive lookup becomes a self-join of unknown depth: you keep joining the table to itself until nothing new appears. Relational engines are built for joins of known shape, not for "keep going until the frontier is empty".
So the lookups run over a graph store — Neo4j, variable-depth traversal — while the rows themselves stay in the relational table. Same reasoning, a job earlier, put a search index next to a transactional store: the queries a search box asks — fuzzy text, faceting, ranking — are questions a row store answers badly at any size worth having.
Both decisions were right. I would make both again. That is exactly why they are worth writing about, because the cost is easy to leave out of the story.
The honest cost
Each derived store is a copy, and a copy is a debt with three repayments that never stop coming due.
How is it rebuilt from source? The relational table is the truth; the graph mirrors it. The day the mirror is corrupted, or the schema shifts, or you simply stop trusting it, you need a path that regenerates the whole thing from the rows. That path is code, it must be written, and it must still run — a rebuild script that worked at half the current row count is a hypothesis, not a tool.
How do you detect that it has drifted? A mirror does not announce that it is behind. A row updated in the table and not in the graph produces no error anywhere — just a lookup that quietly answers from yesterday. Something has to compare the two on a schedule, or count what should be equal on both sides. How far the graph has ever actually drifted from the table, I cannot tell you — we did not measure it continuously, and that admission is part of the debt's carrying cost too: an unmeasured drift is not the same thing as zero drift.
What does the system do while it is behind? During a rebuild, or a lag, queries still arrive. Serve stale answers, serve partial answers, or refuse — somebody has to pick one, deliberately, before the first time it happens rather than during it.
The graph has been carrying this debt since 2022 and the arrangement still holds. But "still holds" is a statement about discipline, not about the architecture — the sync is a thing someone maintains, forever, and it never graduates into being free.
Before the next one
The questions to answer before adding a derived store, in the order I would ask them now:
- Can it be rebuilt from scratch, from source, by a script that exists? Not in principle — in a file, runnable today.
- How long does that rebuild take? For our sizes I have not timed a full rebuild recently, and that is the honest answer most teams have — which is itself the warning. A rebuild whose duration nobody knows is a recovery plan whose duration nobody knows.
- What is the user-visible behaviour while it lags? Stale, partial, or refused — chosen and written down, not discovered in an incident.
If a store cannot answer the first question, it is not a derived store — it is a second source of truth, and now there are two, which is the previous lesson on this site all over again.
What I took away
Adding a store is easy; the price is the rebuild path you have to write anyway. Write it first. If the rebuild path is too expensive to write, the store was too expensive to add — you have just learned it before the incident instead of during one.
A copy nobody audits is a copy you should assume is wrong. The only question is by how much, and an unmeasured drift is not zero.
Derived stores are bought, not gained. The graph and the index were both worth the price. The mistake is not paying it — the mistake is not noticing there was one.