Skip to main content

BufFileWrite

Short Description

Waiting for a write to a buffered file.

Additional Information​

Buffered files in PostgreSQL are primarily temporary files. Temporary files are used for sorts and hashes. BufFileWrite is seen when these in memory buffers are filled and written (or spilled) to disk. Usually this is caused by your SQL queries.

How to reduce this wait​

Large waits on BufFileWrite 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 on it. 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.