Make recently inserted/updated records available in the buffer/cache

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

Make recently inserted/updated records available in the buffer/cache

Sachin Divekar
Hi, 

I am looking for tuning my PG setup such that recently inserted or updated record will be available in the buffer/cache (I believe they are same in this context). Does PostgreSQL do it by default? If yes, just increasing buffer size sufficient? What will be its effect on LRU performance -- I guess there won't be any adverse effect?

My use case is that I am going to use it as a queue and performance will be dependent upon whether the recently updated record is available in the cache.

Thank you.

regards
Sachin
Reply | Threaded
Open this post in threaded view
|

Re: Make recently inserted/updated records available in the buffer/cache

Hüseyin Demir
Hi, 

PostgreSQL decides which pages should be evicted from memory and written to disk with the help of LRU algorithm. Thus, it depends on your query work load. In OLTP systems, the algorithm is beneficial to business requirements(almost :) )

It's hard to figure out that a configuration change will affect the performance in a good way. Maybe, you can use PostgreSQL warmup features in order to make sure the data pages that you need will be available in cache.

Because the results of LRU algorithm can vary depending on your business and system workload. 

Best Regards.


Sachin Divekar <[hidden email]>, 2 Ara 2019 Pzt, 20:03 tarihinde şunu yazdı:
Hi, 

I am looking for tuning my PG setup such that recently inserted or updated record will be available in the buffer/cache (I believe they are same in this context). Does PostgreSQL do it by default? If yes, just increasing buffer size sufficient? What will be its effect on LRU performance -- I guess there won't be any adverse effect?

My use case is that I am going to use it as a queue and performance will be dependent upon whether the recently updated record is available in the cache.

Thank you.

regards
Sachin


--

Hüseyin DEMİR

IT SOLUTION ARCHITECT

0534-614-72-06
[hidden email]

selfarrival.blogspot.com.tr


Reply | Threaded
Open this post in threaded view
|

Re: Make recently inserted/updated records available in the buffer/cache

Michaeldba@sqlexec.com
All updated/dirty records go through PG internal memory buffer,
shared_buffers.  Make sure that is configured optimally.  Use
pg_buffercache extension to set it correctly.

Regards,
Michael Vitale

Hüseyin Demir wrote on 12/2/2019 12:13 PM:
> I guess there won't be any adverse effect



Reply | Threaded
Open this post in threaded view
|

Re: Make recently inserted/updated records available in the buffer/cache

Michael Lewis
"I am going to use it as a queue"

You may want to look at lowering fillfactor if this queue is going to have frequent updates, and also make autovacuum/analyze much more aggressive assuming many updates and deletes.
Reply | Threaded
Open this post in threaded view
|

Re: Make recently inserted/updated records available in the buffer/cache

Michaeldba@sqlexec.com
Yep, I concur completely!  For tables treated like queues you gotta do
this stuff or deal with bloat and fragmented indexes.

Michael Lewis wrote on 12/3/2019 12:29 PM:
> "I am going to use it as a queue"
>
> You may want to look at lowering fillfactor if this queue is going to
> have frequent updates, and also make autovacuum/analyze much more
> aggressive assuming many updates and deletes.



Reply | Threaded
Open this post in threaded view
|

Re: Make recently inserted/updated records available in the buffer/cache

Sachin Divekar
Thank you, Michaels.
.
I didn't know about fillfactor and table bloat. Did some reading on those topics. We will definitely need to tweak these settings. 

I am also going to use SKIP LOCKED to _select for update_. Any suggestions on tuning parameters for SKIP LOCKED?

Thanks

On Tue, Dec 3, 2019 at 11:02 PM MichaelDBA <[hidden email]> wrote:
Yep, I concur completely!  For tables treated like queues you gotta do
this stuff or deal with bloat and fragmented indexes.

Michael Lewis wrote on 12/3/2019 12:29 PM:
> "I am going to use it as a queue"
>
> You may want to look at lowering fillfactor if this queue is going to
> have frequent updates, and also make autovacuum/analyze much more
> aggressive assuming many updates and deletes.

Reply | Threaded
Open this post in threaded view
|

Re: Make recently inserted/updated records available in the buffer/cache

Michael Lewis
On Tue, Dec 3, 2019 at 11:46 AM Sachin Divekar <[hidden email]> wrote:
I am also going to use SKIP LOCKED to _select for update_. Any suggestions on tuning parameters for SKIP LOCKED?

I am not aware of any. Either you use it because it fits your need, or not.

Note- please don't top-post (reply and include all the previous conversation below) on the Postgres mailing lists. Quote only the part(s) you are responding to and reply there.