TimescaleDB for Data Engineering: Hypertables, Continuous Aggregates & Compression

timescaledb is the pick-one architectural decision that decides whether your Postgres box graduates into a proper time-series database or stays a general-purpose OLTP store that quietly falls over at TB-scale metric ingestion — and it is the single Postgres extension senior data engineers reach for most often because "just add more shards" is not an answer when the write path is a firehose of one-second sensor rows. Every operational metric your business writes — a device reading, an application latency histogram, a market tick, an IoT gauge sample, a Kubernetes pod-level CPU count — has to be ingested at hundreds of thousands of rows per second, queried by WHERE ts BETWEEN … AND … predicates that touch a narrow slice of the last few days, aggregated into rolled-up buckets your dashboards can scan in milliseconds, and compressed down to a fraction of raw size so retention costs do not devour the storage budget. The engineering trade-off does not live in "should we do time-series" — every observability, IoT, and financial-tick stack needs it — but in which time series database you pick and what it costs your Postgres skill investment.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "walk me through timescaledb hypertable chunking and how the planner prunes on time," or "your dashboard hits a raw 30-day table and it takes 20 seconds — how do you fix it with a timescaledb continuous aggregate?", or "explain timescale compression and why segmentby matters more than any other tuning knob." It walks through the four pillars that make TimescaleDB a viable time-series database on top of Postgres — hypertables and chunking, continuous aggregates with real-time materialization, columnar compression plus retention, and the timescaledb_toolkit hyperfunctions (time_bucket, first/last, histogram, LTTB downsampling, gauge_agg / counter_agg, stats_agg) — the axes that decide when TimescaleDB wins against vanilla Postgres partitioning, InfluxDB, QuestDB, or ClickHouse, and the interview signals senior data engineers listen for. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, sharpen aggregation reflexes on the aggregation practice library →, and rehearse the window-function axis with the window-functions practice library →.

---

On this page

• Why TimescaleDB matters in 2026
• Hypertables + chunking
• Continuous aggregates + real-time materialization
• Compression + retention
• Hyperfunctions + when TimescaleDB wins
• Cheat sheet — TimescaleDB recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why TimescaleDB matters in 2026

One extension turns Postgres into a full time-series database — and the choice binds every dashboard downstream

The one-sentence invariant: timescaledb is a Postgres extension that installs create_hypertable, columnar compression per chunk, continuous aggregates with real-time materialization, and a timescaledb_toolkit hyperfunctions library on top of a stock Postgres cluster — turning it into a time-series database that competes with InfluxDB, QuestDB, and ClickHouse without asking your team to abandon SQL, JOINs, foreign keys, or the two decades of Postgres tooling they already know. The pattern you pick in month one becomes the pattern your dashboards, retention jobs, and observability pipeline all hard-code assumptions against, because every downstream consumer starts to depend on continuous-aggregate freshness, compression ratios, and hypertable chunk boundaries in ways that cannot be undone without rewriting the read path.

The four axes interviewers actually probe.

• Ingest rate. Vanilla Postgres tops out somewhere around 20–50 k rows/s per table before index maintenance and WAL write amplification become the bottleneck. TimescaleDB's hypertables push that ceiling into the hundreds of thousands per second on the same hardware because writes flow into the newest chunk (a small, hot, in-memory table) rather than into one giant B-tree. Interviewers open here because the ingest ceiling is what pushes teams off vanilla PG in the first place.
• Query pruning. A WHERE ts BETWEEN '2026-07-20' AND '2026-07-21' predicate on a naive Postgres table walks either the full table or a ts-index range. On a hypertable, the planner does chunk exclusion — it prunes entire chunks before opening a single one — turning a full-table scan into a two-chunk scan. This is the query win TimescaleDB was built for.
• Storage cost. Uncompressed time-series rows on Postgres cost roughly the same per row as any OLTP data — often 200–500 bytes per row after index overhead. Compressed TimescaleDB chunks routinely hit 10–100× reduction; a 500 GB uncompressed metric table can drop to 5–50 GB compressed, which is what makes multi-year retention affordable.
• SQL-native surface. Every other serious time-series database (InfluxDB Flux, QuestDB SQL, ClickHouse SQL) requires learning a dialect. TimescaleDB is Postgres — the same SELECT, JOIN, GROUP BY, foreign keys, JSONB columns, and thousands of Postgres-shaped tools (pgbouncer, PgBackrest, Patroni, DataGrip). This is the "keep your Postgres skills" answer, and it's the single biggest reason TimescaleDB wins in shops that already have a Postgres bench.

The 2026 reality — TimescaleDB is one of four viable time-series options.

• TimescaleDB. The Postgres-native answer. Hypertables, continuous aggregates, columnar compression, timescaledb_toolkit. Timescale Cloud (formerly Forge) is the managed offering with tiered storage into object stores. Free (Apache-licensed) core; some features (multi-node, bottomless storage) are behind Timescale Cloud or the Timescale License. Adopted by observability, IoT, financial-tick, and application-metrics teams that already know Postgres.
• InfluxDB. The purpose-built time-series database. Excellent write throughput, Flux query language (learning curve), rich retention-policy story. InfluxDB 3.0 is a rewrite on top of DataFusion + Parquet + object storage — a genuine competitor to TimescaleDB but with a smaller Postgres-shaped ecosystem.
• QuestDB. The Java-based columnar time-series database. SQL-first, very fast on single-node ingest, smaller ecosystem. Strong in high-frequency-trading contexts.
• ClickHouse. The columnar OLAP warehouse that happens to work well on time-series aggregates. Excellent at scan-heavy analytics; less optimised for one-row-per-second ingest with high-cardinality dimensions. Adopted by teams that need both time-series and general OLAP.

What interviewers listen for.

• Do you name the hypertable + chunking mechanism as the primitive TimescaleDB is built on, not just "it's Postgres with time-series stuff"? — senior signal.
• Do you say "continuous aggregates handle the read side; hypertables handle the write side" in the first sentence when someone asks about scaling? — required answer.
• Do you distinguish Postgres declarative partitioning from TimescaleDB chunking — same idea, but automated, dynamic, with query pruning and compression built in? — senior signal.
• Do you name segmentby and orderby as the two tuning knobs that decide whether compression hits 10× or 100×? — senior signal.
• Do you describe TimescaleDB as "Postgres that grew time-series muscles" rather than as vague "another database"? — required answer.

Worked example — the four-database comparison table

Detailed explanation. The single most useful artifact for a TimescaleDB interview is a memorised 4×4 comparison table across the four axes that matter — SQL surface, ingest ceiling, compression, ecosystem. Every senior time-series discussion converges on this table within the first ten minutes; having it in your head is what separates a fluent answer from a stumbling one. Walk through building the table for a hypothetical observability platform ingesting Kubernetes pod-level CPU + memory samples at 200 k rows/s across 10 k pods.

• Workload. 200 k rows/s ingest, 10 k active devices, 90-day retention, 1-minute rollups for dashboards, 1-hour rollups for capacity planning, 1-day rollups for cost reports.
• Downstream. Grafana dashboards, Snowflake nightly export for finance, an alerting service reading the last five minutes.
• Target. Sub-second dashboards at any zoom level; retention affordable under $2 k / month; team already runs Postgres.

Question. Build the four-database comparison for the observability workload and pick the database each requirement drives you toward.

Input.

Database	SQL surface	Ingest ceiling	Compression	Ecosystem
TimescaleDB	Postgres SQL, JOINs, JSONB	~250 k rows/s (single node)	10–100× per chunk (columnar)	full Postgres tooling
InfluxDB 3.0	Flux + SQL (DataFusion)	~1 M rows/s	Parquet on object store	Influx-native
QuestDB	SQL (ANSI-ish)	~1 M rows/s (single node)	columnar native	small but growing
ClickHouse	SQL (rich dialect)	~2 M rows/s (per node)	MergeTree columnar	wide OLAP tooling

Code.

-- TimescaleDB — the greenfield greenfield hypertable for our workload
CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE metrics (
    ts          TIMESTAMPTZ NOT NULL,
    device_id   TEXT        NOT NULL,
    metric      TEXT        NOT NULL,
    value       DOUBLE PRECISION,
    labels      JSONB
);

-- Turn it into a hypertable, chunked weekly for a 90-day retention window
SELECT create_hypertable(
    'metrics',
    'ts',
    chunk_time_interval => INTERVAL '7 days'
);

-- Index for the common query shape: device + time range
CREATE INDEX idx_metrics_device_ts ON metrics (device_id, ts DESC);

Step-by-step explanation.

1. The comparison table forces the four axes into a single view: SQL surface, ingest ceiling, compression, ecosystem. TimescaleDB wins on SQL surface (it is Postgres) and ecosystem (every Postgres tool works); it draws with the others on ingest ceiling for single-node workloads under ~250 k rows/s; and it hits competitive compression via columnar per-chunk storage.
2. Multi-node ingest above ~250 k rows/s is where TimescaleDB starts to trail purpose-built stores — Timescale's multi-node offering exists but is behind the Timescale License and adds coordination overhead. Above ~1 M rows/s, ClickHouse or InfluxDB 3.0 typically win.
3. For an observability workload that already runs Postgres for OLTP, TimescaleDB's SQL surface is worth the ~4× ingest headroom trade. Teams pay in engineer time far more than in hardware, and shipping metrics into the same Postgres cluster the app team already uses collapses the operations story.
4. Compression is the sleeper win. A naive Postgres table storing three months of 200 k rows/s is ~450 GB. A TimescaleDB hypertable with weekly chunks and compression enabled after 24 h drops to ~15 GB (30× ratio for high-cardinality metrics). Retention cost is the axis that separates viable TimescaleDB deployments from doomed ones.
5. The final choice for our observability workload is TimescaleDB with weekly chunks, continuous aggregates for the three rollup grains, and compression on chunks older than 24 h. The alternative — InfluxDB — buys ~4× ingest headroom but costs the entire Postgres skill investment and doubles the operations surface (now Postgres and Influx).

Output.

Consumer	Recommended primitive	Why
Grafana dashboards (5-min view)	continuous aggregate metrics_1m	pre-materialized 1-minute buckets; sub-second scan
Capacity planning (30-day view)	continuous aggregate metrics_1h	pre-materialized hourly buckets; monthly report in ms
Finance (nightly Snowflake)	continuous aggregate metrics_1d	daily rollup; small export volume
Alerting service (last 5 min)	raw hypertable + real-time materialization	freshest possible bucket; sub-second latency

Rule of thumb. Never pick a time-series database based on "which one is fastest on the benchmark page." Pick it on (SQL surface × ingest ceiling × compression × ecosystem) — the four axes. Write the table on a whiteboard first; the database choice falls out of the constraints, and 8 times out of 10 the constraint that decides it is "we already run Postgres."

Worked example — what interviewers actually probe on TimescaleDB

Detailed explanation. The senior data-engineering TimescaleDB interview has a predictable structure: the interviewer opens with an ambiguous question ("how would you store our device metrics?"), then progressively narrows to test whether you understand hypertables, continuous aggregates, compression, and the ecosystem trade-off. The candidates who name the primitive in sentence one score highest; the candidates who describe "an ingestion pipeline" score lowest. Walk through the interview grading rubric.

• Ambiguous opener. "How would you store 10 k devices reporting a CPU sample every second?" — invites you to name a hypertable.
• Follow-up 1. "Dashboards on 30 days of data are slow — what do you do?" — probes continuous aggregates.
• Follow-up 2. "Retention is 90 days but disk is filling up — what do you tune?" — probes compression.
• Follow-up 3. "Why TimescaleDB over InfluxDB or vanilla Postgres partitioning?" — probes ecosystem and comparison.
• Follow-up 4. "How would you write the p95 latency query for the last hour?" — probes hyperfunctions.

Question. Draft a 5-minute senior TimescaleDB answer that covers the four probes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Primitive named	"a big Postgres table with an index on ts"	"a hypertable chunked weekly on ts, indexed on (device_id, ts DESC)"
Dashboard scaling	"add more CPU"	"continuous aggregate at 1-minute grain with a 5-minute refresh policy"
Retention	"drop old rows"	"compression policy after 24 h + retention policy at 90 days"
Comparison	"TimescaleDB is faster"	"Postgres SQL + JOINs + JSONB is worth the ingest trade vs InfluxDB"
Query fluency	"GROUP BY hour"	"time_bucket('1 hour', ts) + approx_percentile or percentile_agg"

Code.

Senior TimescaleDB answer template (5 minutes)
==============================================

Minute 1 — name the primitive up front
  "I'd model this as a TimescaleDB hypertable on (ts) with a weekly
   chunk_time_interval, indexed on (device_id, ts DESC) for the common
   query shape."

Minute 2 — dashboards
  "For dashboard freshness I'd add three continuous aggregates — 1-min,
   1-hour, 1-day — with refresh policies matching the dashboard cadence.
   Real-time materialization means the latest bucket unions raw rows with
   the materialised view for freshness."

Minute 3 — compression + retention
  "Compression policy after 24 h with segmentby=device_id and
   orderby=ts DESC — hits 10–30× on this shape. Retention policy at 90
   days drops old chunks in O(1). Timescale Cloud tiered storage moves
   older chunks to S3 if we want longer history at lower cost."

Minute 4 — comparison
  "TimescaleDB over vanilla PG partitioning because chunks are automatic,
   compression is columnar per chunk, and the planner does chunk
   exclusion for free. Over InfluxDB because we keep Postgres SQL, JOINs
   to the device inventory table, and the entire Postgres tooling
   ecosystem. The trade is ~4× lower ingest ceiling vs Influx 3.0."

Minute 5 — query fluency
  "For p95 latency last hour: SELECT time_bucket('1 minute', ts) AS b,
   approx_percentile(0.95, percentile_agg(latency_ms))
   FROM  metrics WHERE ts > now() - INTERVAL '1 hour' GROUP BY b;
   That uses toolkit's percentile_agg for streaming quantile estimation."

Step-by-step explanation.

1. Minute 1 is the crucial framing. Naming the primitive immediately — "hypertable on (ts) with weekly chunks" — signals you're a decision-maker, not a task-runner. Weak candidates dive into tools ("we'd use PostgreSQL and…") before naming the primitive that makes TimescaleDB different from vanilla PG.
2. Minute 2 addresses the read-side scaling problem before the interviewer asks. Continuous aggregates are the single biggest read-side win TimescaleDB offers; naming them, their refresh cadence, and real-time materialization is the senior signal.
3. Minute 3 covers storage economics — compression + retention are the two policies that decide whether TimescaleDB is affordable at multi-year scale. Naming segmentby and the expected 10–30× ratio shows you've tuned production compression, not just read the docs.
4. Minute 4 handles the comparison probe unprompted. Framing it as "keep Postgres tooling for a 4× ingest trade" is the mature answer; the weak answer is "TimescaleDB is faster" (which is only sometimes true and misses the ecosystem point entirely).
5. Minute 5 flexes hyperfunction fluency — time_bucket, percentile_agg, approx_percentile. This shows you know the library on top of Postgres, not just the extension. Interviewers love this last minute because it separates candidates who've written five queries from those who've written five hundred.

Output.

Grading criterion	Weak score	Senior score
Names hypertable in minute 1	rare	mandatory
Names continuous aggregates for dashboards	rare	required
Names compression policy	occasional	mandatory
Names the ecosystem trade	rare	senior signal
Names hyperfunctions by name	rare	senior signal

Rule of thumb. The senior TimescaleDB answer is a 5-minute monologue that covers hypertables, continuous aggregates, compression, comparison, and hyperfunctions without waiting for the follow-ups. Rehearse it once; deploy it every time.

Senior interview question on TimescaleDB adoption

A senior interviewer often opens with: "You inherit a Postgres 16 cluster that stores three years of Kubernetes pod-level CPU + memory samples in a single 2 TB table. Ingest is 200 k rows/s and rising. Dashboards on 30 days of data take 20 seconds. Walk me through the TimescaleDB migration you'd propose, the hypertable + chunk_time_interval choice, the continuous-aggregate layer, and the compression + retention story."

Solution Using TimescaleDB hypertable + 3-tier continuous aggregates + compression + retention

-- Step 1 — install the extension on the Postgres primary (v2.x)
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Step 2 — convert the existing table to a hypertable in place
-- (`migrate_data => true` moves existing rows into chunks)
SELECT create_hypertable(
    'metrics',
    'ts',
    chunk_time_interval => INTERVAL '7 days',
    migrate_data        => true
);

-- Step 3 — index the common query shape
CREATE INDEX IF NOT EXISTS idx_metrics_device_ts
    ON metrics (device_id, ts DESC);

-- Step 4 — three continuous aggregates for the three dashboard grains
CREATE MATERIALIZED VIEW metrics_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts)  AS bucket,
       device_id,
       metric,
       avg(value)                    AS avg_value,
       max(value)                    AS max_value,
       count(*)                      AS n
FROM   metrics
GROUP  BY bucket, device_id, metric;

CREATE MATERIALIZED VIEW metrics_1h
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', bucket) AS bucket,
       device_id,
       metric,
       avg(avg_value)                 AS avg_value,
       max(max_value)                 AS max_value,
       sum(n)                         AS n
FROM   metrics_1m
GROUP  BY 1, 2, 3;

CREATE MATERIALIZED VIEW metrics_1d
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', bucket)  AS bucket,
       device_id,
       metric,
       avg(avg_value)                 AS avg_value,
       max(max_value)                 AS max_value,
       sum(n)                         AS n
FROM   metrics_1h
GROUP  BY 1, 2, 3;

-- Step 5 — refresh policies (materialised buckets refreshed on cadence)
SELECT add_continuous_aggregate_policy('metrics_1m',
    start_offset      => INTERVAL '2 hours',
    end_offset        => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '5 minutes');

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

SELECT add_continuous_aggregate_policy('metrics_1d',
    start_offset      => INTERVAL '30 days',
    end_offset        => INTERVAL '1 day',
    schedule_interval => INTERVAL '6 hours');

-- Step 6 — compression policy (columnar compression on chunks older than 24 h)
ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id, metric',
    timescaledb.compress_orderby   = 'ts DESC'
);

SELECT add_compression_policy('metrics', INTERVAL '24 hours');

-- Step 7 — retention policy (drop chunks older than 90 days)
SELECT add_retention_policy('metrics', INTERVAL '90 days');

Step-by-step trace.

Step	Before (raw Postgres table)	After (TimescaleDB)
Ingest ceiling	~50 k rows/s (index bottleneck)	~250 k rows/s (chunk-local writes)
Dashboard scan (30 days)	~20 s (full-index scan)	< 500 ms (continuous aggregate)
Storage (90 days)	~450 GB uncompressed	~15 GB compressed (30× ratio)
Retention	manual DELETE + VACUUM (hours)	O(1) chunk drop via policy
Query pruning	index range scan	chunk exclusion (2 of 13 chunks)
Rollup freshness	ad-hoc GROUP BY (10-20 s)	pre-materialised + real-time union
Multi-year retention	infeasible on disk	tiered storage to S3 (Timescale Cloud)

After the migration, ingest saturates at ~250 k rows/s (per single-node primary), dashboards on 30 days of 1-minute-grain data return in ~200 ms via the metrics_1m continuous aggregate, compressed chunks older than 24 h use ~30× less disk than raw, and the 90-day retention policy drops old chunks in O(1) each night. The 20-second dashboard query becomes a two-chunk scan of a pre-materialised view; the disk-full risk disappears from the on-call runbook.

Output:

Metric	Before	After
p50 dashboard latency (30 days)	~20 s	~200 ms
Ingest ceiling	~50 k rows/s	~250 k rows/s
Storage for 90 days	~450 GB	~15 GB (30×)
Retention cost	manual DELETE (hours)	O(1) chunk drop
Query pruning	index range scan	chunk exclusion
Ecosystem	vanilla Postgres tooling	vanilla Postgres tooling (unchanged)

Why this works — concept by concept:

• Hypertable + weekly chunks — the hypertable API (create_hypertable) automatically partitions the parent table into chunks by ts, one per week. Writes flow into the newest, hot, small chunk; queries prune irrelevant chunks before opening them. The chunk boundary is the coarse-grained partition every read and write inherits.
• Three-tier continuous aggregates — metrics_1m reads the raw hypertable; metrics_1h reads metrics_1m; metrics_1d reads metrics_1h. Each higher grain is orders of magnitude smaller than the last, so dashboards scan a table that matches their zoom level. Refresh policies keep each grain fresh on its own cadence.
• Columnar compression with segmentby — chunks older than 24 h are compressed columnarly, with rows grouped by (device_id, metric) inside each chunk and ordered by ts DESC. Same (device_id, metric) values compress spectacularly (they're identical); the resulting ratio hits 10–100× for high-cardinality time-series data. orderby ts DESC keeps the newest rows first for dashboard queries.
• Retention policy at 90 days — add_retention_policy schedules a background job that drops chunks entirely older than 90 days. Because chunks are small (weekly), the drop is O(1); no DELETE ... WHERE ts < … full-table scan. This is the operational win that makes multi-year retention feasible.
• Cost — one Postgres primary (unchanged), the TimescaleDB extension (free / Apache-licensed core), three continuous aggregates (~1% storage overhead), one compression policy, one retention policy. The eliminated cost is the 20-second dashboard scan (developer trust in the platform) and the manual DELETE + VACUUM retention chore. Net O(1) per chunk drop versus O(N) full-table DELETE.

SQL
Topic — sql
SQL problems on time-series and TimescaleDB queries

Practice →

Aggregation Topic — aggregation Aggregation problems on time-bucketed rollups

Practice →

---

2. Hypertables + chunking

create_hypertable turns a Postgres table into an automatically-partitioned time-series store — writes land in the hot chunk, reads prune the cold ones

The mental model in one line: a timescaledb hypertable is a virtual parent table over many small child chunks — each chunk covers a contiguous time range (weekly, daily, hourly, whatever chunk_time_interval says), writes go into the chunk that contains the row's ts, and reads let the planner exclude chunks whose time range does not overlap the query's WHERE predicate before opening a single one. Every senior TimescaleDB deployment lives or dies by the chunk-time-interval choice; every scale problem senior engineers face traces back to a chunk boundary that no longer matches the workload.

The four things create_hypertable actually does.

• Creates a virtual parent. After SELECT create_hypertable('metrics', 'ts'), the metrics table is no longer a normal table — it is a hypertable, and every existing row moves into chunks by ts. The parent still accepts INSERTs and SELECTs exactly like a normal Postgres table; the chunking is transparent.
• Registers a chunk-creation trigger. Every INSERT looks up the row's ts, computes which chunk it belongs to, creates that chunk if it doesn't already exist, and routes the write there. Chunk creation is automatic — the DBA never runs CREATE TABLE chunk_2026_w30.
• Enables chunk-exclusion planning. The Postgres planner learns to prune chunks whose time range does not intersect the query's ts predicate. A WHERE ts BETWEEN '2026-07-20' AND '2026-07-21' on a weekly-chunked hypertable opens exactly one chunk out of 52 for a year's retention.
• Unlocks TimescaleDB-only features. Continuous aggregates, compression policies, retention policies, add_reorder_policy — none of these work against a plain Postgres table. All of them are hypertable-only.

The chunk_time_interval tuning rule — the single most important knob.

• What it is. The width of one chunk on the time axis. INTERVAL '7 days' says "each chunk covers a week." INTERVAL '1 day' says "each chunk covers a day." The default is 7 days but the default is often wrong.
• The rule of thumb. Aim for chunk size where one recent chunk plus its indexes fit comfortably in ~25% of shared_buffers. A chunk that's too small (thousands of tiny chunks) inflates planning time and metadata overhead. A chunk that's too large (multi-hundred-GB chunks) breaks the "hot chunk fits in memory" invariant and drops write throughput.
• Sizing example. 200 k rows/s × 400 bytes/row × 86 400 s/day = ~6.9 GB/day of raw data. On a Postgres box with 32 GB shared_buffers, ~8 GB is 25% — so daily chunks are about right. Weekly chunks would be ~48 GB, which does not fit; write throughput would collapse as the OS page cache thrashes.
• When to change it. Recomputable at any time via set_chunk_time_interval(...). New chunks respect the new interval; old chunks are untouched. Migrating chunk boundaries requires a manual chunk-rewrite job.

Chunk exclusion — the query planner win.

• What it looks like. EXPLAIN on a chunk-excluded query shows one or two Custom Scan (ChunkAppend) nodes with the excluded chunks listed as "Chunks excluded during planning: N."
• When it fires. Any WHERE predicate that involves the time column with a constant or stable expression. WHERE ts > now() - INTERVAL '1 hour' prunes correctly because now() is stable within a transaction. WHERE ts > some_function(other_col) does not prune because the planner can't evaluate it at plan time.
• When it doesn't fire. Complex JOINs where the time predicate is on a joined table can defeat pruning. The fix is to push the time predicate down onto the hypertable directly, or use TimescaleDB's _timescaledb_functions.chunk_status() helper to see which chunks were opened.

Chunk reordering — the query-locality trick.

• What it does. add_reorder_policy('metrics', 'idx_metrics_device_ts') schedules a background job that rewrites older chunks so their rows are physically ordered by the index. This makes range scans over a single device sequentially-clustered on disk — dramatically fewer random reads.
• When to use. Any workload where dashboards or alerting queries hit a small set of devices at a time. The reorder pays off when compression + index-scan-heavy queries dominate. Not needed for pure ingest workloads.
• Cost. One background job per chunk, per reorder cycle. Chunks are rewritten in place; free space accumulates during the rewrite so plan disk headroom accordingly.

Native Postgres partitioning vs TimescaleDB chunking — the comparison every senior interviewer probes.

• Both. Both partition a large table into smaller child tables by a range column. Both let the planner prune child tables before opening them.
• TimescaleDB wins. Automatic chunk creation (no manual CREATE PARTITION); dynamic chunk boundaries; per-chunk columnar compression; continuous aggregates; retention policies; reorder policies; chunk-time-interval tuning without downtime. All the operational polish is TimescaleDB-only.
• Native PG wins. No extension dependency; simpler mental model for DBAs who already know declarative partitioning; slightly more flexible partition-by-list (TimescaleDB's space partitioning is more constrained).
• Verdict. For any workload where the partition column is time-shaped and the write volume matters, TimescaleDB chunking is a clear upgrade. For a rarely-changing, cold-storage-shaped table, plain Postgres partitioning is fine.

Common interview probes on hypertables.

• "What is a hypertable?" — required answer is "a virtual parent table over many small chunks, partitioned by time, with automatic chunk creation and chunk-exclusion query planning."
• "How do you pick chunk_time_interval?" — one recent chunk + indexes ~25% of shared_buffers.
• "How does chunk exclusion work?" — planner sees the WHERE ts predicate at plan time, prunes chunks whose range doesn't intersect.
• "Why not use native Postgres partitioning?" — no automatic chunk creation, no compression, no continuous aggregates.

Worked example — creating a hypertable with tuned chunk_time_interval

Detailed explanation. The canonical hypertable setup: create the table, create_hypertable with a size-appropriate chunk_time_interval, add the right indexes, verify chunks are being created on insert, and confirm chunk exclusion fires on the common query shape. Walk through the setup for the observability workload.

• Table shape. metrics(ts, device_id, metric, value, labels).
• Ingest. 200 k rows/s peak, ~7 GB/day of raw data.
• Shared_buffers. 32 GB (Postgres box has 128 GB RAM total).
• Common query. WHERE device_id = ? AND ts BETWEEN ? AND ?.

Question. Set up the hypertable, size the chunks correctly, add the right index, and prove chunk exclusion is firing.

Input.

Parameter	Value
Table	metrics(ts, device_id, metric, value, labels)
chunk_time_interval	1 day (~7 GB/chunk fits in 25% of 32 GB shared_buffers)
Index	(device_id, ts DESC) — matches the common query shape
Query shape	WHERE device_id = ? AND ts BETWEEN ? AND ?

Code.

-- 1. Install the extension (superuser)
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 2. Create the table (still plain Postgres at this point)
CREATE TABLE metrics (
    ts          TIMESTAMPTZ NOT NULL,
    device_id   TEXT        NOT NULL,
    metric      TEXT        NOT NULL,
    value       DOUBLE PRECISION,
    labels      JSONB
);

-- 3. Convert to a hypertable with daily chunks
SELECT create_hypertable(
    'metrics',
    'ts',
    chunk_time_interval => INTERVAL '1 day'
);

-- 4. Index the common query shape
CREATE INDEX idx_metrics_device_ts
    ON metrics (device_id, ts DESC);

-- 5. Insert a week of data (production would ingest via COPY / batch INSERT)
INSERT INTO metrics(ts, device_id, metric, value)
SELECT generate_series(
           now() - INTERVAL '7 days',
           now(),
           INTERVAL '1 minute'
       ) AS ts,
       'device-' || (random() * 100)::int AS device_id,
       'cpu_pct'                          AS metric,
       random() * 100                      AS value;

-- 6. Verify chunks were created (one per day, 7 chunks total)
SELECT chunk_name, range_start, range_end
FROM   timescaledb_information.chunks
WHERE  hypertable_name = 'metrics'
ORDER  BY range_start;

-- 7. Prove chunk exclusion fires on the common query
EXPLAIN (ANALYZE, BUFFERS)
SELECT avg(value)
FROM   metrics
WHERE  device_id = 'device-42'
  AND  ts BETWEEN now() - INTERVAL '2 hours' AND now();

Step-by-step explanation.

1. Step 1 installs the TimescaleDB extension. This is a one-time superuser operation per database and adds the _timescaledb_catalog schema plus a set of hypertable / continuous-aggregate / compression functions.
2. Step 2 creates the ordinary Postgres table. At this point it's just a heap; no chunking, no time-series machinery. This is intentional — the shape of the table (columns, PK, defaults) is a normal Postgres design decision.
3. Step 3 promotes the table to a hypertable with INTERVAL '1 day' chunks. Under the hood, TimescaleDB rewrites the table into a virtual parent + registers a chunk-creation trigger + subscribes it to the extension's metadata. From the app's perspective, nothing changes; INSERT and SELECT still target metrics.
4. Step 4 adds a composite index on (device_id, ts DESC). This is the chunk-local index — Postgres creates one per chunk, automatically. Every future chunk inherits this index shape. Choosing ts DESC matches the "give me the most recent data first" reflex most dashboards use.
5. Steps 6 and 7 verify the setup. timescaledb_information.chunks lists every chunk with its time range; you should see 7 chunks (one per day) for a week's data. The EXPLAIN output shows Chunks excluded during planning: 5 — the planner opened only the last two days' chunks because the query's ts BETWEEN predicate covers a 2-hour window.

Output.

Chunk	Range start	Range end	Rows
_hyper_1_1_chunk	2026-07-20 00:00	2026-07-21 00:00	~1 440
_hyper_1_2_chunk	2026-07-21 00:00	2026-07-22 00:00	~1 440
_hyper_1_3_chunk	2026-07-22 00:00	2026-07-23 00:00	~1 440
_hyper_1_4_chunk	2026-07-23 00:00	2026-07-24 00:00	~1 440
_hyper_1_5_chunk	2026-07-24 00:00	2026-07-25 00:00	~1 440
_hyper_1_6_chunk	2026-07-25 00:00	2026-07-26 00:00	~1 440
_hyper_1_7_chunk	2026-07-26 00:00	2026-07-27 00:00	~1 440

Rule of thumb. Set chunk_time_interval so one recent chunk plus its indexes lives comfortably in 25% of shared_buffers. Verify chunk exclusion fires with EXPLAIN ANALYZE on your most common query shape before going to production. Add the composite (dimension, ts DESC) index that matches your dashboards — chunk-local indexes are cheap and each chunk gets one.

Worked example — the chunk-size-too-large failure mode

Detailed explanation. An observability team ingests 200 k rows/s into a hypertable with the default INTERVAL '7 days' chunks. Ingest throughput is fine for the first ~24 hours, then collapses to ~40 k rows/s. The team's diagnosis reveals the current chunk has grown to 48 GB — well beyond the box's 32 GB shared_buffers — and the OS page cache is thrashing as writes fault in random pages of the chunk's B-tree indexes. Walk through the diagnosis and the fix.

• Symptom. Ingest throughput drops from 200 k rows/s to 40 k rows/s after ~24 hours.
• Root cause. Default weekly chunk grew to 48 GB; hot chunk no longer fits in shared_buffers; every insert triggers random-page faults on index maintenance.
• Fix. Change chunk_time_interval to INTERVAL '1 day'; write a one-off migration to split the oversized chunk into daily sub-chunks.

Question. Diagnose the chunk-size failure mode and reset chunk_time_interval on the live hypertable without dropping data.

Input.

Component	Before	After
chunk_time_interval	7 days	1 day
Chunk size (peak)	~48 GB	~7 GB
Fits in shared_buffers 25%?	no	yes
Steady-state ingest	~40 k rows/s	~250 k rows/s
Chunk count for 30 days	4-5	30

Code.

-- 1. Diagnose — chunk sizes and whether the hot chunk fits in memory
SELECT chunk_name,
       pg_size_pretty(total_bytes)                                                AS total_size,
       pg_size_pretty(index_bytes)                                                AS index_size,
       range_start,
       range_end
FROM   chunks_detailed_size('metrics')
JOIN   timescaledb_information.chunks USING (chunk_name)
ORDER  BY range_start DESC
LIMIT  5;

-- 2. Change chunk_time_interval for future chunks
SELECT set_chunk_time_interval('metrics', INTERVAL '1 day');

-- 3. Verify the change
SELECT h.hypertable_name,
       h.chunk_time_interval
FROM   timescaledb_information.dimensions h
WHERE  h.hypertable_name = 'metrics';

# 4. One-off migration to split the oversized weekly chunk into daily chunks
#    (do this off-peak; the rewrite requires a moderate exclusive lock)

import psycopg2
from datetime import datetime, timedelta, timezone

def split_oversized_chunk(conn, chunk_name: str, day_start: datetime, days: int) -> None:
    """Copy rows from the oversized chunk into per-day tables, then drop the source."""
    cur = conn.cursor()

    for d in range(days):
        day_lo = day_start + timedelta(days=d)
        day_hi = day_lo + timedelta(days=1)
        cur.execute(f"""
            INSERT INTO metrics
            SELECT ts, device_id, metric, value, labels
            FROM   ONLY {chunk_name}
            WHERE  ts >= %s AND ts < %s
        """, (day_lo, day_hi))
        conn.commit()

    # After all rows are re-inserted, TimescaleDB has created 7 daily chunks
    # that shadow the same time range as the oversized weekly chunk.
    # Drop the old chunk.
    cur.execute(f"DROP TABLE {chunk_name}")
    conn.commit()
    print(f"split {chunk_name} into {days} daily chunks")


conn = psycopg2.connect(...)
split_oversized_chunk(
    conn,
    "_timescaledb_internal._hyper_1_23_chunk",
    datetime(2026, 7, 13, tzinfo=timezone.utc),
    7,
)

Step-by-step explanation.

1. Step 1 uses chunks_detailed_size to list every chunk with its size on disk. The output should reveal the current chunk at ~48 GB — this is the smoking gun. The rule of thumb is broken: 48 GB is 150% of the 32 GB shared_buffers.
2. Step 2 changes chunk_time_interval to INTERVAL '1 day'. This only affects future chunks; existing chunks retain their original 7-day range. New writes going into today's chunk will land in a 1-day chunk once the current week's chunk ends.
3. Step 3 verifies the setting via timescaledb_information.dimensions. Sanity check — always confirm the extension state after any tuning change.
4. Step 4 is a one-off migration. It re-inserts rows from the oversized chunk into the parent table; because chunk_time_interval is now 1 day, each INSERT routes to a per-day chunk. After all seven days are moved, the original weekly chunk is empty and can be dropped. This is the operational cost of the wrong initial choice — a rewrite job that competes with ingest.
5. After the migration, hot-chunk size drops from 48 GB to ~7 GB, page-cache thrashing disappears, and ingest returns to ~250 k rows/s. The lesson: pick chunk_time_interval on day zero based on your peak ingest × chunk-time × 25% shared_buffers rule; changing it later is possible but painful.

Output.

Metric	Before fix	After fix
Hot chunk size	48 GB	7 GB
Fits in 25% shared_buffers	no	yes
Steady-state ingest	40 k rows/s	250 k rows/s
Chunk count for 30 days	4-5 oversized	30 balanced
Page-cache thrash	severe	none

Rule of thumb. Never leave chunk_time_interval at the default without checking the math for your ingest rate. (peak_ingest_bytes_per_second × 86 400 × chunk_days) ≤ 0.25 × shared_buffers is the invariant. If your rate is 200 k rows/s and shared_buffers is 32 GB, daily chunks are the right answer, not weekly.

Senior interview question on hypertable chunking

A senior interviewer might ask: "Your team runs a TimescaleDB hypertable receiving 200 k rows/s of Kubernetes pod metrics. The default 7-day chunks were kept. Ingest throughput has dropped to 40 k rows/s over the last month. Walk me through the diagnosis, the tuning fix, and the operational plan to resize existing chunks without downtime."

Solution Using chunk-size diagnosis + set_chunk_time_interval + chunk-split migration + validation

-- 1. Diagnose — list chunks by size to find the oversized ones
SELECT chunk_name,
       pg_size_pretty(total_bytes)  AS total,
       pg_size_pretty(index_bytes)  AS idx,
       range_start,
       range_end,
       is_compressed
FROM   chunks_detailed_size('metrics')
JOIN   timescaledb_information.chunks USING (chunk_name)
ORDER  BY total_bytes DESC
LIMIT  10;

-- Expected: top chunks show total = 30–50 GB, breaking the 25% shared_buffers rule

-- 2. Tune future chunks — daily going forward
SELECT set_chunk_time_interval('metrics', INTERVAL '1 day');

-- 3. Verify
SELECT hypertable_name, column_name, time_interval
FROM   timescaledb_information.dimensions
WHERE  hypertable_name = 'metrics';

# 4. Chunk-split migration — runs off-peak; splits the last N weekly chunks
#    into daily chunks by re-inserting through the parent

import psycopg2

def split_weekly_chunks_into_daily(conn, hypertable: str, last_n_chunks: int) -> None:
    """Rewrite the last N weekly chunks into daily chunks."""
    cur = conn.cursor()
    cur.execute("""
        SELECT chunk_schema || '.' || chunk_name AS chunk,
               range_start,
               range_end
        FROM   timescaledb_information.chunks
        WHERE  hypertable_name = %s
        ORDER  BY range_start DESC
        LIMIT  %s
    """, (hypertable, last_n_chunks))

    for chunk, lo, hi in cur.fetchall():
        # Re-insert; new chunk_time_interval routes to daily chunks
        cur.execute(f"""
            INSERT INTO {hypertable}
            SELECT * FROM ONLY {chunk}
            ORDER BY ts
        """)
        conn.commit()
        # Drop the source chunk (its data now lives in daily chunks)
        cur.execute(f"DROP TABLE {chunk}")
        conn.commit()
        print(f"split {chunk} ({lo} → {hi}) into daily chunks")

split_weekly_chunks_into_daily(psycopg2.connect(...), "metrics", 4)

-- 5. Validate — new chunks show up as daily, sized ~7 GB each
SELECT chunk_name,
       pg_size_pretty(total_bytes) AS size,
       range_start,
       range_end
FROM   chunks_detailed_size('metrics')
JOIN   timescaledb_information.chunks USING (chunk_name)
WHERE  range_start > now() - INTERVAL '30 days'
ORDER  BY range_start DESC;

-- 6. Prove ingest recovered — instrument the write path
-- (external: Prometheus counter on rows-per-second at the ingest proxy)

Step-by-step trace.

Step	Action	Impact
Diagnose	list chunk sizes	reveals oversized weekly chunks
set_chunk_time_interval	INTERVAL '1 day'	future chunks bounded to ~7 GB
Split migration	re-insert + DROP old chunk	existing oversized chunks split into daily
Validate size	inspect new chunks	~7 GB each; fits in 25% shared_buffers
Validate ingest	Prometheus counter	40 k → 250 k rows/s recovery
Ongoing	new chunks stay daily	no future regression

After the tuning + migration, the hot chunk sits comfortably at ~7 GB (within 25% of shared_buffers), the OS page cache stops thrashing, and ingest recovers to ~250 k rows/s within a few hours of the split. The migration ran off-peak in ~2 hours per chunk; ingest continued in parallel because writes route to new chunks, not the ones being split.

Output:

Metric	Before	After
Hot chunk size	48 GB	7 GB
Fits in 25% shared_buffers	no	yes
Steady-state ingest	40 k rows/s	250 k rows/s
Chunk count (last 30 days)	4-5 oversized	30 balanced
Migration downtime	0 (background)	0 (background)
Time to run migration	~2 h per chunk × 4 chunks	~8 h off-peak

Why this works — concept by concept:

• Hypertable chunk-size invariant — the hot chunk's data + indexes must fit in ~25% of shared_buffers for writes to stay index-cached. Above that, every insert faults random index pages from disk, and throughput collapses from CPU-bound to IO-bound.
• set_chunk_time_interval — a metadata-only change that resets the future chunk width. Existing chunks are untouched; future chunks respect the new interval. This is the safe, online step; no lock, no rewrite.
• Chunk-split migration via re-insert — the operational cost of the wrong initial choice. Reading from ONLY chunk and inserting through the parent routes rows into the new daily chunks. DROP TABLE chunk reclaims the source. This is O(N) per split chunk; run it off-peak, one chunk at a time.
• Validation with Prometheus / EXPLAIN — you don't trust the fix until the ingest counter recovers and EXPLAIN on a common query shows chunk exclusion still fires. Both are cheap checks; both catch regressions early.
• Cost — one metadata update (free), one migration job (~2 h per chunk, off-peak), ~24 h of temporarily doubled write I/O during the split. The recovered cost is a 6× ingest headroom and the elimination of the page-cache thrash incident from the on-call runbook. Net O(chunks_to_split) one-time versus permanent ingest collapse.

SQL
Topic — sql
SQL problems on partitioned + time-series queries

Practice →

Design Topic — design Design problems on time-series chunking

Practice →

---

3. Continuous aggregates + real-time materialization

CREATE MATERIALIZED VIEW ... WITH (timescaledb.continuous) is the read-side scale-out — pre-computes rollups on a schedule, unions with fresh raw rows for the latest bucket

The mental model in one line: a timescaledb continuous aggregate is a Postgres materialised view over a time_bucket(...)-based GROUP BY on a hypertable, backed by a hidden hypertable of pre-computed buckets, refreshed on a policy schedule, and — critically — enhanced with real-time materialization that unions the pre-materialised older buckets with a live scan of the raw hypertable for the newest bucket so dashboards see sub-second-fresh data without waiting for the next refresh. Every senior TimescaleDB deployment layers continuous aggregates at multiple grains (1-minute, 1-hour, 1-day) because a single grain never satisfies every dashboard zoom level.

The four things a continuous aggregate actually is.

• A materialised view. It's a Postgres CREATE MATERIALIZED VIEW — the same SQL, the same SELECT ... GROUP BY shape — with a special WITH (timescaledb.continuous) clause.
• Backed by its own hypertable. The materialised buckets live in a hidden hypertable inside the _timescaledb_internal schema. That hidden hypertable inherits all hypertable benefits: chunking, compression, retention. A continuous aggregate is a hypertable underneath, which is why it scales.
• Refreshed by a policy. add_continuous_aggregate_policy(...) schedules a background job that periodically re-runs the underlying aggregate over the window [now() - start_offset, now() - end_offset] and updates the hidden hypertable. Refresh cadence is configurable per aggregate.
• Enhanced with real-time materialization. By default, a query against the aggregate returns pre-materialised buckets plus a live union with the raw hypertable for buckets that haven't been refreshed yet. This is what makes dashboards feel sub-second-fresh; the alternative (only pre-materialised buckets) is stale by the refresh cadence.

The refresh-policy anatomy.

• start_offset. How far back to refresh. INTERVAL '2 hours' means "recompute buckets from 2 hours ago up to end_offset." Longer offsets tolerate late-arriving data; shorter offsets reduce refresh cost.
• end_offset. How recent to refresh. INTERVAL '5 minutes' means "don't touch buckets newer than 5 minutes ago." This is the "watermark" — buckets inside the end_offset are considered "live" and served by real-time materialization.
• schedule_interval. How often the refresh runs. INTERVAL '5 minutes' means "every 5 minutes, refresh the window." Match this to the dashboard's expected freshness.
• Idempotent. A refresh over [t1, t2] reads raw rows in that window and upserts the corresponding buckets in the hidden hypertable. Re-running the same refresh yields the same result. Missed refreshes catch up automatically.

Hierarchical continuous aggregates — the multi-grain pattern.

• The setup. metrics_1m reads from raw metrics. metrics_1h reads from metrics_1m (not from raw). metrics_1d reads from metrics_1h.
• Why hierarchical. Each higher grain reads a 60× smaller table. metrics_1d refresh over 30 days reads ~720 rows from metrics_1h (1 per hour × 30 days) rather than ~43 200 rows from raw (1 per minute × 30 days). Refresh cost drops accordingly.
• The catch. Downstream aggregates depend on upstream aggregates being refreshed first. Schedule metrics_1m refresh every 5 min, metrics_1h every 30 min, metrics_1d every 6 h — each downstream refresh runs after its upstream has caught up.
• The trade. Slight loss of precision (aggregates of aggregates aren't always exactly equal to aggregates of raw — e.g. average of averages is not average of raw unless weighted). Mitigation: aggregate sum(...) and count(*) separately, derive averages downstream.

Real-time materialization — the freshness trick most engineers miss.

• Default. ON. WITH (timescaledb.materialized_only = false) is the default; the aggregate exposes materialised buckets and a live union.
• How it works. Query on metrics_1m for the last hour: TimescaleDB reads the materialised buckets for [hour_ago, now - end_offset] and unions them with a live GROUP BY on raw metrics for [now - end_offset, now]. The result is a single result set covering the full hour — pre-materialised where possible, live where necessary.
• The cost. Every read pays for the small live GROUP BY on the tail. For most dashboards this is negligible (last few minutes of a 200 k rows/s stream = ~60 M rows scanned live; still sub-second on a decent box).
• Turn off with. WITH (timescaledb.materialized_only = true) — for pure batch use cases where staleness by the refresh cadence is acceptable.

Comparison to Postgres matviews + Snowflake dynamic tables — the ecosystem probe.

• Postgres MATERIALIZED VIEW. No incremental refresh; a REFRESH MATERIALIZED VIEW re-runs the entire aggregate. Fine for hourly reports; ruinous for high-cardinality time-series.
• Snowflake DYNAMIC TABLE. Warehouse-native incremental refresh with a target lag. Excellent inside Snowflake; useless if your data lives in Postgres.
• TimescaleDB continuous aggregate. Incremental refresh over the recent window; hypertable-backed for chunk-scale storage; real-time materialization for freshness. The Postgres-native version of Snowflake dynamic tables.
• Verdict. For any dashboards backed by a Postgres time-series store, continuous aggregates are the right primitive. They don't replace warehouse dynamic tables for warehouse workloads; they replace ad-hoc GROUP BY on raw hypertables, which is the pattern most teams start with and outgrow.

Common interview probes on continuous aggregates.

• "What is a continuous aggregate?" — required answer is "a materialised view over a time_bucket GROUP BY on a hypertable, backed by its own hypertable, refreshed on a policy schedule."
• "How does the latest bucket stay fresh?" — real-time materialization unions the materialised view with a live raw scan.
• "How do you scale to multi-grain?" — hierarchical continuous aggregates (1m → 1h → 1d).
• "What's the refresh policy's end_offset for?" — watermark; buckets inside the offset are served by real-time materialization, not pre-materialised.

Worked example — creating a 1-minute continuous aggregate + refresh policy

Detailed explanation. The canonical continuous-aggregate setup: define the time_bucket GROUP BY, wrap it in CREATE MATERIALIZED VIEW ... WITH (timescaledb.continuous), attach a refresh policy, verify the hidden hypertable exists, and validate that real-time materialization is on. Walk through the setup for the metrics workload.

• Grain. 1 minute — matches the dashboard's finest zoom level.
• Aggregates. avg, max, count — the trio dashboards need most often.
• Refresh cadence. Every 5 minutes; refresh window [now - 2h, now - 5m].
• Real-time. ON — dashboards see live union for the last 5 minutes.

Question. Create the metrics_1m continuous aggregate, attach the refresh policy, and prove it serves both materialised and real-time buckets.

Input.

Component	Value
Source hypertable	metrics(ts, device_id, metric, value)
Grain	1 minute (time_bucket('1 minute', ts))
Aggregates	avg(value), max(value), count(*)
Refresh window	[now - 2 hours, now - 5 minutes]
Refresh cadence	every 5 minutes
Real-time materialization	ON (default)

Code.

-- 1. Create the continuous aggregate
CREATE MATERIALIZED VIEW metrics_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts)  AS bucket,
       device_id,
       metric,
       avg(value)                     AS avg_value,
       max(value)                     AS max_value,
       count(*)                       AS n
FROM   metrics
GROUP  BY bucket, device_id, metric
WITH NO DATA;   -- create empty; the refresh policy fills it

-- 2. Attach a refresh policy
SELECT add_continuous_aggregate_policy('metrics_1m',
    start_offset      => INTERVAL '2 hours',
    end_offset        => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '5 minutes');

-- 3. One-time backfill of the last 24 hours (subsequent policy runs handle new data)
CALL refresh_continuous_aggregate(
    'metrics_1m',
    now() - INTERVAL '24 hours',
    now() - INTERVAL '5 minutes'
);

-- 4. Verify the hidden hypertable exists
SELECT view_name, materialization_hypertable_name, view_definition
FROM   timescaledb_information.continuous_aggregates
WHERE  view_name = 'metrics_1m';

-- 5. Query — mixes materialised buckets with live real-time union
SELECT bucket, device_id, avg_value, max_value, n
FROM   metrics_1m
WHERE  device_id = 'device-42'
  AND  bucket   > now() - INTERVAL '10 minutes'
ORDER  BY bucket;

Step-by-step explanation.

1. Step 1 creates the continuous aggregate with WITH NO DATA — the materialised view is defined empty. The time_bucket('1 minute', ts) clause is the load-bearing expression; it must be present and it must reference the hypertable's time column.
2. Step 2 attaches the refresh policy. start_offset = 2 hours means "recompute buckets from 2 hours ago"; end_offset = 5 minutes means "don't touch buckets newer than 5 minutes ago." The 5-minute watermark absorbs late-arriving data and defines where real-time materialization takes over.
3. Step 3 backfills the last 24 hours manually. Without this call the aggregate starts empty; the refresh policy would only cover the rolling start_offset window going forward, and historical dashboards would show gaps until enough time passed. CALL refresh_continuous_aggregate(...) is the one-time bootstrap.
4. Step 4 verifies via timescaledb_information.continuous_aggregates — this catalog view lists every continuous aggregate with its backing materialization hypertable name (e.g. _timescaledb_internal._materialized_hypertable_2). Confirming it exists is a sanity check.
5. Step 5 runs the dashboard query. For buckets older than 5 minutes, TimescaleDB reads the pre-materialised hypertable — fast, small. For buckets in the last 5 minutes, TimescaleDB runs a live GROUP BY on the raw metrics hypertable, unions the result, and returns a single ordered stream. The dashboard sees a smooth graph with no staleness gap at the tail.

Output.

Bucket	device_id	avg_value	max_value	n	Source
2026-07-27 09:50	device-42	47.3	82.1	60	materialised (older than 5 min)
2026-07-27 09:51	device-42	51.7	79.4	60	materialised
2026-07-27 09:52	device-42	49.9	88.0	60	materialised
2026-07-27 09:53	device-42	44.2	71.5	60	materialised
2026-07-27 09:54	device-42	55.6	91.2	60	materialised
2026-07-27 09:55	device-42	48.8	76.8	60	real-time (within end_offset)
2026-07-27 09:56	device-42	52.1	83.5	60	real-time
2026-07-27 09:57	device-42	47.3	79.1	60	real-time
2026-07-27 09:58	device-42	50.4	85.6	60	real-time
2026-07-27 09:59	device-42	46.7	74.3	47	real-time (partial bucket)

Rule of thumb. Every continuous aggregate needs three things — a time_bucket GROUP BY, an explicit WITH (timescaledb.continuous) clause, and a refresh policy with start_offset > end_offset. Backfill historical windows once with refresh_continuous_aggregate. Leave real-time materialization ON unless you have a specific batch-only use case.

Worked example — hierarchical continuous aggregates (1m → 1h → 1d)

Detailed explanation. A single continuous aggregate can't cover every dashboard zoom level. A 1-minute grain is fine for the last hour; a 1-hour grain is right for the last week; a 1-day grain is right for the last quarter. Building each grain from raw is wasteful — the 1-hour aggregate would rescan the same rows the 1-minute aggregate already processed. Hierarchical continuous aggregates let each grain read from the one below, cutting refresh cost by 60×. Walk through the three-tier setup.

• Grain 1. metrics_1m reads from raw metrics. Refresh every 5 min.
• Grain 2. metrics_1h reads from metrics_1m. Refresh every 30 min.
• Grain 3. metrics_1d reads from metrics_1h. Refresh every 6 h.

Question. Define the three tiers with the correct dependencies and refresh schedules that respect the "downstream refreshes after upstream" invariant.

Input.

Tier	Source	Refresh cadence	Refresh window
metrics_1m	raw metrics	every 5 min	[now - 2h, now - 5m]
metrics_1h	metrics_1m	every 30 min	[now - 2d, now - 1h]
metrics_1d	metrics_1h	every 6 h	[now - 30d, now - 1d]

Code.

-- Tier 1 — 1-minute buckets over raw metrics
CREATE MATERIALIZED VIEW metrics_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts)  AS bucket,
       device_id,
       metric,
       sum(value)                     AS sum_value,
       count(*)                       AS n
FROM   metrics
GROUP  BY bucket, device_id, metric;

SELECT add_continuous_aggregate_policy('metrics_1m',
    start_offset      => INTERVAL '2 hours',
    end_offset        => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '5 minutes');

-- Tier 2 — 1-hour buckets over metrics_1m
CREATE MATERIALIZED VIEW metrics_1h
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', bucket) AS bucket,
       device_id,
       metric,
       sum(sum_value)                  AS sum_value,
       sum(n)                          AS n
FROM   metrics_1m
GROUP  BY 1, 2, 3;

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

-- Tier 3 — 1-day buckets over metrics_1h
CREATE MATERIALIZED VIEW metrics_1d
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', bucket)  AS bucket,
       device_id,
       metric,
       sum(sum_value)                  AS sum_value,
       sum(n)                          AS n
FROM   metrics_1h
GROUP  BY 1, 2, 3;

SELECT add_continuous_aggregate_policy('metrics_1d',
    start_offset      => INTERVAL '30 days',
    end_offset        => INTERVAL '1 day',
    schedule_interval => INTERVAL '6 hours');

-- Downstream: dashboards can compute avg on-the-fly via sum/n
-- SELECT bucket, device_id, sum_value/n AS avg_value
-- FROM   metrics_1d
-- WHERE  device_id = 'device-42' AND bucket > now() - INTERVAL '30 days';

Step-by-step explanation.

1. Tier 1 stores sum(value) and count(*) rather than avg(value). This is intentional — averages of averages are only correct when re-weighted by the underlying counts. Storing sum and n lets each downstream tier compute sum(sum_value) / sum(n) for the correct weighted average, and it keeps the schema uniform across tiers.
2. Tier 2 reads from metrics_1m — not from raw metrics. This is the "hierarchical" trick. metrics_1h scans one row per minute per device per metric (a 60× smaller input than raw for a 1-minute-ingest workload), so its refresh cost is 60× lower.
3. Tier 3 reads from metrics_1h, again a 60× smaller input. metrics_1d refresh over 30 days scans ~720 rows per device per metric (1 per hour × 30 days × 24) rather than ~43 200 rows from raw.
4. Refresh cadences respect the dependency order — metrics_1m refreshes most frequently (every 5 min), then metrics_1h every 30 min (after metrics_1m has caught up), then metrics_1d every 6 h (after metrics_1h). Refresh windows are chosen so the downstream refresh's start_offset covers the upstream refresh's freshest data.
5. Dashboards compose queries against the lowest-precision-that-covers-the-range tier. A 5-minute view uses metrics_1m; a 7-day view uses metrics_1h; a 30-day view uses metrics_1d. Each query scans a small pre-materialised table plus (for the tail) a live raw union.

Output.

Tier	Source table	Refresh cadence	Rows per refresh (approx)	Refresh cost
metrics_1m	metrics (raw, ~200 k rows/s)	every 5 min	~60 M raw rows	high
metrics_1h	metrics_1m	every 30 min	~1 M rows (60× smaller)	medium
metrics_1d	metrics_1h	every 6 h	~17 k rows (60× smaller)	low

Rule of thumb. Store sum and count in every tier, derive averages downstream. Each higher grain reads from the one below (not from raw) — this is the hierarchical trick. Schedule refresh cadences from finest to coarsest, each waiting long enough for its upstream to have caught up. Never build metrics_1d from raw when you have metrics_1h already; the refresh cost multiplies by 60× per skipped level.

Worked example — real-time materialization vs materialized_only

Detailed explanation. By default, continuous aggregates union pre-materialised buckets with a live raw scan for the freshest bucket (real-time materialization). For batch reports where staleness by the refresh cadence is acceptable, you can turn this off with materialized_only = true — the query then only reads pre-materialised buckets and never scans raw. The trade-off is freshness vs. read cost. Walk through when each mode is right.

• Real-time. Default. Every query costs a small live GROUP BY on the tail.
• Materialized-only. Opt-in. Queries never touch raw; staleness bounded by the refresh cadence.

Question. Show how to switch modes on an existing continuous aggregate and quantify the cost/freshness trade.

Input.

Mode	Freshness	Read cost	Use case
real-time (default)	up to now (tail scanned live)	pre-materialised + small live GROUP BY	live dashboards, alerting
materialized_only = true	stale by end_offset + refresh interval	pre-materialised only	batch reports, offline analytics

Code.

-- Turn OFF real-time materialization (batch-only mode)
ALTER MATERIALIZED VIEW metrics_1m
    SET (timescaledb.materialized_only = true);

-- Turn it back ON (live mode)
ALTER MATERIALIZED VIEW metrics_1m
    SET (timescaledb.materialized_only = false);

-- Inspect current setting
SELECT view_name, materialized_only
FROM   timescaledb_information.continuous_aggregates
WHERE  view_name = 'metrics_1m';

-- Query cost comparison — instrument with EXPLAIN
--
-- REAL-TIME (materialized_only=false): reads _materialized_hypertable_N
--   for [start, now - end_offset], unions with a live scan of `metrics`
--   for [now - end_offset, now]
EXPLAIN (ANALYZE, BUFFERS)
SELECT time_bucket('1 minute', ts) AS bucket,
       avg_value
FROM   metrics_1m
WHERE  bucket > now() - INTERVAL '30 minutes';

-- MATERIALIZED_ONLY (materialized_only=true): reads _materialized_hypertable_N
--   only; ignores buckets newer than the last refresh
EXPLAIN (ANALYZE, BUFFERS)
SELECT time_bucket('1 minute', ts) AS bucket,
       avg_value
FROM   metrics_1m
WHERE  bucket > now() - INTERVAL '30 minutes';

Step-by-step explanation.

1. ALTER MATERIALIZED VIEW ... SET (timescaledb.materialized_only = true) is a metadata-only flip; no rewrite, no lock. Future queries obey the new mode; the underlying materialization is unchanged.
2. In real-time mode, the EXPLAIN plan shows a Custom Scan (ChunkAppend) on the materialization hypertable plus a raw-hypertable scan with a time_bucket GROUP BY. The live tail is bounded — it scans only rows newer than now() - end_offset — so cost is small.
3. In materialized_only mode, the EXPLAIN plan shows only the Custom Scan (ChunkAppend) on the materialization hypertable. No raw scan, no live GROUP BY. The result is bounded in staleness by the refresh policy's end_offset plus the schedule_interval.
4. Freshness math: with end_offset = 5 min and schedule_interval = 5 min, materialized-only staleness is up to 10 minutes worst-case (5 min inside end_offset + up to 5 min waiting for the next refresh). For dashboards, 10 min stale is often too stale; for finance reports, it's fine.
5. Choose based on read shape: if queries fire dozens of times per minute per user, the live GROUP BY on the tail adds up; consider materialized_only + shorter refresh cadence. If queries fire a few times per hour, real-time is essentially free.

Output.

Metric	Real-time	Materialized-only
Freshness	up to now	up to (end_offset + schedule_interval) stale
Read cost (per query)	pre-materialised + small live GROUP BY	pre-materialised only
CPU on raw hypertable	small per query	zero per query
Best use case	live dashboards, alerts	batch reports, offline export
Failure mode	slow raw hypertable → slow queries	high refresh cost → higher end-of-refresh spike

Rule of thumb. Leave real-time materialization ON for anything dashboards or alerts consume. Turn it OFF only for pure batch reports where staleness by the refresh cadence is explicitly acceptable — and in that case, tighten the refresh policy accordingly (shorter end_offset, shorter schedule_interval).

Senior interview question on continuous aggregates

A senior interviewer might ask: "Your team runs a TimescaleDB hypertable with 200 k rows/s of pod metrics. Dashboards on 30 days of data hit the raw hypertable directly and take 20 s. Design the continuous-aggregate layer, including grain choices, refresh policies, hierarchy, and real-time materialization. Then extend to how the 5-minute alerting service reads freshest data."

Solution Using 3-tier hierarchical continuous aggregates + real-time materialization + alert-friendly reads

-- 1. Tier 1 — 1-minute buckets over raw (finest grain)
CREATE MATERIALIZED VIEW metrics_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts)  AS bucket,
       device_id,
       metric,
       sum(value)                     AS sum_value,
       max(value)                     AS max_value,
       count(*)                       AS n
FROM   metrics
GROUP  BY bucket, device_id, metric
WITH NO DATA;

SELECT add_continuous_aggregate_policy('metrics_1m',
    start_offset      => INTERVAL '2 hours',
    end_offset        => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '5 minutes');

CALL refresh_continuous_aggregate('metrics_1m',
     now() - INTERVAL '7 days', now() - INTERVAL '5 minutes');

-- 2. Tier 2 — 1-hour buckets over metrics_1m
CREATE MATERIALIZED VIEW metrics_1h
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', bucket) AS bucket,
       device_id,
       metric,
       sum(sum_value)                  AS sum_value,
       max(max_value)                  AS max_value,
       sum(n)                          AS n
FROM   metrics_1m
GROUP  BY 1, 2, 3;

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

CALL refresh_continuous_aggregate('metrics_1h',
     now() - INTERVAL '30 days', now() - INTERVAL '1 hour');

-- 3. Tier 3 — 1-day buckets over metrics_1h
CREATE MATERIALIZED VIEW metrics_1d
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', bucket)  AS bucket,
       device_id,
       metric,
       sum(sum_value)                  AS sum_value,
       max(max_value)                  AS max_value,
       sum(n)                          AS n
FROM   metrics_1h
GROUP  BY 1, 2, 3;

SELECT add_continuous_aggregate_policy('metrics_1d',
    start_offset      => INTERVAL '30 days',
    end_offset        => INTERVAL '1 day',
    schedule_interval => INTERVAL '6 hours');

CALL refresh_continuous_aggregate('metrics_1d',
     now() - INTERVAL '365 days', now() - INTERVAL '1 day');

-- 4. Real-time materialization is ON by default — verify
SELECT view_name, materialized_only
FROM   timescaledb_information.continuous_aggregates
WHERE  view_name IN ('metrics_1m', 'metrics_1h', 'metrics_1d');
-- expect materialized_only = false for all three

-- 5. Dashboard query — chooses tier by range
--    (30-day zoom uses metrics_1d; 7-day uses metrics_1h; 5-minute uses metrics_1m)
SELECT bucket,
       sum_value / n AS avg_value,
       max_value
FROM   metrics_1d
WHERE  device_id = 'device-42'
  AND  metric    = 'cpu_pct'
  AND  bucket    > now() - INTERVAL '30 days'
ORDER  BY bucket;

-- 6. Alerting service — sub-minute freshness via metrics_1m (real-time union)
SELECT bucket,
       sum_value / n AS avg_cpu,
       max_value
FROM   metrics_1m
WHERE  device_id = 'device-42'
  AND  metric    = 'cpu_pct'
  AND  bucket    > now() - INTERVAL '5 minutes'
ORDER  BY bucket DESC;

Step-by-step trace.

Layer	Component	Purpose
Raw hypertable	metrics (200 k rows/s)	ground truth, chunked daily
Tier 1	metrics_1m (from raw)	1-min buckets, refresh every 5 min
Tier 2	metrics_1h (from metrics_1m)	1-h buckets, refresh every 30 min
Tier 3	metrics_1d (from metrics_1h)	1-d buckets, refresh every 6 h
Dashboards	pick tier by range	5-min → 1m; 7-day → 1h; 30-day → 1d
Alerts	metrics_1m + real-time union	sub-minute freshness

After deployment, the 30-day dashboard query scans ~30 rows per device per metric from metrics_1d (a pre-materialised, tiny hypertable) in ~50 ms — down from ~20 s on raw. The alerting service reads metrics_1m for the last 5 minutes; TimescaleDB serves the pre-materialised 55 minutes from the hidden hypertable and unions a live scan for the last 5 minutes, returning in ~150 ms. The refresh policy jobs run in the background, each grain 60× smaller than raw thanks to the hierarchy.

Output:

Query	Before (raw hypertable)	After (continuous aggregates)
5-minute alert (device_id filter)	~2 s	~150 ms
24-h dashboard (device_id filter)	~4 s	~200 ms
7-day dashboard	~8 s	~250 ms
30-day dashboard	~20 s	~50 ms
90-day capacity report	~60 s	~80 ms
Refresh CPU (per policy)	N/A	2–5% during refresh burst

Why this works — concept by concept:

• Hierarchical continuous aggregates — three tiers, each reading from the one below. Storage overhead is bounded (each tier is 60× smaller than the previous), refresh cost drops multiplicatively, and dashboards scan the smallest table that covers their zoom.
• Refresh policy with end_offset — the watermark that defines "materialised" vs "live." Buckets older than end_offset are pre-computed on the schedule; buckets newer are served by real-time materialization. end_offset also tolerates late-arriving data.
• Real-time materialization — the union that keeps dashboards and alerts sub-second-fresh without waiting for the next refresh. Default ON; cost is a small live GROUP BY on the tail of the raw hypertable, bounded by end_offset.
• sum + count storage pattern — every tier stores sum(value) and count(*), derives averages downstream. This preserves precision when aggregating aggregates and keeps the schema uniform across tiers.
• Cost — three continuous aggregates, three refresh policies, ~5-10% additional storage (each tier is small), ~2-5% CPU during refresh bursts. The recovered cost is the elimination of 20-second dashboard scans and the enablement of the sub-second alerting service. Net O(1) per query at any zoom level versus O(N × range) on raw.

Aggregation
Topic — aggregation
Aggregation problems on continuous-aggregate design

Practice →

SQL Topic — sql SQL problems on materialized-view refresh patterns

Practice →

---

4. Compression + retention

ALTER TABLE ... SET (timescaledb.compress) swaps row-oriented chunks for columnar-compressed chunks — 10-100× storage reduction and the single biggest cost win

The mental model in one line: timescale compression is a per-chunk transformation that takes a row-oriented Postgres heap and rewrites it into a columnar layout inside the same chunk, grouping identical segmentby values into arrays and delta-encoding orderby columns so that repeated time-series data compresses 10-100× — the extension ships a background add_compression_policy job that runs the transformation on any chunk older than a configurable age, and the retention policy on top of that drops chunks entirely once they exceed the retention horizon. Every senior TimescaleDB deployment lives or dies by the compression ratio; every "our TimescaleDB is expensive" complaint traces back to an untuned segmentby list.

The four things TimescaleDB compression actually does.

• Groups rows by segmentby. All rows in a chunk with the same segmentby column values (e.g. same device_id, metric) are grouped into one physical compressed row. Each column of the group becomes a compressed array.
• Orders columns by orderby. Within each segmentby group, columns are sorted by orderby (typically ts DESC). Sorted values compress dramatically better than unsorted ones (delta encoding, RLE, Gorilla for floats).
• Applies per-column encoding. Different data types get different encodings — timestamps use delta-of-delta, floats use Gorilla, strings use dictionary encoding. This is what lets identical-value columns hit 100× ratios.
• Preserves query semantics. Queries against the compressed chunk still return the same result as against the uncompressed one. The planner decompresses just-in-time; simple predicates on segmentby columns can prune whole compressed rows without decompressing.

The segmentby + orderby tuning knobs — the difference between 10× and 100×.

• segmentby. The columns whose values are grouped together. Pick columns that (a) have low cardinality per chunk and (b) are the ones your queries filter on. For a metrics(ts, device_id, metric, value) hypertable with 10 k devices and 20 metrics, segmentby = 'device_id, metric' is the right choice — 200 k unique combinations per day-chunk, each with ~432 identical rows compressed into one.
• orderby. The column(s) values within a segment are sorted by. Almost always ts DESC for time-series — sorted timestamps delta-encode near-perfectly. Adding a secondary sort (e.g. orderby = 'ts DESC, metric ASC') can help if queries filter on that secondary column.
• The math. Compression ratio = raw_rows / compressed_rows_per_segment. With 432 rows per segment (1 metric per device per minute × 24 h × 60), the theoretical ratio is 432×. Actual ratio depends on how well the value column itself compresses (Gorilla on floats: 10-20×). Combining segment grouping (400×) and Gorilla (10×) gets you to the 30-100× range most workloads observe.
• Anti-patterns. segmentby on a high-cardinality column (e.g. segmentby = 'ts') produces one compressed row per raw row — no compression. segmentby on the value column produces near-random grouping — no compression. Pick dimensions, not measures.

The compression policy — the "compress chunks older than N" background job.

• What it does. add_compression_policy('metrics', INTERVAL '24 hours') schedules a background job that runs periodically and calls compress_chunk(...) on any chunk whose time range is entirely older than 24 h.
• Why 24 h. Freshly-written chunks are read-write; compressing them prematurely blocks writes (compressed chunks are read-mostly — inserts require decompression). 24 h is the common threshold that ensures the chunk is "cold enough" that writes have moved on.
• Compressed chunks can still accept writes. As of TimescaleDB 2.11+, INSERTs into compressed chunks work; TimescaleDB stages them in a small uncompressed staging table and merges during background maintenance. Late-arriving data still lands correctly.
• Reversible. decompress_chunk(...) puts a chunk back into row-oriented storage. Rarely needed in production, but useful during backfill or schema evolution.

The retention policy — the "drop chunks older than N" background job.

• What it does. add_retention_policy('metrics', INTERVAL '90 days') schedules a background job that runs periodically and calls drop_chunks(...) on any chunk whose time range is entirely older than 90 days.
• Why chunks not rows. Dropping a chunk is O(1) — a single DROP TABLE. DELETE FROM metrics WHERE ts < now() - INTERVAL '90 days' on a 500 GB table is O(N) with full-table VACUUM afterward. The chunking is what makes retention affordable.
• Combines with compression. Compression policy at 24 h + retention policy at 90 days = chunks live compressed for 89 days, then get dropped. Storage curve stays flat.
• Continuous aggregates outlive raw. A common pattern is retention_policy('metrics', '90 days') but retention_policy('metrics_1d', '5 years') — you drop raw after 90 days but keep the daily rollup for 5 years. Storage is cheap at the daily grain.

Tiered storage on Timescale Cloud — the multi-year retention answer.

• What it is. Timescale Cloud's managed offering (formerly "bottomless") automatically tiers older chunks to S3-compatible object storage. Queries against tiered chunks are transparent — a small latency penalty, no code change.
• Why it matters. On-premises disk at TB scale is expensive; S3 at TB scale is cheap. Tiering combines TimescaleDB's compression (10-100× smaller before tiering) with S3 economics (~$0.023/GB/month) so multi-year retention costs pennies per gig.
• Trade-off. Tiered chunks have higher query latency (S3 GET vs local NVMe). Fine for compliance / audit queries; not for dashboards.
• Availability. Timescale Cloud-only in 2026; self-hosted equivalents (e.g. pg_backrest + S3) don't provide the same seamless query path.

Common interview probes on compression.

• "How much compression does TimescaleDB give you?" — required answer is "10-100× per chunk, driven by segmentby grouping + per-column encoding."
• "What is segmentby and how do you pick it?" — grouping column for identical values; pick low-cardinality dimensions your queries filter on.
• "Why compress after 24 h?" — hot writes moved on; compression makes the chunk read-mostly.
• "Can you write to compressed chunks?" — yes, as of 2.11+; late writes stage into an uncompressed sub-table and merge during maintenance.

Worked example — enabling compression with tuned segmentby / orderby

Detailed explanation. The canonical compression setup: ALTER TABLE to enable compression, choose segmentby based on your queries, choose orderby for time-series (ts DESC almost always), attach the compression policy, then verify the ratio you actually hit. Walk through the setup for the observability workload.

• Segmentby choice. device_id, metric — low cardinality per chunk, high grouping factor.
• Orderby choice. ts DESC — newest first for dashboard queries; delta-encoding for compression.
• Policy. Compress chunks older than 24 h.
• Expected ratio. 20-50× for this workload.

Question. Enable compression on metrics, tune segmentby and orderby, attach the policy, and measure the actual ratio achieved.

Input.

Parameter	Value
Table	metrics(ts, device_id, metric, value)
segmentby	device_id, metric
orderby	ts DESC
Policy trigger	chunk older than 24 h
Expected ratio	20-50×

Code.

-- 1. Enable compression with segmentby + orderby
ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id, metric',
    timescaledb.compress_orderby   = 'ts DESC'
);

-- 2. Attach the compression policy (chunks older than 24 h)
SELECT add_compression_policy('metrics', INTERVAL '24 hours');

-- 3. Manually compress an old chunk (for immediate verification)
SELECT compress_chunk(chunk_schema || '.' || chunk_name)
FROM   timescaledb_information.chunks
WHERE  hypertable_name = 'metrics'
  AND  range_end < now() - INTERVAL '24 hours'
  AND  NOT is_compressed
ORDER  BY range_start
LIMIT  1;

-- 4. Measure compression ratio per chunk
SELECT c.chunk_name,
       pg_size_pretty(uncompressed_total_bytes)   AS before,
       pg_size_pretty(compressed_total_bytes)     AS after,
       round(
         uncompressed_total_bytes::numeric
         / NULLIF(compressed_total_bytes, 0),
         1
       )                                          AS ratio
FROM   chunk_compression_stats('metrics') c
ORDER  BY chunk_name;

Step-by-step explanation.

1. Step 1 enables compression via ALTER TABLE with the two tuning knobs. segmentby = 'device_id, metric' says "group all rows for the same device+metric into one compressed row." orderby = 'ts DESC' says "sort columns within each group by timestamp descending" — this is what enables delta encoding on ts and RLE / Gorilla on value.
2. Step 2 attaches the compression policy. The background job scheduler runs every hour by default; the first pass compresses every chunk whose range_end is older than 24 h. Subsequent runs compress newly-eligible chunks as they age past 24 h.
3. Step 3 is optional but useful for immediate verification. compress_chunk(...) synchronously compresses one chunk — this lets you observe the ratio without waiting for the background policy.
4. Step 4 uses chunk_compression_stats to inspect the actual ratio. uncompressed_total_bytes is what the chunk would have cost as a row heap; compressed_total_bytes is what it actually costs after compression. The ratio column shows the win — expect 20-50× for a 200 k rows/s workload with (device_id, metric) segmentby.
5. Sanity-check via query performance: SELECT count(*) FROM metrics WHERE ts BETWEEN '<old range>' should return the same count against compressed and uncompressed chunks. Compression is transparent to queries — only faster (less disk I/O) and cheaper (less storage).

Output.

Chunk	Before (row heap)	After (compressed)	Ratio
_hyper_1_1_chunk	7.2 GB	210 MB	34.3
_hyper_1_2_chunk	7.1 GB	195 MB	36.4
_hyper_1_3_chunk	7.0 GB	205 MB	34.1
_hyper_1_4_chunk	7.3 GB	220 MB	33.2
_hyper_1_5_chunk	7.1 GB	200 MB	35.5
_hyper_1_6_chunk	7.0 GB	195 MB	35.9
_hyper_1_7_chunk	7.2 GB	215 MB	33.5

Rule of thumb. Every compressed hypertable needs segmentby and orderby set explicitly — the defaults compress poorly. Pick segmentby on low-cardinality dimensions your queries filter on; almost always pick orderby = 'ts DESC'. Measure the actual ratio with chunk_compression_stats; anything below 10× is a mis-tuned segmentby waiting to be diagnosed.

Worked example — the segmentby anti-pattern (why compression can hit 2× instead of 30×)

Detailed explanation. A team enables compression on their metrics hypertable but observes only 2× compression instead of the expected 30×. Investigation reveals they used segmentby = 'device_id, metric, ts' — including ts in segmentby means every row is its own segment (because ts is unique per row), so no grouping happens. Walk through the diagnosis and the fix.

• Symptom. Chunk compression ratio ~2× instead of expected ~30×.
• Root cause. segmentby includes the time column, so every raw row becomes its own compressed segment; no grouping.
• Fix. Remove ts from segmentby; move it to orderby.

Question. Diagnose the poor compression ratio and fix segmentby on the live hypertable without dropping data.

Input.

Component	Before	After
segmentby	device_id, metric, ts	device_id, metric
orderby	(none)	ts DESC
Compression ratio	~2×	~30×
Storage for 30 days	~100 GB	~7 GB

Code.

-- 1. Diagnose — inspect the current compression settings and ratio
SELECT hypertable_name,
       attname,
       segmentby_column_index,
       orderby_column_index
FROM   timescaledb_information.compression_settings
WHERE  hypertable_name = 'metrics'
ORDER  BY segmentby_column_index NULLS LAST, orderby_column_index;

SELECT c.chunk_name,
       pg_size_pretty(uncompressed_total_bytes) AS before,
       pg_size_pretty(compressed_total_bytes)   AS after,
       round(
         uncompressed_total_bytes::numeric
         / NULLIF(compressed_total_bytes, 0),
         1
       )                                        AS ratio
FROM   chunk_compression_stats('metrics') c
ORDER  BY ratio ASC
LIMIT  10;
-- Ratios of ~1.5-2.5 indicate segmentby too narrow / includes high-card column

-- 2. Fix — remove the compression settings and re-set correctly
--    (this requires decompressing existing chunks first)

-- 2a. Decompress every currently-compressed chunk
SELECT decompress_chunk(chunk_schema || '.' || chunk_name)
FROM   timescaledb_information.chunks
WHERE  hypertable_name = 'metrics'
  AND  is_compressed;

-- 2b. Reset compression settings
ALTER TABLE metrics RESET (
    timescaledb.compress_segmentby,
    timescaledb.compress_orderby
);

ALTER TABLE metrics SET (
    timescaledb.compress_segmentby = 'device_id, metric',
    timescaledb.compress_orderby   = 'ts DESC'
);

-- 2c. Re-compress chunks with the corrected settings
SELECT compress_chunk(chunk_schema || '.' || chunk_name)
FROM   timescaledb_information.chunks
WHERE  hypertable_name = 'metrics'
  AND  range_end < now() - INTERVAL '24 hours'
  AND  NOT is_compressed;

-- 3. Verify new ratio
SELECT c.chunk_name,
       pg_size_pretty(compressed_total_bytes)   AS size,
       round(
         uncompressed_total_bytes::numeric
         / NULLIF(compressed_total_bytes, 0),
         1
       )                                        AS ratio
FROM   chunk_compression_stats('metrics') c
ORDER  BY chunk_name DESC
LIMIT  5;

Step-by-step explanation.

1. Step 1 diagnoses via timescaledb_information.compression_settings — this catalog view lists which columns are in segmentby (positive segmentby_column_index) and which are in orderby (positive orderby_column_index). Seeing ts in segmentby is the smoking gun.
2. Step 2a decompresses every currently-compressed chunk. This is required before changing compression settings — TimescaleDB's on-disk layout is tied to the segmentby/orderby config, so a change requires a rewrite.
3. Step 2b resets the compression settings via ALTER TABLE ... RESET and re-sets them correctly. segmentby = 'device_id, metric' now groups rows by (device, metric); orderby = 'ts DESC' sorts them within the group.
4. Step 2c re-compresses eligible chunks with the corrected settings. This is O(compressed_chunks) — plan the migration off-peak.
5. Step 3 verifies the new ratio via chunk_compression_stats. Expected ratio jumps from ~2× to ~30×; storage on 30 days of chunks drops from ~100 GB to ~7 GB, a 14× recovery.

Output.

Metric	Before (mis-tuned)	After (fixed)
segmentby	device_id, metric, ts	device_id, metric
orderby	(none)	ts DESC
Compression ratio	~2×	~30×
Storage (30 days)	~100 GB	~7 GB
Query performance	poor (per-row decompress)	good (per-segment decompress)
Migration cost	O(compressed chunks) rewrite off-peak	one-time

Rule of thumb. Never put the time column in segmentby — it belongs in orderby. Never put the measurement column (value) in segmentby — it defeats grouping. Pick segmentby on dimensions (device, region, tenant, metric type); pick orderby on time. When compression ratio surprises you low, chunk_compression_stats is the first diagnostic — don't guess.

Worked example — retention policy + continuous-aggregate hierarchy

Detailed explanation. Retention isn't uniform across grains. Raw data at 200 k rows/s is expensive to keep forever; but rolled-up aggregates at daily grain cost pennies per year and are useful for capacity planning. The mature pattern is short retention on raw + long retention on aggregates. Walk through the multi-grain retention setup.

• Raw. 90-day retention.
• 1-minute aggregate. 180-day retention.
• 1-hour aggregate. 2-year retention.
• 1-day aggregate. 10-year retention.

Question. Attach retention policies to raw + three continuous-aggregate tiers such that storage stays bounded across all grains.

Input.

Table	Retention	Rationale
metrics (raw)	90 days	ingest firehose; expensive per row
metrics_1m	180 days	1-min grain; useful for post-hoc incident forensics
metrics_1h	2 years	1-h grain; capacity planning, quarterly reviews
metrics_1d	10 years	1-d grain; historical trend, cost reports

Code.

-- Raw hypertable — drop chunks entirely older than 90 days
SELECT add_retention_policy('metrics', INTERVAL '90 days');

-- Continuous aggregates — outlive the raw hypertable at coarser grains
SELECT add_retention_policy('metrics_1m', INTERVAL '180 days');
SELECT add_retention_policy('metrics_1h', INTERVAL '2 years');
SELECT add_retention_policy('metrics_1d', INTERVAL '10 years');

-- Verify all four policies
SELECT hypertable_name,
       drop_after
FROM   timescaledb_information.job_stats j
JOIN   timescaledb_information.jobs      USING (job_id)
WHERE  proc_name = 'policy_retention'
ORDER  BY hypertable_name;

-- Sanity check — how many chunks in each tier after policies stabilise?
SELECT hypertable_name,
       count(*)                                            AS chunk_count,
       pg_size_pretty(sum(total_bytes))                    AS total_size,
       min(range_start)                                    AS oldest,
       max(range_end)                                      AS newest
FROM   timescaledb_information.chunks
JOIN   chunks_detailed_size(hypertable_schema || '.' || hypertable_name)
       USING (chunk_name)
WHERE  hypertable_name IN ('metrics', 'metrics_1m', 'metrics_1h', 'metrics_1d')
GROUP  BY hypertable_name
ORDER  BY hypertable_name;

Step-by-step explanation.

1. Each add_retention_policy call schedules a background job that runs (by default) once per day. The job identifies chunks whose entire range is older than the retention interval and calls drop_chunks(...) on them.
2. drop_chunks is O(1) per chunk — it's a DROP TABLE on the chunk's underlying table. This is fundamentally cheaper than DELETE FROM ... WHERE ts < ... on a monolithic table, which is O(N) and requires a subsequent VACUUM to reclaim disk.
3. Retention on continuous aggregates operates on the materialization hypertable — the hidden hypertable that backs each aggregate. The policy syntax is identical (add_retention_policy('metrics_1m', ...)); TimescaleDB knows to target the backing table.
4. The 90-day / 180-day / 2-year / 10-year cascade is a common pattern. Raw is dropped first (expensive to keep); each higher aggregate outlives raw at coarser grain, so historical queries at low precision remain answerable for years.
5. Storage math: with 30× compression on raw + hierarchical aggregates, 90 days of raw + 180 days of 1-min + 2 years of 1-h + 10 years of 1-day totals ~20 GB for a 200 k rows/s workload. Compare to naive uncompressed raw at 10 years: ~54 TB. The reduction is genuine, not marketing.

Output.

Tier	Retention	Chunk count (steady state)	Total size
metrics (raw, compressed after 24 h)	90 days	~90	~15 GB
metrics_1m	180 days	~180	~1 GB
metrics_1h	2 years	~730	~500 MB
metrics_1d	10 years	~3 650	~500 MB
Total	—	~4 650	~17 GB

Rule of thumb. Retention isn't uniform. Drop raw fast (it's expensive); keep aggregates long (they're cheap). The multi-tier pattern combined with compression is what makes multi-year time-series retention on Postgres a solved problem, not a war story.

Senior interview question on compression + retention

A senior interviewer might ask: "Your TimescaleDB hypertable is consuming 800 GB and growing 10 GB/day. Retention is 'we'd like a year' but disk keeps filling up. Walk me through the compression tuning, retention policies, tiered-storage story, and the cascade across continuous-aggregate tiers that keeps this workload on-budget."

Solution Using tuned compression + 24-h policy + tiered retention + continuous-aggregate hierarchy

-- 1. Enable compression with the correct segmentby / orderby
ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id, metric',
    timescaledb.compress_orderby   = 'ts DESC'
);

-- 2. Compression policy — chunks older than 24 h are compressed
SELECT add_compression_policy('metrics', INTERVAL '24 hours');

-- 3. Retention policy — drop chunks entirely older than 90 days
SELECT add_retention_policy('metrics', INTERVAL '90 days');

-- 4. Continuous-aggregate tier retention — outlive raw at coarser grains
SELECT add_retention_policy('metrics_1m', INTERVAL '180 days');
SELECT add_retention_policy('metrics_1h', INTERVAL '2 years');
SELECT add_retention_policy('metrics_1d', INTERVAL '10 years');

-- Continuous aggregates themselves benefit from compression too
ALTER MATERIALIZED VIEW metrics_1m SET (
    timescaledb.compress = true,
    timescaledb.compress_segmentby = 'device_id, metric',
    timescaledb.compress_orderby   = 'bucket DESC'
);
SELECT add_compression_policy('metrics_1m', INTERVAL '7 days');

-- 5. Backfill: compress every eligible chunk right now (don't wait for policy)
SELECT compress_chunk(chunk_schema || '.' || chunk_name)
FROM   timescaledb_information.chunks
WHERE  hypertable_name = 'metrics'
  AND  range_end < now() - INTERVAL '24 hours'
  AND  NOT is_compressed
ORDER  BY range_start;

-- 6. Monitoring — compression ratio + chunk count over time
CREATE VIEW compression_report AS
SELECT hypertable_name,
       count(*)                                                                AS chunks,
       count(*) FILTER (WHERE is_compressed)                                   AS compressed_chunks,
       pg_size_pretty(sum(total_bytes))                                        AS total_size,
       round(
         sum(uncompressed_total_bytes)::numeric
         / NULLIF(sum(compressed_total_bytes), 0),
         1
       )                                                                       AS avg_ratio
FROM   timescaledb_information.chunks
LEFT   JOIN chunk_compression_stats(hypertable_schema || '.' || hypertable_name)
       USING (chunk_name)
WHERE  hypertable_name IN ('metrics', 'metrics_1m', 'metrics_1h', 'metrics_1d')
GROUP  BY hypertable_name;

# 7. Timescale Cloud — enable tiered storage to S3 for multi-year retention
# (Console-driven config; API also exposed)
tiered_storage:
  enabled:                true
  hypertable:             metrics
  tier_after:             INTERVAL '180 days'
  storage_class:          S3_INFREQUENT_ACCESS
  bucket:                 acme-metrics-tiered

Step-by-step trace.

Step	Action	Impact
Enable compression	segmentby + orderby	future compression ratio 30×
Compression policy	after 24 h	chunks older than 24 h auto-compress
Retention policy	90 days on raw	drop-chunks in O(1)
Aggregate retention	180 d / 2 y / 10 y	tiered retention across grains
Aggregate compression	after 7 d on metrics_1m	continuous aggregate itself compresses
Backfill compression	manual compress_chunk	recovers existing chunk space now
Tiered storage	after 180 d to S3	multi-year retention at S3 cost

After deployment, raw chunks older than 24 h drop from ~7 GB to ~200 MB each (30× ratio), the 90-day rolling window steady-states at ~15 GB (vs ~630 GB uncompressed), continuous aggregates add another ~2 GB total across the three tiers, and Timescale Cloud tiered storage moves chunks older than 180 days to S3 at ~$0.023/GB/month. Total on-primary storage stabilises at ~17 GB; multi-year history lives on S3 at ~$10/month.

Output:

Metric	Before	After
Raw storage (90 days)	~630 GB (uncompressed)	~15 GB (30× compression)
Continuous aggregate overhead	(not deployed)	~2 GB across 3 tiers
On-primary total	~800 GB	~17 GB
Multi-year cost (10 y history)	infeasible on primary	~$10/month on S3
Retention operation	manual DELETE (hours)	O(1) chunk drop
Ingest impact	0 (chunks are cold when compressed)	0

Why this works — concept by concept:

• Columnar compression per chunk — the fundamental storage win. Row-oriented Postgres storage becomes columnar arrays inside compressed rows. Grouping by segmentby (identical dimensions) + delta / RLE / Gorilla encoding on orderby-sorted columns delivers the 10-100× ratio.
• 24-h compression policy — the "chunk is cold enough to compress" heuristic. Hot writes into fresh chunks stay uncompressed; chunks older than 24 h are compressed by a background job. TimescaleDB 2.11+ still accepts inserts into compressed chunks (staged then merged), so the 24-h threshold rarely causes late-write friction.
• Tiered retention across grains — raw gets 90 days, aggregates get progressively longer (180 d, 2 y, 10 y). Storage cost per grain is 60× lower than the one above, so 10 years of daily aggregate is smaller than a week of raw. The multi-tier pattern is what makes multi-year retention affordable.
• Timescale Cloud tiered storage — chunks older than a threshold move to S3-compatible object storage transparently. Queries against tiered chunks work (with a latency penalty), so audit / compliance workloads remain answerable at S3 economics. On-premises equivalents don't provide the same seamless query path.
• Cost — one compression policy job, one retention policy job per grain, ~30× storage reduction on raw, ~$10/month for 10-year retention on S3. The eliminated cost is the "disk is filling up" incident, the multi-hour DELETE + VACUUM retention job, and the pressure to shrink retention below what the business needs. Net O(1) per chunk operation versus O(N) per row.

SQL
Topic — sql
SQL problems on storage optimisation and compression

Practice →

Design Topic — design Design problems on retention + tiered storage

Practice →

---

5. Hyperfunctions + when TimescaleDB wins

timescaledb_toolkit ships time_bucket, first/last, histogram, LTTB downsampling, gauge / counter aggregates — Postgres becomes a time-series specialist

The mental model in one line: hyperfunctions are the second half of what makes TimescaleDB a genuine time-series database — a library of aggregate and window functions (time_bucket, first(value, ts), last(value, ts), histogram(value, min, max, buckets), LTTB downsampling via lttb(...), gauge / counter accumulators like gauge_agg and counter_agg, streaming quantile estimation via percentile_agg / approx_percentile, and statistical rollups via stats_agg) that turn Postgres into a first-class query engine for time-series data instead of a general-purpose OLTP store that happens to have GROUP BY. Every senior TimescaleDB deployment leans on at least three or four of these; every "we could rewrite this on vanilla Postgres" claim collapses when someone asks for LTTB downsampling.

The core hyperfunctions every senior TimescaleDB user knows by heart.

• time_bucket(interval, ts). The workhorse GROUP BY function. time_bucket('1 minute', ts) rounds every ts down to its enclosing 1-minute bucket. Optional offset parameter aligns buckets to a wall-clock start (time_bucket('1 hour', ts, INTERVAL '30 minutes') gives half-past-the-hour buckets).
• first(value, ts) / last(value, ts). Ordered aggregates that pick the value at the minimum / maximum ts within the group. Not just any first row — the first chronologically. Essential for computing session start / end values, opening / closing tick prices, first-response times.
• histogram(value, min, max, num_buckets). Bucketed distribution. Returns an array of counts per equal-width bucket. Perfect for latency histograms without any downstream client-side binning.
• LTTB downsampling. lttb(ts, value, points) reduces a time-series to a specified number of points using the Largest-Triangle-Three-Buckets algorithm — preserves visual shape (peaks + valleys) far better than naive stride sampling. Essential for zoom-out dashboards that need to show ten years of data in 500 pixels.
• Gauge / counter aggregates. gauge_agg(value, ts) handles gauge-shaped data (arbitrary values); counter_agg(value, ts) handles monotonic counters (with automatic reset detection). Both expose accessors like rate(...), delta(...), interpolated_rate(...) for computing per-second rates from raw counter values.
• Streaming quantile estimation. percentile_agg(value) builds a T-Digest sketch; approx_percentile(0.95, sketch) returns the estimated p95. Approximate quantiles at streaming scale — you never store the sketch, and it's rollup-friendly (sketches merge).
• stats_agg(value). Streaming statistical rollup — count, sum, average, variance, stddev, skewness, kurtosis — all in one aggregate. Rollup-friendly: stats_agg of hourly rollups equals stats_agg of raw within precision bounds.

When TimescaleDB wins over vanilla Postgres partitioning.

• Automatic chunk creation. Native PG requires manual CREATE TABLE partition_20260727 PARTITION OF metrics ... per day. TimescaleDB creates chunks on insert.
• Chunk-local everything. Indexes, compression, statistics — all per-chunk. Native PG partitions inherit indexes but not the automation.
• Columnar compression built in. Native PG has no columnar compression story out of the box (pg_columnar exists but is not core).
• Continuous aggregates. Native PG's MATERIALIZED VIEW is full-refresh only; no incremental refresh, no real-time union.
• Hyperfunctions. Every one of the functions above is TimescaleDB-only. Vanilla PG has PERCENTILE_CONT (exact but slow) and window functions (powerful but generic); it does not have time_bucket, first, last, LTTB, or T-Digest.

When TimescaleDB wins over InfluxDB.

• SQL. TimescaleDB is Postgres SQL. InfluxDB is Flux (or SQL over DataFusion in 3.0 — genuinely competitive now, but a learning curve).
• JOINs. TimescaleDB JOINs the hypertable to your device inventory, tenant table, or metrics catalogue in the same query. InfluxDB does JOINs but they're second-class.
• Ecosystem. Every Postgres tool (pgbouncer, PgBackrest, Patroni, DataGrip, dbt, PostgREST, PostGIS) works against TimescaleDB. InfluxDB has its own tooling universe.
• ACID transactions. TimescaleDB inherits Postgres MVCC — you can transact across metrics and non-metrics tables. InfluxDB does not offer transactional writes across measurements.
• InfluxDB wins on. Raw ingest ceiling (~1 M rows/s single-node vs TimescaleDB's ~250 k), edge-friendly single-binary deployment, and native down-sampling / retention policies without a policy library.

When TimescaleDB wins over QuestDB.

• Postgres surface. Same argument as InfluxDB — SQL, JOINs, ecosystem. QuestDB has SQL but a smaller ecosystem.
• Multi-tenant / write-heavy transactional workloads. Postgres MVCC handles concurrent writers, updates to old rows, foreign keys against non-hypertable tables. QuestDB is more append-only in shape.
• QuestDB wins on. Single-node ingest speed, minimal footprint, and specific high-frequency-trading benchmarks where microsecond latencies matter.

When TimescaleDB wins over ClickHouse.

• OLTP + analytics hybrid. TimescaleDB runs OLTP writes and OLAP reads on the same Postgres cluster. ClickHouse is analytics-only; a typical stack pairs it with Postgres for OLTP.
• JOINs at reasonable cardinality. TimescaleDB's Postgres JOIN engine handles dimensional joins well. ClickHouse's JOIN support is limited (though dictGet covers common lookups).
• Ecosystem breadth. Postgres tooling is bigger.
• ClickHouse wins on. Very-large-scan analytics (billions of rows), high compression on wide tables, and workloads where columnar OLAP is the primary pattern.

Common interview probes on hyperfunctions and comparisons.

• "Name three TimescaleDB-only functions." — time_bucket, first(value, ts), LTTB (or any hyperfunction).
• "How would you compute p95 latency over a 5-minute rolling window?" — percentile_agg(latency) + approx_percentile(0.95, ...) grouped by time_bucket('5 minutes', ts).
• "Why not use vanilla Postgres partitioning?" — no compression, no continuous aggregates, no hyperfunctions, manual chunk creation.
• "When would you pick InfluxDB over TimescaleDB?" — >1M rows/s single-node ingest, edge deployments, no need for JOINs to Postgres tables.
• "When would you pick ClickHouse?" — analytics-primary workloads where scans dominate and OLTP is elsewhere.

Worked example — computing p95 latency with percentile_agg + approx_percentile

Detailed explanation. The canonical hyperfunction use case: computing streaming p95 (or any quantile) over a time-bucketed window using T-Digest sketches, then rolling those sketches up into hourly / daily grains without re-scanning raw. Walk through the query and the continuous-aggregate pattern.

• Metric. latency_ms on a requests(ts, endpoint, latency_ms) hypertable.
• Grain. 1-minute buckets in the finest tier.
• Method. percentile_agg(latency_ms) builds the T-Digest; approx_percentile(0.95, sketch) extracts p95.
• Rollup. T-Digest sketches merge, so hourly rollup calls rollup(sketch) over the 1-minute sketches.

Question. Compute p95 latency per minute per endpoint, roll it up hourly, and materialise both grains.

Input.

Component	Value
Source hypertable	requests(ts, endpoint, latency_ms)
Sketch aggregate	percentile_agg(latency_ms)
Quantile extract	approx_percentile(0.95, sketch)
1-min continuous aggregate	latency_1m stores the sketch
1-h continuous aggregate	latency_1h rolls up the sketch

Code.

-- 1. Direct query — p95 latency per minute per endpoint over the last hour
SELECT time_bucket('1 minute', ts)                                    AS bucket,
       endpoint,
       approx_percentile(0.95, percentile_agg(latency_ms))             AS p95_ms,
       approx_percentile(0.50, percentile_agg(latency_ms))             AS p50_ms,
       count(*)                                                        AS n
FROM   requests
WHERE  ts > now() - INTERVAL '1 hour'
GROUP  BY bucket, endpoint
ORDER  BY bucket, endpoint;

-- 2. Continuous aggregate storing the sketch (not the p95)
--    -- sketches are rollup-friendly; p95s of p95s are not
CREATE MATERIALIZED VIEW latency_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts)     AS bucket,
       endpoint,
       percentile_agg(latency_ms)       AS sketch,
       count(*)                         AS n
FROM   requests
GROUP  BY bucket, endpoint;

SELECT add_continuous_aggregate_policy('latency_1m',
    start_offset      => INTERVAL '2 hours',
    end_offset        => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '5 minutes');

-- 3. Hourly aggregate — rolls up sketches, not raw
CREATE MATERIALIZED VIEW latency_1h
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', bucket)    AS bucket,
       endpoint,
       rollup(sketch)                    AS sketch,
       sum(n)                            AS n
FROM   latency_1m
GROUP  BY 1, 2;

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

-- 4. Query — extract p95 from the hourly sketch
SELECT bucket,
       endpoint,
       approx_percentile(0.95, sketch) AS p95_ms,
       approx_percentile(0.50, sketch) AS p50_ms,
       n
FROM   latency_1h
WHERE  bucket > now() - INTERVAL '7 days'
  AND  endpoint = '/api/v1/orders'
ORDER  BY bucket;

Step-by-step explanation.

1. Step 1 uses percentile_agg (a T-Digest sketch aggregate) plus approx_percentile(0.95, sketch) to extract p95. This is streaming — no per-row median, no expensive PERCENTILE_CONT. On 200 k rows/s over a 60-second bucket (~12 M rows), the T-Digest sketch computes in ~200 ms with ~1% quantile error.
2. Step 2 materialises the sketch itself (not the extracted p95) in the continuous aggregate. This is the load-bearing decision — storing the sketch lets you extract any quantile later (p50, p95, p99, p99.9) and lets downstream tiers roll it up.
3. Step 3 rolls up 1-minute sketches into 1-hour sketches using rollup(sketch). This preserves quantile accuracy across grains because T-Digest sketches merge losslessly. Storing p95 at 1-minute grain and averaging it would be wrong (percentiles don't average); storing sketches and rolling them up is correct.
4. Step 4 extracts p95 (and p50) from the hourly sketch. The query scans a small pre-materialised table (one row per hour per endpoint) and returns quantiles at essentially free cost — no raw-hypertable scan.
5. The equivalent in vanilla Postgres would be PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY latency_ms) — exact but O(N log N) per bucket, unrollupable, and unusable at high volume. The T-Digest approach is 100× cheaper and rollup-friendly.

Output.

Bucket	endpoint	p95_ms	p50_ms	n
2026-07-27 09:00	/api/v1/orders	187	42	720 000
2026-07-27 10:00	/api/v1/orders	195	45	745 000
2026-07-27 11:00	/api/v1/orders	210	48	780 000
2026-07-27 12:00	/api/v1/orders	245	55	812 000
2026-07-27 13:00	/api/v1/orders	220	51	795 000
2026-07-27 14:00	/api/v1/orders	180	43	720 000

Rule of thumb. Store sketches (T-Digest, gauge_agg, counter_agg, stats_agg) in continuous aggregates, not the derived scalar. Sketches are rollup-friendly; scalars are not. Extract the scalar (p95, average, rate) at query time. This is the pattern that lets a 1-second time_bucket roll up correctly to a 1-day grain years later.

Worked example — LTTB downsampling for zoom-out dashboards

Detailed explanation. A dashboard shows 10 years of daily-aggregated CPU data in a 500-pixel-wide chart. Naive stride sampling (every 10th point) drops peaks and valleys and misleads viewers. LTTB (Largest-Triangle-Three-Buckets) is a downsampling algorithm that preserves visual shape by picking the point in each bucket that forms the largest triangle with adjacent bucket picks. TimescaleDB's lttb(ts, value, points) implements it. Walk through the query.

• Input. 3 650 daily points (10 years of daily aggregate).
• Output. 500 downsampled points that preserve visual shape.
• Method. lttb(bucket, avg_value, 500) returns an ordered array of (ts, value) pairs.

Question. Compute a 500-point LTTB downsample of 10 years of daily CPU averages for a device.

Input.

Component	Value
Source	metrics_1d (10 years, ~3 650 rows per device per metric)
Downsample function	lttb(bucket, avg_value, 500)
Chart width	500 px
Preserved features	peaks, valleys, trends

Code.

-- 1. LTTB downsample — returns SET OF (time, value) rows via unnest
WITH downsampled AS (
    SELECT unnest(
             lttb(bucket, sum_value / n, 500)
           )                                                AS point
    FROM   metrics_1d
    WHERE  device_id = 'device-42'
      AND  metric    = 'cpu_pct'
      AND  bucket    > now() - INTERVAL '10 years'
)
SELECT (point).time  AS ts,
       (point).value AS avg_cpu
FROM   downsampled
ORDER  BY ts;

-- 2. Compare to naive stride sampling (every Nth row) — visually inferior
--    (drops peaks/valleys; only good for uniform-density series)
WITH numbered AS (
    SELECT bucket,
           sum_value / n AS avg_cpu,
           row_number() OVER (ORDER BY bucket) AS rn,
           count(*)      OVER () AS total
    FROM   metrics_1d
    WHERE  device_id = 'device-42' AND metric = 'cpu_pct'
)
SELECT bucket, avg_cpu
FROM   numbered
WHERE  rn % greatest(total / 500, 1) = 0
ORDER  BY bucket;

Step-by-step explanation.

1. Step 1 uses lttb(bucket, sum_value / n, 500) — the LTTB function takes the time column, the value column, and the target number of output points. It returns a SET OF timevector_point which is then unnested into (time, value) rows.
2. LTTB works by partitioning the input into N equal-time buckets, then for each bucket picking the point that forms the largest triangle with the adjacent bucket picks. This preserves peaks and valleys — an LTTB downsample "looks like" the original curve even at 100× fewer points.
3. The naive alternative (step 2) picks every Nth row via row_number() OVER () % stride = 0. This drops peaks and valleys because they don't necessarily land on the stride's grid. For dashboards that need to show real behaviour, this is misleading.
4. Storage / compute: LTTB is O(N) — a single pass over the input. On a 3 650-row input it's essentially free. The bottleneck is the raw scan of metrics_1d, which itself is small at daily grain (~3 650 rows per device per metric).
5. Downstream: the 500 points are the chart's data series. The chart library renders them; the viewer sees a smooth curve that faithfully represents the 10-year history. This is a genuine differentiator over InfluxDB (which requires client-side LTTB) or vanilla Postgres (which requires a UDF).

Output.

ts	avg_cpu (LTTB)
2016-07-27	34.2
2016-08-04	39.8 (local peak preserved)
2016-08-13	22.1 (local valley preserved)
…	…
2026-07-13	51.7
2026-07-20	76.3 (recent spike preserved)
2026-07-27	48.9

Rule of thumb. For any zoom-out chart wider than the underlying data's density, use lttb(...) — not stride sampling. LTTB preserves visual shape; stride sampling drops it. If your dashboard renders 10 years of data in 500 pixels, LTTB is why the peaks and valleys still show up.

Worked example — counter_agg for computing per-second rates from monotonic counters

Detailed explanation. Prometheus-shaped counters (bytes sent, requests handled, errors) are monotonically increasing until they reset (service restart). Computing the rate of a counter requires handling resets correctly — a naive (last - first) / duration is wrong when a reset lands inside the window. counter_agg(value, ts) builds a counter-shaped aggregate that handles resets; rate(counter_agg) returns per-second rates correctly. Walk through the pattern.

• Metric. requests_total on counters(ts, service, requests_total).
• Aggregate. counter_agg(requests_total, ts) per 1-minute bucket.
• Rate. rate(counter_agg) returns requests-per-second.
• Reset handling. Automatic — counter_agg detects value_t2 < value_t1 and subtracts appropriately.

Question. Compute per-second request rate per minute per service, correctly handling counter resets.

Input.

Component	Value
Source hypertable	counters(ts, service, requests_total)
Aggregate	counter_agg(requests_total, ts)
Rate extract	rate(counter_agg)
Reset detection	automatic (value drops)

Code.

-- 1. Per-minute request rate — handles counter resets automatically
SELECT time_bucket('1 minute', ts)             AS bucket,
       service,
       rate(counter_agg(requests_total, ts))   AS req_per_sec,
       delta(counter_agg(requests_total, ts))  AS total_requests_in_bucket,
       num_resets(counter_agg(requests_total, ts)) AS resets_in_bucket
FROM   counters
WHERE  ts > now() - INTERVAL '1 hour'
GROUP  BY bucket, service
ORDER  BY bucket, service;

-- 2. Continuous aggregate storing the counter_agg — rollup-friendly
CREATE MATERIALIZED VIEW rates_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts)      AS bucket,
       service,
       counter_agg(requests_total, ts)   AS counter
FROM   counters
GROUP  BY bucket, service;

SELECT add_continuous_aggregate_policy('rates_1m',
    start_offset      => INTERVAL '2 hours',
    end_offset        => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '5 minutes');

-- 3. Hourly rollup — merges counter_agg values via rollup(counter)
CREATE MATERIALIZED VIEW rates_1h
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', bucket)     AS bucket,
       service,
       rollup(counter)                     AS counter
FROM   rates_1m
GROUP  BY 1, 2;

-- 4. Query rate from either grain
SELECT bucket,
       service,
       rate(counter) AS req_per_sec
FROM   rates_1h
WHERE  bucket > now() - INTERVAL '7 days'
  AND  service = 'orders-api'
ORDER  BY bucket;

Step-by-step explanation.

1. Step 1 uses counter_agg(requests_total, ts) to build a counter-shaped aggregate per bucket. rate(...) extracts the per-second rate; delta(...) extracts the total change; num_resets(...) reports how many counter resets happened in the bucket.
2. Reset detection is automatic. If the raw counter goes 100 → 200 → 50 → 150, counter_agg detects the drop at the third sample as a reset, treats the delta as (50 - 0) + (150 - 50) = 150, and returns a rate that reflects the true request count, not a negative anomaly.
3. Step 2 materialises counter_agg in a continuous aggregate. This is the load-bearing choice — storing the aggregate (not the extracted rate) lets downstream tiers roll it up correctly.
4. Step 3 rolls up 1-minute counter_agg values into 1-hour ones via rollup(counter). Counter aggregates merge across time (each merge preserves reset history), so hourly / daily rollups are accurate.
5. Vanilla Postgres would compute this with a window function: LAG(requests_total) OVER (PARTITION BY service ORDER BY ts) plus custom logic to detect resets — verbose, error-prone, non-rollupable. counter_agg collapses this into one function call.

Output.

bucket	service	req_per_sec	resets
2026-07-27 09:00	orders-api	1 250	0
2026-07-27 09:01	orders-api	1 340	0
2026-07-27 09:02	orders-api	1 180	1 (pod restart)
2026-07-27 09:03	orders-api	1 275	0
2026-07-27 09:04	orders-api	1 310	0

Rule of thumb. For any monotonic counter (bytes sent, requests handled, errors, ticks), use counter_agg + rate(...) — not manual LAG() window arithmetic. Reset detection is automatic; rollups merge losslessly; the pattern scales from 1-minute to 1-year grains without accuracy loss.

Senior interview question on hyperfunctions + the 4-way comparison

A senior interviewer might ask: "You have three workloads on the table: (1) Kubernetes pod CPU metrics ingested at 200 k rows/s; (2) HTTP request logs ingested at 500 k events/s where p95 latency dashboards matter; (3) IoT sensor readings from 100 k devices where 10-year cost-of-storage matters. Walk me through which database (TimescaleDB / InfluxDB / QuestDB / ClickHouse) you'd pick per workload, why, and what hyperfunctions you'd lean on."

Solution Using workload-driven database selection + hyperfunction fluency + comparison-matrix framing

-- Workload 1 — Kubernetes pod CPU metrics @ 200 k rows/s
-- Pick: TimescaleDB
-- Why:  we already run Postgres; SQL surface is a huge win;
--       ingest ceiling comfortably above 200 k rows/s;
--       hyperfunctions cover the dashboard queries.

CREATE TABLE pod_metrics (
    ts          TIMESTAMPTZ NOT NULL,
    pod_id      TEXT        NOT NULL,
    metric      TEXT        NOT NULL,
    value       DOUBLE PRECISION
);
SELECT create_hypertable('pod_metrics', 'ts',
    chunk_time_interval => INTERVAL '1 day');
ALTER  TABLE pod_metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'pod_id, metric',
    timescaledb.compress_orderby   = 'ts DESC');
SELECT add_compression_policy('pod_metrics', INTERVAL '24 hours');
SELECT add_retention_policy  ('pod_metrics', INTERVAL '90 days');

-- Continuous aggregate + hyperfunction for the "top 10 hottest pods" panel
CREATE MATERIALIZED VIEW pod_metrics_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts) AS bucket,
       pod_id,
       metric,
       avg(value)                    AS avg_v,
       max(value)                    AS max_v,
       percentile_agg(value)         AS sketch
FROM   pod_metrics
GROUP  BY bucket, pod_id, metric;

-- Workload 2 — HTTP request logs @ 500 k events/s, p95 dashboards
-- Pick: TimescaleDB IF the ingest ceiling holds; otherwise InfluxDB 3.0
-- Why:  500 k / s is at the upper end of single-node TimescaleDB;
--       percentile_agg + T-Digest sketches make p95 dashboards trivial;
--       if we need to go multi-node, InfluxDB 3.0 is a stronger horizontal story.

-- (assuming TimescaleDB is chosen)
CREATE TABLE http_requests (
    ts          TIMESTAMPTZ NOT NULL,
    endpoint    TEXT        NOT NULL,
    latency_ms  DOUBLE PRECISION,
    status      INT
);
SELECT create_hypertable('http_requests', 'ts',
    chunk_time_interval => INTERVAL '12 hours');   -- shorter chunks for higher throughput

CREATE MATERIALIZED VIEW latency_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts)                       AS bucket,
       endpoint,
       percentile_agg(latency_ms)                         AS sketch,
       count(*) FILTER (WHERE status >= 500)              AS errors
FROM   http_requests
GROUP  BY bucket, endpoint;

-- Query — p95 per endpoint over the last hour
SELECT bucket, endpoint,
       approx_percentile(0.95, sketch) AS p95,
       errors
FROM   latency_1m
WHERE  bucket > now() - INTERVAL '1 hour'
ORDER  BY bucket, endpoint;

-- Workload 3 — 100 k IoT devices, 10-year retention, cost-sensitive
-- Pick: TimescaleDB with tiered storage (Timescale Cloud) OR ClickHouse
-- Why:  compression + hierarchical continuous aggregates + tiered storage
--       drop the storage bill to pennies per year; ClickHouse wins if the
--       workload is analytics-only (no OLTP writes) and multi-billion-row
--       scans dominate.

-- (assuming TimescaleDB Cloud with tiered storage)
CREATE TABLE iot_readings (
    ts          TIMESTAMPTZ NOT NULL,
    device_id   TEXT        NOT NULL,
    sensor      TEXT        NOT NULL,
    reading     DOUBLE PRECISION
);
SELECT create_hypertable('iot_readings', 'ts',
    chunk_time_interval => INTERVAL '1 day');
ALTER  TABLE iot_readings SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id, sensor',
    timescaledb.compress_orderby   = 'ts DESC');

SELECT add_compression_policy('iot_readings', INTERVAL '24 hours');
SELECT add_retention_policy  ('iot_readings', INTERVAL '180 days');

-- Yearly aggregate lives 10 years
CREATE MATERIALIZED VIEW iot_1d
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', ts)        AS bucket,
       device_id, sensor,
       avg(reading)                     AS avg_r,
       max(reading)                     AS max_r,
       min(reading)                     AS min_r,
       count(*)                         AS n
FROM   iot_readings
GROUP  BY bucket, device_id, sensor;
SELECT add_retention_policy('iot_1d', INTERVAL '10 years');

Step-by-step trace.

Workload	Ingest	Choice	Rationale
K8s pod metrics	200 k rows/s	TimescaleDB	already Postgres; SQL + JOINs; hyperfunctions
HTTP request logs	500 k events/s	TimescaleDB (12-h chunks) or InfluxDB 3.0	at the ingest ceiling; sketches for p95
IoT 10-year retention	30 k rows/s	TimescaleDB + tiered storage	compression + hierarchical aggregates make it cheap
Pure OLAP analytics	(any)	ClickHouse	scan-heavy, no OLTP, columnar-first workload
Edge / single-binary	(any)	InfluxDB	single-binary, edge-friendly, no Postgres dependency
Ultra-high ingest HFT	> 1 M/s	QuestDB or ClickHouse	benchmark-tuned single-node ingest

After the design decisions, each workload gets a database that matches its constraints. Two of the three land on TimescaleDB because the Postgres SQL surface + JOIN capability + hyperfunctions cover the shape well; the third (HTTP request logs at 500 k events/s) is the marginal case where InfluxDB 3.0 competes seriously. The stack ends up with one Postgres cluster running TimescaleDB (two workloads) and possibly InfluxDB for the third — vastly less operational surface than four different specialised stores.

Output:

Workload	Winner	Runner-up	Key hyperfunctions
K8s pod metrics	TimescaleDB	ClickHouse	time_bucket, percentile_agg
HTTP request logs	TimescaleDB	InfluxDB 3.0	percentile_agg, approx_percentile
IoT 10-year retention	TimescaleDB + tiered storage	ClickHouse	time_bucket, LTTB, stats_agg
Pure OLAP	ClickHouse	TimescaleDB	(ClickHouse dictGet, arrays)
Edge / single-binary	InfluxDB	QuestDB	Flux math, downsampling
>1M ingest HFT	QuestDB	ClickHouse	SIMD ingest, minimal binary

Why this works — concept by concept:

• Workload-driven database selection — the 4-axis comparison (SQL surface × ingest ceiling × compression × ecosystem) applied per workload. Never let "we already use X" be the sole reason; let the constraints pick the database.
• TimescaleDB's ecosystem win — two of three workloads land on TimescaleDB because SQL, JOINs, foreign keys, and full Postgres tooling are worth the ingest trade-off. The operational surface shrinks; the team's Postgres skill compounds.
• Hyperfunctions as competitive moat — percentile_agg, counter_agg, time_bucket, lttb, stats_agg. Every one of them removes a class of downstream client-side computation. Dashboards get simpler; correctness improves; the SQL becomes readable.
• Tiered storage for multi-year retention — Timescale Cloud tiered storage moves cold chunks to S3 seamlessly. 10-year retention on IoT at 30 k rows/s costs pennies per year at S3 economics; on-premises disk would be infeasible.
• Cost — one Postgres cluster (TimescaleDB extension free / Apache-licensed), optional Timescale Cloud for managed + tiered storage, ~5-15% CPU overhead vs vanilla Postgres for the same workload. The eliminated cost is running three different specialised time-series stores side-by-side, each with its own operational surface. Net O(1) additional systems versus O(N) — one Postgres cluster is cheaper to operate than three.

Window Functions
Topic — window-functions
Window function problems on time-series analytics

Practice →

Design
Topic — design
Design problems on picking time-series databases

Practice →

---

Cheat sheet — TimescaleDB recipes

• Hypertable creation template. CREATE EXTENSION IF NOT EXISTS timescaledb; then SELECT create_hypertable('metrics', 'ts', chunk_time_interval => INTERVAL '1 day'); with an explicit chunk interval sized so one recent chunk plus its indexes fits comfortably in 25% of shared_buffers — (peak_ingest_bytes_per_second × 86400 × chunk_days) ≤ 0.25 × shared_buffers. Add the composite index that matches your common query shape (CREATE INDEX ... ON metrics (device_id, ts DESC)). Reset chunk width at any time with SELECT set_chunk_time_interval('metrics', INTERVAL '1 day') — future chunks respect the new width; existing chunks require a manual split via re-insert + DROP TABLE chunk.
• Continuous aggregate template. CREATE MATERIALIZED VIEW metrics_1m WITH (timescaledb.continuous) AS SELECT time_bucket('1 minute', ts) AS bucket, device_id, metric, sum(value) AS sum_v, count(*) AS n FROM metrics GROUP BY bucket, device_id, metric WITH NO DATA; then attach SELECT add_continuous_aggregate_policy('metrics_1m', start_offset => INTERVAL '2 hours', end_offset => INTERVAL '5 minutes', schedule_interval => INTERVAL '5 minutes'); and backfill history once with CALL refresh_continuous_aggregate('metrics_1m', now() - INTERVAL '7 days', now() - INTERVAL '5 minutes');. Store sum + count (not avg) so downstream tiers can weighted-average correctly.
• Hierarchical continuous aggregate pattern. Build metrics_1m from raw, metrics_1h from metrics_1m (not from raw), metrics_1d from metrics_1h. Each tier reads ~60× less data than the one below; refresh cost drops multiplicatively. Refresh cadences from finest to coarsest, each waiting long enough for its upstream to have caught up. Downstream tiers keep the sum/count columns so weighted averages remain correct across grains.
• Real-time materialization. ON by default (timescaledb.materialized_only = false). Every query unions the pre-materialised buckets with a live GROUP BY on raw for buckets inside end_offset. Sub-second freshness at the cost of a small live scan on the tail. Turn OFF only for pure batch reports where staleness by the refresh cadence is explicitly acceptable — ALTER MATERIALIZED VIEW metrics_1m SET (timescaledb.materialized_only = true);.
• Compression template — segmentby / orderby. ALTER TABLE metrics SET (timescaledb.compress, timescaledb.compress_segmentby = 'device_id, metric', timescaledb.compress_orderby = 'ts DESC'); then SELECT add_compression_policy('metrics', INTERVAL '24 hours');. segmentby = low-cardinality dimensions your queries filter on (device, metric, tenant); orderby = time column, almost always ts DESC. Never put ts in segmentby (breaks grouping). Expected ratio: 10-100×; measure with chunk_compression_stats('metrics').
• Retention policy template. SELECT add_retention_policy('metrics', INTERVAL '90 days'); drops chunks entirely older than 90 days via drop_chunks — O(1) per chunk versus O(N) for a naive DELETE. Cascade retention across grains: raw at 90 days, metrics_1m at 180 days, metrics_1h at 2 years, metrics_1d at 10 years. Storage stays bounded across all tiers because each higher grain is ~60× smaller than the one below.
• Tiered storage on Timescale Cloud. Enable via the Cloud console or API — chunks older than a threshold move to S3-compatible object storage transparently. Queries against tiered chunks work with a latency penalty (S3 GET vs local NVMe). 10-year retention at S3 economics costs pennies per gig per year. On-premises equivalents (pg_backrest + custom logic) don't provide the same seamless query path.
• Hyperfunctions cheat sheet. time_bucket('1 hour', ts) for GROUP BY; first(value, ts) / last(value, ts) for chronologically-ordered picks; histogram(value, min, max, buckets) for distributions; lttb(ts, value, points) for zoom-out downsampling that preserves peaks/valleys; counter_agg(value, ts) + rate(...) for monotonic counter rates with automatic reset detection; gauge_agg(value, ts) for gauge-shaped data; percentile_agg(value) + approx_percentile(0.95, sketch) for streaming quantiles; stats_agg(value) for count/sum/avg/variance/stddev in one call.
• Store sketches, not scalars. In continuous aggregates, materialise the aggregate (T-Digest sketch, counter_agg, gauge_agg, stats_agg) — not the extracted scalar (p95, rate, average). Sketches roll up losslessly across grains via rollup(sketch); scalars don't (percentiles of percentiles are wrong). Extract the scalar at query time. This is the single most important continuous-aggregate design rule for time-series workloads.
• Query planner chunk exclusion. Any WHERE predicate on the time column with a constant or stable expression prunes chunks at plan time. WHERE ts > now() - INTERVAL '1 hour' prunes correctly. WHERE ts > some_function(other_col) does not. Verify with EXPLAIN (ANALYZE, BUFFERS) — look for Chunks excluded during planning: N. Complex JOINs where the time predicate lives on a joined table can defeat pruning; push the predicate down to the hypertable directly.
• When TimescaleDB wins. Postgres already in the stack; SQL surface matters; JOINs to non-hypertable tables matter; ingest ≤ 250 k rows/s single-node; retention 1-10 years; compression + continuous aggregates + hyperfunctions cover the workload. When it loses: > 1 M rows/s single-node ingest (InfluxDB / ClickHouse / QuestDB win); pure OLAP scan-heavy workloads (ClickHouse); edge single-binary deployments (InfluxDB).
• Chunk-time-interval tuning rule. (peak_ingest_bytes_per_second × 86400 × chunk_days) ≤ 0.25 × shared_buffers. For 200 k rows/s × 400 bytes/row × 32 GB shared_buffers, daily chunks are correct. Weekly chunks at this rate hit 48 GB each and thrash the page cache. Change the width at any time with set_chunk_time_interval; new chunks respect the new width but existing chunks require manual split-migration.
• Slot / disk-full defenses (Timescale-adjacent). Enable max_wal_size proportional to your ingest burst tolerance. Monitor pg_stat_bgwriter and pg_stat_wal alongside chunks_detailed_size for storage growth. Retention policies + compression policies + tiered storage should keep on-primary storage flat; if it isn't, the compression ratio is under-tuned (check chunk_compression_stats) or the retention cadence is off.
• Migration from vanilla Postgres to TimescaleDB. CREATE EXTENSION timescaledb; (superuser); then SELECT create_hypertable('metrics', 'ts', migrate_data => true, chunk_time_interval => INTERVAL '1 day'); — migrate_data moves existing rows into chunks in place. No table drop, no downtime beyond the migration transaction. Add compression + retention + continuous aggregates afterward. Budget ~1 engineer-week for the migration + tuning; the payoff is 5-10× ingest headroom and 20-100× dashboard scan speedup.
• Migration from InfluxDB to TimescaleDB. Export via Influx influxd inspect export-lp or Flux to() to Parquet; import via COPY into a fresh hypertable. Rewrite Flux queries to Postgres SQL — time_bucket replaces aggregateWindow, first/last replace their Flux counterparts, percentile_agg replaces quantile. Budget ~2-4 engineer-weeks per pipeline; the payoff is the Postgres ecosystem + JOIN capability.

Frequently asked questions

What is TimescaleDB in one sentence?

TimescaleDB is a Postgres extension that turns a stock Postgres cluster into a full time-series database — automatically partitioning tables by time via hypertables, materialising rollup queries via continuous aggregates with real-time union of raw data for freshest buckets, compressing older chunks columnarly for 10-100× storage reduction via ALTER TABLE ... SET (timescaledb.compress, ...) tuned by segmentby and orderby, dropping expired chunks in O(1) via retention policies, and shipping a library of timescaledb_toolkit hyperfunctions (time_bucket, first/last, histogram, LTTB, counter_agg, percentile_agg, stats_agg) that turn Postgres into a first-class time-series query engine without asking the team to abandon SQL, JOINs, foreign keys, or the two decades of Postgres tooling they already know. Every senior data-engineering interview probes TimescaleDB when the topic is time-series storage because it's the single Postgres-native answer that competes with InfluxDB, QuestDB, and ClickHouse on ingest, compression, and query pattern.

Hypertables vs native Postgres partitioning — what's actually different?

Both hypertables and native Postgres declarative partitioning break a large table into smaller child tables keyed by a range column, and both let the planner prune child tables before opening them. The differences are all operational polish: hypertables create chunks automatically on insert (native PG requires manual CREATE TABLE partition_YYYYMMDD PARTITION OF metrics ... per interval); hypertables support per-chunk columnar compression via timescaledb.compress (native PG has no columnar compression story out of the box); hypertables support continuous aggregates with incremental refresh + real-time union (native PG's MATERIALIZED VIEW is full-refresh only); hypertables support retention + reorder + compression policies via add_*_policy(...) (native PG requires cron + custom scripts); and hypertables integrate with the timescaledb_toolkit hyperfunctions (native PG has none). If your data is time-shaped and volume matters, TimescaleDB chunking is a clear upgrade. If the table is small and rarely-updated, plain PG partitioning is fine.

What is a continuous aggregate and how does refresh work?

A timescaledb continuous aggregate is a Postgres MATERIALIZED VIEW over a time_bucket(...) GROUP BY on a hypertable, defined with the WITH (timescaledb.continuous) clause, backed by its own hidden hypertable that stores the pre-computed buckets (inheriting all hypertable benefits — chunking, compression, retention), and refreshed on a schedule via add_continuous_aggregate_policy(name, start_offset, end_offset, schedule_interval). Refresh works by re-running the underlying aggregate over the rolling window [now - start_offset, now - end_offset] and upserting the resulting buckets into the materialization hypertable — the operation is idempotent (re-running yields the same result) and missed refreshes catch up automatically. Real-time materialization (ON by default) makes queries against the aggregate union the pre-materialised buckets with a live GROUP BY on the raw hypertable for buckets inside end_offset, giving dashboards sub-second freshness without waiting for the next refresh. The pattern scales via hierarchical continuous aggregates: metrics_1m reads from raw, metrics_1h reads from metrics_1m (not raw), metrics_1d reads from metrics_1h — each tier refreshes on ~60× less data than the one below, storage cost drops multiplicatively, and dashboards scan the smallest table that covers their zoom level.

How does TimescaleDB compression compare to columnar warehouses?

TimescaleDB compression is columnar per chunk — inside each chunk, rows with the same segmentby column values are grouped into a single physical row, columns within each group are sorted by orderby, and per-column encodings (delta-of-delta on timestamps, RLE on repeating strings, Gorilla on floats) drive the 10-100× ratio. It is not a full columnar store like ClickHouse's MergeTree — TimescaleDB remains a row-first Postgres, and compressed chunks decompress just-in-time for query. That trade-off is deliberate: you get 30× storage reduction without giving up Postgres's SQL surface, MVCC transactions, JOINs, or foreign keys. Compared to Snowflake / BigQuery / Redshift columnar warehouses, TimescaleDB doesn't win on billion-row analytical scans (columnar warehouses do); it wins on combined OLTP + time-series workloads where the same cluster serves live application writes and historical dashboards. Compared to ClickHouse specifically, TimescaleDB gives up some scan throughput (ClickHouse's columnar-first design wins on very-wide, very-selective scans) but gains full Postgres SQL, JOINs to arbitrary reference tables, and the ability to run OLTP writes to the same cluster without a second database.

TimescaleDB vs InfluxDB, QuestDB, ClickHouse — when do I pick each?

Pick TimescaleDB when your team already runs Postgres, when JOINs to non-time-series tables matter (device inventory, tenant catalogue, metric registry), when ingest sits at ≤ 250 k rows/s single-node, when hyperfunctions cover your query patterns, and when SQL surface + ecosystem tooling matter more than raw ingest throughput. Pick InfluxDB when ingest exceeds 1 M rows/s single-node and JOINs don't matter, when edge single-binary deployments are required, when Flux (or InfluxQL) is acceptable, and when the workload is measurement-only with no OLTP writes. Pick QuestDB when microsecond-latency single-node ingest is the primary constraint (high-frequency trading, ultra-low-latency benchmarks), when the schema is stable and append-only, and when the smaller ecosystem is acceptable. Pick ClickHouse when the workload is analytics-primary (billion-row scans, wide-table aggregations), when OLTP writes happen on a different cluster, when the schema fits column-first storage well, and when the team is willing to invest in ClickHouse's specific SQL dialect and cluster operations. The 2026 default answer for most data-engineering teams — running Postgres, needing time-series + OLTP together, valuing SQL over dialect learning — is TimescaleDB; each alternative earns its place by breaking one of those defaults hard.

Is TimescaleDB production-ready in 2026?

Yes — TimescaleDB has been production-hardened for over a decade (since 2017), with major deployments at IBM, Warner Music, Bloomberg, Kubernetes-native monitoring stacks, and thousands of self-hosted installations. The core is Apache-licensed and free; commercial features (multi-node, some enterprise operational tooling) sit behind the Timescale License; and the managed service (Timescale Cloud, which includes the tiered-storage story that moves cold chunks to S3-compatible object storage) is the production default for teams that don't want to run Postgres themselves. The 2.x series (mature as of 2023, with continuous improvements through 2026) added significant reliability and feature work: real-time materialization on continuous aggregates, INSERTs into compressed chunks (2.11+), improved chunk-exclusion planning, better hyperfunction rollup semantics, and the tiered-storage bottomless offering. The one caveat: multi-node TimescaleDB (horizontal scale-out across primaries) is a smaller, more specialised deployment shape — most production workloads are single-primary with read replicas, and single-primary tops out around 250 k rows/s. Above that, the honest recommendation is to compare seriously against InfluxDB 3.0, ClickHouse, or a sharded architecture — TimescaleDB's sweet spot is the vast middle of the market, not the extreme tail.

Practice on PipeCode

• Drill the SQL practice library → for the time-series, hypertable, and continuous-aggregate query problems senior interviewers love.
• Sharpen aggregation reflexes on the aggregation practice library → for the time_bucket, rolling-window, and multi-grain rollup patterns.
• Rehearse the window-function axis with the window-functions practice library → for the counter-rate, running-total, and per-partition ordering shapes.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the four-axis TimescaleDB decision matrix against real graded inputs.

Lock in timescaledb muscle memory

Docs explain hypertables. PipeCode drills explain the decision — when `chunk_time_interval` collapses your ingest, when a mis-tuned `segmentby` costs you a 30× compression win, when a `metrics_1m` continuous aggregate saves a 20-second dashboard scan, when `percentile_agg` sketches rescue you from unrollupable percentiles. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice aggregation problems →