TimescaleDB

What TimescaleDB does

TimescaleDB's core abstraction is the hypertable. A hypertable looks and behaves like a normal PostgreSQL table — you insert, query, join, and index it with standard SQL — but internally it is partitioned into chunks, each covering a time range. TimescaleDB creates new chunks automatically as data arrives and routes queries to only the relevant chunks, which keeps scan times predictable even as the table grows to billions of rows.

On top of this partitioning layer, TimescaleDB provides three features that address the most common time-series pain points:

• Continuous aggregates — materialized views that refresh incrementally, recomputing only the time buckets where new data has arrived rather than re-scanning the entire table
• Columnar compression — transparent compression of older chunks using column-oriented storage with type-specific algorithms, typically achieving 90%+ compression on time-series data
• Data retention policies — automated dropping of chunks older than a configured threshold, keeping storage bounded without manual maintenance

TimescaleDB is developed by Timescale, Inc. (now Tiger Data). The extension is available in two editions: an Apache 2.0 licensed open-source core, and a source-available community edition under the Timescale License (TSL) that includes continuous aggregates, compression, and retention policies. The TSL is free to use but restricts offering the software as a hosted database-as-a-service.

When to use TimescaleDB

TimescaleDB is purpose-built for workloads where data is indexed primarily by time and arrives in append-heavy patterns. Specific scenarios:

• IoT telemetry — sensor readings, device heartbeats, environmental monitoring where thousands of devices report metrics every few seconds
• Application metrics and observability — request latencies, error rates, queue depths, and other operational metrics collected at high frequency
• Financial tick data — trades, quotes, and order book snapshots where time-ordered storage and fast windowed aggregations are essential
• Log analytics — structured log events that need time-range queries, rollup aggregations, and automatic expiration
• Event tracking — user activity streams, clickstreams, and audit trails that grow without bound and need lifecycle management

If your data does not have a natural time dimension, or if your workload is primarily random-access reads and updates rather than append-heavy writes, standard PostgreSQL (possibly with native partitioning via pg_partman) is a better fit. I should note — with some candour — that I have seen teams reach for TimescaleDB when pg_partman and a well-maintained materialized view would have served perfectly well. The presence of a timestamp column does not, by itself, constitute a time-series workload.

Installation and setup

TimescaleDB must be loaded as a shared library at server startup. This means adding it to shared_preload_libraries in postgresql.conf and restarting PostgreSQL. The extension is not a contrib module — it must be installed separately from your package manager or built from source.

-- 1. Add to postgresql.conf (requires restart)
shared_preload_libraries = 'timescaledb'

-- 2. Restart PostgreSQL, then create the extension
CREATE EXTENSION timescaledb;

-- 3. Verify installation
SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';

After the restart, the extension is ready. No additional configuration is required for basic usage, though you may want to tune timescaledb.max_background_workers if you plan to use compression and retention policies (the default of 8 is sufficient for most setups). The restart requirement is the one genuine inconvenience — a shared preload library is not something you trial on a Friday afternoon.

Creating hypertables

A hypertable starts as a regular PostgreSQL table. You create the table with standard DDL, then convert it with create_hypertable(). The by_range() dimension builder specifies which column to partition on.

-- Create a regular table
CREATE TABLE metrics (
  time        TIMESTAMPTZ NOT NULL,
  device_id   TEXT        NOT NULL,
  temperature DOUBLE PRECISION,
  cpu_usage   DOUBLE PRECISION
);

-- Convert it to a hypertable partitioned by time
SELECT create_hypertable('metrics', by_range('time'));

After conversion, the table behaves identically from the application's perspective. Inserts, selects, joins, indexes, and foreign keys all work as expected. The partitioning into time-based chunks is entirely transparent. This is, if you'll permit me, the single best design decision in the extension — it did not invent a new query language when SQL was sitting right there.

Querying and time_bucket()

TimescaleDB adds the time_bucket() function for grouping timestamps into uniform intervals — the time-series equivalent of date_trunc() but with arbitrary bucket sizes.

-- Insert data exactly like a normal table
INSERT INTO metrics (time, device_id, temperature, cpu_usage)
VALUES
  (now(), 'sensor-01', 22.5, 0.73),
  (now(), 'sensor-02', 19.8, 0.41);

-- Query with standard SQL — indexes, joins, subqueries all work
SELECT
  time_bucket('1 hour', time) AS hour,
  device_id,
  avg(temperature)            AS avg_temp,
  max(cpu_usage)              AS peak_cpu
FROM metrics
WHERE time > now() - INTERVAL '24 hours'
GROUP BY hour, device_id
ORDER BY hour DESC;

time_bucket() supports any interval: '5 minutes', '1 hour', '1 day', '1 week'. It is the building block for both ad-hoc analytics and continuous aggregates.

Continuous aggregates

Continuous aggregates are materialized views that update incrementally. When new data arrives in the underlying hypertable, only the affected time buckets are recomputed on the next refresh — not the entire view. This makes them practical for tables with millions of rows receiving constant inserts.

-- Create a continuous aggregate that rolls up hourly averages
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS hour,
  device_id,
  avg(temperature)            AS avg_temp,
  avg(cpu_usage)              AS avg_cpu,
  count(*)                    AS samples
FROM metrics
GROUP BY hour, device_id
WITH NO DATA;

-- Add a policy to refresh the aggregate automatically
SELECT add_continuous_aggregate_policy('metrics_hourly',
  start_offset  => INTERVAL '3 hours',
  end_offset    => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour'
);

The refresh policy runs on a background worker. The start_offset and end_offset parameters define the refresh window — in this example, the policy refreshes data between 3 hours ago and 1 hour ago on each run, leaving a 1-hour buffer for late-arriving data.

Compression

TimescaleDB can compress older chunks using column-oriented storage. Compressed chunks are read-only but remain queryable with normal SQL — decompression happens transparently at query time. Compression ratios of 90-95% are typical for time-series data with repeated device identifiers and predictable numeric patterns.

-- Enable compression on the hypertable
ALTER TABLE metrics SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id',
  timescaledb.compress_orderby   = 'time DESC'
);

-- Add a policy to compress chunks older than 7 days
SELECT add_compression_policy('metrics', INTERVAL '7 days');

The compress_segmentby parameter defines which column to group rows by within each compressed chunk (typically the series identifier), and compress_orderby controls the physical sort order (typically time descending for range scans). Two statements, and your storage bill improves by an order of magnitude. Elegant arrangements need not be complicated.

Data retention

Retention policies automatically drop chunks that fall outside a configured time window. Because TimescaleDB stores data in chunks, dropping old data is a DROP TABLE on the chunk — instant, regardless of table size, and with no index bloat or vacuum overhead.

-- Automatically drop chunks older than 90 days
SELECT add_retention_policy('metrics', INTERVAL '90 days');

Retention policies pair naturally with continuous aggregates: you can keep raw data for 90 days but retain hourly aggregates for years, giving you long-term trend data without the storage cost of individual rows. A well-run household does not keep every receipt in the foyer — it files the summaries and discards the originals on a schedule.

Cloud availability

Where TimescaleDB is available through a managed provider, it is typically the Apache 2 edition — meaning continuous aggregates, compression, and retention policies may not be included. For the full feature set, Timescale's own managed cloud service (available on AWS, GCP, and Azure) is the most straightforward option.

How Gold Lapel relates

I shall be direct about the comparison, because the honest version serves you better than a diplomatic one. Gold Lapel's automatic materialized views and TimescaleDB's continuous aggregates share a family resemblance — both maintain pre-computed rollups that stay current with underlying data. But they are built for different households entirely. Continuous aggregates require a time_bucket() grouping on a hypertable. Gold Lapel's materialized views work on any query pattern — time-series, OLTP, analytical, or the peculiar hybrid that most real applications actually are.

For genuine time-series workloads — thousands of sensors reporting every few seconds, financial tick data, high-frequency application metrics — TimescaleDB is the right tool, and I would not suggest otherwise. Its chunk-based partitioning, compression, and retention policies are purpose-built for append-heavy, time-ordered data at scale. Gold Lapel does not replicate that functionality and would not attempt to.

Where things become interesting is in the household that has both. Your application likely runs time-series queries alongside general OLTP traffic against the same PostgreSQL instance. TimescaleDB attends to the former; Gold Lapel attends to the latter. The two coexist without friction — Gold Lapel's proxy layer is entirely transparent to TimescaleDB's background workers and chunk management. A well-staffed household, if I may say so, where each member of staff knows their duties.