I have a large Postgres database with many tables, some with tens of millions of rows. Several working processes update the database concurrently. For faster searches, relevant data is compiled into a materialized view.
There are possibly multiple parallel processes writing to the database, then refreshing the materialized view afterwards. However, as the "REFRESH MATERIALIZED VIEW" query takes at least several minutes, quite often such queries pile up in a queue, and they all execute one after the other.
Unfortunately in such cases, only the latest query is of any relevance; all the previous queries consume processing time in vain to refresh stale data. Is there a way for stopping already running calls to "REFRESH MATERIALIZED VIEW" when a new call is issued?
Please note that "REFRESH MATERIALIZED VIEW CONCURRENTLY" has the same behaviour, but considerably slows the refresh (from a few minutes up to an hour), therefore aggravating the performance problem.
Of course it's possible to test for an existing lock on the view for each new query, so it's easy to cancel new queries; the problem is that I'd rather cancel the old queries and keep only the latest one...