Re: how smart is is pg with shared_buffers?

Joseph Shraibman wrote:

> Joseph Shraibman wrote:
>
>> Does pg know to keep indexes in memory and release table data? Does
>> it use an lru algorithm or does it know that certain tables are hit
>> more often and others and should be given prefrence?

PG does not do any caching except required for query manipulation. It relies on
OS to do it. So typically postgresql buffers are set to 16-128MB no matter how
much RAM your machine has.

It does use LRU algorithm to expire pages in it's own shared cache. There are
other algo. that are attempted in current development release.

Since it does not do true caching of data, it doesn't maintain statistics the
way you expect it. If something is hit frequently, OS cache will take care of
it. At certain times it could have bad impact such as doing large database dump
while database is updated too frequently but other than that, it works pretty
well in general.

> And when it has to do a seq scan on a table does it start with what is
> in the cache before it goes to the os to read from the disk?

Well since there is OS file system cache sitting in between postgresql disk
block request and actual IO, postgresql does not bother itself with such
questions. If it needs it and does not find in it's own cache, it will fetch it.

Please correct me if I am wrong.

Shridhar