Evaluating TimescaleDB: the TimescaleDB way to do a time series join

TimescaleDB and PostgreSQL
,
1

Hi All, I’m evaluating a couple alternatives to SQL Server that we are currently using in our workflow. We have a 3rd party software that streams data into SQL Server (and can with PostgreSQL as well) in the form of 1 table per device. I wish I could change this, but currently it is one table per each device. The table will have a timestamp (datetime2 with records at every second), record number, and a varying amount of data value fields. A clustered index is created on timestamp and record number.

When bringing this data to clients, we need these tables joined on timestamp. But because some devices (ie tables) will begin earlier or later than others, I need a full join across these tables. My current solution is a VIEW of the following form using an “anchor” table:

SELECT
     allTS.TmStamp
    ,allTS.TmStamp AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' As 'TmStampLocal'
    ,d1.Measurement_1 As 'Device 1 Measurement 1'
    ,d1.Measurement_2 As 'Device 1 Measurement 2'
    ,d2.Measurement_1 As 'Device 2 Measurement 1'
    ,d3.Measurement_1 As 'Device 3 Measurement 1'
    ,d3.Measurement_2 As 'Device 3 Measurement 2'
    ,d3.Measurement_3 As 'Device 3 Measurement 3'
FROM
    (
        SELECT dbo.[Device1].TmStamp FROM dbo.[Device1]
    UNION
        SELECT dbo.[Device2].TmStamp FROM dbo.[Device2]
    UNION
        SELECT dbo.[Device3].TmStamp FROM dbo.[Device3]
    ) allTS
    LEFT OUTER JOIN dbo.[Device1] d1 ON allTS.TmStamp = d1.TmStamp
    LEFT OUTER JOIN dbo.[Device2] d2 ON allTS.TmStamp = d2.TmStamp
    LEFT OUTER JOIN dbo.[Device3] d3 ON allTS.TmStamp = d3.TmStamp

I have tried this with FULL JOINs and COALESCE-ing the TmStamps, but it doesn’t seem to be any quicker. These individual Device tables are usually in the order of 1 Million to 10 Million rows and can be up to 75 measurement variables. This generally stays performant with SQL Server, but depending on client load, we have had to throw quite a few processors at it. Queries against this view range from streaming 5 to 10 records every few seconds, to 3 or 6 hour aggregate views, to whole job downloads. We usually join 2 to 5 tables, but have had some jobs go up to 13 different devices.

The question is: what is the best way to tackle this in TimescaleDB? And would it be a fit for our workflow?