We are taking another look at our current TimescaleDB setup and we have been wondering what the optimal Timescale configuration would be going forward.
We save IoT data from a lot of different sources in the following format
id (int8)
value (float8)
time (int8)
So far we have just used an index based on id and time. We are probably going to change time to type timestamptz going forward; in out testing there seems to be a moderate efficiency gain when using timestamptz vs int8, though we can’t figure out why.
What will be the optimal configuration if I want query years of data efficiently for specific id’s? No time_bucket or anything like that, just something like
SELECT id, value, time FROM timeseries_data WHERE id = 99999 AND time BETWEEN
(‘2022-11-25T00:00:00.000’::timestamp AT TIME ZONE ‘Europe/Copenhagen’)
AND (‘2025-11-25T23:59:59.999’::timestamp AT TIME ZONE ‘Europe/Copenhagen’);
We were wondering if implementing hypercore would gain us anything, since that seems to introduce the additional dimension of the id’s-.