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 the memory of each backend.

8.5.1. Ring Buffer

PostgreSQL uses a ring buffer instead of the buffer pool when reading or writing a large table. 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:
    The relation size exceeds one-quarter of the buffer pool size (shared_buffers/4). In this case, the ring buffer size is 256 KB.

  2. Bulk-writing:
    The following SQL commands are executed. In these cases, the ring buffer size is 16 MB:

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

PostgreSQL releases the ring buffer immediately after use.

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

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

The README in the buffer manager’s source directory explains the reason:

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 multiple backends access the same relation concurrently, they share the ring buffer.

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

Figure 8.13. Sharing the Ring Buffer with Two Backends.
  1. Backend_1 creates and accesses a ring buffer to read a table.
  2. Backend_1 and Backend_2 access the table pages loaded into the shared 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 private memory area and creates a local buffer.

Shared buffer bucket slots use positive numbers. In contrast, local buffer bucket slots use negative numbers (e.g., -1, -2). This distinction allows the backend to access both regular and temporary tables seamlessly.

Managing local buffers does not require locks because only the owner backend accesses them. Additionally, local buffers do not require WAL logging or checkpointing.