Downsampling Time-Series Data with InfluxDB 1.x Continuous Queries

Raw metrics collected every 10 seconds become useless noise after a few weeks — nobody needs per-second CPU readings from three months ago, but the storage cost and query latency keep growing. The standard fix in InfluxDB 1.x is a continuous query: a server-side query that runs automatically on a schedule and writes aggregated results into a separate measurement, so dashboards read small pre-computed data instead of scanning millions of raw points.

Important version note before you start: continuous queries are an InfluxDB 1.x feature. In InfluxDB 2.x and later they were removed and replaced by tasks written in Flux. Run influx version (or check your package manager) first; if you are on 2.x, this guide does not apply and you should look at Flux tasks instead.

What you need in place

• InfluxDB 1.7 or 1.8 running, with access to the influx CLI or the HTTP API.
• A database with at least two retention policies: the default (short, e.g. 7 days) holding raw data, and a long-lived one (e.g. 2 years) for aggregates. Create the second one before the continuous query, for example: CREATE RETENTION POLICY "two_years" ON "metrics" DURATION 104w REPLICATION 1.
• Write access to the database. Continuous queries run as the server, but creating them requires a user with sufficient privileges (admin, or a user with write permission on the target database).

Creating the continuous query

The general shape is:

CREATE CONTINUOUS QUERY cpu_downsample ON metrics
BEGIN
  SELECT mean("usage_idle") AS "mean_usage_idle",
         max("usage_idle") AS "max_usage_idle"
  INTO "two_years"."cpu_1h"
  FROM "cpu"
  GROUP BY time(1h), *
END

Run this in the influx shell after selecting nothing — the ON metrics clause names the database, so you execute it from any context. Breaking the pieces down:

• SELECT mean(...) — you must use an aggregate function. Continuous queries exist to reduce data, so raw field selections are not allowed in the SELECT list.
• INTO "two_years"."cpu_1h" — writes results into measurement cpu_1h under the long retention policy. If you omit the retention policy, results land in the default policy and get expired along with the raw data, defeating the purpose.
• GROUP BY time(1h), * — the time(1h) bucket sets both the aggregation window and how often the query runs (hourly, in this case). The trailing , * preserves all tags (host, region, etc.) so the downsampled data stays filterable. Dropping it collapses everything into one series per hour, which is rarely what you want.

Verifying it actually runs

Creation succeeding only means the syntax parsed. Check these in order:

1. SHOW CONTINUOUS QUERIES — confirms the query is registered on the right database with the definition you expect.
2. Wait for at least one interval boundary to pass (on the hour for a 1h query), then query the target: SELECT * FROM "two_years"."cpu_1h" ORDER BY time DESC LIMIT 5. You should see one point per hour per tag set, with timestamps aligned to the bucket boundary.
3. Compare counts: SELECT count("usage_idle") FROM "cpu" WHERE time > now() - 2h versus the same count on cpu_1h. The aggregate measurement should have roughly one point per hour per series.
4. Check the InfluxDB server logs (journalctl -u influxdb on systemd systems, or the configured log path) for continuous query execution errors — a query that fails silently in the background is a common cause of "empty downsampled measurement" reports.

Known limitations and pitfalls

Continuous queries in 1.x have real rough edges worth planning around:

• Backfill is manual. A continuous query only processes data written after it was created. To downsample historical data, run the same SELECT ... INTO once by hand with an explicit WHERE time range.
• Late-arriving data is missed. Each execution covers the most recent interval. Points that arrive after that window closes are never aggregated. If your pipeline has ingestion delays, add an offset by using the RESAMPLE clause (e.g. RESAMPLE EVERY 1h FOR 2h) so each run re-scans a wider window.
• Execution load. A continuous query over a high-cardinality measurement can spike CPU and I/O at each interval boundary. On busy servers, stagger multiple continuous queries across different intervals rather than aligning them all to the top of the hour, and watch query-duration metrics after deployment.
• Overlapping intervals. Known race conditions exist in some 1.x versions when intervals overlap or when RESAMPLE windows are large; verify aggregate counts against raw counts after any change rather than assuming correctness.

Changing or removing one

There is no ALTER CONTINUOUS QUERY. To change the definition, drop and recreate it — this only affects future executions and does not touch already-written aggregate data:

DROP CONTINUOUS QUERY cpu_downsample ON metrics

Then re-run the CREATE statement with the new definition. If you also want to discard previously written aggregates, drop the cpu_1h measurement explicitly; otherwise old and new aggregation logic will coexist in the same series and skew long-range graphs.

The practical payoff: point your long-range dashboards at cpu_1h and keep short-range panels on raw cpu. Queries that previously scanned weeks of 10-second points now read a few hundred rows, which is where the real performance gain comes from — not from the aggregation itself, but from never touching the raw data again.