Re: Caching by Postgres

On Tue, Aug 23, 2005 at 10:10:45 -0700,
gokulnathbabu manoharan <gokulnathbabu(at)yahoo(dot)com> wrote:
> Hi all,
>
> I like to know the caching policies of Postgresql.
> What parameter in the postgresql.conf affects the
> cache size used by the Postgresql? As far as I have
> searched my knowledge of the parameters are

The main policy is to let the OS do most of the caching.

> 1. shared_buffers - Sets the limit on the amount of
> shared memory used. If I take this is as the cache
> size then my performance should increase with the
> increase in the size of shared_buffers. But it seems
> it is not the case and my performance actually
> decreases with the increase in the shared_buffers. I
> have a RAM size of 32 GB. The table which I use more
> frequently has around 68 million rows. Can I cache
> this entire table in RAM?

Using extermely large values for shared buffers is known to be a performance
loss for Postgres. Some improvements were made for 8.0 and more for 8.1.

The OS will cache frequently used data from files for you. So if you are using
that table a lot and the rows aren't too wide, it should mostly be cached
for you by the OS.

> 2. work_mem - It is the amount of memory used by an
> operation. My guess is once the operation is complete
> this is freed and hence has nothing to do with the
> caching.

This is used for sorts and some other things.

> 3. effective_cache_size - The parameter used by the
> query planner and has nothing to do with the actual
> caching.

You are supposed to use this to give the planner an idea about how much
space the OS will using for caching on behalf of Posgres.

> So kindly help me in pointing me to the correct
> parameter to set.
>
> It will be great if you can point me to the docs that
> explains the implementation of caching in Postgresql
> which will help me in understanding things much
> clearly.

You probably want to read the following:
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html