setup-timescaledb-hypertables_skill

Overview

This skill provides step-by-step guidance for designing TimescaleDB hypertables and related objects: partitioning, segment_by and order_by selection, chunk interval tuning, sparse indexes, compression, retention policies, continuous aggregates, and real-time aggregation. It focuses on insert-heavy, append-only patterns and gives concrete SQL patterns and configuration advice to maximize storage efficiency and query performance.

How this skill works

The skill inspects typical time-series and append-only schemas and walks through creating hypertables with appropriate partition (time) and segment_by columns, setting order_by for natural progression, and configuring sparse indexes for compressed data. It then covers chunk time interval sizing, compression policies, retention rules, continuous aggregate definitions and refresh policies, real-time aggregation toggles, and compressing/retaining aggregates.

When to use it

• Designing new time-series or event-log tables with heavy inserts and rare updates
• Optimizing schemas for metrics, IoT telemetry, transaction logs, or audit trails
• Setting up compression and sparse indexes to reduce storage and speed queries on compressed data
• Creating continuous aggregates for dashboarding and long-term reporting
• Tuning chunk interval and retention for predictable memory and storage behavior

Best practices

• Always use a time-based partition column (timestamp/timestamptz/date) or a sequential integer for partitioning
• Prefer a single segment_by column with >100 rows per value per chunk; if density is low, prepend that column to order_by instead
• Use order_by = 'timestamp DESC' (or sequence DESC) to create natural progression for compression
• Enable columnstore compression by default for insert-heavy workloads unless using incompatible types (e.g., pgvector)
• Only create indexes and primary keys you will use; include the partition column in any PK/unique constraint
• Match continuous aggregate start_offset to raw-table retention or comment out both if retention is unknown

Example use cases

• IoT telemetry: device_id as segment_by, timestamp DESC order_by, minmax sparse indexes on temperature and pressure
• High-frequency metrics: time_bucket hourly continuous aggregate for dashboards and daily aggregate for reports
• Event logs: created_at partition, session_id in order_by when user_id density is low, retention policy for raw logs
• Financial ticks: symbol as segment_by, timestamp DESC, small chunk interval (minutes to hours) for high-volume symbols
• Append-only analytics: compress aggregates and set longer retention for aggregates than raw data