/setup-timescaledb-hypertables

name: setup-timescaledb-hypertables
description: |
  Use 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.

  **Trigger when user asks to:**
  - Create or design SQL schemas/tables AND Timescale/TimescaleDB/TigerData/Tiger Cloud is available
  - Set up hypertables, compression, retention policies, or continuous aggregates
  - Configure partition columns, segment_by, order_by, or chunk intervals
  - Optimize time-series database performance or storage
  - Create tables for sensors, metrics, telemetry, events, or transaction logs

  **Keywords:** CREATE TABLE, hypertable, Timescale, TimescaleDB, time-series, IoT, metrics, sensor data, compression policy, continuous aggregates, columnstore, retention policy, chunk interval, segment_by, order_by

  Step-by-step instructions for hypertable creation, column selection, compression policies, retention, continuous aggregates, and indexes.
license: Apache-2.0
compatibility: Requires PostgreSQL 15+ with TimescaleDB
metadata:
  author: tigerdata

TimescaleDB Complete Setup

Instructions for insert-heavy data patterns where data is inserted but rarely changed:

• **Time-series data** (sensors, metrics, system monitoring)
• **Event logs** (user events, audit trails, application logs)
• **Transaction records** (orders, payments, financial transactions)
• **Sequential data** (records with auto-incrementing IDs and timestamps)
• **Append-only datasets** (immutable records, historical data)

Step 1: Create Hypertable

CREATE TABLE your_table_name (
    timestamp TIMESTAMPTZ NOT NULL,
    entity_id TEXT NOT NULL,          -- device_id, user_id, symbol, etc.
    category TEXT,                    -- sensor_type, event_type, asset_class, etc.
    value_1 DOUBLE PRECISION,         -- price, temperature, latency, etc.
    value_2 DOUBLE PRECISION,         -- volume, humidity, throughput, etc.
    value_3 INTEGER,                  -- count, status, level, etc.
    metadata JSONB                    -- flexible additional data
) WITH (
    tsdb.hypertable,
    tsdb.partition_column='timestamp',
    tsdb.enable_columnstore=true,     -- Disable if table has vector columns
    tsdb.segmentby='entity_id',       -- See selection guide below
    tsdb.orderby='timestamp DESC',     -- See selection guide below
    tsdb.sparse_index='minmax(value_1),minmax(value_2),minmax(value_3)' -- see selection guide below
);

Compression Decision

• **Enable by default** for insert-heavy patterns
• **Disable** if table has vector type columns (pgvector) - indexes on vector columns incompatible with columnstore

Partition Column Selection

Must be time-based (TIMESTAMP/TIMESTAMPTZ/DATE) or integer (INT/BIGINT) with good temporal/sequential distribution.

**Common patterns:**

• TIME-SERIES: `timestamp`, `event_time`, `measured_at`
• EVENT LOGS: `event_time`, `created_at`, `logged_at`
• TRANSACTIONS: `created_at`, `transaction_time`, `processed_at`
• SEQUENTIAL: `id` (auto-increment when no timestamp), `sequence_number`
• APPEND-ONLY: `created_at`, `inserted_at`, `id`

**Less ideal:** `ingested_at` (when data entered system - use only if it's your primary query dimension) **Avoid:** `updated_at` (breaks time ordering unless it's primary query dimension)

Segment_By Column Selection

**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.

**Requirements:**

• Frequently used in WHERE clauses (most common filter)
• Good row density (>100 rows per value per chunk)
• Primary logical partition/grouping

**Examples:**

• IoT: `device_id`
• Finance: `symbol`
• Metrics: `service_name`, `service_name, metric_type` (if sufficient row density), `metric_name, metric_type` (if sufficient row density)
• Analytics: `user_id` if sufficient row density, otherwise `session_id`
• E-commerce: `product_id` if sufficient row density, otherwise `category_id`

**Row density guidelines:**

• Target: >100 rows per segment_by value within each chunk.
• Poor: <10 rows per segment_by value per chunk → choose less granular column
• What to do with low-density columns: prepend to order_by column list.

**Query pattern drives choice:**

SELECT * FROM table WHERE entity_id = 'X' AND timestamp > ...
-- ↳ segment_by: entity_id (if >100 rows per chunk)

**Avoid:** timestamps, unique IDs, low-density columns (<100 rows/value/chunk), columns rarely used in filtering

Order_By Column Selection

Creates natural time-series progression when combined with segment_by for optimal compression.

**Most common:** `timestamp DESC`

**Examples:**

• IoT/Finance/E-commerce: `timestamp DESC`
• Metrics: `metric_name, timestamp DESC` (if metric_name has too low density for segment_by)
• Analytics: `user_id, timestamp DESC` (user_id has too low density for segment_by)

**Alternative patterns:**

• `sequence_id DESC` for event streams with sequence numbers
• `timestamp DESC, event_order DESC` for sub-ordering within same timestamp

**Low-density column handling:** If a column has <100 rows per chunk (too low for segment_by), prepend it to order_by:

• Example: `metric_name` has 20 rows/chunk → use `segment_by='service_name'`, `order_by='metric_name, timestamp DESC'`
• Groups similar values together (all temperature readings, then pressure readings) for better compression

**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

Compression Sparse Index Selection

**Sparse indexes** enable query filtering on compressed data without decompress