Evaluating engineering tools? Get the comparison in Google Sheets

Connection

Connect to TimescaleDB via psql
psql -h hostname -p port -U username -d dbname

Connect using a connection string
psql 'postgresql://username:password@hostname:port/dbname'

Hypertable Management

Create a hypertable
SELECT create_hypertable('table_name', 'time_column');

Create hypertable with specific chunk interval
SELECT create_hypertable('table_name', 'time_column', chunk_time_interval => INTERVAL '1 day');

Add dimensions to a hypertable
SELECT add_dimension('table_name', 'dimension_column', number_partitions => 4);

List all hypertables
SELECT * FROM timescaledb_information.hypertables;

Get information about chunks
SELECT * FROM timescaledb_information.chunks;

Data Management

Insert data
INSERT INTO hypertable_name (time, value) VALUES (NOW(), 100);

Select data with time range
SELECT * FROM hypertable_name WHERE time > NOW() - INTERVAL '1 day';

Time bucket aggregation
SELECT time_bucket('1 hour', time) AS hour, AVG(value) FROM hypertable_name GROUP BY hour;

Retention Policy

Add retention policy
SELECT add_retention_policy('hypertable_name', INTERVAL '1 month');

Remove retention policy
SELECT remove_retention_policy('hypertable_name');

Show retention policies
SELECT * FROM timescaledb_information.jobs WHERE proc_name = 'policy_retention';

Continuous Aggregates

Create continuous aggregate
CREATE MATERIALIZED VIEW continuous_agg_name WITH (timescaledb.continuous) AS SELECT time_bucket('1 hour', time) as bucket, AVG(value) FROM hypertable_name GROUP BY bucket;

Add refresh policy
SELECT add_continuous_aggregate_policy('continuous_agg_name', start_offset => INTERVAL '1 day', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '1 hour');

Manually refresh
CALL refresh_continuous_aggregate('continuous_agg_name', NULL, NOW());

Compression

Enable compression
ALTER TABLE hypertable_name SET (timescaledb.compress = true);

Add compression policy
SELECT add_compression_policy('hypertable_name', INTERVAL '7 days');

Compress chunks manually
SELECT compress_chunk(chunk) FROM show_chunks('hypertable_name', older_than => INTERVAL '7 days') AS chunk;

Maintenance

Show scheduled jobs
SELECT * FROM timescaledb_information.jobs;

Reorder chunks
SELECT reorder_chunk(chunk, 'index_name') FROM show_chunks('hypertable_name') AS chunk;

View detailed stats
SELECT * FROM timescaledb_information.hypertable_stats;