2

I'm working on a system that includes a scheduling component, which has support for recurring events. After reading many, many posts on storing recurring events, it was suggested that they be calculated for a specific time period using a Postgres function and then stored in a materialized view to avoid having to recalculate every time.

The system I'm working on serves multiple businesses, each with their own calendar and customers. My thought was that a materialized view would be created on a per-business basis. However, if the service is successful, there could be well over a 1000 businesses using the system. That said, are there issues with having that many materialized views? And if so, is there a better pattern that would scale as the service grows?

FYI: I'm running Postgres 9.4

Improve this question
1
  • 1000 MV are no problem per se. Running the outdated Postgres version 9.4 is a problem, though. Refreshing the MV may or may not be a problem. Depends on details that are not in your question. Cardinalities, concurrency, frequency, complexity, ...
    – Erwin Brandstetter
    Commented Dec 2, 2021 at 11:13

1 Answer 1

Reset to default
0

I know of no such limit, although I have never had more than tens of materialized views, not thousands. However a way of hedging against such a problem is to make one materialised view with the customer as a column and create an index on the customer column. That way, Postgres will be able to pick out the relevant parts of the view very quickly. You should have performance that is not significantly worse than having lots and lots of views. It will also be a lot less work to maintain.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.