Lindorm:Pre-downsampling

Create a rule for a table (SQL)

Use CREATE PREDOWNSAMPLE to define a rule on a specific table.

Example 1: Pre-downsample at 1-hour intervals using sum and max.

CREATE PREDOWNSAMPLE `1h` AGGREGATORS (`sum`, `max`) ON sensor;

Example 2: Pre-downsample at 1-hour intervals with a 90-day TTL on the pre-aggregated data.

CREATE PREDOWNSAMPLE `1h` AGGREGATORS (`sum`, `max`) TTL `90d` ON sensor;

For full syntax, see CREATE PREDOWNSAMPLE.

Create a rule for a database (API)

Use the /api/database endpoint to configure pre-downsampling rules for an entire database. Each update replaces all existing rules for the database.

Request path and method

Request parameters

Parameters for each rule in downsampleRules.rules:

PUT /api/database

{
    "name": "default",
    "downsampleRules": {
        "rules": [
            {
                "aggregator": "sum",
                "intervals": ["1m", "1h"]
            },
            {
                "aggregator": "max",
                "intervals": ["1m", "1h"]
            }
        ]
    }
}

Query rules for a table (SQL)

-- Query rules for all tables in the current database
SHOW PREDOWNSAMPLES;

-- Query rules for a specific table
SHOW PREDOWNSAMPLES ON sensor;

For full syntax, see SHOW PREDOWNSAMPLES.

Query rules for a database (API)

GET /api/database

Sample response

[{
    "name": "default",
    "downsampleRules": {
        "rules": [
            {
                "aggregator": "sum",
                "intervals": ["1m", "1h"]
            },
            {
                "aggregator": "max",
                "intervals": ["1m", "1h"]
            }
        ]
    }
}]

A successful request returns HTTP status code 204.

Delete rules for a table (SQL)

DROP PREDOWNSAMPLE `1h` AGGREGATORS (`sum`, `max`) ON sensor;

For full syntax, see DROP PREDOWNSAMPLE.

Delete rules for a database (API)

Send the same PUT /api/database request used to create rules, but set downsampleRules to an empty object.

PUT /api/database

{
    "name": "default",
    "downsampleRules": {}
}

A successful request returns HTTP status code 200.

Use SQL

Add the /*+ PREDOWNSAMPLE */ hint to route the query to pre-aggregated data. Without this hint, the query reads raw data.

-- Route to pre-aggregated data using the PREDOWNSAMPLE hint
SELECT /*+ PREDOWNSAMPLE */ sum(temperature)
FROM sensor
WHERE time >= 1619074800000 AND time <= 1619085600000
SAMPLE BY 1h;

Use TSDB-compatible methods

You can use API-based multi-value queries, API-based single-value queries, and SDKs to query pre-downsampled data.

long startTime = 1619074800000L;
long endTime   = 1619085600000L;

MultiFieldSubQueryDetails fieldSubQueryDetails = MultiFieldSubQueryDetails
        .field("speed")
        .aggregator(Aggregator.SUM)
        .downsample("1h-sum")       // Downsample interval and aggregate function
        .build();

MultiFieldSubQuery subQuery = MultiFieldSubQuery
        .metric("wind")
        .fieldsInfo(fieldSubQueryDetails)
        .downsampleDataSource(DownsampleDataSource.DOWNSAMPLE)  // Use pre-downsampled data
        .build();

MultiFieldQuery query = MultiFieldQuery
        .start(startTime)
        .end(endTime)
        .sub(subQuery)
        .build();

List<MultiFieldQueryResult> result = tsdb.multiFieldQuery(query);

POST /api/mquery

{
    "start": 1346846400000,
    "end":   1346846402000,
    "queries": [
        {
            "downsampleDataSource": "downsample",
            "metric": "wind",
            "fields": [
                {
                    "field":      "speed",
                    "aggregator": "none",
                    "downsample": "1h-sum"
                }
            ],
            "tags": {
                "sensor": "IOTE_8859_0002"
            }
        }
    ]
}

Considerations

Before configuring pre-downsampling, keep the following in mind:

• Rules apply to future data only. Data written before a rule is created is not pre-downsampled. Apply rules before data ingestion if possible.

• Fallback to raw data. If no pre-downsampled data matches the query interval, LindormTSDB automatically falls back to raw data.

• The avg function is not supported. Configure sum and count instead; LindormTSDB uses them to compute averages automatically.

• Deleted rule data expires naturally. After you delete a rule, its pre-aggregated data is excluded from results and removed when its TTL expires.

• Set recommended intervals. Configure intervals at 1m, 10m, 1h, or 1d to cover most downsampling query patterns.

Pre-downsampling for second-level data

For data sampled every second, configure rules at 1-minute, 1-hour, and 1-day intervals. This multi-level structure lets queries at any minute, hour, or day granularity hit pre-aggregated data rather than raw rows.

Create rules

-- Level 1: aggregate raw (per-second) data into 1-minute summaries
CREATE PREDOWNSAMPLE `1m` AGGREGATORS (`sum`, `count`, `min`, `max`) ON sensor;

-- Level 2: aggregate into 1-hour summaries
CREATE PREDOWNSAMPLE `1h` AGGREGATORS (`sum`, `count`, `min`, `max`) ON sensor;

-- Level 3: aggregate into 1-day summaries
CREATE PREDOWNSAMPLE `1d` AGGREGATORS (`sum`, `count`, `min`, `max`) ON sensor;

Query examples

-- Any-minute interval query — uses the 1m pre-downsampled data
SELECT /*+ PREDOWNSAMPLE */ sum(temperature), max(temperature)
FROM sensor
WHERE time >= 1619074800000 AND time <= 1619085600000
SAMPLE BY <N>m;

-- Any-hour interval query — uses the 1h pre-downsampled data
SELECT /*+ PREDOWNSAMPLE */ sum(temperature), max(temperature)
FROM sensor
WHERE time >= 1619074800000 AND time <= 1619085600000
SAMPLE BY <N>h;

-- Any-day interval query — uses the 1d pre-downsampled data
SELECT /*+ PREDOWNSAMPLE */ sum(temperature), max(temperature)
FROM sensor
WHERE time >= 1619074800000 AND time <= 1619600400000
SAMPLE BY <N>d;

Pre-downsampling for minute-level data

For data sampled every minute, configure rules at 1-hour and 1-day intervals. Minute-level granularity queries read raw data directly, so only coarser intervals need pre-downsampling.

Create rules

-- Aggregate per-minute data into 1-hour summaries
CREATE PREDOWNSAMPLE `1h` AGGREGATORS (`sum`, `count`, `min`, `max`) ON sensor;

-- Aggregate into 1-day summaries
CREATE PREDOWNSAMPLE `1d` AGGREGATORS (`sum`, `count`, `min`, `max`) ON sensor;

Query examples

-- Any-hour interval query — uses the 1h pre-downsampled data
SELECT /*+ PREDOWNSAMPLE */ sum(temperature), max(temperature)
FROM sensor
WHERE time >= 1619074800000 AND time <= 1619085600000
SAMPLE BY <N>h;

-- Any-day interval query — uses the 1d pre-downsampled data
SELECT /*+ PREDOWNSAMPLE */ sum(temperature), max(temperature)
FROM sensor
WHERE time >= 1619074800000 AND time <= 1619600400000
SAMPLE BY <N>d;