[gigatexal]

Have you thought about materialized views that pre-aggregate your queries and then updating those views with triggers? I got this idea from a forum so it’s not mine but it works.

Sharding by month or other bucket of time could help.

We have a very similar situation except it’s billions of rows. One benefit is it’s a bit denormalized in that we store the meat of our data in a hstore field

[dbnewbie]

Thanks a ton for the help, I will take a look into what we might be able to do in terms of adding a materialized aggregate view!

[gigatexal]

You’ll get the most benefit from pre-aggregating not for a specific query but in a general way. Sum orders group by user in a view and then use triggers to update that view. That way to get the orders for a given user it’s a simple lookup and all the calculation is done. And this way you can build up meta reports from these general aggregates.

[dbnewbie]

Ok awesome, and thanks again!

[gigatexal]

Just to be super clear don’t use the triggers to rebuild the view every time but merely to add one to the sum’d account when a row matches — I wasn’t sure I made that clear. Good luck! And do put up a blog post or something if it works out

[dbnewbie]

OK that makes sense, and I’ll do a bit of reading on materialized view functionality before actually implementing anything.

[gigatexal]

Again not my idea. It was suggested to me in response to the very problem you’re having. I’m just so happy to have it as an idea that I didn’t want to keep it to myself. Here’s the link. https://hashrocket.com/blog/posts/materialized-view-strategi...

[gigatexal]

I have a link somewhere I’ll find it