New TimescaleDB Persistence Service for openHAB

Add-ons Persistence Services
1

Hi all,

I just opened a PR ([timescaledb] Initial contribution by ulbi · Pull Request #20412 · openhab/openhab-addons · GitHub) for a new native TimescaleDB persistence add-on.

Why I built it

JDBC can connect to TimescaleDB, but it treats it like a normal SQL database.

I wanted a persistence service that actually uses TimescaleDB as a time-series database.

What it does

  • Stores states in a TimescaleDB hypertable
  • Supports per-item downsampling (AVG, MIN, MAX, SUM) via metadata
  • Supports global and per-item retention
  • Supports optional compression of older data
  • Supports normal openHAB persistence queries and state writes/removes
  • Works well for SQL/Grafana use cases

Goal

Better long-term performance and lower storage usage for larger installations.
If you use TimescaleDB (or plan to), I would really appreciate feedback on:

  • downsampling strategy
  • retention setup
  • query/dashboard use cases

Thanks!
René

8 Likes
2

Super interesting! I have been wanting to drop influxdb for while and ideally in favour of postgres/timescaledb since I have an instance of that running anyway.

I have a question though regarding how to pull data out of it again which I hope you might be able to shed some light on.

With 2 items defined as follows, I am able to pass additional data thorugh to influxdb:

---
items:
  cs_1_battery:
    channel: mqtt:topic:cs-1:battery
    dimension: Dimensionless
    format: '%.0f%%'
    groups: [gFrontDoor, gBatteries]
    icon: battery
    label: Front Door Battery
    metadata:
      alexa:
        value: BatteryLevel
      influxdb:
        config:
          kind: zigbee
          location: indoors
          room: Corridor
        value: battery
    tags: [Measurement, Voltage]
    type: Number

  cs_2_battery:
    channel: mqtt:topic:cs-2:battery
    dimension: Dimensionless
    format: '%.0f%%'
    groups: [gFridgeDoor, gBatteries]
    icon: battery
    label: Fridge Door Battery
    metadata:
      alexa:
        value: BatteryLevel
      influxdb:
        config:
          kind: zigbee
          location: indoors
          room: Kitchen
        value: battery
    tags: [Measurement, Voltage]
    type: Number

This allows me to use grafana to create a graph based on the battery influxdb value and I will automticaly get all items that have a battery. Similar things are happening with temperature as well.

Are you aware of a way to do something similar to this with postgres/timescaledb instead of influxdb?

3

@peterhoeg For the timescaledb I’ve not implemented it this way yet, but could be done. At the moment the timescaledb persistence just reads the metadata, but does not write them to the database. We could extend the current persistence to write the metadata value as well.
In order to safe space, I use a item_meta table to keep the item names etc.

So this said, I’m currently waiting for the maintainers to approve the new persistence bundle. Once this is done, I could add an enhancement to it.

4

I unfortunately don’t know enough about the persistence layer to say anything remotely intelligent about but that’s not going to stop me! Off-hand, I would imagine that the influxdb specific metadata configuration could be made generic as all persistence services could have a mechanism to handle metadata, but I would imagine that this would probably have an easier way to mainline if you make it specific to your timescaledb persistence bundle.

1 Like
5

in any case, as soon your have metadata writing supported, I’ll flip in a heart beat. One of the good things about having generated configuration is that it’s very easy to make these types of moves.

1 Like
6

Hi @ulbi

2 quick things

will the addon support a cloud based timescaled instance?

I could not find the link/url to the built addon for 5.1.x (or any version) did i miss it?

Brett

7

@BrettLHolmes I did not provide a build version so far. The PR is under review right know, when it makes it into main, the next snapshot will contain it.

For the cloud connection - I guess you can use it, never tested it - just give it a try. Whether this is really a fast approach depends on the physical distance between your openhab instance and the cloud endpoint.

8

Thanks @ulbi

The PR said there was a link above so i will wait to test.

tailscaled definitely an alternative to Influxdb now

Brett

1 Like
9

Very interesting!

I’m currently on postgres via the jdbc binding. Is there a migration path for this setup into timescaledb?

10

@seime I migrated from mongodb, which is completely different than jdbc. I did this by direct bulk copy with a python script (thanks to Claude code). For a more generic approach you could use the openhab persistence REST API get all data for an item and write it to the other service.

1 Like
11

I’m happy to share that the PR got approved and the new timescaledb persistence addon is available in openhab-addons. You can find the current snapshot build here: JFrog

2 Likes
12

@peterhoeg fyi… I created a feature request - so I can work on this later this weekend [timescaledb] Support item tag and metadata mapping similar to InfluxDB for TimescaleDB · Issue #20460 · openhab/openhab-addons · GitHub

1 Like
13

@peterhoeg just changed it to behave similar to the influxdb - will be interessting to see how it works for you.

BTW - I changed the way how the aggregation gets configured. See [timescaledb] Add metadata tag support and JSONB config storage by ulbi · Pull Request #20464 · openhab/openhab-addons · GitHub

14

I see that it was merged for 5.2, so I’ll give it a go when it is released. Or if I magically find some free time before that, it will be then! :wink:

In any case, thanks a ton for doing this. Will be great to drop influx.