Handle time series data with TimescaleDB
Table of contents
Preamble
TimescaleDB is an open source time series database ranked fifth in DB-engine at the time of writing this post. The since long number one in the category in still InfluxDB but this is a commercial product. TimescaleDB is an open source extension of PostgreSQL so you benefit from all nice bits of PostgreSQL like SQL language, ACID, backup, high available features and so on… Of course the competition in the time series DBMS is challenging with product like Prometheus, Apache Druid, TDengine, Apache IoTDB, Graphite only to name a few…

The magic behind TimescaleDB is the automatic partitioning management (by date or more with additional key columns) of your tables into chunks to improve performance access. Queries benefit from partition pruning and parallel access of multiple smaller partition (chunks). Insertion, which is a typical action of time series, are improved by inserting in smaller objects…
My initial idea was to create a Python script to insert, over time, a temperature coming from different equipments. But over Internet the typical use case is to use the New York City taxi open data information and there is a very nice video on how to do it from Dreams of Code. I have over simplified what they do in the video to concentrate only on the queries response time. For reference they have also created a GitHub repository so go check their code as well !
Testing has been done using PostgreSQL 17.2 with TimescaleDB extension release 2.17.2. My test server is a Red Hat Enterprise Linux release 8.7 (Ootpa) with 12 cores and 64GB of memory. I have allocated only 1GB to my PostgreSQL cluster (shared_buffers = 1GB, pg_settings).
Worth also mentioning that Timescale have two other very interesting open source PostgreSQL extensions around AI and Vectors (hype subjects isn’t it ?) called pgai and pgvectorscale:
- pgai allows you to develop RAG, semantic search, and other AI applications directly in PostgreSQL
- pgvectorscale builds on pgvector with higher performance embedding search and cost-efficient storage for AI applications.
TimescaleDB installation as a container
I initially started with a PostgreSQL 17 container and tried to install the TimescaleDB extension into it but this is over complicated as you would have to put the TimescaleDB library inside the container. Much easier is to take directly the TimescaleDB container with podman pull:
PS C:\> podman pull --tls-verify=false timescale/timescaledb:latest-pg16 Resolving "timescale/timescaledb" using unqualified-search registries (/etc/containers/registries.conf.d/999-podman-machine.conf) Trying to pull docker.io/timescale/timescaledb:latest-pg16... Getting image source signatures Copying blob sha256:ca62116b55c19c087ed3b64f9565a255a448ce2976efe683c2920634cd91955e Copying blob sha256:20079513fd70082ee24bfb0d3b25ad8e5c7b5f497ba6212746b14b99f9292adb Copying blob sha256:f7edf311d9fcff699cfd5c5dde053b91d1134995849a3f3a2bbb421bb00415eb Copying blob sha256:43c4264eed91be63b206e17d93e75256a6097070ce643c5e8f0379998b44f170 Copying blob sha256:b2a477ebc38ead811b455bdf61539f4e47168e62b073078c04f6b9214ce16869 Copying blob sha256:31d68496fc4b0a353f7ffb53c025f76fd7858c8c734393c7225b983cd923e830 Copying blob sha256:fe10a127d942c50bd7f45cff7394698a21d4f9f04af58f080cde55a2bc444580 Copying blob sha256:d0e4b84306782836749cef2f3a5c1d967b8fde0a1a4cc9ef8782e43bf0e508dc Copying blob sha256:44f245f4a44f90ae99e778836a39703f0f5ed1066724a3c93f57f81b4b4889f5 Copying blob sha256:bd90f43efd19d4fc07bd7effb1cc6fc159f1726a21d2a065063d82f323be3e18 Copying blob sha256:4740ce06cadaaf98b16d1a48e70b26b64cd179e034335af4a91b3d972823b7e2 Copying blob sha256:65404663cb86248363f0a5095bbc2da1f1a0492653110799cf7626a1dd9a47e4 Copying blob sha256:9e6b578ca4c452571b0f1de345de6d394a779d5dc7225e8bd9eff0d6eda358f8 Copying blob sha256:0c71403cc6f8acdee74852a62a8e893039b23f615c6c3689c49631cd9b627c0c Copying blob sha256:78a1e2980e76a7077b7340d9f2aeb58c863fe3b88261d08590e72ae6094473d0 Copying blob sha256:1a84f007e2c22abe88686ade3fdb2f1954b81de63c437401a6b8cab1bb1a1b7f Copying blob sha256:8824a62eeca50fe92fa57ff63158bb16472415845308a8ac1b32b46fb095e962 Copying blob sha256:79f0a54dc8f61764452d6a1a560a217c5ba7d84709afe6a205865a36798bbba7 Copying blob sha256:7f145fad294929b9e83e56592f54adf8f8f733586c5b219c8b7f4341910aacd7 Copying config sha256:c1559c1c97757c6d715371874952b4a2eb5c9b9138ee932e2d368f91c665ff57 Writing manifest to image destination c1559c1c97757c6d715371874952b4a2eb5c9b9138ee932e2d368f91c665ff57
Create a TmescaleDB container with:
PS C:\> podman run --detach --name timescaledb16 -p 5433:5432 -e POSTGRES_PASSWORD=secure_password -e POSTGRES_USER=yjaquier timescaledb:latest-pg16 5ce918977fff919b4b0de767c1fbd20f7673013ad209af1eadf5ffb4b4307d8b PS C:\> podman ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 5ce918977fff docker.io/timescale/timescaledb:latest-pg16 postgres 6 seconds ago Up 6 seconds 0.0.0.0:5433->5432/tcp timescaledb16
You can connect to it at OS level with:
PS C:\> podman exec -it 5ce918977fff /bin/bash
5ce918977fff:/# su - postgres
5ce918977fff:~$ psql --username=yjaquier --dbname=yjaquier
psql (16.4)
Type "help" for help.
yjaquier=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
postgres | yjaquier | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
template0 | yjaquier | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/yjaquier +
| | | | | | | | yjaquier=CTc/yjaquier
template1 | yjaquier | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/yjaquier +
| | | | | | | | yjaquier=CTc/yjaquier
yjaquier | yjaquier | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
(4 rows)
yjaquier=# select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 16.6 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20240309) 13.2.1 20240309, 64-bit
(1 row)
yjaquier=# select datname from pg_database where datistemplate = false;
datname
----------
postgres
yjaquier
(2 rows)
Or configure you preferred graphical SQL client (HeidiSQL for me) and access to it remotely…
TimescaleDB extension installation
You can also install TimescaleDB extension in any existing supported version of PostgreSQL. Configure the TimescaleDB repository with:
sudo tee /etc/yum.repos.d/timescale_timescaledb.repo <
for usual certificate issue with my proxy I had to change sslverify=1 by sslverify=0…
Finally install TimescaleDB library with:
[root@server01 ~]# dnf install timescaledb-2-postgresql-17 [root@server01 ~]# ll /usr/pgsql-17/lib/timescale* -rwxr-xr-x 1 root root 1117824 Nov 27 11:11 /usr/pgsql-17/lib/timescaledb-2.17.2.so -rwxr-xr-x 1 root root 69688 Nov 27 11:11 /usr/pgsql-17/lib/timescaledb.so -rwxr-xr-x 1 root root 875096 Nov 27 11:11 /usr/pgsql-17/lib/timescaledb-tsl-2.17.2.so
As suggested in the installation:
TimescaleDB has been installed. You need to update your postgresql.conf file to load the library by adding 'timescaledb' to your shared_preload_libraries. The easiest way to do this (and more configuration) is to use timescaledb-tune: timescaledb-tune --pg-config=/usr/pgsql-17/bin/pg_config
Either you use timescaledb-tune or you modify your postgresql.conf file and add shared_preload_libraries = ‘timescaledb’ or concatenate timescaledb to your existing list (restart then your PostgreSQL cluster). Then install the extension for the database where you plan to use the extension:
yjaquier=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
yjaquier=# create extension if not exists timescaledb;
CREATE EXTENSION
yjaquier=# \dx
List of installed extensions
Name | Version | Schema | Description
-------------+---------+------------+---------------------------------------------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
timescaledb | 2.17.2 | public | Enables scalable inserts and complex queries for time-series data (Community Edition)
(2 rows)
yjaquier=# select extname, extversion from pg_extension;
extname | extversion
-------------+------------
plpgsql | 1.0
timescaledb | 2.17.2
(2 rows)
Remark:
You may already have (you should) this in your shared_preload_libraries. If yes simply concatenate with a comma the timescaledb extension name:
shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track = all
TimescaleDB development environment setup
As I plan to use Python to simulate an equipment sending data to my time series database, if you have Miniconda create the environment and install one of the most famous PostgreSQL Python library Psycopg with:
conda create -n "PostgreSQL" python=3.13 conda activate PostgreSQL pip install "psycopg[binary]"
On my RedHat Linux server the latest available Python is 3.12.6, if you have Miniconda you have more flexibility in Python version. To avoid polluting the main Python environment and for better segragation create a Python vitual environment with:
[postgres@server01 ~]$ /usr/bin/python3.12 -m venv psycopg [postgres@server01 ~]$ source psycopg/bin/activate (psycopg) [postgres@server01 ~]$ python -V Python 3.12.6 (psycopg) [postgres@server01 ~]$ pip list Package Version ------- ------- pip 23.2.1 (psycopg) [postgres@server01 ~]$ export https_proxy='http://proxy_user:proxy_password@proxy_server:proxy_port' (psycopg) [postgres@server01 ~]$ pip install "psycopg[binary]"
Then install required packages with (Pandas needed by PyArrow for transformation):
pip install pyarrow pip install pandas
And let’s write a simple example to confirm all is fine:
import psycopg
try:
# Connect to an existing database
connection = psycopg.connect(user="yjaquier", password="secure_password", host="localhost", port="5433", dbname="yjaquier")
# Open a cursor to perform database operations
cursor = connection.cursor()
cursor.execute("select version()")
row=cursor.fetchone()
if not row:
raise KeyError("No data found")
print(row[0])
# closing database connection
if connection:
cursor.close()
connection.close()
print("PostgreSQL connection is closed \n")
except psycopg.errors.Error as error:
print("Error fetching data from PostgreSQL table", error)
exit(1)
TimescaleDB database setup
Simulating multiple equipments sending temperature information
My initial idea of time series data was simulating multiple equipments inserting temperature information into TimescaleDB in Python. The keyword to create a TimescaleDB or hypertable is create_hypertable, I also create a traditional one to be able to compare performance:
create table measure_timescale (
time timestamp with time zone not null,
equipement__id int not null,
temperature double precision null
);
select create_hypertable('measure_timescale', by_range('time'));
create index measure_timescale_idx_key01 on measure_timescale (equipement__id, time desc);
create table measure_postgresql (
time timestamp with time zone not null,
equipement__id int not null,
temperature double precision null
);
create index measure_postgresql_idx_key01 on measure_postgresql (equipement__id, time desc);
create table equipment (
id int not null,
description varchar(50) not null
);
insert into equipment values (1, 'Equipment 1'), (2, 'Equipment 2'), (3, 'Equipment 3'), (4, 'Equipment 4'), (5, 'Equipment 5');
Final data model:
yjaquier=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------------------+-------+----------
public | equipment | table | yjaquier
public | measure_postgresql | table | yjaquier
public | measure_timescale | table | yjaquier
(3 rows)
The Python script to load those two tables is:
from time import sleep
import psycopg
import random
import os
from datetime import datetime, timezone
random.seed()
try:
# Connect to an existing database
connection = psycopg.connect(user="yjaquier", password="secure_password", port="5433", dbname="yjaquier")
except psycopg.errors.Error as error:
print("Error fetching data from PostgreSQL table", error)
exit(1)
# Open a cursor to perform database operations
cursor = connection.cursor()
rows_commit = 100
loop=1
while not(os.path.exists('stop')):
equipment__id = random.randint(1,5)
temperature = random.uniform(10, 100)
dt = datetime.now(tz=timezone.utc)
sql_command01 = "insert into measure_timescale values(%s, %s, %s)"
sql_command02 = "insert into measure_postgresql values(%s, %s, %s)"
data = (dt.strftime('%Y-%m-%d %H:%M:%S%z'), str(equipment__id), str(temperature))
# print(sql_command01)
# print(sql_command02)
cursor.execute(sql_command01, data)
cursor.execute(sql_command02, data)
if loop % rows_commit == 0:
connection.commit()
print("Committing %s rows..." % (rows_commit))
loop += 1
sleep(1)
# closing database connection
if connection:
cursor.close()
connection.close()
print("PostgreSQL connection is closed \n")
But at the end I realized I would need to let it run for days to have a sufficient bunch of data. And loading an already available dataset is much quicker…
Loading NYC yellow cab information
To download the NYC yellow cab information from Internet directly on my Linux box and to go through my proxy server I have to create .wgetrc in the home directory of my account:
use_proxy=yes http_proxy=http://proxy_user:proxy_password@proxy_server:proxy_port https_proxy=http://proxy_user:proxy_password@proxy_server:proxy_port
For my trial and because I have also limited disk space I have downloaded the full 2023 year with:
for i in 01 02 03 04 05 06 07 08 09 10 11 12
do
wget --no-check-certificate https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-${i}.parquet
done
Create the NYC tables, one with Timescale features using create_hypertable function and a traditional one to compare performance
create table if not exists trips_timescale (
started_at timestamp with time zone not null,
ended_at timestamp with time zone not null,
passenger_count float, --Some value are NaN
distance float not null,
tip_amount float,
total_amount float
);
select create_hypertable('trips_timescale', 'started_at');
create table if not exists trips_postgresql (
started_at timestamp with time zone not null,
ended_at timestamp with time zone not null,
passenger_count float, --Some value are NaN
distance float not null,
tip_amount float,
total_amount float
);
Convert all Parquet files to CSV with a code like (beware of disk space as CSV files are 4-5 times bigger than Parquet ones). I also keep only few columns :
import os
import pyarrow.parquet as pq
directory = '.'
parquet_files = [f for f in os.listdir(directory) if f.endswith('.parquet')]
for filename in parquet_files:
if os.path.isfile(os.path.join(directory, filename)):
print(filename)
trips = pq.read_table(filename)
trips = trips.to_pandas()
trips = trips[["tpep_pickup_datetime", "tpep_dropoff_datetime", "passenger_count", "trip_distance", "tip_amount", "total_amount"]]
trips.to_csv(filename.replace("parquet", "csv"), index=False)
print(filename.replace("parquet", "csv"))
Then insert those CSV files in trips_timescale table with highly performant COPY command, in Python, with something like:
import psycopg
import os
connection = psycopg.connect(user="yjaquier", password="secure_password", port="5433", dbname="yjaquier")
cursor = connection.cursor()
directory = '.'
copy_sql = "COPY trips_timescale FROM stdin WITH CSV HEADER DELIMITER as ','"
csv_files = [f for f in os.listdir(directory) if f.endswith('.csv')]
for filename in csv_files:
print("Loading " + filename)
f = open(filename, "r")
with cursor.copy(copy_sql) as copy:
while data := f.read():
copy.write(data)
f.close()
connection.commit()
connection.close()
It has taken around 4 minutes and 20 seconds on my test server to load 2023 year. Only for one year there is a good bunch of rows:
yjaquier=# select count(*) from trips_timescale; count ---------- 38310226 (1 row)
There is also a more highly performant tool call timescaledb-parallel-copy. I have been able to load the same data in 1 minutes and 5 seconds (almost 5 times faster):
[postgres@server01 ~]$ time for file in `ls *.csv`; do timescaledb-parallel-copy -connection "username=yjaquier dbname=yjaquier port=5433" -file $file -skip-header -table trips_timescale -workers 12; done COPY 3066766 COPY 2913955 COPY 3403766 COPY 3288250 COPY 3513649 COPY 3307234 COPY 2907108 COPY 2824209 COPY 2846722 COPY 3522285 COPY 3339715 COPY 3376567 real 1m4.755s user 0m30.274s sys 0m9.290s
Insert the same rows in the traditional table with INSERT command. I have tried using modified Python script and COPY command but it has taken the exact same time to insert in the traditional table. If TimescaleDB is faster while inserting it is NOT for massive insert but more for a continuous flow of data that will land in a smaller object (auto partitioning of TimescaleDB tables):
yjaquier=# insert into trips_postgresql select * from trips_timescale; INSERT 0 38310226
Checking the tables size:
yjaquier=# select pg_size_pretty(pg_indexes_size('trips_postgresql'));
pg_size_pretty
----------------
0 bytes
(1 row)
yjaquier=# select pg_size_pretty(pg_table_size('trips_postgresql'));
pg_size_pretty
----------------
2788 MB
(1 row)
yjaquier=# select pg_size_pretty(pg_total_relation_size('trips_postgresql'));
pg_size_pretty
----------------
2788 MB
(1 row)
yjaquier=# select
pg_size_pretty(table_bytes) as "Table size",
pg_size_pretty(index_bytes) as "Indexes size",
pg_size_pretty(toast_bytes) as "Total size",
pg_size_pretty(total_bytes)
FROM hypertable_detailed_size('trips_timescale');
Table size | Indexes size | Total size
------------+--------------+------------
2789 MB | 864 MB | 3654 MB
(1 row)
yjaquier=# select
chunk_name,
pg_size_pretty(table_bytes) as "Table size",
pg_size_pretty(index_bytes) as "Indexes size",
pg_size_pretty(total_bytes) as "Total size"
from chunks_detailed_size('trips_timescale')
order by chunk_name;
.
.
.
You can notice that the hyperscale table has an index on started_at column:
yjaquier=# \d trips_timescale
Table "public.trips_timescale"
Column | Type | Collation | Nullable | Default
-----------------+--------------------------+-----------+----------+---------
started_at | timestamp with time zone | | not null |
ended_at | timestamp with time zone | | not null |
passenger_count | double precision | | |
distance | double precision | | not null |
tip_amount | double precision | | |
total_amount | double precision | | |
Indexes:
"trips_timescale_started_at_idx" btree (started_at DESC)
Triggers:
ts_insert_blocker BEFORE INSERT ON trips_timescale FOR EACH ROW EXECUTE FUNCTION _timescaledb_functions.insert_blocker()
Number of child tables: 59 (Use \d+ to list them.)
So to be fair created an index on trips_postgresql traditionnal table (no impact on performance for some queries):
yjaquier=# create index trips_postgresql_idx_started_at on trips_postgresql(started_at desc);
CREATE INDEX
yjaquier=# select pg_size_pretty(pg_indexes_size('trips_postgresql'));
pg_size_pretty
----------------
653 MB
(1 row)
yjaquier=# select pg_size_pretty(pg_total_relation_size('trips_postgresql'));
pg_size_pretty
----------------
3440 MB
(1 row)
While loading data you will surely fill your pg_wal directory even with wal_level = minimal, as it is a trial and if you have not put in place an archiving strategy feel free to empty pg_wal directory (located inside PGDATA directory) and/or keep the current one or switch to a new one if you have deleted all files:
yjaquier=# SELECT pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
0000000100000003000000A3
(1 row)
yjaquier=# select pg_switch_wal();
pg_switch_wal
---------------
3/A3E079E8
(1 row)
yjaquier=# SELECT pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
0000000100000003000000A4
(1 row)
TimescaleDB performance versus PostgreSQL
Without compression
To start fresh I vacuum and gather statistics on my two tables:
yjaquier=# vacuum (analyze) trips_timescale; VACUUM yjaquier=# vacuum (analyze) trips_postgresql; VACUUM
To get execution timing in psql use:
yjaquier=# \timing Timing is on.
I have used the classical query of the Youtube video:
select /* Yannick */
time_bucket('1 month', started_at) as bucket,
avg(total_amount),
max(total_amount),
min(total_amount)
from trips_timescale
where started_at >= '2023-01-01' and started_at <= '2023-12-31' and total_amount > 0
group by bucket
order by bucket;
select /* Yannick */
time_bucket(interval '1 month', started_at) as bucket,
avg(total_amount),
max(total_amount),
min(total_amount)
from trips_postgresql
where started_at >= '2023-01-01' and started_at <= '2023-12-31' and total_amount > 0
group by bucket
order by bucket;
But first execution provided a strange result:
bucket | avg | max | min ------------------------+--------------------+-----------+------ 2022-12-01 01:00:00+01 | 27.519151389151425 | 297.07 | 1.01 2023-01-01 01:00:00+01 | 27.449526070897573 | 1169.4 | 0.01 2023-02-01 01:00:00+01 | 27.34237982221717 | 2208.1 | 0.01 2023-03-01 01:00:00+01 | 28.26085276344107 | 2100 | 1 2023-04-01 02:00:00+02 | 28.793417286784024 | 2451 | 0.01 2023-05-01 02:00:00+02 | 29.444296735557856 | 6304.9 | 0.01 2023-06-01 02:00:00+02 | 29.59118161563325 | 386987.63 | 0.01 2023-07-01 02:00:00+02 | 29.176164417212338 | 1079.45 | 0.01 2023-08-01 02:00:00+02 | 29.247406395127765 | 1435.19 | 0.01 2023-09-01 02:00:00+02 | 30.362431044148803 | 187513.9 | 0.01 2023-10-01 02:00:00+02 | 29.782298225879245 | 6339 | 0.01 2023-11-01 01:00:00+01 | 29.30402132805749 | 1814.27 | 0.01 2023-12-01 01:00:00+01 | 29.23256668202939 | 4269.16 | 0.01 (13 rows)
I restrict the date to 2023 and I get a time bucket in December 2022 ??!! I have tried a conversion with:
select time_bucket(interval '1 month', started_at::timestamp) as bucket, avg(total_amount), max(total_amount), min(total_amount) from trips_timescale where started_at >= '2023-01-01' and started_at <= '2023-12-31' and total_amount > 0 group by bucket order by bucket;
But this is killing performance ! And found this in Official TimescaleDB documentation:
Timezones
The origin time depends on the data type of your time values.If you use TIMESTAMP, by default, bucket start times are aligned with 00:00:00. Daily and weekly buckets start at 00:00:00. Shorter buckets start at a time that you can get to by counting in bucket increments from 00:00:00 on the origin date.
If you use TIMESTAMPTZ, by default, bucket start times are aligned with 00:00:00 UTC. To align time buckets to another timezone, set the timezone parameter.
So change timezone with (pg_timezone_names):
yjaquier=# show timezone; TimeZone -------------- Europe/Paris (1 row) yjaquier=# set timezone='GMT'; SET
And got good result without any transformation:
bucket | avg | max | min ------------------------+--------------------+-----------+------ 2023-01-01 00:00:00+00 | 27.44952607089689 | 1169.4 | 0.01 2023-02-01 00:00:00+00 | 27.34237982221717 | 2208.1 | 0.01 2023-03-01 00:00:00+00 | 28.26085276344107 | 2100 | 1 2023-04-01 00:00:00+00 | 28.79341728678402 | 2451 | 0.01 2023-05-01 00:00:00+00 | 29.444296735557856 | 6304.9 | 0.01 2023-06-01 00:00:00+00 | 29.59118161563325 | 386987.63 | 0.01 2023-07-01 00:00:00+00 | 29.176164417212338 | 1079.45 | 0.01 2023-08-01 00:00:00+00 | 29.247406395127765 | 1435.19 | 0.01 2023-09-01 00:00:00+00 | 30.36243104414881 | 187513.9 | 0.01 2023-10-01 00:00:00+00 | 29.782298225879245 | 6339 | 0.01 2023-11-01 00:00:00+00 | 29.30402132805749 | 1814.27 | 0.01 2023-12-01 00:00:00+00 | 29.232050702766923 | 4269.16 | 0.01 (12 rows)
For query execution time and IOPS statistics I’m obviously using pg_stat_statements. You can execute both query one time to warm the buffer and reset pg_stat_statements with:
yjaquier=# select pg_stat_statements_reset(); pg_stat_statements_reset ------------------------------- 2024-12-02 10:54:21.549674+00 (1 row)
After 10 executions with warm buffer cache I got:
yjaquier=# select query, calls, mean_exec_time, shared_blks_read/calls AS "Blocks per call"
from pg_stat_statements
where query like 'select /* Yannick */%';
query | calls | mean_exec_time | Blocks per call
---------------------------------------------------+-------+--------------------+-----------------
select /* Yannick */ +| 10 | 11512.110878700001 | 355322
time_bucket(interval $1, started_at) as bucket,+| | |
avg(total_amount), +| | |
max(total_amount), +| | |
min(total_amount) +| | |
from trips_postgresql +| | |
where started_at >= $2 and started_at <= $3 +| | | and total_amount > $4 +| | |
group by bucket +| | |
order by bucket | | |
select /* Yannick */ +| 10 | 5864.9380092 | 356739
time_bucket($1, started_at) as bucket, +| | |
avg(total_amount), +| | |
max(total_amount), +| | |
min(total_amount) +| | |
from trips_timescale +| | |
where started_at >= $2 and started_at <= $3 +| | | and total_amount > $4 +| | |
group by bucket +| | |
order by bucket | | |
(2 rows)
So execution time is divided by 2 when using TimescaleDB (11.5 seconds versus 5.8 seconds) !
With compression
Activate compression and add a compression policy with below commands. The compression strategy must be chosen versus the queries you plan to have on your data:
yjaquier=# alter table trips_timescale set (timescaledb.compress, timescaledb.compress_orderby = 'started_at desc', timescaledb.compress_segmentby = 'total_amount');
ALTER TABLE
yjaquier=# select add_compression_policy('trips_timescale', compress_after => interval '1 minutes');
add_compression_policy
------------------------
1000
(1 row)
yjaquier=# select * from timescaledb_information.hypertables;
hypertable_schema | hypertable_name | owner | num_dimensions | num_chunks | compression_enabled | tablespaces
-------------------+-------------------+----------+----------------+------------+---------------------+-------------
public | trips_timescale | yjaquier | 1 | 59 | t |
(1 rows)
Wait for background processes doing the job and check as well the result with:
yjaquier=# select
total_chunks,
number_compressed_chunks,
pg_size_pretty(before_compression_table_bytes) AS "Before compression table size",
pg_size_pretty(after_compression_table_bytes) AS "After compression table size",
pg_size_pretty(before_compression_index_bytes) AS "Before compression indexes size",
pg_size_pretty(after_compression_index_bytes) AS "After compression indexes size",
pg_size_pretty(before_compression_total_bytes) AS "Before compression total size",
pg_size_pretty(before_compression_total_bytes) AS "After compression total size"
from hypertable_compression_stats('trips_timescale');
total_chunks | number_compressed_chunks | Before compression table size | After compression table size | Before compression indexes size | After compression indexes size | Before compression total size | After compression total size
--------------+--------------------------+-------------------------------+------------------------------+---------------------------------+--------------------------------+-------------------------------+------------------------------
59 | 59 | 2789 MB | 411 MB | 864 MB | 27 MB | 3654 MB | 3654 MB
(1 row)
yjaquier=# select
pg_size_pretty(table_bytes) AS "Table size",
pg_size_pretty(index_bytes) AS "Indexes size",
pg_size_pretty(total_bytes) AS "Total size"
from hypertable_detailed_size('trips_timescale');
Table size | Indexes size | Total size
------------+--------------+------------
409 MB | 27 MB | 1132 MB
(1 row)
Then I re-executed 10 times my query and the result is a drastic fall in blocks read (from 356739 to 7157) as well as a bit more than 20% response time improvment:
yjaquier=# select query, calls, mean_exec_time, shared_blks_read/calls AS "Blocks per call"
from pg_stat_statements
where query like 'select /* Yannick */%';
query | calls | mean_exec_time | Blocks per call
---------------------------------------------+-------+----------------+-----------------
select /* Yannick */ +| 10 | 4473.7342535 | 7157
time_bucket($1, started_at) as bucket, +| | |
avg(total_amount), +| | |
max(total_amount), +| | |
min(total_amount) +| | |
from trips_timescale +| | |
where started_at >= $2 and started_at <= $3+| | | and total_amount > $4 +| | |
group by bucket +| | |
order by bucket | | |
(1 row)
graphical comparison of the performance I got:

References
- Timescale documentation
- TimescaleDB GitHub
- Getting started with TimescaleDB and defining best practices






Comments