TimescaleDB vs PostgreSQL

Quick verdict

This page is for teams who already use PostgreSQL, or are about to, and need to store time-stamped readings: BMS points such as AHU supply-air temperature and zone CO2, chiller kW, or meter readings every 1 to 60 seconds that feed a CAFM or CMMS. The question is whether native PostgreSQL features are enough, or whether the TimescaleDB extension earns its place. For background on time-series data and the device-to-dashboard flow, see the time-series and IoT data hub; for a full design walk-through, see how to store IoT sensor data.

PostgreSQL is the open source relational database released under the PostgreSQL License. Version 18.6 is the current release used for the examples here. TimescaleDB, maintained by Tiger Data, is a PostgreSQL extension for time-series and event data; it runs inside PostgreSQL, so every table, driver and SQL feature still works. Its latest GitHub release when checked was 2.31.0 (20 October 2026). See the TimescaleDB guide for installation and design detail.

Side by side

Key differences

Partitioning: hypertables versus declarative partitions

Native PostgreSQL partitioning splits one logical table into child tables by range. You create each partition, and queries that filter on the partition key skip the others. This script was run on PostgreSQL 18.6:

-- Plain PostgreSQL (run on PostgreSQL 18.6 in PGlite)
CREATE TABLE sensor_reading (
    sensor_id   int              NOT NULL,
    ts          timestamptz      NOT NULL,
    value       double precision NOT NULL,
    PRIMARY KEY (sensor_id, ts)
) PARTITION BY RANGE (ts);

CREATE TABLE sensor_reading_2026_09 PARTITION OF sensor_reading
    FOR VALUES FROM ('2026-09-01 00:00+00') TO ('2026-10-01 00:00+00');
CREATE TABLE sensor_reading_2026_10 PARTITION OF sensor_reading
    FOR VALUES FROM ('2026-10-01 00:00+00') TO ('2026-11-01 00:00+00');

EXPLAIN (COSTS OFF)
SELECT avg(value) FROM sensor_reading
WHERE ts >= '2026-10-01 00:00+00' AND ts < '2026-10-02 00:00+00';

-- Aggregate
--   ->  Seq Scan on sensor_reading_2026_10 sensor_reading
--         Filter: (ts >= ... AND ts < ...)

The plan touches only the October partition. Two rules catch people out. First, partitions must exist before rows arrive, so someone (or something) has to create next month's partition in advance. Second, a primary key or unique constraint on a partitioned table must include the partition key. Running a table with an identity-only primary key returned:

-- Plain PostgreSQL (run on PostgreSQL 18.6 in PGlite)
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "bad_reading" lacks column "ts" which is part of the partition key.

pg_partman fills the first gap: it is an extension (PostgreSQL License) that creates and manages time-based and number-based partition sets on top of native partitioning, including retention, with a background worker so no external scheduler is needed in most cases. A TimescaleDB hypertable goes further: you create the table once and chunks are created automatically as data arrives, with the chunk interval configurable.

-- Illustrative DDL, not run here: from the Tiger Data documentation;
-- check every setting against the documentation for your version before use.
CREATE TABLE conditions (
   time        TIMESTAMPTZ      NOT NULL,
   location    TEXT             NOT NULL,
   device      TEXT             NOT NULL,
   temperature DOUBLE PRECISION NULL,
   humidity    DOUBLE PRECISION NULL
) WITH (
   tsdb.hypertable,
   tsdb.segmentby = 'device',
   tsdb.orderby   = 'time DESC'
);

Indexing: BRIN in plain PostgreSQL

Sensor data is usually inserted in roughly time order, so the timestamp correlates with the physical position of rows. That is the case BRIN (Block Range Index) is designed for: it stores a small summary per block range instead of one entry per row, so it stays tiny on very large tables. It can be created on a partitioned table and is cascaded to each partition:

-- Plain PostgreSQL (run on PostgreSQL 18.6 in PGlite)
CREATE INDEX ON sensor_reading USING brin (ts);

A B-tree on (sensor_id, ts) (here, the primary key) still serves "latest readings for one point" queries; BRIN serves wide time-range scans. TimescaleDB creates a descending time index on hypertables automatically and, in its columnstore, adds sparse min/max and bloom metadata per batch. For indexing basics, see SQL indexes explained.

Rollups: materialized views versus continuous aggregates

In plain PostgreSQL you bucket data with date_bin or date_trunc and cache rollups in a materialized view. The run below inserts one reading per minute for three sensors across two months, then buckets one hour of sensor 1 into 15-minute intervals:

-- Plain PostgreSQL (run on PostgreSQL 18.6 in PGlite)
SELECT date_bin(interval '15 minutes', ts, timestamptz '2026-01-01 00:00+00') AS bucket,
       sensor_id, count(*) AS samples
FROM sensor_reading
WHERE sensor_id = 1
  AND ts >= '2026-10-01 00:00+00' AND ts < '2026-10-01 01:00+00'
GROUP BY 1, 2
ORDER BY 1;
-- 4 rows: buckets 00:00, 00:15, 00:30, 00:45 (UTC), each with 15 samples

CREATE MATERIALIZED VIEW sensor_hourly AS
SELECT date_trunc('hour', ts) AS hour, sensor_id,
       count(*) AS samples, min(value) AS min_value, max(value) AS max_value
FROM sensor_reading
GROUP BY 1, 2;

CREATE UNIQUE INDEX ON sensor_hourly (hour, sensor_id);

INSERT INTO sensor_reading VALUES (1, '2026-10-01 02:00+00', 13.5);
SELECT count(*) FROM sensor_hourly;              -- 12: the new hour is not there yet
REFRESH MATERIALIZED VIEW CONCURRENTLY sensor_hourly;
SELECT count(*) FROM sensor_hourly;              -- 13

The view is stale until refreshed, and REFRESH MATERIALIZED VIEW recomputes the whole view each time. CONCURRENTLY (which needs a unique index) lets readers continue during the refresh, but the work is still a full recomputation. The cost of that recomputation grows with the data behind the view, so in our judgement it is small for thousands of rows and can become expensive for years of one-second data; measure the refresh time on your own volume.

TimescaleDB continuous aggregates track which time ranges changed and refresh only those, in the background, on a policy. The Tiger Data documentation states: "Continuous aggregates have a much lower maintenance burden than regular PostgreSQL materialized views, because the whole view is not created from scratch on each refresh." Real-time aggregation (disabled by default since 2.13) can also merge not-yet-materialized raw data into results.

-- Illustrative configuration, not run here: from the Tiger Data documentation;
-- check every setting against the documentation for your version before use.
CREATE MATERIALIZED VIEW conditions_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket, device,
       avg(temperature) AS avg_temp
FROM conditions
GROUP BY bucket, device;

SELECT add_continuous_aggregate_policy('conditions_hourly',
  start_offset      => INTERVAL '1 month',
  end_offset        => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

Retention and compression

In plain PostgreSQL, the efficient way to delete old data is to detach and drop a partition, which the PostgreSQL documentation notes is far faster than a bulk DELETE and avoids its vacuum overhead (note that DROP TABLE on a partition takes an ACCESS EXCLUSIVE lock on the parent table):

-- Plain PostgreSQL (run on PostgreSQL 18.6 in PGlite)
ALTER TABLE sensor_reading DETACH PARTITION sensor_reading_2026_09;
DROP TABLE sensor_reading_2026_09;
SELECT count(*) FROM sensor_reading;   -- 361: only October rows remain

TimescaleDB does the same at chunk level, automatically: a retention policy drops chunks whose data is entirely older than a threshold, and it can be combined with continuous aggregates so summaries outlive raw data.

-- Illustrative configuration, not run here: from the Tiger Data documentation;
-- check every setting against the documentation for your version before use.
SELECT add_retention_policy('conditions', drop_after => INTERVAL '6 months');

Compression is the clearest gap. PostgreSQL compresses only large individual values (TOAST); it has no columnar storage in core. TimescaleDB's hypercore engine keeps recent chunks in the rowstore and converts older ones to a compressed columnstore, while still accepting INSERT, UPDATE, DELETE and upserts. The vendor, Tiger Data, reports 90 to 98% compression in the columnstore; that is the vendor's figure, not reproduced here, and real ratios depend on data types, segmenting and how repetitive the readings are.

Editions, licensing and hosting

TimescaleDB ships in two editions. The Apache 2 Edition includes hypertables, chunk management (drop_chunks) and basic hyperfunctions such as time_bucket, first and last. The Community Edition, under the Timescale License (TSL), adds the columnstore, continuous aggregates, retention and other policies, the job scheduler and gap-filling functions. You can check what is loaded with SHOW timescaledb.license;, which returns timescale (Community) or apache.

This matters for managed PostgreSQL: a cloud provider that offers the TimescaleDB extension may offer only the Apache 2 Edition, which lacks the Community Edition features described above. Plain PostgreSQL partitioning and BRIN are core features; whether pg_partman is available depends on the provider's extension list. If you use TimescaleDB on a managed platform, confirm the edition before designing around continuous aggregates or compression.

Licence texts. The TimescaleDB repository's LICENSE file states that outside the tsl directory "source code in a given file is licensed under the Apache License Version 2.0, unless otherwise noted", and that within it "source code in a given file is licensed under the Timescale License, unless otherwise noted". Tiger Data's licence page summarises the TSL as: "Roughly speaking, as long as you are not offering TimescaleDB as a hosted Database-as-a-Service, you can use all Community features for free." The TSL agreement is more detailed: its Internal Use grant covers use "solely for Your own internal business purposes in a manner that does not expose or give access to, directly or indirectly (e.g., via a wrapper), the Timescale Data Definition Interfaces or the Timescale Data Manipulation Interfaces" to anyone other than you, your employees and contractors; a separate grant with its own conditions covers Value Added Products or Services; and section 2.2 prohibits "using any TSL Licensed Software to provide time-sharing services or database-as-a-service services". PostgreSQL is released under the PostgreSQL License, and pg_partman's LICENSE.txt states that it "is released under the PostgreSQL License, a liberal Open Source license, similar to the BSD or MIT licenses"; both texts grant permission "to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement", provided the copyright notice and the following paragraphs appear in all copies.

Not legal advice: this section summarises and quotes the licence texts as published on 27 October 2026; which terms apply to your deployment depends on how it is built and offered, so read the licences for the versions you use and take legal advice for your situation.

Sizing, operations and security

A useful first estimate is rows per day = points x (86,400 / sample interval in seconds). Taking one building with 2,000 BMS points at 60-second intervals as an illustrative assumption, that is 2.88 million rows a day, about a billion a year; at 10-second intervals, six times that. In our judgement (not a measured or documented threshold), the first case is within reach of plain PostgreSQL with monthly or weekly partitions, BRIN and a few rollups, and the second is where compression and incremental aggregates are more likely to pay for themselves; confirm either way with a test on your own hardware, queries and retention.

Operationally both are PostgreSQL: the same backups (pg_dump, physical backups, point-in-time recovery), replication, roles, row-level security and TLS. TimescaleDB adds an extension version to keep in step with PostgreSQL upgrades and background jobs to monitor. Both keep the integration story simple: Kafka consumers, MQTT bridges and BI tools connect with ordinary PostgreSQL drivers. Duplicate readings from at-least-once delivery can be handled with INSERT ... ON CONFLICT DO NOTHING on the (sensor_id, ts) key; see PostgreSQL error 23505 for the duplicate-key error you get without it.

Pricing and licensing

PostgreSQL and pg_partman are licensed under the PostgreSQL License, which grants use "without fee"; managed PostgreSQL is priced by each provider. TimescaleDB carries no licence fee to self-host in either edition (Tiger Data: "TimescaleDB Community Edition is completely free if you manage your own service"), subject to the licence terms summarised above. Tiger Cloud, Tiger Data's managed service, charges based on consumption: its pricing page (checked 27 October 2026) describes hourly billing for the compute you configure and the storage you actually use, lists Performance, Scale and Enterprise plans and offers a free trial. Compute and storage rates depend on the plan, region and configuration and change over time, so we do not quote them; use the pricing page for a current figure for your region. Add-ons such as HA replicas, tiered storage and production support are priced separately.

Pricing checked on the vendors' official pages on 27 October 2026. Prices change; confirm before buying.

Where each one leads

Limitations

When to choose each

When neither is right

• You want a dedicated time-series server rather than PostgreSQL: see TimescaleDB vs InfluxDB and the InfluxDB and QuestDB guides.
• The job is large-scale analytics across many sites and years: a columnar engine may suit better; see ClickHouse vs PostgreSQL.
• The data is infrastructure metrics for alerting: a metrics system such as Prometheus is the usual tool; see Prometheus vs Datadog.
• You are comparing many engines at once: see the time-series database selection guide.

Final recommendation

Frequently asked questions

Sources

• PostgreSQL: table partitioning
• PostgreSQL: BRIN indexes
• PostgreSQL: REFRESH MATERIALIZED VIEW
• PostgreSQL: date/time functions (date_bin)
• PostgreSQL License
• pg_partman GitHub repository
• TimescaleDB GitHub repository
• Tiger Data: compare TimescaleDB editions
• Tiger Data: software licensing
• Tiger Data: understand hypertables
• Tiger Data: create a hypertable
• Tiger Data: continuous aggregates
• Tiger Data: data retention
• Tiger Data: add_retention_policy
• Tiger Data: hypercore
• Tiger Data pricing
• TimescaleDB repository: LICENSE file
• TimescaleDB repository: Timescale License agreement
• pg_partman repository: LICENSE.txt

Checked October 2026.

How we research comparisons: our editorial method.