Is it possible to set an automatic refresh frequency for materialized views?
Here's the situation, I have an important but relatively small (20 thousand row) table that's subject to constant updates (hundreds of updates/second). The table is also subject to frequent (low priority) reads. What I've done before is to copy the table every 60 seconds into a second table and allow public read-only access to that table.
I suspect that using a materialized view is a more suitable solution, but if possible I'd also like to automatically refresh the view based on the number of SELECTs made against the view. If no-one SELECTS from the view the don't bother with a refresh, but if the view is being accessed then the table should be refreshed every 60 seconds.
Is this something that postgres provides a solution to, or should I code the refreshing of the view into my application?