Manual time-based partitioning is effective and has an operational tail: something must create next month's partition before data for next month arrives, or inserts fail. That job is easy to write and easy to forget, and its failure mode is a production outage at midnight on the first of the month.
Hypertables remove that. Partitions — called chunks — are created automatically as data arrives, and the table behaves like an ordinary table for every other purpose.
What changed in 2026
- Adoption broadened beyond metrics. Financial data, IoT, and event logging became common use cases alongside monitoring.
- Compression became a standard policy. Automatically compressing chunks older than a threshold became routine configuration.
- Continuous aggregates matured. Incrementally-maintained rollups reduced the cost of dashboard queries substantially.
- The relational advantage held. Keeping time-series data queryable with ordinary SQL and joinable against relational tables remained the main reason to choose this over a separate system.
What you get
|
Manual partitioning |
Hypertable |
| Partition creation |
You automate it |
Automatic |
| Query interface |
Ordinary SQL |
Ordinary SQL |
| Retention |
You script it |
Policy |
| Compression |
Per-engine, manual |
Policy |
| Rollups |
Materialised views you refresh |
Continuous aggregates |
| Joins to relational tables |
Yes |
Yes |
The last row is the real argument. Time-series data in the same database as your application data can be joined against it directly — device readings joined to device metadata, events joined to users. With a separate time-series system that requires application-level joining, which is considerably more work and slower.
Chunk interval is the decision
The main tuning parameter, and getting it wrong costs in one of two directions.
Too large and chunks are big, so a query for a narrow time range still reads a large chunk. Pruning becomes coarse and you lose much of the benefit.
Too small and you accumulate an enormous number of chunks. Query planning considers each one, and planning overhead grows with chunk count — the same problem as over-partitioning a relational table, per table partitioning.
The usual guidance targets chunks that fit comfortably in memory alongside their indexes, which in practice means intervals producing chunks of a manageable size given your ingest rate. That means the right interval depends on your data volume, not on a calendar preference — a high-ingest system wants shorter intervals than a low-ingest one for the same chunk size.
Get it approximately right and adjust; the interval applies to newly-created chunks, so changing it does not rewrite history.
Compression and its cost
Compressing older chunks converts them to a columnar representation, which produces large storage savings for time-series data — ordered similar values compress extremely well, per columnar storage.
The cost is flexibility. Compressed chunks are optimised for reading and aggregating, and modifying them is restricted or expensive depending on version and configuration. That is usually fine, because old time-series data is not updated — but it means late-arriving data for a compressed period is awkward.
If your ingest can produce records with timestamps hours or days old, set the compression threshold beyond that window. Compressing data that is still receiving late arrivals produces friction.
Retention policies pair naturally: compress after a week, drop after a year. Both are metadata-level operations on whole chunks rather than row-by-row work.
Continuous aggregates
The feature that most changes dashboard performance. A continuous aggregate is a rollup — hourly averages, daily counts — maintained incrementally as new data arrives, rather than recomputed on query.
A dashboard querying a year of data at hourly resolution reads a small rollup rather than scanning a year of raw records. The difference is frequently orders of magnitude.
Unlike an ordinary materialised view, it updates incrementally rather than requiring a full refresh, which is what makes it practical at time-series volumes — see what is a materialized view for the general concept.
Common mistakes
- Chunk interval chosen by calendar rather than by volume. Produces chunks too large or too small.
- Compressing data still receiving late arrivals. Awkward and expensive.
- No retention policy. Storage grows without bound.
- Querying raw data for dashboards. Use continuous aggregates.
- Using it for data without a time dimension. The design assumes one.
- Ignoring chunk count. Planning overhead grows with it.
FAQ
Is this just partitioning?
Automatic partitioning plus compression, retention, and incremental rollups. The partitioning is the foundation; the policies are what remove the operational work.
Can I convert an existing table?
Generally yes, with a migration that creates the hypertable and moves data. Plan for it on a large table; it is not instant.
Does it work with ordinary SQL?
Yes — that is the main advantage. Queries, joins, and existing tooling work unchanged, which is not true of most separate time-series systems.
When would I use a dedicated time-series database instead?
At very high ingest rates where a relational engine cannot keep up, or where you do not need joins to relational data and want the operational simplicity of a purpose-built system.
Where to go next
For the manual equivalent, read table partitioning. For the broader selection question, time-series databases, and for the compression mechanism, columnar storage.