Searching the table and filtering by processing version can be slow.
Although this will be disk-space expensive, we can improve search performance by creating materialized views of processing versions.
This will require a lot of work:
-
Write the code that creates the materialized versions of the necessary tables. This would be diaobject, diasource, diaforcedsource, host_galaxy. We would want to create a materialized view for a processing version, not for a base processing version.
-
Figure out the protocol for creating these views. The most obvious is just that some admin triggers the creation if it; this should be in the web API. However, we might also want to have the ability to regularly recreate them. (This would be most useful for things like the realtime processing version that are changing all the time.)
-
Update all the functions to detect if a relevant materialized view is present (which means standardized view names), and to use it if it's there. If not, fall back to the current queries that join to the base_procver_of_procver table. Functions and web API endpoints should also take an argument to make it possible to ignore the materialized view. That would allow us, for instance, to have a materialized view of the realtime processing version that we update every so often, but that is ignored by things that really want to have the absolute latest and greatest.
Searching the table and filtering by processing version can be slow.
Although this will be disk-space expensive, we can improve search performance by creating materialized views of processing versions.
This will require a lot of work:
Write the code that creates the materialized versions of the necessary tables. This would be diaobject, diasource, diaforcedsource, host_galaxy. We would want to create a materialized view for a processing version, not for a base processing version.
Figure out the protocol for creating these views. The most obvious is just that some admin triggers the creation if it; this should be in the web API. However, we might also want to have the ability to regularly recreate them. (This would be most useful for things like the
realtimeprocessing version that are changing all the time.)Update all the functions to detect if a relevant materialized view is present (which means standardized view names), and to use it if it's there. If not, fall back to the current queries that join to the
base_procver_of_procvertable. Functions and web API endpoints should also take an argument to make it possible to ignore the materialized view. That would allow us, for instance, to have a materialized view of therealtimeprocessing version that we update every so often, but that is ignored by things that really want to have the absolute latest and greatest.