Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wYIaQ-000AwB-0y for pgsql-admin@arkaria.postgresql.org; Sat, 13 Jun 2026 07:15:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wYIaP-00324S-0A for pgsql-admin@arkaria.postgresql.org; Sat, 13 Jun 2026 07:15:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wYIaO-00324K-2B for pgsql-admin@lists.postgresql.org; Sat, 13 Jun 2026 07:15:20 +0000 Received: from mail-lj1-x22e.google.com ([2a00:1450:4864:20::22e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wYIaM-0000000092X-2GL1 for pgsql-admin@lists.postgresql.org; Sat, 13 Jun 2026 07:15:20 +0000 Received: by mail-lj1-x22e.google.com with SMTP id 38308e7fff4ca-3965bc493caso14668391fa.2 for ; Sat, 13 Jun 2026 00:15:17 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1781334917; cv=none; d=google.com; s=arc-20240605; b=VhxCbwgrNsOeVa0QG9XqFHis7fVVFKQmY9nGzuB9Z3uL9hwsE2lIrjlUDOIpW3YKnj VelczVHg+sPs/ug4d3uZ2PHo07DZCT3ImGijU4RB7tIblrUU24VrK7wgShQk4HOKDu+Q B0XVGbrILPuiZHN2cfe7pXEmup90mL3jPHbMwbLp6yzjHUcJVuDcEfyDijkuGdB6RbyV dJEzOLip7b0/PAlkPHSrFg2eDYGkx/KpjmtNVLx+z9d31DJAF7MSiTvYzvrdRRKwXXuD jnOsQplnSBeYR93GY8zU2+cBCbWbQovhX5wnxFBcW6ZDoLuGenQmSWeOv0YTeDnpQO8/ zgTA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=rqWippsOOdP226YT9IK07FlMtseXaGMIXQKZPr0Nd8c=; fh=mL4mDZRxOS+wDBzR2RPLXB840mh/Zi2JNpiRkIG7cpc=; b=O/xOdP7RXMZ57HIIee2VncLAtRvRLcS4xnWs5O/zHMVZAea2SzdmYVN+1tNinmNcCk WFZDE533Asw7TF4hkevvC2IMJxvO6XyoJ1xH8guXIklojrIM46ggrKiIpZEpJV5et6Bk NoBtK1DtRcTkLJ6gKetk7QRe3dIjnBu6SuLt6RSwjc87bL4AG49qnyWkz/fOQMfcd9o+ 8BO19opEAt29Cvpp0iFAp7RAwNVjBWpCOmqSfM1E/6wraU76HX0J7U+eTAN6FoCXe3W6 mPaERy5NG/0a/ebJcGzwwrQBaiM570FCwr/IumMJVYi1d1QjiDC/T3VuDCt3vCZ87dpH +Otg==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1781334917; x=1781939717; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=rqWippsOOdP226YT9IK07FlMtseXaGMIXQKZPr0Nd8c=; b=QIfW89xOXP90C+Bj3kIGTYLE6kKr+vv9wFzAFKgIceysuATtG9DSwiywCZInSwsG93 JgPTEhOlbf8EXj4wTLswbRaBTCBD1BPEaSic1i2XQlFVuG9+GfurW1mf+fzEEaA1H3sA cEe5uusEzWQpAcwMMIcFvFHpsA5D7t1YXcdX44JQ05zcEsOdjMpg8qzvp9fz0DL8qPnq /JSe041UONQnvHxKrjISegDRxBje0mYK9hqwDbLID3CpdXiS8CaM86MygZzs4WlGyxDp BgrdEbQ581Ue7PMoL3mBOTgTytx3rnN3cJHgE6c5979YCdBUI4M5RHd1eLyhH/RFlO31 PUDA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1781334917; x=1781939717; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=rqWippsOOdP226YT9IK07FlMtseXaGMIXQKZPr0Nd8c=; b=frrFwGhgeWpqAkxbvLYV4kjHvH0LfhC97iRzisImHl96D2QrkGsi39V/KtUM9pgszy yiqEsdPx9L0V9M8rYH+TtBEdDyidnpbkCTb/ad/mn1N8n9QbEPd9F0HUzDqRqqfjE8HW Nv7sVmQtdWqXYTdMBwMIWB2l/yM7+u1mUq5S/OvBNjF0iz7Dy3f/T/fowiINcAFyPy2O mcbM7TbNJLJvyMBbUwKPAg/5/fEYIrorgbkGm5o2BgLy3aDThl6W5D4dkPKMR5bdKxIN GhwAnEWglyydEERkEJqDTd5EdZg5Z8xzjnXlgC/xZ8DJlnZRLUM/Yfz4F0BX1dYmlLkU lfLQ== X-Gm-Message-State: AOJu0YxBfVZ34BrhgimjHim5KQrPsB0eIX6wtuzR4z5mlGkR1Q7p+pO9 McvEh8QCTZWnnmLuJlw9rk7ZmWQ6ezXJQBJUTLJyuaa+q79prQa+YnoKCOxfdAgUYn05E9Ap+MJ l9C1qdazYEV6aCLRKnenmZTY99GUmf+I= X-Gm-Gg: Acq92OERTVdGmYQLKQl20bz4umNGjlYZfet0HE/L+/djn38pcTzI92q6OpY9xuhg3za STlQw1y1NnEQIPGlACBcoYaRJaiHH998R2CV/jWzjalA6I8pJp/Rz4Jk+fX1wbd1859zWxO80Z7 MRzs3t3Oz14vmX2ta4J7/XTOeP2zEBF4dI/fB7N+bF3e89qkjPZ/xWUeGlcRWjsiuDSbBsACboO F4Ak4yy1vN1TwSQYLJzJLVdC8b1g8tH5JpliEiuTODq+zjA3JpoGT6K8ZDpr30q/wBpGwewSkHc uzCvp76EMla22QhufYC1UGLRdCwDs5SpD5PNKC7mow== X-Received: by 2002:a05:651c:615:b0:38e:d3e6:a89a with SMTP id 38308e7fff4ca-3992b26cd09mr11255671fa.20.1781334916725; Sat, 13 Jun 2026 00:15:16 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Raj Date: Sat, 13 Jun 2026 12:45:05 +0530 X-Gm-Features: AVVi8Cd-gectMu9JsRl7R17QxQ-64v8OUdKo50-RoFXdDLKw5HPX5Dn9WbVmahU Message-ID: Subject: Re: Size of /pgdata To: Bhanu Prakash Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000effe3e06541d5a15" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000effe3e06541d5a15 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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, wrote: > Checked temp files as well...no thesee are not temp.files > > On Sat, 13 Jun 2026, 12:39 Bhanu Prakash, > 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=E2=80=99s >> Too low work_mem >> >> On Sat, 13 Jun 2026 at 12:18=E2=80=AFPM, Raj 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 siz= e >>> 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 =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 fi= nd >>> the total size the files occpuing and found 113gb.......so for one tabl= e >>> 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? >>> >>> >>> >>> >>> --000000000000effe3e06541d5a15 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
As I was saying I see relfilenode (say 324678).. du -sh 3= 24678* list all files....then totaled their size summing up 113Gb ...wherea= s actual table size is 73gb

On Sat, 13 Jun 2026, 12:40= Raj, <rajeshkumar.dba09@= gmail.com> wrote:
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.=C2=A0

During large query executions, if data to be sorted exceeds wor= k_mem settings, postgresql creates temporary files and spills data to disk.= =C2=A0

Possible reasons = :=C2=A0
Poor query
Large batc= h 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 u= sing 17.9 version.=C2=A0

I see suddenly du -sh /pgdata/17 shows=C2=A0 930GB. Total allocated is 1TB= .

I checked the db size = using \l+ and also pg_total_relation_size and size for particular db is 630= GB. Ignoring postgres and template dbs as they are in KBs.

Now where the remaining 300 comes from a= pproximately..?

I checke= d 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=C2=A0 approximately don't know where it comes from.=

So du -sh /base is appr= oximately 830Gb whereas actual db size is 630gb as mentioned earlier.
=

I took one big sample table w= hich is 73GB, and using relfilenode(327683) I tried finding the underlying = file size du -sh 327683*=C2=A0 and able to find the total size the files oc= cpuing and found 113gb.......so for one table itself it is 40Gb additional.= ..

I also checked the bl= oat of table using pgstatstuple and no bloat observed for this sample table= =C2=A0

Why we have so mu= ch increase of file size? How we can avoid or tune it? Is it about realloca= ting files and not being used fully? What's the solution?




--000000000000effe3e06541d5a15--