Greg,
> 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.
Thank you for the link. Lots of useful information there.
>> 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.
Interesting! I assumed that it is a common practice to dedicate most of
the RAM to a database engine leaving only small percentage to OS.
Kind regards,
Alexei