--- title: "Durable Analytics rollout, ClickHouse backfill, and rollback — Analytics 0.1 documentation" description: "PostgreSQL raw is the authoritative event log. The ingress process does only one thing: commit each backend event to that log. PostgreSQL-derived tables and ClickHouse are independent, restartable consumers. This means…" source_url: https://spiff.works/docs/analytics/clickhouse-rollout.html --- ☰ Menu # Durable Analytics rollout, ClickHouse backfill, and rollback[](#durable-analytics-rollout-clickhouse-backfill-and-rollback "Link to this heading") PostgreSQL `raw` is the authoritative event log. The ingress process does only one thing: commit each backend event to that log. PostgreSQL-derived tables and ClickHouse are independent, restartable consumers. This means a consumer deployment, ClickHouse outage, or initial backfill does not restart the TCP listener and cannot discard an in-memory fan-out queue. The controls are deliberately independent: - `SPIFFWORKFLOW_BACKEND_EVENT_STREAM_ACK_ENABLED` is an Arena/backend setting. It enables the database-agnostic acknowledged event protocol; Arena does not know which analytical warehouse consumes the durable log. - `SPIFFWORKFLOW_ANALYTICS_CLICKHOUSE_ENABLED` starts the Analytics ClickHouse consumer. - `ANALYTICS_DATA_BACKEND` selects API reads and defaults to `postgres`. In infra these are managed by `spiffworkflow_analytics_clickhouse_enabled` and `spiffworkflow_analytics_data_backend`; changing the latter recreates only the Analytics API. Ingress and both consumers connect directly to PostgreSQL because their schema and singleton advisory locks are session-scoped. The query API may continue through transaction-pooling PgBouncer. ## Delivery guarantees and limits[](#delivery-guarantees-and-limits "Link to this heading") In ACK mode the backend gives every event a UUID, negotiates protocol version 2, and waits for `ACK `. Ingress sends that ACK only after the raw event and its UUID receipt commit in one PostgreSQL transaction. If the connection breaks before the ACK, the backend reconnects and retries the exact same UUID. `event_receipt.event_id` makes that retry idempotent. An established TCP connection cannot move between processes. During an ingress replacement it will close and reconnect, but an ACK-mode backend retains the unacknowledged event, so the interruption does not lose it. The synchronous retry can add request latency while ingress or PostgreSQL is unavailable; that backpressure is intentional. This does not preserve an event across simultaneous backend process death before acknowledgement. A transactional Arena outbox would be required for that stronger guarantee. ## Upgrade order from the legacy dual writer[](#upgrade-order-from-the-legacy-dual-writer "Link to this heading") Keep API reads on PostgreSQL throughout these steps. 1. Set `SPIFFWORKFLOW_ANALYTICS_CLICKHOUSE_ENABLED=false`. Leave backend ACK mode disabled. 2. Apply the event-monitoring PostgreSQL migration from the new image while the old listener remains running, then deploy the image. Adding `raw.id` takes a PostgreSQL table lock and builds its primary-key index; measure this on a production-sized copy and schedule the one-time operation appropriately. The old listener’s in-memory PostgreSQL writer retains events while an insert is blocked by the DDL, provided that listener is not restarted during the migration. After pulling/selecting the new image tag without recreating the old container: ``` docker compose run --rm --no-deps event-listener python manage_db.py migrate ``` Verify that `raw.id`, `event_receipt`, and `consumer_cursor` exist before replacing ingress. Legacy schema files recorded their database creation date as `last_schema_update`; the current migrator detects and repairs that misleading version, but this verification also catches an old image or an incomplete migration before traffic is affected. 3. Compose now runs `event-listener` as stable ingress and `analytics-consumer` separately. Confirm ingress port `8006` and metrics `9101`; confirm consumer metrics `9103` even though its ClickHouse child is disabled. 4. Deploy the ACK-capable Arena backend with `SPIFFWORKFLOW_BACKEND_EVENT_STREAM_ACK_ENABLED=false`. This is wire-compatible with both listener versions. 5. Replace/backfill the existing shadow ClickHouse store as described below. 6. Enable the ClickHouse consumer and verify it reaches zero lag. 7. Set `SPIFFWORKFLOW_BACKEND_EVENT_STREAM_ACK_ENABLED=true` and deploy only the backend. Confirm ingress duplicate-retry and failure metrics remain healthy. 8. Run parity checks. Switch API reads only after the observation period succeeds. The first replacement of a legacy listener is the only transition without ACK protection. Quiesce event-producing requests for that one deployment if the environment cannot tolerate that residual window. Later consumer deployments never touch ingress; later ingress deployments are protected by ACK/retry. ## Online initial load or replacement[](#online-initial-load-or-replacement "Link to this heading") The backfill and live ClickHouse consumer take the same PostgreSQL advisory lock. Ingress does not take it and continues committing new raw events throughout the load. Run a read-only preflight with a direct PostgreSQL connection rather than PgBouncer: ``` docker compose run --rm --no-deps \ -e PGHOST=spiffdb -e PGPORT=5432 \ analytics-consumer python backfill_clickhouse.py \ --workers 2 --chunks 128 --ch-send-receive-timeout 7200 --dry-run ``` For a new empty ClickHouse store: ``` docker compose run --rm --no-deps \ -e PGHOST=spiffdb -e PGPORT=5432 \ analytics-consumer python backfill_clickhouse.py \ --workers 2 --chunks 128 --ch-send-receive-timeout 7200 ``` For a shadow store populated by the old direct dual writer, stage a fresh copy and atomically replace it: ``` docker compose run --rm --no-deps \ -e PGHOST=spiffdb -e PGPORT=5432 \ analytics-consumer python backfill_clickhouse.py \ --workers 2 --chunks 128 --ch-send-receive-timeout 7200 \ --restart --replace-existing ``` The command acquires the consumer lock, captures `max(raw.id)` as a cutoff, loads disjoint ranges into `raw_events_backfill`, verifies the live target did not change during the load, and exchanges the raw tables. It reconstructs each layer-2 aggregate replacement from bounded raw-ID chunks before exchanging that table. It then builds the versioned serving tables from bounded raw-ID chunks and advances their durable cursor only after both process and task publication succeed. Ingress may add IDs above the cutoff at any time. When the lock is released, the consumer resumes from ClickHouse’s maximum `postgres_raw_id` and copies and publishes every later event. `--workers` bounds simultaneous PostgreSQL-to-ClickHouse queries; `--chunks` creates smaller, progress-visible units without increasing that concurrency. The two-worker, 128-chunk form is the conservative default for a large shared single-server environment. The longer ClickHouse client timeout applies to each chunk and avoids treating a healthy long-running query as failed. If a staging worker fails, the live table is unchanged. Fix the cause and repeat with `--restart` (and `--replace-existing` when applicable). Keep ClickHouse API reads disabled until raw exchange and both aggregation exchanges finish. ## Verification[](#verification "Link to this heading") Run the full API parity fixture: ``` ./bin/check_dual_store_parity ``` Monitor: - `spiff_analytics_clickhouse_source_head` - `spiff_analytics_clickhouse_consumer_cursor` - `spiff_analytics_clickhouse_consumer_lag_events` - `spiff_analytics_clickhouse_consumer_events_total` - `spiff_analytics_clickhouse_consumer_failures_total` - `spiff_analytics_clickhouse_consumer_last_success_unixtime` - `spiff_analytics_listener_postgres_events_inserted_total` - `spiff_analytics_listener_postgres_duplicate_events_total` - `spiff_analytics_listener_postgres_insert_failures_total` - API query latency/errors by backend and query - Prometheus `up` for listener, consumer, API, and ClickHouse After deploying an image that changes an incremental raw-to-aggregate materialized view or aggregation-table definition, keep reads on PostgreSQL, stop `analytics-consumer`, and rebuild the aggregate tables before restarting it: ``` docker compose stop analytics-consumer docker compose run --rm --no-deps \ -e PGHOST=spiffdb -e PGPORT=5432 \ analytics-consumer python backfill_clickhouse.py --rebuild-aggregations docker compose up -d analytics-consumer ``` `docker compose run` creates a one-off container from the stopped service’s new image and configuration. It does not start the normal consumer service. If the rebuild exits nonzero, leave API reads on PostgreSQL and investigate before starting the consumer or allowing ClickHouse reads. The rebuild uses the same singleton lock and atomically exchanges each completed layer-2 replacement table. Its default reconstruction queries cover at most 500,000 raw IDs, use at most two ClickHouse threads, and have a 2 GiB per-query memory ceiling. Those bounds are independently adjustable by the backfill CLI. A failed chunk leaves the live aggregate untouched. ## Incremental serving publication and freshness[](#incremental-serving-publication-and-freshness "Link to this heading") Schema versions 2 and 3 introduced query-ordered snapshots and precomputed durations. Their refreshable materialized views recopied every process and task each minute, which is unsafe at production scale. Schema version 4 drops those refresh jobs but deliberately retains their last target tables as a rollback snapshot. Schema 4 adds query-ordered `ReplacingMergeTree` serving tables and `serving_publication_state`. For each bounded raw-ID range the publisher: 1. finds the process and task IDs touched by that range; 2. writes tombstones for their prior serving rows; 3. finalizes only those IDs from the layer-2 aggregate state; 4. writes the replacement rows at a higher deterministic version; and 5. advances the serving cursor only after both entity tables succeed. A retry uses the same versions, so interruption before cursor advancement is idempotent. Tombstones also handle the uncommon correction that changes an entity’s model or start-time sorting key. Public views use `FINAL` and filter tombstones while retaining model/time ordering for dashboard queries. Schema version 5 makes event time the primary process/task state ordering key and uses PostgreSQL raw ID only to break same-millisecond ties. This prevents older task events that were inserted after a process-completion event from reopening the completed process. It also removes tasks without a valid process instance from the finalized layer-2 view. Existing layer-2 rows retain their old aggregate states until `--rebuild-aggregations` completes; use the bounded rebuild procedure above before enabling ClickHouse API reads. Schema version 6 exposes a task terminal timestamp only when its latest state is `COMPLETED`, `ERROR`, or `CANCELLED`. Tasks that recover from an error no longer retain a stale end time or completed duration. The migration changes only the finalized layer-2 view; republish layer 3 to correct existing serving rows. It also adds a process-instance skipping index to the task serving table so task queries can follow the process-window contract without scanning unrelated task rows. Schema version 7 preserves the raw event timestamp’s microsecond remainder inside the existing aggregate ordering key. This resolves task transitions that occur within one millisecond but reach PostgreSQL in the opposite order. PostgreSQL raw ID still breaks genuinely identical timestamp ties. Existing layer-2 aggregate states must be rebuilt before ClickHouse API reads are enabled; the bounded aggregation rebuild and clean-volume enablement paths both apply the corrected ordering. Schema version 8 replaces the layer-3 task table with a layout ordered by process model, process instance, task start time, and task ID. Detail queries first select process instances by the process window and can therefore prune directly to those instances instead of scanning every historical task for the model. The migration leaves the complete legacy table and public view intact but clears serving publication state. Keep API reads on PostgreSQL and run a serving rebuild before enabling ClickHouse reads. After the replacement has been populated, validated, and made public, the publisher drops the legacy layer-3 task table. This rebuild does not recopy raw events or reconstruct layer 2. A clean ClickHouse volume also receives the latest schema, but it must recopy every raw event from PostgreSQL before performing the same aggregate and serving builds. Retaining a healthy volume is therefore normally faster and preserves the durable raw layer. A clean-volume run is useful as an explicit installation test, not as a required upgrade step. The migration does not point public views at empty new tables. Initialize and validate them while API reads remain on PostgreSQL: ``` docker compose stop analytics-consumer docker compose run --rm --no-deps \ -e PGHOST=spiffdb -e PGPORT=5432 \ analytics-consumer python backfill_clickhouse.py --publish-serving docker compose up -d analytics-consumer ``` Use `--restart-serving` only to discard and recreate the new serving tables. That clean-start path snapshots the already-finalized layer-2 process and task tables directly, with one thread, a bounded memory limit, and external sort spilling by default. It does not replay every raw-ID range. Without `--restart-serving`, `--publish-serving` resumes from the durable serving cursor. The full initial backfill and `--rebuild-aggregations` initialize serving publication automatically. If the direct snapshot exceeds a server limit, the publisher clears its partial rows and automatically falls back to the slower bounded raw-ID path. After initialization, every live raw insert updates layer 2 synchronously and the consumer publishes touched entities after accumulating 50,000 raw IDs or waiting 10 seconds, whichever comes first. Publication uses one thread and a 2 GiB query ceiling by default. The batching threshold and flush interval are configurable with `SPIFFWORKFLOW_ANALYTICS_CLICKHOUSE_SERVING_BATCH_SIZE` and `SPIFFWORKFLOW_ANALYTICS_CLICKHOUSE_SERVING_FLUSH_INTERVAL_SECONDS`. The API reports `serving_uninitialized` until initial publication is ready and `serving_stale` whenever the serving cursor trails the ClickHouse raw cursor. Completed durations remain stored. Public views calculate the moving duration for only the filtered active processes and unfinished tasks at query time, so they no longer require periodic full-table rewrites. Monitor these consumer metrics in addition to raw-log lag: - `spiff_analytics_clickhouse_serving_publish_cursor` - `spiff_analytics_clickhouse_serving_publish_lag_events` - `spiff_analytics_clickhouse_serving_publish_ready` - `spiff_analytics_clickhouse_serving_publish_last_success_unixtime` - `spiff_analytics_clickhouse_serving_publish_duration_seconds` - `spiff_analytics_clickhouse_serving_publish_batches_total` An older consumer refuses to run against a newer schema before changing any ClickHouse objects; leave the latest consumer running or disable ClickHouse ingestion during an application rollback. The durable PostgreSQL event log, layer-2 aggregate data, and legacy serving snapshot are unaffected. The Grafana **Spiff Analytics: PostgreSQL vs ClickHouse** dashboard and Alertmanager rules cover scrape health, durable consumer lag/stalls/failures, query performance, and ClickHouse merge/part pressure. ## Routine deployment and rollback[](#routine-deployment-and-rollback "Link to this heading") Deploying `analytics-consumer` restarts only derived-table and ClickHouse consumers. Ingress and its backend TCP connections stay untouched. Advisory locks make overlapping old/new consumer instances safe. To roll back reads, set `ANALYTICS_DATA_BACKEND=postgres` and restart only the Analytics API. To pause ClickHouse ingestion, set `SPIFFWORKFLOW_ANALYTICS_CLICKHOUSE_ENABLED=false` and restart only `analytics-consumer`; PostgreSQL ingress continues. Re-enabling the consumer resumes from its durable raw-ID boundary.