Thread: Indices pinned in pageable RAM?

Has anyone seriously looked at how it would impact things to give the 
DBA the option of storing certain indices in RAM instead of on disk?

Queries (both select and insert/update) against heavily indexed tables 
do most of the reads and writes to the index trees and relatively little 
reading and writing to the actual table data.

The index is completely redundant (read: rebuildable during restart) and 
its rebuild "should" be very parallelizable.  Without the WAL and disk 
synchronization munching, even a single-threaded rebuild during restart 
should be fairly quick.

This would allow the index to stay in RAM. giving it a higher priority 
than all other disk objects in the OS cache.  Putting it in pageable RAM 
would allow the OS to reclaim core if it absolutely had to.

Am I way off here?

Marty Scholes <marty@outputservices.com> writes:
> Has anyone seriously looked at how it would impact things to give the 
> DBA the option of storing certain indices in RAM instead of on disk?

I'd classify it as an utter waste of time.  If the index is being hit
heavily, it will stay cached in RAM anyway.  If it isn't, locking it in
RAM would be a pessimization not an optimization, because the space
would be better used for something else.
        regards, tom lane