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
Installs just this skill. Get the whole plugin for auto-invocation.
⚡ How it fires
How this skill gets triggered: by you, by Claude, or both.
Fires itselfClaude auto-loads it when your prompt matches the work.
You can call itInvoke it directly when you want it.
Slash command/setup-timescaledb-hypertables
👁️ Context preview
The summary Claude sees to decide when to auto-load this skill.
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
📊 Stats
Stars1,793
Forks99
LanguagePython
LicenseApache-2.0
📦 Ships with pg-aiguide
</> SKILL.md
setup-timescaledb-hypertables.SKILL.md
---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:
- 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)
- 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:**
```sql
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 decompression. Store metadata per batch (~1000 rows) to eliminate batches that don't match query predicates.
**Types:**
- **minmax:** Min/max values per batch - for range queries (>, <, BETWEEN) on numeric/temporal columns
- minmax for outlier detection (temperature > 90).
- minmax for fields that are highly correlated with segmentby and orderby columns (e.g. if orderby includes `created_at`, minmax on `updated_at` is useful).
**Avoid:** rarely filtered columns.
IMPORTANT: NEVER index columns in segmentby or orderby. Orderby columns will always have minmax indexes without any configuration.
**Configuration:**
The format is a comma-separated list of type_of_index(column_name).
**Good test:** recent chunk indexes should fit in less than 25% of RAM.
### Indexes & Primary Keys
Common index patterns - composite indexes on an id and timestamp:
```sql
CREATE INDEX idx_entity_timestamp ON your_table_name (entity_id, timestamp DESC);
```
**Important:** Only create indexes you'll actually use - each has maintenance overhead.
**Primary key and unique constraints rules:** Must include partition column.
**Option 1: Composite PK with partition column**
```sql
ALTER TABLE your_table_name ADD PRIMARY KEY (entity_id, timestamp);
```
**Option 2: Single-column PK (only if it's the partition column)**
```sql
CREATE TABLE ... (id BIGINT PRIMARY KEY, ...) WITH (tsdb.partition_column='id');
```
**Option 3: No PK**: strict uniqueness is often not required for insert-heavy patterns.
## Step 2: Compression Policy (Optional)
**IMPORTANT**: 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).
```sql
-- In TimescaleDB 2.23 and later only needed if you want to override the default 7-day policy created by tsdb.enable_columnstore=true
Use different aggregation intervals for different uses.
### Short-term (Minutes/Hours)
For up-to-the-minute dashboards on high-frequency data.
```sql
CREATE MATERIALIZED VIEW your_table_hourly
WITH (timescaledb.continuous) AS
SELECT
time_bucket(INTERVAL '1 hour', timestamp) AS bucket,
entity_id,
category,
COUNT(*) as record_count,
AVG(value_1) as avg_value_1,
MIN(value_1) as min_value_1,
MAX(value_1) as max_value_1,
SUM(value_2) as sum_value_2
FROM your_table_name
GROUP BY bucket, entity_id, category;
```
### Long-term (Days/Weeks/Months)
For long-term reporting and analytics.
```sql
CREATE MATERIALIZED VIEW your_table_daily
WITH (timescaledb.continuous) AS
SELECT
time_bucket(INTERVAL '1 day', timestamp) AS bucket,
entity_id,
category,
COUNT(*) as record_count,
AVG(value_1) as avg_value_1,
MIN(value_1) as min_value_1,
MAX(value_1) as max_value_1,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value_1) as median_value_1,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY value_1) as p95_value_1,
SUM(value_2) as sum_value_2
FROM your_table_name
GROUP BY bucket, entity_id, category;
```
## Step 5: Aggregate Refresh Policies
Set up refresh policies based on your data freshness requirements.
**start_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.
**end_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.
**schedule_interval:** Set to the same value as the end_offset but not more than 1 hour.
-- start_offset => INTERVAL '7 days', -- only refresh last 7 days (NULL = refresh all)
-- end_offset => INTERVAL '15 minutes',
-- schedule_interval => INTERVAL '15 minutes');
```
IMPORTANT: 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.
If the retention policy is commented out, comment out the start_offset as well. like this:
start_offset => NULL, -- Use NULL to refresh all data, or set to retention period if enabled on raw data
-- start_offset => INTERVAL '<retention period here>', -- uncomment if retention policy is enabled on the raw data table
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');
```
## Step 6: Real-Time Aggregation (Optional)
Real-time combines materialized + recent raw data at query time. Provides up-to-date results at the cost of higher query latency.
More 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.
Disabled by default in v2.13+, before that it was enabled by default.
**Use when:** Need data newer than end_offset, up-to-minute dashboards, can tolerate higher query latency
**Disable when:** Performance critical, refresh policies sufficient, high query volume, missing and stale data for recent data is acceptable
**Enable for current results (higher query cost):**
```sql
ALTER MATERIALIZED VIEW your_table_hourly SET (timescaledb.materialized_only = false);
```
**Disable for performance (but with stale results):**
```sql
ALTER MATERIALIZED VIEW your_table_hourly SET (timescaledb.materialized_only = true);
```
## Step 7: Compress Aggregates
Rule: segment_by = ALL GROUP BY columns except time_bucket, order_by = time_bucket DESC
```sql
-- Hourly
ALTER MATERIALIZED VIEW your_table_hourly SET (
timescaledb.enable_columnstore,
timescaledb.segmentby = 'entity_id, category',
timescaledb.orderby = 'bucket DESC'
);
CALL add_columnstore_policy('your_table_hourly', after => INTERVAL '3 days');
-- Daily
ALTER MATERIALIZED VIEW your_table_daily SET (
timescaledb.enable_columnstore,
timescaledb.segmentby = 'entity_id, category',
timescaledb.orderby = 'bucket DESC'
);
CALL add_columnstore_policy('your_table_daily', after => INTERVAL '7 days');
```
## Step 8: Aggregate Retention
Aggregates are typically kept longer than raw data.
IMPORTANT: Don't guess - ask user or you **MUST comment out if unknown**.
## Step 9: Performance Indexes on Continuous Aggregates
**Index strategy:** Analyze WHERE clauses in common queries → Create indexes matching filter columns + time ordering
**Pattern:** `(filter_column, bucket DESC)` supports `WHERE filter_column = X AND bucket >= Y ORDER BY bucket DESC`
Examples:
```sql
CREATE INDEX idx_hourly_entity_bucket ON your_table_hourly (entity_id, bucket DESC);
CREATE INDEX idx_hourly_category_bucket ON your_table_hourly (category, bucket DESC);
```
**Multi-column filters:** Create composite indexes for `WHERE entity_id = X AND category = Y`:
```sql
CREATE INDEX idx_hourly_entity_category_bucket ON your_table_hourly (entity_id, category, bucket DESC);
```
**Important:** Only create indexes you'll actually use - each has maintenance overhead.
## Step 10: Optional Enhancements
### Space Partitioning (NOT RECOMMENDED)
Only for query patterns where you ALWAYS filter by the space-partition column with expert knowledge and extensive benchmarking. STRONGLY prefer time-only partitioning.
## Step 11: Verify Configuration
```sql
-- Check hypertable
SELECT * FROM timescaledb_information.hypertables
WHERE hypertable_name = 'your_table_name';
-- Check compression settings
SELECT * FROM hypertable_compression_stats('your_table_name');
-- Check aggregates
SELECT * FROM timescaledb_information.continuous_aggregates;
-- Check policies
SELECT * FROM timescaledb_information.jobs ORDER BY job_id;
-- Monitor chunk information
SELECT
chunk_name,
range_start,
range_end,
is_compressed
FROM timescaledb_information.chunks
WHERE hypertable_name = 'your_table_name'
ORDER BY range_start DESC;
```
## Performance Guidelines
- **Chunk size:** Recent chunk indexes should fit in less than 25% of RAM
- **Compression:** Expect 90%+ reduction (10x) with proper columnstore config
- **Query optimization:** Use continuous aggregates for historical queries and dashboards
- **Memory:** Run `timescaledb-tune` for self-hosting (auto-configured on cloud)
## Schema Best Practices
### Do's and Don'ts
- ✅ Use `TIMESTAMPTZ` NOT `timestamp`
- ✅ Use `>=` and `<` NOT `BETWEEN` for timestamps
- ✅ Use `TEXT` with constraints NOT `char(n)`/`varchar(n)`
- ✅ Use `snake_case` NOT `CamelCase`
- ✅ Use `BIGINT GENERATED ALWAYS AS IDENTITY` NOT `SERIAL`
- ✅ Use `BIGINT` for IDs by default over `INTEGER` or `SMALLINT`
- ✅ Use `DOUBLE PRECISION` by default over `REAL`/`FLOAT`
- ✅ Use `NUMERIC` NOT `MONEY`
- ✅ Use `NOT EXISTS` NOT `NOT IN`
- ✅ Use `time_bucket()` or `date_trunc()` NOT `timestamp(0)` for truncation
