Re: Size of /pgdata

73GB only table size ? / total size ?

On Sat, 13 Jun 2026 at 12:45 PM, Raj <rajeshkumar(dot)dba09(at)gmail(dot)com> wrote:

> As I was saying I see relfilenode (say 324678).. du -sh 324678* list all
> files....then totaled their size summing up 113Gb ...whereas actual table
> size is 73gb
>
> On Sat, 13 Jun 2026, 12:40 Raj, <rajeshkumar(dot)dba09(at)gmail(dot)com> wrote:
>
>> 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?
>>>>
>>>>
>>>>
>>>>
>>>>