TimescaleDB
TimescaleDB — a Postgres extension for time-series: automatic partitioning by time (hypertables), continuous aggregates, retention/compression policies. ide99 provides UI on top of TimescaleDB commands.
When useful
When data has a time axis and accumulates:
- IoT metrics
- Event logs
- Market data
- Monitoring
TimescaleDB gives two main things:
- Hypertable — auto-partitioning by time without manual config
- Continuous aggregates — materialized views that auto-update as new data arrives
Activate
CREATE EXTENSION timescaledb;
On Postgres ≥ 17, the TimescaleDB version must be compatible. Check on docs.timescale.com.
In ide99, time-series candidates in Schema Browser get a Timescale panel.
Creating a hypertable
- Create a regular table:
CREATE TABLE metrics (
ts TIMESTAMPTZ NOT NULL,
device_id TEXT NOT NULL,
temperature DOUBLE PRECISION,
humidity DOUBLE PRECISION
);
- Right-click
metrics → Convert to hypertable.
- Form:
- Time column — which column is time (ide99 suggests TIMESTAMPTZ candidates)
- Chunk interval — period per chunk (default
1 day, for sparse writes — 1 week)
- Space partitioning (optional) — extra partitioning by
device_id or similar
- ide99 shows:
SELECT create_hypertable('metrics', 'ts', chunk_time_interval => INTERVAL '1 day');
After — the table in Schema Browser gets a "hypertable" icon, and chunks become visible in the tree.
Continuous aggregates
"I want to see hour-by-hour average temperature per device — but not recompute from scratch each time."
Solution — continuous aggregate:
- Right-click the hypertable → New continuous aggregate.
- Form:
- Name — view name (
metrics_hourly)
- Time bucket —
1 hour, 1 day, custom interval
- Aggregation — pick columns and functions (avg, sum, min, max, count, percentile_disc(0.95))
- Group by — extra keys (device_id, region)
- ide99 assembles the SQL:
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 hour', ts) AS bucket,
device_id,
avg(temperature) AS avg_temp,
percentile_disc(0.95) WITHIN GROUP (ORDER BY temperature) AS p95_temp
FROM metrics
GROUP BY bucket, device_id;
- Then you set a refresh policy:
SELECT add_continuous_aggregate_policy(
'metrics_hourly',
start_offset => INTERVAL '1 month',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '15 minutes'
);
ide99 shows a wizard: which range to refresh, how often.
After — query metrics_hourly like a regular view but get a fast answer.
Compression
Old chunks can be compressed on disk (5–20× less space, but slower on query).
Right-click the hypertable → Compression policy:
- Compress after: data older than N days/weeks gets compressed
- Segment by: which columns to group by inside the chunk for better compression (usually
device_id)
- Order by: inside the segment, sort by (usually
ts DESC)
ide99 generates:
ALTER TABLE metrics SET (
timescaledb.compress,
timescaledb.compress_segmentby = 'device_id',
timescaledb.compress_orderby = 'ts DESC'
);
SELECT add_compression_policy('metrics', INTERVAL '7 days');
Retention
"I want to keep only the last 90 days."
Right-click → Retention policy:
- Drop after: drop chunks older than N intervals
- Schedule: how often to run
ide99 generates:
SELECT add_retention_policy('metrics', INTERVAL '90 days');
After — old chunks drop automatically.
Viewing chunks
In the tree, the hypertable shows all chunks:
metrics (hypertable, 91 chunks, compression: 23 compressed)
├─ _hyper_1_1_chunk (2026-01-01 — 02, 4.2 MB)
├─ _hyper_1_2_chunk (2026-01-02 — 03, 4.5 MB)
└─ ...
Icons show: ✓ compressed, ↻ active, ✕ to be dropped (matches retention policy).
Next