public inbox for [email protected]  

help / color / mirror / Atom feed

From: Bhanu Prakash <[email protected]>
To: Raj <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: Re: Size of /pgdata
Date: Sat, 13 Jun 2026 12:39:38 +0530
Message-ID: <CANMw1QKObj2B3=v0t0F-i3VHWE4pctH3G1Xw+B5Hqy55SLTStw@mail.gmail.com> (raw)
In-Reply-To: <CAJk5Atb1v7=x-wsS-tL7Lxs7yXQy8Ng-GhXG+zzZ1gVAx=Xzjw@mail.gmail.com>
References: <CAJk5Atb1v7=x-wsS-tL7Lxs7yXQy8Ng-GhXG+zzZ1gVAx=Xzjw@mail.gmail.com>

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: <CANMw1QKObj2B3=v0t0F-i3VHWE4pctH3G1Xw+B5Hqy55SLTStw@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