Re: Bogus temp file reporting?

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(dot)ntt(at)gmail(dot)com> wrote:
>
> At Thu, 27 Jul 2023 10:30:41 +0300, kg(dot)postgresql(at)olympiakos(dot)com <mailto:kg(dot)postgresql(at)olympiakos(dot)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