TimescaleDB

TimescaleDB is a PostgreSQL extension, not a separate database, and that has a concrete payoff in AddisDB: it gets the complete Postgres toolset. The Live Monitor works, test clones work, in-grid editing works, mock data works.

Who it is for

Timescale partitions large time-series tables into hypertables automatically, compresses older chunks, and keeps continuous aggregates up to date — while the table still behaves like an ordinary Postgres table with joins, constraints and full SQL.

A hypertable is a set of chunks partitioned by time, which is what makes retention cheap: dropping a month of data drops chunks rather than deleting rows, so there is no vacuum bill afterwards. Compression converts older chunks into a columnar layout, typically an order of magnitude smaller, at the cost of making them read-mostly.

It suits IoT and sensor data, application and infrastructure metrics, financial ticks, and any workload where you want time-series performance without giving up relational data next to it.

Set up the extension

1. Timescale Cloud: create a service and copy the connection string. The extension is already enabled.
2. Self-hosted: install the timescaledb package for your Postgres version, add it to shared_preload_libraries, and restart. Or use the timescale/timescaledb Docker image.
3. Run CREATE EXTENSION IF NOT EXISTS timescaledb; in your database.
4. Convert a table to a hypertable with create_hypertable() — this is the step that makes it a time-series table.
5. Add a compression policy and a retention policy once you know how long the data is worth keeping.

CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE readings (
  time  TIMESTAMPTZ NOT NULL,
  sensor_id INT,
  value DOUBLE PRECISION
);

SELECT create_hypertable('readings', 'time');

Connect from AddisDB

1. New Connection → TimescaleDB under Timeseries. Port prefills to 5432 — it is a Postgres server.
2. Paste the connection string into the Connection URL box and click Fill fields, or enter the fields by hand.
3. Set SSL mode to require for Timescale Cloud.
4. Test, then Save.

The three functions that do the work

time_bucket is the one you will type most: it rounds timestamps into fixed intervals so a raw stream becomes a chart. Continuous aggregates keep the same rollup materialized and incrementally refreshed, which is what makes a dashboard over a year of data instant instead of merely possible.

-- Downsample on the fly
SELECT time_bucket('5 minutes', time) AS bucket,
       sensor_id, avg(value) AS avg_value
FROM   readings
WHERE  time > now() - INTERVAL '6 hours'
GROUP  BY bucket, sensor_id
ORDER  BY bucket;

-- Or keep it materialized
CREATE MATERIALIZED VIEW readings_5m
WITH (timescaledb.continuous) AS
  SELECT time_bucket('5 minutes', time) AS bucket,
         sensor_id, avg(value) AS avg_value
  FROM readings GROUP BY bucket, sensor_id;

Compression and retention are policies rather than jobs you run: add_compression_policy and add_retention_policy schedule themselves, and hypertable_compression_stats tells you what they have saved.

What AddisDB gives you

• The Chart view — a result with a time column renders as a time-series chart immediately.
• Live Monitor with real active queries, blocking, locks and connection stats, plus the always-on flight recorder for replaying an incident.
• Test clones — dry-run a retention or compression policy change against a disposable copy of the database.
• The full schema diagram, in-grid editing, mock data, migrations and diff.
• AI querying grounded in your real hypertables.
• ⌘K search and the full safety model.