title: Dangers of having too many Materialized Views?
body: <p>My application is going to be creating a growing number of materialized views. </p>
  
  <p>The number of materialized views will increase by 8,000 - 10,000 per month. </p>
  
  <p>Most of the views will hold very little information, around 100-1000 rows with small fields, but a few (10 per month) will hold from 100,000 to millions of rows with small fields. </p>
  
  <p>I am cautious to see if this is a good idea before going too far into the implementation. </p>
  
  <p><strong>Can anyone tell me the hard limits I may hit, or if this is a good idea at all?</strong> </p>
  
  <p>If needed, I can explain further the use-case. It may be possible to drop some of the older views if needed (99%+), but not all (the 10/month big ones must stay).</p>
  
  <p><strong>EDIT: Explanation Requested</strong></p>
  
  <p><a href="https://docs.google.com/document/d/1T2JH9J73WjgZ9-cULJAzrYvZzyPSXEA_fdgt21lHnDc/preview" rel="nofollow noreferrer">The app</a> allows users to vote on content, and then we make charts of the content with the most votes. We have 5-minute units of voting, and charts generated by request. A user can look at any length of time with a granularity of 5 minutes. For example, I could look at just the past 5 minutes of votes, 15 minutes, 2 hours, etc.</p>
  
  <p>To tally votes and make the "Top Charts" we must do an expensive query, searching all the content that has gotten votes within the time units that are requested, tallying, and sorting. To mitigate this, I wanted to make a sorted materialized view with the results of the vote for EVERY time unit, as after voting closes for that 5 minutes, the votes will never change. This way, a popular search (like the latest 5-minute chart) will not have to be generated and sorted every time a user searches (there are 8760 5-minute units per month). I also wanted to make mviews for the weeks, months, and days.</p>
  
  <p>This is the table I will be using to generate the mviews, with tuid being a reference to the 5-minute voting time unit
  <a href="https://i.stack.imgur.com/yl5dA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/yl5dA.png" alt="This is the table I will be using to generate the mviews"></a></p>
  
  <p>Perhaps there is a better way to make this efficient with caching?</p>