Downsampling Time-Series Data with SQLite

Building Rollups, Retention Tiers, and Efficient Historical Summaries

In our previous article, Storing IoT Telemetry Streams with SQLite, we built a database capable of continuously capturing sensor readings. Each measurement had a device, metric, value, and timestamp, giving us an accurate history of what happened and when.

That works beautifully for recent data.

The problem appears later.

Imagine a temperature sensor reporting once every second. That’s 86,400 readings every day and more than 31 million readings per year. Add vibration, pressure, voltage, humidity, and hundreds of devices, and a useful telemetry database can become enormous.

Most applications don’t actually need every second-by-second measurement forever.

An engineer investigating a problem from ten minutes ago may need every raw reading. Someone viewing last year’s temperature trends probably doesn’t.

This is where downsampling becomes valuable.

Instead of treating every measurement as equally important forever, we gradually transform older raw data into smaller summaries. SQLite can keep recent data at full resolution while preserving useful historical information for months or years.

The result is a database that ages gracefully rather than simply growing forever.

What Downsampling Actually Means

Suppose a temperature sensor records these values:

10:00:00    21.2
10:00:10    21.4
10:00:20    21.3
10:00:30    21.8
10:00:40    22.0
10:00:50    21.7

For troubleshooting something that happened at 10:00:30, every measurement may matter.

Six months later, perhaps all we need to know about that minute is:

Minimum: 21.2
Maximum: 22.0
Average: 21.57
Samples: 6

We’ve replaced six rows with one.

At real telemetry volumes, that reduction can be dramatic.

The important point is that downsampling is not simply deleting old data.

It is deliberately converting high-resolution information into lower-resolution information before the original readings disappear.

Think of it as changing the level of detail as data gets older.

Raw readings
     ↓
Minute summaries
     ↓
Hourly summaries
     ↓
Daily summaries

Each stage contains less detail but covers a longer period efficiently.

Why Not Keep Everything?

Storage is inexpensive on many servers, so it can be tempting to keep every measurement forever.

At the edge, however, storage may be much more constrained.

An industrial gateway might have 32 GB or 64 GB of flash storage. A Raspberry Pi-class device may rely on an SD card. Embedded systems may have even tighter limits.

And database size isn’t the only consideration.

Larger raw datasets can mean:

• More pages to manage

• Larger indexes

• Longer maintenance operations

• More data to back up

• More data to synchronize

• Greater flash-storage usage

• More expensive historical queries
  

If a dashboard asks:

What was the average temperature for each day last year?

scanning millions of second-level readings just to produce 365 points is wasteful.

A daily summary table can answer the same question from roughly 365 rows.

Downsampling therefore solves two problems at once:

storage efficiency and query efficiency.

Designing Retention Tiers

A useful telemetry system usually has different levels of resolution.

For example:

| Data tier      | Resolution    | Retention     |
| -------------- | ------------- | ------------- |
| Raw telemetry  | Every reading | 7 days        |
| Minute rollups | 1 minute      | 30 days       |
| Hourly rollups | 1 hour        | 1 year        |
| Daily rollups  | 1 day         | Several years |

These aren’t universal values.

A factory investigating fast vibration changes may need raw measurements for several months. A weather station may be comfortable keeping minute averages for years.

The important idea is the tiered model.

New data begins with maximum detail.

As it ages, we preserve progressively less detail.

NOW
│
├── Raw readings
│
├── Minute summaries
│
├── Hourly summaries
│
└── Daily summaries
                     → OLDER

This gives the application a predictable storage lifecycle.

Starting with the Raw Telemetry Table

We’ll continue with a compact telemetry structure similar to the one from our previous article:

CREATE TABLE Telemetry (
    TelemetryID INTEGER PRIMARY KEY,
    DeviceID INTEGER NOT NULL,
    MetricID INTEGER NOT NULL,
    Value REAL NOT NULL,
    RecordedAt INTEGER NOT NULL
);

And our main time-series index:

CREATE INDEX idx_telemetry_device_metric_time
ON Telemetry(DeviceID, MetricID, RecordedAt);

Assume RecordedAt stores a UTC Unix timestamp.

Raw telemetry remains the source of truth for recent measurements.

Now we need somewhere to put our summaries.

Designing a Minute Rollup Table

A useful summary needs more than an average.

Consider this minute:

4.0
4.1
4.2
9.8
4.1
4.0

Its average might look relatively normal, while the maximum reveals a significant spike.

For that reason, we’ll preserve:

• Minimum
  

• Maximum
  

• Average
  

• Sample count
  

Our minute table becomes:

CREATE TABLE TelemetryMinute (
    DeviceID INTEGER NOT NULL,
    MetricID INTEGER NOT NULL,
    PeriodStart INTEGER NOT NULL,
    MinimumValue REAL NOT NULL,
    MaximumValue REAL NOT NULL,
    AverageValue REAL NOT NULL,
    SampleCount INTEGER NOT NULL,

    PRIMARY KEY (
        DeviceID,
        MetricID,
        PeriodStart
    )
);

PeriodStart identifies the beginning of the minute.

For example:

10:42:00
10:43:00
10:44:00

Each device and metric can have one summary row for each minute.

Creating the Rollup

Suppose we’re processing a completed minute.

The application knows:

Start = 10:42:00
End   = 10:43:00

We can summarize it with:

INSERT INTO TelemetryMinute
(
    DeviceID,
    MetricID,
    PeriodStart,
    MinimumValue,
    MaximumValue,
    AverageValue,
    SampleCount
)
SELECT
    DeviceID,
    MetricID,
    ?,
    MIN(Value),
    MAX(Value),
    AVG(Value),
    COUNT(*)
FROM Telemetry
WHERE RecordedAt >= ?
  AND RecordedAt < ?
GROUP BY
    DeviceID,
    MetricID;

Notice the range:

RecordedAt >= ?
AND RecordedAt < ?

rather than using an inclusive end boundary.

This avoids accidentally placing a reading exactly at 10:43:00 into both the 10:42 and 10:43 windows.

Small boundary decisions like this become extremely important in time-series systems.

Rollups Must Be Safe to Retry

Imagine the aggregation worker creates the 10:42 summary.

Then the process crashes before recording that the work completed.

When it restarts, it processes 10:42 again.

We don’t want:

10:42 summary
10:42 summary

Our composite primary key already prevents duplicate periods:

DeviceID + MetricID + PeriodStart

But production systems should go further and make the aggregation operation deliberately repeatable.

An upsert works well:

INSERT INTO TelemetryMinute
(
    DeviceID,
    MetricID,
    PeriodStart,
    MinimumValue,
    MaximumValue,
    AverageValue,
    SampleCount
)
SELECT
    DeviceID,
    MetricID,
    ?,
    MIN(Value),
    MAX(Value),
    AVG(Value),
    COUNT(*)
FROM Telemetry
WHERE RecordedAt >= ?
  AND RecordedAt < ?
GROUP BY DeviceID, MetricID

ON CONFLICT(DeviceID, MetricID,PeriodStart)
DO UPDATE SET
    MinimumValue = excluded.MinimumValue,
    MaximumValue = excluded.MaximumValue,
    AverageValue = excluded.AverageValue,
    SampleCount = excluded.SampleCount;

If the window is processed again, the existing summary is replaced rather than duplicated.

This property becomes extremely valuable when jobs restart after failures.

Rolling Minutes into Hours

Once minute summaries exist, we don’t necessarily need to scan raw telemetry again to create hourly summaries.

Create another table:

CREATE TABLE TelemetryHourly (
    DeviceID INTEGER NOT NULL,
    MetricID INTEGER NOT NULL,
    PeriodStart INTEGER NOT NULL,
    MinimumValue REAL NOT NULL,
    MaximumValue REAL NOT NULL,
    AverageValue REAL NOT NULL,
    SampleCount INTEGER NOT NULL,

    PRIMARY KEY (
        DeviceID,
        MetricID,
        PeriodStart
    )
);

Then summarize the minute data.

But there is an important detail.

This would be wrong:

AVG(AverageValue)

Why?

Because each minute may contain a different number of measurements.

Suppose:

Minute A
Average = 10
Samples = 60

Minute B
Average = 20
Samples = 10

Simply averaging 10 and 20 gives:

15

But Minute A represents six times as many measurements.

We need a weighted average.

SUM(AverageValue * SampleCount)
/
SUM(SampleCount)

Our hourly aggregation therefore looks more like:

INSERT INTO TelemetryHourly
(
    DeviceID,
    MetricID,
    PeriodStart,
    MinimumValue,
    MaximumValue,
    AverageValue,
    SampleCount
)
SELECT
    DeviceID,
    MetricID,
    ?,
    MIN(MinimumValue),
    MAX(MaximumValue),
    SUM(AverageValue * SampleCount)
        / SUM(SampleCount),
    SUM(SampleCount)
FROM TelemetryMinute
WHERE PeriodStart >= ?
  AND PeriodStart < ?
GROUP BY DeviceID, MetricID;

Now the hourly average still represents the underlying measurements correctly.

Designing Rollups That Can Be Rolled Up Again

This exposes a useful design principle.

If one summary tier will be used to build another, store enough information to combine summaries correctly.

Minimum combines naturally:

MIN(all minimums)

Maximum does too:

MAX(all maximums)

Count becomes:

SUM(all counts)

Average requires both the average and the count.

An alternative design is to store SumValue as well:

SumValue REAL NOT NULL

Then:

Combined average =
SUM(SumValue) / SUM(SampleCount)

This is often cleaner and avoids repeatedly reconstructing sums from averages.

For a production rollup system, I would therefore consider storing:

MinimumValue
MaximumValue
SumValue
SampleCount

and calculate the average when querying:

SumValue / SampleCount

That makes higher-level aggregation straightforward.

Daily Rollups

The same architecture continues:

Raw
 ↓
Minute
 ↓
Hourly
 ↓
Daily

A daily table could be:

CREATE TABLE TelemetryDaily (
    DeviceID INTEGER NOT NULL,
    MetricID INTEGER NOT NULL,
    PeriodStart INTEGER NOT NULL,
    MinimumValue REAL NOT NULL,
    MaximumValue REAL NOT NULL,
    SumValue REAL NOT NULL,
    SampleCount INTEGER NOT NULL,

    PRIMARY KEY (
        DeviceID,
        MetricID,
        PeriodStart
    )
);

Hourly summaries become daily summaries using the same composable statistics.

We are no longer scanning millions of raw measurements to generate long-term reports.

Each tier builds efficiently on the one below it.

Don’t Delete Raw Data Immediately

Suppose a minute ends at 10:43.

Should we summarize it at 10:43:01 and delete the raw readings?

Usually, no.

IoT measurements can arrive late.

A device might temporarily lose connectivity and later send readings for:

10:39
10:40
10:41

If those windows have already been permanently summarized, the rollups become inaccurate.

Instead, introduce a lateness window.

For example:

Current time:       11:00
Aggregation delay:  10 minutes
Safe through:       10:50

Only periods older than the delay are considered stable enough for normal aggregation.

Even then, raw data doesn’t need to disappear immediately.

The minute summary might be created after ten minutes while raw telemetry remains available for seven days.

That overlap gives us plenty of time to correct recent summaries.

Handling Late-Arriving Measurements

There are several ways to deal with late data.

One simple approach is to mark affected periods for recalculation.

Suppose a reading arrives with:

RecordedAt = 10:42:27

but we’ve already generated the 10:42 rollup.

The ingestion process can identify the minute bucket:

10:42:00

and mark it dirty.

For example:

CREATE TABLE RollupDirtyPeriods (
    Tier TEXT NOT NULL,
    PeriodStart INTEGER NOT NULL,
    PRIMARY KEY (Tier, PeriodStart)
);

Then:

INSERT OR IGNORE INTO RollupDirtyPeriods
(Tier, PeriodStart)
VALUES ('minute', ?);

A background worker periodically recalculates dirty windows.

Once corrected, the dirty marker can be removed.

This avoids rebuilding large ranges unnecessarily.

The Ripple Effect of Corrections

There is another subtle issue.

If we correct:

10:42 minute

then the corresponding:

10:00 hourly summary

may now be wrong.

And if that hour has already contributed to a daily rollup, the daily summary may also need rebuilding.

Corrections can therefore move upward:

Late raw reading
      ↓
Minute changed
      ↓
Hour changed
      ↓
Day changed

A robust rollup worker should understand these dependencies.

This is one reason it helps to make every aggregation tier safe to rebuild.

Querying Across Retention Tiers

Now imagine a dashboard with several time ranges:

Last hour
Last 24 hours
Last 30 days
Last year

We don’t need to query the same table for all four.

The application can select the appropriate resolution.

For example:

Last hour       → Raw telemetry
Last 24 hours   → Minute rollups
Last 30 days    → Hourly rollups
Last year       → Daily rollups

That dramatically reduces the number of rows returned.

A chart 800 pixels wide gains very little from receiving two million data points.

Sending 500 or 800 meaningful summary points is often more useful.

Resolution Should Match the Question

This leads to an important rule:

Use the finest resolution necessary to answer the question, not the finest resolution available.

If someone asks:

What was average power consumption each month last year?

Raw second-level readings are unnecessary.

If someone asks:

What happened to vibration immediately before Motor 17 failed at 14:37?

Raw readings may be essential.

Downsampling doesn’t eliminate detail indiscriminately.

It gives the application multiple levels of detail to choose from.

Retention Should Follow Successful Aggregation

Consider this dangerous sequence:

Delete old raw data
      ↓
Generate summary

If aggregation fails, we’ve lost the source.

Instead:

Aggregate
   ↓
Verify
   ↓
Mark complete
   ↓
Raw data eventually becomes eligible for deletion

The retention worker should only remove raw periods that have been successfully rolled up.

For important systems, you may also require successful cloud synchronization or backup before deletion.

The lifecycle might become:

Raw telemetry
      ↓
Minute rollup confirmed
      ↓
Cloud copy confirmed
      ↓
Retention age reached
      ↓
Raw data deleted

Now retention is based on state as well as age.

Tracking Rollup Progress

A small state table can tell the application how far each aggregation process has progressed.

CREATE TABLE RollupState (
    Tier TEXT PRIMARY KEY,
    LastCompletedPeriod INTEGER NOT NULL
);

Example:

minute    1788675600
hourly    1788672000
daily     1788566400

When the worker restarts, it doesn’t need to guess where to continue.

It reads the last completed period and proceeds from there.

Combined with idempotent upserts, this creates a resilient aggregation pipeline.

Deleting Data in Batches

Eventually raw telemetry becomes old enough to remove.

Avoid one enormous transaction such as:

DELETE FROM Telemetry
WHERE RecordedAt < very_old_timestamp;

when that could affect millions of rows.

Instead, remove manageable batches.

For example:

DELETE FROM Telemetry
WHERE TelemetryID IN (
    SELECT TelemetryID
    FROM Telemetry
    WHERE RecordedAt < ?
    ORDER BY TelemetryID
    LIMIT 10000
);

Commit, allow other work to proceed, then continue later.

This reduces the time one maintenance transaction holds the writer.

What Happens to the Freed Space?

Deleting millions of telemetry rows doesn’t necessarily make the database file immediately smaller.

SQLite marks database pages as reusable.

Future inserts can reuse that free space.

For a rolling telemetry workload, this can be ideal:

Old telemetry deleted
        ↓
Pages become free
        ↓
New telemetry reuses them

Once the database reaches a relatively stable operating size, the file may stop growing rapidly because incoming data reuses storage released by retention.

If you actually need to return space to the operating system, VACUUM or an appropriate auto-vacuum strategy may be considered, but that is a separate operational decision.

Retention does not require constantly shrinking the file.

Indexing the Rollup Tables

Our rollup primary key is:

DeviceID
MetricID
PeriodStart

That naturally supports a common historical query:

SELECT
    PeriodStart,
    MinimumValue,
    MaximumValue,
    SumValue / SampleCount AS AverageValue
FROM TelemetryHourly
WHERE DeviceID = ?
  AND MetricID = ?
  AND PeriodStart >= ?
  AND PeriodStart < ?
ORDER BY PeriodStart;

Because the primary key already matches the access pattern, we may not need another index.

That’s valuable.

Rollup tables exist partly to reduce work, so we shouldn’t burden them with unnecessary indexes.

Different Metrics May Need Different Rollups

Not every sensor measurement should be summarized identically.

Temperature works naturally with:

Minimum
Maximum
Average

But consider a digital sensor:

Door open
Door closed

An average isn’t especially meaningful.

For an event-like metric, we might want:

Number of state changes
Time spent open
Time spent closed

For electricity:

Minimum power
Maximum power
Average power
Total energy

For network telemetry:

Bytes received
Bytes transmitted
Packet failures
Peak throughput

The rollup model should reflect the meaning of the data.

A generic MIN/MAX/AVG pipeline is useful, but it isn’t universally correct.

Counters Need Special Treatment

Suppose a sensor reports a cumulative electricity meter:

10:00    12500.2 kWh
11:00    12504.8 kWh

Averaging those numbers doesn’t tell us hourly consumption.

The useful calculation is approximately:

12504.8 - 12500.2 = 4.6 kWh

Counters, gauges, states, and events behave differently.

A mature telemetry system should classify metrics before deciding how to downsample them.

This prevents mathematically valid SQL from producing meaningless business information.

Missing Samples Must Remain Visible

Suppose a sensor normally reports 60 measurements per minute.

A rollup contains:

AverageValue = 21.5
SampleCount  = 60

Good.

Another minute contains:

AverageValue = 21.6
SampleCount  = 4

The average itself looks normal.

But the sample count reveals that the sensor was mostly silent.

This is one reason SampleCount is so important.

Historical summaries should preserve enough information to tell us something about data quality, not merely the calculated value.

Don’t Invent Missing Data

Suppose there are no readings between 02:00 and 03:00.

Avoid automatically creating:

Average = 0

Zero is a measurement.

No measurement is a different condition.

Depending on the application, a missing period might be represented by:

• No rollup row
  

• A row with a zero sample count
  

• A separate quality/status indicator
  

But don’t silently turn absence into a valid sensor value.

Downsampling and WAL

The telemetry collector may be writing new measurements while the rollup worker reads older measurements and writes summaries.

This is another workload where WAL mode is useful.

PRAGMA journal_mode = WAL;

The architecture might look like:

Sensor Collector
      ↓
Raw Telemetry
      ↓
SQLite WAL
      ↓
Rollup Worker
      ↓
Summary Tables

However, aggregation jobs should still avoid unnecessarily long transactions.

Process bounded windows.

Commit completed work.

Move forward.

That keeps the system responsive.

A Complete Downsampling Pipeline

We can now put everything together:

Sensors
   ↓
Raw Telemetry
   ↓
   ├── Recent dashboards
   ├── Alerts
   └── Troubleshooting
   ↓
Minute Rollups
   ↓
Hourly Rollups
   ↓
Daily Rollups
   ↓
Long-Term Historical Queries

Meanwhile:

Late Data
   ↓
Dirty Periods
   ↓
Recalculation

And:

Retention Worker
   ↓
Verify rollup state
   ↓
Verify retention age
   ↓
Delete old raw data

Instead of treating telemetry as one giant table, we’ve built a data lifecycle.

Example Retention Strategy

For an industrial monitoring system, we might choose:

Raw telemetry: 7 days

Used for detailed troubleshooting, recent alerts, and second-level charts.

Minute summaries: 30 days

Used for operational dashboards and recent trend analysis.

Hourly summaries: 2 years

Used for seasonal analysis, maintenance history, and long-range comparisons.

Daily summaries: 7 years

Used for long-term reporting and capacity planning.

Again, these aren’t magic numbers.

The important part is matching resolution to usefulness.

What Should Be Configurable?

Avoid hard-coding every retention decision.

A production system may need different policies for different metrics.

For example:

Vibration
Raw: 30 days

Temperature
Raw: 7 days

Battery level
Raw: 3 days

Critical safety sensor
Raw: 1 year

Retention policy can itself become data.

For example:

CREATE TABLE RetentionPolicy (
    MetricID INTEGER PRIMARY KEY,
    RawRetentionDays INTEGER NOT NULL,
    MinuteRetentionDays INTEGER,
    HourlyRetentionDays INTEGER,
    DailyRetentionDays INTEGER
);

Now storage policy can evolve without rewriting application logic.

Measure Before Choosing Retention

Retention decisions should not be based purely on intuition.

Measure:

Average readings per second
Average telemetry row size
Index size
Database growth per day
WAL growth
Available disk space
Historical query patterns
Cloud synchronization volume

If the database grows by 500 MB per day and the device has 20 GB available for telemetry, the limits become concrete.

Retention can then be designed around real capacity.

When Downsampling Should Happen Elsewhere

SQLite doesn’t need to perform every level of aggregation forever.

An edge device might keep:

7 days raw
30 days minute

and upload those summaries to a central platform.

The cloud could then generate:

hourly
daily
monthly
yearly

for fleet-wide analytics.

That gives us:

Sensor
   ↓
SQLite Edge Database
   ↓
Raw + Recent Rollups
   ↓
Cloud
   ↓
Long-Term Aggregation

The correct boundary depends on storage, connectivity, query requirements, and how much historical analysis must remain available locally.

Best Practices

When building a downsampling pipeline with SQLite:

• Keep recent telemetry at the resolution users actually need.
  

• Reduce resolution as data becomes older.
  

• Preserve minimum, maximum, sum, and sample count where appropriate.
  

• Don’t average averages without accounting for sample counts.
  

• Make rollup operations safe to retry.
  

• Use half-open time ranges to avoid boundary duplication.
  

• Allow for late-arriving measurements.
  

• Rebuild dependent rollups when lower tiers change.
  

• Track aggregation progress explicitly.
  

• Delete raw data only after required summaries are safely created.
  

• Use retention tiers rather than one universal expiration period.
  

• Delete old data in manageable transactions.
  

• Let SQLite reuse freed pages when that suits the workload.
  

• Choose rollup logic according to metric type.
  

• Preserve evidence of missing or incomplete samples.
  

• Query the resolution appropriate to the requested time range.
  

• Keep indexes focused on actual historical access patterns.
  

• Measure storage growth before choosing retention periods.
  

Closing Thoughts

Collecting telemetry is only the beginning.

A production system also needs to decide how that data should age.

Yesterday’s second-by-second readings may be essential for troubleshooting. Six months later, the minimum, maximum, average, and sample count for each hour may contain everything the application still needs.

Downsampling lets us make that transition deliberately.

With SQLite, we can keep recent telemetry rich and detailed, transform older measurements into efficient rollups, preserve important statistical information, handle late data safely, and remove raw history only after its useful information has been retained.

The result is not simply a smaller database.

It is a database designed around the changing value of information over time.

Store detail while it matters. Preserve the history that matters. Let everything else age gracefully.

Subscribe Now

Build Smarter Data Systems with SQLite

Storing data is only part of the challenge. Production systems also need to decide how much detail to keep, how long to keep it, and how to preserve useful history without letting databases grow forever.

Subscribe to SQLite Forum for practical tutorials, advanced SQLite techniques, and real-world architectures covering time-series data, performance, edge systems, synchronization, storage, and production-ready database design.

Subscribe and keep discovering what you can build with SQLite.