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 1wYIec-000Az8-2T for pgsql-admin@arkaria.postgresql.org; Sat, 13 Jun 2026 07:19:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wYIeZ-0038tB-1T for pgsql-admin@arkaria.postgresql.org; Sat, 13 Jun 2026 07:19:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wYIeY-0038sk-1t for pgsql-admin@lists.postgresql.org; Sat, 13 Jun 2026 07:19:39 +0000 Received: from mail-qk1-x730.google.com ([2607:f8b0:4864:20::730]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wYIeU-000000007vd-2qj6 for pgsql-admin@lists.postgresql.org; Sat, 13 Jun 2026 07:19:35 +0000 Received: by mail-qk1-x730.google.com with SMTP id af79cd13be357-915b5ce94c7so183071385a.2 for ; Sat, 13 Jun 2026 00:19:34 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1781335173; cv=none; d=google.com; s=arc-20240605; b=LzCmnrEPnW9CpH3XjOHHxJJyPjXEHRW1Wb/EqRPtylewSn/fLAL/cqS8eJ25X/jUnd zUeeHJTK6RJX5ZnBJw/ejR3ZsNwxZDHb8hCH+RwG+rEOOdPw1nUv1qxN+yaycmCTj8Ji Y0hpNRu+clkhbfY/LxDQvqlfTgBp02MKeLelTuLNs3CY/ow+TUfY6M3LhaT2NBiw9HRr aeA3XrE5B56NsVO0AuVwwxVlpOh3TQILS5cb44440KqjfNmJFFie2+dS+1/dNFMokKmv 13FktyMnSDoV7D5nrKtpONCzemhRqUC4BoqovEGrhOKBBTR/37Rk1dOrN0TCIZalqQ1e F3EQ== 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=biSV6/x8bvbHsdZG9qTP7Is2XLKq1c446Q6Ukw9ZXvY=; fh=wdXGVzlufEJVTc6wMaMv0VsO7oswMxTqQBT0LXOkQhg=; b=cqrJPAsgOfSf+4znJV5kqwdWP43o7mJ28DsVdS03hoS5vUrXfH8ftPEi1HO2uxYpMr G0F4fwFx7o1KbRgIPIhEKbbjFbM0Hxgn3TiY0lald5HW0iOJ4OVQ6JO/IwSxRg4KJLXb Rk6B0G1iWWkyqG13yGuqlq9tLuGvsDS/t3wen+ZY2tpIySdHgqC2KuHpp1dvIN/Y6Nes MZ7Cv9owz4w0qiZWRI71ZUs/lph/E9nIVO2JLgOAfv1ewYh+oUtPCxcHOUcSzAU7wA0P oMsqKaPgls7n4//pHe2HZCwJH+EFaVLRiep64xlNIdURQ+u+sBJ/xZmrji1PItQhf6/r YiBQ==; 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=1781335173; x=1781939973; 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=biSV6/x8bvbHsdZG9qTP7Is2XLKq1c446Q6Ukw9ZXvY=; b=lFa4NfeReNKeEmnE5EZxNh5l5u8YhT+2XPdAMuj21YAP+gz+nxBZz8kHHY98142D5D ty+lzA3aOLa/lyhb+Lt0Qqd4rVQb9NBPqEoRDbv6Obl1sJL3qPahAPpqoUwxDm6d4x4g MohoIFDpEFn56W9BRBQHj1inipR/8IStE3YR4z5FmIOcihP/MSUdcA9SulHxLAYxPcuC ayZvrEzVDMjoNx2ils+s9IN1BQIf8M4Ey6LHx1e+T6b6N58HsB1nvCYiBDVghIf2KqLO Prvg5rtouTMeVjSReQjxlqf7kkWt16AVzhwSl4pPt7ruBWEyHiuHhkE8W2Ky+8nrPwRU dB0A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1781335173; x=1781939973; 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=biSV6/x8bvbHsdZG9qTP7Is2XLKq1c446Q6Ukw9ZXvY=; b=pdG/fmmdAzAoWzPGNIEDV2xtoECfz6eIlX526nwbYReOk8RyqdtR5xjJrScdWdtD/V aEpvQGSx8VKJUJWcKOFj4k1K7rqucfBV30ZhrvKzRjG7sZG4Y6KYjZMb1akj0HIh2fZV QWxJS4KypFIGXHr1Im/mkK66h3zWyqMfMMxz4tva0kFr/v7LXf/h6TkgjeBzBn1naBAs r0q0SMXPPcmrmi2MQ8OjBoVSF5lUbQd4R+hPCje68xeI/dAFmxK8Y5lCfIEW3zKUEgIw KGk04AgGMaDOuoGLAn0TLaZZyiptzTvzZK0z1SFdvVWG/x9kVxWJmz9Ot4TW8St/qk21 A71Q== X-Gm-Message-State: AOJu0Yxhw8G1Rg4QpQKDGn1CX83YCiBCbwc5ZfVkT/B3wzTYnHNIMD4K s/NDqLGdTbtJmzPNePHnbr43HlmRRaUd6S+4UJCAUY3TUDJolajdu2rAfUieJzS/G13VqPGVKo9 TWRK2mP88hjOXhZg4TI0s3ofGhhgr+rM4Hg== X-Gm-Gg: Acq92OExE9feJxJZHXZc4uguxfKVkU3DgCQFXdDEf8SUWommFKHpnR0ShL6yYFAOnOf alKdKbu+N8pKAmOpxokIqwn9OdaQINiDt+dg9s41m19+rzRVPYMxDUOCHsQUjw3aYlg+fSD1K1o 1kG51K67XHb4Twx3l5PK7kkToK2O/9ooDIUTuO7uUxjKJvB8zN3wJitir8uWeImvv1z0eyiY9RX Q/uSk2iHkQBStAecXzEVTXyzSFcd8kc7oBRtfopC1TPM0yEA9ZMNGopP8M6zcR7pgrEKaBkfAuC uB2kodEkjx3rWjmfm91Jz+LgSsrwvpW6eutoX1iC X-Received: by 2002:a05:620a:44d6:b0:913:dcd2:f132 with SMTP id af79cd13be357-917f1177c5emr459316985a.40.1781335173598; Sat, 13 Jun 2026 00:19:33 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Bhanu Prakash Date: Sat, 13 Jun 2026 12:49:21 +0530 X-Gm-Features: AVVi8Cd-m7iGIO6u_fx80qmWdm0MAAlp-XISVye0Tz8E8smumX6Nc5cf3y3Tjr0 Message-ID: Subject: Re: Size of /pgdata To: Raj Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="0000000000003f91f606541d6aa1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003f91f606541d6aa1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Means is it table + index size ? On Sat, 13 Jun 2026 at 12:47=E2=80=AFPM, Bhanu Prakash < mallelabhanuprakash@gmail.com> wrote: > 73GB only table size ? / total size ? > > On Sat, 13 Jun 2026 at 12:45=E2=80=AFPM, Raj wrote: > >> As I was saying I see relfilenode (say 324678).. du -sh 324678* list all >> files....then totaled their size summing up 113Gb ...whereas actual tabl= e >> 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 >>>>> size for particular db is 630GB. Ignoring postgres and template dbs a= s 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 630g= b >>>>> 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 t= he >>>>> solution? >>>>> >>>>> >>>>> >>>>> >>>>> --0000000000003f91f606541d6aa1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Means is it table + index size ?

On Sat, 13 Jun 2026 at 12:47=E2=80=AFPM, Bhanu Prakash <mallelabhanuprakash@gmail.com> wr= ote:
73GB only table size ? / total size ?=C2=A0

On Sat, 13 Jun 2026 = at 12:45=E2=80=AFPM, Raj <rajeshkumar.dba09@gmail.com> wrote:
As I was sa= ying 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, <rajeshkumar.dba09@gmail.com> wrote:
Checke= d temp files as well...no thesee are not temp.files

On Sat, 13 Jun 2026, 12:= 39 Bhanu Prakash, <mallelabhanuprakash@gmail.com> wro= te:
Probably, pgsql_tmp file might consumed rest of ~200gb disk. You will= find this inside base directory.=C2=A0

During large query executions, if data to be sorted exceeds= work_mem settings, postgresql creates temporary files and spills data to d= isk.=C2=A0

Possible reas= ons :=C2=A0
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.co= m> wrote:
Hi all,

I a= m using 17.9 version.=C2=A0

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

I checked the db si= ze 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 fro= m approximately..?

I che= cked pg_wal and is 100gb..that is intended based on max_wal_size setting.

Postgres logs also in MBs= ..so ruling out that.

N= ow 300 - 100 =3D 200gb=C2=A0 approximately don't know where it comes fr= om.

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

I took one big sample tabl= e which is 73GB, and using relfilenode(327683) I tried finding the underlyi= ng file size du -sh 327683*=C2=A0 and able to find the total size the files= occpuing and found 113gb.......so for one table itself it is 40Gb addition= al...

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

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




--0000000000003f91f606541d6aa1--