Support for TimescaleDB (Postgres extension) [PATCH]

[jensb]

Is your feature request related to a problem? Please describe.
Traditional SQL databases can handle time series data only to a certain extent.
Timescale is a Postgres extension which optimizes Postgresql tables for time series data, allowing more and faster time range related requests and making these easier.

Describe the solution you'd like
I would like iobroker to offer a checkbox during setup "Use TimescaleDB extension" when setting up Postgres, and if the user chooses to do this, execute this command once for each table containing time series data during database creation:

SELECT create_hypertable('timeseries_table_name','timestamp_column');

This is already all. Users can then use Timescale functions and features during requests, but inserting data is not changed.

Describe alternatives you've considered
Alternatively this would have to be done manually for each table using ˋpsqlˋ.

Additional context
This feature would make it more clearly visible that there are traditional SQL database options for time series data. Influxdb is the obvious choice for many people but it has drawbacks that may make users look for time series capable alternatives. This woul make one such alternative obvious.

[Apollon77]

How the quwries exactly need to look? Did you tried it out manually?

[jensb]

not yet because I could not get an up to date Psql to work on my raspi using packages, there were too many dependancy errors.
Here are some examples: https://www.timescale.com/

[Apollon77]

hm ... but then it is very difficult to verify that a change works if I would do them because Ialso do not use postgrSQL at all on my system ...

Ok, lets see when someone finds time:

For testing: maybe use https://hub.docker.com/r/timescale/timescaledb image as test target

[Blockmove]

SELECT create_hypertable('timeseries_table_name','timestamp_column');

This is already all. Users can then use Timescale functions and features during requests, but inserting data is not changed.

Sorry, but that's not already all :-(
After your SQL-Command you get an Error "ERROR: integer dimensions require an explicit interval"

[jensb]

Weird. I seem to recall I got this working on a PC VM, but eventually gave up on my Raspi IoT device because of packaging issues. Maybe the syntax has changed?

[rovo89]

ERROR: integer dimensions require an explicit interval

Probably it's because a Unix timestamp (integer) is used for the timestamp instead of a TIMESTAMPTZ field:

For integer types, the chunk_time_interval must be set explicitly, as the database does not otherwise understand the semantics of what each integer value represents (a second, millisecond, nanosecond, etc.). So if your time column is the number of milliseconds since the UNIX epoch, and you wish to have each chunk cover 1 day, you should specify chunk_time_interval => 86400000.

https://docs.timescale.com/api/latest/hypertable/create_hypertable/

So the call should be something like this:

SELECT create_hypertable('timeseries_table_name', 'timestamp_column', chunk_time_interval => 604800000);

Where 604800000 means to store data for 7 days * 86400 sec/day * 1000 ms/s per chunk. That's the default. See also: https://docs.timescale.com/use-timescale/latest/hypertables/about-hypertables#best-practices-for-time-partitioning