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 1wZvHC-001PQS-1s for pgsql-admin@arkaria.postgresql.org; Wed, 17 Jun 2026 18:46:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wZvH9-007FrU-3D for pgsql-admin@arkaria.postgresql.org; Wed, 17 Jun 2026 18:46:12 +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 1wZvH9-007FrM-20 for pgsql-admin@lists.postgresql.org; Wed, 17 Jun 2026 18:46:11 +0000 Received: from mail-lf1-x134.google.com ([2a00:1450:4864:20::134]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wZvH7-00000000xi1-1JTL for pgsql-admin@lists.postgresql.org; Wed, 17 Jun 2026 18:46:11 +0000 Received: by mail-lf1-x134.google.com with SMTP id 2adb3069b0e04-5ad4c229861so88562e87.2 for ; Wed, 17 Jun 2026 11:46:08 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1781721963; cv=none; d=google.com; s=arc-20240605; b=eCjS250EfBVsxvIkBfz174cq7i+WjqUZJyMi4+1Nn3qa+tRNOpTIG8nxNnb5mkZyI4 JyvtuqQFunSkXHyLxzrRUBQy9wMfQqvLPk0MNhUbUApajrlHr9eTUBQhusH8+bJUob9s VtB+i4LwMhhgyCKcuLC+fauiyaP8PzxMEqlDlEvYRSI4KnZKTXuxECsfiXK+GCJIx3L0 YfmD7y9fyoDPlG+vnoc1yndXaH8Q2Ptu7ee91MpsMLX3x+Nlhcor8/k23sWtlYRdEYYQ ZTmjbQDDoY2EIq1QKcEVOXP/RyNUWjXxF++CKstA1wIQhvibSqkXGqD+Iey0Oi0plS+D R0cQ== 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=LSeG/P6fsc6XjjInyJ8eMQf3P58HQPOjyERaB1kml9g=; fh=Cp29mwXDVqKFYgGhOlLUFdXUAv1jULi9WpoUirGEABo=; b=COzAAlI9P757lGbMEY8Nj8LFNPH58o2wyMHQNPjCZZwYOIpt/4WOyfT4QR80oXA6w5 xAF4z1Rj3DGbjrfKuCOc2XrclCp3bhXY+gh6RBIPVsEtDKO4Ln5kzuHdkoPes5EAEMt3 i60hKtHnnlbrGfhB49svBCMLLKyCus/mT5hzyTUh9h7tqiUd8lO2wz8LGnsJhRwv2JJ7 am5PtxkzPi3GCOxifSFVxwWRg9TIsk+rBwrcnMz7V6Hc946spAuYw7BPaXrm3kAm1Oj3 4OlGTZ+CclhwEFgL7R7HYAcB/345eQFcILLx9xOC5iDjzn/sl8H+aK9ae5nz2G43vuXp LKfQ==; 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=1781721963; x=1782326763; 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=LSeG/P6fsc6XjjInyJ8eMQf3P58HQPOjyERaB1kml9g=; b=fxnAKCRTXbVopaWek1suHQegM6o98EvD2DoARcHT0F2ObCnR21epNk28crtSzdzL2f MSfFUlHH9nW5/wfimIiQT7lE0EeARgB1gOgMtlali1wcByRrjuzlTau5PwKxo6f7JoTQ Fq+6byrP89bpem6R5R03HYAglCpryAFfJ+mcBFXATdpo53q+kkfYlZULRqgNkF7DKwFb lNLhxXQA2YHuKFQ5ulkiQ7lEyZbxhTudFSo9MWE6G8e7XF6xu/Cu0qAP3zO9tjJmBJ4I WKa2OOper38tdRXktR+DgKZJNitkGGC2y4IPjMC3zMfdlbxbzK0pXj6+owVI8ll9xD0I 2qKg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1781721963; x=1782326763; 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=LSeG/P6fsc6XjjInyJ8eMQf3P58HQPOjyERaB1kml9g=; b=AQEERbpBDRIQa4botSCRqTHKINxHq+vfw826Bi8EdcldYrkHBbvC7SVsuQDBb/Zfj5 C5NOF38Y/21i2J7ujFRB+clPUItoQKx2RpDv6r9D45EcVR5RgWzAZwy1amv38kLzh4Vb 3zNA9e1EJ7gWq5vmIG1rg5LsQEV8wSd0k1sMb3s0SubkMyfBk6GXtkfbCTheOCVLpF/6 71ggD2QsIHy99e11ySH4Tol32AhEh4NoLyaP5bXh1pXW6pD52d4hLkWhtAVuXVX/zfIM kpXugFe/CIYcm4Zord3Pkbu6ZJU1Ijq1cqBg8BwnxjD2wJCMRJ0oDGX3ZQTD39a97AIp Y4sA== X-Forwarded-Encrypted: i=1; AFNElJ/3kHhStSBtDOetocDrM9H29DLEeIXH1nWeeekBQNYfDaPETXOQYxrckHPSmrSa5hBh3XbcfWydXSQCaQ==@lists.postgresql.org X-Gm-Message-State: AOJu0YwysabeUcvPVPU8V4r83vLR4qM2356p8BBBiD/m9BQ+qMIzzdGk lVef3copKODz7cqCzOn7qVTB5R0vf+VdckYkKs0NRm8dU3gU8rAfZFNXV3fiATJjnHH3OTBKrER 9PQujFkR8gnoHY9uRNB46idQNzZcNsVhD7w== X-Gm-Gg: AfdE7cl126nCk4GrPq4nSOA26SF69eLlDK4Nmj5oQWfckFguL80AwAt89fz78DVAEfd cdgd5QfKt6Jtq8Lsv6QtfMzhKFXtAVkAbpJq00nU3zriFw9jDdiTUem0KOsXtU7UQdJTSbf+Nz2 gVxA1qPS2plA/GO5adC3C8wBqK08I01SMFio07PMuoSMOoF8sTLdi54taZM4nbL9v1I78GgZKQq 1EEJMxL/qL6WBU2PuvualjC1VQkXUTnEonTQU3K//pQd7Hww0hHQZv3vlXLshA5yvUneOnCv3UW urD1P0QH4M99Zg== X-Received: by 2002:a05:6512:61ab:b0:5aa:8824:156a with SMTP id 2adb3069b0e04-5ad4dacaf45mr67422e87.46.1781721962780; Wed, 17 Jun 2026 11:46:02 -0700 (PDT) MIME-Version: 1.0 References: <3a2c4d549587beb3528f6ce09b3d4c448d32f8ee.camel@cybertec.at> In-Reply-To: From: Raj Date: Thu, 18 Jun 2026 00:15:50 +0530 X-Gm-Features: AVVi8Ce9PLmtd0CEskmCtHvz7lNDEonZTby4cy--Nb0Sujgru2hHG-p9ZxXpA4M Message-ID: Subject: Re: Size of /pgdata To: "William Sescu (Suva)" Cc: Laurenz Albe , Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000ae1ee306547778a3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ae1ee306547778a3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Yes, this is what I have been asking. I also mentioned/asked earlier whether it's Abt XFS file system. I checked bitmaps and under flags I could find 'unwritten preallocated extent'. --apparent-size says shows correct size. Is the fix - setting up allocsize? On Wed, 17 Jun 2026, 19:29 William Sescu (Suva), wrote: > In case you are using XFS, this might be related to the "Speculative > preallocation" feature. > We saw the same behavior on Redhat 9, with PostgreSQL 18 and XFS. > > > https://docs.redhat.com/en/documentation/red_hat_enterprise_linux/7/html/= storage_administration_guide/migrating-ext4-xfs > > Speculative preallocation > > XFS uses speculative preallocation to allocate blocks past EOF as files > are written. This avoids file fragmentation due to concurrent streaming > write workloads on NFS servers. By default, this preallocation increases > with the size of the file and will be apparent in "du" output. If a file > with speculative preallocation is not dirtied for five minutes the > preallocation will be discarded. If the inode is cycled out of cache befo= re > that time, then the preallocation will be discarded when the inode is > reclaimed. > If premature ENOSPC problems are seen due to speculative preallocation, a > fixed preallocation amount may be specified with the -o allocsize=3Damoun= t > mount option. > > See also "man xfs" > > allocsize=3Dsize > Sets the buffered I/O end-of-file preallocation size when doing delaye= d > allocation writeout. Valid values for this option are page size (typicall= y > 4KiB) through to > 1GiB, inclusive, in power-of-2 increments. > > The default behavior is for dynamic end-of-file preallocation size, which > uses a set of heuristics to optimise the preallocation size based on the > current allocation > patterns within the file and the access patterns to the file. Specifying = a > fixed allocsize value turns off the dynamic behavior. > > > Cheers > William > > > -----Urspr=C3=BCngliche Nachricht----- > Von: Laurenz Albe > Gesendet: Mittwoch, 17. Juni 2026 13:34 > An: Raj > Cc: Pgsql-admin > Betreff: Re: Size of /pgdata > > > > ACHTUNG: Diese Nachricht kommt von extern. Seien Sie kritisch beim > =C3=96ffnen von Links und Anh=C3=A4ngen. > > > > > On Wed, 2026-06-17 at 11:47 +0530, Raj wrote: > > The file count is same between them..but the file size varies. > > > > For example, assume node1 has issue and node2 is clean. > > > > 1. On both nodes file count is 76 > > 2. File size varies for some of them > > > > Each files are either 1GB or 2GB and some or them are 1.2gb or 1.5gb or > between 1-2gb ....by looking at files.. > > In the problematic node, some of the 1gb files in node2 shows 2GB in > node1...and also file size differences. making the difference. > > That is highly suspicious. > > What do you get if you run > > SHOW segment_size; > > Unless you built PostgreSQL yourself after changing the segment size, it > should be 1GB. But then it would be impossible for some segment to be > bigger than 1GB. > > Are you using some weird file system that reports file sizes wrongly? > > What does "ls -l" on one of these big files show? > > Yours, > Laurenz Albe > > > > ________________________________ > > Disclaimer: > > Diese Nachricht und ihr eventuell angeh=C3=A4ngte Dateien sind nur f=C3= =BCr den > Adressaten bestimmt. Sie kann vertrauliche oder gesetzlich gesch=C3=BCtzt= e Daten > oder Informationen beinhalten. Falls Sie diese Nachricht irrt=C3=BCmlich > erreicht hat, bitten wir Sie h=C3=B6flich, diese unter Ausschluss jeglich= er > Reproduktion zu l=C3=B6schen und die absendende Person zu benachrichtigen= . Danke > f=C3=BCr Ihre Hilfe. > > This message and any attached files are for the sole use of the recipient > named above. It may contain confidential or legally protected data or > information. If you have received this message in error, please delete it > without making any copies whatsoever and notify the sender. Thank you for > your assistance. > --000000000000ae1ee306547778a3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Yes, this is what I have been asking. I also mention= ed/asked earlier whether it's Abt XFS file system.=C2=A0

I checked bitmaps and under flags I could fi= nd 'unwritten preallocated extent'. --apparent-size says shows corr= ect size.

Is the fix - s= etting up allocsize?


On Wed, 17 Jun 2026, 19:29 Wi= lliam Sescu (Suva), <william.se= scu@suva.ch> wrote:
In case = you are using XFS, this might be related to the "Speculative prealloca= tion" feature.
We saw the same behavior on Redhat 9, with PostgreSQL 18 and XFS.

https://docs.redhat.com/en/documentation/red= _hat_enterprise_linux/7/html/storage_administration_guide/migrating-ext4-xf= s

Speculative preallocation

XFS uses speculative preallocation to allocate blocks past EOF as files are= written. This avoids file fragmentation due to concurrent streaming write = workloads on NFS servers. By default, this preallocation increases with the= size of the file and will be apparent in "du" output. If a file = with speculative preallocation is not dirtied for five minutes the prealloc= ation will be discarded. If the inode is cycled out of cache before that ti= me, then the preallocation will be discarded when the inode is reclaimed. If premature ENOSPC problems are seen due to speculative preallocation, a f= ixed preallocation amount may be specified with the -o allocsize=3Damount m= ount option.

See also "man xfs"

allocsize=3Dsize
Sets=C2=A0 the=C2=A0 buffered=C2=A0 I/O end-of-file preallocation size when= doing delayed allocation writeout. Valid values for this option are page s= ize (typically 4KiB) through to
1GiB, inclusive, in power-of-2 increments.

The default behavior is for dynamic end-of-file preallocation size, which u= ses a set of heuristics to optimise the preallocation size based on the cur= rent=C2=A0 allocation
patterns within the file and the access patterns to the file. Specifying a = fixed allocsize value turns off the dynamic behavior.


Cheers
William


-----Urspr=C3=BCngliche Nachricht-----
Von: Laurenz Albe <laurenz.albe@cybertec.at>
Gesendet: Mittwoch, 17. Juni 2026 13:34
An: Raj <rajeshkumar.dba09@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org><= br> Betreff: Re: Size of /pgdata



=C2=A0 ACHTUNG: Diese Nachricht kommt von extern. Seien Sie kritisch beim = =C3=96ffnen von Links und Anh=C3=A4ngen.




On Wed, 2026-06-17 at 11:47 +0530, Raj wrote:
> The file count is same between them..but the file size varies.
>
> For example, assume node1 has issue and node2 is clean.
>
> 1. On both nodes file count is 76
> 2. File size varies for some of them
>
> Each files are either 1GB or 2GB and some or them are 1.2gb or 1.5gb o= r between 1-2gb ....by looking at files..
>=C2=A0 In the problematic node, some of the 1gb files in node2=C2=A0 sh= ows 2GB in node1...and also file size differences. making the difference.
That is highly suspicious.

What do you get if you run

=C2=A0 SHOW segment_size;

Unless you built PostgreSQL yourself after changing the segment size, it sh= ould be 1GB.=C2=A0 But then it would be impossible for some segment to be b= igger than 1GB.

Are you using some weird file system that reports file sizes wrongly?

What does "ls -l" on one of these big files show?

Yours,
Laurenz Albe



________________________________

Disclaimer:

Diese Nachricht und ihr eventuell angeh=C3=A4ngte Dateien sind nur f=C3=BCr= den Adressaten bestimmt. Sie kann vertrauliche oder gesetzlich gesch=C3=BC= tzte Daten oder Informationen beinhalten. Falls Sie diese Nachricht irrt=C3= =BCmlich erreicht hat, bitten wir Sie h=C3=B6flich, diese unter Ausschluss = jeglicher Reproduktion zu l=C3=B6schen und die absendende Person zu benachr= ichtigen. Danke f=C3=BCr Ihre Hilfe.

This message and any attached files are for the sole use of the recipient n= amed above. It may contain confidential or legally protected data or inform= ation. If you have received this message in error, please delete it without= making any copies whatsoever and notify the sender. Thank you for your ass= istance.
--000000000000ae1ee306547778a3--