TimescaleDB

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.

TimescaleDB is an open-source, high-performance time-series database optimized for time-based workloads, built as a PostgreSQL extension

Hypertables: Automatically partitions data into smaller, manageable chunks based on time, boosting ingest speeds

Hyperfunctions: Provides over 100 specialized SQL functions for time-series analysis.

Row-columnar storage: providing the flexibility of a row store for transactions and the performance of a column store for analytics

The following figure shows how TimescaleDB optimizes your data for superfast real-time analytics:

Drizzle Studio

Device Access Events

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

CREATE TABLE device_access_events (
    time TIMESTAMPTZ NOT NULL,

    organization_id UUID NOT NULL,
    device_id UUID NOT NULL,
    user_id UUID NOT NULL,
    policy_id UUID NOT NULL,

    hostname TEXT,
    remote_address TEXT,
    application TEXT NOT NULL,

    direction direction NOT NULL,
    action action NOT NULL,

    tm_enabled boolean DEFAULT true NOT NULL,
    metadata JSONB
) WITH (
    tsdb.hypertable,
    tsdb.partition_column='time',
    tsdb.segmentby='device_id',
    tsdb.orderby='time DESC',
    tsdb.sparse_index='minmax(action),minmax(direction),minmax(remote_address)'
);

CREATE INDEX ON device_access_events (organization_id, time DESC);
CREATE INDEX ON device_access_events (device_id, time DESC);

SELECT add_retention_policy(
  'device_access_events',
  drop_after => INTERVAL '30 days',
  if_not_exists => true
);

Device Access Events

On this page