Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Sachin Divekar
Date:
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

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

From
Hüseyin Demir
Date:
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 <ssd532@gmail.com>, 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
demirhuseyinn.94@gmail.com

selfarrival.blogspot.com.tr


Re: Make recently inserted/updated records available in thebuffer/cache

From
MichaelDBA
Date:
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

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

From
Michael Lewis
Date:
"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.

Re: Make recently inserted/updated records available in thebuffer/cache

From
MichaelDBA
Date:
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.

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

From
Sachin Divekar
Date:
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 <MichaelDBA@sqlexec.com> 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.

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

From
Michael Lewis
Date:
On Tue, Dec 3, 2019 at 11:46 AM Sachin Divekar <ssd532@gmail.com> 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.