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 1wYIcw-000AyV-0Y for pgsql-admin@arkaria.postgresql.org; Sat, 13 Jun 2026 07:17:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wYIcv-0035V7-0R for pgsql-admin@arkaria.postgresql.org; Sat, 13 Jun 2026 07:17:57 +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 1wYIcu-0035Uz-2V for pgsql-admin@lists.postgresql.org; Sat, 13 Jun 2026 07:17:56 +0000 Received: from mail-qk1-x72b.google.com ([2607:f8b0:4864:20::72b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wYIcs-0000000095B-1k4o for pgsql-admin@lists.postgresql.org; Sat, 13 Jun 2026 07:17:56 +0000 Received: by mail-qk1-x72b.google.com with SMTP id af79cd13be357-91587626ae1so199144085a.3 for ; Sat, 13 Jun 2026 00:17:54 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1781335072; cv=none; d=google.com; s=arc-20240605; b=ghaYzITktipEXK76wdLo17+BfdzOa8i1ct0YENcPN0/rqlpMsMKfBd24oJyiBjFhiV rkkPxN5ua6Cf5SajZdtVrUIHi8o1HYIAjJHnVDSdZMvy2wgMn7ewoqgt/k3YTRQVXmyZ GmaZosZN1DOO9DkbkYy/RpfilWZZKcy63A45AhQTbQy6jTP0PDfulWkbw8B01+3DVVAF ebDvTxtRpqoc55IAecqF2jbJzRomkyfjSRn0oR/9e+x9vVOFwLonOYriQ7ueTwT6NggG bqLOVhZviU36RpcfFQIGAWbtxMpqQDMTwuUInQYA/08JYRu90R6bYF5VNq70E2IIbUTu O2Bg== 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=DtL37Q9KZr2pSX0POdNc6F98C80brbJKuIe6VFJiiLM=; fh=wdXGVzlufEJVTc6wMaMv0VsO7oswMxTqQBT0LXOkQhg=; b=Cxf9KRKmW94ORbwxthdSHwraxmd+6QrmkQlckLePP+kTQ0ANPKuIdepaap9WnyHnGj LIgPEn37fP4rjEFa2QfqqzqQKivDLJFQRgA8aFVMjlO5SGAoOqwiy1dQmQ5QqhcWPJ6u lBg9tqa6UJLbU5Mw1/NFsvBLkvYXnLK6FNzsmowmMg8ew+I4FhRK/8jgEiAH/UI9Mdas j0yyolFTUkSTiCPwZBcODnWwTiotqbIoem221IqTH5aQ7H2k0nOx3WRgYk2l8o0yYSAM 3r5bUouv4Y3ALy2XG5STwVuXkgnZvgSk57Z+OMpyN7758D51N5jojtMSJCwqxd3IVjsJ G7jw==; 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=1781335072; x=1781939872; 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=DtL37Q9KZr2pSX0POdNc6F98C80brbJKuIe6VFJiiLM=; b=ii+mxzeUnTl0c8OY2TqBeFXe+0KEQjh+p5+35stS7KMGmwk4KlHzFFXyc6p43TEglr wedMf5ZAtkWrcgz13p76r0SzdgOUoH6wwG51TOip44rFwZyPDpaFrLS91Mskjs/IhYfJ 8bWH+J9FBBV3a0kYizgKNzJV1SFbSWcblEsgMECGL7W3HoH2PasnLT+1S8YkbaPBX8uU 7SXY4qxKKdbz/QLcySRSvCuJx/ijmdtL1d5170NU/U5c+ugx9oC2ZUnGLvTJV6w9z38j HuwbA1p8nbe7oPYIwsM3Xy+rNLwnn0yl7A/icObSuCK4D7w/0ZYZ0dZ/uPkyNDt00HrM A+pQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1781335072; x=1781939872; 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=DtL37Q9KZr2pSX0POdNc6F98C80brbJKuIe6VFJiiLM=; b=Gl1Gs/Ul7C2JyC3txMxtOqKjDfsLmaeTF+8DfsDnJC0W5HQEc9ULbuuardVw/BLcO9 YU8vjoM7P73UsqSJla6JGmAr7Qgu3LnIiNIreLrNDfOHgNDiFJqednI7Wv09v/A28Qg6 Ugth9ailmRJsSUre3IzAscnz8YVITnEazBc8j3oRnd4NnNzckl5WKcskhp283s5FNOfj G7/py7n+E4THBb/fxU38ElVlrjBWJK2MHQruaeTb4npMWqP3uvpJ2aFgJ2Bem22jsqbG MbZOHyaDH6t6PUzszuxaSf0+agL5S9YZH/kMlLAjqPqxEW9D1uP10Gn27KJBaKh8YHpD M/bw== X-Gm-Message-State: AOJu0YydzG4op8YHhnPOSTZnaWLaPlTpp702QfAsIcaJLAs+3+48W6uM m6/ZyEudSFOFy8kTSk4kLxVmGVsVEqLDLpjIu3ehqTG8Bkt+Ak2AmEAru8KhIAMDkp/qMcwR6ww LD7WokT99OoBaQo1wIbMwUOss5B3//qkL0Q== X-Gm-Gg: Acq92OE3cnt0rAcbQN88qMzuMnQqkcAnNoXDtVtijEMzcfCo6UlJxjyBn96qKXrrx5E 07qu/cBav6kN2NXrFVPjw4MVlLmJTwOnVBB1Vxkx5zWl3h44Bd3v+sOnSbO5UY4vunhBilIM4wV K6JAR+aai8pQBMbCnf206FEwVXeK4OVmmtaK23GD9g0PB0syH155n4VAsx4v8z8ckFs9w82vJGs Ju835ZfcRHOwmyPPG/d6RGdqna66ytzaKPzXPdR1UO6lccTmeNLOpBDKlsS900JoxtufTV6t7Xe /vicTotX1AaTHr8OKjszze+6xIYUbTFsnaWwhqiH X-Received: by 2002:a05:620a:29d2:b0:915:79c8:ec94 with SMTP id af79cd13be357-9161bcd7135mr857946485a.39.1781335072557; Sat, 13 Jun 2026 00:17:52 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Bhanu Prakash Date: Sat, 13 Jun 2026 12:47:41 +0530 X-Gm-Features: AVVi8Cf_qwE6CvQ7vuYdEDbRscdmOiXJnptzJLhB6PMse3rLl2YVzPSoG2S9cs0 Message-ID: Subject: Re: Size of /pgdata To: Raj Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="00000000000039d06406541d6431" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000039d06406541d6431 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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 >>>> 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 =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 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? >>>> >>>> >>>> >>>> >>>> --00000000000039d06406541d6431 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 saying I see relfilenode (say 324678).. du -sh 324678* list al= l 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:
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.=C2=A0

During large query executions, if data to be= sorted exceeds work_mem settings, postgresql creates temporary files and s= pills data to disk.=C2=A0

Possible reasons :=C2=A0
Poor query
Large batch jobs
Missing index
LRQ=E2=80=99s
Too low work_mem

On Sa= t, 13 Jun 2026 at 12:18=E2=80=AFPM, Raj <rajeshkumar= .dba09@gmail.com> wrote:
Hi all,

I am using 17.9 version.=C2=A0

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

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

Now where the remainin= g 300 comes from approximately..?

I checked pg_wal and is 100gb..that is intended based on max_wal_= size setting.

Postgres l= ogs also in MBs ..so ruling out that.

Now 300 - 100 =3D 200gb=C2=A0 approximately don't know wh= ere it comes from.

So du= -sh /base is approximately 830Gb whereas actual db size is 630gb as mentio= ned earlier.

I took one = big sample table which is 73GB, and using relfilenode(327683) I tried findi= ng the underlying 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 i= s 40Gb additional...

I a= lso checked the bloat of table using pgstatstuple and no bloat observed for= this sample table=C2=A0

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 solut= ion?




--00000000000039d06406541d6431--