Downsampling High-Frequency Data in InfluxDB 1.x with Continuous Queries and Retention Policies

The Problem and the Takeaway

Storing raw metrics at second-level granularity quickly consumes disk and slows queries. InfluxDB 1.x solves this with Continuous Queries (CQs) that automatically aggregate high-frequency data into a lower-frequency bucket, paired with Retention Policies (RPs) that expire the raw data while keeping the summaries. The practical takeaway: define a long-retention RP for downsampled data, create a CQ that writes into that RP, and verify the aggregation actually runs.

Core Mechanism: RP + CQ

A Retention Policy controls how long data lives in a database. A Continuous Query is a scheduled InfluxQL statement that reads from one measurement (usually under the default/short RP) and writes aggregated results into another measurement under a longer RP. The CQ runs on the server at a configurable interval; if the server restarts, it resumes automatically.

Worked Example: 10-Second Raw → 1-Minute Mean

Assume a database telegraf with the default RP autogen (retention 7 days). We want to keep 1-minute averages for 90 days.

1. Create the long-retention RP (run in the influx CLI or via HTTP API with admin rights):

   CREATE RETENTION POLICY "ninety_days" ON "telegraf" DURATION 90d REPLICATION 1

2. Create the CQ that downsamples cpu measurement into cpu_1m under the new RP:

   CREATE CONTINUOUS QUERY "cq_cpu_1m" ON "telegraf" BEGIN
     SELECT mean("usage_user"), mean("usage_system")
     INTO "ninety_days"."cpu_1m"
     FROM "cpu"
     GROUP BY time(1m), "host", "cpu"
   END

   Key points in this statement:

   • INTO "ninety_days"."cpu_1m" explicitly targets the long-retention RP. Omitting the RP name writes to the measurement's current RP (often autogen), causing the aggregated points to expire with the raw data.
   • GROUP BY time(1m), "host", "cpu" preserves tag cardinality so downstream queries can still filter by host and CPU core.
   • The CQ name cq_cpu_1m must be unique per database.
3. Verify the CQ exists:

   SHOW CONTINUOUS QUERIES ON "telegraf"

   Output should list cq_cpu_1m with the SELECT clause matching the example.

4. Confirm aggregation runs (wait at least one CQ interval, default 1 minute, then query):

   SELECT * FROM "ninety_days"."cpu_1m" LIMIT 5

   Compare point counts: SELECT count(*) FROM "cpu" WHERE time > now() - 1h vs SELECT count(*) FROM "ninety_days"."cpu_1m" WHERE time > now() - 1h. The downsampled measurement should have roughly 1/60th the points.

Configuration Details That Matter

CQ Execution Interval

By default, CQs run every minute. Change the interval per database with ALTER DATABASE (requires restart in some 1.x versions) or globally in influxdb.conf under [continuous_queries]:

run-interval = "30s"

Shorter intervals reduce latency between raw ingest and aggregated availability but increase CPU load.

Resampling for Late-Arriving Data

If points arrive late (e.g., network buffering), a single CQ pass may miss them. Use RESAMPLE to re-aggregate a wider window:

CREATE CONTINUOUS QUERY "cq_cpu_1m_resample" ON "telegraf" RESAMPLE EVERY 1m FOR 10m BEGIN
  SELECT mean("usage_user")
  INTO "ninety_days"."cpu_1m"
  FROM "cpu"
  GROUP BY time(1m), "host", "cpu"
END

EVERY 1m sets the run cadence; FOR 10m tells the CQ to recompute the last 10 minutes each run, capturing late points.

Limits and Common Mistakes

1. Target RP Omission Causes Data Loss

If you write INTO "cpu_1m" without the RP qualifier, InfluxDB uses the measurement's current RP (often the short-lived default). When that RP expires, your aggregated data disappears. Always prefix the measurement with the RP name: INTO "ninety_days"."cpu_1m".

2. Undefined Execution Order for Multiple CQs

When two CQs write to the same target measurement, InfluxDB does not guarantee execution order. This can cause race conditions where the second CQ overwrites or doubles-counts the first's output. Solution: give each CQ a distinct target measurement (e.g., cpu_1m_mean, cpu_1m_max) or consolidate into a single CQ with multiple aggregates.

3. Over-Aggressive Downsampling Masks Anomalies

A 1-minute mean on 1-second data smooths away spikes shorter than the window. If you need to detect brief CPU bursts, keep a separate CQ with MAX or PERCENTILE (InfluxQL supports PERCENTILE in 1.7+). Example:

SELECT max("usage_user"), percentile("usage_user", 95)
INTO "ninety_days"."cpu_1m_extremes"
FROM "cpu"
GROUP BY time(1m), "host", "cpu"

4. CQs Are Deprecated in InfluxDB 2.x

InfluxDB 2.x replaces CQs with Flux scheduled queries (tasks). If you plan to migrate, design your downsampling logic in Flux now. The RP concept maps to bucket retention rules; the CQ maps to a task that runs a Flux script on a schedule.

5. Tag Cardinality Explosion

Including high-cardinality tags (e.g., request_id, session_id) in GROUP BY creates a separate series per tag value, negating storage savings. Only group by tags you actually filter on (host, region, service).

Verification Checklist

1. Run SHOW CONTINUOUS QUERIES and confirm the CQ appears with the correct INTO clause.
2. After at least one CQ interval, query the target measurement and verify point count ≈ raw count / aggregation factor.
3. Run SHOW RETENTION POLICIES ON "telegraf" and confirm ninety_days shows 90d0h0m0s duration.
4. Check influxdb.log for CQ execution lines (enabled at debug log level) to ensure runs succeed without errors.

Rollback Consideration

Creating an RP or CQ is metadata-only; it does not mutate existing data. To undo, drop the CQ (DROP CONTINUOUS QUERY "cq_cpu_1m" ON "telegraf") and optionally drop the RP (DROP RETENTION POLICY "ninety_days" ON "telegraf"). Dropping an RP deletes all data in that RP immediately—verify no live dashboards depend on it first.

Summary

Continuous Queries plus Retention Policies give InfluxDB 1.x a built-in, server-side downsampling pipeline. The pattern is: create a long-retention RP, write a CQ that explicitly targets that RP, use RESAMPLE if late data arrives, and verify with SHOW CONTINUOUS QUERIES and a point-count comparison. Avoid the common pitfalls—missing RP qualifier, multiple CQs on one target, and over-aggregation—and you'll keep storage predictable while retaining query speed for historical trends.