Instead of doing a full refresh when any of the sources changes, we could based on what changes in the source compute how does (and if) this influence current results. There is some work for materialized views on how to do that, known as Incremental View Maintenance.

There are two main questions:

• How to know/store what exactly changed.
• How to map that to how results change.

Then we can update both our temporary cache table and send those changes to the client. Instead of doing a full refresh. And only if we do not know how to map new inputs to new results, we do a full refresh.

There are some related projects which could help here:

• https://github.com/ntqvinh/PgMvIncrementalUpdate
• https://github.com/twosigma/postgresql-contrib
• https://www.postgresql.org/message-id/flat/20181227215726.4d166b4874f8983a641123f5%40sraoss.co.jp

It might be necessary to parse and rewrite the query, for which this could help.