Understanding of TimescaleDB

What is the Time series:

Time series is a data set that tracks a sample over time. Time series data also referred to as time-stamped data, is a sequence of data points(any single fact is a data point) indexed in time order.

Uses Time series data:

  • Forecasting is one of the most main cases. (Predictions)
  • Anomaly detection.
  • Used across IoT systems to manage the insane volume of data coming from millions of devices
  • It is used in DevOps to track system health and trends.
  • Time series data is used by scientists, engineers, thinkers

Timescale DB:

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.

Hypertables:

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.


-- Step 1: Define regular table
CREATE TABLE IF NOT EXISTS weather_metrics (
time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
timezone_shift int NULL,
city_name text NULL,
temp_c double PRECISION NULL,
temp_min_c double PRECISION NULL,
temp_max_c double PRECISION NULL,
weather_type_id int NULL
);
-- Step 2: Turn into hypertable
SELECT create_hypertable('weather_metrics','time');

Chunks:

Chunks are for partitioning Hypertables. Chunks are created by partitioning a Hypertables’s data into one or multiple dimensions. All Hypertables are partitioned by the values belonging to a time column, which may be in timestamp, date or various integer forms.

Advantages of Hypertables and Chunks:

In-memory:

Limitations:

  • Foreign key referencing is not supported in Hypertables.
  • The time column used in partitioning can not have null values.
  • UPDATE statements that move value between chunks are not supported.
  • Unique indexes must include all columns that are partitioning dimensions.

Installation:

For installing TimescaleDB follow this official link.

--

--

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
Edison Devadoss

Edison Devadoss

Software developer / JavaScript / React / React Native / Firebase / Node.js / C Programming / Book Reader / Story Teller