8.5. Ring Buffer and Local Buffer

This section introduces two types of temporary buffers:

  • Ring Buffer: Allocated in shared memory
  • Local Buffer: Allocated in each backend’s memory

8.5.1. Ring Buffer

When reading or writing a huge table, PostgreSQL uses a ring buffer instead of the buffer pool.

The ring buffer is a small, temporary buffer area. It is allocated in shared memory when any of the following conditions is met:

  1. Bulk-reading:
    When scanning a relation whose size exceeds one-quarter of the buffer pool size ($ \text{shared_buffers} / 4 $). In this case, the ring buffer size is 256 KB.

  2. Bulk-writing:
    When executing the following SQL commands, the ring buffer size is 16 MB:

  3. Vacuum-processing:
    When an autovacuum process performs vacuuming. In this case, the ring buffer size is 256 KB.

The ring buffer is released immediately after use.

The benefit of the ring buffer is clear: If a backend process reads a large table without a ring buffer, all pages stored in the buffer pool may be evicted, reducing the cache hit ratio. The ring buffer prevents this by providing a temporary buffer area for large tables.

Why the default ring buffer size for bulk-reading and vacuum processing is 256 KB?

The answer is explained in the README located under the buffer manager’s source directory.

For sequential scans, a 256 KB ring is used. That’s small enough to fit in L2 cache, which makes transferring pages from OS cache to shared buffer cache efficient. Even less would often be enough, but the ring must be big enough to accommodate all pages in the scan that are pinned concurrently. (snip)

When the same relation is accessed concurrently by two or more backends, the ring buffer is shared among them.

Figure 8.13 illustrates how two backends access the ring buffer using a sequential scan:

Figure 8.13. Sharing the Ring Buffer with Two Backends.
  1. Backend_1 accesses a table via the ring buffer, which is created when Backend_1 accesses the table.
  2. Backend_1 and Backend_2 access the table loaded into the ring buffer. Backend_2 starts its sequential scan from the middle of the table.
  3. After Backend_1 completes its scan, Backend_2 continues scanning the rest of the table, starting from the beginning and proceeding to the middle.

8.5.2. Temporary Tables and Local Buffer Management

When a backend creates a temporary table, the buffer manager allocates a memory area for the backend and creates a local buffer.

While the numbering of bucket slots for the shared buffer is positive, the numbering of the local bucket slots for the local buffer is negative, i.e., -1, -2, and so on. This allows the backend to seamlessly access both regular tables and temporary tables.

Managing local buffers does not require locks, as these buffers are accessed exclusively by the backend that creates them. Additionally, local buffers do not need to be WAL-logged or checkpointed.