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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.