postgresql – TimescaleDB database design – should I use inheritance?

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_event_data1 pair_id1_event_data2 pair_id2_event_data1 pair_id2_event_data2

Or:

|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

  1. Create a parent table
  2. For each data_type create a data_type_table inheriting from the parent table
  3. For each pair_id create a table inheriting from the appropriate data_type_table

Questions

  • 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?