Time-series data is a collection of metrics (regular) or measurements (irregular) that are tracked over time. In other words, time-series data is data that collectively represents how a system, process, or behavior changes over time.<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/02\/DSC_0066-2000x670-1-1024x343.jpg\")
<\/figure>\n\n\n\nAs you can imagine, time-series database are not new. The first time-series databases were mainly used for storing financial data. Today, time-series data are everywhere and we can find them in several applications and across various industries and business. The major reason is that we live surrounded by objects that are more and more connected and therefore have more and more sensors. IoT is thus one of the main use cases of time-series data, but it is obviously not the only one. Here are some other areas where this kind of data is used :<\/p>\n\n\n\n
- \n
- Weather (e.g. rainfall measurement)<\/li>\n\n\n\n
- Medical (e.g. heartbeat per minutes) <\/li>\n\n\n\n
- Website (e.g. daily visitors on a blog)<\/li>\n\n\n\n
- Retail (e.g. annual sales)<\/li>\n\n\n\n
- And much more…<\/li>\n<\/ul>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/02\/sample-time-seriese-analysis.png\")
Example of the monthly orders quantity – including forecasts<\/em><\/figcaption><\/figure>\n<\/div>\n\n\n TimescaleDB is actually a relational database for time-series data. It’is packaged as an extension of PostgreSQL, bringing new capabilities and features for data management and analytics as well as new optimizations and mechanisms for storage and query planner\/execution.
Being integrated to PostgreSQL as an extension allows TimescaleDB to take advantage of all the possibilities PostgreSQL offers in terms of data (data types, index types, schema, etc,…) but also of all the other available extensions. Some of them are particularly well adapted to work with TimescaleDB, I am thinking in particular of the well known PostGIS<\/a> and pg_stat_statement<\/a>.<\/p>\n\n\n\nTimescaleDB features have been designed specifically for time-series data management. Among them, we can find : <\/p>\n\n\n\n
- \n
- Transparent and automated partitioning
<\/strong>– To improve performance by keeping latest data and indexes in memory
<\/li>\n<\/ul>\n\n\n\n<\/p>\n\n\n\n
- \n
- Native columnar compression
<\/strong>– Compression according to data type (up to 97% storage reduction)
<\/li>\n<\/ul>\n\n\n\n<\/p>\n\n\n\n
- \n
- Continuous and real-time aggregations
<\/strong>– By maintaining a materialized view of aggregate time-series data to improve query performance
<\/li>\n<\/ul>\n\n\n\n<\/p>\n\n\n\n
- \n
- Automated time-series data management
<\/strong>– Retention policies, reordering policies, compression policies, aso…
<\/li>\n<\/ul>\n\n\n\n<\/p>\n\n\n\n
- \n
- In-database job-scheduling framework
<\/strong>– To run native and user-defined actions
<\/li>\n<\/ul>\n\n\n\n<\/p>\n\n\n\n
- \n
- Horizontally-scalable multi-node operation
<\/strong>– To scale the the time-series data across many databases<\/li>\n<\/ul>\n\n\n\nHypertables<\/h2>\n\n\n\n
TimescaleDB works with a specific type of tables called hypertables. Hypertables are designed to improve insert and query performance by partitioning time-series data on its time parameter.
The hypertables are actually parent tables made of many regular PostgreSQL child tables, called “chunks”. Each chunk is a partition based on a time constraint and only contains data from that time range. They are automatically set up and maintained by the database, meanwhile you insert and read the data as if it stored in a single and standard PostgreSQL table.<\/p>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/02\/hypertables-chunks-1024x780.png\")
https:\/\/docs.timescale.com\/timescaledb\/latest\/overview\/core-concepts\/hypertables-and-chunks\/hypertable-architecture\/<\/em><\/figcaption><\/figure>\n\n\n\n You can have both hypertables and regular PostgreSQL tables in the same database. Obviously, you should choose hypertables for time-series data, and regular PostgreSQL tables for relational data.<\/p>\n\n\n\n
Deployment<\/h2>\n\n\n\n
TimescaleDB is available with several deployment options : <\/p>\n\n\n\n
- \n
- Timescale Cloud
<\/strong>Fully managed service with automatic backup and restore, HA via replication, scaling, resizing, aso…
https:\/\/console.cloud.timescale.com\/
<\/a><\/li>\n<\/ul>\n\n\n\n<\/p>\n\n\n\n
- \n
- Managed Service for TimescaleDB
<\/strong>A service is a cloud instance on your chosen cloud provider, which you can install your database on. Available cloud providers are Azure, GCP and certain regions in AWS.
https:\/\/portal.managed.timescale.com\/login
<\/a><\/li>\n<\/ul>\n\n\n\n<\/p>\n\n\n\n
- \n
- Self hosted
<\/strong>Of course, TimescaleDB can also be installed on-premise on Linux (Debian, Ubuntu, RHEL, Rocky Linux, Fedora), on Windows or MacOS.
This is the option I chose for this blog.<\/li>\n<\/ul>\n\n\n\nInstallation<\/h2>\n\n\n\n
I decided to install TimescalDB on one of my PostgreSQL 14.6 instance that I have deployed with YaK<\/a> :-). When deploying TimescalDB on an already existing instance, it is recommended to install it from source instead of using the package manager of the Linux distribution. Please check before the compatibility matrix between PostgreSQL and TimescaleDB : https:\/\/docs.timescale.com\/timescaledb\/latest\/how-to-guides\/upgrades\/upgrade-pg\/<\/a>. <\/p>\n\n\n\n
The first thing to do is to clone the git repository and to checkout to the latest version (here 2.9.3) :<\/p>\n\n\n
\npostgres@timescale:\/home\/postgres\/ [pg14.6] git clone https:\/\/github.com\/timescale\/timescaledb\nCloning into 'timescaledb'...\nremote: Enumerating objects: 58335, done.\nremote: Counting objects: 100% (238\/238), done.\nremote: Compressing objects: 100% (161\/161), done.\nremote: Total 58335 (delta 126), reused 142 (delta 77), pack-reused 58097\nReceiving objects: 100% (58335\/58335), 30.13 MiB | 17.35 MiB\/s, done.\nResolving deltas: 100% (48073\/48073), done.\n09:08:42 postgres@timescale:\/home\/postgres\/ [pg14.6] \n\npostgres@timescale:\/home\/postgres\/ [pg14.6] cd timescaledb\/\n\npostgres@timescale:\/home\/postgres\/timescaledb\/ [pg14.6] git checkout 2.9.3\nNote: switching to '2.9.3'.\n...\n...\n...\npostgres@timescale:\/home\/postgres\/timescaledb\/ [pg14.6] \n<\/pre><\/div>\n\n\n
Then, bootstrap the built system :<\/p>\n\n\n
\npostgres@timescale:\/home\/postgres\/timescaledb\/ [pg14.6] .\/bootstrap\n-- The C compiler identification is GNU 10.2.1\n-- Detecting C compiler ABI info\n-- Detecting C compiler ABI info - done\n-- Check for working C compiler: \/usr\/bin\/cc - skipped\n-- Detecting C compile features\n-- Detecting C compile features - done\n-- TimescaleDB version 2.9.3. Can be updated from version 2.9.2\n-- Build type is Release\n-- Install method is 'source'\n-- Performing Test CC_SUPPORTS_NO_UNUSED_CLI_ARG\n...\n...\n...\n-- Configuring done\n-- Generating done\n-- Build files have been written to: \/home\/postgres\/timescaledb\/build\nTimescaleDB build system initialized in .\/build. To compile, do:\ncd .\/build && make\npostgres@timescale:\/home\/postgres\/timescaledb\/ [pg14.6] \n<\/pre><\/div>\n\n\n
Build the extension :<\/p>\n\n\n
\npostgres@timescale:\/home\/postgres\/timescaledb\/ [pg14.6] cd build && make\nScanning dependencies of target sqlupdatescripts\n[ 1%] Generating \/home\/postgres\/timescaledb\/build\/sql\/updates\/post-update.sql.processed\n[ 1%] Generating \/home\/postgres\/timescaledb\/build\/sql\/timescaledb--2.9.2--2.9.3.sql\n[ 2%] Generating \/home\/postgres\/timescaledb\/build\/sql\/timescaledb--2.9.1--2.9.3.sql\n[ 2%] Generating \/home\/postgres\/timescaledb\/build\/sql\/timescaledb--2.9.0--2.9.3.sql\n[ 3%] Generating \/home\/postgres\/timescaledb\/build\/sql\/timescaledb--2.8.1--2.9.3.sql\n...\n...\n...\n[ 99%] Building C object tsl\/src\/CMakeFiles\/timescaledb-tsl.dir\/remote\/utils.c.o\n[100%] Linking C shared module timescaledb-tsl-2.9.3.so\n[100%] Built target timescaledb-tsl\npostgres@timescale:\/home\/postgres\/timescaledb\/build\/ [pg14.6]\n<\/pre><\/div>\n\n\n
And install it :<\/p>\n\n\n
\npostgres@timescale:\/home\/postgres\/timescaledb\/build\/ [pg14.6] make install\nInstall the project...\n-- Install configuration: "Release"\n-- Installing: \/u01\/app\/postgres\/product\/14\/db_6\/share\/extension\/timescaledb.control\n-- Installing: \/u01\/app\/postgres\/product\/14\/db_6\/share\/extension\/timescaledb--2.9.3.sql\n...\n...\n...\n-- Installing: \/u01\/app\/postgres\/product\/14\/db_6\/lib\/timescaledb-2.9.3.so\n-- Installing: \/u01\/app\/postgres\/product\/14\/db_6\/lib\/timescaledb.so\n-- Installing: \/u01\/app\/postgres\/product\/14\/db_6\/lib\/timescaledb-tsl-2.9.3.so\npostgres@timescale:\/home\/postgres\/timescaledb\/build\/ [pg14.6] \n<\/pre><\/div>\n\n\n
Once the extension is installed, we need to add it to list of the shared_preload_libraries PostgreSQL parameter :<\/p>\n\n\n
\nshared_preload_libraries = 'pg_stat_statements,timescaledb'\n<\/pre><\/div>\n\n\n
The instance must be restarted to take the new value in account.<\/p>\n\n\n\n
Some other instance parameters can be automatically adapted using timescaledb-tune<\/a> (memory, parallelism, WAl, aso) but I’ll not do it for this demo.<\/p>\n\n\n\n
Like all PostgreSQL extensions, TimescalDB needs to be added to the database. I usually create the extensions in the template1 database, so that they are automatically available for all the databases that will be created in the future :<\/p>\n\n\n
\npostgres=# \\c template1\nYou are now connected to database "template1" as user "postgres".\n\ntemplate1=# create extension if not exists timescaledb;\nWARNING: \nWELCOME TO\n _____ _ _ ____________ \n|_ _(_) | | | _ \\ ___ \\ \n | | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_\/ \/ \n | | | | _ ` _ \\ \/ _ \\\/ __|\/ __\/ _` | |\/ _ \\ | | | ___ \\ \n | | | | | | | | | __\/\\__ \\ (_| (_| | | __\/ |\/ \/| |_\/ \/\n |_| |_|_| |_| |_|\\___||___\/\\___\\__,_|_|\\___|___\/ \\____\/\n Running version 2.9.3\nFor more information on TimescaleDB, please visit the following links:\n\n 1. Getting started: https:\/\/docs.timescale.com\/timescaledb\/latest\/getting-started\n 2. API reference documentation: https:\/\/docs.timescale.com\/api\/latest\n 3. How TimescaleDB is designed: https:\/\/docs.timescale.com\/timescaledb\/latest\/overview\/core-concepts\n\nNote: TimescaleDB collects anonymous reports to better understand and assist our users.\nFor more information and how to disable, please see our docs https:\/\/docs.timescale.com\/timescaledb\/latest\/how-to-guides\/configuration\/telemetry.\n\nCREATE EXTENSION\ntemplate1=# \n<\/pre><\/div>\n\n\n
Once done, let’s create a new database to check the extension availability :<\/p>\n\n\n
\npostgres=# create database timescaledb;\nCREATE DATABASE\npostgres=#\n\npostgres=# \\c timescaledb \nYou are now connected to database "timescaledb" as user "postgres".\ntimescaledb=# \n\ntimescaledb=# \\dx\n List of installed extensions\n Name | Version | Schema | Description \n--------------------+---------+------------+------------------------------------------------------------------------\n pg_prewarm | 1.2 | extensions | prewarm relation data\n pg_stat_statements | 1.9 | extensions | track planning and execution statistics of all SQL statements executed\n pg_trgm | 1.6 | extensions | text similarity measurement and index searching based on trigrams\n plpgsql | 1.0 | pg_catalog | PL\/pgSQL procedural language\n timescaledb | 2.9.3 | public | Enables scalable inserts and complex queries for time-series data\n(5 rows)\n\ntimescaledb=# \n<\/pre><\/div>\n\n\n
All right !<\/p>\n\n\n\n
Hypertable creation<\/h2>\n\n\n\n
The process of creating a hypertable is easy. First of all, let’s create some standard tables :<\/p>\n\n\n
\ntimescaledb=# CREATE TABLE "locations"(\n device_id TEXT,\n location TEXT,\n environment TEXT\n);\nCREATE TABLE\ntimescaledb=# \n\ntimescaledb=# CREATE TABLE "conditions"(\n time TIMESTAMP WITH TIME ZONE NOT NULL,\n device_id TEXT,\n temperature NUMERIC,\n humidity NUMERIC\n);\n\ntimescaledb=# CREATE INDEX ON "conditions"(time DESC);\nCREATE INDEX\ntimescaledb=# CREATE INDEX ON "conditions"(device_id, time DESC);\nCREATE INDEX\ntimescaledb=#\n\ntimescaledb=# \\dt\n List of relations\n Schema | Name | Type | Owner \n--------+------------+-------+----------\n public | conditions | table | postgres\n public | locations | table | postgres\n(2 rows)\n<\/pre><\/div>\n\n\n
Then convert one of the table to hypertable :<\/p>\n\n\n
\ntimescaledb=# SELECT create_hypertable('conditions', 'time', chunk_time_interval => INTERVAL '24 hours');\n<\/pre><\/div>\n\n\nThe first argument is the name of the table we want to convert. The second argument is the name of the column containing time values as well as the primary column to partition by. The 3rd argument is the event time that each chunk covers. Default is 7 days, here we set it to 1 day.<\/p>\n\n\n\n
To populate the tables, I have loaded the sample datasets<\/a> provided by Timescale :<\/p>\n\n\n
\npostgres@timescale:\/home\/postgres\/ [PG01] psql -U postgres -d timescaledb -c "\\COPY locations FROM weather_big_locations.csv CSV"\nCOPY 2000\n<\/pre><\/div>\n\n\n
- Self hosted
- Managed Service for TimescaleDB
- Timescale Cloud
- Horizontally-scalable multi-node operation
- In-database job-scheduling framework
- Automated time-series data management
- Continuous and real-time aggregations
- Native columnar compression
- Transparent and automated partitioning