TimescaleDB database design - should I use inheritance?

Question

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?

Answer 1

• A) No, I don't believe that type of inheritance is supported in Timescale.
• B) This won't help query performance at all, it'd probably hurt it significantly and be a big mess.
• C) I'd recommend just having each data type as a column in the table and for each pair id filling in the correct data type. Then you can store which pair ids use which data types in a second table, and when you're constructing your query you grab the correct column. You can also do the correct continuous aggregates, grouped by pair id, with the correct aggregation for each data type, which should mean only one continuous aggregate.

Finally, you can aggregate and then join like that, but probably not for all of the thousands of columns, there'll be some limit where that gets quite inefficient and also very difficult to deal with...are you actually using thousands of columns in a single calculation? And is it really more efficient to have them in row form? You might consider using array_agg(row(pair_id, data) ORDER BY pair_id) GROUP BY time or the like to do something like what you want. you could also use JSON or some other thing as your return type if that's better, but I would also see if you can limit your queries to say 10s or hundreds of columns. Or split the whole thing into real tables with related columns and use the composite types to fix some of this...