Re: pg_prewarm

On Fri, Mar 9, 2012 at 5:42 AM, Hans-Jürgen Schönig
<postgres@cybertec.at> wrote:
> we had some different idea here in the past: what if we had a procedure / method to allow people to save the list of
currentbuffers / cached blocks to be written to disk (sorted). we could then reload this "cache profile" on startup in
thebackground or people could load a certain cache content at runtime (maybe to test or whatever). 
> writing those block ids in sorted order would help us to avoid some random I/O on reload.

I don't think that's a bad idea at all, and someone actually did write
a patch for it at one point, though it didn't get committed, partly I
believe because of technical issues and partly because Greg Smith was
uncertain how much good it did to restore shared_buffers without
thinking about the OS cache.  Personally, I don't buy into the latter
objection: a lot of people are running with data sets that fit inside
shared_buffers, and those people would benefit tremendously.

However, this just provides mechanism, not policy, and is therefore
more general.  You could use pg_buffercache to save the cache contents
at shutdown and pg_prewarm to load those blocks back in at startup, if
you were so inclined.  Or if you just want to load up your main
relation, and its indexes, you can do that, too.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company