Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add file_extend_method=posix_fallocate,write_zeros.

  2. Add smgrzeroextend(), FileZero(), FileFallocate()

  1. [PING] fallocate() causes btrfs to never compress postgresql files

    Dimitrios Apostolou <jimis@gmx.net> — 2025-05-28T14:22:51Z

    Hello, sorry for mass sending this, but I didn't get any response to my 
first email [1] so I'm now CC'ing the commit's 4d330a6 [2] author and the 
reviewers. I think it's an important issue, because I need to 
custom-compile postgresql to have what I had before: a transparently 
compressed database.

[1] https://www.postgresql.org/message-id/d0f4fc11-969d-7b3a-aacf-00f86450e738@gmx.net
[2] https://github.com/postgres/postgres/commit/4d330a61bb1969df31f2cebfe1ba9d1d004346d8

My previous message follows:

Hi,

this is just a heads-up about files being generated by PostgreSQL 17 not
being compressed by Btrfs, even when mounted with the force-compress mount
option. I have this occuring aggressively when restoring a database via
pg_restore. I think this is caused mdzeroextend() calling FileFallocate(),
which in turn invokes posix_fallocate().

I also verified that turning off the use of fallocate causes the database
to write compressed files again, like it did in older versions.
Unfortunately the only way I found was to configure with a "hack" so that
autoconf thinks the feature is not available:

    ./configure ac_cv_func_posix_fallocate=no

There have been discussions on the btrfs mailing list about why it does
that, the summary is that it is very difficult to guarantee that
compressed writes will not fail with ENOSPACE on a CoW filesystem, thus
files with fallocate()d ranges are treated as being marked NOCOW,
effectively disabling compression.

Should PostgreSQL provide a setting to avoid the use of fallocate()? Or is
it the filesystem at fault for not returning EOPNOTSUPP, in which case
postgres would use its fallback code?

BTW even in the last case, PostgreSQL would not notice the lack of
fallocate() support as glibc implements a userspace fallback in
posix_fallocate(). That fallback has its own issues that hopefully will
not affect postgres (see CAVEATS in man 3 posix_fallocate).

Regards,
Dimitris

  2. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Tomas Vondra <tomas@vondra.me> — 2025-05-28T15:27:10Z

    On 5/28/25 16:22, Dimitrios Apostolou wrote:
> Hello, sorry for mass sending this, but I didn't get any response to my
> first email [1] so I'm now CC'ing the commit's 4d330a6 [2] author and
> the reviewers. I think it's an important issue, because I need to
> custom-compile postgresql to have what I had before: a transparently
> compressed database.
> 

That message arrived a couple days before the feature freeze, so
everyone was busy with getting PG18 patches over the line. I assume
that's why no one responded to a message about an issue that already
affects PG17. We're in the quieter part of the dev cycle, people are
recovering etc. Hence the delay.

> [1] https://www.postgresql.org/message-id/d0f4fc11-969d-7b3a-
> aacf-00f86450e738@gmx.net
> [2] https://github.com/postgres/postgres/
> commit/4d330a61bb1969df31f2cebfe1ba9d1d004346d8
> 
> My previous message follows:
> 
> Hi,
> 
> this is just a heads-up about files being generated by PostgreSQL 17 not
> being compressed by Btrfs, even when mounted with the force-compress mount
> option. I have this occuring aggressively when restoring a database via
> pg_restore. I think this is caused mdzeroextend() calling FileFallocate(),
> which in turn invokes posix_fallocate().
> 

Right, I don't think we're really using posix_fallocate() in other
places, or at least not in places that would matter. And this code comes
from commit 4d330a61bb in PG17:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4d330a61bb1969df31f2cebfe1ba9d1d004346d8

The commit message explains why we do that - it has advantages when
allocating large number of blocks. FWIW it's a general code, when we
need to add space to a relation, not just for pg_restore.


> I also verified that turning off the use of fallocate causes the database
> to write compressed files again, like it did in older versions.
> Unfortunately the only way I found was to configure with a "hack" so that
> autoconf thinks the feature is not available:
> 
>    ./configure ac_cv_func_posix_fallocate=no
> 

Unfortunately, that seems pretty heavy handed, because it will affect
the whole build, no matter which filesystem it gets used with. And I
guess we don't want to disable posix_fallocate() just because one
filesystem does something ... strange.

> There have been discussions on the btrfs mailing list about why it does
> that, the summary is that it is very difficult to guarantee that
> compressed writes will not fail with ENOSPACE on a CoW filesystem, thus
> files with fallocate()d ranges are treated as being marked NOCOW,
> effectively disabling compression.
> 

Isn't guaranteeing success of a write a general issue with compressed
filesystem? Why is posix_fallocate() any special in this regard?
Shouldn't the filesystem be defensive and assume the data is not
compressible? Or maybe just return EOPNOTSUPP when in doubt.

> Should PostgreSQL provide a setting to avoid the use of fallocate()? Or is
> it the filesystem at fault for not returning EOPNOTSUPP, in which case
> postgres would use its fallback code?
> 

I don't have a clear opinion on whether it's a filesystem issue. Maybe
we should be handling this differently, not sure.

> BTW even in the last case, PostgreSQL would not notice the lack of
> fallocate() support as glibc implements a userspace fallback in
> posix_fallocate(). That fallback has its own issues that hopefully will
> not affect postgres (see CAVEATS in man 3 posix_fallocate).
> 

Well, if btrfs starts returning EOPNOTSUPP, and glibc switches to the
userspace fallback, we wouldn't notice. But that's up to the btrfs to
decide if they want to support fallocate. We still need our fallback
anyway, because of other OSes.


regards

-- 
Tomas Vondra

  3. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Dimitrios Apostolou <jimis@gmx.net> — 2025-05-29T15:57:40Z

    On Wed, 28 May 2025, Tomas Vondra wrote:
>
> Isn't guaranteeing success of a write a general issue with compressed
> filesystem? Why is posix_fallocate() any special in this regard?
> Shouldn't the filesystem be defensive and assume the data is not
> compressible? Or maybe just return EOPNOTSUPP when in doubt.

It's not simple for CoW filesystems, including Btrfs and ZFS. What I know 
is that the current design is a compromise, it's not that the developers 
are happy with it. I can point you to some discussion, with pointers to 
further discussions if you are interested:

https://marc.info/?l=linux-btrfs&m=174310663519516&w=2

>> BTW even in the last case, PostgreSQL would not notice the lack of
>> fallocate() support as glibc implements a userspace fallback in
>> posix_fallocate(). That fallback has its own issues that hopefully will
>> not affect postgres (see CAVEATS in man 3 posix_fallocate).
>>
>
> Well, if btrfs starts returning EOPNOTSUPP, and glibc switches to the
> userspace fallback, we wouldn't notice. But that's up to the btrfs to
> decide if they want to support fallocate. We still need our fallback
> anyway, because of other OSes.

Btrfs has decided a few years back: they will "support" fallocate, but 
because real support is very difficult, they disable compression (among 
others) for files with fallocate'd ranges. They can't change that and 
return EOPNOTSUPP out of the blue now, but they are open to adding a mount 
option to optionally do that:

https://marc.info/?l=linux-btrfs&m=174310663519516&w=2


>> Should PostgreSQL provide a setting to avoid the use of fallocate()? Or is
>> it the filesystem at fault for not returning EOPNOTSUPP, in which case
>> postgres would use its fallback code?
>>
>
> I don't have a clear opinion on whether it's a filesystem issue. Maybe
> we should be handling this differently, not sure.

All I'm saying is that this is a regression for PostgreSQL users that keep 
tablespaces on compressed Btrfs. What could be done from postgres, is to 
provide a runtime setting for avoiding fallocate(), going instead through 
the old code path. Idelly this would be an option per tablespace, but even 
a global one is better than nothing.




Thanks,
Dimitris

  4. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Thomas Munro <thomas.munro@gmail.com> — 2025-05-31T14:00:17Z

    On Fri, May 30, 2025 at 3:58 AM Dimitrios Apostolou <jimis@gmx.net> wrote:
> All I'm saying is that this is a regression for PostgreSQL users that keep
> tablespaces on compressed Btrfs. What could be done from postgres, is to
> provide a runtime setting for avoiding fallocate(), going instead through
> the old code path. Idelly this would be an option per tablespace, but even
> a global one is better than nothing.

Here's an initial sketch of such a setting.  Better name, design,
words welcome.  Would need a bit more work to cover temp tables too.
It's slightly tricky to get smgr to behave differently because of the
contents of a system catalogue!  I couldn't think of a better way than
exposing it as a flag that the buffer manager layer has to know about
and compute earlier, but that also seems a bit strange, as fallocate
is a highly md.c specific concern.  Hmm.

I suppose something like the 0001 part could be back-patched if this
is considered a serious enough problem without other workarounds, so I
did this in two steps.  I wonder if there are good reasons to want to
change the number on other file systems.  I suppose it at least allows
experimentation.

  5. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Thomas Munro <thomas.munro@gmail.com> — 2025-05-31T14:32:45Z

    Or for a completely different approach: I wonder if ftruncate() would
be more efficient on COW systems anyway.  The minimum thing we need is
for the file system to remember the new size, 'cause, erm, we don't.
All the rest is probably a waste of cycles, since they reserve real
space (or fail to) later in the checkpointer or whatever process
eventually writes the data out.

  6. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Tomas Vondra <tomas@vondra.me> — 2025-05-31T14:33:27Z

    On 5/31/25 16:00, Thomas Munro wrote:
> On Fri, May 30, 2025 at 3:58 AM Dimitrios Apostolou <jimis@gmx.net> wrote:
>> All I'm saying is that this is a regression for PostgreSQL users that keep
>> tablespaces on compressed Btrfs. What could be done from postgres, is to
>> provide a runtime setting for avoiding fallocate(), going instead through
>> the old code path. Idelly this would be an option per tablespace, but even
>> a global one is better than nothing.
> 
> Here's an initial sketch of such a setting.  Better name, design,
> words welcome.  Would need a bit more work to cover temp tables too.
> It's slightly tricky to get smgr to behave differently because of the
> contents of a system catalogue!  I couldn't think of a better way than
> exposing it as a flag that the buffer manager layer has to know about
> and compute earlier, but that also seems a bit strange, as fallocate
> is a highly md.c specific concern.  Hmm.
> 

I find the definition of io_min_fallocate confusing, or rather that 0
means "never" instead of "always". It's described as a "threshold at
which to start using fallocate", so I'd expect 0 to mean "always"
because (len >= 0).

I suggest to use "-1" to mean never and "0" always, as for other similar
settings (e.g. log_min_duration_statement or log_lock_waits).

> I suppose something like the 0001 part could be back-patched if this
> is considered a serious enough problem without other workarounds, so I
> did this in two steps.  I wonder if there are good reasons to want to
> change the number on other file systems.  I suppose it at least allows
> experimentation.

Maybe. It'd need to get some of the 0002 bits too, ofc.

I'm not sure we really want all these special GUC tailored for different
filesystems. We already have a few such GUCs, it's getting tricky to
know which ones to set / not set, and it also changes with the
filesystem version ... I personally don't know which ones to set, a lot
of the knowledge is somewhat outdated I think.

Wouldn't it be better for btrfs to just start returning EOPNOTSUPP
(maybe with a mount option), in which case we already do the right thing
automatically already? Sure, it means the admin needs to be aware of
this in both cases.


regards

-- 
Tomas Vondra

  7. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-05-31T15:42:01Z

    Thomas Munro <thomas.munro@gmail.com> writes:
> It's slightly tricky to get smgr to behave differently because of the
> contents of a system catalogue!

The mere thought makes me blanch.  I'm okay with the GUC part,
but I do not think we should put in 0002 --- the odds of
causing serious problems greatly outweigh the value, IMO.
Fundamental layering violations tend to bite you on tender
parts of your anatomy.

			regards, tom lane

  8. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Jakub Wartak <jakub.wartak@enterprisedb.com> — 2025-06-02T07:49:26Z

    On Sat, May 31, 2025 at 4:33 PM Tomas Vondra <tomas@vondra.me> wrote:
>
> On 5/31/25 16:00, Thomas Munro wrote:
> > On Fri, May 30, 2025 at 3:58 AM Dimitrios Apostolou <jimis@gmx.net> wrote:
> >> All I'm saying is that this is a regression for PostgreSQL users that keep
> >> tablespaces on compressed Btrfs. What could be done from postgres, is to
> >> provide a runtime setting for avoiding fallocate(), going instead through
> >> the old code path. Idelly this would be an option per tablespace, but even
> >> a global one is better than nothing.
> >
> > Here's an initial sketch of such a setting.  Better name, design,
> > words welcome.  Would need a bit more work to cover temp tables too.
> > It's slightly tricky to get smgr to behave differently because of the
> > contents of a system catalogue!  I couldn't think of a better way than
> > exposing it as a flag that the buffer manager layer has to know about
> > and compute earlier, but that also seems a bit strange, as fallocate
> > is a highly md.c specific concern.  Hmm.
> >
>
> I find the definition of io_min_fallocate confusing, [..]

Thanks to Thomas for providing the patch, but - same here - but my
take is that making it a GUC that takes a number for this instead of
simply making it on/off switches makes it less more understandable. I
think io_fallocate=on/off would be easier for the users.

> > I suppose something like the 0001 part could be back-patched if this
> > is considered a serious enough problem without other workarounds, so I
> > did this in two steps.  I wonder if there are good reasons to want to
> > change the number on other file systems.  I suppose it at least allows
> > experimentation.
>
> Maybe. It'd need to get some of the 0002 bits too, ofc.
>
> I'm not sure we really want all these special GUC tailored for different
> filesystems. We already have a few such GUCs, it's getting tricky to
> know which ones to set / not set, and it also changes with the
> filesystem version ... I personally don't know which ones to set, a lot
> of the knowledge is somewhat outdated I think.

Well, XFS also got quite several reports of regressions due to
fallocate() being used [1], but there you could at least try to
mitigate it. I don't think we'll be able to get away without it and
the ginnie is already out of the bottle as the kernels are already
widely used (well, in theory we could add capability that would help
set some of those internal switches based on statfs(/path).fs_type,
but realistically we would still need to have the ability to override
anyway).

-J.

[1] - https://www.postgresql.org/message-id/CADofcAV8xu3hCNHq7-7x56KrP9rD6%3DA04%3DqjTr3nETh-gptF8w%40mail.gmail.com

  9. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Dimitrios Apostolou <jimis@gmx.net> — 2025-06-02T10:14:01Z

    On Sun, 1 Jun 2025, Thomas Munro wrote:

> Or for a completely different approach: I wonder if ftruncate() would
> be more efficient on COW systems anyway.  The minimum thing we need is
> for the file system to remember the new size, 'cause, erm, we don't.
> All the rest is probably a waste of cycles, since they reserve real
> space (or fail to) later in the checkpointer or whatever process
> eventually writes the data out.

FWIW I asked the btrfs devs. From 
https://github.com/kdave/btrfs-progs/pull/976
I quote Qu Wenruo:

> Only for falloc(), not ftruncate().
> 
> The PREALLOC inode flag is added for any preallocated file extent, 
> meanwhile truncate only creates holes.
>
> truncate is fast but it's really different from fallocate by there is 
> nothing really allocated.
> 
> This means the later writes will need to allocate their own data 
> extents. This is fine and even preferred for btrfs, but may lead to 
> performance drop for more traditional fses.
> 
> We're in an era that fs features are not longer that generic, fallocate 
> is just one example, in fact fallocate will cause more problems more 
> than no compression.
>
> It's really a deep rabbit hole, and is not something simple true or 
> false questions.


In other words, btrfs will not try to allocate anything with ftruncate(), 
it will just mark the new space as a "hole". As such, the file is not 
marked as "PREALLOC" which is what disables compression. Of course there 
is no guarantee that further writes will succeed, and as quoted above, 
other (non-COW) filesystems might be slower writing the 
ftruncate()-allocated space.


Regards,
Dimitris

  10. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Thomas Munro <thomas.munro@gmail.com> — 2025-06-02T12:35:42Z

    On Mon, Jun 2, 2025 at 10:14 PM Dimitrios Apostolou <jimis@gmx.net> wrote:
> On Sun, 1 Jun 2025, Thomas Munro wrote:
> > Or for a completely different approach: I wonder if ftruncate() would
> > be more efficient on COW systems anyway.  The minimum thing we need is
> > for the file system to remember the new size, 'cause, erm, we don't.
> > All the rest is probably a waste of cycles, since they reserve real
> > space (or fail to) later in the checkpointer or whatever process
> > eventually writes the data out.
>
> FWIW I asked the btrfs devs. From
> https://github.com/kdave/btrfs-progs/pull/976
> I quote Qu Wenruo:
>
> > Only for falloc(), not ftruncate().
> >
> > The PREALLOC inode flag is added for any preallocated file extent,
> > meanwhile truncate only creates holes.
> >
> > truncate is fast but it's really different from fallocate by there is
> > nothing really allocated.
> >
> > This means the later writes will need to allocate their own data
> > extents. This is fine and even preferred for btrfs, but may lead to
> > performance drop for more traditional fses.
> >
> > We're in an era that fs features are not longer that generic, fallocate
> > is just one example, in fact fallocate will cause more problems more
> > than no compression.
> >
> > It's really a deep rabbit hole, and is not something simple true or
> > false questions.
>
>
> In other words, btrfs will not try to allocate anything with ftruncate(),
> it will just mark the new space as a "hole". As such, the file is not
> marked as "PREALLOC" which is what disables compression. Of course there
> is no guarantee that further writes will succeed, and as quoted above,
> other (non-COW) filesystems might be slower writing the
> ftruncate()-allocated space.

Yeah, right, I know.  But PostgreSQL has at least two different goals
when extending a relation:

1.  Remember the new size of the relation somewhere*.
2.  Reserve space now, so that we can report ENOSPC and roll back the
transaction that wants to extend the relation when the disk is full,
instead of causing a checkpoint or buffer eviction to fail later (see
https://wiki.postgresql.org/wiki/ENOSPC for longer version).

But the second thing just can't work on a COW system by definition, so
the whole notion is bogus, which is why I wondered if fruncate() is
actually a reasonable option to have, even though it just creates
holes (on Unixen).  I also know of another completely different reason
to want to use ftruncate(): NTFS, which *doesn't* create holes (NTFS
supports holes via other syscalls, but ftruncate() or rather
_chsize_s() as they spell it doesn't make them), making it more like
posix_fallocate() in this usage.  So I was beginning to wonder if we
might want to experiment with a patch that adds
file_extend_method=fallocate,ftruncate,write.  Perhaps accompanied by
a threshold setting below which it always writes.  Then we could
experiment with various COW file systems (zfs, btrfs, apfs, refs, ???)
and NTFS to see how that speculation works out in reality.

Wild speculation: To actually achieve the second thing on a COW file
system, you'd probably need some totally new kind of interface,
because that POSIX interface has the wrong shape.  I have wondered
about a new fcntl() or whatever that would let you reserve the right
to write N blocks (ie just once!) without ENOSPC on a given
descriptor, that a database could conceptually acquire when dirtying
buffers, since that's the point at which we know that a write must
eventually happen (then probably amortise that accounting a lot),
including but not limited to this relation-extension case, and that
way you could achieve goal #2, ie transferring ENOSPC errors to
transaction time.  But that's just a daydream about vapourware.  One
problem is that PostgreSQL has many processes with separate file
descriptors, so that'd make the bookkeeping trickier but not
impossible.

(*That has a few known issues...)

  11. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Bruce Momjian <bruce@momjian.us> — 2025-06-02T13:14:01Z

    On Sun, Jun  1, 2025 at 02:00:17AM +1200, Thomas Munro wrote:
> I suppose something like the 0001 part could be back-patched if this
> is considered a serious enough problem without other workarounds, so I
> did this in two steps.  I wonder if there are good reasons to want to
> change the number on other file systems.  I suppose it at least allows
> experimentation.

Consider that postgresql.conf is installed by initdb, so backpatching
this is not going to add the setting to postgresql.conf unless we do
some magic.  That will be confusing to users.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.

  12. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Dimitrios Apostolou <jimis@gmx.net> — 2025-06-02T13:57:59Z

    On Tue, 3 Jun 2025, Thomas Munro wrote:

> On Mon, Jun 2, 2025 at 10:14 PM Dimitrios Apostolou <jimis@gmx.net> wrote:
>> On Sun, 1 Jun 2025, Thomas Munro wrote:
>>> Or for a completely different approach: I wonder if ftruncate() would
>>> be more efficient on COW systems anyway.  The minimum thing we need is
>>> for the file system to remember the new size, 'cause, erm, we don't.
>>> All the rest is probably a waste of cycles, since they reserve real
>>> space (or fail to) later in the checkpointer or whatever process
>>> eventually writes the data out.
>>
>> FWIW I asked the btrfs devs. From
>> https://github.com/kdave/btrfs-progs/pull/976
>> I quote Qu Wenruo:
>>
>>> Only for falloc(), not ftruncate().
>>>
>>> The PREALLOC inode flag is added for any preallocated file extent,
>>> meanwhile truncate only creates holes.
>>>
>>> truncate is fast but it's really different from fallocate by there is
>>> nothing really allocated.
>>>
>>> This means the later writes will need to allocate their own data
>>> extents. This is fine and even preferred for btrfs, but may lead to
>>> performance drop for more traditional fses.
>>>
>>> We're in an era that fs features are not longer that generic, fallocate
>>> is just one example, in fact fallocate will cause more problems more
>>> than no compression.
>>>
>>> It's really a deep rabbit hole, and is not something simple true or
>>> false questions.
>>
>>
>> In other words, btrfs will not try to allocate anything with ftruncate(),
>> it will just mark the new space as a "hole". As such, the file is not
>> marked as "PREALLOC" which is what disables compression. Of course there
>> is no guarantee that further writes will succeed, and as quoted above,
>> other (non-COW) filesystems might be slower writing the
>> ftruncate()-allocated space.
>
> Yeah, right, I know.  But PostgreSQL has at least two different goals
> when extending a relation:
>
> 1.  Remember the new size of the relation somewhere*.
> 2.  Reserve space now, so that we can report ENOSPC and roll back the
> transaction that wants to extend the relation when the disk is full,
> instead of causing a checkpoint or buffer eviction to fail later (see
> https://wiki.postgresql.org/wiki/ENOSPC for longer version).
>
> But the second thing just can't work on a COW system by definition, so
> the whole notion is bogus, which is why I wondered if fruncate() is
> actually a reasonable option to have, even though it just creates
> holes (on Unixen).  I also know of another completely different reason
> to want to use ftruncate(): NTFS, which *doesn't* create holes (NTFS
> supports holes via other syscalls, but ftruncate() or rather
> _chsize_s() as they spell it doesn't make them), making it more like
> posix_fallocate() in this usage.  So I was beginning to wonder if we
> might want to experiment with a patch that adds
> file_extend_method=fallocate,ftruncate,write.  Perhaps accompanied by
> a threshold setting below which it always writes.

This sounds like the best solution IMO. People can then experiment with 
different settings and filesystems, and that way we also learn in the 
process. Thank you for the effort and patches so far.

Dimitris

  13. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Thomas Munro <thomas.munro@gmail.com> — 2025-06-09T08:33:25Z

    On Tue, Jun 3, 2025 at 1:58 AM Dimitrios Apostolou <jimis@gmx.net> wrote:
> This sounds like the best solution IMO. People can then experiment with
> different settings and filesystems, and that way we also learn in the
> process. Thank you for the effort and patches so far.

OK, here's a basic patch to experiment with.  You can set:

file_extend_method = fallocate,ftruncate,write
file_extend_method_threshold = 8 # (below 8 always write, 0 means never write)

To really make COPY fly we also need to get write combining and AIO
going (we've had this working with various prototypes, but it all
missed the boat for v18 which can only do that stuff for reads).  Then
you'll have concurrent 128kB or up to 1MB writes trundling along in
the background which I guess should work pretty nicely for stuff like
BTRFS/ZFS and compression and all that jazz.

  14. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Dimitrios Apostolou <jimis@gmx.net> — 2025-06-12T12:41:18Z

    On Mon, 9 Jun 2025, Thomas Munro wrote:

> On Tue, Jun 3, 2025 at 1:58 AM Dimitrios Apostolou <jimis@gmx.net> wrote:
>> This sounds like the best solution IMO. People can then experiment with
>> different settings and filesystems, and that way we also learn in the
>> process. Thank you for the effort and patches so far.
>
> OK, here's a basic patch to experiment with.  You can set:
>
> file_extend_method = fallocate,ftruncate,write
> file_extend_method_threshold = 8 # (below 8 always write, 0 means never write)
>

I applied the patch on PostgreSQL v17 and am testing it now. I chose 
ftruncate method and I see ftruncate in action using strace while doing 
pg_restore of a big database. Nothing unexpected has happened so far. I 
also verified that files are being compressed, obeying Btrfs's mount 
option compress=zstd.

Thanks for the patch! What are the odds of commiting it to v17?

Dimitris

  15. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Dimitrios Apostolou <jimis@gmx.net> — 2025-07-10T17:39:10Z

    On Thu, 12 Jun 2025, Dimitrios Apostolou wrote:

> On Mon, 9 Jun 2025, Thomas Munro wrote:
>
>>  On Tue, Jun 3, 2025 at 1:58 AM Dimitrios Apostolou <jimis@gmx.net> wrote:
>>>  This sounds like the best solution IMO. People can then experiment with
>>>  different settings and filesystems, and that way we also learn in the
>>>  process. Thank you for the effort and patches so far.
>>
>>  OK, here's a basic patch to experiment with.  You can set:
>>
>>  file_extend_method = fallocate,ftruncate,write
>>  file_extend_method_threshold = 8 # (below 8 always write, 0 means never
>>  write)
>> 
>
> I applied the patch on PostgreSQL v17 and am testing it now. I chose 
> ftruncate method and I see ftruncate in action using strace while doing 
> pg_restore of a big database. Nothing unexpected has happened so far. I also 
> verified that files are being compressed, obeying Btrfs's mount option 
> compress=zstd.
>
> Thanks for the patch! What are the odds of commiting it to v17?

Ping. :-)
Patch behaves good for me. Any chance of applying it and backporting it?

>
> Dimitris
>
>

  16. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Thomas Munro <thomas.munro@gmail.com> — 2025-07-10T22:45:04Z

    On Fri, Jul 11, 2025 at 5:39 AM Dimitrios Apostolou <jimis@gmx.net> wrote:
> > I applied the patch on PostgreSQL v17 and am testing it now. I chose
> > ftruncate method and I see ftruncate in action using strace while doing
> > pg_restore of a big database. Nothing unexpected has happened so far. I also
> > verified that files are being compressed, obeying Btrfs's mount option
> > compress=zstd.
> >
> > Thanks for the patch! What are the odds of commiting it to v17?
>
> Ping. :-)
> Patch behaves good for me. Any chance of applying it and backporting it?

Yeah, this seems to make sense, as it is a pretty bad regression for
people who are counting on BTRFS compression for their large database.
Not so sure about the threshold bit -- I'd probably leave that out of
the backport in the interest of stable branch-minimalism.  Anyone have
any better ideas, better naming, or objections?

  17. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Dimitrios Apostolou <jimis@gmx.net> — 2025-07-28T20:41:54Z

    On Friday 2025-07-11 00:45, Thomas Munro wrote:

> On Fri, Jul 11, 2025 at 5:39 AM Dimitrios Apostolou <jimis@gmx.net> wrote:
>>> I applied the patch on PostgreSQL v17 and am testing it now. I chose
>>> ftruncate method and I see ftruncate in action using strace while doing
>>> pg_restore of a big database. Nothing unexpected has happened so far. I also
>>> verified that files are being compressed, obeying Btrfs's mount option
>>> compress=zstd.
>>>
>>> Thanks for the patch! What are the odds of commiting it to v17?
>>
>> Ping. :-)
>> Patch behaves good for me. Any chance of applying it and backporting it?
>
> Yeah, this seems to make sense, as it is a pretty bad regression for
> people who are counting on BTRFS compression for their large database.
> Not so sure about the threshold bit -- I'd probably leave that out of
> the backport in the interest of stable branch-minimalism.  Anyone have
> any better ideas, better naming, or objections?

What is the right process to not lose track of this? Should I create a 
commitfest entry? Should I keep pinging every couple of weeks? Or is the 
patch queued somewhere and I have to wait patiently? If July commitfest 
passes, could it miss the next release?

Please forgive my ignorance, but I'm lost with respect to the postgresql 
development process. I also have some patches or suggestions of my own 
that struggle to get feedback, so I'd appreciate any tips regarding the 
development process.


Thank you,
Dimitris

  18. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Magnus Hagander <magnus@hagander.net> — 2025-07-29T06:52:34Z

    On Fri, Jul 11, 2025 at 12:45 AM Thomas Munro <thomas.munro@gmail.com>
wrote:

> On Fri, Jul 11, 2025 at 5:39 AM Dimitrios Apostolou <jimis@gmx.net> wrote:
> > > I applied the patch on PostgreSQL v17 and am testing it now. I chose
> > > ftruncate method and I see ftruncate in action using strace while doing
> > > pg_restore of a big database. Nothing unexpected has happened so far.
> I also
> > > verified that files are being compressed, obeying Btrfs's mount option
> > > compress=zstd.
> > >
> > > Thanks for the patch! What are the odds of commiting it to v17?
> >
> > Ping. :-)
> > Patch behaves good for me. Any chance of applying it and backporting it?
>
> Yeah, this seems to make sense, as it is a pretty bad regression for
> people who are counting on BTRFS compression for their large database.
> Not so sure about the threshold bit -- I'd probably leave that out of
> the backport in the interest of stable branch-minimalism.  Anyone have
> any better ideas, better naming, or objections?
>

Not just to throw a wrench in there, but... Should this perhaps be a
tablespace option? ISTM having different filesystems for them is a good
reason to use tablespaces in the first place, and then being able to pick
different options...

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

  19. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Thomas Munro <thomas.munro@gmail.com> — 2025-08-05T13:07:57Z

    On Tue, Jul 29, 2025 at 6:52 PM Magnus Hagander <magnus@hagander.net> wrote:
> Not just to throw a wrench in there, but... Should this perhaps be a tablespace option? ISTM having different filesystems for them is a good reason to use tablespaces in the first place, and then being able to pick different options...

We discussed that a bit earlier in the thread.  Some problems about
layering violations and general weirdness, I recall trying it even.
On the flip side, is it right to declare very local
filesystem-specific choices in a system catalogue that is replicated
and affects replicas?
What about a fancier GUC that can reference tablespaces?

  20. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Magnus Hagander <magnus@hagander.net> — 2025-08-07T13:38:10Z

    On Tue, Aug 5, 2025 at 3:08 PM Thomas Munro <thomas.munro@gmail.com> wrote:

> On Tue, Jul 29, 2025 at 6:52 PM Magnus Hagander <magnus@hagander.net>
> wrote:
> > Not just to throw a wrench in there, but... Should this perhaps be a
> tablespace option? ISTM having different filesystems for them is a good
> reason to use tablespaces in the first place, and then being able to pick
> different options...
>
> We discussed that a bit earlier in the thread.  Some problems about
> layering violations and general weirdness, I recall trying it even.
> On the flip side, is it right to declare very local
> filesystem-specific choices in a system catalogue that is replicated
> and affects replicas?
> What about a fancier GUC that can reference tablespaces?
>

Wouldn't that be something that applies to *all* the tablespace configs
then, taht is a proper movement of the goalposts? :) Such as being able to
set random_page_cost per tablespace to different values on different
machines. I agree that it would be useful though.  But it seems like a
different patch, if useful, and one that should be generic?

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

  21. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Thomas Munro <thomas.munro@gmail.com> — 2025-08-17T23:23:04Z

    On Fri, Aug 8, 2025 at 1:38 AM Magnus Hagander <magnus@hagander.net> wrote:
> On Tue, Aug 5, 2025 at 3:08 PM Thomas Munro <thomas.munro@gmail.com> wrote:
>> We discussed that a bit earlier in the thread.  Some problems about
>> layering violations and general weirdness, I recall trying it even.
>> On the flip side, is it right to declare very local
>> filesystem-specific choices in a system catalogue that is replicated
>> and affects replicas?
>> What about a fancier GUC that can reference tablespaces?
>
>
> Wouldn't that be something that applies to *all* the tablespace configs then, taht is a proper movement of the goalposts? :) Such as being able to set random_page_cost per tablespace to different values on different machines. I agree that it would be useful though.  But it seems like a different patch, if useful, and one that should be generic?

Yeah.  And while we're talking pie-in-the-sky future features,
full_page_writes is also describing a property of a particular
server's file system and/or hardware for a given tablespace.  Can't do
much about that today, as it can only be decided by the primary node
that must log full pages or not, but its potential replacement
"atomic_double_write" (as I call it) *can* be chosen on a per-server
basis in a replication chain.  We could probably have done that
independently, but it gets easier with new infrastructure for
streaming large asynchronous combined writes...

To solve Dimitrios's real production issue, I am planning to proceed
with the simple whole-system GUC(s) already posted, after I've done
some light testing on ZFS (which has similar design constraints though
makes different choices) and thought a bit harder about the
Windows/NTFS situation.  I'll post a new version before pushing
anything.  My plan is to have this in the next minor release, unless
the upcoming 18 release forces me to delay it until the one after.

Another thing I noticed is that macOS has its own funky way[1] of
preallocating disk space that looks plausibly relevant.  Not
investigated and not planning to work on that myself necessarily but
it might be worth thinking for a moment about the GUC future-proofing
implications.

[1] https://github.com/libgit2/libgit2/commit/bd132046b04875f928e52d16363fb73f8e85dded

  22. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Dimitrios Apostolou <jimis@gmx.net> — 2025-10-28T15:31:16Z

    Sorry to ping again, but was there a conclusion reached regarding adding the new file_extend_method setting?

  23. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Thomas Munro <thomas.munro@gmail.com> — 2025-10-30T03:55:29Z

    On Wed, Oct 29, 2025 at 4:31 AM Dimitrios Apostolou <jimis@gmx.net> wrote:
> Sorry to ping again, but was there a conclusion reached regarding adding the new file_extend_method setting?

No objections appeared, so the conclusion I am drawing is that we
should do this, and back-patch it into 17 for the upcoming release.
It is working as expected on my ZFS system in light testing.  Rebasing
and figuring out where to add the missing documentation for last
chance review...

  24. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Jakub Wartak <jakub.wartak@enterprisedb.com> — 2025-10-30T10:14:07Z

    On Thu, Oct 30, 2025 at 4:56 AM Thomas Munro <thomas.munro@gmail.com> wrote:
>
> On Wed, Oct 29, 2025 at 4:31 AM Dimitrios Apostolou <jimis@gmx.net> wrote:
> > Sorry to ping again, but was there a conclusion reached regarding adding the new file_extend_method setting?
>
> No objections appeared, so the conclusion I am drawing is that we
> should do this,

Hi Thomas,

+1 to this GUCs as this would also help the nearby thread with XFS
mysteries which are not fully solved [1]. Since the latest message in
that discussion, I'm aware of at least one additional report of XFS
failing at fallocate() with free space too, but without any details
from the OS support vendor why that happened, so this $patch could be
also used to workaround that problem too.

Just nitpicking:

> and back-patch it into 17 for the upcoming release.
> It is working as expected on my ZFS system in light testing.  Rebasing
> and figuring out where to add the missing documentation for last
> chance review...

Why just 17? (wasn't fallocate() introduced in 16? 4d330a61bb19 and
31966b151e6ab are from Apr 2023, while 16 was released on Sep 2023)

From other things, I was wondering about this:

> PGC_USERSET

QQ: Do we really want to have those two GUCs to be alterable like that
by anyone? The alternative would be like let's say PGC_SIGHUP? (on one
end it's flexible, but are there any downsides to this as it stands
out in 0001?). I've checked others and io_workers is PGC_SIGHUP
(understandable), but we also have io_combine_limit &&
effective_io_concurrency with PGC_USERSET. I'm just wondering if it
would be sane to have one backend doing I/O with fallocate() and other
just writing using pwrite(). One could argue you could be writing to
two different filesystems with two different users...

-J.

[1] - https://www.postgresql.org/message-id/flat/CADofcAV8xu3hCNHq7-7x56KrP9rD6%3DA04%3DqjTr3nETh-gptF8w%40mail.gmail.com

  25. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Bruce Momjian <bruce@momjian.us> — 2025-10-30T16:59:41Z

    On Thu, Oct 30, 2025 at 11:14:07AM +0100, Jakub Wartak wrote:
> On Thu, Oct 30, 2025 at 4:56 AM Thomas Munro <thomas.munro@gmail.com> wrote:
> >
> > On Wed, Oct 29, 2025 at 4:31 AM Dimitrios Apostolou <jimis@gmx.net> wrote:
> > > Sorry to ping again, but was there a conclusion reached regarding adding the new file_extend_method setting?
> >
> > No objections appeared, so the conclusion I am drawing is that we
> > should do this,
> 
> Hi Thomas,
> 
> +1 to this GUCs as this would also help the nearby thread with XFS
> mysteries which are not fully solved [1]. Since the latest message in
> that discussion, I'm aware of at least one additional report of XFS
> failing at fallocate() with free space too, but without any details
> from the OS support vendor why that happened, so this $patch could be
> also used to workaround that problem too.

Uh, the problem with backpatching new GUCs is that the GUC variable will
_not_ appear in any postgresql.conf file until a new initdb is run. 
This can be quite confusing for people.  The minor release notes have to
explain this.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.

  26. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Thomas Munro <thomas.munro@gmail.com> — 2025-12-15T06:00:16Z

    Here's a new version with some cleanup and documentation.  I tried to
pare it down to the minimum change for the back-branches, keeping
unnecessary changes for master.  In the process, I also thought a bit
about how to de-confused matters on Windows, where the function we
call as ftruncate() behaves differently in a crucial respect.  See
attached.

I'm proposing to back-patch 0001.  0002 and 0003 are proposals for master only.

See below for replies to separate messages from Jakub and Bruce.

On Thu, Oct 30, 2025 at 11:14 PM Jakub Wartak
<jakub.wartak@enterprisedb.com> wrote:
> +1 to this GUCs as this would also help the nearby thread with XFS
> mysteries which are not fully solved [1]. Since the latest message in
> that discussion, I'm aware of at least one additional report of XFS
> failing at fallocate() with free space too, but without any details
> from the OS support vendor why that happened, so this $patch could be
> also used to workaround that problem too.

Yeah, that seems quite important, and the new report in psql-bugs
#19348 sounds like another case.

> Just nitpicking:
>
> > and back-patch it into 17 for the upcoming release.
> > It is working as expected on my ZFS system in light testing.  Rebasing
> > and figuring out where to add the missing documentation for last
> > chance review...
>
> Why just 17? (wasn't fallocate() introduced in 16? 4d330a61bb19 and
> 31966b151e6ab are from Apr 2023, while 16 was released on Sep 2023)

Right, fixed.

> From other things, I was wondering about this:
>
> > PGC_USERSET
>
> QQ: Do we really want to have those two GUCs to be alterable like that
> by anyone? The alternative would be like let's say PGC_SIGHUP? (on one
> end it's flexible, but are there any downsides to this as it stands
> out in 0001?). I've checked others and io_workers is PGC_SIGHUP
> (understandable), but we also have io_combine_limit &&
> effective_io_concurrency with PGC_USERSET. I'm just wondering if it
> would be sane to have one backend doing I/O with fallocate() and other
> just writing using pwrite(). One could argue you could be writing to
> two different filesystems with two different users...

Yeah.  Let's go with PGC_SIGHUP.  Let's worry about multiple
filesystems when we've figured out how to do per-tablespace settings.

This is vapourware for later, but I've been wondering if we could
invent a sysctl-style hierarchy as a scoping mechanism, something
like:

tablespace.foo.random_page_cost=1
tablespace.foo.file_extend_method=ftruncate
tablespace.foo.io_combine_limit=1MB

Obviously there are some name resolution problems with that.  I also
thought about allowing a new kind of configuration file inside
tablespace directories, but that doesn't work for PGC_USERSET stuff
like random_page_cost.  If the hierarchy idea goes somewhere, it might
also allow a reorganisation like [tablespace.foo.]io.combine_limit,
with legacy long names like io_combine_limit still supported, but
that's getting quite far off topic...

On Fri, Oct 31, 2025 at 5:59 AM Bruce Momjian <bruce@momjian.us> wrote:
> Uh, the problem with backpatching new GUCs is that the GUC variable will
> _not_ appear in any postgresql.conf file until a new initdb is run.
> This can be quite confusing for people.  The minor release notes have to
> explain this.

Yeah.  Fortunately the vast majority of users won't ever need to know
about this.  Those who run into a problem should hopefully find their
way to the docs, release notes, settings view, these threads, or write
to us?  Any other way of controlling this that we invent to avoid
back-patching a GUC would surely only be harder to find than a new
GUC, I think?  And I don't think we're anywhere near the level of
needing to revert the posix_fallocate() feature: both reported
problems are rare.  (Though there is a lesson here in terms of
off-switch planning.)

Here's my attempt at a release note:

"The new setting file_extend_method can be set to write_zeros to
disable the use of the posix_fallocate() system call when extending
relation files.  This is a workaround for users of BTRFS compression,
reported to be disabled by posix_fallocate(), and some versions of
XFS, reported to fail with spurious ENOSPC errors under some
workloads."

  27. Re: [PING] fallocate() causes btrfs to never compress postgresql files

    Jakub Wartak <jakub.wartak@enterprisedb.com> — 2025-12-15T07:50:00Z

    On Mon, Dec 15, 2025 at 7:00 AM Thomas Munro <thomas.munro@gmail.com> wrote:
>
> Here's a new version with some cleanup and documentation.  I tried to
> pare it down to the minimum change for the back-branches, keeping
> unnecessary changes for master.  In the process, I also thought a bit
> about how to de-confused matters on Windows, where the function we
> call as ftruncate() behaves differently in a crucial respect.  See
> attached.
>
> I'm proposing to back-patch 0001.  0002 and 0003 are proposals for master only.
>

Hi Thomas,

Thanks for working on this. I have reviewed and played a little with
them and they are in very good shape, so +1 from my side. Just couple
of minor things:

1. 0001 I would just add another Discussion there too in commit
message (https://www.postgresql.org/message-id/flat/CADofcAV8xu3hCNHq7-7x56KrP9rD6%3DA04%3DqjTr3nETh-gptF8w%40mail.gmail.com
 - XFS thread)
2. I've tested those lightly and they pass my local/built/test. Just a
non-actionable observation from my side: I'm just not sure how useful
the v2-0002 (the new file_extend_method_threshold) is going to be in
real life, for me it sounds like it could be debug_file_extend*...
however that would break convention of using just file_extend
3. I haven't tested 0003 as it is for Windows, probably we could add
it to cfbot, so that it would tell us something more there.

> See below for replies to separate messages from Jakub and Bruce.
>
> On Thu, Oct 30, 2025 at 11:14 PM Jakub Wartak
> <jakub.wartak@enterprisedb.com> wrote:
> > +1 to this GUCs as this would also help the nearby thread with XFS
> > mysteries which are not fully solved [1]. Since the latest message in
> > that discussion, I'm aware of at least one additional report of XFS
> > failing at fallocate() with free space too, but without any details
> > from the OS support vendor why that happened, so this $patch could be
> > also used to workaround that problem too.
>
> Yeah, that seems quite important, and the new report in psql-bugs
> #19348 sounds like another case.

Right, I think we've got another report internally too since last time
we've talked, but contact went silent after being redirected to the OS
vendor (after some recommended workaround did not work for them , but
those worked for others).

> > Why just 17? (wasn't fallocate() introduced in 16? 4d330a61bb19 and
> > 31966b151e6ab are from Apr 2023, while 16 was released on Sep 2023)
>
> Right, fixed.

Cool, thanks.

> Yeah.  Let's go with PGC_SIGHUP.  Let's worry about multiple
> filesystems when we've figured out how to do per-tablespace settings.

Cool, thanks.

> This is vapourware for later, but I've been wondering if we could
> invent a sysctl-style hierarchy as a scoping mechanism, something
> like:
>
> tablespace.foo.random_page_cost=1
> tablespace.foo.file_extend_method=ftruncate
> tablespace.foo.io_combine_limit=1MB

This looks more like sysfs than sysctl (as foo is tablespace name?)
:^). Anyway I think that 0001 should go in and then new thread could
be started for this if You want (as this would be a little conflicting
to stuff we already have: e.g. alter tablespace pg_default set
(maintenance_io_concurrency=XXX), but it is highly unlikely anybody
uses '\db+' in psql see those options there).

-J.