QuestDB to ClickHouse Migration Framework

On the night of April 23, 2026, we discovered that QuestDB's WAL implementation had accumulated 2,810 garbage partitions in the bars_1m table containing rows with timestamps ranging from year 2027 to year 2262. These were produced by an integer overflow bug in an early version of the Rust downloader that converted millisecond timestamps to nanoseconds without bounds checking. The overflow wrapped negative numbers into astronomically large positive values, which QuestDB obediently assigned to partitions named after the overflow year.

The symptom was 10-core CPU saturation every time QuestDB restarted. The service manager would start QuestDB, it would attempt to compact 2,810+ partitions simultaneously during the WAL recovery phase, and the system would saturate within 30 seconds. Wall-clock time to finish compaction was never observed. The server was effectively unusable for any analytical query.

The deeper problem was a QuestDB WAL constraint we had not accounted for: CTAS followed by DROP TABLE on an active WAL table silently drops partition-level data that has not yet been committed to the immutable part store. This ruled out the naive approach of reading QuestDB, writing a clean version via CTAS, and swapping.

The migration executed in three sequential phases, each with explicit validation before proceeding to the next. Phase 0 was cleanup: stop the QuestDB systemd service, delete the 2,810 garbage partition directories via direct filesystem operations, restart QuestDB and verify load normalized to under 4 CPUs within 60 seconds. The garbage partitions contained no real data; they received rows with year-2047 through year-2262 timestamps produced by the bug. Deleting the directories deleted corrupt metadata, not production rows.

Phase 1 was migration: a Rust binary reads from QuestDB via its REST HTTP API in partition-sized batches, validates each batch, and writes to ClickHouse. QuestDB stays live as a read-only source throughout. Phase 2 was verification and cutover: compare row counts and aggregate checksums per table between QuestDB and ClickHouse. Only after all tables match within a 0.01% tolerance does ClickHouse become primary. QuestDB remains running in read-only mode for 72 hours as a fallback.

The migration binary checkpoints progress to a local SQLite database after each successful batch. Batches are 500,000 rows each. The checkpoint record stores the table name, partition key range, source row count, destination row count, and a SHA-256 of the first 1,000 rows' prices to detect silent data corruption during the copy phase.

The first version of the migration read QuestDB via SELECT with LIMIT/OFFSET pagination. This failed on bars_1m at 400M+ rows. The OFFSET approach requires QuestDB to scan from the beginning of the table for each page, making page N take O(N) time. Fetching page 100,000 at a 500-row page size required scanning 50M rows to return 500 rows. The migration would have taken weeks.

The correct approach used partition-key ranges rather than OFFSET. We enumerated all partition keys from the system catalog, then fetched each partition independently using a WHERE clause on the timestamp range. This reduced the largest monthly partition, October 2024 with 47M rows, from an estimated 14-hour OFFSET scan to a 23-minute sequential read.

The validation step applied three rules: timestamp must be within [2010-01-01, now() + 1 day]; OHLCV values must be non-negative and high >= low; volume must be non-negative. Any batch with a failing row was quarantined to a separate ClickHouse error table. Of 425M rows migrated, 1,315 rows triggered the timestamp guard from year-2299 corruption in bars_1d, and 4 rows triggered the OHLCV sanity check from negative volumes in a Deribit options feed.

The checkpoint mechanism was used in practice. The migration binary crashed twice during the 6.5-day run: once due to a ClickHouse OOM on a particularly dense partition, and once due to a network timeout on the QuestDB connection. Both times the binary restarted from the last committed partition checkpoint, not from the beginning of the table.

The 72-hour QuestDB read-only window was the right call. Post-cutover, three query patterns in the Next.js API layer were hitting QuestDB instead of ClickHouse due to a stale environment variable in the API server. We caught this during the 72-hour window, not via QuestDB queries, but the fallback gave us confidence to investigate deliberately rather than scrambling to restore a shut-down service.