TimescaleDB

TimescaleDB is a PostgreSQL extension for time-series workloads. It turns a regular table into a hypertable: rows are partitioned into time-based chunks automatically, while applications keep using ordinary SQL, indexes and transactions.

pgcli installs the Pigsty TSL build of TimescaleDB 2.31.0. Hypertables, time_bucket, chunk management, columnstore/compression, continuous aggregates and retention policies are available. The TSL permits free internal use but prohibits offering a time-series database as a service.

Use cases

TimescaleDB fits append-heavy data where time is the main access dimension:

• IoT and device telemetry — per-device readings, latest values and time-window aggregates.
• Infrastructure and application monitoring — CPU, memory, latency and request metrics for dashboards and alerting.
• Industrial and energy metering — high-frequency sensor streams with a long retention window.
• Financial market data — ticks, quotes and trades; OHLC-style rollups with time_bucket and first/last.
• Product and event analytics — user events, sessions and API calls aggregated over recent time windows.
• Smart home and building automation — sensor history and state changes.

The typical shape is append-only rows keyed by (entity_id, time): queries filter a recent time range, and old data is expired by dropping whole chunks.

It is a poor fit for OLTP workloads without a time dimension, key-value or document storage, and blob storage. The TSL build includes compression, continuous aggregates and retention policies, but the TSL does not allow offering a time-series database as a service.

Install

pg extension install timescaledb -i pg01 --auto-restart

pgcli builds an extension image with the Pigsty TSL package, puts timescaledb first in shared_preload_libraries, restarts PostgreSQL and runs CREATE EXTENSION IF NOT EXISTS timescaledb in the instance’s default database.

To use it from another database in the same instance:

pg psql -i pg01 -- -d appdb
CREATE EXTENSION IF NOT EXISTS timescaledb;

Check the installed version and license:

pg exec -i pg01 "SELECT extversion FROM pg_extension WHERE extname = 'timescaledb'"
pg exec -i pg01 "SHOW timescaledb.license"

The Pigsty package reports timescale.

Quick start

1. Create an instance

pg create -i tsdb
pg start -i tsdb

pg create writes the configuration and prints the admin password; pg start brings up PostgreSQL and the backup container. The default database is tsdb_db.

2. Install the extension

pg extension install timescaledb -i tsdb --auto-restart

pgcli builds the extension image, preloads timescaledb, restarts PostgreSQL and runs CREATE EXTENSION IF NOT EXISTS timescaledb in tsdb_db.

3. Create a hypertable

Connect to the instance:

pg psql -i tsdb

Create a table and convert it to a hypertable:

CREATE TABLE sensor (
    time      timestamptz NOT NULL,
    sensor_id text NOT NULL,
    value     double precision
);

SELECT create_hypertable('sensor', by_range('time', INTERVAL '1 day'));
CREATE INDEX ON sensor (sensor_id, time DESC);

Or create it in one step; the default chunk interval applies and can be changed afterwards:

CREATE TABLE sensor (
    time      timestamptz NOT NULL,
    sensor_id text NOT NULL,
    value     double precision
) WITH (tsdb.hypertable);

SELECT set_chunk_time_interval('sensor', INTERVAL '1 day');

Choose the chunk interval to fit the query and retention window. Existing chunks keep their interval when you change it later.

4. Write data

INSERT INTO sensor
SELECT now() - (i * INTERVAL '1 minute'),
       'sensor_' || (i % 3 + 1),
       random() * 100
FROM generate_series(1, 1440) AS i;

SELECT time_bucket('1 hour', time) AS bucket,
       sensor_id,
       round(avg(value)::numeric, 2) AS avg_value
FROM sensor
WHERE time > now() - INTERVAL '24 hours'
GROUP BY bucket, sensor_id
ORDER BY bucket DESC, sensor_id;

5. Daily operations

Append new rows, inspect chunks and jobs, and enable automatic maintenance:

INSERT INTO sensor VALUES (now(), 'sensor_1', 42.0);

SELECT show_chunks('sensor');
SELECT * FROM timescaledb_information.jobs;

ALTER TABLE sensor SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'time DESC'
);
CALL add_columnstore_policy('sensor', INTERVAL '7 days');
SELECT add_retention_policy('sensor', INTERVAL '365 days');

Operational commands:

pg status -i tsdb               # instance and backup status
pg logs -i tsdb -n 100          # server log tail
pg export -i tsdb -o sensor.dump

See Compression and columnstore, Retention policies, Continuous aggregates and Managing chunks for the full set.

InfluxDB-compatible API: the influx2tsdb addon

If existing tooling speaks InfluxDB rather than SQL, the influx2tsdb addon runs a managed, stateless proxy that serves the InfluxDB 1.x HTTP API (Line Protocol writes and InfluxQL queries) on top of a TimescaleDB database:

• each InfluxDB database becomes a PostgreSQL schema, and each measurement becomes a hypertable with time as the partition column;
• CREATE / ALTER / DROP RETENTION POLICY is translated into TimescaleDB’s own add_retention_policy / add_compression_policy jobs — no pg_cron is needed, and chunk cleanup continues while the proxy is stopped;
• the proxy holds no data, so backup, PITR, replication and HA remain ordinary PostgreSQL operations.

pg addon install influx2tsdb --pg-name tsdb

The trade-off is throughput: every request is translated to SQL, so pure time-series ingest and query load is slower than native InfluxDB. The proxy has no authentication or TLS; front it with nginx or isolate the port. See the addon page for flags, the data model, retention semantics and the test scripts.

Query patterns

First and last value per series:

SELECT sensor_id,
       first(value, time) AS first_value,
       last(value, time) AS last_value
FROM sensor
GROUP BY sensor_id;

Latest row per series:

SELECT DISTINCT ON (sensor_id) sensor_id, time, value
FROM sensor
ORDER BY sensor_id, time DESC;

Daily buckets in a specific timezone:

SELECT time_bucket('1 day', time, 'Asia/Shanghai') AS day,
       count(*)
FROM sensor
GROUP BY day
ORDER BY day DESC;

Fill gaps in a bucket sequence:

SELECT time_bucket_gapfill('1 hour', time) AS bucket,
       avg(value)
FROM sensor
WHERE time BETWEEN now() - INTERVAL '6 hours' AND now()
GROUP BY bucket
ORDER BY bucket;

time_bucket_gapfill needs an explicit start and finish, either as the second and third arguments or in the WHERE clause.

Compression and columnstore

Older chunks can be moved to the columnstore, which compresses them and speeds up analytical scans:

ALTER TABLE sensor SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'time DESC'
);

-- Compress chunks older than 7 days; runs as a background job
CALL add_columnstore_policy('sensor', INTERVAL '7 days');

Check the compression status and the space used:

SELECT * FROM hypertable_columnstore_stats('sensor');
SELECT * FROM chunk_columnstore_stats('sensor');

Retention policies

Expire chunks automatically once they fall outside the retention window:

SELECT add_retention_policy('sensor', INTERVAL '365 days');

-- Inspect or remove the policy
SELECT * FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

SELECT remove_retention_policy('sensor');

These jobs run in TimescaleDB’s own background scheduler; pg_cron is not required. The influx2tsdb addon uses the same mechanism: its CREATE / ALTER / DROP RETENTION POLICY statements are translated into these jobs automatically, and cleanup continues while the proxy is stopped.

Continuous aggregates

Pre-aggregate recent data and keep the rollup refreshed in the background:

CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
       sensor_id,
       avg(value) AS avg_value
FROM sensor
GROUP BY bucket, sensor_id;

SELECT add_continuous_aggregate_policy('sensor_hourly',
    start_offset      => INTERVAL '3 hours',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

Query the rollup like a normal table:

SELECT * FROM sensor_hourly
ORDER BY bucket DESC
LIMIT 10;

The policy refreshes only the recent window between start_offset and end_offset; refresh older ranges manually after a backfill.

Managing chunks

Chunk intervals

Set the interval at creation, or change it later for new chunks:

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

-- Existing chunks keep their ranges; new chunks use 6 hours
SELECT set_chunk_time_interval('sensor', INTERVAL '6 hours');

SELECT time_interval
FROM timescaledb_information.dimensions
WHERE hypertable_name = 'sensor';

For timestamptz columns, chunk boundaries align to UTC. Choose an interval large enough that a chunk fits comfortably in memory; a very small interval creates many chunks and adds planning overhead.

List chunks

SELECT show_chunks('sensor');
SELECT show_chunks('sensor', older_than => INTERVAL '30 days');
SELECT show_chunks('sensor', newer_than => INTERVAL '1 day');

SELECT chunk_name, range_start, range_end
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor'
ORDER BY range_start DESC
LIMIT 10;

older_than and newer_than filter by the chunk’s time range, not by its creation time.

Chunk sizes

SELECT chunk_name,
       pg_size_pretty(pg_total_relation_size(
           format('%I.%I', chunk_schema, chunk_name)::regclass)) AS total_size
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor'
ORDER BY range_start DESC;

SELECT pg_size_pretty(hypertable_size('sensor'));
SELECT * FROM hypertable_detailed_size('sensor');

Split a chunk

Split one chunk at a timestamp inside its range; take the chunk name from show_chunks('sensor'):

CALL split_chunk('_timescaledb_internal._hyper_1_2_chunk',
                 '2026-10-26 12:00:00+00'::timestamptz);

split_chunk is a procedure; use CALL, not SELECT.

Remove chunks

-- Chunks entirely older than 90 days, or newer than tomorrow
SELECT count(*) FROM drop_chunks('sensor', older_than => INTERVAL '90 days');
SELECT count(*) FROM drop_chunks('sensor', newer_than => now() + INTERVAL '1 day');

drop_chunks removes files instead of producing dead tuples. Avoid deleting large time ranges with DELETE. For automatic expiration prefer a retention policy; pg_cron is only needed for a custom schedule:

pg extension install pg_cron -i pg01 --auto-restart

SELECT cron.schedule('drop-sensor-chunks', '0 3 * * *',
  $$SELECT drop_chunks('sensor', older_than => INTERVAL '90 days')$$);

Manual columnstore conversion

With compression settings configured (see Compression and columnstore), convert a single chunk without waiting for the policy:

CALL convert_to_columnstore('_timescaledb_internal._hyper_1_1_chunk');
SELECT chunk_name, is_compressed
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor'
ORDER BY range_start DESC;

CALL convert_to_rowstore('_timescaledb_internal._hyper_1_1_chunk');

Both procedures take a chunk name, not the hypertable name.

Metadata views

SELECT * FROM timescaledb_information.hypertables;
SELECT * FROM timescaledb_information.dimensions;
SELECT * FROM timescaledb_information.chunks;
SELECT * FROM timescaledb_information.jobs;

jobs also lists the columnstore, retention and continuous-aggregate policies created above, alongside the internal job-history task.

Feature availability

Rows marked ✅ Apache follow Apache-2.0. Rows marked ✅ TSL come from the Timescale License: free for internal use, but offering a time-series database as a service is not permitted.

Backup and restore

Logical backups and pg export/pg import work as usual. A physical restore (pgBackRest, used by pg backup/pg restore) also needs TimescaleDB’s restore hooks:

SELECT timescaledb_pre_restore();   -- before replacing the data files, when possible
-- restore the instance
SELECT timescaledb_post_restore();  -- after the restored instance starts

pg restore does not run these automatically, so run timescaledb_post_restore() after the restore completes. Test the procedure before relying on it.

Permissions

CREATE EXTENSION requires a superuser; pg exec connects as the instance admin. The owner of a table can convert it with create_hypertable and manage its chunks. Application roles need only ordinary table privileges.

Troubleshooting

extension "timescaledb" is not available. The package is missing from the instance image, or the extension was not created in the target database. Run pg extension install timescaledb -i <instance> and create it in that database.

timescaledb is not preloaded. The extension is in shared_preload_libraries but PostgreSQL was not restarted. pg extension install handles this; verify with SHOW shared_preload_libraries; and restart the instance.

License errors for a function. The instance image still contains the Apache build (an older pgcli release or a manually installed PGDG package). Reinstall with pg extension install timescaledb -i <instance> --auto-restart to switch to the Pigsty TSL package.

cannot create a unique index without the column "time". Every unique index and primary key on a hypertable must include the partitioning column:

CREATE TABLE sensor (
    time      timestamptz NOT NULL,
    sensor_id text NOT NULL,
    value     double precision,
    PRIMARY KEY (sensor_id, time)
);

Too many chunks. Lower the chunk interval with set_chunk_time_interval; existing chunks are not rewritten.

Old data does not disappear. Add a retention policy with add_retention_policy, or run drop_chunks manually. Check timescaledb_information.jobs for the policy job.

Function reference