Continuous aggregates for non timeseries/aggregate data

TimescaleDB and PostgreSQL
1

Hello all,

I’ve became acquainted with TimescaleDB just couple of days ago, and I’m wondering if could fit in a simpler use case, which is basically to use continuous aggregates to automatically refresh materialized views that don’t contain any aggregate data.

They are basically materialized views to simplify and speed up queries with many joins, some transformations, but without aggregate functions, at least at this point.

To put it a more objective way, I’m interested on the ability of not needing to maintain triggers to get up to date data on such materialized views.

So couple of objective questions:

  1. Can I use TimescaleDB’s continuous aggregate for that?
  2. If so, should I? Is it overkill? Are there better alternatives for this use case?

Thanks!

1 Like
2

Welcome @dmnelson!

I’m glad Timescale has caught your attention and that you’re looking at some of the great features like Continous Aggregates.

To your specific question, the query definition of a continuous aggregate must include the time_bucket() (or the experimental time_bucket_ng()) function. If your query doesn’t contain that as an aggregating (grouping) factor, then you can’t use a CAGG for this.

One other alternative, as opposed to the trigger gymnastics, might be to use a User-defined Action that refreshes your data on a schedule.

Thanks again for reaching out! Let me know if I can clarify in some other way!

2 Likes