Time-series analytics on ClickHouse

When time-series analytics on ClickHouse fits your workload

ClickHouse fits teams that hold metrics or sensor readings at high volume and need fast range queries, aggregation and long retention in infrastructure they control. It is built for analytical scans over timestamped rows, where a query reads many points across many devices and returns a summary. The ClickHouse server is open source under the Apache-2.0 license, so you can run it on your own hardware, in your cloud accounts or in an air-gapped network. ClickHouse Inc. sells ClickHouse Cloud as a separate managed service, which is a different purchase from the server software.

The questions this fits are usually specific. Engineers ask for the trend of one device over the last month, operations teams ask for fleet-wide averages and percentiles by site or model, and dashboard users expect charts that stay readable when a sensor drops out for an hour. Each of these needs a time-ordered table, a way to bucket values by interval, and a plan for which raw points to keep. Before you commit, confirm that most of your queries aggregate over ranges rather than fetch single series by exact key at very high rates, and that your team can own schema design, since the ordering key determines query cost.

A dedicated metrics store may still be the better choice when your core need is short-window alerting on a Prometheus-style data model, when you depend on its native query language and ecosystem, or when your team has no capacity to operate a database cluster. Keep ClickHouse in the comparison when you need joins to business or asset data, long-term analysis across months or years, or one store for both telemetry and reporting. Ask your team to write down the ten most frequent queries and the retention each one needs, then test them against a representative dataset before choosing.

Which time-series table design fits each workload

This table compares the main ways to lay out time-series data in ClickHouse, with the workload each one fits, what it costs in writes and storage, and the query shape it favors. Read it as a set of decisions to settle before the schema is created, since changing the table layout later usually means rebuilding data. The main takeaway is that raw tables, rollups and per-resolution tables trade storage and write cost against query speed, and most teams combine two of them.

Design	When it fits	Cost in writes and storage	Query shape it favors
Raw sample table, ordered by series and time	Small to mid-size fleets where you need every sample and ad hoc drill-downs by device or metric name	One row per sample, so ingest volume and storage grow with sample rate and retention; compression depends on codecs you confirm	Point lookups for one series over a time range, ordered by the sorting key
Narrow layout (one row per sample, few columns)	Sensor or metric streams with a stable, small set of attributes	Compact rows and predictable compression; more rows overall than a wide layout	Aggregations across many series that filter on metric name and time
Wide layout (many attribute columns or Map columns)	Many labels per series that you filter on frequently and that change rarely	Wider rows and sparse columns can cost more storage; Map columns trade schema rigidity for flexibility	Filters on several labels at once, with fewer joins at query time
Rollup table fed by a materialized view	Dashboards and reports that query coarse intervals over long retention windows	Extra write work on every insert and extra storage for the aggregated rows; aggregate state choice affects size	Fast reads at minute, hour or day granularity over long ranges
Separate table per resolution	Distinct retention or access rules per resolution, such as short raw data and long daily summaries	Each table adds storage and maintenance; retention can be set per table with TTL	Queries that always target one resolution, with simple, predictable TTL policies
DateTime64 precision	Any table holding timestamps with sub-second resolution	Higher precision uses more bytes per timestamp; confirm the precision choice and its storage effect in the ClickHouse documentation	Accurate ordering and range filters on fine-grained timestamps
Column codecs for time series	Timestamp and numeric value columns with regular patterns	Codecs change compression ratio and CPU on read and write; confirm defaults and options in the ClickHouse documentation before relying on them	Scans over large ranges, where smaller on-disk data reduces I/O
Sorting key and partitioning	Every time-series table, decided before data load	A poor sorting key increases scanned data on every query; changing it requires a new table	Range scans by series and time, with partitions aligned to retention drops

How DateTime64, codecs and ordering keys shape storage and queries

The three schema decisions that most affect disk use and scan cost on a time-series table are the timestamp type, the ordering key and the column codecs. Timestamp precision is a width decision. A DateTime64 column stores sub-second values at a precision you choose, and a finer precision costs more bytes per row before compression is applied. Ask the team which resolution the queries actually need. Dashboards that aggregate per minute rarely need microseconds, while sensor pipelines that correlate events may. Store the finest precision the source emits only if a query depends on it, and keep the type consistent across every table that will be joined or rolled up.

The ordering key decides how rows sit on disk, so it decides how much data a query skips. For metrics, the usual choice is to order by series identifier and then by time, which keeps each series in a contiguous range and makes a query for one sensor over a time window read few granules. Ordering by time first suits queries that scan all series across a short window, such as fleet-wide alerting. Write down the five or six queries the team runs most, then test both orders with a representative sample. Codecs such as Delta or DoubleDelta combined with a general-purpose codec like ZSTD usually suit timestamps and slowly changing readings, but the gain depends on your data, so confirm it on your own columns.

Measure before you commit. The ClickHouse production guidance recommends a pre-production environment loaded with representative data and queries, and this is where ordering and codec choices should be settled. Compare bytes on disk per column from the system.columns and system.parts tables, and compare query time and rows read from system.query_log for each candidate layout. Changing an ordering key later means rebuilding the table, so treat it as a design decision with a documented rationale. Confirm the behavior of your ClickHouse version against the ClickHouse production FAQ before you adopt specific codecs or types in production.

Rollups and gap filling for recent versus historical queries

Dashboards that refresh every few seconds or minutes usually read the last hours or days of data. For this window, query the raw MergeTree table with a time predicate that matches the sorting key, so ClickHouse can skip most data parts. Keep the raw table as the source for the newest data, because a rollup written by a materialized view only contains rows inserted after the view existed.

Filter on the sorting key: Put the time column early in the sorting key and bound the time range in the WHERE clause so the query reads only recent parts.
Bucket in the query: Use toStartOfInterval on the DateTime64 column to group points at the panel's resolution, and cap how many points a panel can request.
Read raw for freshness: Raw rows are queryable as soon as they are inserted, so panels that must show the latest sample should not depend on a rollup alone.
Limit the series count: Query cost grows with the number of distinct label combinations, so filter by metric name and tags before grouping.

Check the rows read and the query plan for your slowest panel at your real retention, and confirm how the panel's time range maps to the table's partitions and TTL settings.

Sensors and scrapers miss samples, so a bucketed query returns no row for some intervals. A line chart then shows a gap or drops to zero, depending on how the query is written. Decide for each metric whether a missing bucket means no data, a stale value or a zero, and fill only where that meaning holds. In SQL, you can generate a continuous list of buckets for the requested range and left join the observed values, or use the WITH FILL clause with an INTERPOLATE option, and you should verify the syntax and interpolation behavior in your ClickHouse version.

Generate the time axis: Build the expected bucket list for the range first, then join the aggregated samples so empty buckets are explicit.
Use WITH FILL carefully: Confirm which fill step and interpolation options your version supports, and test them on a series with a deliberate gap.
Separate absence from zero: A counter that stopped reporting is different from a gauge that reads zero, so do not write zeros into buckets where no sample arrived.
Mark filled points: Return a flag column for interpolated or filled rows so the chart or alert can display them differently.

Verify the fill output in the dashboard tool your team uses, because charting tools can add their own gap handling on top of the query result.

How to set up a time-series schema on ClickHouse

This sequence takes a time-series workload from cardinality and query analysis through a tested ReplicatedMergeTree schema, rollups, tiered retention and load tests on a pre-production cluster. Each step closes with a check, so the schema is proven before it reaches production.

Define the workload and query shapes
Your data engineers and the people who own the dashboards and alerts list each metric or sensor stream, its expected series cardinality (the number of distinct label or device combinations), sample interval, and the queries they run most often, such as last-value lookups, range scans per device and hourly or daily aggregates. Write these down as a short table the schema will be judged against. The step is done when every query has a named owner, a time range and a grouping key, and when the expected cardinality has a written estimate with a stated method for producing it.
Build a pre-production cluster with representative data
Your platform team stands up a pre-production ClickHouse environment that matches the production topology you plan to run, including replicas and ClickHouse Keeper, then loads a representative slice of real time-series data. The ClickHouse production FAQ recommends a pre-production environment with representative data and queries, and the replication guide recommends at least three Keeper nodes on dedicated hosts for production, so test with the same layout. The step is done when the slice covers peak cardinality and the busiest time ranges, and when the ClickHouse version you will deploy has been chosen from the supported list in the security policy.
Create the raw ReplicatedMergeTree table and ordering key
Your database engineers create the raw table with a DateTime64 timestamp, a compact series identifier, and value columns with codecs chosen for the data shape, using ReplicatedMergeTree so every replica holds the same parts. Put the series identifier and timestamp in the ORDER BY key so that a query for one series over a time range reads a narrow set of granules. Confirm the result by running EXPLAIN indexes = 1 on your top queries and checking that the primary key condition filters the granules you expect. The step is done when each priority query shows the key being used and the table definition is saved in version control.
Add materialized views for each rollup
Your database engineers create one materialized view per rollup interval, such as minute and hour aggregates, using AggregatingMergeTree target tables with aggregate states so partial results merge correctly across inserts. Each rollup view reads from the raw table at insert time, so the cost of aggregation is paid during ingestion rather than on every dashboard refresh. Check the output by comparing a rollup query for a sample series and interval against a direct aggregate over the raw table, and confirm the values match. The step is done when every rollup returns results equal to the raw aggregates for the test window.
Define TTL rules for each retention tier
Your data owners set retention per tier, for example keeping raw samples for a short window and rollups for longer, and your engineers express each rule with TTL clauses on the raw and rollup tables, using TTL ... RECOMPRESS or TTL ... TO VOLUME where your storage layout supports it. Confirm the retention periods with the data owners before writing them, because deletion through TTL is hard to reverse, and keep the periods consistent with any backup policy. The step is done when a test table with short TTLs shows expired rows removed after a merge, and when the production retention values are signed off in writing.
Load test ingestion and queries
Your platform team replays production-like write rates and dashboard query patterns against the pre-production cluster, measuring insert throughput, merge backlog, query latency at the p95 level and memory use while the cluster serves reads. Run the test long enough to include merges and at least one planned restart, and record the thresholds the team agreed in step one. The step is done when ingestion keeps up without a growing part count, the priority queries meet the latency targets, and the results are written down with the exact ClickHouse version and hardware used.
Verify replica recovery before go-live
Your operations team stops one replica during a controlled test, writes more data to the surviving replica, then restarts the stopped node and watches it catch up. The replication guide states that a recovered replica automatically syncs missing data from a healthy replica without manual intervention, so confirm that the row counts and rollup checksums match across replicas once sync finishes. Also confirm that backups have been taken and restored to a spare cluster, because replication does not protect against human error or corruption. The step is done when both replicas return identical results for the priority queries and a restore test has succeeded, which marks the schema ready for production.

Securing sensor and metrics data in ClickHouse

Time-series stores hold years of sensor and metrics history that operators, tenants and sites should not all reach equally. The controls below decide who can connect, what each identity can read, how users authenticate, and whether retained data can be recovered after a mistake. Work through them in this order before any production data arrives.

Restrict the default user

A fresh ClickHouse server starts with a default user that holds all permissions. Before loading sensor data, decide which accounts replace it, then disable or lock down the default user and confirm that remote connections no longer authenticate as it. Check the access control documentation for the SQL-driven workflow, and verify the result by attempting a connection with the default account from each network segment that should be blocked. Record who holds administrative rights and why.

Separate sites with roles and row policies

When one cluster holds metrics for several plants, business units or customers, table-level grants alone are too coarse. Roles group privileges by function, and row policies filter which rows a user sees in a shared table. Decide the tenant key (site, asset class or customer), map it to roles, and test each policy with a query from a restricted account. Confirm that aggregate views and materialized view targets carry the same restrictions, since a policy on the raw table does not automatically govern a rollup.

Choose external authentication

ClickHouse can delegate sign-in to LDAP directories, Kerberos, SSL X.509 client certificates, an HTTP authenticator or JWT tokens. Pick the mechanism that matches your identity provider and how machine clients, such as collectors and dashboards, obtain credentials. Confirm which options your build and version support, and test expiry and revocation behavior for each. Certificates and tokens need rotation procedures that someone owns, or ingestion will fail silently at renewal time.

Test backups by restoring

BACKUP and RESTORE can write full and incremental copies of tables, partitions or databases to local disk, S3-compatible storage or Azure Blob Storage. A backup that has never been restored is unproven, so schedule restores into a spare cluster and time them against your recovery targets. Verify row counts, the latest timestamp per series and a sample of rollup values after each restore. Record the restore steps as a runbook that an on-call engineer can follow without the original builder.

Guard against deletion errors

Replication copies every change to healthy replicas, including a mistaken DROP, DELETE or TTL misconfiguration. Protection against human error therefore depends on backups taken at intervals you choose, stored outside the cluster's failure domain, and retained long enough to cover the time a mistake goes unnoticed. Decide who may run destructive statements on retained partitions, require a second approval for them, and set the backup retention period in writing. Confirm that a restored copy can be recovered at a point before the error.

Operating retention, upgrades and recovery over time

TTL retention and tier moves run in the background, so storage can drift from what the design intended without anyone noticing. Check disk usage per table and per volume on a schedule, compare it with the retention rules you signed off, and confirm that TTL expressions still match the business rules for each rollup table. Rules that were correct for the first year of data can quietly keep too much or too little once volumes, sources or compliance requirements change. Review the rules whenever a new event source, rollup or tier is added, because a rule written for one table is easy to forget when the next table copies its pattern.

Plan version choice before you need it. The ClickHouse production FAQ describes stable releases shipping roughly monthly and LTS releases twice a year, each supported for a year, so an LTS line suits teams that want fewer upgrade cycles. The ClickHouse security policy lists which versions receive security updates, and it changes over time, so confirm the current list on the ClickHouse security policy page before you pick a line. Read the changelog for every release you cross, since minor backward-incompatible changes can occur, and rehearse the upgrade on a pre-production cluster loaded with representative time-series data and your rollup queries.

Replica recovery is largely automatic: a recovered replica syncs missing data from a healthy replica without manual intervention, provided ClickHouse Keeper remains available to coordinate it. Your work is the proof. Backups of the rollup and raw tables, taken with BACKUP and restored to a spare cluster on a schedule, are the only way to show that data can come back after deletion or corruption, because replication copies mistakes too. Record how long each restore takes, confirm that row counts and rollup totals match the source, and treat a failed restore test as a priority incident.

Limits to confirm before you commit to ClickHouse for metrics

ClickHouse can store and query metrics well, but it is not a drop-in replacement for a Prometheus-style stack. The cards below list the points to settle with the vendor or in a proof of concept before you commit. Each one changes the architecture, the cost of operating the platform, or the tools your teams can use day to day.

Query language and PromQL compatibility

Your alert rules and dashboards may depend on PromQL functions such as rate, histogram quantiles and range vectors. ClickHouse uses SQL, so those functions must be rewritten or supplied through a tool that translates them. Ask which query language your dashboards and alerting engine will use against ClickHouse, then run your ten most complex production queries against a copy of your data and compare the results with the current system.

Series cardinality and memory

Each unique combination of metric name and labels creates a series, and high-cardinality labels such as request IDs or user IDs multiply the number of series that ingestion, merges and queries must handle. Memory use and merge load grow with that count, not only with the number of data points. Measure the distinct series count your exporters produce today and test ingestion at peak cardinality, including label values you expect to appear after a deployment.

Late and out-of-order samples

Sensors buffer data during network outages, and some collectors send samples minutes or hours after their timestamps. Whether those samples land in the right partition, trigger rollup recalculation and show up in alerts depends on your insert path and on how your materialized views are defined. Ask how your collector or message queue delivers late data and test a backfill of several hours to confirm that rollups and gap-filled queries return the values you expect.

Feature availability in your chosen version

Time-series features such as specific codecs, materialized view behavior, TTL options and table functions are added or changed across releases. Stable and LTS lines receive fixes on different schedules, so a feature in the latest stable release may not be in the LTS line you plan to run. Check the release notes for the exact version you will deploy and confirm it is in the supported list in the ClickHouse security policy before you finalize the design.

Dashboard and alerting connectivity

Grafana, Metabase, Superset and custom alerting services each connect to ClickHouse through their own data source plugin, and each plugin supports a different subset of time-series macros, time zone handling and query timeouts. Alert evaluation adds load on the cluster on every interval. Test each dashboard and alert rule against ClickHouse under realistic concurrency and confirm that the plugin version you run is maintained for your ClickHouse release.

What running time-series analytics on ClickHouse costs and needs

What drives the cost of building a ClickHouse time-series schema and pipeline?

The main drivers are data volume, series cardinality, the number of rollups, retention rules and the high availability design. Volume and cardinality set how much hardware, storage and ingest capacity you need. Each rollup adds a materialized view, a target table and a backfill to test, so more rollups means more design and verification work. Retention rules decide which tiers and TTL policies must be built and checked. An HA design adds at least three ClickHouse Keeper nodes, which the replication guide recommends running on dedicated hosts with about 4 GB of RAM each in production. Ask any partner to price each of these separately in the written scope so you can see which choice moves the number.

Which parts of a ClickHouse time-series project are one-time and which recur?

One-time work includes schema design, the ingest pipeline, rollup definitions, initial tuning and the first backup and restore test. Recurring work includes upgrades, backup restore tests, monitoring, capacity reviews and any support retainer. ClickHouse stable releases ship roughly monthly, and the production FAQ asks teams to read the changelog before each upgrade, so upgrade effort repeats for the life of the system. The backup documentation recommends regular restore tests on spare clusters, which is a standing cost rather than a single task. Ask for the recurring items to be listed as separate line items with their frequency.

How long does a ClickHouse time-series pilot take?

The duration depends on how many data sources, query patterns, rollups and retention tiers are in scope, and on how quickly your team can provide representative data. A pilot that loads one metric source with a few dashboard queries is much shorter than one that covers several sources, a full rollup chain and tiered retention. The ClickHouse production guidance recommends a pre-production environment with representative data and queries before go-live, so a pilot should be built around that environment. No general duration applies, so request a timeline with milestones, dependencies and acceptance criteria in the written scope before work starts.

What must our team provide before a ClickHouse time-series engagement starts?

Your team should provide representative data samples, a list of the queries and dashboards that matter, a written retention policy and an access model. Representative data lets the schema, codecs and rollups be tested against real cardinality and timestamp patterns. The query list drives ordering keys and materialized view design. The retention policy defines TTL and tiering rules. The access model maps teams to ClickHouse users and roles, and the access control documentation recommends restricting the default user, which starts with all permissions. Ask the partner to confirm in writing which of these inputs it needs and the format it expects.

Can our internal team build and run ClickHouse time-series analytics alone?

An internal team with database and Linux operations experience can build it, but it needs time to learn ClickHouse specifics such as ReplicatedMergeTree, Keeper, materialized view behavior and backup and restore. The replication guide says a recovered replica syncs missing data automatically, yet the backup documentation notes that replication alone does not protect against human error or corruption, so backups must be designed separately. Assess whether you have people who can own the schema, the upgrade calendar, restore tests and on-call response. If any of those are missing, a fixed-scope build with written handover, or a support retainer, can fill the gap.

What risks should a buyer check in a time-series ClickHouse scope?

The main risks are underestimated cardinality, rollups that were never backfilled or tested, retention rules that delete data earlier than compliance requires, and a backup that has never been restored. The backup documentation warns that a backup never test-restored will probably not restore properly when needed. Ask the scope to name who signs off on retention and rollup definitions, and to include a restore test on a spare cluster. Confirm which ClickHouse version you will run, since the security policy lists supported versions and all other versions are unsupported.

Does DeployOpen plan and build ClickHouse time-series systems, and what does a quote need?

DeployOpen implements self-hosted open-source platforms inside your own cloud accounts, data centers or air-gapped networks, and most engagements are fixed-scope with scope, deliverables and timelines agreed in writing before work starts. A quote follows from that written scope, and DeployOpen does not publish a price list. To get a comparable quote, provide the data sources, volumes, cardinality estimates, query list, rollup and retention requirements, HA targets, and the access model. Ask for the fee broken down by design, build, testing, handover and any recurring support or upgrade retainer.

How DeployOpen works on this

DeployOpen plans, implements, hardens, documents and supports self-hosted ClickHouse inside your own cloud accounts, data centers or restricted networks. For time-series work, we start from the metrics and sensor data you hold, the queries your teams run and the retention rules you must meet, then build and hand over the tables, rollups and tier policies in writing.

How we scope

Engagements are fixed-scope, with the schema, rollup, retention and backup deliverables agreed in writing before work starts.
We inventory sources, sample rates, cardinality, query patterns and the dashboards and alerts that depend on the data.
The written scope names the staging environment, representative test data and the acceptance queries each deliverable must pass.

How we build

We design DateTime64 columns, codecs and ordering keys around the time-range and series filters you actually run.
We build materialized view rollups and gap-filling queries, then test them against representative data and known edge cases.
We configure TTL retention by tier and test restores with BACKUP and RESTORE on a spare cluster before sign-off.

How we hand over

You receive schema definitions, rollup logic, retention settings and runbooks in a form your team can version and review.
Handover includes a restore test record, replica failover notes and the upgrade checks to run against the changelog.
We walk your engineers through query examples and operating procedures so ownership stays with your team.

How we support

Ongoing support, upgrades and continuous testing are available as a retainer, scoped separately from the build.
Retainer work can cover version upgrades reviewed against the production FAQ guidance and changelog notes.
Periodic restore tests and retention checks can be scheduled so backup claims are confirmed rather than assumed.

Running time-series analytics on ClickHouse