Handling Time Series Data On The Timescale Database
Posted By : Shivansh Pandey | 07-Jan-2021
Timescale DB is an open source database and it can also be considered as Postgres DB enhancement. It is used for fast ingest and complex queries.
More specifically, compared to PostgreSQL, TimescaleDB exhibits:
- Scalable up to 20x higher inserts (no hiccups even at billions of rows)
- Ultra-fast queries, for time-based queries you get improvements ranging from 1.2x to over 14,000x
- 2000x faster deletes, it facilitates implementation of data retention policies that is very critical
- New time-centric functions, for easier manipulation of time-series in SQL
Hypertables are something that makes timescale different and unique from other databases
Hypertables are easy to manage tables and also behave as expected to users who are familiar with standard PostgreSQL tables. Due to this, you have identical SQL commands to create, alter, or delete (hyper)tables in TimescaleDB as in PostgreSQL. Despite consisting of many interlinked "chunk" tables, the hypertable automatically passes down any command made to its every chunk.
Also Read: An Introduction To T SQL Server
How To Create a Hypertable
Creating a hypertable is a two-step process.
Create a standard table
CREATE TABLE conditions ( time TIMESTAMPTZ NOT NULL, location TEXT NOT NULL, temperature DOUBLE PRECISION NULL );
ALTER TABLE conditions ADD COLUMN humidity DOUBLE PRECISION NULL;
The schema changes will automatically reach every chunk of the hypertable with the help of TimescaleDB.
DELETING A HYPERTABLE
DROP TABLE conditions;
SOME IMPORTANT FUNCTIONS THAT MAKE TIMESCALE DB FASTER FROM OTHER SQL DATABASES
Getting the number of data inserted in the past 10 hours
SELECT COUNT(*) FROM conditions WHERE time > NOW() - INTERVAL '10 hours';
Use this function to get information for a 15-min period for each location over the past 3 hours, ordered by time and temperature
SELECT time_bucket('15 minutes', time)
AS fifteen_min, location, COUNT(*), MAX(temperature)
AS max_temp, MAX(humidity)
AS max_hum FROM conditions WHERE time > NOW() - INTERVAL '3 hours' GROUP BY fifteen_min, location
ORDER BY fifteen_min DESC, max_temp DESC;
Also Read: Enhancing SQL query Performance
How many distinct locations with air conditioning have reported data in the past day
SELECT COUNT(DISTINCT location) FROM conditions JOIN locations ON conditions.location = locations.location WHERE locations.air_conditioning = True AND time > NOW() - INTERVAL '1 day'
All the time series functions can be enabled only if you create a hypertable of your existing table otherwise you won't be able to use these functions.
There are many more important features in the timescale database such as Histogram, First Last, Time Bucket, etc.
You can visit the official documentation of the Timescale database for more.
Choose Oodles For SaaS App Development Services
We are a 360-degree SaaS app development company that focuses on building high-quality web and mobile applications using the latest tools, frameworks, and SDKs. Our development team analyzes your project requirements and formulates effective strategies to create performance-driven applications that maximize enterprise benefits. We have successfully completed several full-scale SaaS application development projects with a focus on next-gen technologies. For project-related queries, reach us out at email@example.com.