public inbox for [email protected]
help / color / mirror / Atom feedSize of /pgdata
20+ messages / 6 participants
[nested] [flat]
* Size of /pgdata
@ 2026-06-13 06:48 Raj <[email protected]>
2026-06-13 07:09 ` Re: Size of /pgdata Bhanu Prakash <[email protected]>
2026-06-13 12:39 ` Re: Size of /pgdata Ron Johnson <[email protected]>
2026-06-14 19:30 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
0 siblings, 3 replies; 20+ messages in thread
From: Raj @ 2026-06-13 06:48 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
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?
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
@ 2026-06-13 07:09 ` Bhanu Prakash <[email protected]>
2026-06-13 07:10 ` Re: Size of /pgdata Raj <[email protected]>
2 siblings, 1 reply; 20+ messages in thread
From: Bhanu Prakash @ 2026-06-13 07:09 UTC (permalink / raw)
To: Raj <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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 <[email protected]> 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?
>
>
>
>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-13 07:09 ` Re: Size of /pgdata Bhanu Prakash <[email protected]>
@ 2026-06-13 07:10 ` Raj <[email protected]>
2026-06-13 07:15 ` Re: Size of /pgdata Raj <[email protected]>
0 siblings, 1 reply; 20+ messages in thread
From: Raj @ 2026-06-13 07:10 UTC (permalink / raw)
To: Bhanu Prakash <[email protected]>; +Cc: Pgsql-admin <[email protected]>
Checked temp files as well...no thesee are not temp.files
On Sat, 13 Jun 2026, 12:39 Bhanu Prakash, <[email protected]>
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 <[email protected]> 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?
>>
>>
>>
>>
>>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-13 07:09 ` Re: Size of /pgdata Bhanu Prakash <[email protected]>
2026-06-13 07:10 ` Re: Size of /pgdata Raj <[email protected]>
@ 2026-06-13 07:15 ` Raj <[email protected]>
2026-06-13 07:17 ` Re: Size of /pgdata Bhanu Prakash <[email protected]>
0 siblings, 1 reply; 20+ messages in thread
From: Raj @ 2026-06-13 07:15 UTC (permalink / raw)
To: Bhanu Prakash <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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, <[email protected]> wrote:
> Checked temp files as well...no thesee are not temp.files
>
> On Sat, 13 Jun 2026, 12:39 Bhanu Prakash, <[email protected]>
> 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 <[email protected]> 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?
>>>
>>>
>>>
>>>
>>>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-13 07:09 ` Re: Size of /pgdata Bhanu Prakash <[email protected]>
2026-06-13 07:10 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-13 07:15 ` Re: Size of /pgdata Raj <[email protected]>
@ 2026-06-13 07:17 ` Bhanu Prakash <[email protected]>
2026-06-13 07:19 ` Re: Size of /pgdata Bhanu Prakash <[email protected]>
0 siblings, 1 reply; 20+ messages in thread
From: Bhanu Prakash @ 2026-06-13 07:17 UTC (permalink / raw)
To: Raj <[email protected]>; +Cc: Pgsql-admin <[email protected]>
73GB only table size ? / total size ?
On Sat, 13 Jun 2026 at 12:45 PM, Raj <[email protected]> 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, <[email protected]> wrote:
>
>> Checked temp files as well...no thesee are not temp.files
>>
>> On Sat, 13 Jun 2026, 12:39 Bhanu Prakash, <[email protected]>
>> 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 <[email protected]>
>>> 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?
>>>>
>>>>
>>>>
>>>>
>>>>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-13 07:09 ` Re: Size of /pgdata Bhanu Prakash <[email protected]>
2026-06-13 07:10 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-13 07:15 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-13 07:17 ` Re: Size of /pgdata Bhanu Prakash <[email protected]>
@ 2026-06-13 07:19 ` Bhanu Prakash <[email protected]>
0 siblings, 0 replies; 20+ messages in thread
From: Bhanu Prakash @ 2026-06-13 07:19 UTC (permalink / raw)
To: Raj <[email protected]>; +Cc: Pgsql-admin <[email protected]>
Means is it table + index size ?
On Sat, 13 Jun 2026 at 12:47 PM, Bhanu Prakash <
[email protected]> wrote:
> 73GB only table size ? / total size ?
>
> On Sat, 13 Jun 2026 at 12:45 PM, Raj <[email protected]> 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, <[email protected]> wrote:
>>
>>> Checked temp files as well...no thesee are not temp.files
>>>
>>> On Sat, 13 Jun 2026, 12:39 Bhanu Prakash, <[email protected]>
>>> 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 <[email protected]>
>>>> 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?
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
@ 2026-06-13 12:39 ` Ron Johnson <[email protected]>
2 siblings, 0 replies; 20+ messages in thread
From: Ron Johnson @ 2026-06-13 12:39 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Sat, Jun 13, 2026 at 2:48 AM Raj <[email protected]> wrote:
> Hi all,
>
> I am using 17.9 version.
>
> I see suddenly du -sh /pgdata/17 shows 930GB. Total allocated is 1TB.
>
Don't stop with "-s". Drill deeper:
du -hd2 /pgdata/17
>
> 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.
>
Use pg_database_size() instead, since pg_total_relation_size() might miss
something.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
@ 2026-06-14 19:30 ` Laurenz Albe <[email protected]>
2026-06-15 01:18 ` Re: Size of /pgdata Raj <[email protected]>
2 siblings, 1 reply; 20+ messages in thread
From: Laurenz Albe @ 2026-06-14 19:30 UTC (permalink / raw)
To: Raj <[email protected]>; Pgsql-admin <[email protected]>
On Sat, 2026-06-13 at 12:18 +0530, Raj wrote:
> 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...
Investigate more:
- Make sure that you don't get files from another filenode like 3276838.
- If the relation (as shown by pg_relation_size('tablename')) is 73GB,
it should consist of 327683, 327683.1, 327683.2, ..., 327683.72.
Check what additional files there are.
- Extra files can be left behind after a crash. Were there any?
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-14 19:30 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
@ 2026-06-15 01:18 ` Raj <[email protected]>
2026-06-15 06:17 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
0 siblings, 1 reply; 20+ messages in thread
From: Raj @ 2026-06-15 01:18 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Pgsql-admin <[email protected]>
No, I am using the command du -ch 327101*
On Mon, 15 Jun 2026, 01:00 Laurenz Albe, <[email protected]> wrote:
> On Sat, 2026-06-13 at 12:18 +0530, Raj wrote:
> > 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...
>
> Investigate more:
>
> - Make sure that you don't get files from another filenode like 3276838.
>
> - If the relation (as shown by pg_relation_size('tablename')) is 73GB,
> it should consist of 327683, 327683.1, 327683.2, ..., 327683.72.
> Check what additional files there are.
>
> - Extra files can be left behind after a crash. Were there any?
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-14 19:30 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 01:18 ` Re: Size of /pgdata Raj <[email protected]>
@ 2026-06-15 06:17 ` Laurenz Albe <[email protected]>
2026-06-15 08:58 ` Re: Size of /pgdata Raj <[email protected]>
0 siblings, 1 reply; 20+ messages in thread
From: Laurenz Albe @ 2026-06-15 06:17 UTC (permalink / raw)
To: Raj <[email protected]>; +Cc: Pgsql-admin <[email protected]>
On Mon, 2026-06-15 at 06:48 +0530, Raj wrote:
> On Mon, 15 Jun 2026, 01:00 Laurenz Albe, <[email protected]> wrote:
> > On Sat, 2026-06-13 at 12:18 +0530, Raj wrote:
> > > 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...
> >
> > Investigate more:
> >
> > - Make sure that you don't get files from another filenode like 3276838.
> >
> > - If the relation (as shown by pg_relation_size('tablename')) is 73GB,
> > it should consist of 327683, 327683.1, 327683.2, ..., 327683.72.
> > Check what additional files there are.
> >
> > - Extra files can be left behind after a crash. Were there any?
>
> No, I am using the command du -ch 327101*
That reply does not make any sense to me.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-14 19:30 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 01:18 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 06:17 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
@ 2026-06-15 08:58 ` Raj <[email protected]>
2026-06-15 15:21 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
0 siblings, 1 reply; 20+ messages in thread
From: Raj @ 2026-06-15 08:58 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Pgsql-admin <[email protected]>
The command du -ch 327101* picks all files 327202, 327101.1, 327101.2
.......and give each of them sizes and also total size of all files listed
as u see in my earlier screenshot
On Mon, 15 Jun 2026, 11:47 Laurenz Albe, <[email protected]> wrote:
> On Mon, 2026-06-15 at 06:48 +0530, Raj wrote:
> > On Mon, 15 Jun 2026, 01:00 Laurenz Albe, <[email protected]>
> wrote:
> > > On Sat, 2026-06-13 at 12:18 +0530, Raj wrote:
> > > > 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...
> > >
> > > Investigate more:
> > >
> > > - Make sure that you don't get files from another filenode like
> 3276838.
> > >
> > > - If the relation (as shown by pg_relation_size('tablename')) is 73GB,
> > > it should consist of 327683, 327683.1, 327683.2, ..., 327683.72.
> > > Check what additional files there are.
> > >
> > > - Extra files can be left behind after a crash. Were there any?
> >
> > No, I am using the command du -ch 327101*
>
> That reply does not make any sense to me.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-14 19:30 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 01:18 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 06:17 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 08:58 ` Re: Size of /pgdata Raj <[email protected]>
@ 2026-06-15 15:21 ` Laurenz Albe <[email protected]>
2026-06-16 10:00 ` Re: Size of /pgdata Raj <[email protected]>
0 siblings, 1 reply; 20+ messages in thread
From: Laurenz Albe @ 2026-06-15 15:21 UTC (permalink / raw)
To: Raj <[email protected]>; +Cc: Pgsql-admin <[email protected]>
On Mon, 2026-06-15 at 14:28 +0530, Raj wrote:
> The command du -ch 327101* picks all files 327202, 327101.1, 327101.2 .......
> and give each of them sizes and also total size of all files listed as u see in my earlier screenshot
I guess you didn't read my answer.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-14 19:30 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 01:18 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 06:17 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 08:58 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 15:21 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
@ 2026-06-16 10:00 ` Raj <[email protected]>
2026-06-16 13:44 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
0 siblings, 1 reply; 20+ messages in thread
From: Raj @ 2026-06-16 10:00 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Pgsql-admin <[email protected]>
I answered it. There were no additional files.. I am not including files of
other relfilenode.
Moreover in a 3 node PATRONI...other two nodes are didn't have this
issue....only one node has this issue of disk space difference of around
200gb
On Mon, 15 Jun 2026, 20:51 Laurenz Albe, <[email protected]> wrote:
> On Mon, 2026-06-15 at 14:28 +0530, Raj wrote:
> > The command du -ch 327101* picks all files 327202, 327101.1, 327101.2
> .......
> > and give each of them sizes and also total size of all files listed as u
> see in my earlier screenshot
>
> I guess you didn't read my answer.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-14 19:30 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 01:18 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 06:17 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 08:58 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 15:21 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-16 10:00 ` Re: Size of /pgdata Raj <[email protected]>
@ 2026-06-16 13:44 ` Laurenz Albe <[email protected]>
2026-06-17 06:17 ` Re: Size of /pgdata Raj <[email protected]>
0 siblings, 1 reply; 20+ messages in thread
From: Laurenz Albe @ 2026-06-16 13:44 UTC (permalink / raw)
To: Raj <[email protected]>; +Cc: Pgsql-admin <[email protected]>
On Tue, 2026-06-16 at 15:30 +0530, Raj wrote:
> Moreover in a 3 node PATRONI...other two nodes are didn't have this issue....
> only one node has this issue of disk space difference of around 200gb
Well, figure out which files are different.
Perhaps you had a crash on one of the nodes that left some orphaned files around.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-14 19:30 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 01:18 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 06:17 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 08:58 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 15:21 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-16 10:00 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-16 13:44 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
@ 2026-06-17 06:17 ` Raj <[email protected]>
2026-06-17 11:34 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
0 siblings, 1 reply; 20+ messages in thread
From: Raj @ 2026-06-17 06:17 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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.
On Tue, 16 Jun 2026, 19:14 Laurenz Albe, <[email protected]> wrote:
> On Tue, 2026-06-16 at 15:30 +0530, Raj wrote:
> > Moreover in a 3 node PATRONI...other two nodes are didn't have this
> issue....
> > only one node has this issue of disk space difference of around 200gb
>
> Well, figure out which files are different.
>
> Perhaps you had a crash on one of the nodes that left some orphaned files
> around.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-14 19:30 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 01:18 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 06:17 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 08:58 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 15:21 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-16 10:00 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-16 13:44 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-17 06:17 ` Re: Size of /pgdata Raj <[email protected]>
@ 2026-06-17 11:34 ` Laurenz Albe <[email protected]>
2026-06-17 13:59 ` AW: Size of /pgdata William Sescu (Suva) <[email protected]>
0 siblings, 1 reply; 20+ messages in thread
From: Laurenz Albe @ 2026-06-17 11:34 UTC (permalink / raw)
To: Raj <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* AW: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-14 19:30 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 01:18 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 06:17 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 08:58 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 15:21 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-16 10:00 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-16 13:44 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-17 06:17 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-17 11:34 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
@ 2026-06-17 13:59 ` William Sescu (Suva) <[email protected]>
2026-06-17 18:45 ` Re: Size of /pgdata Raj <[email protected]>
0 siblings, 1 reply; 20+ messages in thread
From: William Sescu (Suva) @ 2026-06-17 13:59 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; Raj <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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_guid...
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 before 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=amount mount option.
See also "man xfs"
allocsize=size
Sets the buffered I/O end-of-file preallocation size when doing delayed allocation writeout. Valid values for this option are page size (typically 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üngliche Nachricht-----
Von: Laurenz Albe <[email protected]>
Gesendet: Mittwoch, 17. Juni 2026 13:34
An: Raj <[email protected]>
Cc: Pgsql-admin <[email protected]>
Betreff: Re: Size of /pgdata
ACHTUNG: Diese Nachricht kommt von extern. Seien Sie kritisch beim Öffnen von Links und Anhängen.
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ängte Dateien sind nur für den Adressaten bestimmt. Sie kann vertrauliche oder gesetzlich geschützte Daten oder Informationen beinhalten. Falls Sie diese Nachricht irrtümlich erreicht hat, bitten wir Sie höflich, diese unter Ausschluss jeglicher Reproduktion zu löschen und die absendende Person zu benachrichtigen. Danke für 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.
Attachments:
[application/pkcs7-signature] smime.p7s (5.8K, ../../GV0P278MB0244DE5A2049D0A42E471445E8E42@GV0P278MB0244.CHEP278.PROD.OUTLOOK.COM/2-smime.p7s)
download
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-14 19:30 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 01:18 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 06:17 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 08:58 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 15:21 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-16 10:00 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-16 13:44 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-17 06:17 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-17 11:34 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-17 13:59 ` AW: Size of /pgdata William Sescu (Suva) <[email protected]>
@ 2026-06-17 18:45 ` Raj <[email protected]>
2026-06-18 04:59 ` AW: Size of /pgdata William Sescu (Suva) <[email protected]>
0 siblings, 1 reply; 20+ messages in thread
From: Raj @ 2026-06-17 18:45 UTC (permalink / raw)
To: William Sescu (Suva) <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Pgsql-admin <[email protected]>
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), <[email protected]>
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_guid...
>
> 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 before
> 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=amount
> mount option.
>
> See also "man xfs"
>
> allocsize=size
> Sets the buffered I/O end-of-file preallocation size when doing delayed
> allocation writeout. Valid values for this option are page size (typically
> 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üngliche Nachricht-----
> Von: Laurenz Albe <[email protected]>
> Gesendet: Mittwoch, 17. Juni 2026 13:34
> An: Raj <[email protected]>
> Cc: Pgsql-admin <[email protected]>
> Betreff: Re: Size of /pgdata
>
>
>
> ACHTUNG: Diese Nachricht kommt von extern. Seien Sie kritisch beim
> Öffnen von Links und Anhängen.
>
>
>
>
> 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ängte Dateien sind nur für den
> Adressaten bestimmt. Sie kann vertrauliche oder gesetzlich geschützte Daten
> oder Informationen beinhalten. Falls Sie diese Nachricht irrtümlich
> erreicht hat, bitten wir Sie höflich, diese unter Ausschluss jeglicher
> Reproduktion zu löschen und die absendende Person zu benachrichtigen. Danke
> für 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.
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* AW: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-14 19:30 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 01:18 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 06:17 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 08:58 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 15:21 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-16 10:00 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-16 13:44 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-17 06:17 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-17 11:34 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-17 13:59 ` AW: Size of /pgdata William Sescu (Suva) <[email protected]>
2026-06-17 18:45 ` Re: Size of /pgdata Raj <[email protected]>
@ 2026-06-18 04:59 ` William Sescu (Suva) <[email protected]>
2026-06-18 20:33 ` Re: AW: Size of /pgdata Aleksey M Boltenkov <[email protected]>
0 siblings, 1 reply; 20+ messages in thread
From: William Sescu (Suva) @ 2026-06-18 04:59 UTC (permalink / raw)
To: Raj <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Pgsql-admin <[email protected]>
By setting XFS allocsize, you switch off the dynamic behavior, and it becomes more predictable.
Another fix might be a switch to a different filesystem. E.g. ext4
While this XFS feature is a quite old one, I have seen it the first time kicking in quite heavily only on Red Hat 9, with PostgreSQL 18 and
only on the standby. Some times double the size for some files.
Von: Raj <[email protected]>
Gesendet: Mittwoch, 17. Juni 2026 20:46
An: Sescu William (SW0) <[email protected]>
Cc: Laurenz Albe <[email protected]>; Pgsql-admin <[email protected]>
Betreff: Re: Size of /pgdata
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), <[email protected]<mailto:[email protected]>> 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_guid...
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 before 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=amount mount option.
See also "man xfs"
allocsize=size
Sets the buffered I/O end-of-file preallocation size when doing delayed allocation writeout. Valid values for this option are page size (typically 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üngliche Nachricht-----
Von: Laurenz Albe <[email protected]<mailto:[email protected]>>
Gesendet: Mittwoch, 17. Juni 2026 13:34
An: Raj <[email protected]<mailto:[email protected]>>
Cc: Pgsql-admin <[email protected]<mailto:[email protected]>>
Betreff: Re: Size of /pgdata
ACHTUNG: Diese Nachricht kommt von extern. Seien Sie kritisch beim Öffnen von Links und Anhängen.
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ängte Dateien sind nur für den Adressaten bestimmt. Sie kann vertrauliche oder gesetzlich geschützte Daten oder Informationen beinhalten. Falls Sie diese Nachricht irrtümlich erreicht hat, bitten wir Sie höflich, diese unter Ausschluss jeglicher Reproduktion zu löschen und die absendende Person zu benachrichtigen. Danke für 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.
Attachments:
[application/pkcs7-signature] smime.p7s (5.8K, ../../GV0P278MB0244AD72B327E57199FDD07CE8E32@GV0P278MB0244.CHEP278.PROD.OUTLOOK.COM/3-smime.p7s)
download
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: AW: Size of /pgdata
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-14 19:30 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 01:18 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 06:17 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-15 08:58 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-15 15:21 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-16 10:00 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-16 13:44 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-17 06:17 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-17 11:34 ` Re: Size of /pgdata Laurenz Albe <[email protected]>
2026-06-17 13:59 ` AW: Size of /pgdata William Sescu (Suva) <[email protected]>
2026-06-17 18:45 ` Re: Size of /pgdata Raj <[email protected]>
2026-06-18 04:59 ` AW: Size of /pgdata William Sescu (Suva) <[email protected]>
@ 2026-06-18 20:33 ` Aleksey M Boltenkov <[email protected]>
0 siblings, 0 replies; 20+ messages in thread
From: Aleksey M Boltenkov @ 2026-06-18 20:33 UTC (permalink / raw)
To: William Sescu (Suva) <[email protected]>; Raj <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Pgsql-admin <[email protected]>
On 6/18/26 07:59, William Sescu (Suva) wrote:
> By setting XFS allocsize, you switch off the dynamic behavior, and it
> becomes more predictable.
>
> Another fix might be a switch to a different filesystem. E.g. ext4
>
> While this XFS feature is a quite old one, I have seen it the first time
> kicking in quite heavily only on Red Hat 9, with PostgreSQL 18 and
>
> only on the standby. Some times double the size for some files.
>
> *Von:*Raj <[email protected]>
> *Gesendet:* Mittwoch, 17. Juni 2026 20:46
> *An:* Sescu William (SW0) <[email protected]>
> *Cc:* Laurenz Albe <[email protected]>; Pgsql-admin <pgsql-
> [email protected]>
> *Betreff:* Re: Size of /pgdata
>
> 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), <[email protected]
> <mailto:[email protected]>> 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 <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 before 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=amount mount option.
>
> See also "man xfs"
>
> allocsize=size
> Sets the buffered I/O end-of-file preallocation size when doing
> delayed allocation writeout. Valid values for this option are page
> size (typically 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üngliche Nachricht-----
> Von: Laurenz Albe <[email protected]
> <mailto:[email protected]>>
> Gesendet: Mittwoch, 17. Juni 2026 13:34
> An: Raj <[email protected]
> <mailto:[email protected]>>
> Cc: Pgsql-admin <[email protected] <mailto:pgsql-
> [email protected]>>
> Betreff: Re: Size of /pgdata
>
>
>
> ACHTUNG: Diese Nachricht kommt von extern. Seien Sie kritisch
> beim Öffnen von Links und Anhängen.
>
>
>
>
> 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ängte Dateien sind nur für
> den Adressaten bestimmt. Sie kann vertrauliche oder gesetzlich
> geschützte Daten oder Informationen beinhalten. Falls Sie diese
> Nachricht irrtümlich erreicht hat, bitten wir Sie höflich, diese
> unter Ausschluss jeglicher Reproduktion zu löschen und die
> absendende Person zu benachrichtigen. Danke für 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.
>
R U joking? Preallocation is around kilobytes, some time megabytes.
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2026-06-18 20:33 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-13 06:48 Size of /pgdata Raj <[email protected]>
2026-06-13 07:09 ` Bhanu Prakash <[email protected]>
2026-06-13 07:10 ` Raj <[email protected]>
2026-06-13 07:15 ` Raj <[email protected]>
2026-06-13 07:17 ` Bhanu Prakash <[email protected]>
2026-06-13 07:19 ` Bhanu Prakash <[email protected]>
2026-06-13 12:39 ` Ron Johnson <[email protected]>
2026-06-14 19:30 ` Laurenz Albe <[email protected]>
2026-06-15 01:18 ` Raj <[email protected]>
2026-06-15 06:17 ` Laurenz Albe <[email protected]>
2026-06-15 08:58 ` Raj <[email protected]>
2026-06-15 15:21 ` Laurenz Albe <[email protected]>
2026-06-16 10:00 ` Raj <[email protected]>
2026-06-16 13:44 ` Laurenz Albe <[email protected]>
2026-06-17 06:17 ` Raj <[email protected]>
2026-06-17 11:34 ` Laurenz Albe <[email protected]>
2026-06-17 13:59 ` AW: Size of /pgdata William Sescu (Suva) <[email protected]>
2026-06-17 18:45 ` Raj <[email protected]>
2026-06-18 04:59 ` AW: Size of /pgdata William Sescu (Suva) <[email protected]>
2026-06-18 20:33 ` Re: AW: Size of /pgdata Aleksey M Boltenkov <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox