RE: Tuning threshold for BAS_BULKREAD (large tables)

On Tuesday, January 22, 2019 5:36 PM, Ron wrote:

>How can a subset of the database be larger than the database?
Oops. Sorry, I made a mistake on that part. What I meant was how does Postgres handle the large relations caching in terms of performance, especially if majority of the data it has to read would take up most of the shared buffers. Say, more than 3/4 of shared buffers. Is the current buffer access strategy (BAS_BULKREAD) optimal for this?

On 1/22/19 1:35 AM, Jamison, Kirk wrote:

Hi,

 

I have a source code-related question on BufferAccessStrategyType BAS_BULKREAD.

Currently, this access method is set internally to cache tables larger than 1/4 of shared_buffers.

src/backend/access/heap/heapam.c:initscan()

     if (!RelationUsesLocalBuffers(scan->rs_rd) &&

         scan->rs_nblocks > NBuffers / 4)

        ...

         /* During a rescan, keep the previous strategy object. */

         if (scan->rs_strategy == NULL)

             scan->rs_strategy = GetAccessStrategy(BAS_BULKREAD);

 

Users can tune their shared_buffers size, but not able to tune this component.

I'm just wondering how it affects the current workload when the table size is larger than the database.

How can a subset of the database be larger than the database?