TimescaleDB Tutorial: Learn Time-Series SQL from Scratch (2026)
I have run TimescaleDB in production for years. You get the power of PostgreSQL with automatic partitioning for time-series data using standard SQL.
We will explore hypertables, time-based partitioning, compression, continuous aggregates, and operational patterns for running TimescaleDB at scale.
Installing TimescaleDB and Creating Hypertables
TimescaleDB is a PostgreSQL extension. Add the apt repository and install. On Docker, use the timescaledb image. Add timescaledb to shared_preload_libraries.
A hypertable is a PostgreSQL table automatically partitioned by time. Create a regular table and call create_hypertable(). Chunks are created automatically.
CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE TABLE sensor_data (time TIMESTAMPTZ NOT NULL, sensor_id INT NOT NULL, temperature DOUBLE PRECISION, humidity DOUBLE PRECISION);
SELECT create_hypertable('sensor_data', 'time', chunk_time_interval => INTERVAL '1 day');Querying Time-Series Data with SQL
Use time_bucket() for fixed-width time windows. TimescaleDB provides first(), last(), delta(), time_bucket_gapfill(), and approx_percentile().
Gapfilling produces evenly-spaced time series from irregularly sampled data using LOCF (last observation carried forward).
SELECT time_bucket('10 minutes', time) AS bucket, sensor_id, AVG(temperature) AS avg_temp FROM sensor_data WHERE time > now() - INTERVAL '24 hours' GROUP BY bucket, sensor_id ORDER BY bucket;
SELECT time_bucket_gapfill('5 minutes', time) AS bucket, LOCF(AVG(temperature)) AS avg_temp FROM sensor_data WHERE time BETWEEN '2026-07-01' AND '2026-07-02' GROUP BY bucket;Compression: Saving 90% on Storage
TimescaleDB uses columnar compression (delta-delta, gorilla, LZ) adapted for time series. Most users see 90-95% compression ratios.
Configure compression per hypertable: specify columns to compress and segment by column. Queries on compressed data are transparently decompressed.
ALTER TABLE sensor_data SET (timescaledb.compress, timescaledb.compress_segmentby = 'sensor_id', timescaledb.compress_orderby = 'time DESC');
SELECT add_compression_policy('sensor_data', INTERVAL '7 days');Continuous Aggregates for Real-Time Downsampling
Continuous aggregates are materialized views that refresh incrementally as new data arrives, unlike PostgreSQL materialized views that block reads during refresh.
Define a refresh lag to exclude real-time data from the materialized view, avoiding recomputation for late-arriving data.
CREATE MATERIALIZED VIEW hourly_sensor_stats WITH (timescaledb.continuous) AS SELECT time_bucket('1 hour', time) AS bucket, sensor_id, AVG(temperature) AS avg_temp FROM sensor_data GROUP BY bucket, sensor_id;
SELECT add_continuous_aggregate_policy('hourly_sensor_stats', start_offset => INTERVAL '3 days', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '30 minutes');Data Retention and Chunk Management
TimescaleDB creates chunks based on chunk_time_interval. Drop old chunks with retention policies, which is O(1) vs expensive DELETE.
Choose chunk interval for 10-50 chunks per hypertable. Too many chunks cause high metadata overhead; too few make compression and reordering inefficient.
SELECT add_retention_policy('sensor_data', INTERVAL '30 days');
SELECT drop_chunks('sensor_data', INTERVAL '30 days');
SELECT chunk_table, range_start, range_end FROM timescaledb_information.chunks WHERE hypertable_name = 'sensor_data' ORDER BY range_start;Distributed Hypertables and Multi-Node Deployment
TimescaleDB supports distributed hypertables across data nodes. An access node plans queries; data nodes store chunks.
Data distribution uses a space partition key. Queries are pushed down to data nodes for parallel execution and aggregated at the access node.
SELECT add_data_node('dn1', host => 'dn1.example.com', port => 5432);
SELECT add_data_node('dn2', host => 'dn2.example.com', port => 5432);
SELECT create_distributed_hypertable('sensor_data', 'time', partitioning_column => 'sensor_id', number_partitions => 2);Frequently Asked Questions
Is TimescaleDB free?
TimescaleDB is open-source under Apache 2.0. TimescaleDB Cloud has a free tier. Self-hosted is available at no cost.
Can I use standard PostgreSQL tools with TimescaleDB?
Yes. All PostgreSQL tools work: pg_dump, pgAdmin, psql, and all ORMs. You get the full PostgreSQL ecosystem plus time-series features.
When should I choose TimescaleDB over InfluxDB?
Choose TimescaleDB for standard SQL, joins with relational data, or PostgreSQL compatibility. InfluxDB may be better for very high write throughput.
How does TimescaleDB handle high-cardinality data?
TimescaleDB handles high cardinality well using PostgreSQL B-tree indexes. Very high cardinality can increase chunk sizes; use space partitioning if needed.
Originally published on Ayodhyyya. Last updated June 1, 2026.