How can I mitigate a cold cache in Postgres?

Issue

New instances of Postgres, e.g. upgrades, maintenance, etc. may appear to have performance issues due to a cold cache.

Resolution

To mitigate cold cache issues on a new Postgres instance:

  • If using a manual upgrade, e.g. a follower changeover, before the unfollow & promote step, use pg_prewarm on the follower to help make sure important portions of the dataset are cached in the OS and PostgreSQL's buffer caches.
    If using the addons:upgrade method you could run pg_prewarm as soon as the new instance is active, as the replacement follower instance isn't accessible by the customer prior to the maintenance being run.

  • In addition, run common queries to help rebuild the Postgres buffer cache - On top of using pg_prewarm, if there are any query statements in particular that you know run frequently, running it yourself will help ensure that the relevant blocks are loaded into cache.

Full documentation can be found in the Postgres documentation for the pg_prewarm extension. To use this pg_prewarm to pre-warm your tables, first install the extension:

CREATE EXTENSION pg_prewarm;

Then, pre-warm your core tables with:

SELECT * FROM pg_prewarm('<tablename>');

Ask on Stack Overflow

Engage with a community of passionate experts to get the answers you need

Ask on Stack Overflow

Heroku Support

Create a support ticket and our support experts will get back to you

Contact Heroku Support