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

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  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.
>
>