I have the following TimescaleDB hypertable:
CREATE TABLE public.data ( event_time timestamp with time zone NOT NULL, pair_id integer NOT NULL, entry_id bigint NOT NULL, event_data1 int NOT NULL, event_data2 int NOT NULL, CONSTRAINT con1 UNIQUE (pair_id, entry_id ), CONSTRAINT pair_id_fkey FOREIGN KEY (pair_id) REFERENCES public.pairs (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION <unique index on event_time, pair_id, entry_id> ) <Some continuous aggregates>
But when querying this data I would actually never need to just get data across pair_id’s such as:
SELECT * FROM data WHERE <some condition on the time>
Instead I would like to query the data such that I get the data joined on the event_time for each pair_id – something like this:
|event_time | pair_id1_continous_agregate1 | pair_id1_continous_agregate2 | pair_id2_continous_agregate1 | pair_id2_continous_agregate2 | … |
| — | — | — | — | — | — |
Sorry for not writing the actual query, I’m still learning how to do this.
Given I have 1000s of pair_ids, does this database design make sense to have efficient query performance?
The alternative I am considering is to use inheritance like this https://www.postgresql.org/docs/current/ddl-inherit.html:
I have about 5 types of data stored in this table, some of them have an extra column or two
- Create a parent table
- For each data_type create a data_type_table inheriting from the parent table
- For each pair_id create a table inheriting from the appropriate data_type_table
- A) Is this type of inheritance even supported in TimescaleDB?
- B) Would this improve my query performance?
- C) Is there another alternative which would be better?