[SOLVED] How and when to create non-unique tsdb hypertable two item indexes?

How and when to create non-unique tsdb hypertable two item indexes?

I have a table that will sort of resemble a [metadata table][1].

        CREATE TABLE IF NOT EXISTS sensor1 (
        datetime TIMESTAMPTZ NOT NULL,
        device_id TEXT NOT NULL,
        field_name TEXT NOT NULL,
        device_value FLOAT NOT NULL
        );

In this table the datetime and device_id will not be unique.

I would like to make this a hypertable and be able to make indexes out of the device_id and datetime columns (the two indexes combined will also not be unique).

My question is when and how should I make the indexes? Before or after making the hypertable?

My read query will be

        SELECT * 
        FROM sensor1 
        WHERE device_id = '5555' 
        AND datetime >= 'Feb 10 2024'
        AND datetime < 'Feb 20 2024';

SELECT device_id, 
datetime,
MAX(CASE WHEN name = '[field_name]' THEN '[device_value]' AS '[field_name]',
MAX(CASE WHEN name = '[field_name]' THEN '[device_value]' AS '[field_name]',
MAX(CASE WHEN name = '[field_name]' THEN '[device_value]' AS '[field_name]'
FROM sensor1
GROUP BY device_id, datetime
ORDER BY datetime DESC;

  [1]: https://www.timescale.com/learn/best-practices-for-time-series-metadata-tables

Solution