I am querying some data from a view using copy (select * from tbl where id in (n) order by 1) command (the table's in the view are ~600GB, almost all the columns included in the view) and it generates around 200Gb of temp files since my current work_mem is with default configuration. I know I must change few params to avoid temporary files. But I am confused on below points.

  1. If the query's result size is > work_mem, will it generate more temp files / use disks?

  2. Is it true that work_mem wont support more than 2Gb?

  3. My RAM is 128Gb and SSD storage, how much work_mem should I need to use this for this query? (I will create a separate user to query this table, and alter the user's work_mem)

  4. Is there any way to cancel the query before if it reaches out of space error? (I could limit by enabling max temp file limit. But I am not sure how the temp file size will be and how many files will be generated for a specific query)

(i.e., finding the more temp file/huge result(>10G) generating query, trigger pg_cancel_backend for that before it crashes the server)

  • You should also consider to tune temp_buffers parameter. Moreover I suggest you to set a limit for temp files for excessive usage with temp_file_limit parameter. – Sahap Asci Apr 21 at 12:30
  • 1
    There is no reason for Postgres to push a select * from tbl into temp files, unless tbl is a view or something requiring building up results. Showing EXPLAIN of the select queries might give a clue about what's going on, as well as log output with log_temp_files set. – Daniel Vérité Apr 22 at 21:19
  • @DanielVérité, yeah. it was from view. I haven't checked it since its has similar name as a table. Sorry about that. I will update the question. – Spike Apr 23 at 11:44
  • This is probably not the answer to your question, but it is sure a source for debate: pgtune.leopard.in.ua/# – sibert Apr 23 at 18:10

Your Answer

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

Browse other questions tagged or ask your own question.