TimescaleDB — Time-series database

What is TimescaleDB?

TimescaleDB is an open-source time-series database, but with a nice catch: it speaks SQL. Due to this very nice feature, you can use your existing clients to connect to the database and go on with your queries as usual. Since TimescaleDB is a PostgreSQL extension, in the same database you can have time-series data and non-time-series data. No need for multiple databases to handle all your data.

“A picture is worth a thousand words” — Google

Installing

Of course if you just want to try it out (or have the infrastructure in place), you can use the docker image

timescale/timescaledb:2.2.0-pg13 // there are other versions, e.g if you are using PostgreSQL 12 or 11

But installing on your infrastructure, depending on the case, just look up their docs.

Using

Since we are a yogurt connoisseur, we want to be able to track all our yogurt. So of course we pick TimescaleDB, we want to know where was our yogurt at a specific time, and as is always, build nice graphs while we enjoy our yogurt.

-- Create the database, let's call it 'yogurt'
CREATE database yogurt;
-- Connect to yogurt db
\c yogurt
-- Install the timescaledb extension on our database
CREATE EXTENSION IF NOT EXISTS timescaledb;
-- Our table for our precious yogurt
CREATE TABLE positions
(
    time     TIMESTAMP    NOT NULL,
    name     VARCHAR(255) NOT NULL,
    position POINT        NOT NULL
);
-- Special sauce to convert our table into a hypertable (table that can store time-series data)
SELECT create_hypertable('positions', 'time');

If you look at the last query, the first parameter is table name and the second parameter is the column which holds the time. For more details click HERE.

My yogurt moved!!! Need to store that info.

-- Need to insert
INSERT INTO positions VALUES (NOW(), 'Greek Yogurt', '(59.3325800,18.0649000)');

Made a mistake? Just use UPDATE. Need to see your data? SELECT

No special queries, all the traditional SQL you know applies.

Too much yogurt!

The unexpected happened, I have too much yogurts to track. Well, we’re in luck, TimescaleDB supports multi-node setups. Now all your data can be safely partitioned, and easily queried. Again nothing changes (apart from the query that creates the hypertable, that has a slight change).

If you don’t need old data frequently, compression can be used (you can even store it in slow media selectively if needed <— fancy).

-- Enable compression for the hypertable
ALTER TABLE positions SET (timescaledb.compress, timescaledb.compress_orderby = 'time DESC', timescaledb.compress_segmentby = 'name');
-- We need to tell it what to compress, basically all records older than 10 days
SELECT add_compression_policy('positions', INTERVAL '10d');

Conclusion

There is no special conclusion, while this database does solve a lot of the problems that you might have for a specific case (your yogurt placements). For some other it might not, to make your own case, go through the docs, one of the best documentations you can find and inform yourself.