TimescaleDB | Ravi Singh

TimescaleDB

TimescaleDB is a relational database for time-series data. It is implemented as an extension of PostgreSQL. This extension model allows the database to take advantage of the richness of PostgreSQL from 40+ data types. It is an open-source time-series database developed by Timesacle Inc.

It is written in C and extends PostgreSQL. It supports standard SQL queries and relational databases. An open-source time-series SQL database optimized for fast ingest and complex queries.

Timescale DB was founded by Ajay Kulkarni and Michael in response to their need for a database solution to support IoT workloads.

Uses of Time series data:

1. Forecasting is one of the most main cases. (Predictions)

2. Anomaly detection.

3. Used across IoT systems to manage the insane volume of data coming from millions of devices

4. It is used in DevOps to track system health and trends

5. Time series data is used by scientists, engineers, and thinkers

List of various Time-series databases:

why do we choose TimescaleDB?

In TimescaleDB, we can access using SQL language. SQL is one of the most popular languages to work with datasets. It supports running the databases in multi-cluster mode, as well as provides various features. For a better understanding of the timescaleDB, we need to understand two main concepts: Hypertables and Chunks.

Hypertables:

A hypertable looks and acts like a regular table. But it's actually a parent table made of many regular PostgreSQL tables, called chunks. Virtually all user interactions with TimescaleDB are Hypertable. However, Hypertables are abstractions or virtual views of many individual tables that actually store data called chunks.

Chunks:

Chunks are used to partition Hypertables. Chunks are formed by dividing the data in Hypertables into one or more dimensions. All Hypertables are partitioned by the values of a time column, which can take the form of a timestamp, a date, or an integer. As rows are inserted into the database, TimescaleDB automatically generates these chunks.

A standard database table is used to implement each chunk. Internally, the chunk is a "child table" of the "parent" Hypertable in PostgreSQL. The partitioning of all chunks in a Hypertable is disjoint.

For example, assume each chunk contains 1 day's worth of data. For each day for which you have data, TimescaleDB creates a chunk. It examines the time column for each row of data and inserts the data into the appropriate chunk. All rows with the same day's time values are inserted into the same chunk. Rows from different days are inserted into various chunks

Advantages of Hypertables and Chunks:

1. In-memory: Chunks can be configured so that the recent chunks fit in memory. So inserting into recent time intervals as well as queries to recent data can be fast because it is stored in memory, not disk.

2. Local indexes: Indexes are built on each chunk independently, rather than a global index across all data.

3. Easy data retention:

With TimescaleDB users can quickly delete chunks based on their time range. Users can create a data retention policy to make it automatic.

Limitations:

1. Foreign key referencing is not supported in Hypertables.

2. The time column used in partitioning can not have null values.

3. UPDATE statements that move value between chunks are not supported.

4. Unique indexes must include all columns that are partitioning dimensions.