Evaluate usage of TimescaleDB instead of PostgreSQL

[swiffer]

TimescaleDB might be a perfect fit for TeslaMate as it's a PostgreSQL extension especially for time series data and analytics.

Why?

After having approx. 1 year of data collected (1 car) the positions table has grown to 4 mio rows (98% of all data collected).

As many users are running TeslaMate on small servers (Rasperry PI / cheap cloud instances / VMs) Grafana Dashboards perform slowly (#4187 (comment)) and writing dashboard queries can be a complex task (#4200). TimescaleDB aims to solve these problems by providing the possibilities for

• automatically partitioning a table (Hypertable)
• advanced analytical functions (Hyperfunctions)

Some users already started partitioning their tables for increased performance (#4187 (comment)). TeslaMate should aim at providing respective functionality out of the box.

Limitations (WIP)

Foreign Keys to partitioned table (positions)

TimescaleDB currently mentions "Foreign key constraints referencing a hypertable are not supported." in their limitations. However support is mentioned in Release Notes of 2.16.0.

TeslaMate is currently having 3 Foreign Key constraints affected by this limitation.

Docs issue for clarification: timescale/docs#3483
Also reached out to the original author: timescale/timescaledb#6989 (comment)

Hypertable requires timezonetz

It is recommended to store timestamps in utc as timezonetz (with time zone). This is currently not the case. TimescaleDB can therefore not use the date column of the positions table as is and we might need to migrate it to timestamptz. This is recommended in PostgreSQL as well. See here for details:

• https://docs.timescale.com/use-timescale/latest/hypertables/create/#create-a-hypertable
• https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

Docs Issue for clarification: timescale/docs#3482

Partitioning strategy

From the Docs: We recommend setting the chunk_time_interval so that 25% of main memory can store one chunk.

Current Memory Requirement for TeslaMate is 1GB - let's assume 50% of that is available for PostgreSQL means 512MB. By recommendation that means 128MB chunk size. With one year of data summing up to 700mb (data + indexes) that means roughly 2 months as chunk size.

@coreGreenberet used monthly partitions in his instance. I would start testing with a chunk every 8 weeks.

(e.g. SELECT create_hypertable('positions', by_range('date', INTERVAL '8 week'), if_not_exists => TRUE, migrate_data => TRUE);

A detailed guide is available here: https://www.timescale.com/blog/timescale-cloud-tips-testing-your-chunk-size/

Testing & Feedback

With TimescaleDB 2.17.0 (incl. Postgres 17) support beeing published soon I would like to start exploring what effort is needed to migrate to TimescaleDB and how query performance is influenced by partitioning.

If there are users willing to participate in testing (especially with slow hardware) or there are arguments pro / against this idea please reply within this discussion.

@coreGreenberet - tagging you as you are making use of partitioning already so seem to have some experience with databases.

[DrMichael]

Happy to test. I don't know timescalDB though (yet)...

[coreGreenberet]

I haven't played with timescaledb yet, but i might give it a shot.

Looking at it it's basically postgresql + addons, so it should be easy to migrate.

Edit:
I'm not sure if 8 weeks of chuck size is to much. 4 weeks (1 month) looks cleaner IMHO. It should also be better for the statistic views which (default) grouped by month

[swiffer]

As TimescaleDB 2.17.0 with pg17 support is available:

• Create a new Docker Compose project used for evaluation, containing only a database container

services:
  database:
    image: timescale/timescaledb:2.17.0-pg17
    restart: always
    environment:
      - POSTGRES_USER=teslamate
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=teslamate_timescaledb
      - TZ=Europe/Berlin
    volumes:
      - teslamate-db:/var/lib/postgresql/data
    ports:
      - "127.0.0.1:5437:5432"

volumes:
  teslamate-db:
    driver: local

• Follow Restore Docs to Restore a Backup taken from Prod and Restore within your evaluation project

• Run the following SQL to create a Hypertable

create extension timescaledb;

alter table positions drop positions_pkey cascade;
alter table positions add primary key (id, date);

SELECT create_hypertable('positions', by_range('date', interval '8 week'), migrate_data => TRUE);

• Connect to Database and Post info about chunks created

select * from "_timescaledb_internal".hypertable_chunk_local_size hcls;

[swiffer]

chunk info with 8 weeks partitioning time

[swiffer]

trying several queries shows no huge differences here but i'm hosting teslamate on a very performant setup. therefore please feel free to share and try queries from dashboards currently performing slow on your machine.

[DrMichael]

Does it make any difference regarding the queries? Or is it just a configuration issue?

[swiffer]

Don't know if I've understood your question.

For me it looks like partitioning is adding an overhead and without further changes not resulting in query execution performance improvements. i guess that holds true as long as data / indexes fit into memory. by excluding streaming data from multiple queries and having a small (4mb) additional index in place for positions i think it's currently not really needed to consider migrating to a hypertable. that's what i observe on my instance. would be great if someone with more constraint hardware could as well (maybe after next version is released).

where i do see potential is by making use of the hyperfunctions timescaledb provides as these do make writing queries way easier and lead to shorter queries. that would be benefitial for contributors. what i've haven't tested yes: do rewritten queries that are making use of hyperfunctions result in query execution performance improvements.

[DrMichael]

Don't know if I've understood your question.

Well, there is a large base of developers, who can construct queries in Postgresql. If special timescaleDB knowledge is necessary for the queries, the developer base would be smaller...

would be great if someone with more constraint hardware could as well (maybe after next version is released).

Well I am running teslamate on a RPi3B+ with 1GB memory as a backup. I could give it a try, but I don't know anything about timescaleDB yet. Though willing to learn... :-)

where i do see potential is by making use of the hyperfunctions timescaledb provides as these do make writing queries way easier and lead to shorter queries. that would be benefitial for contributors. what i've haven't tested yes: do rewritten queries that are making use of hyperfunctions result in query execution performance improvements.

Hmmm, hyperfunctions... That could be interesting, but not trivial.

[swiffer]

it's an addition to postgres all postgres functionality is still there - but yes, if we are making use of timescaledb specific features / functions some knowledge would be required. in general i wouldn't change much, the additions are pretty streight forward.

would be great if you could give it a try (separate instance), just to see how it performs (based on my guide). i might will organize an rpi to test on my end as well.