Archival Store

A ClickHouse archival store is the pattern of moving cold, append-only history out of a transactional database (PostgreSQL, MySQL, MariaDB, SQL Server) into ClickHouse, where it compresses ten to thirty times smaller, answers analytical questions in seconds, and can be tiered to object storage by age. The transactional database keeps only the working set, so its indexes fit in memory again, autovacuum or purge has less to do, and backups shrink. This page is the pattern written as a six-stage worksheet, with the DDL, the validation queries and the cost arithmetic that decide whether it pays.

The posts in this category are the case studies: archiving from PostgreSQL to ClickHouse, the general archiving process, the RDBMS-archival design behind ChistaDATA Fabric, and the real-time analytics architecture that an archival tier becomes once it is queried directly. The worksheet below is the method they share.

Stage 1: decide what qualifies for the ClickHouse archival store

Not every large table is an archival candidate. The test has three parts. Rows must stop changing after some age: an orders table where rows are final 30 days after creation qualifies; a customers table where any row may change at any time does not. The transactional workload must not need the old rows by primary key at OLTP latency; a support tool that fetches a three-year-old order once a day is acceptable over a ClickHouse lookup, a checkout path is not.

The analytical reads that currently hit the old rows must be the kind ClickHouse serves well: aggregates, time ranges, group-bys, not point lookups joined five ways.

The measurement that starts the exercise is the age profile of reads. On PostgreSQL, pg_stat_user_tables gives total tuples read but not their age; the practical method is to sample pg_stat_statements for the slowest queries against the candidate table and read their date predicates. On MySQL, performance_schema.events_statements_summary_by_digest plays the same role. A table where 95 percent of reads touch the last 60 days and 5 percent touch anything older is the canonical ClickHouse archival store candidate.

-- PostgreSQL: size and dead-tuple pressure of the candidate
SELECT
    relname,
    pg_size_pretty(pg_total_relation_size(relid)) AS total,
    n_live_tup,
    n_dead_tup,
    last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- share of rows older than the proposed cut-over age
SELECT
    count(*) FILTER (WHERE created_at < now() - INTERVAL '90 days') * 100.0 / count(*) AS pct_archivable
FROM orders;

Stage 2: design the ClickHouse archival store table

The archival table is not a copy of the OLTP schema. It is denormalised where the analytical queries join, typed for compression, sorted for the range predicates that will hit it, and partitioned by the same unit the retention policy uses. The DDL below is the shape used for an orders archive; every engine parameter is written out, because an archival table is a long-lived object and defaults change between releases.

CREATE TABLE archive.orders_archive
(
    order_id        UInt64,
    customer_id     UInt64,
    status          LowCardinality(String),
    created_at      DateTime64(3, 'UTC') CODEC(DoubleDelta, ZSTD(1)),
    updated_at      DateTime64(3, 'UTC') CODEC(DoubleDelta, ZSTD(1)),
    amount_cents    Int64 CODEC(T64, ZSTD(1)),
    currency        LowCardinality(String),
    line_items      Nested(sku String, qty UInt32, cents Int64),
    source_row_hash UInt64,
    archived_at     DateTime DEFAULT now()
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/archive/orders_archive', '{replica}')
PARTITION BY toYYYYMM(created_at)
ORDER BY (customer_id, created_at, order_id)
TTL toDateTime(created_at) + INTERVAL 2 YEAR TO VOLUME 'cold',
    toDateTime(created_at) + INTERVAL 7 YEAR DELETE
SETTINGS storage_policy = 'tiered', index_granularity = 8192;

Three choices carry the design. ORDER BY (customer_id, created_at, order_id) serves per-customer history and date-range reads; a store queried mainly by date would lead with created_at. PARTITION BY toYYYYMM makes a month the unit of tiering and deletion, so ALTER TABLE ... DROP PARTITION can remove one month in a metadata operation when legal retention expires. And the two-clause TTL moves parts older than two years to the cold volume (object storage in the storage policy) and deletes them at seven, both without a mutation. source_row_hash is the reconciliation key for stage 4.

Stage 3: choose the transfer path into the ClickHouse archival store

There are four ways to move rows, and the right one depends on whether the archive is a one-time backfill, a nightly batch, or continuous. The PostgreSQL post in this category walks through the first two in detail.

-- nightly batch: one closed month at a time from PostgreSQL
INSERT INTO archive.orders_archive
SELECT
    order_id,
    customer_id,
    status,
    created_at,
    updated_at,
    amount_cents,
    currency,
    line_items.sku, line_items.qty, line_items.cents,
    cityHash64(order_id, updated_at, amount_cents) AS source_row_hash,
    now()
FROM postgresql('${PG_HOST}:5432', 'shop', 'orders_flat', '${PG_USER}', '${PG_PASSWORD}')
WHERE created_at >= '2026-06-01' AND created_at < '2026-07-01'
SETTINGS max_insert_threads = 4;

The postgresql() table function pushes the WHERE down to the source (since 21.x), so the read on PostgreSQL is an index range scan rather than a full table read. Chunking by month keeps the source transaction short and makes a failed chunk re-runnable; the archive table’s source_row_hash makes a re-run safe because stage 4 will detect duplicates.

Stage 4: reconcile before deleting anything from the source

This is the stage that separates a ClickHouse archival store from a copy that nobody trusts. Before a single row leaves the transactional database, the archived range is proven equal on both sides by count, by sum of a monetary column, and by a hash of the rows. The delete on the source is gated on that proof, and the procedure keeps the proof output as the audit record.

-- ClickHouse side of the proof for one month
SELECT
    count()                                   AS rows,
    sum(amount_cents)                         AS cents,
    uniqExact(order_id)                       AS distinct_orders,
    groupBitXor(source_row_hash)              AS xor_hash
FROM archive.orders_archive
WHERE created_at >= '2026-06-01' AND created_at < '2026-07-01';

-- PostgreSQL side (same three numbers; hash via the same expression)
SELECT
    count(*),
    sum(amount_cents),
    count(DISTINCT order_id)
FROM orders
WHERE created_at >= '2026-06-01' AND created_at < '2026-07-01';

rows must equal distinct_orders on the ClickHouse side (no duplicates from a re-run), and all three numbers must match the source. Only then does the source delete run, and it runs in batches with a confirmation gate written into the procedure: DELETE FROM orders WHERE created_at < '2026-07-01' AND order_id IN (SELECT ... LIMIT 50000) in a loop, never one statement over the whole range, because a single large delete on PostgreSQL bloats the table it was meant to slim and on InnoDB holds an undo log the size of the range.

Stage 5: route the reads to the ClickHouse archival store

Once history lives in ClickHouse, the reads that used to hit it need a new path. The cleanest is a query router or proxy that sends analytical statements to ClickHouse and transactional ones to the RDBMS; the ChistaDATA Fabric post in this category describes that split as its core design. The simpler alternative is application-level: the reporting service gets a ClickHouse connection, the OLTP service does not. In both cases the archival store is queried directly, which is the point at which it stops being an archive and becomes the real-time analytics tier described in the architecture post.

Where a query needs both recent and historical rows, ClickHouse can present the union without the application knowing. A postgresql engine table over the live orders table and a Merge engine (or a plain UNION ALL view) over it and the archive gives one name for the full history, with the recent rows pulled from PostgreSQL at query time.

CREATE TABLE archive.orders_live
(
    order_id UInt64, customer_id UInt64, status String,
    created_at DateTime64(3, 'UTC'), amount_cents Int64, currency String
)
ENGINE = PostgreSQL('${PG_HOST}:5432', 'shop', 'orders', '${PG_USER}', '${PG_PASSWORD}');

CREATE VIEW archive.orders_all AS
SELECT order_id, customer_id, status, created_at, amount_cents, currency
FROM archive.orders_archive
UNION ALL
SELECT order_id, customer_id, status, created_at, amount_cents, currency
FROM archive.orders_live
WHERE created_at >= (SELECT max(created_at) FROM archive.orders_archive);

Stage 6: operate the ClickHouse archival store as a production system

An archive that is queried is a production database with the same obligations as the one it relieved. Backups run with BACKUP TABLE archive.orders_archive TO S3(...) (native since 22.x) and are restore-tested quarterly. The tiering TTL is verified by watching system.parts.disk_name change for parts crossing the two-year line, and the delete TTL by the monthly partition count. Retention is a legal setting, so the seven-year DELETE clause is reviewed with whoever owns the retention policy before it is created, and the procedure to shorten it carries the same confirmation gate as the source delete.

-- tiering health: parts per volume per month
SELECT
    partition,
    disk_name,
    count()                                AS parts,
    formatReadableSize(sum(bytes_on_disk)) AS on_disk,
    min(min_time)                          AS oldest
FROM system.parts
WHERE database = 'archive' AND table = 'orders_archive' AND active
GROUP BY partition, disk_name
ORDER BY partition;

Type mapping rules when the source is PostgreSQL or MySQL

Most transfer failures in stage 3 are type failures, and they follow a short list. numeric(p, s) and DECIMAL map to Decimal64(s) or Decimal128(s) by precision, never to Float64, because monetary sums must reconcile exactly in stage 4. timestamp with time zone becomes DateTime64(6, 'UTC'); timestamp without time zone needs the source session time zone pinned before export, or a day-boundary drift appears at the edges of every monthly chunk.

PostgreSQL jsonb and MySQL JSON land as String for a pure archive, or as the JSON type (production since 25.x) when the analytical queries reach inside the document.

Nullable columns deserve a decision rather than a default. ClickHouse stores a separate null mask per Nullable column, which costs space and blocks some index optimisations; a source column that is nullable in the schema but never null in the data (check with count(*) FILTER (WHERE col IS NULL)) should be declared non-nullable in the archive. Enumerated status columns become LowCardinality(String) or an explicit Enum8; the former tolerates new values arriving later, the latter does not, and an archive that will receive CDC for years should choose tolerance.

Text columns longer than a few kilobytes get their own ZSTD(3) codec, and identifiers that are UUIDs use the UUID type, not String, halving their footprint.

The monthly cycle as a runbook

Once the backfill is done, the pattern settles into a repeatable monthly procedure that a DBA can run or a scheduler can drive. On the first business day after month close, the closed month is transferred with the chunked INSERT ... SELECT from stage 3, and the stage 4 proof is run and written to an audit table (archive.reconciliation_log with the three numbers from each side, the operator, and a pass/fail flag).

A passing proof unlocks the batched source delete, which runs off-peak with a row-count checkpoint after every batch; a failing proof stops the cycle and pages the on-call, and nothing is deleted.

After the delete, the transactional side is verified: pg_stat_user_tables.n_dead_tup on PostgreSQL should show the deleted rows awaiting vacuum, and a manual VACUUM (VERBOSE) on the table confirms the space is reusable; on InnoDB the freed pages stay in the tablespace until an OPTIMIZE TABLE, which is scheduled separately because it rebuilds the table.

The ClickHouse side is verified with the tiering query from stage 6, and the month’s partition count and bytes are recorded beside the proof. The whole cycle, including verification, is the runbook that a customer receives with the archive, versioned and with the confirmation gate written into the delete step.

The cost arithmetic of a ClickHouse archival store (illustrative)

The numbers below are an illustrative worksheet, not a benchmark; every engagement replaces them with the customer’s own measurements from stage 1. A 4 TB PostgreSQL orders history where 3.5 TB is older than 90 days, on provisioned SSD at a cloud list price, and a ClickHouse archive compressing that history at 12:1 (typical for typed, sorted order data; measure with system.columns) yields roughly 300 GB of hot ClickHouse storage, most of which moves to object storage after two years.

The saving is rarely the storage line; it is the instance class the transactional database can drop to once its working set fits in shared buffers or the InnoDB buffer pool again, plus the reporting queries that stop competing with checkout for I/O. Both are measurable before committing: pg_stat_bgwriter and buffer hit ratios on PostgreSQL, Innodb_buffer_pool_reads on MySQL, and the p95 of the OLTP path during the reporting window.

Where a ClickHouse archival store is the wrong answer

Three situations argue against it. Rows that keep changing after the cut-over age force a ReplacingMergeTree with continuous CDC and FINAL on every read, which works but removes most of the simplicity. Regulatory regimes that require point-in-time restore of the archive to the second are better served by keeping history in the RDBMS and adding read replicas. And a history that nobody queries analytically is cheaper as compressed Parquet in object storage with no ClickHouse at all; the archive exists to be read, and if the reads are absent the pattern is just a second database to operate.

What the transactional database gets back

The benefit on the OLTP side is measurable within one autovacuum or purge cycle of the first delete. Index sizes shrink once REINDEX CONCURRENTLY (PostgreSQL 12+) or a rebuild runs, the buffer-cache hit ratio for the table rises because the working set now fits, checkpoint I/O drops with the smaller dirty footprint, and full backups and their restore times fall in proportion to the bytes removed. Each of these is recorded before and after in the engagement report, because the archive is justified by these numbers rather than by the ClickHouse side alone.

Version notes

The postgresql() and mysql() table functions with predicate push-down are 21.x and later. BACKUP/RESTORE to S3 is native since 22.x. TTL TO VOLUME tiering and multi-clause TTL are 19.x and later; ttl_only_drop_parts = 1 (recommended for partition-aligned TTL) is 20.x and later. Nested columns flatten to arrays by default (flatten_nested = 1). The reference for the storage policy syntax used above is the ClickHouse multiple-volumes documentation; confirm all of the above on the running version.

Reading the archive

Start with the PostgreSQL-to-ClickHouse post for stages 2 and 3, the general archiving post for the seven-step process it describes, the Fabric post for stage 5, and the real-time analytics architecture post for what the store becomes once it is queried directly.

ChistaDATA designs and operates ClickHouse archival stores as part of ClickHouse consulting and managed services, and MinervaDB handles the transactional side of the same engagements. Every stage above is run on staging with a production-sized sample first, the source delete is never executed without the stage 4 proof on record, and a tested restore of both databases exists before the first row moves.