Archiving Data to ClickHouse: The Proven Engineering Guide for PostgreSQL and MySQL (2026)

On this page

Selection

Which rows are candidates for archiving data to ClickHouse

The right candidates are found in the catalog, not in a meeting: large tables whose old rows are almost never written and mostly read by wide scans. On PostgreSQL the first pass is size and churn per table; the second pass is how much of each table lies beyond a candidate cutoff and when it was last written.

-- PostgreSQL 16+: tables ranked for archiving data to ClickHouse
SELECT
    s.relname,
    pg_size_pretty(pg_total_relation_size(s.relid))     AS total_size,
    s.n_live_tup,
    s.n_dead_tup,
    s.seq_scan,
    s.idx_scan,
    s.n_tup_upd + s.n_tup_del                            AS writes_to_existing_rows,
    s.last_autovacuum
FROM pg_stat_user_tables AS s
ORDER BY pg_total_relation_size(s.relid) DESC
LIMIT 20;

-- Staleness of one candidate against a 12-month cutoff
SELECT
    COUNT(*) FILTER (WHERE created_at < now() - INTERVAL '12 months') AS rows_beyond_cutoff,
    COUNT(*)                                                           AS rows_total,
    MAX(updated_at) FILTER (WHERE created_at < now() - INTERVAL '12 months') AS last_write_beyond_cutoff
FROM orders;

-- MySQL 8.4 LTS: the same two questions before archiving data to ClickHouse
SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024 / 1024, 1) AS size_gb,
       table_rows
FROM information_schema.tables
WHERE table_schema = 'shop'
ORDER BY data_length + index_length DESC
LIMIT 20;

SELECT SUM(created_at < NOW() - INTERVAL 12 MONTH) AS rows_beyond_cutoff,
       COUNT(*)                                     AS rows_total,
       MAX(CASE WHEN created_at < NOW() - INTERVAL 12 MONTH THEN updated_at END) AS last_write_beyond_cutoff
FROM orders;

A table qualifies when the rows beyond the cutoff are a large share of its size and last_write_beyond_cutoff is comfortably older than the cutoff. A table where old rows keep receiving updates is not a candidate yet; widen the window or archive with continuous CDC so the archive follows the updates. The query fingerprints that touch the table, from pg_stat_statements or performance_schema.events_statements_summary_by_digest, tell you which reads will move to the archive and therefore what the archive’s sort key must serve.

Physical design

Shaping the archive table before archiving data to ClickHouse

The archive table is not a copy of the source schema. Each column is mapped by type, nullability and codec, and the sort key and partitioning are derived from how the archive will be queried and purged, not from the transactional primary key. The mapping below is for a typical orders table; the rules under it are the ones we apply on every engagement.

-- ClickHouse 25.x / 26.x LTS: target table for archiving data to ClickHouse (engine parameters in full)
CREATE TABLE archive.orders ON CLUSTER '{cluster}'
(
    order_id     UInt64,
    customer_id  UInt64,
    tenant_id    UInt32,
    status       LowCardinality(String),
    total        Decimal(18, 2)                CODEC(ZSTD(3)),
    currency     LowCardinality(FixedString(3)),
    created_at   DateTime64(3, 'UTC')          CODEC(DoubleDelta, ZSTD(3)),
    updated_at   Nullable(DateTime64(3, 'UTC')) CODEC(DoubleDelta, ZSTD(3)),
    notes        String                        CODEC(ZSTD(6)),
    attrs        String                        CODEC(ZSTD(3)),   -- or JSON on 24.8+ once validated
    _version     UInt64,                                          -- source LSN / GTID, or export batch id
    _deleted     UInt8 DEFAULT 0
)
ENGINE = ReplicatedReplacingMergeTree(
    '/clickhouse/tables/{shard}/archive/orders',
    '{replica}',
    _version,
    _deleted
)
PARTITION BY toYYYYMM(created_at)
ORDER BY (tenant_id, customer_id, created_at, order_id)
TTL toDateTime(created_at) + INTERVAL 12 MONTH TO VOLUME 'cold',
    toDateTime(created_at) + INTERVAL 84 MONTH DELETE
SETTINGS
    index_granularity = 8192,
    storage_policy    = 'tiered';

-- Reference data becomes a dictionary, not a second archived table
CREATE DICTIONARY archive.dim_customer ON CLUSTER '{cluster}'
(
    customer_id UInt64,
    segment     String,
    country     String
)
PRIMARY KEY customer_id
SOURCE(POSTGRESQL(NAME crm_pg TABLE 'customers'))
LAYOUT(HASHED())
LIFETIME(MIN 3600 MAX 4200);

Three decisions in that DDL do most of the work. The sort key leads with the columns the archive’s queries filter on most selectively, in order of cardinality, then time, then the primary key last so duplicates collapse correctly during merges. Monthly partitions match the granularity at which the source will be purged, so a source partition and its archive partition are reconciled, detached and dropped as a unit. And ReplacingMergeTree with a version and a delete marker is used even for a one-time backfill, because it makes every reload idempotent: loading the same month twice cannot double-count.

Verification

Proving the archive before a source row is removed

Archiving data to ClickHouse only earns the purge of the source once the copy is proven, and proof is a protocol, not a feeling. Per partition, in order: freeze check, source fingerprint, archive fingerprint, comparison, sampled row diff, a dual-read window, detach, and only then a drop behind a retention hold and a written confirmation gate.

-- Archiving data to ClickHouse, proof step 2: source fingerprint per day (PostgreSQL)
SELECT date_trunc('day', created_at)::date                      AS d,
       COUNT(*)                                                  AS n,
       SUM(hashtext(order_id::text || status || total::text))    AS h
FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01'
GROUP BY 1 ORDER BY 1;

-- Archive fingerprint per day (ClickHouse), deduplicated view of the same month
SELECT toDate(created_at)                                       AS d,
       count()                                                   AS n,
       sum(cityHash64(toString(order_id), status, toString(total))) AS h
FROM archive.orders FINAL
WHERE toYYYYMM(created_at) = 202501 AND _deleted = 0
GROUP BY d ORDER BY d;

-- Sampled row diff: 1,000 random keys pulled from both sides and compared column by column
SELECT a.order_id, a.status = s.status AS status_ok, a.total = s.total AS total_ok,
       a.created_at = s.created_at AS created_ok
FROM archive.orders FINAL AS a
INNER JOIN postgresql(shop_pg, table = 'orders') AS s USING (order_id)
WHERE a.order_id IN (SELECT order_id FROM archive.orders WHERE toYYYYMM(created_at) = 202501 ORDER BY rand() LIMIT 1000)
  AND NOT (status_ok AND total_ok AND created_ok);   -- must return zero rows

The day-bucketed counts must match exactly; the hashes are compared within each side across runs to detect drift, since a byte-identical cross-engine hash is rarely worth the type-normalisation effort. The sampled diff catches what hashes hide: precision loss on decimals, time-zone shifts on timestamps, trailing-space differences on fixed-width strings. The dual-read window routes production reads for the range to the archive for seven days, through ChistaDATA Fabric or the application, and compares p95 and error rate against the baseline.

-- Step 7 on PostgreSQL 16+: verification BEFORE, detach, validation AFTER
SELECT COUNT(*) AS rows_in_partition, MAX(updated_at) AS last_write FROM orders_2025_01;
ALTER TABLE orders DETACH PARTITION orders_2025_01 CONCURRENTLY;
SELECT COUNT(*) FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';  -- expect 0
SELECT COUNT(*) FROM orders_2025_01;                                                             -- expect original count

-- Step 8, after the retention hold and an explicit confirmation gate:
-- DROP TABLE orders_2025_01;   -- CONFIRM: reconciled, sampled, dual-read clean, hold expired
-- VACUUM (VERBOSE) orders;      -- or pg_repack if the table was bloated before archival

On MySQL 8.4 the equivalent is EXCHANGE PARTITION into a holding table, the same before and after counts, and DROP TABLE of the holding table after the hold. The reconciliation table, one row per partition with source hash, archive hash, timestamps and result, is the audit evidence that the archive is complete, and it is what a regulator or an auditor is shown.

Serving

Querying seven years of history after archiving data to ClickHouse

The point of archiving data to ClickHouse rather than to a bucket is that the history stays useful. Four mechanisms make that cheap. The sparse primary index over (tenant_id, customer_id, created_at) makes one customer’s full history a contiguous run of granules. Partition pruning means a query bounded by date opens only the monthly partitions in range, and cold partitions outside it are never touched. A projection gives operational reports a second physical order inside the same table, chosen by the planner automatically. And an AggregatingMergeTree materialized view holds monthly totals so that year-over-year reports read thousands of rows rather than billions.

-- After archiving data to ClickHouse: projection for operational reporting by status and time
ALTER TABLE archive.orders ON CLUSTER '{cluster}'
    ADD PROJECTION by_status (SELECT * ORDER BY (status, created_at));
ALTER TABLE archive.orders ON CLUSTER '{cluster}' MATERIALIZE PROJECTION by_status;

-- Monthly revenue per tenant as mergeable states
CREATE MATERIALIZED VIEW archive.orders_monthly_mv ON CLUSTER '{cluster}'
ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/tables/{shard}/archive/orders_monthly', '{replica}')
PARTITION BY toYear(month) ORDER BY (tenant_id, month)
AS SELECT tenant_id, toStartOfMonth(created_at) AS month,
          sumState(total) AS revenue, countState() AS orders, uniqState(customer_id) AS customers
   FROM archive.orders GROUP BY tenant_id, month;

-- Prove the plan reads what you expect
EXPLAIN indexes = 1
SELECT count(), sum(total)
FROM archive.orders
WHERE tenant_id = 42 AND created_at BETWEEN '2021-01-01' AND '2021-12-31';

Every one of these is validated against the real fingerprints from the selection phase, and its benefit recorded as read rows and read bytes in system.query_log before and after. That log is also where the archive’s own SLO lives: p95 per fingerprint, worst first, reviewed weekly.

Field notes

Mistakes we see when reviewing archiving data to ClickHouse