I would like to do something like
results = select * from quick_table
if no results:
insert into quick_table select slow_query
results = select * from quick_table
return results
This is a pretty standard caching pattern. Is there any way I can do this in postgres that's more clever than just literally writing a function to do what I listed above?
The PostgreSQL feature that comes closest to what you want to do is a materialized view.
This creates a copy on disk of the results of your view, which you can then query as if it were a table. You can also add indexes to it in the usual way.
A caveat is that when you generate a materialized view, its data does not update automatically when the source tables’ data change. To reflect changes, you must issue a REFRESH MATERIALIZED VIEW command.
Typical approaches to refreshing are:
Each approach has advantages and disadvantages, so the route you take will depend on your circumstance. It may also be useful to make sure you can add a unique index to your MV, as that will allow you to run concurrent refreshes; without that, a refresh places an exclusive lock on the view, so it won’t be readable until the refresh has finished.
