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?
| Pros | Cons | |
|---|---|---|
| Writes | Fast appends of large batches into sorted parts | Each insert creates a part, so many small inserts hurt; you must batch or use asynchronous inserts |
| Reads | Fast aggregation over time ranges; sparse index skips most data | Point lookups and per-row access are not its strength |
| Storage | Columnar layout plus time-oriented codecs | The sort key is chosen up front; changing it means recreating the table |
| Corrections | Re-insert with ReplacingMergeTree and a version column | Deduplication is eventual, and exact results need FINAL |
| Rollups | Materialized views pre-aggregate at insert time | Views only see new inserts, so backfills take work, and re-inserted corrections can be double counted |
| Ecosystem | Standard SQL, one engine for analytics | Not 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
FINALto 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
UPDATEandDELETEas 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 choice | Illustrative setting for PV inverter readings |
|---|---|
| Columns | Plant ID, device ID, timestamp, AC power, energy, module temperature, and a version timestamp |
| Engine | ReplacingMergeTree if readings can be corrected, plain MergeTree if not |
| Sort key | Plant, then device, then timestamp |
| Partitioning | By month of the timestamp |
| Types | LowCardinality strings for IDs, DateTime64 in UTC for timestamps |
| Codecs | DoubleDelta on timestamps, Gorilla on slowly changing floats, then ZSTD |
| Rollups | Hourly energy and sample counts per plant, via a materialized view |
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,ReplacingMergeTreedoes 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
DateTime64in 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?
| Situation | Better fit |
|---|---|
| Very large volume, aggregate dashboards, ad hoc analytics | ClickHouse |
| Small volume, relational joins, frequent updates and transactions | PostgreSQL, optionally with a time series extension such as TimescaleDB |
| Infrastructure and application metrics with alerting | A monitoring system such as Prometheus |
| Every reading is corrected often and must be exact immediately | A transactional database, or ClickHouse with FINAL and a clear cost budget |
| A tiny team that does not want to run a database | A 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
- ClickHouse documentation: MergeTree table engine
- ClickHouse documentation: ReplacingMergeTree
- ClickHouse documentation: selecting an insert strategy
- ClickHouse documentation: incremental materialized views
- ClickHouse time series guide: storage efficiency
- ClickHouse case study: Open Electricity and Australia's energy transition
- ClickHouse time series guide: query performance
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.