MIME-Version: 1.0
References: 
 <CAJk5Atb1v7=x-wsS-tL7Lxs7yXQy8Ng-GhXG+zzZ1gVAx=Xzjw@mail.gmail.com>
 <CANMw1QKObj2B3=v0t0F-i3VHWE4pctH3G1Xw+B5Hqy55SLTStw@mail.gmail.com>
In-Reply-To: 
 <CANMw1QKObj2B3=v0t0F-i3VHWE4pctH3G1Xw+B5Hqy55SLTStw@mail.gmail.com>
From: Raj <rajeshkumar.dba09@gmail.com>
Date: Sat, 13 Jun 2026 12:40:48 +0530
Message-ID: 
 <CAJk5Atac1PbU-XKPg7+9-rDnXAhNxN5_Y1nSAL=sjK0kxp6wew@mail.gmail.com>
Subject: Re: Size of /pgdata
To: Bhanu Prakash <mallelabhanuprakash@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000009d879706541d4ba7"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJk5Atac1PbU-XKPg7%2B9-rDnXAhNxN5_Y1nSAL%3DsjK0kxp6wew%40mail.gmail.com>
Precedence: bulk

--0000000000009d879706541d4ba7
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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

On Sat, 13 Jun 2026, 12:39 Bhanu Prakash, <mallelabhanuprakash@gmail.com>
wrote:

> Probably, pgsql_tmp file might consumed rest of ~200gb disk. You will fin=
d
> 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=E2=80=99s
> Too low work_mem
>
> On Sat, 13 Jun 2026 at 12:18=E2=80=AFPM, Raj <rajeshkumar.dba09@gmail.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 a=
re
>> 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 =3D 200gb  approximately don't know where it comes from.
>>
>> So du -sh /base is approximately 830Gb whereas actual db size is 630gb a=
s
>> 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 fin=
d
>> 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?
>>
>>
>>
>>
>>

--0000000000009d879706541d4ba7
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto">Checked temp files as well...no thesee are not temp.files=
</div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr"=
 class=3D"gmail_attr">On Sat, 13 Jun 2026, 12:39 Bhanu Prakash, &lt;<a href=
=3D"mailto:mallelabhanuprakash@gmail.com">mallelabhanuprakash@gmail.com</a>=
&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 =
0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir=3D"auto">Proba=
bly, pgsql_tmp file might consumed rest of ~200gb disk. You will find this =
inside base directory.=C2=A0</div><div dir=3D"auto"><br></div><div dir=3D"a=
uto">During large query executions, if data to be sorted exceeds work_mem s=
ettings, postgresql creates temporary files and spills data to disk.=C2=A0<=
/div><div dir=3D"auto"><br></div><div dir=3D"auto">Possible reasons :=C2=A0=
</div><div dir=3D"auto">Poor query</div><div dir=3D"auto">Large batch jobs<=
/div><div dir=3D"auto">Missing index</div><div dir=3D"auto">LRQ=E2=80=99s</=
div><div dir=3D"auto">Too low work_mem</div><div><br><div class=3D"gmail_qu=
ote"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, 13 Jun 2026 at 12:18=E2=
=80=AFPM, Raj &lt;<a href=3D"mailto:rajeshkumar.dba09@gmail.com" target=3D"=
_blank" rel=3D"noreferrer">rajeshkumar.dba09@gmail.com</a>&gt; wrote:<br></=
div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"auto">Hi =
all,<div dir=3D"auto"><br></div><div dir=3D"auto">I am using 17.9 version.=
=C2=A0</div><div dir=3D"auto"><br></div><div dir=3D"auto">I see suddenly du=
 -sh /pgdata/17 shows=C2=A0 930GB. Total allocated is 1TB.</div><div dir=3D=
"auto"><br></div><div dir=3D"auto">I checked the db size using \l+ and also=
 pg_total_relation_size and size for particular db is 630GB. Ignoring postg=
res and template dbs as they are in KBs.</div><div dir=3D"auto"><br></div><=
div dir=3D"auto">Now where the remaining 300 comes from approximately..?</d=
iv><div dir=3D"auto"><br></div><div dir=3D"auto">I checked pg_wal and is 10=
0gb..that is intended based on max_wal_size setting.</div><div dir=3D"auto"=
><br></div><div dir=3D"auto">Postgres logs also in MBs ..so ruling out that=
.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Now 300 - 100 =3D 200g=
b=C2=A0 approximately don&#39;t know where it comes from.</div><div dir=3D"=
auto"><br></div><div dir=3D"auto">So du -sh /base is approximately 830Gb wh=
ereas actual db size is 630gb as mentioned earlier.</div><div dir=3D"auto">=
<br></div><div dir=3D"auto">I took one big sample table which is 73GB, and =
using relfilenode(327683) I tried finding the underlying file size du -sh 3=
27683*=C2=A0 and able to find the total size the files occpuing and found 1=
13gb.......so for one table itself it is 40Gb additional...</div><div dir=
=3D"auto"><br></div><div dir=3D"auto">I also checked the bloat of table usi=
ng pgstatstuple and no bloat observed for this sample table=C2=A0</div><div=
 dir=3D"auto"><br></div><div dir=3D"auto">Why we have so much increase of f=
ile size? How we can avoid or tune it? Is it about reallocating files and n=
ot being used fully? What&#39;s the solution?</div><div dir=3D"auto"><br></=
div><div dir=3D"auto"><br></div><div dir=3D"auto"><br></div><div dir=3D"aut=
o"><br></div></div>
</blockquote></div></div>
</blockquote></div>

--0000000000009d879706541d4ba7--