LabHub

Blog

TimescaleDB Time Series Database Operations Guide

2026-03-09 · 16 min read · #database#timescaledb#time-series#postgresql#2026-03

한국어English日本語

1. Why TimescaleDB

Time-series data — IoT sensors, infrastructure metrics, financial quotes, application logs — is the fastest-growing data type in modern systems. What this data has in common is continuous generation along a time axis, heavy INSERT traffic with almost no UPDATE, and queries centred on recent data.

PostgreSQL, as a general-purpose relational database, can store time-series data, but at a scale of billions of rows the performance of time-range queries falls off sharply. Even when you work around it with native partitioning, there is the operational burden of implementing partition management, compression and aggregate optimization all by yourself.

TimescaleDB runs as a PostgreSQL extension and tackles this problem head on. It provides automatic partitioning through hypertables, continuous aggregates, native compression and automatic data retention policies, while letting you keep using PostgreSQL's SQL syntax, indexes, JOINs and transactions exactly as before.

TimescaleDB 2.25, released in January 2026, introduced a ColumnarIndexScan execution path for compressed data, making MIN/MAX/FIRST/LAST queries up to 289x faster, and improving COUNT queries with a time filter by up to 50x.

2. TimescaleDB architecture and the PostgreSQL extension structure

Installation and initial setup

TimescaleDB is loaded as a PostgreSQL shared library and enabled with a single CREATE EXTENSION line.

# Installation on Ubuntu/Debian
sudo apt install timescaledb-2-postgresql-16

# Add shared_preload_libraries to the PostgreSQL configuration file
sudo timescaledb-tune --yes

# Restart PostgreSQL
sudo systemctl restart postgresql

# Enable the extension in the database
psql -d mydb -c "CREATE EXTENSION IF NOT EXISTS timescaledb;"

Internal architecture

The key point of TimescaleDB is the hypertable. To the user it looks like a single table, but internally it consists of several chunks that are split automatically along the time axis. Each chunk is an ordinary PostgreSQL table and holds the data for a given time interval (7 days by default).

User's view:
+-------------------------------------------+
|        metrics (Hypertable)                |
|  SELECT * FROM metrics                     |
|  WHERE time > now() - interval '1 hour'    |
+-------------------------------------------+

Internal structure:
+-----------+-----------+-----------+-----------+
| chunk_1   | chunk_2   | chunk_3   | chunk_4   |
| 02-15~21  | 02-22~28  | 03-01~06  | 03-07~now |
| [compressed] | [compressed] | [to compress] | [active]  |
+-----------+-----------+-----------+-----------+

The benefits this architecture provides are as follows.

3. Hypertable design and chunk management

Creating a hypertable

-- Create an ordinary table
CREATE TABLE sensor_data (
    time        TIMESTAMPTZ NOT NULL,
    device_id   TEXT        NOT NULL,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION,
    battery     DOUBLE PRECISION
);

-- Convert it to a hypertable (chunk interval of 1 day)
SELECT create_hypertable(
    'sensor_data',
    by_range('time', INTERVAL '1 day')
);

-- Add space partitioning (optional, for large environments)
SELECT add_dimension(
    'sensor_data',
    by_hash('device_id', 4)
);

A guide to setting the chunk interval

The chunk interval is the key tuning point for TimescaleDB performance. Both INSERT and SELECT are fast only when the index of the active chunk can stay resident in memory.

Rows inserted per dayRecommended chunk intervalWhy
Under 1 million7 days (default)Fewer chunks, so metadata overhead is minimized
1 million to 10 million1 dayBalances index size against chunk management
Over 10 million6 hours to 12 hoursKeeps the active index resident in memory
Over 100 million1 hour to 3 hoursKeeps the per-chunk index size within 25% of RAM
-- Change the chunk interval
SELECT set_chunk_time_interval('sensor_data', INTERVAL '12 hours');

-- List the current chunks
SELECT chunk_name, range_start, range_end,
       pg_size_pretty(total_bytes) AS size,
       is_compressed
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_data'
ORDER BY range_start DESC
LIMIT 10;

Monitoring chunk management

-- Detailed information per hypertable
SELECT hypertable_name,
       num_chunks,
       pg_size_pretty(hypertable_size(format('%I.%I',
           hypertable_schema, hypertable_name)::regclass)) AS total_size,
       pg_size_pretty(hypertable_size(format('%I.%I',
           hypertable_schema, hypertable_name)::regclass)
           - pg_total_relation_size(format('%I.%I',
               hypertable_schema, hypertable_name)::regclass)) AS chunk_size
FROM timescaledb_information.hypertables;

-- Find hypertables with an excessive number of chunks (over 1000 is worth attention)
SELECT hypertable_name, num_chunks
FROM timescaledb_information.hypertables
WHERE num_chunks > 1000;

4. Setting up and using continuous aggregates

Continuous aggregates precompute the aggregation results for time-series data and refresh them incrementally, which improves dashboard query performance dramatically. When new data arrives, only the changed portion is refreshed rather than everything being recomputed.

Creating a continuous aggregate

-- Create a 1-hour aggregate view
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
    device_id,
    time_bucket('1 hour', time) AS bucket,
    AVG(temperature)   AS avg_temp,
    MAX(temperature)   AS max_temp,
    MIN(temperature)   AS min_temp,
    AVG(humidity)      AS avg_humidity,
    COUNT(*)           AS sample_count
FROM sensor_data
GROUP BY device_id, bucket
WITH NO DATA;

-- 1-day aggregate (hierarchical continuous aggregate - a continuous aggregate on top of another)
CREATE MATERIALIZED VIEW sensor_daily
WITH (timescaledb.continuous) AS
SELECT
    device_id,
    time_bucket('1 day', bucket) AS bucket,
    AVG(avg_temp)      AS avg_temp,
    MAX(max_temp)      AS max_temp,
    MIN(min_temp)      AS min_temp,
    AVG(avg_humidity)  AS avg_humidity,
    SUM(sample_count)  AS sample_count
FROM sensor_hourly
GROUP BY device_id, bucket
WITH NO DATA;

Configuring the automatic refresh policy

-- Hourly aggregate: refresh the last 3 days at 1-hour intervals
SELECT add_continuous_aggregate_policy('sensor_hourly',
    start_offset  => INTERVAL '3 days',
    end_offset    => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour'
);

-- Daily aggregate: refresh the last 1 month at 1-day intervals
SELECT add_continuous_aggregate_policy('sensor_daily',
    start_offset  => INTERVAL '1 month',
    end_offset    => INTERVAL '1 day',
    schedule_interval => INTERVAL '1 day'
);

The meaning of each parameter is as follows.

Manual refresh and inspection

-- Refresh a specific range manually
CALL refresh_continuous_aggregate('sensor_hourly',
    '2026-03-01', '2026-03-09');

-- Check the state of the continuous aggregate policies
SELECT view_name, schedule_interval,
       config ->> 'start_offset' AS start_offset,
       config ->> 'end_offset'   AS end_offset
FROM timescaledb_information.continuous_aggregate_stats;

-- Check continuous aggregate query performance
EXPLAIN ANALYZE
SELECT device_id, bucket, avg_temp
FROM sensor_hourly
WHERE bucket >= now() - INTERVAL '7 days'
  AND device_id = 'sensor-001';

5. Data retention policies and compression

Configuring native compression

TimescaleDB's native compression converts row-based data into a columnar form for storage. Storage savings of 90% or more are typical.

-- Compression settings: specifying segmentby and orderby is the crux
ALTER TABLE sensor_data SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id',
    timescaledb.compress_orderby = 'time DESC'
);

-- A policy that automatically compresses chunks older than 7 days
SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

-- Check the compression result
SELECT
    pg_size_pretty(before_compression_total_bytes) AS before,
    pg_size_pretty(after_compression_total_bytes)  AS after,
    round(
        (1 - after_compression_total_bytes::numeric
             / before_compression_total_bytes) * 100, 1
    ) AS compression_ratio_pct
FROM hypertable_compression_stats('sensor_data');

Choosing the segmentby column: if the cardinality is too low (say 3 status values), compression efficiency drops; if it is too high, the number of segments becomes excessive. Generally, a column with somewhere between a few hundred and a few tens of thousands of distinct values is a good fit.

Data retention policy

-- Automatically drop raw data older than 90 days
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');

-- A longer retention period can be set for continuous aggregates
SELECT add_retention_policy('sensor_hourly', INTERVAL '1 year');
SELECT add_retention_policy('sensor_daily', INTERVAL '5 years');

-- Check policy execution status
SELECT application_name, schedule_interval,
       last_run_status, last_run_duration,
       next_start
FROM timescaledb_information.jobs
WHERE application_name LIKE '%retention%'
   OR application_name LIKE '%compress%';

The downsampling pipeline pattern

The pattern used most in practice is a staged data lifecycle: raw data -> continuous aggregate -> compression -> deletion.

[raw data]  --> [compress after 7 days] --> [drop after 90 days]
      |
      +-- [hourly continuous aggregate] --> [drop after 1 year]
              |
              +-- [daily continuous aggregate] --> [drop after 5 years]

Set up this way, recent data can be queried at second-level resolution while older data is kept long term in aggregated form. Storage costs can be cut by as much as 95% or more.

6. Index strategy and query optimization

The basic index strategy

When you create a hypertable, a B-tree index on the time column is created automatically. Additional indexes have to be designed around your query patterns.

-- When time-range queries per device are frequent
CREATE INDEX idx_sensor_device_time
ON sensor_data (device_id, time DESC);

-- A partial index for a specific condition (non-NULL values only)
CREATE INDEX idx_sensor_battery_low
ON sensor_data (device_id, time DESC)
WHERE battery < 20.0;

-- Check index efficiency
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size
FROM pg_stat_user_indexes
WHERE schemaname = '_timescaledb_internal'
ORDER BY idx_scan DESC
LIMIT 20;

Query optimization tips

-- GOOD: state the time range so chunk exclusion applies
SELECT device_id, AVG(temperature)
FROM sensor_data
WHERE time >= now() - INTERVAL '1 hour'
GROUP BY device_id;

-- BAD: without a time predicate, every chunk is scanned
SELECT device_id, AVG(temperature)
FROM sensor_data
GROUP BY device_id;

-- GOOD: a dashboard query that uses a continuous aggregate
SELECT device_id, bucket, avg_temp
FROM sensor_hourly
WHERE bucket >= now() - INTERVAL '24 hours'
ORDER BY bucket DESC;

-- Query performance analysis
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM sensor_data
WHERE time >= now() - INTERVAL '6 hours'
  AND device_id = 'sensor-042';

The core optimization principles are as follows.

7. TimescaleDB vs InfluxDB vs ClickHouse

Here are the three systems most often compared when choosing a time-series database.

ItemTimescaleDBInfluxDBClickHouse
FoundationPostgreSQL extensionDedicated engine (Go)Dedicated engine (C++)
Query languageStandard SQLFlux / InfluxQLSQL (non-standard extensions)
Storage modelRow-based + column compressionTSM (Time-Structured Merge)Columnar (MergeTree)
JOIN supportFull SQL JOINLimitedSupported (expensive)
ACID transactionsFully supportedNot supportedLimited
INSERT performanceAbout 1 million rows/secAbout 1 million rows/secAbout 4 million rows/sec
Compression ratio10-20x50-100x20-50x
Disk usageRelatively largeVery efficientEfficient
EcosystemThe whole PostgreSQL ecosystemDedicated Telegraf/GrafanaIndependent ecosystem
Learning curveLow (reuses SQL knowledge)Moderate (learning Flux)Moderate (non-standard SQL)
High availabilityPostgreSQL replicationClustering in Enterprise onlyNative clustering

How to choose

In practice, rather than picking just one, teams often combine them along the data lifecycle. For example, you can build a pipeline that handles real-time collection and alerting with InfluxDB, runs the control system that needs transactions on TimescaleDB, and performs long-term analytical queries on ClickHouse.

8. Building a production monitoring data pipeline

This is an end-to-end pipeline example that stores server metrics collected by Telegraf in TimescaleDB and visualizes them with Grafana.

Telegraf configuration

# telegraf.conf
[agent]
  interval = "10s"
  flush_interval = "10s"

[[inputs.cpu]]
  percpu = true
  totalcpu = true

[[inputs.mem]]

[[inputs.disk]]
  ignore_fs = ["tmpfs", "devtmpfs"]

[[inputs.net]]

[[outputs.postgresql]]
  connection = "host=localhost port=5432 user=telegraf dbname=metrics sslmode=disable"
  create_templates = [
    "CREATE TABLE IF NOT EXISTS {TABLE}({COLUMNS})",
    "SELECT create_hypertable('{TABLE}', by_range('time'), if_not_exists => true)",
  ]
  add_column_templates = [
    "ALTER TABLE {TABLE} ADD COLUMN IF NOT EXISTS {COLUMN} {TYPE}",
  ]
  tag_table_suffix = "_tag"

Example schema design

-- Server metrics table
CREATE TABLE server_metrics (
    time        TIMESTAMPTZ NOT NULL,
    host        TEXT        NOT NULL,
    region      TEXT,
    cpu_usage   DOUBLE PRECISION,
    mem_usage   DOUBLE PRECISION,
    disk_usage  DOUBLE PRECISION,
    net_in      BIGINT,
    net_out     BIGINT
);

SELECT create_hypertable('server_metrics', by_range('time', INTERVAL '1 day'));

-- Compression settings
ALTER TABLE server_metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'host',
    timescaledb.compress_orderby = 'time DESC'
);

-- Set the policies in one go
SELECT add_compression_policy('server_metrics', INTERVAL '3 days');
SELECT add_retention_policy('server_metrics', INTERVAL '30 days');

-- Continuous aggregate: 5-minute buckets
CREATE MATERIALIZED VIEW server_metrics_5m
WITH (timescaledb.continuous) AS
SELECT
    host,
    time_bucket('5 minutes', time) AS bucket,
    AVG(cpu_usage)  AS avg_cpu,
    MAX(cpu_usage)  AS max_cpu,
    AVG(mem_usage)  AS avg_mem,
    MAX(mem_usage)  AS max_mem,
    AVG(disk_usage) AS avg_disk
FROM server_metrics
GROUP BY host, bucket
WITH NO DATA;

SELECT add_continuous_aggregate_policy('server_metrics_5m',
    start_offset    => INTERVAL '7 days',
    end_offset      => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '5 minutes'
);

-- Continuous aggregate: 1-hour buckets (hierarchical)
CREATE MATERIALIZED VIEW server_metrics_1h
WITH (timescaledb.continuous) AS
SELECT
    host,
    time_bucket('1 hour', bucket) AS bucket,
    AVG(avg_cpu)  AS avg_cpu,
    MAX(max_cpu)  AS max_cpu,
    AVG(avg_mem)  AS avg_mem,
    MAX(max_mem)  AS max_mem,
    AVG(avg_disk) AS avg_disk
FROM server_metrics_5m
GROUP BY host, bucket
WITH NO DATA;

SELECT add_continuous_aggregate_policy('server_metrics_1h',
    start_offset    => INTERVAL '30 days',
    end_offset      => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour'
);

9. Troubleshooting and operational cautions

Issues around chunk locks

Dropping a chunk (drop_chunks) or compressing one requires an exclusive lock on that chunk. If another session is referencing the chunk, the operation times out and fails.

-- Check locks on the chunk
SELECT pid, mode, granted, relation::regclass
FROM pg_locks
WHERE relation IN (
    SELECT format('%I.%I', chunk_schema, chunk_name)::regclass
    FROM timescaledb_information.chunks
    WHERE hypertable_name = 'sensor_data'
      AND NOT is_compressed
);

-- Terminate the offending session (caution: work in progress is rolled back)
SELECT pg_terminate_backend(<pid>);

Handling compression failures

Compressing a large chunk can fail because temp_file_limit or maintenance_work_mem is insufficient.

-- Relax the limits temporarily, then compress manually
SET temp_file_limit = '10GB';
SET maintenance_work_mem = '2GB';
SELECT compress_chunk('<chunk_name>');

Background workers stop running

Sometimes the scheduled policies (compression, retention, continuous aggregate refresh) do not run.

-- Restart the background workers
SELECT timescaledb_pre_restore();
SELECT timescaledb_post_restore();

-- Check jobs that failed to run
SELECT job_id, application_name, last_run_status,
       last_run_started_at, last_run_duration,
       total_failures
FROM timescaledb_information.job_stats
WHERE last_run_status = 'Failed';

Delay in continuous aggregate refresh

Data newer than the continuous aggregate's end_offset is not included in the aggregate. If the newest data appears to be missing from a dashboard, check this setting.

10. Failure cases and recovery procedures

Case 1: metadata explosion from too small a chunk interval

Problem: the chunk interval was set to 1 minute, which created hundreds of thousands of chunks. The query planner took several seconds just to process the metadata, and every query became slow.

Recovery:

-- Change the chunk interval to a sensible value
SELECT set_chunk_time_interval('sensor_data', INTERVAL '1 day');

-- Clean up the old small chunks manually (via the retention policy)
SELECT drop_chunks('sensor_data', older_than => INTERVAL '30 days');

Prevention: monitor so that the chunk count does not exceed 1,000, and set an interval that matches the daily INSERT volume.

Case 2: attempting UPDATE/DELETE after compression

Problem: running UPDATE or DELETE against an already compressed chunk raises an error or is extremely slow.

Recovery:

-- Decompress, then modify
SELECT decompress_chunk('<chunk_name>');
-- Perform the UPDATE or DELETE
UPDATE sensor_data SET temperature = NULL
WHERE time = '2026-03-01 12:00:00' AND device_id = 'sensor-bad';
-- Compress again
SELECT compress_chunk('<chunk_name>');

Prevention: the data you compress should be data that is no longer changing. Set the compress_after interval long enough.

Case 3: INSERT failures from running out of disk space

Problem: the disk filled up before the retention policy ran, and new data INSERTs failed.

Recovery:

-- Emergency chunk drop
SELECT drop_chunks('sensor_data', older_than => INTERVAL '7 days');

-- Immediately compress old chunks that are not compressed yet
SELECT compress_chunk(c.chunk_name)
FROM timescaledb_information.chunks c
WHERE c.hypertable_name = 'sensor_data'
  AND NOT c.is_compressed
  AND c.range_end < now() - INTERVAL '2 days'
ORDER BY c.range_start ASC;

Prevention: set an alert at 80% disk utilization and adjust the retention policy interval to match the rate of disk growth.

Backup and recovery

# Logical backup with pg_dump
pg_dump -Fc -f backup.dump mydb

# Calling pre_restore before recovery is mandatory
psql -d mydb -c "SELECT timescaledb_pre_restore();"
pg_restore -d mydb backup.dump
psql -d mydb -c "SELECT timescaledb_post_restore();"

11. Operations checklist

Here are the items to check without fail when running TimescaleDB in production.

Design stage

Policy configuration

Monitoring

Backup and recovery

12. Wrapping up

TimescaleDB is a practical choice that keeps PostgreSQL's reliability and ecosystem while delivering performance specialized for time-series workloads. There are three key points.

  1. Set an appropriate chunk interval: tune it to the daily data volume so that the active index can stay resident in memory.
  2. Combine continuous aggregates with compression: compress and then drop the raw data, and retain the aggregated data long term, optimizing storage and query performance at the same time.
  3. Policy-based automation: automate compression, retention and continuous aggregate refresh entirely through policies to reduce the operational burden.

What sets it apart from InfluxDB and ClickHouse is SQL compatibility and ACID transaction support. If you already have a PostgreSQL environment and need JOINs against relational data, TimescaleDB is the most natural choice.

References

Read the original: https://www.youngju.dev/blog/database/2026-03-09-timescaledb-time-series-operations-guide.en

Comments

No comments yet.

Sign in to leave a comment