TimescaleDB Interview Questions: Hypertables, Chunks, and Continuous Aggregates

A TimescaleDB dashboard can be fast and still show the wrong historical answer. A late sensor reading lands in an old chunk, but the continuous aggregate refresh policy never revisits that hour. Adding an index will not repair the result.

TimescaleDB interview questions become easier when you separate physical storage from answer freshness. A hypertable partitions raw rows; a continuous aggregate maintains a derived result under an explicit refresh contract. This guide connects both decisions through original chunk-sizing and late-write exercises. SQL fragments assume the named hypertable already exists and are practice examples, not benchmark results or a tested deployment.

Official Tiger Data documentation, checked in October 2026, supports the mechanisms below; extension versions and deployment settings still need confirmation in an actual interview environment.

What is a hypertable, and why is it not just an index?

A hypertable presents a PostgreSQL table interface while automatically organizing data into chunks, usually by time and optionally another dimension. A time predicate can restrict the relevant chunks. An index serves a different purpose inside the selected storage. These features complement each other rather than replacing one another. See Tiger Data’s hypertable explanation.

For a telemetry example, assume observed_at, device_id, and temperature. Ask whether the dominant query is one device’s last hour, a fleet-wide daily report, or a historical export. A design that works well for the first access pattern may have a different cost for the third.

Consider a query restricted to one day and one device. The time range helps identify relevant chunks; an appropriate device-and-time access path can then reduce work within them. If the query asks for every historical reading from one device without a time bound, the hypertable cannot magically turn the request into a single recent chunk.

In an interview, explain the expected plan before saying “add an index.” Name the predicate, expected rows, and chunks accessed. Then inspect a plan and representative timings in the actual environment. No documentation example establishes performance for your workload, hardware, or data distribution.

How do you choose a chunk interval?

Start with the active write pattern, not a memorized interval. Relevant inputs include ingest rate, index growth, late writes that reopen older periods, number of active hypertables, query ranges, and available memory. Smaller intervals create more chunks; larger intervals can enlarge the active working set.

Tiger Data updated its chunk-sizing guidance in TimescaleDB 2.21: the approximate 25% memory reference concerns indexes on uncompressed chunks, not total chunk data size. It is a starting point for measurement, not a universal optimum. See the official 2.21 announcement’s sizing correction.

Use this synthetic calculation. The instance has 32 GiB of memory. New chunk indexes grow by 0.5 GiB per day for the telemetry hypertable. A seven-day interval creates approximately 3.5 GiB of index growth per full chunk. If writes touch the current chunk and the preceding chunk, the combined estimate approaches 7 GiB before considering other tables and caches.

A fourteen-day interval would produce approximately 7 GiB per full chunk under the same simplified growth rate. Two active chunks could approach 14 GiB. This does not prove seven days is optimal; it shows why “make chunks bigger to reduce count” needs a working-set check.

Candidate choice	Useful argument	Evidence still needed
Shorter time interval	Smaller individual active indexes	Planning cost and number of chunks scanned
Longer time interval	Fewer chunks for broad time scans	Active index memory and write behavior
Add a device index	Improve selective device access	Selectivity, write overhead, actual plan
Change several settings together	Potentially alter many costs	Cannot attribute the improvement without controlled comparisons

Ask what “active” means. If devices backfill data from several weeks ago, more than two chunks may receive writes. Estimate that workload explicitly rather than assuming every insert belongs to today. Also distinguish a memory budget from a physical disk-size limit: the full database need not fit in RAM.

What does a continuous aggregate maintain?

A continuous aggregate stores a time-bucketed result and refreshes relevant ranges instead of requiring every dashboard query to recompute all raw history. Its definition and its refresh policy are separate choices. The continuous aggregate creation guide documents the materialized-view approach and supported configuration.

For an existing readings hypertable, an illustrative hourly definition is:

CREATE MATERIALIZED VIEW temperature_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket(INTERVAL '1 hour', observed_at) AS bucket,
       device_id,
       COUNT(temperature) AS sample_count,
       SUM(temperature) AS temperature_sum,
       AVG(temperature) AS average_temperature
FROM readings
GROUP BY bucket, device_id
WITH NO DATA;

Explain the grain: one row per device per hour. The count covers non-null temperatures, matching the average's denominator. WITH NO DATA also means the definition alone has not populated historical results. Confirm the installed version, supported aggregate functions, timestamp type, and setup requirements before treating this fragment as a runnable migration.

Why keep count and sum alongside average? Suppose one hour has two readings averaging 30, and another has eight averaging 40. Averaging the hourly averages gives 35; the combined sample-weighted average is 38. A dashboard rollup should preserve the statistic’s meaning, not merely reuse a convenient column.

That is an interview distinction about aggregation grain. If the intended metric is an equal-weighted average of hourly means, 35 may be correct—but it answers a different question. State the denominator and weighting before writing the rollup query.

Which late writes does a refresh policy cover?

Refresh offsets describe the window relative to policy execution; cadence describes how often it runs. Only complete buckets wholly inside the specified range are refreshed. Those are different controls. See refresh policy and manual refresh documentation.

For a practice fixture, use a three-day lookback, a one-hour end offset, and a fifteen-minute schedule:

SELECT add_continuous_aggregate_policy(
  'temperature_hourly',
  start_offset => INTERVAL '3 days',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '15 minutes'
);

Assume the job runs at 12:00 UTC. Its nominal range begins three days earlier at 12:00 and ends today at 11:00. A completed hourly bucket from yesterday lies inside that window. A bucket from five days earlier does not. Data in the current 12:00–13:00 bucket is outside the materialized range.

Now insert a late value of 90 into yesterday’s 08:00 hour. The old readings were 20 and 40, so the correct count becomes three, sum becomes 150, and average becomes 50 instead of 30. The raw query can show the new row immediately; the materialized answer needs a successful refresh covering that bucket.

Insert the same late value into a five-day-old hour. Running the recent-data policy more often will not include that older bucket. The fix needs an explicitly bounded historical refresh or another deliberately configured refresh strategy—not a claim that fifteen-minute cadence means all historical corrections become visible within fifteen minutes.

An example bounded repair for one historical hour is:

CALL refresh_continuous_aggregate(
  'temperature_hourly',
  TIMESTAMPTZ '2026-09-26 08:00:00+00',
  TIMESTAMPTZ '2026-09-26 09:00:00+00'
);

This is a database-changing operation for a controlled lab or approved runbook, not something to run blindly against production. Before refreshing, prove which buckets are incorrect and that the required raw data still exists. Verify the repaired count and sum against a raw reference query.

Do real-time aggregates fix every freshness problem?

No. Real-time aggregation adds the recent raw tail to materialized results; it does not automatically repair late writes inside already materialized historical buckets. TimescaleDB 2.13 and later disable real-time aggregation by default, so inspect the actual setting rather than assuming it is enabled. See real-time aggregates.

The interview trap is a dashboard that includes current readings correctly while yesterday’s count remains wrong. Those symptoms are compatible. The recent raw tail is fresh, but the historical bucket still needs refresh. “The chart updates live” is not evidence that all time ranges are equally current.

For the example above, define three checks: current partial-hour visibility, yesterday’s repaired average of 50, and the five-day-old bucket’s explicitly repaired value. A single latest-timestamp check tests only one part of the contract.

Also distinguish a view definition from an operational promise. A job may be delayed, fail, or take longer than the schedule interval. A defensible freshness expectation needs execution history and a data comparison, not merely a configured cadence.

How should raw retention and aggregate history interact?

If a refresh revisits a range whose raw data has been removed, it recalculates using the remaining source data; that can remove the corresponding historical aggregate. The refresh documentation’s retention warning makes this interaction explicit.

Suppose the product needs a year of hourly summaries but only thirty days of raw readings. Write that as a lifecycle contract. Materialize and verify summaries before raw deletion, keep routine refresh away from already dropped history, and define how legitimate older corrections can be reconstructed from an authoritative archive.

Avoid an open-ended repair simply because it is easy to type. Refreshing all history after raw retention has run can destroy the very historical summaries the product intended to preserve. A bounded repair, with a before-and-after comparison and a recovery source, is easier to defend.

The important question is not “can we store aggregates longer?” It is “what evidence can rebuild or correct them after raw data disappears?” If no source remains, disclose the correction limit rather than promising arbitrary historical backfills.

Diagnose a fast but incorrect dashboard

Use a reproducible discrepancy: one device, one completed hour, expected count three, displayed count two. Confirm timestamps and bucket boundaries first. A timezone mismatch can make two correct queries appear to disagree because they are measuring different intervals.

Then compare raw count and sum with the aggregate row. Inspect the definition’s grain, refresh coverage, real-time setting, and job outcome. If the wrong bucket is outside the policy, document that specific cause before changing the schedule. If raw data was deleted, stop and identify a reconstruction source before refreshing.

For a slow query, take a different branch: inspect selected chunks, predicates, index usage, representative cardinality, and resource pressure. Correctness evidence and performance evidence should not be mixed. A faster wrong result does not close the incident.

In a final answer, name a measurable acceptance criterion: the selected historical fixture matches the raw reference, the latest partial bucket follows the intended real-time setting, and observed job execution meets the freshness budget. State explicitly that these checks must be run in the target database.

Sources and Further Reading

• Understand hypertables.
• TimescaleDB 2.21: updated chunk-sizing guidance.
• Create a continuous aggregate.
• Refresh continuous aggregates.
• Real-time aggregates.