TimescaleDB Time-Series skill for Claude Code

A time-series table is done when chunks are sized so recent data fits in memory, old chunks are compressed into the columnstore with a segmentby that matches the main filter, dashboards read continuous aggregates rather than raw rows, and retention drops whole chunks instead of running DELETE.

Version notes

TimescaleDB is developed by Tiger Data (formerly Timescale). API names changed over 2.x:

• 2.13+: create_hypertable('t', by_range('time')) generalized form; old positional form still works.
• 2.18+: “compression” was renamed “columnstore” (hypercore): add_columnstore_policy, timescaledb.enable_columnstore. add_compression_policy and compress_segmentby are deprecated but still accepted.
• Recent 2.x releases accept CREATE TABLE ... WITH (tsdb.hypertable, ...), which also sets up a default columnstore policy.

Run SELECT extversion FROM pg_extension WHERE extname = 'timescaledb'; and use the API that matches. On managed Postgres without TimescaleDB (RDS, Cloud SQL), use the plain-Postgres section.

Workflow

- [ ] 1. Profile the data: ingest rate, row width, series cardinality, query shapes, retention
- [ ] 2. Design the table: narrow vs wide, types, keys
- [ ] 3. Create the hypertable with a chosen chunk interval
- [ ] 4. Configure columnstore (segmentby/orderby) and its policy
- [ ] 5. Build continuous aggregates for dashboard resolutions
- [ ] 6. Add retention policies (raw short, aggregates long)
- [ ] 7. Verify chunk sizes, compression ratio, and query plans

2. Table design

CREATE TABLE metrics (
  time       timestamptz      NOT NULL,
  device_id  integer          NOT NULL,
  metric     smallint         NOT NULL,     -- FK to a small lookup table, not text per row
  value      double precision NOT NULL
);

• Always timestamptz, never timestamp without time zone.
• Narrow (one row per measurement) suits many metric types per device; wide (one column per metric) suits a fixed schema read together. Do not create thousands of sparse columns.
• Unique constraints and primary keys on a hypertable must include the time column. UNIQUE (device_id, metric, time) doubles as the idempotent-upsert target.
• Keep repeated strings out of the fact table: device names and tags belong in a dimension table joined at query time.

3. Hypertable and chunk interval

CREATE EXTENSION IF NOT EXISTS timescaledb;
SELECT create_hypertable('metrics', by_range('time', INTERVAL '1 day'));
CREATE INDEX ON metrics (device_id, time DESC);

Or, on releases that support it:

CREATE TABLE metrics (...) WITH (
  tsdb.hypertable, tsdb.partition_column = 'time',
  tsdb.segmentby = 'device_id', tsdb.orderby = 'time DESC'
);

Chunk interval rule: the most recent chunk(s) plus their indexes should fit in roughly 25% of memory. Estimate rows/day x bytes/row x 1.5 (indexes).

Ingest	Typical interval
under 1 GB/day	7 days
1-25 GB/day	1 day
above 25 GB/day	a few hours

Change for future chunks with SELECT set_chunk_time_interval('metrics', INTERVAL '6 hours');. Thousands of tiny chunks slow planning; a handful of huge chunks defeat chunk exclusion.

4. Columnstore (compression)

ALTER TABLE metrics SET (
  timescaledb.enable_columnstore,
  timescaledb.segmentby = 'device_id',
  timescaledb.orderby   = 'time DESC'
);
CALL add_columnstore_policy('metrics', after => INTERVAL '7 days');
-- pre-2.18: ALTER TABLE ... SET (timescaledb.compress, timescaledb.compress_segmentby = 'device_id');
--           SELECT add_compression_policy('metrics', INTERVAL '7 days');

• segmentby: the column most queries filter by equality, with enough rows per value per chunk (aim for hundreds to thousands). Too-high cardinality (a unique id) ruins the ratio.
• orderby: usually time DESC, optionally after a secondary column queries range over.
• Compress after the window in which late data and updates are common. Updates and deletes on compressed chunks work in recent versions but cost more.
• Expect 5-20x size reduction on typical metrics; measure, do not promise.

5. Continuous aggregates

CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket(INTERVAL '1 hour', time) AS bucket, device_id, metric,
       avg(value) AS avg_value, min(value) AS min_value, max(value) AS max_value, count(*) AS n
FROM metrics
GROUP BY bucket, device_id, metric
WITH NO DATA;

SELECT add_continuous_aggregate_policy('metrics_hourly',
  start_offset      => INTERVAL '3 days',
  end_offset        => INTERVAL '1 hour',
  schedule_interval => INTERVAL '30 minutes');

CALL refresh_continuous_aggregate('metrics_hourly', '2026-01-01', '2026-10-01');  -- backfill once

• start_offset must cover how late data can arrive; rows older than it are not re-aggregated by the policy. end_offset keeps the still-changing bucket out.
• Real-time aggregation (union with raw recent data) is off by default since 2.13 (materialized_only = true). Enable with ALTER MATERIALIZED VIEW metrics_hourly SET (timescaledb.materialized_only = false); when dashboards need the last hour.
• Build daily on top of hourly (hierarchical aggregates, 2.9+) instead of re-reading raw data.
• Store aggregatable components (sum and count, not only avg) so higher rollups stay correct; an average of averages is wrong when bucket counts differ.

6. Retention

SELECT add_retention_policy('metrics', drop_after => INTERVAL '90 days');
SELECT add_retention_policy('metrics_hourly', drop_after => INTERVAL '2 years');

Retention drops whole chunks (instant, no bloat). Never DELETE FROM metrics WHERE time < ... on large tables. Make the raw retention longer than the aggregate’s start_offset, or the refresh will re-aggregate an empty window and erase history. Confirm retention windows with the user before adding a policy - dropped chunks are gone unless backed up.

Query patterns

-- Gap-filled hourly series for a chart
SELECT time_bucket_gapfill(INTERVAL '1 hour', time) AS bucket,
       locf(avg(value)) AS value
FROM metrics
WHERE device_id = $1 AND time >= now() - INTERVAL '24 hours' AND time < now()
GROUP BY bucket ORDER BY bucket;

-- Latest value per device
SELECT DISTINCT ON (device_id) device_id, time, value
FROM metrics WHERE time > now() - INTERVAL '1 day'
ORDER BY device_id, time DESC;

• Always bound queries by time so chunk exclusion works; now() - interval is excluded at execution time, which is fine. Wrapping time in a function prevents it.
• first(value, time) / last(value, time) are TimescaleDB aggregates for open/close values.

Ingest

• Batch inserts (1k-10k rows per statement or COPY); single-row inserts cap out early.
• Use INSERT ... ON CONFLICT (device_id, metric, time) DO NOTHING for idempotent retries.
• Parallel writers help up to the core count; use timescaledb-parallel-copy for bulk loads.

Plain Postgres alternative

When the extension is unavailable or data is modest (under ~100M rows):

CREATE TABLE events (time timestamptz NOT NULL, device_id int NOT NULL, value float8)
PARTITION BY RANGE (time);
CREATE TABLE events_2026_10 PARTITION OF events FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE INDEX ON events USING brin (time);        -- tiny index for append-only time order

Create partitions ahead of time (pg_partman or a scheduled job), drop old partitions for retention, and use materialized views refreshed on a schedule in place of continuous aggregates.

Gotchas

• Querying a continuous aggregate with a bucket width it was not built for re-buckets correctly only for additive measures; build one aggregate per needed resolution.
• time_bucket buckets in UTC by default; pass a timezone argument for daily buckets in local time, or days split at the wrong hour.
• Large IN (...) lists of device ids against compressed chunks can be slower than a join to a VALUES list or a temp table; check the plan.
• Background jobs need workers: timescaledb.max_background_workers must cover policies across all databases. Check timescaledb_information.job_stats for failures.

Verification

SELECT hypertable_name, chunk_name, range_start, range_end, is_compressed
FROM timescaledb_information.chunks WHERE hypertable_name = 'metrics' ORDER BY range_start DESC LIMIT 10;
SELECT * FROM hypertable_compression_stats('metrics');     -- before/after bytes (columnstore_stats on newer versions)
SELECT job_id, proc_name, last_run_status, last_run_started_at, total_failures
FROM timescaledb_information.job_stats;
EXPLAIN (ANALYZE, BUFFERS) <dashboard query>;               -- few chunks scanned, reads the aggregate

Done when chunks match the planned interval, compression ratio is recorded, every job reports success, and dashboard queries hit the continuous aggregate with chunk exclusion.