Skip to content
Search

TimescaleDB

lastmod 2025-02-15created 2020-10-3018 edits

An open-source time-series SQL database optimized for fast ingest and complex queries. Packaged as a PostgreSQL extension.

Categories

About

  • Multi-node, petabyte-scale RDB for time-series data (based on PostgreSQL)
  • Completely free under the Timescale License
    • Offering it as-a-service is not allowed
  • Installation binaries provided for Docker or Ubuntu/Debian/Centos
  • TimescaleDB is built on PostgreSQL and specifically designed for time-series workloads
  • Supports 94+% or more native compression, with queries 10–100x faster than PostgreSQL/InfluxDB/Cassandra/MongoDB
  • Introduces a virtual table technique called distributed Hypertable for the multi-node implementation (automatic partitioning across multiple nodes)

What is time-series data (Time-series Data)?

When A sends money to B, you only need to record that the number decreased on one side and increased on the other, but time-series data is a data set that tracks data changes with INSERT rather than UPDATE. Through this change information, you can tell that the two are related in some way or that something is happening between them.

Time-series data was mainly applied in industries such as finance, manufacturing, power, and utilities, but over the past few years its use has grown explosively with the growth of monitoring, IoT, cryptocurrency, gaming, machine learning, and more. It is the most accurate data for tracking change for data-driven decision-making.

Quick start

If you use Docker: 

docker run --rm --name timescaledb -p 5432:5432 -e POSTGRES_USER=userid -e POSTGRES_PASSWORD=password timescale/timescaledb:2.1.0-pg13

Hypertable

Creates a TimescaleDB hypertable from a PostgreSQL table (replacing the latter), partitioned on time and with the option to partition on one or more other columns (i.e., space). All actions such as ALTER TABLE, SELECT, etc. continue to work on the resulting hypertable.

Required Arguments

relation
Identifier of table to convert to hypertable.
time_column_name
Name of the column containing time values as well as the primary column to partition by.

Example 

CREATE TABLE conditions (
  time        TIMESTAMPTZ       NOT NULL,
  location    TEXT              NOT NULL,
  temperature DOUBLE PRECISION  NULL,
  humidity    DOUBLE PRECISION  NULL
);

Just pass the time column of the conditions table as an argument. 

SELECT create_hypertable('conditions', 'time');

APIs

add_retention_policy

Create a policy to drop chunks older than a given interval of a particular hypertable or continuous aggregate on a schedule in the background. (See drop_chunks). This implements a data retention policy and will remove data on a schedule. Only one retention policy may exist per hypertable.

Management and tips

Chunk

  • Applying hypertable after creating a table creates chunk tables.
  • Unless chunk tables are configured separately, the default interval is 7 days, and a new chunk table is created after 7 days.
  • Chunk tables hold the data stored in the table.
  • Deleting a chunk table also deletes its data.

Removing old data in TimeScaleDB

Time-series data accumulates a lot, so it needs to be removed periodically, but deleting with DELETE causes a heavy load, so the official website recommends the following approach. 

-- 24시간이 지난 chunk table 삭제
SELECT add_retention_policy('a4y_state', INTERVAL '24 hours');

To remove the configured policy: 

SELECT remove_retention_policy('a4y_state');

JOB status can be checked with the following query. 

SELECT * FROM timescaledb_information.job_stats;

When chunk tables are not deleted

When there is only one chunk table
Check the chunk table interval.

Setting the Chunk Table interval 

SELECT set_chunk_time_interval('conditions', INTERVAL '24 hours');
  • Sets a 24-hour interval
  • This setting is applied after the interval set at table creation ends.
  • If it was not set at table creation, it is applied after the default 7 days.

Advanced SQL functions for time-series data

Timescale has many custom SQL functions that help you perform time-series analysis in fewer lines of code.

Examples of these functions include:

  • time_bucket() - Used to analyze data over arbitrary time intervals
  • first() - Used to find the earliest value based on time within an aggregate group
  • last() - Used to find the latest value based on time within an aggregate group
  • time_bucket_gapfill() - Used to analyze data over arbitrary time intervals and fill gaps in the data
  • locf() - Used to fill gaps in the data by carrying the last observed value forward
  • interpolate() - Used to fill gaps by linearly interpolating missing values between known data points

time_bucket

The following is an example of how to use time_bucket() to find the 15-day average temperature for each city over the past 6 months: 

-----------------------------------
-- time_bucket
-- Average temp per 15 day period
-- for past 6 months, per city
-----------------------------------
SELECT time_bucket('15 days', time) as "bucket"
   ,city_name, avg(temp_c)
   FROM weather_metrics
   WHERE time > now() - (6* INTERVAL '1 month')
   GROUP BY bucket, city_name
   ORDER BY bucket DESC;

Query used in the A4y test: 

SELECT time_bucket('1 days', time) as bucket,
   max(pm10) as max_pm10,
   min(pm10) as min_pm10
   FROM a4y_state
   WHERE time > now() - (6* INTERVAL '1 month') and uid=1079
   group by bucket;

See also

Favorite site

Documentation

Guide

author Zer0 sitemap