Subject: Re: Preallocation changes in Postgresql 16

From: Thomas Munro <[email-masked]>
Date: Fri, 26 Apr 2024 09:25:03 +1200

On Fri, Apr 26, 2024 at 4:37 AM Riku Iki <[email-masked]> wrote:
> I am wondering if there were preallocation related changes in PG16, and if it is possible to disable preallocation in PostgreSQL 16?

I have no opinion on the btrfs details, but I was wondering if someone
might show up with a system that doesn't like that change.  Here is a
magic 8, tuned on "some filesystems":

        /*
         * If available and useful, use posix_fallocate() (via
         * FileFallocate()) to extend the relation. That's often more
         * efficient than using write(), as it commonly won't cause the kernel
         * to allocate page cache space for the extended pages.
         *
         * However, we don't use FileFallocate() for small extensions, as it
         * defeats delayed allocation on some filesystems. Not clear where
         * that decision should be made though? For now just use a cutoff of
         * 8, anything between 4 and 8 worked OK in some local testing.
         */
        if (numblocks > 8)

I wonder if it wants to be a GUC.

---

From: Riku Iki <[email-masked]>
Date: Fri, 26 Apr 2024 16:15:35 -0700

Thank you, I have such a system. I think my task would be to compile PG
from sources(need to learn this), and see how it works with and without
that code block.

On Thu, Apr 25, 2024 at 2:25 PM Thomas Munro <[email-masked]> wrote:

> > I am wondering if there were preallocation related changes in PG16, and
> if it is possible to disable preallocation in PostgreSQL 16?
>

---

From: Riku Iki <[email-masked]>
Date: Thu, 2 May 2024 20:11:19 -0700

I did the testing and confirmed that this was the issue.

I run following query:

 create table t as select '1234567890' from generate_series(1, 1000000000);

I commented if (numblocks > 8) codeblock, and see the following results
from "compsize /dbdir/" command.

Before my changes:

Processed 1381 files, 90007 regular extents (90010 refs), 15 inline.
Type       Perc     Disk Usage   Uncompressed Referenced
TOTAL       97%       41G          42G          42G
none       100%       41G          41G          41G
zstd        14%      157M         1.0G         1.0G
prealloc   100%       16M          16M          16M

After the changes:

Processed 1381 files, 347328 regular extents (347331 refs), 15 inline.
Type       Perc     Disk Usage   Uncompressed Referenced
TOTAL        3%      1.4G          42G          42G
none       100%       80K          80K          80K
zstd         3%      1.4G          42G          42G

It is clearly visible that files created with fallocate are not compressed,
and disk usage is much larger.
I am wondering if there is a way to have some feature request to have this
parameter user configurable..

---

From: "Pierre Barre" <[email-masked]>
Date: Fri, 27 Dec 2024 00:16:53 +0100

Hello,

It seems that I am running into this issue as well. 
Is it likely that this would ever be a config option?

Best,
Pierre Barre

On Fri, May 3, 2024, at 05:11, Riku Iki wrote:
> I commented if (numblocks > 8) codeblock, and see the following results from "compsize /dbdir/" command.
> 
> 
> Before my changes:
> 
> It is clearly visible that files created with fallocate are not compressed, and disk usage is much larger.
> I am wondering if there is a way to have some feature request to have this parameter user configurable..  
> 
>> Thank you, I have such a system. I think my task would be to compile PG from sources(need to learn this), and see how it works with and without that code block.
>>