8.5. 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 and temporary buffer area. When any of the following conditions is met, a ring buffer is allocated to shared memory:

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

2. Bulk-writing:
   When the SQL commands listed below are executed. In this case, the ring buffer size is 16 MB.

   • COPY FROM command.
   • CREATE TABLE AS command.
   • CREATE MATERIALIZED VIEW or REFRESH MATERIALIZED VIEW command.
   • ALTER TABLE command.

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

The allocated ring buffer is released immediately after use.

The benefit of the ring buffer is obvious. If a backend process reads a huge table without using a ring buffer, all stored pages in the buffer pool are evicted, which decreases the cache hit ratio. The ring buffer avoids this issue by providing a temporary buffer area for the huge table.