public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bhanu Prakash <[email protected]>
To: Raj <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: Re: Size of /pgdata
Date: Sat, 13 Jun 2026 12:49:21 +0530
Message-ID: <CANMw1QJe70tBc_MMMs2hTAq87PehdK0E6jv2x7D_=Us5LVrDNA@mail.gmail.com> (raw)
In-Reply-To: <CANMw1Q+rVEC7Z8Sxv96j85janHp6j9Oe+P6G5ng41ZnO5Vgkrw@mail.gmail.com>
References: <CAJk5Atb1v7=x-wsS-tL7Lxs7yXQy8Ng-GhXG+zzZ1gVAx=Xzjw@mail.gmail.com>
<CANMw1QKObj2B3=v0t0F-i3VHWE4pctH3G1Xw+B5Hqy55SLTStw@mail.gmail.com>
<CAJk5Atac1PbU-XKPg7+9-rDnXAhNxN5_Y1nSAL=sjK0kxp6wew@mail.gmail.com>
<CAJk5AtaAOREG8zk6UAGFQmeunfQ+vR+D5GmhURbqkRqNObEQDg@mail.gmail.com>
<CANMw1Q+rVEC7Z8Sxv96j85janHp6j9Oe+P6G5ng41ZnO5Vgkrw@mail.gmail.com>
Means is it table + index size ?
On Sat, 13 Jun 2026 at 12:47 PM, Bhanu Prakash <
[email protected]> wrote:
> 73GB only table size ? / total size ?
>
> On Sat, 13 Jun 2026 at 12:45 PM, Raj <[email protected]> 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, <[email protected]> wrote:
>>
>>> Checked temp files as well...no thesee are not temp.files
>>>
>>> On Sat, 13 Jun 2026, 12:39 Bhanu Prakash, <[email protected]>
>>> 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 <[email protected]>
>>>> 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?
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
view thread (20+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Size of /pgdata
In-Reply-To: <CANMw1QJe70tBc_MMMs2hTAq87PehdK0E6jv2x7D_=Us5LVrDNA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox