CAPACITY NOTES
Merge two odds history stores without duplicates
Two pollers, two databases, one history. The primary key that makes the merge idempotent, and the order of operations that keeps it safe.
It happens after a migration, a second poller, or a colleague's experiment: two stores, both holding Pinnacle odds history, with overlapping coverage. The merge is safe if you lean on the one thing the storage guide designed for exactly this: a primary key that names a price observation completely, checked 2026-10-14.
The key that makes merges boring
The storage guide's price_changes table keys each row on the full identity of the observation: event, period, market, side, points and the time you observed it. Two stores that both saw the same price at the same moment hold the same key. Two stores that disagree hold different keys. That is the whole merge.
An insert that skips existing keys, INSERT ... ON CONFLICT DO NOTHING in Postgres terms, makes the merge idempotent: run it twice and the second run is a no-op.
The order of operations
Merge in three steps, and keep both sources read-only while you do. First, point both schemas at a fresh target table, so a botched run costs you nothing. Second, insert from the newer store, then from the older one, skipping conflicts; the conflict count is your overlap report. Third, run the sanity checks you already trust: row counts per day, a few spot-checks of matches you remember, and the close-capture queries the storage guide describes.
Only when the checks pass does the merged table become the source. The two old stores stay read-only until you are sure, because the merge is reversible until you delete something.
What to do with genuine conflicts
A real conflict, the same key with a different price, means the two stores disagreed about what the price was at one moment. That happens when the two pollers read different snapshots or when one of them had a parser bug. Pick the store with the better monitoring trail, log every conflict you resolve, and keep the losing row in a quarantine table rather than deleting it.
The monitoring guide's metrics are how you judge which store was healthier over the overlap window.
Keep the gaps marked
Each store has its own outage windows, and the merged history inherits both. If you recorded outages per the storage guide's advice, union the outage tables too, so a query over the merged history can still tell a quiet market from a missing one. The merged store should know what it does not know.
One key, idempotent inserts, conflicts quarantined, gaps marked. Then two histories become one, and the merge is a script you can run again next time.