ClickHouse Backfill#

ClickHouse backfill refers to a family of long-running background migrations that copy historic observations and traces data into the new events_full table as part of Langfuse's V4 events-based architecture 1. Two generations of implementation exist:

1. Early ad-hoc scripts (backfillEventsHistoric.ts, backfillEventsHistoricFromParts.ts) — shipped to handle specific time windows before the production chain was built. These are now deleted as part of the V4 chain merges 2.
2. V4 chain (M1–M5) — a structured five-step migration introduced in PR #14206, built on the ChunkedClickhouseBackfillMigration base class. All steps are dormant by default and activated via env gates (LANGFUSE_BACKGROUND_MIGRATION_V4_ENABLE_HISTORIC_BACKFILL, LANGFUSE_BACKGROUND_MIGRATION_V4_DROP_PID_TID_SORTING_TABLES).

The chain steps execute sequentially, each guarded by a checkPredecessorMigrationFinalized dependency check 3:

Fire-and-Poll Pattern#

Both the old scripts and the V4 chain use the same fundamental mechanism for long-running ClickHouse INSERT … SELECT queries. This pattern is documented with a reference to the ClickHouse JS examples.

Sequence 4:

1. Fire: Call commandClickhouse() with an AbortController signal and a pre-generated queryId passed as queryId on the request. The query is dispatched and the rejection handler is attached in the same tick (to avoid unhandled-rejection crashes on fast errors — a reliability fix in the V4 base class).
2. Confirm start: After an initial wait (default 10 s), check system.processes via pollQueryStatus(queryId). If not_found, wait an additional 30 s and check again (ClickHouse's query_log flush interval is ~7.5 s plus replication lag).
3. Abort HTTP connection: Once confirmed running, call abortController.abort(). The query continues executing server-side.
4. Poll for completion: The scheduler loop calls pollQueryStatus() on each active queryId at every pollIntervalMs (default 30 s), up to concurrency queries in parallel (default 1 in V4 base class, 4 in old scripts).

Query ID format: generateQueryId produces backfill-{chunkId}-{8-char-uuid}, making active queries human-readable in system.processes.

Timeout bypass: The base class sets max_execution_time: 0 and timeout_before_checking_execution_speed: 0 on each fire-and-poll query so the shared client's derived server-side cap (~35 s) does not kill long chunk queries after the abort 5. The old scripts left this unset, meaning they relied on the client's default request_timeout not applying after abort.

Retry settings: On retry attempts, max_block_size, max_threads, and max_insert_threads are progressively reduced to avoid OOM on large partitions 6 7.

Scheduler differences: The old scripts used a ConcurrentQueryManager with setInterval, creating potential data races (three concurrent paths writing background_migrations.state). The V4 base class replaced this with a single sequential poll loop that is the sole writer of state, eliminating the race 8.

Status Tracking via System Tables#

All status tracking is centralized in packages/shared/src/server/clickhouse/queryTracking.ts.

pollQueryStatus(queryId) performs a two-stage lookup 9:

1. SELECT query_id FROM clusterAllReplicas('default', 'system.processes') WHERE query_id = {queryId} — returns "running" if found. Uses a 60 s request_timeout with skip_unavailable_shards: 1.
2. SELECT type, exception_code FROM clusterAllReplicas('default', 'system.query_log') WHERE query_id = {queryId} — maps:
   • QueryFinish → "completed"
   • ExceptionBeforeStart / ExceptionWhileProcessing / non-zero exception_code → "failed"
   • QueryStart → "running" (still flushing)
   • empty result → "not_found"

getQueryError(queryId) retrieves the full exception text from system.query_log for failed queries 10. Used to populate todo.error before marking a chunk as permanently failed.

Recovery on restart: On worker restart, recoverInProgressTodos iterates persisted in-progress chunks and calls pollQueryStatus to classify each: re-attach "running" queries to the active map, mark "completed" ones done, reset "failed" and "not_found" to pending 11.

Cluster note: Both system.processes and system.query_log queries use clusterAllReplicas('default', ...). The cluster name is hardcoded as 'default' in queryTracking.ts 12, independent of the CLICKHOUSE_CLUSTER_NAME env var used for DDL by the V4 chain.

V4 Chain: ChunkedClickhouseBackfillMigration Base Class#

The ChunkedClickhouseBackfillMigration<T> abstract class 13 is the production foundation. Subclasses implement:

• enumerateChunks(config) — returns the list of ChunkTodos (partitions or parts discovered from system.parts)
• buildChunkQuery(todo) — returns { query, params } for the per-chunk INSERT
• afterTablesValidated() (optional hook) — runs after required tables exist; M2 uses this for lazy scratch-table DDL: CREATE TABLE IF NOT EXISTS observations_pid_tid_sorting [ON CLUSTER ...]
• verifyCompletedChunk(todo) (optional hook) — post-INSERT verification; the parts-based variant checks the source part still exists in system.parts after processing 14
• onBackfillSucceeded(state) (optional hook) — M2 issues SYSTEM STOP MERGES then SYSTEM SYNC REPLICA STRICT here to freeze and converge the scratch table's part layout before M3 reads system.parts
• onBackfillFailed(state) (optional hook) — cleanup on permanent failure

Database qualification: loadPartitionsFromClickhouse() and detectTableEngine() use database = currentDatabase() rather than hardcoded 'default' 15, making the queries portable across database names.

Cluster awareness: onClusterClause() returns ON CLUSTER <name> when CLICKHOUSE_CLUSTER_ENABLED=true; detectTableEngine() inspects system.tables to branch between SharedMergeTree (ClickHouse Cloud), ReplicatedMergeTree (self-hosted HA), and plain MergeTree (single-node), adapting DDL and SYSTEM commands accordingly 16.

State persistence: Phase (init → loading_chunks → backfill → completed), todos, and active query IDs are serialized as JSON into background_migrations.state (Postgres) on every transition, enabling crash recovery 17.

Known Gaps in the Early Scripts#

The early scripts (backfillEventsHistoric.ts, backfillEventsHistoricFromParts.ts) have several gaps relative to the V4 chain. These files were deleted in PR #13623 2 but may appear in git history or serve as reference for the pattern.

Missing database-qualified table names: Query validation checks in the early scripts use bare table names (e.g., SELECT count() as count FROM ${table} LIMIT 1) without database = currentDatabase() scoping 18 19. If ClickHouse has multiple databases, this relies on the connection's default database being correct. The V4 base class's loadPartitionsFromClickhouse and detectTableEngine use currentDatabase() consistently.

No table lifecycle management: The early scripts have no CREATE TABLE, DROP TABLE, SYSTEM STOP MERGES, or SYSTEM SYNC REPLICA. The V4 chain's M2 manages the scratch table observations_pid_tid_sorting end-to-end (lazy DDL via afterTablesValidated, freeze via onBackfillSucceeded, cleanup via M5). The early scripts assumed all tables pre-existed and never froze the source layout.

ConcurrentQueryManager data race: The old setInterval-based manager and scheduleNext/onComplete closures created three concurrent writers of background_migrations.state (load-modify-save paths). The V4 base class eliminates this with a single sequential while loop 8.

recoverInProgressTodos maxRetries gap (early V4): A Greptile review on PR #14206 flagged that when a recovered query is found in "failed" state, retryCount is incremented and the chunk is reset to "pending" without checking whether the new count exceeds config.maxRetries — allowing one extra attempt beyond the configured limit 8.

m.partition_id vs _partition_id (early V4 M3): PR #13623's Greptile review identified that the M3 traces subquery used t.partition_id (not a real column) instead of the virtual column t._partition_id, which would cause ClickHouse to throw "Missing columns" on every chunk 2. This was fixed before the PR landed.

Key Source Files#

Related PRs:

• PR #14206 — Production V4 chain with ChunkedClickhouseBackfillMigration base class, single-loop scheduler, and all five migration steps
• PR #13623 — Earlier V4 chain draft; introduces the env-gate mechanism and removes the legacy scripts