TimescaleDB Cheat Sheet

Setup & Installation


         CREATE EXTENSION IF NOT EXISTS timescaledb;

Enable TimescaleDB extension in PostgreSQL


         SELECT timescaledb_version();

Check TimescaleDB version


         \dx timescaledb

Show extension details (psql)

Hypertables


         SELECT create_hypertable('table_name', 'time_column');

Convert table to hypertable


         create_hypertable('metrics', 'time', chunk_time_interval => INTERVAL '1 day')

Create hypertable with custom chunk interval


         create_hypertable('data', 'time', partitioning_column => 'device_id', number_partitions => 4)

Hypertable with space partitioning


         SELECT show_chunks('table_name');

List all chunks for hypertable


         SELECT drop_chunks('table_name', INTERVAL '3 months');

Drop chunks older than 3 months


         SELECT set_chunk_time_interval('table_name', INTERVAL '7 days');

Change chunk interval


         SELECT hypertable_size('table_name');

Get total hypertable size


         SELECT chunks_detailed_size('table_name');

Get size of each chunk

Continuous Aggregates


         CREATE MATERIALIZED VIEW conditions_hourly WITH (timescaledb.continuous) AS SELECT ...

Create continuous aggregate


         time_bucket('1 hour', time) AS hour

Group data into hourly buckets


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

Auto-refresh policy for aggregate


         CALL refresh_continuous_aggregate('view_name', NULL, NULL);

Manually refresh continuous aggregate


         SELECT remove_continuous_aggregate_policy('view_name');

Remove auto-refresh policy


         DROP MATERIALIZED VIEW view_name;

Delete continuous aggregate

Time Functions


         time_bucket('5 minutes', time)

Group timestamps into 5-min intervals


         time_bucket_gapfill('1 hour', time)

Fill gaps in time series


         locf(value)

Last observation carried forward (fill gaps)


         interpolate(value)

Linear interpolation for missing values


         first(value, time)

Get first value in time bucket


         last(value, time)

Get last value in time bucket


         histogram(value, min, max, num_buckets)

Create value histogram

Compression


         ALTER TABLE table_name SET (timescaledb.compress);

Enable compression on hypertable


         ALTER TABLE table_name SET (timescaledb.compress, timescaledb.compress_segmentby = 'device_id');

Compress with segment grouping


         ALTER TABLE table_name SET (timescaledb.compress_orderby = 'time DESC');

Set compression ordering


         SELECT add_compression_policy('table_name', INTERVAL '7 days');

Auto-compress chunks older than 7 days


         SELECT compress_chunk(chunk_name);

Manually compress specific chunk


         SELECT decompress_chunk(chunk_name);

Decompress chunk for updates


         SELECT hypertable_compression_stats('table_name');

View compression statistics


         SELECT remove_compression_policy('table_name');

Remove auto-compression policy

Data Retention


         SELECT add_retention_policy('table_name', INTERVAL '6 months');

Auto-delete data older than 6 months


         SELECT remove_retention_policy('table_name');

Remove retention policy


         SELECT show_chunks('table_name', older_than => INTERVAL '1 year');

Find chunks older than 1 year


         SELECT drop_chunks('table_name', older_than => INTERVAL '1 year');

Manually drop old chunks

Downsampling


         CREATE MATERIALIZED VIEW conditions_daily AS SELECT time_bucket('1 day', time), AVG(temp) FROM conditions GROUP BY 1;

Create downsampled view


         WITH (timescaledb.continuous)

Make view continuously updated


         SELECT add_continuous_aggregate_policy(...)

Schedule automatic downsampling

Indexing & Performance


         CREATE INDEX ON table_name (device_id, time DESC);

Create index for common queries


         SELECT set_integer_now_func('table_name', 'unix_now');

Use integer timestamps (microseconds)


         SELECT reorder_chunk(chunk_name, 'table_name_time_idx');

Physically reorder chunk by index


         SELECT add_reorder_policy('table_name', 'table_name_time_idx');

Auto-reorder new chunks


         EXPLAIN ANALYZE SELECT ...

Analyze query performance

Multi-Node


         SELECT add_data_node('node1', host => 'host1');

Add data node to cluster


         SELECT create_distributed_hypertable('table', 'time', 'device_id');

Create distributed hypertable


         SELECT * FROM timescaledb_information.data_nodes;

List all data nodes


         SELECT delete_data_node('node1');

Remove data node

Common Queries


         SELECT time_bucket('1 hour', time), AVG(value) FROM metrics WHERE time > NOW() - INTERVAL '1 day' GROUP BY 1 ORDER BY 1;

Hourly averages for last 24 hours


         SELECT DISTINCT ON (device_id) * FROM readings ORDER BY device_id, time DESC;

Latest reading per device


         SELECT time_bucket_gapfill('5 min', time), locf(AVG(temp)) FROM conditions WHERE time > NOW() - INTERVAL '1 hour' GROUP BY 1;

Fill gaps with last value


         SELECT first(value, time), last(value, time) FROM metrics WHERE time > NOW() - INTERVAL '1 day' GROUP BY time_bucket('1 hour', time);

First and last values per hour

Monitoring & Info


         SELECT * FROM timescaledb_information.hypertables;

List all hypertables


         SELECT * FROM timescaledb_information.chunks;

View chunk information


         SELECT * FROM timescaledb_information.continuous_aggregates;

List continuous aggregates


         SELECT * FROM timescaledb_information.compression_settings;

View compression settings


         SELECT * FROM timescaledb_information.jobs;

View scheduled jobs (policies)


         SELECT * FROM timescaledb_information.job_stats;

Job execution statistics

Best Practices


         chunk_time_interval => INTERVAL '1 week'

Size chunks based on insert rate (1-2GB)


         CREATE INDEX ON table (column, time DESC);

Include time in indexes for range queries


         WHERE time > NOW() - INTERVAL '1 day'

Use time filters to limit chunk scans


         SELECT compress_chunk(show_chunks('table', older_than => INTERVAL '7 days'));

Compress older data to save space


         timescaledb.compress_segmentby = 'device_id, location'

Group by high-cardinality columns