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

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

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=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 ar=
e
> 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 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 t=
he
> total size the files occpuing and found 113gb.......so for one table itse=
lf
> it is 40Gb additional...
>
> I also checked the bloat of table using pgstatstuple and no bloat observe=
d
> for this sample table
>
> Why we have so much increase of file size? How we can avoid or tune it? I=
s
> it about reallocating files and not being used fully? What's the solution=
?
>
>
>
>
>

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

<div dir=3D"auto">Probably, pgsql_tmp file might consumed rest of ~200gb di=
sk. You will find this inside base directory.=C2=A0</div><div dir=3D"auto">=
<br></div><div dir=3D"auto">During large query executions, if data to be so=
rted exceeds work_mem settings, postgresql creates temporary files and spil=
ls data to disk.=C2=A0</div><div dir=3D"auto"><br></div><div dir=3D"auto">P=
ossible 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_quote gmail_quote_container"><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">rajeshkumar.dba09@gmail.com</a>&gt; wr=
ote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px=
 0.8ex;border-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 su=
ddenly du -sh /pgdata/17 shows=C2=A0 930GB. Total allocated is 1TB.</div><d=
iv 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. Ignor=
ing postgres and template dbs as they are in KBs.</div><div dir=3D"auto"><b=
r></div><div dir=3D"auto">Now where the remaining 300 comes from approximat=
ely..?</div><div dir=3D"auto"><br></div><div dir=3D"auto">I checked pg_wal =
and is 100gb..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 200gb=C2=A0 approximately don&#39;t know where it comes from.</div><di=
v dir=3D"auto"><br></div><div dir=3D"auto">So du -sh /base is approximately=
 830Gb whereas 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 siz=
e du -sh 327683*=C2=A0 and able to find the total size the files occpuing a=
nd found 113gb.......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 t=
able using 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 incre=
ase of file size? How we can avoid or tune it? Is it about reallocating fil=
es and not 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 di=
r=3D"auto"><br></div></div>
</blockquote></div></div>

--00000000000077e25606541d4722--