2

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?

1 Answer 1

2
  • 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...

3
  • unfortunately there are calculations where I will need all of those columns at the same time. I will look in to whether array_agg could also work but I don't think so. When you say split it into real tables you mean to actually have a table each for each pair_id. Without the inheritance feature, I think that would be difficult to maintain no?
    – sev
    May 19, 2021 at 0:54
  • When I say split it into real tables, what I mean, is have related pairs have real columns in real tables, so actually design a schema like table1(ts timestamptz, foo int, bar float, baz text) Where foo bar and baz are related events. The array or json approach may be your only option as there are column limits on the number of columns that a query can contain, but if they are composite types, then you'd be fine (ie a table type with foo, bar and baz in it will be fine to return as a "single" column).
    – David K
    May 19, 2021 at 13:10
  • The array approach should be the same as columnar, but your application will need to do some different searching in order to get the values out, you'll end up, essentially, with a map for each timestamp instead of the set of columns and values.
    – David K
    May 19, 2021 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.