TimescaleDb

TimescaleDB is a PostgreSQL extension for high-performance analytics on time-series and event data. It removes the usual choice between keeping data in Postgres and getting columnar query speed, because both live in the same database and answer the same SQL.

An ordinary table becomes a hypertable, which partitions data into time-based chunks behind the scenes. With the columnstore enabled, rows can be inserted straight into columnar form, typically compressing by more than ninety percent, and queries then scan only the time ranges and columns they actually need.

A few constructs do most of the work.

• Hypertables: declared with a table option, they partition by time automatically as data arrives.
• Columnstore: columnar storage with vectorized execution, giving analytical queries over millions of rows in milliseconds.
• Time buckets: the time_bucket function aggregates rows into intervals such as an hour for summary values.
• Continuous aggregates: materialized views refreshed incrementally in the background, so only changed data is recomputed.
• Refresh policies: schedule how often an aggregate updates and which window it processes.

Getting started takes a one-line local install script or a Docker container publishing Postgres on a non-standard port to avoid clashing with an existing instance. Around 8GB of RAM is recommended, and you connect with psql or any Postgres client such as pgAdmin. The install script is for local development and testing, not production.