Do SELECT queries (w/ table JOINs) on a database affect disk space whilst they're running?
Background: I have a Django app with a Postgresql backend (9.3.10). My DB resides in a VM that was running critically low on disk space (around ~400MB left).
I queried a few tables to assess which data to deprecate in order to release disk space (these included joins across tables). These analytical-type queries were bundled behind a single url, and run in unison. When I hit the url, the VM containing my DB went out of space after around half a minute.
I'm an accidental DBA and still learning the ropes. Can anyone explain why I went out of space in this scenario? Are some kind of temporary files created in such operations? I'll share my config details in case they're needed.
Best Answer
Of course, there are actually many ways for a
SELECT
to take disk space. Just look at the docs forwork_mem
Even with a high
work_mem
aSELECT
can chew away disk space. You can for instance, have a function required that writes to disk directly.You may be interested in mitigating this by using
temp_file_limit