Thanks for the clarification. Also, I was wrong about the temp_* columns in the pg_stat_database view in that they report the overall temp file usage since the last stats reset and not the “current” usage.
Regards, Kiriakos Georgiou > On Jul 27, 2023, at 11:31 AM, Kyotaro Horiguchi <horikyota....@gmail.com> > wrote: > > At Thu, 27 Jul 2023 10:30:41 +0300, kg.postgre...@olympiakos.com > <mailto:kg.postgre...@olympiakos.com> wrote in >> Hello, >> > >> Is there a known issue with temp file reporting? I have a >> production db that is reporting 231TB of temp space usage, which >> can’t be true. temp_blks_written in pg_stat_statements sort of >> confirms this, as the reported queries show just a few MB of temp >> space usage. I suspect a reboot will clear this (to do) and wanted >> to check with you to see if someone else has had similar >> experiences. > > > Just to clarify, "temprary files" and "temp blocks" refer to different > entities in this context. The "temprary files" are ephemeral storage > that is created and alive only for tasks like tuple sorting, whereas > "temp blocks" are referring to the storage for temprary tables. > > If you're performing a massive insert into a temporary table, you'll > find that pg_stat_database.temp_bytes doesn't increase at all. > > regards. > > -- > Kyotaro Horiguchi > NTT Open Source Software Center
