PostgreSQL views and materialized views and how they influenced TimescaleDB

> If I’d written out the query, I might have seen that I didn’t need the JOIN (or never written it in the first place). Whereas the view hides that complexity. So they can make things easier, but that can lead to performance pitfalls if we’re not careful.

you can avoid this particular pitfall by using left joins for views like this (that join stuff in for convenience that you might not select) - postgres will eliminate a left join but not an inner join since the inner join could filter rows (eg symbols in stocks_real_time that don't exist in company (commercial engines will use the presence of fk constraints to know that's impossible and go ahead and eliminate inner joins as well))

Did you consider a type of continuous aggregate that works over pre-aggregated partitions for time buckets without modifications and swapping those buckets in the continuous aggregate view with a live view when data is modified? I guess it would mean that performance would sort of continually degrade as inserts/updates/deletes happened in more and more time buckets, but as soon as the aggregation event fired performance would recover. It seems like one could provide a more strongly consistent view of aggregates with this sort of approach.

NB: Post author

I'm not 100% sure I understand what you're asking, but essentially something that would look for data modifications and at query time run the query over the older regions as well?

If that's what you're asking the answer is yes, we did consider it, but basically decided that it was something that relatively few people needed and the complexity and performance tradeoffs were unlikely to be worth it for most folks.

Essentially, we could do something like this now by looking at our invalidation log and running a join against it to get to a more strongly consistent state (I haven't thought through the full implications and whether it's truly strong consistency, I think it might be, but it'd require a proof / some thinking through of all of our locking logic to really get there). It's interesting to consider though.

Enjoyed this post! Djk447 would love to ask a question as well.

We've started working with Timescale to process historical time series data. However there is so much of it that we chose which parts we process.

It's possible that in the future we may need to go back and reprocess the dataset to add something we decided we want after all.

In your post it seems like this is handled automatically on a smaller time scale, meaning you could insert into the past without doing anything special.

What happens if you need to insert data at numerous points across two years worth of data instead? Do you have to use backfilling as described in your documentation? Or is it better to maybe rebuild the entire hypertable?

NB: Post author

Yes. this is generally handled automatically, there may be times though where you want to essentially pause refreshing the view for a while while you do some backfilling and then eventually let it catch up, especially if you're overwriting the same time period multiple times in a row. If you can insert in time order then it just breaks up re-calculation into smaller segments, which can be quite useful rather than having to process the whole data set again.

This can be a little bit different if you're doing compression, but with continuous aggregates I think it should work fine. I'm not 100% sure that was what you were looking for, let me know if it's not.