When ClickHouse is used as the history storage provider, Zabbix does not calculate or store trends in ClickHouse. The documentation recommends extending the history storage period instead. Retention is controlled only by the ClickHouse table TTL (31 days by default), and the housekeeper does not delete data from ClickHouse.

As a result, ClickHouse is challenging to be used for installations that do need long-term data (like banks who need the data for regulatory reasons). For example:

1. A large installation keeps history for 14 days and trends for 2 years, used for monthly availability reports, capacity planning and long-range Grafana dashboards.
2. History is moved to ClickHouse for write throughput and query performance.
3. Trends are no longer available from ClickHouse. To keep 2 years of data, history TTL must be raised to 2 years.
4. Storage now grows with item count x update interval (e.g. 1440 values/day per item at 1m instead of 24 trend rows/day). Every long-range graph, widget, report and history.get request has to aggregate raw values at query time.

Extending history retention does not replace trends. Functionality that reads trends has no data source when history is in ClickHouse:

• trend functions in triggers and calculated items (trendavg, trendmax, trendmin, trendcount, trendsum);
• trend.get in the API;
• graphs and widgets that switch to trends for long periods;
• external integrations that read trends, such as the Grafana Zabbix data source and reporting built on trend tables.

Per-item trend storage periods and Administration > Housekeeping > Trends also do not apply, because retention is a single table-level TTL.

Requested functionality

Support trends for numeric (float and unsigned) items when ClickHouse is the history storage provider:

• store hourly trends in ClickHouse tables (trends, trends_uint) with the standard columns itemid, clock, num, value_min, value_avg, value_max;
• serve trend functions, trend.get, graphs and widgets from these tables, as with SQL backends;
• manage trend retention separately from history retention - at minimum a global trends TTL controlled by Override item trend period;
• create trend tables in history_all.sh (including ReplicatedMergeTree/Distributed cluster setups) and backfill them in the history migration scripts.

Possible approaches:

• Incremental materialized views (preferred). ClickHouse's equivalent of TimescaleDB continuous aggregates. A materialized view on history/history_uint aggregates each insert by itemid and toStartOfHour(clock_ns) into an AggregatingMergeTree table (count, min, max, sum), and a view exposes the standard trend columns. This is a standard, broadly supported ClickHouse feature: no trend calculation load on the server, late data from proxies is aggregated correctly, no UPDATE on restart mid-hour, and it works per shard in cluster setups since history is sharded by itemid.
• Refreshable materialized views. Recompute closed hours on a schedule (REFRESH EVERY 1 HOUR, APPEND). Matches the current behaviour where trends appear after the hour ends.
• Server-side trend cache. Keep the existing trend cache and flush hourly trends to ClickHouse, using ReplacingMergeTree/AggregatingMergeTree instead of UPDATE for the restart-mid-hour case.
• Hybrid (easier and quicker). Keep trends in the relational database while history is in ClickHouse, and support and document this mode officially.

Aggregate projections on the history tables are not a solution, because they share the history table TTL and are removed together with the raw data.

Optional: multiple aggregation levels

Not a hard requirement, but the materialized view approach also allows multiple aggregation levels, similar to the data rollup used by other enterprise monitoring tools. For example:

• raw history - kept 1 month;
• 5-minute aggregates - kept 2 months;
• 1-hour aggregates (current trends) - kept 1 year;
• 1-day aggregates - kept 5 years.

Each level can be a materialized view fed by the level below it (history -> 5m -> 1h -> 1d), each in its own table with its own TTL. The 1-hour level stays the standard trends table, so existing trend functions, trend.get, graphs and widgets keep working unchanged. Additional levels could be used by graphs, widgets and the API to pick the coarsest resolution that fits the requested time range.

Expected result

Installations using ClickHouse for history keep short raw history and long-term hourly trends, with the same trend functions, API, graphs, widgets and retention settings as on PostgreSQL/MySQL. Moving history to ClickHouse no longer requires giving up trends or keeping years of raw history, and optionally allows finer and coarser aggregation levels with independent retention.