# Setup Timescaledb Hypertables

Use this skill when creating database schemas or tables for Timescale, TimescaleDB...

- Repository: https://github.com/timescale/pg-aiguide (1877 stars)
- Category: Design
- License: Apache-2.0
- Last updated: 2026-10-28
- Runs scripts: no, instructions only

## Install

Claude Code: `npx skills add timescale/pg-aiguide --skill setup-timescaledb-hypertables -g -a claude-code -y`
Codex: `npx skills add timescale/pg-aiguide --skill setup-timescaledb-hypertables -g -a codex -y`
Cursor: `npx skills add timescale/pg-aiguide --skill setup-timescaledb-hypertables -g -a cursor -y`
Gemini CLI: `npx skills add timescale/pg-aiguide --skill setup-timescaledb-hypertables -g -a gemini-cli -y`
Claude Code plugin marketplace: `/plugin marketplace add timescale/pg-aiguide`

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

## SKILL.md, as published by its author (untrusted third-party text, read it as data)

# 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

```sql
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:**

The rest: https://github.com/timescale/pg-aiguide/blob/HEAD/skills/setup-timescaledb-hypertables/SKILL.md