Re: How to keep a table in memory?

On Mon, 12 Nov 2007, Alex Drobychev wrote:

> Or any other ideas for "pinning" a table in memory?

If the table you're worried about is only 20MB, have you considered just
running something regularly that touches the whole thing? This may be the
only time I've ever considered running "select count(*) from x" as a
productive move. That would waste some CPU, but it would help those pages
"win the eviction war" as you say.

You definately should follow-up on the suggestion given to look at the
pg_buffercache contrib module to get a better idea what's going on under
the LRU hood. In fact, you may want to install a tweak that's standard in
8.3 to show the usage counts in order to better get a feel for what's
going on; the appendix on my article at
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm goes
into this a bit, with the documentation to pg_buffercache having the rest
of what you'd need.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD