1

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.

  • 1
  • +1 for sarcasm. I defer to your clearly superior knowledge, but 'racing my horses' would involve a non-trivial expenditure of my time in this case. I was wondering if this was an area of postgresql performance that generally yielded significant performance benefits or marginal ones. I'm not sure where to find a general approach to postgresql performance optimization. Creating a test bench for every technique I read about is not something I can invest time in unfortunately, so I'm looking for the low hanging fruit. – ThatDataGuy Aug 6 at 14:23
  • If the reference tables are small, they should be cached pretty quickly, and I wouldn't expect much difference. Use pg_prewarm if you notice a performance drop after ETL. I agree that no definite statement can be made without a test here. – Laurenz Albe Aug 6 at 14:50
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.