LWLock - buffer_io

The buffer_io event occurs when PostgreSQL is waiting for other processes to finish their input/output (I/O) operations when concurrently trying to access a page. Its purpose is for the same page to be read into the shared buffer.

Each shared buffer has an I/O lock that is associated with the buffer_io wait event, each time a block (or a page) has to be retrieved outside the shared buffer pool.

This lock is used to handle multiple sessions that all require access to the same block. This block has to be read from outside the shared buffer pool, defined by the shared_buffers parameter.

As soon as the page is read inside the shared buffer pool, the buffer_io lock is released.

Note

The buffer_io wait event precedes the DataFileRead wait event. The DataFileRead wait event occurs while data is being read from storage.

Common causes for the buffer_io event to appear in top waits include the following:

• Multiple backends or connections trying to access the same page that’s also pending an I/O operation
• The ratio between the size of the shared buffer pool (defined by the shared_buffers parameter) and the number of buffers needed by the current workload
• The size of the shared buffer pool not being well balanced with the number of pages being evicted by other operations
• Large or bloated indexes that require the engine to read more pages than necessary into the shared buffer pool
• Lack of indexes that forces the DB engine to read more pages from the tables than necessary
• Checkpoints occurring too frequently or needing to flush too many modified pages
• Sudden spikes for database connections trying to perform operations on the same page

Run the following query to see the shared buffer cache hit ratio.

SELECT 
  round(100 * sum(blks_hit) / sum(blks_hit + blks_read), 3) as cache_hit_ratio
FROM pg_stat_database;

We recommend different actions depending on the causes of your wait event:

• Observe statistics for correlation between sharp decreases in the cache_hit_ratio and buffer_io wait events. This effect can mean that you have a small shared buffers setting. You might need to increase it or scale up your DB instance class. You can split your workload into more reader nodes.
• Tune max_wal_size and checkpoint_timeout based on your workload peak time if you see buffer_io coinciding with cache_hit_ratio metric dips. Then identify which query might be causing it.
• Verify whether you have unused indexes, then remove them.
• Use partitioned tables (which also have partitioned indexes). Doing this helps to keep index reordering low and reduces its impact.
• Avoid indexing columns unnecessarily.
• Prevent sudden database connection spikes by using a connection pool.
• Restrict the maximum number of connections to the database as a best practice.