All five editions serving market data from ClickHouse as of October 2026. Readers switch to a snapshot only after verification, and interrupted writes can be reconciled. Four editions have recorded archive restore rehearsals; Retail’s archive was checksum-verified but not restore-tested.
I migrated BootyBayBroker’s five game editions from separate TimescaleDB stores to a shared ClickHouse core while preserving the meaning of every price.
Price history, current listings and derived market views now share one read contract. ClickHouse stores market facts and computes the history, screeners, trading-hour views and cross-realm comparisons. PostgreSQL keeps accounts, the item catalog and collection state that need transactions, uniqueness or locks. Redis coordinates publication and distributes changes to readers.
A column store suits the aggregations, but asking it for each individual price would put a database round trip back into every page. Each web replica therefore keeps compact current-price books in memory, with a byte budget and one shared load per market and generation.
A partially written batch must not become the current market.
The writer sorts rows into deterministic chunks and gives each insert a stable deduplication token. A retry checks what landed before sending more. Partial batches can be replayed while their tokens remain valid; older incomplete writes are flagged instead of blindly reinserted. Verification checks row counts and numeric sums before publication. A mismatch leaves the current pointer alone.
Every price book belongs to a market and a generation. Publication allocates a number greater than both the previous value and the durable head, so a Redis restart cannot reuse an old cache namespace.
Readers ignore lower generations. The books use sorted typed arrays for compact storage and binary-search lookup; a caller canceling its wait does not cancel a load that other requests still need.
A collector can finish writing facts and lose its connection before publication. A reconciler completes pending batches and checks for published snapshots whose market head has fallen behind.
Head updates use a per-market Redis lease. If coordination is unavailable, publication waits; it does not fall back to a process-local lock that another replica cannot see. Reconciliation also repairs a lagging head after a lost lease.
The data model records edition, market, source and observation time. Database constraints and the writer both reject a source paired with the wrong edition. Shared read views preserve price-selection rules, rounding, weights and missing values. Daily aggregates keep mergeable integer sums, so combining groups does not turn a weighted price into an average of averages.
I introduced dual writes, backfilled historical data, and compared old and new read results in shadow before switching each surface. Those comparisons covered the values clients actually consume: history, listings, market boards, search, price delivery and alerts. Expected differences had to be declared; an unexplained mismatch was a migration problem to resolve.
The tests exercise exact numeric round trips, missing prices, duplicate inserts, interrupted publication, stale generations, concurrent cache loads and byte-budget eviction. Migration records separately track write stops, archive checksums and restore checks. Four editions have recorded passing restore rehearsals; Retail’s archive was checksum-verified but not restore-tested.
| Edition | Archive checksum | Restore rehearsal |
|---|---|---|
| WoW Forever | Verified | Passed October 23 |
| Season of Discovery | Verified | Passed October 23 |
| Mists of Pandaria Classic | Verified | Passed October 23 |
| The Burning Crusade Classic | Verified | Passed October 23 |
| Retail | Verified October 25 | Not run |
Source: storage retirement verification record. This is a redacted summary of the recorded checks; infrastructure identifiers are omitted. Retail recovery remains unproven by a restore rehearsal.
One data core removes five parallel storage paths, but concentrates failure in one ClickHouse server. Warm price books can keep answering current-price lookups during a short interruption; uncached reads and aggregations still depend on the database. Backups and restore work remain necessary.