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.
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.