name: setup-timescaledb-hypertables\ndescription: |\nUse this skill when creating database schemas or tables for Timescale, TimescaleDB, TigerData, or Tiger Cloud, especially for time-series, IoT, metrics, events, or log data. Use this to improve the performance of any insert-heavy table.
\nTrigger when user asks to:
\nKeywords: CREATE TABLE, hypertable, Timescale, TimescaleDB, time-series, IoT, metrics, sensor data, compression policy, continuous aggregates, columnstore, retention policy, chunk interval, segment_by, order_by
\nInstructions for insert-heavy data patterns where data is inserted but rarely changed:
\nCREATE TABLE your_table_name (\n timestamp TIMESTAMPTZ NOT NULL,\n entity_id TEXT NOT NULL, -- device_id, user_id, symbol, etc.\n category TEXT, -- sensor_type, event_type, asset_class, etc.\n value_1 DOUBLE PRECISION, -- price, temperature, latency, etc.\n value_2 DOUBLE PRECISION, -- volume, humidity, throughput, etc.\n value_3 INTEGER, -- count, status, level, etc.\n metadata JSONB -- flexible additional data\n) WITH (\n tsdb.hypertable,\n tsdb.partition_column='timestamp',\n tsdb.enable_columnstore=true, -- Disable if table has vector columns\n tsdb.segmentby='entity_id', -- See selection guide below\n tsdb.orderby='timestamp DESC', -- See selection guide below\n tsdb.sparse_index='minmax(value_1),minmax(value_2),minmax(value_3)' -- see selection guide below\n);\nMust be time-based (TIMESTAMP/TIMESTAMPTZ/DATE) or integer (INT/BIGINT) with good temporal/sequential distribution.
\nCommon patterns:
\ntimestamp, event_time, measured_atevent_time, created_at, logged_atcreated_at, transaction_time, processed_atid (auto-increment when no timestamp), sequence_numbercreated_at, inserted_at, idLess ideal: ingested_at (when data entered system - use only if it's your primary query dimension)\nAvoid: updated_at (breaks time ordering unless it's primary query dimension)
PREFER SINGLE COLUMN - multi-column rarely optimal. Multi-column can only work for highly correlated columns (e.g., metric_name + metric_type) with sufficient row density.
\nRequirements:
\nExamples:
\ndevice_idsymbolservice_name, service_name, metric_type (if sufficient row density), metric_name, metric_type (if sufficient row density)user_id if sufficient row density, otherwise session_idproduct_id if sufficient row density, otherwise category_idRow density guidelines:
\nQuery pattern drives choice:
\nSELECT * FROM table WHERE entity_id = 'X' AND timestamp > ...\n-- ↳ segment_by: entity_id (if >100 rows per chunk)\nAvoid: timestamps, unique IDs, low-density columns (<100 rows/value/chunk), columns rarely used in filtering
\nCreates natural time-series progression when combined with segment_by for optimal compression.
\nMost common: timestamp DESC
Examples:
\ntimestamp DESCmetric_name, timestamp DESC (if metric_name has too low density for segment_by)user_id, timestamp DESC (user_id has too low density for segment_by)Alternative patterns:
\nsequence_id DESC for event streams with sequence numberstimestamp DESC, event_order DESC for sub-ordering within same timestampLow-density column handling:\nIf a column has <100 rows per chunk (too low for segment_by), prepend it to order_by:
\nmetric_name has 20 rows/chunk → use segment_by='service_name', order_by='metric_name, timestamp DESC'Good test: ordering created by (segment_by_column, order_by_column) should form a natural time-series progression. Values close to each other in the progression should be similar.
Avoid in order_by: random columns, columns with high variance between adjacent rows, columns unrelated to segment_by
\nSparse indexes enable query filtering on compressed data without decompression. Store metadata per batch (~1000 rows) to eliminate batches that don't match query predicates.
\nTypes:
\nUse minmax for: price, temperature, measurement, timestamp (range filtering)
\nUse for:
\ncreated_at, minmax on updated_at is useful).Avoid: rarely filtered columns.
\nIMPORTANT: NEVER index columns in segmentby or orderby. Orderby columns will always have minmax indexes without any configuration.
\nConfiguration:\nThe format is a comma-separated list of type_of_index(column_name).
\nALTER TABLE table_name SET (\n timescaledb.sparse_index = 'minmax(value_1),minmax(value_2)'\n);\nExplicit configuration available since v2.22.0 (was auto-created since v2.16.0).
\nDefault: 7 days (use if volume unknown, or ask user). Adjust based on volume:
\nSELECT set_chunk_time_interval('your_table_name', INTERVAL '1 day');\nGood test: recent chunk indexes should fit in less than 25% of RAM.
\nCommon index patterns - composite indexes on an id and timestamp:
\nCREATE INDEX idx_entity_timestamp ON your_table_name (entity_id, timestamp DESC);\nImportant: Only create indexes you'll actually use - each has maintenance overhead.
\nPrimary key and unique constraints rules: Must include partition column.
\nOption 1: Composite PK with partition column
\nALTER TABLE your_table_name ADD PRIMARY KEY (entity_id, timestamp);\nOption 2: Single-column PK (only if it's the partition column)
\nCREATE TABLE ... (id BIGINT PRIMARY KEY, ...) WITH (tsdb.partition_column='id');\nOption 3: No PK: strict uniqueness is often not required for insert-heavy patterns.
\nIMPORTANT: If you used tsdb.enable_columnstore=true in Step 1, starting with TimescaleDB version 2.23 a columnstore policy is automatically created with after => INTERVAL '7 days'. You only need to call add_columnstore_policy() if you want to customize the after interval to something other than 7 days.
Set after interval for when: data becomes mostly immutable (some updates/backfill OK) AND B-tree indexes aren't needed for queries (less common criterion).
-- In TimescaleDB 2.23 and later only needed if you want to override the default 7-day policy created by tsdb.enable_columnstore=true\n-- Remove the existing auto-created policy first:\n-- CALL remove_columnstore_policy('your_table_name');\n-- Then add custom policy:\n-- CALL add_columnstore_policy('your_table_name', after => INTERVAL '1 day');\nIMPORTANT: Don't guess - ask user or comment out if unknown.
\n-- Example - replace with requirements or comment out\nSELECT add_retention_policy('your_table_name', INTERVAL '365 days');\nUse different aggregation intervals for different uses.
\nFor up-to-the-minute dashboards on high-frequency data.
\nCREATE MATERIALIZED VIEW your_table_hourly\nWITH (timescaledb.continuous) AS\nSELECT\n time_bucket(INTERVAL '1 hour', timestamp) AS bucket,\n entity_id,\n category,\n COUNT(*) as record_count,\n AVG(value_1) as avg_value_1,\n MIN(value_1) as min_value_1,\n MAX(value_1) as max_value_1,\n SUM(value_2) as sum_value_2\nFROM your_table_name\nGROUP BY bucket, entity_id, category;\nFor long-term reporting and analytics.
\nCREATE MATERIALIZED VIEW your_table_daily\nWITH (timescaledb.continuous) AS\nSELECT\n time_bucket(INTERVAL '1 day', timestamp) AS bucket,\n entity_id,\n category,\n COUNT(*) as record_count,\n AVG(value_1) as avg_value_1,\n MIN(value_1) as min_value_1,\n MAX(value_1) as max_value_1,\n PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value_1) as median_value_1,\n PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY value_1) as p95_value_1,\n SUM(value_2) as sum_value_2\nFROM your_table_name\nGROUP BY bucket, entity_id, category;\nSet up refresh policies based on your data freshness requirements.
\nstart_offset: Usually omit (refreshes all). Exception: If you don't care about refreshing data older than X (see below). With retention policy on raw data: match the retention policy.
\nend_offset: Set beyond active update window (e.g., 15 min if data usually arrives within 10 min). Data newer than end_offset won't appear in queries without real-time aggregation. If you don't know your update window, use the size of the time_bucket in the query, but not less than 5 minutes.
\nschedule_interval: Set to the same value as the end_offset but not more than 1 hour.
\nHourly - frequent refresh for dashboards:
\nSELECT add_continuous_aggregate_policy('your_table_hourly',\n start_offset => NULL,\n end_offset => INTERVAL '15 minutes',\n schedule_interval => INTERVAL '15 minutes');\nDaily - less frequent for reports:
\nSELECT add_continuous_aggregate_policy('your_table_daily',\n start_offset => NULL,\n end_offset => INTERVAL '1 hour',\n schedule_interval => INTERVAL '1 hour');\nUse start_offset only if you don't care about refreshing old data\nUse for high-volume systems where query accuracy on older data doesn't matter:
\n-- the following aggregate can be stale for data older than 7 days\n-- SELECT add_continuous_aggregate_policy('aggregate_for_last_7_days',\n-- start_offset => INTERVAL '7 days', -- only refresh last 7 days (NULL = refresh all)\n-- end_offset => INTERVAL '15 minutes',\n-- schedule_interval => INTERVAL '15 minutes');\nIMPORTANT: you MUST set a start_offset to be less than the retention policy on raw data. By default, set the start_offset equal to the retention policy.\nIf the retention policy is commented out, comment out the start_offset as well. like this:
\nSELECT add_continuous_aggregate_policy('your_table_daily',\n start_offset => NULL, -- Use NULL to refresh all data, or set to retention period if enabled on raw data\n-- start_offset => INTERVAL '<retention period here>', -- uncomment if retention policy is enabled on the raw data table\n end_offset => INTERVAL '1 hour',\n schedule_interval => INTERVAL '1 hour');\nReal-time combines materialized + recent raw data at query time. Provides up-to-date results at the cost of higher query latency.
\nMore useful for fine-grained aggregates (e.g., minutely) than coarse ones (e.g., daily/monthly) since large buckets will be mostly incomplete with recent data anyway.
\nDisabled by default in v2.13+, before that it was enabled by default.
\nUse when: Need data newer than end_offset, up-to-minute dashboards, can tolerate higher query latency\nDisable when: Performance critical, refresh policies sufficient, high query volume, missing and stale data for recent data is acceptable
\nEnable for current results (higher query cost):
\nALTER MATERIALIZED VIEW your_table_hourly SET (timescaledb.materialized_only = false);\nDisable for performance (but with stale results):
\nALTER MATERIALIZED VIEW your_table_hourly SET (timescaledb.materialized_only = true);\nRule: segment_by = ALL GROUP BY columns except time_bucket, order_by = time_bucket DESC
\n-- Hourly\nALTER MATERIALIZED VIEW your_table_hourly SET (\n timescaledb.enable_columnstore,\n timescaledb.segmentby = 'entity_id, category',\n timescaledb.orderby = 'bucket DESC'\n);\nCALL add_columnstore_policy('your_table_hourly', after => INTERVAL '3 days');\n\n-- Daily\nALTER MATERIALIZED VIEW your_table_daily SET (\n timescaledb.enable_columnstore,\n timescaledb.segmentby = 'entity_id, category',\n timescaledb.orderby = 'bucket DESC'\n);\nCALL add_columnstore_policy('your_table_daily', after => INTERVAL '7 days');\nAggregates are typically kept longer than raw data.\nIMPORTANT: Don't guess - ask user or you MUST comment out if unknown.
\n-- Example - replace or comment out\nSELECT add_retention_policy('your_table_hourly', INTERVAL '2 years');\nSELECT add_retention_policy('your_table_daily', INTERVAL '5 years');\nIndex strategy: Analyze WHERE clauses in common queries → Create indexes matching filter columns + time ordering
\nPattern: (filter_column, bucket DESC) supports WHERE filter_column = X AND bucket >= Y ORDER BY bucket DESC
Examples:
\nCREATE INDEX idx_hourly_entity_bucket ON your_table_hourly (entity_id, bucket DESC);\nCREATE INDEX idx_hourly_category_bucket ON your_table_hourly (category, bucket DESC);\nMulti-column filters: Create composite indexes for WHERE entity_id = X AND category = Y:
CREATE INDEX idx_hourly_entity_category_bucket ON your_table_hourly (entity_id, category, bucket DESC);\nImportant: Only create indexes you'll actually use - each has maintenance overhead.
\nOnly for query patterns where you ALWAYS filter by the space-partition column with expert knowledge and extensive benchmarking. STRONGLY prefer time-only partitioning.
\n-- Check hypertable\nSELECT * FROM timescaledb_information.hypertables\nWHERE hypertable_name = 'your_table_name';\n\n-- Check compression settings\nSELECT * FROM hypertable_compression_stats('your_table_name');\n\n-- Check aggregates\nSELECT * FROM timescaledb_information.continuous_aggregates;\n\n-- Check policies\nSELECT * FROM timescaledb_information.jobs ORDER BY job_id;\n\n-- Monitor chunk information\nSELECT\n chunk_name,\n range_start,\n range_end,\n is_compressed\nFROM timescaledb_information.chunks\nWHERE hypertable_name = 'your_table_name'\nORDER BY range_start DESC;\ntimescaledb-tune for self-hosting (auto-configured on cloud)TIMESTAMPTZ NOT timestamp>= and < NOT BETWEEN for timestampsTEXT with constraints NOT char(n)/varchar(n)snake_case NOT CamelCaseBIGINT GENERATED ALWAYS AS IDENTITY NOT SERIALBIGINT for IDs by default over INTEGER or SMALLINTDOUBLE PRECISION by default over REAL/FLOATNUMERIC NOT MONEYNOT EXISTS NOT NOT INtime_bucket() or date_trunc() NOT timestamp(0) for truncationDeprecated Parameters → New Parameters:
\ntimescaledb.compress → timescaledb.enable_columnstoretimescaledb.compress_segmentby → timescaledb.segmentbytimescaledb.compress_orderby → timescaledb.orderbyDeprecated Functions → New Functions:
\nadd_compression_policy() → add_columnstore_policy()remove_compression_policy() → remove_columnstore_policy()compress_chunk() → convert_to_columnstore() (use with CALL, not SELECT)decompress_chunk() → convert_to_rowstore() (use with CALL, not SELECT)Compression Stats (use functions, not views):
\nhypertable_compression_stats('table_name')chunk_compression_stats('_timescaledb_internal._hyper_X_Y_chunk')columnstore_settings may not be available in all versions; use functions insteadManual Compression Example:
\n-- Compress a specific chunk\nCALL convert_to_columnstore('_timescaledb_internal._hyper_7_1_chunk');\n\n-- Check compression statistics\nSELECT\n number_compressed_chunks,\n pg_size_pretty(before_compression_total_bytes) as before_compression,\n pg_size_pretty(after_compression_total_bytes) as after_compression,\n ROUND(100.0 * (1 - after_compression_total_bytes::numeric / NULLIF(before_compression_total_bytes, 0)), 1) as compression_pct\nFROM hypertable_compression_stats('your_table_name');\n