Re: shared_buffers advice

Pierre C wrote:
> Actually, I meant that in the case of a seq scan, PG will try to use
> just a few buffers (a ring) in shared_buffers instead of thrashing the
> whole buffers. But if there was actually a lot of free space in
> shared_buffers, do the pages stay, or do they not ?

Pages inserted into the ring buffer and later re-used for new data do
not stay behind even if there is room for them. There's a potential
improvement possible in that code involving better management of the
situation where the buffer cache hasn't actually reached full capacity
yet, but as it's an unusual case it's hard to justify optimizing for.
Besides, the hope is that in this case the OS cache will end up caching
everything anyway until it has a reason to evict it. So if you follow
the rest of the data suggesting you should not give all the memory to
PostgreSQL to manage, you end up with a reasonable solution to this
problem anyway. Those pages will just live in the OS cache instead of
the database's, with only a few trickling in and staying behind each
time you do a sequential scan.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us