Automated Downsampling in InfluxDB with Retention Policies and Continuous Queries

The problem: high-resolution data fills disk fast

When you ingest metrics at sub-second granularity, the raw series can consume storage far faster than your retention needs. Keeping every point for years makes queries slow and drives up costs, yet discarding the data outright removes the detail needed for short-term alerting.

Solution overview: retention policies + continuous queries

InfluxDB lets you define retention policies that automatically drop data after a set period. By pairing a short-lived raw policy with a long-lived downsampled policy, you preserve recent detail while archiving coarser summaries. A continuous query (CQ) runs on a schedule, aggregates the raw points (for example, the mean per minute), and writes the result into the downsampled policy. This gives you:

• High-resolution data for the last few days (alerting, debugging)
• Low-resolution trend data for years (capacity planning, reporting)

Step-by-step setup

Run these statements in the InfluxDB CLI (influx) or through the HTTP query endpoint. You need admin privileges on the target database. Replace mydb and the measurement names with your own.

1. Choose durations. Example: raw data kept 7 days, downsampled data kept 5 years.

2. Create the raw retention policy and make it the default:

   CREATE RETENTION POLICY "raw" ON "mydb" DURATION 7d REPLICATION 1 SHARD DURATION 1h DEFAULT;

3. Create the downsampled policy (non-default, so raw writes stay in raw):

   CREATE RETENTION POLICY "downsampled" ON "mydb" DURATION 5y REPLICATION 1 SHARD DURATION 24h;

4. Add a continuous query that computes the mean per minute and writes into the downsampled policy:

   CREATE CONTINUOUS QUERY "cq_downsample" ON "mydb" BEGIN
     SELECT mean(value) AS value
     INTO "downsampled"."autogen".:MEASUREMENT
     FROM /.*/
     GROUP BY time(1m), *
   END;

5. Verify the objects exist:

   SHOW RETENTION POLICIES ON "mydb";
   SHOW CONTINUOUS QUERIES ON "mydb";

Worked example: inserting test data and verifying the downsample

1. Insert a few high-frequency points into a test measurement (you need write permission on the database):

INSERT temperature,host=server1 value=23.5
INSERT temperature,host=server1 value=23.6
INSERT temperature,host=server1 value=23.4
INSERT temperature,host=server1 value=23.7
INSERT temperature,host=server1 value=23.5
INSERT temperature,host=server1 value=23.6

2. Wait for the CQ to execute. Continuous queries run at the interval defined in the GROUP BY clause (here, every minute), so allow at least one full minute boundary to pass.

3. Query the downsampled measurement to see the aggregated point:

SELECT * FROM "downsampled"."autogen"."temperature" WHERE time > now() - 2m;

You should see a row with a timestamp aligned to the minute boundary and a value close to the mean of the inserted points (about 23.55 for the sample above). The raw points remain in the raw policy and are dropped automatically after seven days.

Trade-offs and limitations

• Granularity loss: a mean per minute discards sub-minute spikes. If your analysis needs percentiles or peak detection, use an aggregation that preserves those characteristics (max, percentile) or store multiple downsampled series.

• Policy alignment: if the raw policy duration is shorter than the CQ's group-by interval, the CQ may have no data to aggregate, producing gaps in the downsampled series. Keep the raw retention longer than the longest interval you aggregate.

• Storage overlap: while both policies hold data, points are effectively duplicated until the raw points expire. Watch disk usage during the first weeks after rollout.

Actionable closing

Start with a small test database, apply the steps above, and confirm the downsampled output matches the aggregation you expect. Then roll out to production and monitor:

• SHOW RETENTION POLICIES for correct durations
• SHOW CONTINUOUS QUERIES to confirm the CQ is active
• Disk usage via SHOW STATS or your monitoring stack to confirm raw data expires as expected

If you see unexpected gaps or excessive storage, revisit the policy durations and aggregation interval. With these controls in place you get detailed recent visibility and economical long-term trend storage.