How does timescale group time-stamps to time-series

TimescaleDB and PostgreSQL
,
1

Hi community. I am about to integrate timescale in my django project, but what’s unclear to me is how timescale groups different timestamps together to form a time-series.

Imagine I have multiple Drinks (coca-cola, lime-juice etc…). I can have a million drinks in my database. Each drink can have multiple time-series related to it. A time-series of consumption data over the years, a time-series of customer data over the years etc… Then imagine I also have food data with the same assumptions. I want to save all those data in a hypertable.

I can start by creating a hypertable and inject all consumption data for Coca-Cola in the year 2018. Now i also want to store customer data for Coca-Cola too, but the time-stamps will collide. I will have 2022-12-03 multiple times. Thus timescale must have a best-practice of how to group timestamps together to from a time-series. Otherwise I could only have a single time-series in a hypertable.

I see two solutions:

  1. I can save a foreign key or an object-id with the timestamp. E.g. 2022-01-01 and 5, whereas 5 is the id of coca cola object.
  2. Or I can create a meta-table which stores the meta-information about the timestamp. The meta object holds the information to which object the timestamp belongs to. E.g. in the meta model I can save the id for the coca-cola object and also other meta information (e.g. the amount of sugar during the whole year in the drink). I see the advantage of storing more meta information as important, so I’d prefer this approach.

My question though is: Is that how timescale is supposed to be used? Or is the idea to have one hypertable per drink in this case? That would surprise me because then I’d have millions of hypertables. Or will I lose performance if I design my setup as explained above? Or to put it simple: What is the best practice of grouping timestamps together to a time-series in timescale.

Thanks a bunch for the feedback

1 Like
2

Hi @micromegas! Both approaches are valid and can serve you in different ways.

The second will make it easier to maintain as you’ll have more flexibility. It’s also a common approach for expanding and adding more metrics.

Let’s say your table would be:

timestamp, metric_id, value

And your metrics table would store the extra metadata.

Depending on the cardinality of your data, it would serve you very well.

Another strategy would be to have a jsonb column that could store any payload/metrics and expand if necessary. Of course, it would need more work to query, but it would also work.

Promscale uses a different approach closer to model 1, and the main advantage of this model is that you can establish custom retention policies for each metric. For example, if you want to store the coca-cola for more time than water, you can create a different retention policy for each metric.