TimeseriesDB

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).

Hypertable and Chunk

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

SELECT show_chunks(‘conditions’);

If the table already has data before making it to hypertable, then set migrate_data to true.

how hypertable help with time-series data?

  1. 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.
  2. 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!🙂

Database Engineer having working experience in SQL and NO-SQL databases. 👨‍💻

Love podcasts or audiobooks? Learn on the go with our new app.

GraphQL & REST with Typescript, Prisma and Azure SQL: love at first sight!

Project architecture for your .NET CORE application- 3 proposals

5 Mistakes To Avoid When Learning a New Framework or Programming Language

Image of half an archway close to housing

How To Manage A Software Development Team

Essential Technical Aspects

Why do you need to backup AWS workloads?

AWS Overview

3.0.0 Alpha Release !

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Nidhi Gupta

Nidhi Gupta

Database Engineer having working experience in SQL and NO-SQL databases. 👨‍💻

More from Medium

Database Basics: Database Introduction, SQL vs NoSQL

Databases Overview — Thumbnail

Taiwan Covid19 Dashboard Data Engineering Project

MySQL InnoDB Cluster node Addition Issue: — Cluster.addInstance:

Data Pipeline using Python