TimescaleDB skill for Codex

Done well means: recent chunks fit in memory, older chunks are compressed with a segmentby that matches how data is filtered, dashboards read continuous aggregates instead of raw rows, retention drops whole chunks on a schedule, and every background job is visible and succeeding.

Orient first

1. Read AGENTS.md, the migrations folder and the ORM setup; TimescaleDB DDL usually lives in raw SQL migrations.
2. Check the version and license, because features differ:

SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';
SHOW timescaledb.license;      -- 'timescale' (community) or 'apache'

Compression, continuous aggregates and policies need the community (TSL) license. Apache-only builds, shipped by some managed Postgres providers, lack them. 3. Get the workload: ingest rate (rows/s), row width, how queries filter (by device, tenant, symbol), the time ranges dashboards request, and how long raw data must be kept.

The company behind TimescaleDB renamed itself Tiger Data in 2025 and the cloud is Tiger Cloud; docs moved to tigerdata.com. Older docs use “compression” where newer ones say “columnstore” or “hypercore”: same feature, newer API names (2.18+).

Create the hypertable

CREATE TABLE readings (
  time       timestamptz NOT NULL,
  device_id  bigint      NOT NULL,
  metric     text        NOT NULL,
  value      double precision,
  PRIMARY KEY (device_id, metric, time)       -- unique keys must include the time column
);
SELECT create_hypertable('readings', by_range('time', INTERVAL '1 day'));
CREATE INDEX ON readings (device_id, time DESC);

Recent releases also accept a single statement that creates the hypertable and configures the columnstore:

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

Check the installed version’s docs for what policy it adds automatically; some versions create a columnstore policy for you, others require add_columnstore_policy afterwards.

Chunk interval

Rule	Value
Target	Recent chunks, including their indexes, fit in about 25% of RAM together
Default	7 days, often too large for high ingest
Typical	1 day for millions of rows per day; 1 hour for very high ingest
Too small	Thousands of chunks slow planning; keep total chunks in the low thousands

SELECT set_chunk_time_interval('readings', INTERVAL '6 hours');  -- affects new chunks only
SELECT chunk_name, range_start, range_end,
       pg_size_pretty(total_bytes) FROM chunks_detailed_size('readings') d
JOIN timescaledb_information.chunks c USING (chunk_schema, chunk_name)
ORDER BY range_start DESC LIMIT 5;

Compression (columnstore)

-- 2.18+ names
ALTER TABLE readings SET (
  timescaledb.enable_columnstore,
  timescaledb.segmentby = 'device_id, metric',
  timescaledb.orderby   = 'time DESC'
);
CALL add_columnstore_policy('readings', after => INTERVAL '3 days');

-- equivalent on older versions
ALTER TABLE readings SET (timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id, metric',
  timescaledb.compress_orderby   = 'time DESC');
SELECT add_compression_policy('readings', INTERVAL '3 days');

Choosing settings:

• segmentby: the columns queries filter on with equality, with enough rows per value per chunk (aim for at least hundreds to thousands). Too high cardinality (a request id) produces tiny segments and poor compression.
• orderby: usually time DESC; add a second column when queries range-scan on it.
• after: older than the window that still receives writes and frequent updates. Late data into compressed chunks works but costs decompression work.

Measure the outcome:

SELECT pg_size_pretty(before_compression_total_bytes) AS before,
       pg_size_pretty(after_compression_total_bytes)  AS after
FROM hypertable_compression_stats('readings');

Expect 5-20x on typical metrics. Under 3x usually means a poor segmentby choice.

Continuous aggregates

CREATE MATERIALIZED VIEW readings_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 samples
FROM readings
GROUP BY bucket, device_id, metric
WITH NO DATA;

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

CALL refresh_continuous_aggregate('readings_hourly', now() - INTERVAL '90 days', now());

• start_offset must cover how late data arrives; rows older than it are never re-aggregated by the policy.
• end_offset leaves the still-filling bucket out of materialization.
• Since 2.13, real-time aggregation is off by default (materialized_only = true), so the newest bucket is missing until refresh. Turn it on if dashboards need it: ALTER MATERIALIZED VIEW readings_hourly SET (timescaledb.materialized_only = false);
• Store mergeable pieces (sum and count, not only avg) when you will roll up further. Build daily aggregates on top of hourly ones (hierarchical continuous aggregates).
• Retention on the raw table can be shorter than on the aggregate: keep raw 30 days, hourly 2 years.

Retention

SELECT add_retention_policy('readings', drop_after => INTERVAL '30 days');
SELECT add_retention_policy('readings_hourly', drop_after => INTERVAL '730 days');

Retention drops whole chunks: instant, no vacuum debt. Never implement it with DELETE. Before adding a policy on an existing table, show the user which chunks it will drop (SELECT show_chunks('readings', older_than => INTERVAL '30 days');) and get explicit confirmation; dropped chunks are gone. Make sure the aggregate policy has already materialized data that raw retention removes.

Query patterns

-- gap-filled series for a chart
SELECT time_bucket_gapfill('5 minutes', time) AS bucket,
       locf(avg(value)) AS value
FROM readings
WHERE device_id = $1 AND metric = 'temp'
  AND time >= now() - INTERVAL '6 hours' AND time < now()
GROUP BY bucket ORDER BY bucket;

-- latest value per device (index on (device_id, time DESC))
SELECT DISTINCT ON (device_id) device_id, time, value
FROM readings WHERE metric = 'temp' AND time > now() - INTERVAL '1 day'
ORDER BY device_id, time DESC;

• Always bound queries on time with constants or now() expressions so chunk exclusion applies; check EXPLAIN shows only the expected chunks.
• time_bucket_gapfill needs both a lower and upper time bound in WHERE.
• Route dashboard ranges by width: under a day from raw, up to a quarter from hourly, beyond from daily.

Jobs and monitoring

SELECT j.job_id, j.proc_name, j.hypertable_name, s.last_run_status,
       s.last_successful_finish, s.total_failures
FROM timescaledb_information.jobs j
JOIN timescaledb_information.job_stats s USING (job_id);

SELECT * FROM timescaledb_information.job_errors ORDER BY finish_time DESC LIMIT 20;

timescaledb.max_background_workers must cover all jobs across databases; starved jobs silently lag. timescaledb-tune suggests memory and worker settings on self-hosted installs.

Verification

• EXPLAIN of top dashboard queries touches only recent chunks or the aggregate
• Compression ratio measured; policy jobs show Success
• Continuous aggregate matches raw for a sample bucket: compare avg(value) from raw against avg_value for one device and hour
• Retention preview reviewed and confirmed before the policy is created
• Ingest benchmark at expected peak with indexes in place

Gotchas

• A unique index or primary key without the time column fails on a hypertable.
• create_hypertable on a populated table needs migrate_data => true and locks the table while copying; for large tables create a new hypertable and backfill ($data-backfills).
• Changing segmentby applies to chunks compressed afterwards; existing chunks keep old settings until decompressed and recompressed.
• timestamp without time zone makes day buckets ambiguous across DST and regions; store timestamptz and pass a timezone argument to time_bucket for local-day buckets.
• Updates and deletes on compressed chunks work in recent versions but are slow; keep the compression after window beyond normal correction periods.

Output template

**Table:** readings, 40M rows/day, 1-day chunks (recent chunk 3.1 GB incl. indexes, RAM 32 GB)
**Columnstore:** segmentby device_id, metric; orderby time DESC; after 3 days; ratio 11.4x
**Aggregates:** readings_hourly (refresh 30 min, start_offset 3 d), readings_daily on hourly
**Retention:** raw 30 d, hourly 2 y (confirmed by user before creation)
**Dashboards:** 6 h and 24 h panels on raw; 7-90 d on hourly; > 90 d on daily
**Jobs:** 5 policies, all Success, no errors in job_errors