How to gracefully keep my specific index in memory ?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

How to gracefully keep my specific index in memory ?

James(王旭)
Hello:

As the title,How to keep a specific index in memory gracefully?

After some statistical query, I can determine that not all indexes can be fit into memory, but one of the most frequently used indexes(say idx_xyz) can be definitely fit into memory(specifically ,[the size of idx_xyz]=20% x [memory size]).

I know there's pgprewarm, but I feel with pgprewarm I can't keep things under control, e.g. no realtime monitor, being squeezed out of memory ,.etc.

Is it possible that I can simply do something like "select idx_xyz into xxx" and keep the specific index in memory forever?

Best regards,
James
Reply | Threaded
Open this post in threaded view
|

Re: How to gracefully keep my specific index in memory ?

Laurenz Albe
James(王旭) wrote:
> As the title,How to keep a specific index in memory gracefully?
>
> After some statistical query, I can determine that not all indexes can be fit into memory,
> but one of the most frequently used indexes(say idx_xyz) can be definitely fit into
> memory(specifically ,[the size of idx_xyz]=20% x [memory size]).
>
> I know there's pgprewarm, but I feel with pgprewarm I can't keep things under control, e.g. no realtime monitor, being squeezed out of memory ,.etc.
>
> Is it possible that I can simply do something like "select idx_xyz into xxx" and keep the specific index in memory forever?

If the indexes are frequently used, they should remain cached anyway.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: How to gracefully keep my specific index in memory ?

James(王旭)
Thanks Laurenz Albe for reply.

Unfortunately this index is only frequently used during a certain period of time(such as 9:30am - 12:00am) .I usually encounter slow load of this index during some other time.
Then it seems to me the only answer for this case would be the pgprewarm and pg_cron ?
 
 
------------------ Original ------------------
Date:  Mon, Aug 12, 2019 08:24 PM
To:  "James(王旭)"<[hidden email]>; "pgsql-general"<[hidden email]>;
Subject:  Re: How to gracefully keep my specific index in memory ?
 
James(王旭) wrote:
> As the title,How to keep a specific index in memory gracefully?
>
> After some statistical query, I can determine that not all indexes can be fit into memory,
> but one of the most frequently used indexes(say idx_xyz) can be definitely fit into
> memory(specifically ,[the size of idx_xyz]=20% x [memory size]).
>
> I know there's pgprewarm, but I feel with pgprewarm I can't keep things under control, e.g. no realtime monitor, being squeezed out of memory ,.etc.
>
> Is it possible that I can simply do something like "select idx_xyz into xxx" and keep the specific index in memory forever?

If the indexes are frequently used, they should remain cached anyway.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com