On Thu, 2008-10-30 at 09:46 -0700, Scott Carey wrote:
>
> Remember that PostgreSQL doesn't cache anything on its own so
> if you do
> want to hit disk it has to be in file cache.
>
> By my understanding, this is absolutely false. Postgres caches pages
> from tables/indexes in shared_buffers. You can make this very large if
> you wish.
You can make it very large with a potentially serious performance hit.
It is very expensive to manage large amounts of shared buffers. It can
also nail your IO on checkpoint if you are not careful (even with
checkpoint smoothing). You are correct that I did not explain what I
meant very well because shared buffers are exactly that, shared
buffers.
However that isn't the exact same thing as a "cache" at least as I was
trying to describe it. shared buffers are used to keep track of pages
(as well as some other stuff) and their current status. That is not the
same as caching a relation.
It is not possible to pin a relation to memory using PostgreSQL.
PostgreSQL relies on the operating system for that type of caching.
Joshua D. Drake
--