Re: To keep indexes in memory, is large enough effective_cache_size enough?

On Wed, Sep 19, 2018 at 5:19 AM Sam R. <samruohola(at)yahoo(dot)com> 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