using pg_prewarm after an ETL load has finished - worth it? I have a database with many small reference data tables and a few large time series tables. The reference data tables can easily fit into memory. I have various ETL jobs that load data, mainly by left joining incoming data onto the existing tables, and insert/update/delete where required.

Does it make sense to call pg_prewarm on all the reference data tables after an ETL job has finished, so as to ensure that all of them are loaded into memory the next time an ETL load fires? Will I notice much of an improvement?

Assume that the memory is sufficiently large so as to not cause an issue with buffer eviction.
Based on your description, prewarm is unlikely to be harmful.  With the information you provide, there is no way to know how helpful it will be.  It probably takes less time to implement than it does to think about whether it is worth implementing it. So just do it and be done with it.


  Does it make sense to call pg_prewarm on all the reference data tables after an ETL job has finished, so as to ensure that all of them are loaded into memory the next time an ETL load fires?


Well, who would be driving this data out of memory in the first place? If you are going to prewarm, it would make more sense to do it immediately before you start a load, not immediately after you finish one in the hopes it sticks around until the next one starts.