Create multiple hypertables based on measurement and set chunk sizes accordingly

1

Hi,

I have a huge table which contains signals from various signal sources. I created a hypertable with chunk size set to 1 hour. But I am working towards finding an optimal chunk size and I had a question regarding creating hypertable itself.

Lets say I have table with name “signal_data” that looks like this

time, signalname, value
2022-01-01 13:00:00 valve 1
2022-01-01 13:01:00 valve 0
2022-01-01 13:00:00 airflow 0
2022-01-01 13:02:03 valve 1
2022-01-01 13:02:04 airflow 0
2022-01-01 13:05:00 valve 1

The signal with name “valve” has much more data in comparison with signal “airflow”. Is there a way to create two hypertables from one “signal_data” table setting specific chunk sizes for each of these hypertables. If this is possible, will it improve performance?

2

Hello and welcome to the forum!

This is absolutely possible, you can do something like:

CREATE TABLE valve_signal_data(time timestamptz, signalname text, value integer);
CREATE TABLE airflow_signal_data(time timestamptz, signalname text, value integer);

SELECT create_hypertable('valve_signal_data', 'time', chunk_time_interval=>'1 hour');
SELECT create_hypertable('airflow_signal_data', 'time', chunk_time_interval=>'1 day');

INSERT INTO valve_signal_data SELECT * FROM signal_data WHERE signalname = 'valve';
INSERT INTO valve_signal_data SELECT * FROM signal_data WHERE signalname = 'airflow';

I would be interested in what your definition of “much more” is though - in the example they don’t look so different?

At the end of the day what you do here will most likely come down to how many signals you have and how you want to query them (all at once, or only by signal types).

-James