> On Thu, May 29, 2008 at 10:19 AM, Justin <justin@emproshunts.com> wrote:
> Quoting You "Also, postgresql doesn't as a rule cache 'results and queries'.
>
> Then what is the purpose of shared buffers if nothing is being reused is it
> only used to keep track locks, changes and what is to being spooled to the
> kernel???
not much. PostgreSQL provides an alternative file cache to the o/s
buffer cache (rather, it layers on top of it). The performance
difference between these caches is an interesting topic. In my
experience, it isn't very much, so I tend to keep shared buffers on
the low side.
> I'm confussed trying to figure out how caches are being use and being moving
> through postgresql backend. As many have stated that small database can
> fit completely in the caches How does that really work.
If your database is smaller than working memory, it will be completely
cached all the time, unless you have some big transient memory demand
(a big sort, or result set). It's just a matter of which cache it
sits in...o/s, or postgres cache, or both.
This is actually the normal state of affairs in many databases.
memory .conf optimization is pretty marginal in effect until your
database is 'big' in terms of relative size to memory, does a lot of
sorting (work_mem becomes important), or receives a lot of writing
(vacuum, sync, checkpoints, and bgwriter become important). There are
a few things you have to pay attention to, especially in older
versions. For example effective cache size...but mainly because it
affects query plans chosen, not cache policy.
For smallish databases, your best investment is writing efficient
queries and good index strategy.
merlin