Skip to main content

BufFileRead

Short Description

Waiting for a read from a buffered file.

Additional Information

Buffered files in PostgreSQL are primarily temporary files. Temporary files are used for sorts and hashes. BufFileRead is seen when these in memory buffers have been filled and the data that was spilled to disk is being re-read. Usually this is caused by your SQL queries.

How to reduce this wait

Large waits on BufFileRead can indicate that your work_mem setting is too small to capture most of your sorts and hashes in memory and are being spilled to disk.

  • Ensure your sorts largely occur in memory rather than writing temp files by using explain analyze on your query and examining the plan output looking for Sort Method: external merge Disk: to get the size of the sort.
  • Bear in mind that work_mem applies to each session therefore server memory can get used up very quickly
  • The AWS page for BufFileRead and BufFileWrite waits has a great information. Look for ORDER BY, GROUP BY, DISTINCT, Window functions, CTAS, and Materialized view refreshes causing spills to disk.

PostgreSQL documentation - The Statistics Collector

Cybertec Blog - POSTGRESQL: IMPROVING SORT PERFORMANCE

AWS - IO:BufFileRead and IO:BufFileWrite

Search online

If this article doesn't have the information you need you can try searching online. Remember, you can contribute suggestions to this page.