Time-series data is data that collectively represents how a system, process, or behavior changes over time. Timescale database is one of the time-series databases. It is an open-source database designed to make SQL scalable. Timescale is an extension of the PostgreSQL database, providing automatic partitioning across time and space (partitioning key), as well as full SQL support.
Using TimescaleDB helps in scaling PostgreSQL for time-series data via automatic partitioning across time and space. In PostgreSQL, we have a table to store data but TimescaleDB has a table that looks like a regular table but is actually a virtual view of many individual tables comprising the actual data.
This single-table view is a hypertable that comprises many chunks, chunks are created by partitioning the hypertables data in either one or two dimensions: by a time interval, and by an (optional) “partition key” such as device id, location, user id, etc.
Hypertables are the heart of TimescaleDB and allow TimescaleDB to work with time-series data. The presence of chunks and hypertable in TimescaleDB helps in storing and querying a huge amount of data(petabyte).
Now, let’s see how we can create and query hypertable and chunks.
— Step1:Add extension TimescaleDB to current database.
CREATE EXTENSION IF NOT EXISTS timescaledb;
— Step 2: Create a table called conditions
CREATE TABLE CONDITIONS (
LOCATION TEXT NOT NULL,
DEVICE_ID INT NOT NULL,
TIME TIMESTAMPTZ NOT NULL,
TEMPERATURE DOUBLE PRECISION NULL
This table creation now is the same as how we create tables in other databases.
— Step3: Add column humidity to conditions table
ALTER TABLE conditions ADD COLUMN humidity DOUBLE PRECISION NULL;
Note:- The time column in create table must be defined as NOT NULL. If this is not already specified on table creation, create_hypertable will automatically add this constraint on the table when it is executed.
— Step4: Add some data to the table
INSERT INTO conditions VALUES (‘office’,101, NOW(),70.0, 50.0)
— Step 4: Turn table conditions into hypertable
SELECT create_hypertable(‘conditions’, ‘time’,chunk_time_interval => INTERVAL ‘1 day’,migrate_data=>’True’);
Time intervals: The current release of TimescaleDB enables both the manual and automated adaption of its time intervals. With manually-set intervals, users should specify a chunk_time_interval when creating their hypertable (the default value is 1 week). The interval used for new chunks can be changed by calling set_chunk_time_interval().
— Command to reset chunk_interval
SELECT set_chunk_time_interval(‘conditions’, INTERVAL ’24 hours’);
— Command to check chunks specific to table
If the table already has data before making it to hypertable, then set migrate_data to true.
how hypertable help with time-series data?
- Hypertables help speed up ingest rates since data is only inserted into the current chunk, leaving data in the other chunks untouched. Contrast this with inserting data into a single table, which will become bigger and more bloated as more data is ingested.
- Hypertables help speed up queries since only specific chunks are queried thanks to the automatic indexing by time and/or space.
— Step 5: SELECT * from conditions;
Do clap, if find useful!🙂