Re: Size of /pgdata - Mailing list pgsql-admin

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 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.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 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?