how smart is is pg with shared_buffers?

[Joseph Shraibman]

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?
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Joseph Shraibman]

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?

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?
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Shridhar Daithankar]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html