clickhouseauditingintegration

Integrating ClickHouse into your feature-flag audit pipeline: schema, retention, and cost tips

UUnknown

2026-02-14

11 min read

Practical guidance to use ClickHouse for feature-flag audits: schema, TTL, partitioning, and cost-performance tradeoffs for 2026 deployments.

Hook: Why your feature-flag audit pipeline needs a purpose-built long-term store

Risky production releases, messy flag sprawl and the inability to produce an auditable trail are top-of-mind for engineering and compliance teams in 2026. If you rely on ephemeral logs or ad-hoc exports to prove who saw what when, you’ll lose time in postmortems and risk failing audits. ClickHouse has become a go-to long-term store for high-volume event data — including flag evaluations — because it combines fast analytical queries with modern tiered storage and rich TTL semantics. This article gives pragmatic, field-tested guidance to integrate ClickHouse into your feature-flag audit pipeline: schema design, partitioning, TTL strategies, and cost-performance tradeoffs.

Top-line guidance (inverted pyramid)

Design for append-only audits: keep events immutable; use MergeTree with careful ORDER BY for compression and query speed.
Partition by time at the right granularity: monthly partitions for multi-year retention; daily for high-cardinality, high-ingest workloads.
Use TTL and tiering: move older partitions to a cold volume (S3) with TTL TO VOLUME, then DELETE or anonymize after compliance windows.
Optimize storage and cost: compress columns, use low_cardinality, push JSON to extracted columns, and reserve raw JSON only when necessary.
Stream ingestion safely: Kafka -> Kafka engine -> MATERIALIZED VIEW -> MergeTree, with batch sizing and backpressure controls.

Context: Why ClickHouse in 2026?

ClickHouse adoption surged through late 2024–2025 and into 2026. Investments and ecosystem improvements (notably ClickHouse Cloud, richer tiered storage and improved TTL semantics released in 2025) make it viable as the canonical long-term store for audit-grade event data. For feature-flag platforms — where volume, retention and auditability collide — ClickHouse is attractive because it supports:

Fast, ad-hoc analytics over billions of rows
Tiered storage policies (hot local disk → warm/cheap object storage)
Column encodings and codecs that maximize compression
Kafka and HTTP ingestion patterns commonly used in CI/CD and telemetry

Schema design: fields, types and immutability

A good schema reduces row width, improves compression, and makes compliance queries simple. Think of two outputs: (1) the canonical audit row (append-only), and (2) aggregated rollups for dashboards and experiment backfills.

Recommended canonical audit schema

CREATE TABLE flag_evaluations (
  event_time DateTime('UTC'),
  evaluation_id UUID,
  flag_key String,
  flag_version UInt64,
  environment String,
  user_key LowCardinality(String),
  user_id_hash FixedString(16),
  variation UInt8,
  result String, -- e.g. 'on'/'off' or JSON for complex payload
  reason String, -- rule id or reason code (LowCardinality if small set)
  client_sdk String,
  context String, -- JSON blob sparingly; prefer extracted fields
  request_id UUID,
  ip_hash FixedString(16),
  meta_map Map(String,String)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (flag_key, event_time, user_key)
SETTINGS index_granularity = 8192;

Key points:

event_time in UTC is the primary time column for TTL and partitioning.
LowCardinality for fields with limited distinct values (SDKs, environment, reason).
FixedString for hashed PII (user_id_hash, ip_hash) — store only pseudonymized values when possible.
Partition granularity is monthly here; adjust to daily for super-high ingestion.
ORDER BY prioritizes flag_key to speed queries that inspect evaluations for a particular flag and to group identical flag events for better compression.

On JSON and context

JSON blobs are convenient but kill compression and query speed. Extract the small set of frequently-queried context fields into typed columns (country, app_version, experiment_id). Keep a single compressed JSON string column for full context only when required:

ALTER TABLE flag_evaluations
  ADD COLUMN IF NOT EXISTS ctx_json String CODEC(ZSTD(3));

Partitioning and ORDER BY: practical rules

Partitioning affects merge performance, storage movement and deletion. ORDER BY affects compression and query latency.

Partitioning guidelines

Monthly partitions (toYYYYMM(event_time)): good default for multi-year retention — reduces number of partitions and metadata overhead.
Daily partitions if ingest > 100M events/day or compliance requires quick truncation of last-day data.
Avoid too many small partitions — the ClickHouse server and ZooKeeper (if used) suffer high overhead.
Retention & TTL interact with partitions — moving entire partitions to cold storage or deleting them is efficient when partition boundaries align with TTL windows.

ORDER BY and primary key

ORDER BY controls sorting within parts and directly influences compression and range scan speed. For flag audit queries you’ll often query by flag_key + time range + user_key. Recommended ORDER BY: (flag_key, event_time, user_key).

Retention, TTL and tiered storage (practical examples)

Auditable data usually needs two-stage retention: a hot window for active investigations and a long cold window for compliance. ClickHouse TTLs let you move or delete data automatically.

Example: 90 days hot, 3 years cold, then delete

ALTER TABLE flag_evaluations
  MODIFY TTL
    event_time + INTERVAL 90 DAY TO VOLUME 'hot',
    event_time + INTERVAL 3 YEAR TO VOLUME 'cold',
    event_time + INTERVAL 5 YEAR DELETE;

Notes:

'hot' and 'cold' are defined in the server's storage policy. The hot volume should be fast SSD; cold can be object storage (S3) mounted via the ClickHouse disk layer.
Moving to 'cold' reduces cost but increases query latency for historical audits; plan SLA for retrievals.
DELETE after the compliance window can be used for full erasure; however, physical deletion is performed during merges and may not be immediate — account for that in policies and legal agreements.

For GDPR erasure, consider pseudonymization as the default: replace user_id_hash and ctx_json with NULL or a token while leaving the evaluation event. ClickHouse supports UPDATE/DELETE but these are resource-heavy. Safer approach: maintain an event-level flag (erased Bool) and a daily background job to rewrite partitions or use TTL to remove PII after a shorter window.

Ingestion patterns: reliable, scalable pipelines

Production-grade ingestion generally follows two patterns: streaming (Kafka) and batch HTTP/REST. Use ClickHouse's Kafka engine for robust streams and materialized views to load MergeTree.

Kafka -> ClickHouse pattern

CREATE TABLE kafka_flag_evals (
  message String
) ENGINE = Kafka SETTINGS
  kafka_broker_list = 'kafka:9092',
  kafka_topic_list = 'flag-evaluations',
  kafka_group_name = 'ch-flag-evals',
  format = 'JSONEachRow';

CREATE MATERIALIZED VIEW mv_kafka_to_evals TO flag_evaluations AS
SELECT
  parseDateTimeBestEffort(JSONExtractString(message,'event_time')) AS event_time,
  -- extract other fields
  JSONExtractString(message,'flag_key') AS flag_key,
  ...
FROM kafka_flag_evals;

Tips:

Batching at the producer reduces overhead — aim for 1-5 MB per batch where possible.
Set max_block_size and server-side buffers to control memory usage.
Deploy backpressure and dead-lettering on malformed events.

Materialized views and projections for fast analytics

To answer frequent audit questions quickly (e.g., "Which users saw flag X in the last 30 days?"), use projections or materialized views that pre-aggregate or index by flag+day.

CREATE MATERIALIZED VIEW mv_daily_flag_counts
TO daily_flag_counts AS
SELECT
  flag_key,
  toDate(event_time) AS day,
  countIf(variation = 1) AS on_count,
  count() AS total
FROM flag_evaluations
GROUP BY flag_key, day;

Query patterns and indexes

For audit queries you’ll run both point queries (single flag + time range) and scans (trend analysis). Optimize for both:

Point queries: ORDER BY with flag_key first plus secondary low-cardinality columns.
Range queries: the toYYYYMM partitioning speeds partition pruning.
Sparse indices: use tokenbf_v1 or bloom_filter for high-cardinality string columns (flag keys rarely require it).
Sampling: for quick, approximate analytics use SAMPLE clauses on MergeTree with a sampling key, but never for compliance queries.

Cost-performance tradeoffs: concrete considerations

ClickHouse gives knobs for cost vs. speed. Here are typical tradeoffs and how to tune them for a feature-flag audit workload.

Compression vs CPU

Higher compression (ZSTD high level) reduces storage cost but increases CPU on reads. For cold volumes where queries are infrequent, push higher compression; for the hot volume set moderate compression to favor latency.

Hot vs. cold storage

Keep the last 90 days on hot SSD for sub-second queries.
Move historical data to S3 (cold) and accept slower response times for rare audit queries.
In cloud deployments, ClickHouse Cloud pricing usually bundles compute and storage; measure where the break-even is vs self-managed S3-backed nodes (late 2025–2026, ClickHouse Cloud offered improved cold tiers that can lower operational toil).

Row width and cardinality

Narrow rows compress better. Remove redundant data and use LowCardinality and enums for frequently repeated strings. Typical compression savings for a well-designed flags schema are 5–20x versus raw JSON logs — test with a representative sample before committing retention policy numbers.

ETL and backfills: best practices

For backfills, write into a staging MergeTree table with the same ORDER BY and then use INSERT INTO ... SELECT to minimize re-sorting work.
Use distributed tables and INSERT SELECT across shards for horizontally-scaled clusters.
When ingesting historical ranges, load oldest-to-newest to avoid very large merges that spike I/O.

Compliance, auditability and security

For audit-grade pipelines you need immutability, audit trail, access controls and documented retention policies.

Immutability: append-only storage + periodic snapshots of object storage gives legal defensibility. Avoid easy DELETE/UPDATE paths unless governed by a process.
Access control: use ClickHouse RBAC, TLS, network policies, and separate query-only roles for auditors.
Encryption: ensure object storage is encrypted at rest; ClickHouse supports encrypting local disks via OS-level encryption and cloud-provider S3 server-side encryption.
Audit logs: store DB audit logs in a separate secure bucket under a different retention policy and include them in compliance evidence.

Operational runbook: monitoring and common pain points

Common operational issues include backpressure from Kafka, long merges creating IO spikes, and runaway queries. Monitor:

Queue lag and consumer offsets in Kafka
Merge queue length (system.merges), parts count, and disk usage per volume
Slow queries (> 1s) targeting cold volumes

Have a runbook to scale the hot nodes, temporarily pause materialized views for heavy backfills, and throttle producers if the merge queue grows too big.

Real-world examples and sample policies (2026 practices)

Example retention policy used by a mid-size SaaS product in 2026:

Hot (SSD): 90 days — reproducible investigations and feature rollouts
Warm (S3 accelerated storage): 3 years — compliance and long-term experiments
Cold (deep archive): 7 years snapshot per legal guidance for financial clients
PII lifecycle: pseudonymize after 30 days, full deletion after 3 years for non-critical personal data

Checklist: implement ClickHouse for flag audits

Define audit data model and extract commonly-queried context fields.
Choose partition granularity (monthly default) and ORDER BY keys aligned to queries.
Set TTLs to move to cold and delete according to legal windows.
Use Kafka engine + materialized view for ingestion, with DLQ handling.
Create projections/materialized views for common audit queries and experiment backfills.
Configure storage policies: hot SSD vs S3 cold volumes; tune compression codecs per volume.
Establish operational alerts and a GDPR erasure runbook (pseudonymize + delete schedule).

Cost modeling: a quick calculation

Rough method: measure average row size (after compression) over a representative sample. Then estimate monthly growth and map hot/warm split. Example:

Raw JSON row: 2 KB; after schema and compression: 300–500 bytes typical.
At 100M events/month: 100M * 0.5 KB = ~50 GB/month compressed.
90-day hot = 150 GB; 3-year warm = ~1.8 TB. Apply provider pricing (hot SSD vs S3 warm) and account for compute costs for queries.

The levers: reduce row width, move older data earlier, increase compression on warm volumes, and use projections to reduce compute cost for frequent queries.

Future-proofing and 2026 trends

As of early 2026, expect continued maturation of ClickHouse Cloud and tighter integrations with event streaming and observability platforms. Key trends to watch:

Tiered compute: query processors that can spin up compute for cold volumes on-demand.
Smarter TTL and lifecycle APIs: more declarative erasure workflows for compliance teams.
Built-in provenance: richer metadata for tracing ingestion pipelines (who ingested, via which pipeline version) to simplify audits.

Actionable takeaways

Design your schema to be narrow and typed — extract frequently used fields from JSON.
Partition monthly by default; move to daily only when ingestion or deletion needs demand it.
Use TTL TO VOLUME to implement hot/warm lifecycle; anonymize PII early to simplify compliance.
Ingest via Kafka -> Materialized View -> MergeTree for reliable streaming at scale.
Pre-aggregate via materialized views/projections for dashboards and experiment analysis to reduce query cost.

"Treat your flag evaluations like financial ledgers: immutable, auditable, and compact." — Practical guidance distilled from 2024–2026 deployments

Next steps / call-to-action

If you’re evaluating ClickHouse as the long-term store for feature-flag audits, start with a small pilot: export 30 days of representative events, implement the schema above, and benchmark compression and query latency across hot and cold volumes. Tune ORDER BY and partitioning until your common audit queries hit the required SLAs.

Want a checklist tailored to your environment or an architectural review for migrating existing flag logs into ClickHouse? Contact our team at Toggle.top for a guided pilot and a cost-performance model tuned to your ingestion rates and compliance windows.

Unknown

Contributor

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.