Alexei Vladishev wrote:
> Is there a way of configuring PostgreSQL so that one specific table would
> use, say, 4GB of buffer cache while other tables would use the rest?
It sounds like you're looking for what other databases call "pinning".
It's not supported in PostgreSQL right now, and as far as I know it's
not on anybody's hotlist of features they're working on. It would be
straightforward to add actually; I know exactly where the code that
evicts pages from the buffer cache would need to be tweaked to support
this. See the "Inside the PostgreSQL Buffer Cache" presentation at
http://www.westnet.com/~gsmith/content/postgresql/ for more details
about how the current implementation works. Be happy to talk about what
what it would take to sponsor the bit of development required if this is
something you really need for your app--it's not a giant feature to
build, just not one that anyone has needed badly enough so far to bother
writing.
> I would like to keep the table and its indexes always in "hot" state,
> so that
> other queries won't pollute this part of the buffer cache. It would
> ensure
> reliable performance and much less disk IOPS working with the table.
The PostgreSQL design presumes that the OS cache is significantly larger
than the RAM dedicated to the database, so a database cache miss doesn't
necessarily turn into physical I/O. Part of the reason this feature
hasn't been been perceived as more valuable is because just letting the
two cache levels involved here sort out what's really valuable or not
can often outperform what an application developer thinks the optimal
configuration will be.
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.com