0

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.

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.

  • 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. – sudo Jun 2 '16 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.