TimescaleDB PostgreSQL Time-Series Extension Cheat Sheet

Back to DatabasesUpdated 2026-05-15

TimescaleDB is a PostgreSQL extension that transforms Postgres into a high-performance time-series database optimized for storing, querying, and analyzing timestamped data at scale. Built as a native extension rather than a fork, TimescaleDB maintains full PostgreSQL compatibility while adding automatic time-based partitioning (hypertables), columnar compression (90-95% storage reduction), continuous aggregates for precomputed rollups, and advanced time-series analytics functions. What sets TimescaleDB apart is its hybrid architecture: data remains in standard PostgreSQL tables under the hood, so you retain all Postgres features (ACID transactions, JOINs, indexes, extensions) while gaining automatic chunk management, intelligent query planning, and purpose-built hyperfunctions. The key mental model: hypertables are abstractions over many time-partitioned chunks, and TimescaleDB handles partitioning, compression, retention, and aggregate refresh automatically in the background — you write SQL as usual, and the extension optimizes everything for time-series workloads without application changes.

What This Cheat Sheet Covers

This topic spans 12 focused tables and 83 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Hypertable Creation and ConfigurationTable 2: Chunk Management and Time PartitioningTable 3: Compression Policies and ConfigurationTable 4: Continuous Aggregates (Materialized Rollups)Table 5: Time Bucketing and Gap FillingTable 6: Hyperfunctions (Time-Series Analytics)Table 7: Data Retention and LifecycleTable 8: Query Performance and OptimizationTable 9: Batch Ingestion and High-Throughput WritesTable 10: Distributed Hypertables (Deprecated Feature)Table 11: Integration and MonitoringTable 12: Deployment Options and Editions

Table 1: Hypertable Creation and Configuration

Function	Example	Description
create_hypertable()	`SELECT create_hypertable('metrics', 'time');`	Converts a standard PostgreSQL table into a hypertable with automatic time-based partitioning into chunks; must be run on an empty table or specify `migrate_data => true` for existing data.
chunk_time_interval	`SELECT create_hypertable('sensors', 'ts',` `chunk_time_interval => INTERVAL '1 day');`	Sets the time range each chunk covers; default is 7 days but should be tuned based on ingest rate and query patterns (1 day for high-volume, 1 month for low-volume).
set_chunk_time_interval()	`SELECT set_chunk_time_interval('metrics',` `INTERVAL '3 days');`	Changes the chunk interval for future chunks only; existing chunks remain unchanged; useful for adapting to changing data volumes.
add_dimension()	`SELECT add_dimension('metrics', 'device_id',` `number_partitions => 4);`	Adds a hash-based space partition on a second column (e.g., device_id, tenant_id) to distribute data across multiple chunks within each time interval; multi-tenant isolation pattern.

Table 1: Hypertable Creation and Configuration

Function	Example	Description
create_hypertable()	`SELECT create_hypertable('metrics', 'time');`	Converts a standard PostgreSQL table into a hypertable with automatic time-based partitioning into chunks; must be run on an empty table or specify `migrate_data => true` for existing data.
chunk_time_interval	`SELECT create_hypertable('sensors', 'ts',` `chunk_time_interval => INTERVAL '1 day');`	Sets the time range each chunk covers; default is 7 days but should be tuned based on ingest rate and query patterns (1 day for high-volume, 1 month for low-volume).
set_chunk_time_interval()	`SELECT set_chunk_time_interval('metrics',` `INTERVAL '3 days');`	Changes the chunk interval for future chunks only; existing chunks remain unchanged; useful for adapting to changing data volumes.
add_dimension()	`SELECT add_dimension('metrics', 'device_id',` `number_partitions => 4);`	Adds a hash-based space partition on a second column (e.g., device_id, tenant_id) to distribute data across multiple chunks within each time interval; multi-tenant isolation pattern.