By Tiger Data Team
Updated at Jun 12, 2026
Most teams that downsample time-series data do it in Python. A batch job summarizes rows into a DataFrame, writes aggregates back to a table, and runs on a schedule. This works until it doesn't: the job falls behind, raw data bloats storage, and dashboards show stale curves with missing peaks. Moving downsampling into the database fixes all three problems. It runs continuously, scales with your data, and preserves curve shape through the entire data lifecycle.
This guide covers when to downsample, which method to use, and how to build the full tiered resolution ladder in SQL. Tiger Data builds a database purpose-built for time-series data, so the examples here use our toolset. Alternatives are noted where relevant.
Time-series downsampling is the process of reducing the temporal resolution of time-series data by replacing multiple raw data points within a time window with a single representative value.
A sensor emitting readings every second produces 86,400 data points per day. Over a year, querying that raw data for dashboard rendering or long-term trend analysis becomes slow and expensive. Downsampling solves this by trading resolution for performance and cost.
Engineers downsample for three reasons:
Storage cost reduction: fewer rows stored means lower storage bills, especially for high-frequency IoT and infrastructure data
Query performance: pre-aggregated data returns results orders of magnitude faster than scanning raw rows
Visualization readability: a chart renderer displays 500 points identically to 50,000; the human eye cannot distinguish them at dashboard scale
Downsampling is not the same as data compression. Compression reduces the storage footprint of raw data without altering its resolution. Downsampling reduces resolution itself. Both are complementary strategies for long-term time-series data management.
Three signals indicate it is time to downsample:
Query latency is growing as your hypertable ages past 30-90 days of raw data
Storage costs are scaling linearly with data volume, and your long-term analysis can tolerate lower granularity
Grafana (or equivalent) renders identically whether it receives 10,000 or 1,000 data points for a given time range
One situation where downsampling is not appropriate: forensic replay of raw events. Security event logs, compliance audit trails, and medical device records must retain full raw resolution. Downsampling here creates gaps that can invalidate investigations or violate regulatory requirements.
Downsample | Keep raw |
IoT sensor telemetry (temperature, humidity, vibration) | Security event logs |
Infrastructure metrics (CPU, memory, network) | Financial transaction records |
Energy and utility consumption readings | Medical device event records |
Dashboards aggregating more than 1 week of data | Data with regulatory retention requirements |
Tiger Data provides four approaches to downsampling, each suited to a different context. This section covers all four; the decision framework appears at the end of the article.
time_bucket() + aggregate: general-purpose, standard SQL
lttb() hyperfunction: visualization-optimized, shape-preserving
asap_smooth() hyperfunction: smoothing-optimized, noise-reducing
Continuous aggregates: database-level automation of any of the above
time_bucket() Averagingtime_bucket() divides a time-series into equal-width intervals. Aggregate functions (AVG, MAX, MIN, SUM) collapse each interval to a single row.
Given a sensor readings table:
CREATE TABLE sensor_readings (
time TIMESTAMPTZ NOT NULL,
sensor_id INT,
temperature FLOAT
) WITH (timescaledb.hypertable);A 1-hour average downsampled query looks like this:
SELECT
time_bucket('1 hour', time) AS hour,
sensor_id,
AVG(temperature) AS avg_temp
FROM sensor_readings
GROUP BY 1, 2
ORDER BY 1, 2;Strengths: Simple, requires no extension beyond TimescaleDB, works with any column type, and is compatible with all aggregate functions including SUM, COUNT, MIN, and MAX.
Limitation: Averaging destroys the shape of the time-series. A sensor that spikes to 95°C for 10 seconds will show only a fraction of a degree above baseline in a 1-hour average; the spike is effectively invisible. For anomaly detection or alerting use cases, this matters.
Best for: Storage reduction and long-term trend queries where the general trajectory matters more than the exact curve shape.
Learn more about time_bucket in the Tiger Data docs.
lttb(): Largest Triangle Three BucketsLTTB selects a fixed number of representative points from a larger dataset such that the resulting line chart is visually indistinguishable from the original.
The algorithm was developed by Sveinn Steinarsson and validated by the tsdownsample benchmark study published in ScienceDirect in January 2025, which benchmarks leading downsampling algorithms for visualization. Tiger Data's lttb() hyperfunction is the SQL-native implementation.
SELECT
time,
value
FROM unnest(
(SELECT lttb(time, temperature, 200)
FROM sensor_readings
WHERE time > NOW() - INTERVAL '7 days')
);This returns 200 representative (time, value) pairs drawn from the original dataset. The output must be unnested before use in a SELECT list.
Strengths: Preserves curve shape including peaks, valleys, and inflection points. Output is visually lossless for dashboard rendering.
Limitation: lttb() selects original data points rather than computing new aggregate values. It cannot be used for SUM or running totals. It is primarily a read-time function for visualization, not a write-time storage reduction tool.
Best for: Grafana panels and chart rendering where visual fidelity is required, and when you want to downsample for display without altering stored data.
See the full lttb() API reference for complete syntax.
asap_smooth(): Automated Smoothing and Predictabilityasap_smooth() returns a smoothed version of the time-series that removes high-frequency noise while preserving the overall trend shape. It automatically selects the degree of smoothing.
SELECT
time,
value
FROM unnest(
(SELECT asap_smooth(time, temperature, 100)
FROM sensor_readings
WHERE time > NOW() - INTERVAL '30 days')
);Contrast with LTTB: lttb() selects original data points from the dataset. asap_smooth() computes new smoothed values that were not present in the raw data. Use lttb() when you want to show actual data with fewer points. Use asap_smooth() when you want to present the trend signal without noise.
Best for: Dashboards where high-frequency noise obscures the trend, and executive-level summaries of sensor telemetry where a clean signal matters more than individual data points.
Limitation: ASAP output is computed values rather than original data points. The smoothed series is not suitable for anomaly detection or alerting thresholds, since it removes the very spikes those systems need to detect.
See the asap_smooth() API reference for full options.
time_bucket(), lttb(), and asap_smooth() all operate at query time. Continuous aggregates operate at write time: they precompute aggregations incrementally as new data arrives, so queries hit precomputed results rather than raw rows.
A continuous aggregate is a materialized view defined with a time_bucket() group, refreshed automatically on a schedule (or in real time as data lands). This makes continuous aggregates the production-grade implementation of time_bucket() downsampling, not a separate concept but an automated wrapper around it.
Continuous aggregates can also be stacked hierarchically. An hourly CAGG built on top of a 10-minute CAGG is the mechanism behind the tiered resolution ladder described in the next section.
For the full guide on creating and managing continuous aggregates, see Continuous Aggregates in Tiger Data. For refresh policy syntax, see the continuous aggregates documentation.
This is the production pattern most practitioners are searching for: raw data at full resolution for 1 day, 10-minute averages for 1 week, hourly averages for 1 month, daily averages for 1 year or longer. All of it is maintained automatically by the database. It is the most-requested pattern in community forums, and no existing guide implements it end-to-end in SQL.
CREATE TABLE sensor_readings (
time TIMESTAMPTZ NOT NULL,
sensor_id INT,
temperature FLOAT
) WITH (timescaledb.hypertable);CREATE MATERIALIZED VIEW readings_10min
WITH (timescaledb.continuous) AS
SELECT
time_bucket('10 minutes', time) AS bucket,
sensor_id,
AVG(temperature) AS avg_temp
FROM sensor_readings
GROUP BY 1, 2;
SELECT add_continuous_aggregate_policy('readings_10min',
start_offset => INTERVAL '1 hour',
end_offset => INTERVAL '10 minutes',
schedule_interval => INTERVAL '10 minutes'
);This is the pattern that surprises practitioners: you can aggregate an aggregate. The hourly CAGG reads from readings_10min, not from the raw table.
CREATE MATERIALIZED VIEW readings_hourly
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 hour', bucket) AS bucket,
sensor_id,
AVG(avg_temp) AS avg_temp
FROM readings_10min
GROUP BY 1, 2;
SELECT add_continuous_aggregate_policy('readings_hourly',
start_offset => INTERVAL '1 day',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour'
);Retention policies drop the raw data and lower-resolution CAGGs once they are no longer needed.
-- Drop raw data older than 1 day
SELECT add_retention_policy('sensor_readings', INTERVAL '1 day');
-- Drop 10-minute rollups older than 1 week
SELECT add_retention_policy('readings_10min', INTERVAL '1 week');
-- Drop hourly rollups older than 1 month
SELECT add_retention_policy('readings_hourly', INTERVAL '1 month');Order of operations matters. Apply the retention policy on raw data only after confirming the CAGG is fully refreshed. Dropping raw data before the CAGG has processed it creates permanent data loss. Verify CAGG population before enabling raw data retention.
For more on data retention policies, including how to check policy status and handle edge cases, see the dedicated guide.
The result: this pattern gives you queryable daily averages going back years, while keeping the last day of raw data available for forensic queries. You do not have to choose between raw access and historical coverage.
Downsampling with continuous aggregates is non-destructive. The CAGG is a separate materialized view. Raw data stays exactly where it is until a retention policy explicitly drops it. You control whether and when raw data is deleted. Adding a CAGG alone changes nothing about the raw table.
Averaging destroys the shape of the time-series. A sensor that spikes to 95°C for 10 seconds will show only a fraction of a degree above baseline in a 10-minute average; the spike is effectively invisible. Peaks, valleys, and inflection points disappear. For visualization, lttb() selects actual data points that preserve curve shape. Use averaging for storage reduction when the trend matters; use lttb() when visual accuracy matters.
Visualization is one use case. Storage cost reduction and long-term analytics are equally valid and often more impactful. A hypertable growing at 1M rows per day generates roughly 365M rows per year at raw resolution. Daily averages per sensor reduce that to 365 rows per day per sensor, a reduction of several orders of magnitude for high-cardinality device fleets.
Downsampling in Python requires extracting data from the database, transforming it in a DataFrame, and writing results back. That adds latency, network overhead, and ETL complexity. Doing it in the database, where the data already lives, eliminates the round-trip, runs continuously without a scheduler to maintain, and scales with the database rather than with application servers.
The tiered resolution ladder gives you both. Raw data is available for the most recent window (for example, 1 day). Lower-resolution aggregates cover longer history. Queries route to the appropriate layer automatically. You get forensic access to recent data and fast long-range analytics over historical data without managing two separate storage systems.
For hyperfunctions for time-series analysis beyond downsampling, see the overview of all available Tiger Data hyperfunctions.
Choose time_bucket() averaging if:
You need storage reduction and trend accuracy matters more than shape preservation
You are running a one-off query or exploratory analysis rather than building production automation
Your aggregate function is SUM, COUNT, or MIN/MAX rather than AVG
Choose lttb() if:
You are rendering a Grafana panel or chart and need visual fidelity with fewer data points
You want to downsample for display without altering stored data
Visualization latency is the problem (too many points to render smoothly)
Choose asap_smooth() if:
High-frequency noise is obscuring the underlying trend in charts
Your audience is non-technical and needs a clean signal without manual smoothing configuration
Choose continuous aggregates if:
You need automated, production-grade downsampling that runs without manual queries
You are building the tiered resolution ladder
Query performance on historical data is degrading as the hypertable grows
You want to combine downsampling with a retention policy for full lifecycle management
Time-series downsampling is the process of reducing the temporal resolution of time-series data by replacing multiple raw data points within a time window with a single representative value: an average, a selected point, or a smoothed value. Engineers downsample to reduce storage cost, improve query performance, and produce cleaner visualizations.
The primary tool is time_bucket() combined with an aggregate function such as AVG, MAX, or MIN. For automated downsampling, define a continuous aggregate, a materialized view that refreshes incrementally as data arrives.
Downsampling is the goal: reducing data resolution. Continuous aggregates are one mechanism for achieving it automatically. A time_bucket() + AVG query at read time is manual downsampling. A continuous aggregate is the same operation precomputed and stored, refreshed on a schedule.
Create the base hypertable, then create a continuous aggregate at each resolution tier (10-minute, hourly, daily), with each tier built on the previous one. Add a retention policy to each tier that drops data older than its window.
LTTB (Largest Triangle Three Buckets) is the standard for visualization downsampling. It selects original data points that preserve the visual shape of the line. Use asap_smooth() when you want to reduce noise. Use averaging when computing aggregate metrics where the shape of the curve is not the goal.
Ingest raw readings into a hypertable, create a hierarchical set of continuous aggregates (10-minute, hourly, daily), and apply a retention policy to the raw table after the first CAGG tier is consistently populated.
Downsampling reduces resolution. With continuous aggregates, aggregate values at each tier are preserved permanently. The tradeoff is that you lose the ability to reconstruct individual raw readings older than the raw retention window.
Do not attach a retention policy to the raw hypertable. A continuous aggregate stores the downsampled data independently. Raw data persists until retention is applied. You can query raw data and downsampled data simultaneously at any time.
LTTB selects representative data points that preserve visual shape including peaks and valleys. Simple averaging collapses a time window to a single computed value, potentially losing short spikes entirely. Use lttb() for visualization. Use averaging for storage and analytics where the trend matters more than individual events.
They work as a pair: continuous aggregates create lower-resolution copies of your data; retention policies drop the higher-resolution source after a defined window. Always verify CAGG population before enabling retention on raw data to avoid permanent data loss.