TimescaleDB, in depth: hypertables and chunks, chunk exclusion, sizing the chunk interval, the columnstore, continuous aggregate refresh and the job scheduler

What the extension adds to Postgres

A hypertable looks like one table to your queries. Underneath, it is a parent table with no rows of its own and many child tables called chunks, each covering one interval of the time column (and optionally a range of a second, space dimension). TimescaleDB keeps its metadata in the _timescaledb_catalog schema and puts chunks in _timescaledb_internal.

Three hooks do the work. On insert, a custom executor node routes each row to the chunk covering its timestamp, creating the chunk if it does not exist. On read, the planner and a custom scan node called ChunkAppend skip chunks that cannot match the time predicate. In the background, a scheduler runs jobs: columnstore conversion, continuous aggregate refresh and retention.

Because each chunk is an ordinary Postgres table, each has its own indexes, its own visibility map and its own autovacuum history. That matters in two ways. Dropping old data is DROP TABLE on a chunk, which is instant and leaves no dead tuples behind, unlike a DELETE that vacuum then has to clean up. And an index on recent data stays small, because it only covers one chunk.

One licensing note: hypertables themselves are Apache 2 licensed, but features including the columnstore and continuous aggregates ship under the Timescale License. Some managed Postgres services ship only the Apache build. Check SHOW timescaledb.license; before you design around a feature.

Creating a hypertable

On 2.20 and later you declare it in CREATE TABLE. Name the partition column explicitly rather than relying on detection, and set segmentby and orderby now, because they shape the columnstore later:

CREATE TABLE readings (
    time        TIMESTAMPTZ      NOT NULL,
    device_id   INTEGER          NOT NULL,
    temperature DOUBLE PRECISION,
    battery     DOUBLE PRECISION,
    status      SMALLINT
) WITH (
    tsdb.hypertable,
    tsdb.partition_column = 'time',
    tsdb.chunk_interval   = '6 hours',
    tsdb.segmentby        = 'device_id',
    tsdb.orderby          = 'time DESC'
);

CREATE INDEX ON readings (device_id, time DESC);

On that form the columnstore is enabled by default and a columnstore policy is created for you; inspect it in timescaledb_information.jobs and adjust it rather than adding a second one. On older versions, create a plain table and convert it:

SELECT create_hypertable('readings', by_range('time', INTERVAL '6 hours'));

Unique constraints and primary keys must include the partition column, because uniqueness is enforced per chunk. A key of (device_id, time) works; a surrogate id BIGSERIAL PRIMARY KEY does not. If you need idempotent ingest, put the natural key in a unique index and use INSERT ... ON CONFLICT DO NOTHING.

Sizing the chunk interval: a worked example

The default interval is 7 days, which suits low-volume tables and is wrong for busy ones. The goal is that the chunks currently receiving writes, plus their indexes, stay in memory. When they do not, every insert touches index pages that have to be read from disk, and ingest rate collapses as the chunk grows.

Take a fleet of 20,000 devices reporting every 10 seconds: 2,000 rows per second, 172.8 million rows a day. A row here is a heap tuple of roughly 60 to 70 bytes once you count the tuple header and alignment, so the heap grows by about 10 to 12 GB a day. Two B-tree indexes, the default one on time and (device_id, time), add perhaps another 8 to 10 GB. Call it 20 GB a day before compression. These are estimates; measure your own with hypertable_detailed_size('readings') after a day of load.

Too small has its own cost: every chunk is a table the planner may consider, a set of files, and lock entries when a query touches it. Pick the largest interval whose active chunk fits comfortably in memory, then check that typical dashboard queries span tens of chunks, not thousands. Change it later with SELECT set_chunk_time_interval('readings', INTERVAL '3 hours');; it only affects chunks created after the call, so existing chunks keep their size.

How chunk exclusion works, and how queries defeat it

A query with WHERE time > '2026-10-01' has a constant bound, so the planner removes non-matching chunks before execution. A query with WHERE time > now() - INTERVAL '1 hour' cannot be fully resolved at plan time because now() is not a constant, so ChunkAppend excludes chunks at executor startup instead. Both show up in EXPLAIN ANALYZE: the plan lists only surviving chunks, and startup exclusion reports a line such as Chunks excluded during startup: 118.

Exclusion needs a predicate on the bare partition column. These patterns turn it off:

• Wrapping the column: WHERE date_trunc('day', time) = '2026-10-02' scans every chunk. Write the range instead: time >= '2026-10-02' AND time < '2026-10-03'.
• Putting the bound in a join or subquery the planner cannot push down, such as time > (SELECT max(time) FROM other_table) in some plan shapes. Check with EXPLAIN.
• No time predicate at all, as in SELECT * FROM readings WHERE device_id = 42 ORDER BY time DESC LIMIT 1. TimescaleDB can often stop early here because ChunkAppend walks chunks in time order, but a device that stopped reporting a month ago forces a walk through every chunk. Keep a separate last-value table for that query.

Use time_bucket() freely in SELECT and GROUP BY; only the predicate must stay simple.

The columnstore: converting chunks that stopped changing

Once a chunk is old enough that writes have mostly stopped, the columnstore policy rewrites it. Rows are grouped by the segmentby columns, sorted by orderby, and packed into batches of up to 1,000 rows. Each column of a batch is stored as one compressed array, using an algorithm chosen by type: delta-of-delta encoding for timestamps and integers, Gorilla-style XOR encoding for floats, dictionary encoding for low-cardinality values, and a general-purpose compressor otherwise. The same ideas are covered generically in Columnar Database Architecture.

Each batch also stores min and max values for the orderby columns. A query for one device over one hour finds the device's batches through the segmentby value, skips batches whose time range does not overlap, and decompresses only the columns it selects.

A useful check: rows per chunk divided by distinct segmentby values per chunk should be in the hundreds or more. At 6-hour chunks the example fleet has 2,160 rows per device per chunk, so device_id fills two full batches and part of a third per device. Set the policy delay to cover your late-arriving data. On 2.20+ the table already has a default policy, so replace it rather than adding a second:

CALL remove_columnstore_policy('readings', if_exists => true);
CALL add_columnstore_policy('readings', after => INTERVAL '1 day');
SELECT add_retention_policy('readings', drop_after => INTERVAL '30 days');

-- Inspect results per hypertable and per chunk
SELECT * FROM hypertable_columnstore_stats('readings');
SELECT chunk_name, before_compression_total_bytes, after_compression_total_bytes
FROM chunk_columnstore_stats('readings') ORDER BY chunk_name DESC LIMIT 10;

-- Manual conversion, for one chunk, during a backfill
CALL convert_to_columnstore('_timescaledb_internal._hyper_1_42_chunk');

On releases before 2.18 the equivalents are ALTER TABLE ... SET (timescaledb.compress, timescaledb.compress_segmentby = 'device_id') and add_compression_policy. Current releases support INSERT, UPDATE, DELETE and upsert directly on columnstore chunks, but they are not free: an update touching a compressed batch has to decompress the affected rows, and a steady stream of late inserts leaves unordered data that a compaction policy or reconversion later tidies up. If late data is normal for you, move the policy delay out rather than fighting it.

Continuous aggregates: refresh windows and invalidation

A continuous aggregate is a materialized view over a hypertable, grouped by time_bucket, whose results live in their own internal hypertable. Unlike a plain Postgres materialized view, it refreshes incrementally: only buckets whose source rows changed are recomputed.

CREATE MATERIALIZED VIEW readings_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket(INTERVAL '1 hour', time) AS bucket,
       device_id,
       avg(temperature) AS avg_temp,
       min(battery)     AS min_battery,
       count(*)         AS samples
FROM readings
GROUP BY bucket, device_id
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');

Each run refreshes the window from now() - start_offset to now() - end_offset. The end offset keeps the still-filling current bucket out of the materialization. The start offset bounds the work. When rows change in a time range that has already been materialized, TimescaleDB records that range in an invalidation log, and the next refresh recomputes only the affected buckets inside its window.

Three consequences catch people out:

1. Data newer than the end offset is missing by default. Since 2.13, real-time aggregation is off, so a query on readings_hourly returns only materialized buckets. Turn it on with ALTER MATERIALIZED VIEW readings_hourly SET (timescaledb.materialized_only = false); and the view unions in raw data past the watermark, at the cost of scanning those raw rows on every query.
2. Changes older than the start offset are not picked up by the policy. A backfill of last month stays invalidated until you run CALL refresh_continuous_aggregate('readings_hourly', '2026-09-01', '2026-10-01'); yourself.
3. Retention must sit behind the refresh window. If retention drops raw chunks that are still inside the refresh window, the next refresh sees no rows and the aggregate loses those buckets. Keep the retention interval longer than the start offset, and keep the aggregate longer than the raw data if the rollup is what you want to retain.

The job scheduler and how to watch it

Policies are rows in a jobs table executed by TimescaleDB background workers. Workers come out of Postgres's max_worker_processes pool, shared with parallel query and logical replication, and are capped by timescaledb.max_background_workers. If you add hypertables and aggregates without raising these, jobs queue silently and conversion or refresh falls behind. Give each database running TimescaleDB enough workers for its jobs plus one for the scheduler itself.

-- What is scheduled
SELECT job_id, proc_name, hypertable_name, schedule_interval, config
FROM timescaledb_information.jobs ORDER BY job_id;

-- Is it keeping up
SELECT job_id, last_run_status, last_successful_finish, next_start, total_failures
FROM timescaledb_information.job_stats ORDER BY next_start;

-- Why did it fail
SELECT job_id, start_time, sqlerrcode, err_message
FROM timescaledb_information.job_errors ORDER BY start_time DESC LIMIT 20;

-- Chunk layout
SELECT chunk_name, range_start, range_end, is_compressed
FROM timescaledb_information.chunks
WHERE hypertable_name = 'readings' ORDER BY range_start DESC LIMIT 12;

Alert on three things: any job whose last_successful_finish is older than twice its schedule, the count of rowstore chunks older than the columnstore delay (conversion is falling behind), and the age of the newest materialized bucket in each continuous aggregate. Change a job with SELECT alter_job(job_id, schedule_interval => ...) rather than editing catalog tables.

Failure modes in production

• Default 7-day chunks on a busy table. Ingest is fast on day one and slow by day five as the active chunk's indexes outgrow memory. Fix by lowering the interval; it applies to new chunks only.
• A high-cardinality segmentby. Compression ratios near 1 and queries that get slower after conversion. Check rows per segment before choosing.
• Upgrades. The extension version in the cluster and the shared library must match after a package upgrade; run ALTER EXTENSION timescaledb UPDATE; as the first command in a fresh session on every database that uses it, and test the upgrade on a restored backup first.

When plain Postgres partitioning is enough

You can get time partitions, cheap retention and partition pruning from native declarative partitioning plus a scheduler such as pg_partman. That is a sound choice when the data is modest, compression is not needed and you already run a managed service without the Timescale License build. TimescaleDB earns its place when you need automatic chunk creation without pre-provisioning, columnar compression of history, incrementally refreshed rollups, or query patterns that rely on runtime chunk exclusion and ordered chunk scans.

What to do next

1. Check the extension version and timescaledb.license on every environment, including managed ones.
2. Measure one day of real ingest and compute bytes per day for heap plus indexes with hypertable_detailed_size.
3. Choose a chunk interval whose active chunk fits comfortably in memory and set it before data accumulates.
4. Pick segmentby from the column your queries filter on and confirm hundreds of rows per segment per chunk.
5. Measure how late your data arrives, then set the columnstore delay beyond it.
6. Create continuous aggregates for your dashboard queries, decide on real-time aggregation, and order retention behind the refresh window.
7. Run EXPLAIN ANALYZE on your top ten queries and confirm chunk exclusion in each one.
8. Alert on job failures, conversion backlog and aggregate staleness from the timescaledb_information views.