Viewing disk usage of temporary buffers of running queries in Postgres

Question

I know how to get the sizes of relations or total table disk usage per table in Postgres 9.5 (https://wiki.postgresql.org/wiki/Disk_Usage), but I have some queries running whose temporary disk buffers use significant amounts of space. How can I see how much each is using? My disk is slowly running out of space due to many long-running queries, and I need to kill one of them.

Answer 1

If you have queries generating temporary files, it could indicate that work_mem is not sufficiently sized or that the query is not optimised.

Try setting the parameter log_temp_files = 0 in your postgresql.conf. This will log the creation of temporary files and the statement being executed.

Additionally an explain analyze of your query may show disk sort happening.

Taking the log output, you will see the size of temp files created by your query. You can use this to either fine tune the query or fine tune the work_mem parameter.

• sudo: It says things like 2016年06月02日 11:24:43 PDT LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp20410.11", size 174272980. I'm assuming the size is in bytes and that it's creating a lot of ~200MiB temp files. I expect my queries to be using lots of temporary files because my tables are around 200GiB, and I've tuned Postgres for my machine with 64GiB of RAM.