How we made data aggregation on PostgreSQL better and faster

Re:How we made data aggregation on PostgreSQL better and faster by throwaway894345 on Tuesday June 21, 2022 @11:10AM (#31824840)

Is time series the right answer for anything with a time dimension, or is it mostly for things where time is THE critical dimension? For example, business intelligence applications care about time, but they also care about a whole bunch of other stuff as well (I think with at least as much importance)--is timeseries the right answer for this use case?

[ Parent | on HN ]

Re:How we made data aggregation on PostgreSQL better and faster by epgui on Tuesday June 21, 2022 @11:45AM (#31825260)

Anytime you’re interested in seeing how things change over time, that’s time series. It’s a very big category of use cases.

[ Parent | on HN ]

Re:How we made data aggregation on PostgreSQL better and faster by throwaway894345 on Tuesday June 21, 2022 @12:04PM (#31825532)

Sure, but analytics is sometimes change over time, and other times change over some other dimension. Presumably if time is just one dimension among many, then timeseries is probably not the right fit in general?

[ Parent | on HN ]

Re:How we made data aggregation on PostgreSQL better and faster by epgui on Tuesday June 21, 2022 @12:09PM (#31825588)

As with anything else, you can approach specific problems in many different ways.

[ Parent | on HN ]

Re:How we made data aggregation on PostgreSQL better and faster by slt2021 on Tuesday June 21, 2022 @12:48PM (#31826117)

timeseries is usually specific to use cases when you data represents some signal over time, like temperature reading, stock price, etc.

so you need 2 components: timestamp and signal reading, in this case all specific timeseries analytics apply: sliding/tumbling window, avg per window, smoothing, autocorrelation and all other techniques from Digital Signal Processing/timeseries analytics.

Your regular monthly Sales data of ACME Corp by product category and storeId - this is not timeseries, just general BI

[ Parent | on HN ]

Re:How we made data aggregation on PostgreSQL better and faster by ryanbooz on Tuesday June 21, 2022 @02:58PM (#31827700)

(NB - post author)

Great definition! Having worked for years on both energy and IoT applications, the argument here is that your "monthly sales data" is likely being aggregated from your time-series data (sales transactions over time). If you store the transaction data in a database like TimescaleDB, then continuous aggregates provide the straightforward method for keeping that aggregated, monthly sales data up-to-date. :-D!

[ Parent | on HN ]

Re:How we made data aggregation on PostgreSQL better and faster by throwaway894345 on Tuesday June 21, 2022 @12:43PM (#31826059)

That's very zen, but ultimately it doesn't answer my question.

[ Parent | on HN ]

Re:How we made data aggregation on PostgreSQL better and faster by epgui on Tuesday June 21, 2022 @07:16PM (#31830228)

Well, I could be more opinionated, but even in very specific situations, reasonable people disagree about the best way to model data, and I don't really know a lot about your specific problem-space or situation.

My personal preference is to think of almost any changing measurement or event stream as a time series. See also the reply to a sibling comment.

[ Parent | on HN ]