Re: Size of /pgdata

Checked temp files as well...no thesee are not temp.files

On Sat, 13 Jun 2026, 12:39 Bhanu Prakash, <mallelabhanuprakash(at)gmail(dot)com>
wrote:

> Probably, pgsql_tmp file might consumed rest of ~200gb disk. You will find
> this inside base directory.
>
> During large query executions, if data to be sorted exceeds work_mem
> settings, postgresql creates temporary files and spills data to disk.
>
> Possible reasons :
> Poor query
> Large batch jobs
> Missing index
> LRQ’s
> Too low work_mem
>
> On Sat, 13 Jun 2026 at 12:18 PM, Raj <rajeshkumar(dot)dba09(at)gmail(dot)com> wrote:
>
>> Hi all,
>>
>> I am using 17.9 version.
>>
>> I see suddenly du -sh /pgdata/17 shows 930GB. Total allocated is 1TB.
>>
>> I checked the db size using \l+ and also pg_total_relation_size and size
>> for particular db is 630GB. Ignoring postgres and template dbs as they are
>> in KBs.
>>
>> Now where the remaining 300 comes from approximately..?
>>
>> I checked pg_wal and is 100gb..that is intended based on max_wal_size
>> setting.
>>
>> Postgres logs also in MBs ..so ruling out that.
>>
>> Now 300 - 100 = 200gb approximately don't know where it comes from.
>>
>> So du -sh /base is approximately 830Gb whereas actual db size is 630gb as
>> mentioned earlier.
>>
>> I took one big sample table which is 73GB, and using relfilenode(327683)
>> I tried finding the underlying file size du -sh 327683* and able to find
>> the total size the files occpuing and found 113gb.......so for one table
>> itself it is 40Gb additional...
>>
>> I also checked the bloat of table using pgstatstuple and no bloat
>> observed for this sample table
>>
>> Why we have so much increase of file size? How we can avoid or tune it?
>> Is it about reallocating files and not being used fully? What's the
>> solution?
>>
>>
>>
>>
>>