TimescaleDB Explained: Architecture, Features and Use Cases

Short answer

TimescaleDB is a PostgreSQL extension: tables, SQL, indexes, joins, roles and drivers are ordinary PostgreSQL, so time-series readings can sit next to asset and work-order tables in one database.
A hypertable looks like one table but is split automatically into chunks, each covering a time range (7 days by default for timestamp columns); queries with a time filter skip chunks outside the range.
Hypercore converts older chunks from the rowstore to a compressed columnstore; continuous aggregates keep roll-ups up to date incrementally; retention policies drop whole chunks by age.
Two editions ship from one repository: the Apache 2 Edition (Apache License 2.0) and the Community Edition under the Timescale License (TSL), which the editions page now calls the Tiger Data License. Columnstore, continuous aggregates, retention policies and jobs are Community Edition features.
Our editorial view: plain PostgreSQL with declarative partitions and roll-up tables is often enough for building-scale data; TimescaleDB is worth considering when you want the partitioning, compression and roll-up maintenance done for you.

How we know: Research-based from Tiger Data's TimescaleDB documentation, the timescaledb GitHub repository (licence file and release 2.31.0) and tigerdata.com, checked 27 October 2026. The plain PostgreSQL example on this page was run on PostgreSQL 18.6 (PGlite 0.5.11). TimescaleDB-specific SQL (hypertables, columnstore, continuous aggregates, policies) is adapted from the documentation, was not run here and is labelled as illustrative. The licence section quotes the Timescale License Agreement and the Apache License 2.0 texts in the repository and the editions page; it is a summary, not legal advice. No performance figures are given except where attributed to the vendor. Lifecycle periods and volumes in the examples are illustrative assumptions; Tiger Cloud pricing is described qualitatively, without figures.

What TimescaleDB is, and who this is for

TimescaleDB is an extension that you load into PostgreSQL. Once it is installed, a table created with the hypertable option is partitioned by time behind the scenes, while your application keeps using INSERT, SELECT, UPDATE and DELETE as before. Tiger Data's documentation describes hypertables as PostgreSQL tables that automatically partition time-series data by time and optionally by other dimensions.

The company behind it was called Timescale and now trades as Tiger Data (the site footer reads "Timescale, Inc., d/b/a Tiger Data"). The extension is still called TimescaleDB; the managed service is Tiger Cloud and the documentation lives under tigerdata.com. On 27 October 2026 the latest release on GitHub was 2.31.0, published on 20 October 2026, and the installation guide uses PostgreSQL 18 in its examples.

This guide is for developers and data engineers who already know some PostgreSQL and are deciding whether to add TimescaleDB for sensor, meter or event data. For the general concepts of time-series storage, start with the time-series database overview.

Architecture: hypertables and chunks

A hypertable is the logical table you query. A chunk is a physical partition: an ordinary PostgreSQL table holding the rows for one time range, and optionally one slice of a second "space" dimension. When a row arrives for a time range that has no chunk yet, TimescaleDB creates the chunk. When a query filters on time, the planner excludes chunks that cannot match, so a "last 24 hours" query on a year of data touches only the newest chunks.

The documentation lists the defaults and the main tuning rule:

The chunk interval defaults to 7 days for timestamp columns and can be changed with set_chunk_time_interval(); a change applies only to chunks created afterwards.
Size the interval so that the indexes of the chunks currently receiving writes fit in about 25% of main memory (shared_buffers). Too many small chunks slow query planning and compress less well.
A time-descending index is created automatically; further indexes, including unique ones, are built per chunk.
Table inheritance is not supported on hypertables.

Because chunks are separate tables, lifecycle work happens per chunk: converting a chunk to the columnstore, dropping it for retention, or (on Tiger Cloud) moving it to object storage. That is the same idea as dropping a monthly partition in plain PostgreSQL, but automated.

Illustrative DDL, not run here: a hypertable for BMS point readings, adapted from the TimescaleDB documentation; check every setting against the documentation for your version before use

CREATE TABLE bms_reading (
    ts        TIMESTAMPTZ      NOT NULL,
    point_id  INTEGER          NOT NULL,   -- references the asset/point register
    value     DOUBLE PRECISION NOT NULL
) WITH (
    tsdb.hypertable,
    tsdb.segmentby = 'point_id',
    tsdb.orderby   = 'ts DESC'
);

A building-management example

Take a portfolio of buildings whose BMS exposes AHU supply-air temperatures, chiller kW, zone CO2 and electricity meters. A gateway reads the points every 1 to 60 seconds and publishes them to an MQTT broker; an ingestion service subscribes, maps each BMS point name to a stable point_id from the asset register, batches the readings and writes them to the hypertable above with multi-row INSERT or COPY, which the documentation recommends for high-volume ingestion.

Because TimescaleDB is PostgreSQL, the same database can hold the asset, point and CMMS work_order tables, and one query can join an hourly chiller roll-up to the open work orders for that chiller. The streaming layer (MQTT or Kafka) moves and buffers readings, TimescaleDB stores and serves them, and a warehouse or lakehouse can still take periodic extracts for portfolio-wide analytics. Tiger Cloud documents a built-in export to Apache Iceberg for that last step.

One possible lifecycle for this data: keep raw readings in the rowstore for about a week, convert older chunks to the columnstore, maintain 15-minute and hourly roll-ups as continuous aggregates, drop raw chunks after a fixed period and keep the roll-ups for years. The periods here and in the next sections (a week in the rowstore, a 7-day refresh window, 30 days of raw data, five years of roll-ups) are illustrative assumptions for the example; choose yours from how late data can arrive, how far back raw readings are needed for fault finding, reporting and compliance requirements, and your storage budget. The next three sections show each piece.

Compression with hypercore and the columnstore

Hypercore is TimescaleDB's hybrid row-columnar engine. New rows land in the rowstore, which suits inserts, updates and late-arriving data. As chunks age, a columnstore policy converts them to the columnstore, where values are grouped by the segmentby column, ordered by the orderby column and compressed. Columnstore chunks still accept INSERT, UPDATE, DELETE and upserts without a manual decompress step.

When a hypertable is created with segmentby and orderby, the documentation says a columnstore policy is created automatically and compresses chunks after one chunk interval. Choose segmentby from the column you filter on most (here point_id); lower-cardinality segment columns compress better. The vendor, Tiger Data, reports compression of 90 to 98% on its documentation pages (not reproduced here); your ratio depends on the data, so measure it on your own chunks with hypertable_columnstore_stats() rather than planning storage on the vendor figure.

Illustrative query, not run here: check columnstore savings, adapted from the TimescaleDB documentation; check it against the documentation for your version before use

SELECT pg_size_pretty(before_compression_total_bytes) AS before,
       pg_size_pretty(after_compression_total_bytes)  AS after
FROM hypertable_columnstore_stats('bms_reading');

Continuous aggregates

A continuous aggregate is a materialized view defined with WITH (timescaledb.continuous). Unlike a plain PostgreSQL materialized view, which is recomputed in full on each refresh, it is refreshed incrementally in the background for the time range a policy specifies, and it is itself stored as a hypertable. The view must group by a time_bucket on the hypertable's time column, and the functions it uses must be immutable.

Real-time aggregation, which adds not-yet-materialized raw rows to query results, is disabled by default from TimescaleDB 2.13. Aggregates can be stacked: an hourly view on the raw table, then a daily view on the hourly one. The interplay with query performance is that dashboards read a small roll-up instead of scanning raw readings.

Illustrative configuration, not run here: an hourly roll-up with a refresh policy, adapted from the TimescaleDB documentation; the offsets and schedule are illustrative values; check every setting against the documentation for your version before use

CREATE MATERIALIZED VIEW bms_reading_hourly
WITH (timescaledb.continuous) AS
SELECT point_id,
       time_bucket(INTERVAL '1 hour', ts) AS bucket,
       avg(value) AS avg_value,
       min(value) AS min_value,
       max(value) AS max_value
FROM bms_reading
GROUP BY point_id, bucket;

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

Retention policies and downsampling

TimescaleDB retention works on chunks, not rows. add_retention_policy() schedules a background job that drops every chunk whose entire time range is older than the interval; a chunk that still contains newer rows is kept until it ages out completely. Dropping a chunk removes its files, which avoids the bloat and vacuum work of a large DELETE.

Combined with continuous aggregates this gives downsampling: raw readings expire while the roll-ups stay. The documentation warns that the order matters. If raw chunks are dropped inside the aggregate's refresh window, the next refresh sees the deletion and removes the aggregated rows too. Keep raw retention longer than the policy's start_offset (30 days of raw data against a 7-day refresh window in the example below), and set a separate, longer retention on the aggregate itself if needed.

Illustrative configuration, not run here: drop raw chunks after 30 days and roll-ups after five years (illustrative periods), adapted from the TimescaleDB documentation; check every setting against the documentation for your version before use

SELECT add_retention_policy('bms_reading', INTERVAL '30 days');
SELECT add_retention_policy('bms_reading_hourly', INTERVAL '5 years');

-- See the scheduled retention jobs
SELECT j.hypertable_name, j.job_id, j.schedule_interval, js.last_run_status
FROM timescaledb_information.jobs j
JOIN timescaledb_information.job_stats js ON j.job_id = js.job_id
WHERE j.proc_name = 'policy_retention';

Editions and licensing: Apache 2 vs Community (TSL)

The timescaledb repository is split by licence. Code outside the tsl directory is under the Apache License 2.0; code inside it is under the Timescale License, which the editions page now calls the Tiger Data License (TSL). The build produces separate binaries, and the ones with -tsl in their name carry the TSL. That gives two editions:

TimescaleDB Apache 2 Edition: hypertables, chunks, drop_chunks, tablespaces and size functions, under the Apache License 2.0. Section 2 of that licence grants "a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display, publicly perform, sublicense, and distribute the Work", subject to the licence's conditions.
TimescaleDB Community Edition (TSL): everything above plus the columnstore, continuous aggregates, retention policies, the job scheduler, SkipScan and chunk reorder or move functions, under the Timescale License Agreement (posted 24 September 2020). The agreement says the TSL Licensed Software "may be used under this TSL Agreement without charge"; its section 2.1 grants, among other licences, an internal-use licence "solely for Your own internal business purposes" that does not expose the data definition or data manipulation interfaces to others, and a licence for "Value Added Products or Services" with conditions; section 2.2 prohibits "using any TSL Licensed Software to provide time-sharing services or database-as-a-service services". The editions page summarises this as "You cannot sell TimescaleDB Community Edition as a service, even if you are the main contributor."

The editions page states that the Community Edition is the default. To see what a server is running, use SHOW timescaledb.license;, which returns timescale (Community features enabled) or apache (Apache 2-only mode). This matters on third-party managed PostgreSQL services: check the setting before you design around continuous aggregates or the columnstore.

Not legal advice. This section quotes the licence texts and the vendor's editions page; it does not say whether a particular use is permitted. Read the Timescale License Agreement and the Apache License for yourself and take advice for your situation, especially if customers or other third parties can reach the database.

Illustrative command, not run here: check the installed edition, from the TimescaleDB documentation; check it against the documentation for your version before use

SHOW timescaledb.license;

Selected features by edition (from the Tiger Data editions page, checked 27 October 2026)
Feature	Apache 2 Edition	Community Edition (TSL)
Hypertables, chunks, `create_hypertable`, `drop_chunks`	Yes	Yes
Hypercore columnstore and columnstore policies	No	Yes
Continuous aggregates and refresh policies	No	Yes
Retention policies (`add_retention_policy`)	No	Yes
Job scheduler (`add_job`, `alter_job`)	No	Yes
Offering the database to others as a service	Governed by the Apache License 2.0 terms	Restricted by TSL section 2.2 (see the quote above)

Self-hosted TimescaleDB vs Tiger Cloud

Self-hosted. You install the extension into your own PostgreSQL (packages for Linux, Homebrew, a Windows installer and Docker images are documented), add it to shared_preload_libraries, and run CREATE EXTENSION timescaledb. Backups, high availability, upgrades and monitoring are your responsibility, using the usual PostgreSQL tools; the PostgreSQL in Docker guide covers the container basics. Tiger Data also lists a commercially supported self-managed product, TimescaleDB Enterprise, for on-premises, edge and private cloud use.

Tiger Cloud. The managed service runs TimescaleDB on PostgreSQL on AWS and Azure. Tiger Data handles backups, PostgreSQL and TimescaleDB upgrades and patches, and adds features that self-hosting does not have, such as tiered storage that moves older chunks to object storage while keeping them queryable, high-availability and read replicas, built-in connectors for S3, Kafka and PostgreSQL, and export to Apache Iceberg.

Pricing. On 27 October 2026 the pricing page listed Performance, Scale and Enterprise plans with separate compute and storage charges, hourly billing and a 30-day free trial. Figures are shown per region and configuration on that page, so we do not reproduce them here; check it for your region.

When plain PostgreSQL is enough

Plain PostgreSQL can do most of what the BMS example needs, with more of the work in your hands: create range partitions ahead of time, keep roll-ups in materialized views or summary tables, and drop old partitions on a schedule. The script below ran on PostgreSQL 18.6. It also shows the gap continuous aggregates close: a plain materialized view is recomputed in full, so after the raw partition for 26 October is dropped, the next refresh removes that day's hourly rows as well. To keep history you would maintain an append-only summary table yourself.

Plain PostgreSQL is worth staying on when volumes are modest (as an illustrative scale, readings per minute or slower for thousands rather than millions of points), retention is months rather than years, and the team prefers no extension to install and upgrade; where the limit lies depends on your hardware, queries and retention, so test with your own data. Consider TimescaleDB when you would otherwise write your own partition management, incremental roll-ups and compression. The TimescaleDB vs PostgreSQL comparison goes through the trade-off feature by feature, and How to Store IoT Sensor Data has a complete plain PostgreSQL design.

Plain PostgreSQL: partitions, a materialized roll-up and partition-drop retention (run on PostgreSQL 18.6)

CREATE TABLE ahu_reading (
    point_id  integer          NOT NULL,
    ts        timestamptz      NOT NULL,
    value     double precision NOT NULL,
    PRIMARY KEY (point_id, ts)
) PARTITION BY RANGE (ts);

CREATE TABLE ahu_reading_20261026 PARTITION OF ahu_reading
    FOR VALUES FROM ('2026-10-26 00:00+00') TO ('2026-10-27 00:00+00');
CREATE TABLE ahu_reading_20261027 PARTITION OF ahu_reading
    FOR VALUES FROM ('2026-10-27 00:00+00') TO ('2026-10-28 00:00+00');

-- Two supply-air temperature points, one reading a minute for two days
INSERT INTO ahu_reading (point_id, ts, value)
SELECT p, t, 18 + p + (extract(minute FROM t) / 30.0)
FROM generate_series(1, 2) AS p,
     generate_series(timestamptz '2026-10-26 00:00+00',
                     timestamptz '2026-10-27 23:59+00',
                     interval '1 minute') AS t;

-- Hourly roll-up kept as an ordinary materialized view
CREATE MATERIALIZED VIEW ahu_reading_hourly AS
SELECT point_id,
       date_bin(interval '1 hour', ts, timestamptz '2000-01-01 00:00+00') AS bucket,
       round(avg(value)::numeric, 2) AS avg_value,
       round(min(value)::numeric, 2) AS min_value,
       round(max(value)::numeric, 2) AS max_value
FROM ahu_reading
GROUP BY 1, 2;

SELECT * FROM ahu_reading_hourly ORDER BY point_id, bucket LIMIT 3;

-- Retention: remove a whole day by dropping its partition
DROP TABLE ahu_reading_20261026;
SELECT count(*) AS raw_rows_left FROM ahu_reading;

-- The view still holds 26 October until the next full refresh
SELECT count(*) AS hourly_rows_26_oct FROM ahu_reading_hourly WHERE bucket < '2026-10-27 00:00+00';
REFRESH MATERIALIZED VIEW ahu_reading_hourly;
SELECT count(*) AS hourly_rows_26_oct FROM ahu_reading_hourly WHERE bucket < '2026-10-27 00:00+00';

Results on PostgreSQL 18.6 (PGlite)

 point_id |         bucket         | avg_value | min_value | max_value
----------+------------------------+-----------+-----------+-----------
        1 | 2026-10-26 00:00:00+00 |     19.98 |     19.00 |     20.97
        1 | 2026-10-26 01:00:00+00 |     19.98 |     19.00 |     20.97
        1 | 2026-10-26 02:00:00+00 |     19.98 |     19.00 |     20.97

raw_rows_left            2880
hourly_rows_26_oct       48   (before the refresh)
hourly_rows_26_oct       0    (after the refresh)

Limitations and when not to use TimescaleDB

Small, slow data. Our editorial view: at an illustrative few hundred meters read every 15 minutes, the table stays small, and the extension adds an upgrade path to manage alongside PostgreSQL for limited gain.
Managed PostgreSQL without the Community Edition. If your provider only offers the Apache 2 Edition, the columnstore, continuous aggregates and retention policies are not available; plan for plain PostgreSQL techniques or Tiger Cloud.
Offering the database as a service. Section 2.2 of the TSL prohibits using TSL Licensed Software "to provide time-sharing services or database-as-a-service services"; if your product exposes the database to customers, read the licence section above and take advice.
Scale beyond one primary. Self-hosted TimescaleDB is PostgreSQL: one writable primary with replicas. Very large write volumes may call for a distributed engine; see the guide to choosing a time-series database.
Non-SQL ecosystems. If your collectors and dashboards are built around InfluxDB line protocol or PromQL, a native engine may fit better; compare in TimescaleDB vs InfluxDB.

Security and operational considerations

Security is PostgreSQL security: roles and grants per table, TLS on connections, and a write-only role for the ingestion service. Keep BMS networks segmented from the database network.
Upgrades involve two versions: PostgreSQL and the extension. Run ALTER EXTENSION timescaledb UPDATE after installing a new package, as described in the self-hosted upgrade documentation.
Backups: use PostgreSQL tools such as pg_dump and physical backups; follow Tiger Data's backup notes for hypertables.
Monitoring: watch chunk count and size (chunks_detailed_size()), job failures in timescaledb_information.job_stats, and whether active chunk indexes still fit in memory.
Sizing: estimate rows per day per point, apply your raw retention, and measure the columnstore ratio on a sample before committing to disk sizes.

Frequently asked questions

Is TimescaleDB a separate database or part of PostgreSQL?

It is a PostgreSQL extension. You install it into a PostgreSQL server and use normal PostgreSQL clients, SQL and tools; hypertables appear as ordinary tables to your application.

Is TimescaleDB open source?

Partly. The Apache 2 Edition is under the Apache License 2.0. The Community Edition, which adds the columnstore, continuous aggregates, retention policies and jobs, is source-available under the Timescale License (TSL), which the agreement itself describes as "open code" or "source-available"; it may be used without charge under the agreement's terms, and its section 2.2 prohibits database-as-a-service use. This is a summary, not legal advice: read the licence text for your situation.

What is the difference between Timescale and Tiger Data?

They are the same company. Timescale, Inc. now does business as Tiger Data; the managed service is called Tiger Cloud and the extension keeps the name TimescaleDB.

How is a continuous aggregate different from a materialized view?

A PostgreSQL materialized view is recomputed in full when you refresh it. A continuous aggregate refreshes incrementally for a configured time window in the background, is stored as a hypertable, and can keep roll-ups after the raw data has been dropped if the refresh window and retention are set correctly.

What chunk interval should I use for sensor data?

The default is 7 days. The documentation advises sizing it so the indexes of chunks currently receiving data fit in about 25% of memory, so the right interval depends on your write rate, index sizes and memory rather than on a fixed value. Very small intervals create many chunks, which the documentation says slows query planning.

Sources

Checked 27 October 2026.

How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.