Short answer: Yes, for analytical workloads. ClickHouse is a columnar database, not a dedicated time series product, but sorted storage, strong compression, fast aggregation and pre-aggregating materialized views fit time series well. Its weak spots are in-place updates, late corrections and small inserts. Solar PV telemetry is the use case in this article.

ClickHouse was built for fast analytics over very large tables, not specifically for time series. Yet many teams use it as a time series database, because the two workloads have a lot in common: append-heavy writes, queries over time ranges, and aggregation instead of single-row lookups. This article looks at why ClickHouse works for time series, what it does badly, and how to design for it.

The use case throughout is solar photovoltaic (PV) data. Picture a fleet of inverters that each report power, energy and temperature every five seconds, plus weather measurements, market prices and production forecasts. As a purely illustrative example, 1,000 inverters reporting every five seconds produce 17.28 million rows a day (1,000 × 86,400 ÷ 5) from that one stream.

Why Use ClickHouse as a Time Series Database?

Because its storage and query model matches what time series workloads need: cheap appends, sorted data, strong compression and fast aggregation. Five features do most of the work.

1. Sorted, columnar storage with a sparse index

The MergeTree engine stores each column separately and sorts rows by the ORDER BY key within each data part. The primary index is sparse: it points to blocks of 8,192 rows by default, so it stays small enough to keep in memory. A query for the average power of one plant over a week reads one or two columns and a contiguous slice of rows, not the whole table.

2. Compression that suits time series

Successive timestamps and sensor values are similar, so they compress well. ClickHouse offers per-column codecs: DoubleDelta is described as optimal for monotonic sequences with a constant stride, such as timestamps, and Gorilla compresses better the more slowly a series changes. ClickHouse's own time series storage guide shows a Wikipedia page-view table shrinking from 12.03 GiB to 2.84 GiB, about 4x, after choosing better types, codecs and an ordering key. That is a web analytics dataset, not solar data, so measure your own.

3. Time-aware SQL

Everything is plain SQL. The DateTime64 type stores timestamps down to nanoseconds with an optional time zone. Functions such as toStartOfHour bucket time, window functions compute running totals and period-over-period changes, ORDER BY ... WITH FILL fills gaps in a series, and ASOF JOIN matches each row to the closest timestamp in another table. For PV data, that last one is useful for lining up a production reading with the nearest forecast or weather sample.

4. Pre-aggregation with materialized views

An incremental materialized view runs a query on each block of data as it is inserted and writes the result to a target table. In the storage guide's example, a monthly top-pages query dropped from about 2.3 seconds on the raw table to about 4 milliseconds on the pre-aggregated one. For PV dashboards, that means a year of hourly energy per plant reads a small rollup instead of billions of raw rows.

5. One engine for time series and analytics

The same store can serve live dashboards, forecast evaluation, ad hoc research and joins against reference tables. You do not need a separate analytical warehouse for the questions a time series database cannot answer.

What Are the Pros and Cons of ClickHouse for Time Series?

ProsCons
WritesFast appends of large batches into sorted partsEach insert creates a part, so many small inserts hurt; you must batch or use asynchronous inserts
ReadsFast aggregation over time ranges; sparse index skips most dataPoint lookups and per-row access are not its strength
StorageColumnar layout plus time-oriented codecsThe sort key is chosen up front; changing it means recreating the table
CorrectionsRe-insert with ReplacingMergeTree and a version columnDeduplication is eventual, and exact results need FINAL
RollupsMaterialized views pre-aggregate at insert timeViews only see new inserts, so backfills take work, and re-inserted corrections can be double counted
EcosystemStandard SQL, one engine for analyticsNot a monitoring stack: its Prometheus-compatible table engine is a private preview feature

The cons in more detail

  • Corrections are eventual. The ReplacingMergeTree documentation states that deduplication happens only during background merges at an unknown time, and that the engine "does not guarantee the absence of duplicates." Adding FINAL to a query gives correct results at the cost of extra work. This is the same consistency trade-off I discuss in PACELC, applied to storage.
  • In-place updates are discouraged. ClickHouse's mutations documentation presents UPDATE and DELETE as exceptions and recommends deduplication for frequent changes. If your readings are revised all the time, weigh that carefully.
  • Small inserts are a trap. The insert strategy guide recommends batches of at least 1,000 rows, ideally 10,000 to 100,000. A device that writes one row at a time straight into ClickHouse is the classic mistake.
  • Joins are not its main strength. Join keys must be equality conditions, apart from the closest-match condition in ASOF JOIN, and large joins can multiply rows. Model hot reference data carefully.
  • The learning curve is real. Sort keys, partitions, merges and engines are new concepts for a team used to PostgreSQL. This is my own observation, not a documented limit.

How Should You Design a ClickHouse Table for PV Data?

Design around your queries: entity first, time last, batched inserts, and rollups for long ranges. The table below is an illustrative design for inverter readings, not one taken from a production system.

Design choiceIllustrative setting for PV inverter readings
ColumnsPlant ID, device ID, timestamp, AC power, energy, module temperature, and a version timestamp
EngineReplacingMergeTree if readings can be corrected, plain MergeTree if not
Sort keyPlant, then device, then timestamp
PartitioningBy month of the timestamp
TypesLowCardinality strings for IDs, DateTime64 in UTC for timestamps
CodecsDoubleDelta on timestamps, Gorilla on slowly changing floats, then ZSTD
RollupsHourly energy and sample counts per plant, via a materialized view
Solar PV time series pipeline on ClickHouse Five boxes from top to bottom joined by arrows. PV sources such as inverters, meters and market APIs send readings to a batching writer. The writer inserts batches into a raw MergeTree table sorted by plant, device and time and partitioned by month. A materialized view maintains an hourly rollup table. Dashboards and APIs read the rollup for long ranges and the raw table, by a dashed arrow, for detail. PV sources inverters, meters, APIs Batching writer 10k-100k rows per insert Raw table ORDER BY plant, device, ts monthly partitions Hourly rollup via materialized view Dashboards and APIs rollup for long ranges, raw table for detail
An illustrative ClickHouse pipeline for PV telemetry: batched inserts into a sorted raw table, with a rollup table for fast long-range queries.

Why the sort key comes first

It decides which queries are fast. ClickHouse's query performance guide compares sorting a page-view table by time alone with sorting by the columns that queries filter on, then time. Filtered queries dropped from about 2.1 to 3.0 seconds to between 0.010 and 0.058 seconds. Again, that dataset is not solar data, but the principle carries over: put the columns you always filter on first.

Partitions, batches and retention

Partition by month. The MergeTree documentation calls partitioning primarily a data management technique, not a query optimization tool, so use it to drop or expire whole months, not to speed up queries. Batch inserts in your own writer or with asynchronous inserts. For retention, a TTL rule removes expired rows during background merges, so expired data can linger briefly. A common PV policy is raw readings for months and hourly or daily rollups for the life of the plant, subject to your warranty and audit obligations.

Handling corrections and late data

Late data from a logger that reconnects is simply inserted. Revised prices and forecasts are re-inserted with a newer version, and queries that need exact results use FINAL. If you also keep rollups, rebuild the affected periods after a correction, or keep rollups for append-only streams. If readings are never revised, skip all of this and use plain MergeTree.

Is There a Real-World Energy Example?

Yes. ClickHouse's case study on Open Electricity, a public platform that tracks Australia's electricity transition, describes about 1 billion records of 5-minute generation, emissions and price data, growing by roughly 500,000 records a day. Both base tables use ReplacingMergeTree, and Postgres still handles the relational data. This is a vendor's own write-up, so read the numbers accordingly.

  • Speed: an all-time energy-by-fuel query grouped by month took 313 seconds on Postgres and 3.4 seconds on ClickHouse, and 60 milliseconds against a monthly materialized view. The article notes the two setups are not hardware-for-hardware comparable.
  • Rollups: the team runs more than 20 materialized views and adds a new one when a query becomes a hot spot.

The lessons are as useful as the numbers, and they match the cons above:

  • Use FINAL. Without it, ReplacingMergeTree does not deduplicate at query time, and you can end up with doubled data.
  • Rollups and versions can collide. Partial daily aggregates could win the version race over complete ones, so the team encoded completeness into the version number.
  • Standardize on UTC. The author recommends DateTime64 in UTC from the start, because dates were handled in about 20 places across the pipeline.
  • Plan backfills separately. The team found that windows of three days with batches of 20,000 records worked best for bulk loads, and that user-facing queries and backfills need very different timeouts.

When Should You Choose Something Else?

SituationBetter fit
Very large volume, aggregate dashboards, ad hoc analyticsClickHouse
Small volume, relational joins, frequent updates and transactionsPostgreSQL, optionally with a time series extension such as TimescaleDB
Infrastructure and application metrics with alertingA monitoring system such as Prometheus
Every reading is corrected often and must be exact immediatelyA transactional database, or ClickHouse with FINAL and a clear cost budget
A tiny team that does not want to run a databaseA managed service, such as ClickHouse Cloud or another provider

A single small plant reporting once a minute does not need ClickHouse. A fleet with many devices, high sampling rates and years of history is where it starts to pay off. Whatever you pick, measure storage per row and query cost on your own data, as I describe in FinOps in Startups.

Frequently Asked Questions

Is ClickHouse a real time series database?

It is a general columnar analytics database, not a dedicated time series product. Its sorted storage, compression and aggregation speed make it a strong time series store for analytical workloads, but it lacks some conveniences of purpose-built tools.

How do I update a row in ClickHouse?

The usual approach is to insert a new version of the row into a ReplacingMergeTree table and query with FINAL. Mutations exist, but the documentation presents them as exceptions and recommends deduplication for frequent changes.

Should I partition by day or by month?

Month is a safe default. Choose day only when volume is very high and you need to drop or move data in daily units. Avoid partitioning by entity, such as plant or device.

Do I need Kafka to load data into ClickHouse?

No. A queue helps when you need buffering and replay, but a small batching writer or asynchronous inserts are enough for many solar and IoT workloads.

Further Reading

Choosing a data store for telemetry, energy or IoT data? Get in touch or write to [email protected] and I'll help you size the architecture before you build it.