On Wed, Sep 19, 2018 at 5:19 AM Sam R. <samruohola@xxxxxxxxx> wrote:
Hi!Is is possible to force PostgreSQL to keep an index in memory?
It might be possible to put the indexes in a separate tablespace, then do something at the file-system level to to force the OS cache to keep pages for that FS in memory.
The data in db table columns is not needed to be kept in memory, only the index. (hash index.)
This sounds like speculation. Do you have hard evidence that this is actually the case?
It would sound optimal in our scenario.I think Oracle has capability to keep index in memory (in-memory db functionality). But does PostgreSQL have such a functionality? (I keep searching.)
There are a lot of Oracle capabilities which encourage people to micromanage the server in ways that are almost never actually productive.
Should I actually set shared_buffers to tens of gigabytes also, if I want to keep one very big index in memory?
If your entire database fits in RAM, then it could be useful to set shared_buffers high enough to fit the entire database.
If fitting the entire database in RAM is hopeless, 10s of gigabytes is probably too much, unless you have 100s of GB of RAM. PostgreSQL doesn't do direct IO, but rather uses the OS file cache extensively. This leads to double-buffering, where a page is read from disk and stored in the OS file cache, then handed over to PostgreSQL where it is also stored in shared_buffers. That means that 1/2 of RAM is often the worse value for shared_buffers. You would want it to be either something like 1/20 to 1/10 of RAM, or something like 9/10 or 19/20 of RAM, so that you concentrate pages into one of the caches or the other. The low fraction of RAM is the more generally useful option. The high fraction of RAM is useful when you have very high write loads, particularly intensive index updating--and in that case you probably need someone to intensively monitor and baby-sit the database.
Cheers,
Jeff
