Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tQx6Z-006Gqf-BH for pgsql-general@arkaria.postgresql.org; Thu, 26 Dec 2024 23:17:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tQx6Y-005QuY-IN for pgsql-general@arkaria.postgresql.org; Thu, 26 Dec 2024 23:17:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tQx6X-005QuQ-BW for pgsql-general@lists.postgresql.org; Thu, 26 Dec 2024 23:17:21 +0000 Received: from fout-b6-smtp.messagingengine.com ([202.12.124.149]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tQx6U-000iQX-37 for pgsql-general@lists.postgresql.org; Thu, 26 Dec 2024 23:17:19 +0000 Received: from phl-compute-05.internal (phl-compute-05.phl.internal [10.202.2.45]) by mailfout.stl.internal (Postfix) with ESMTP id 808E811400EC; Thu, 26 Dec 2024 18:17:17 -0500 (EST) Received: from phl-imap-06 ([10.202.2.83]) by phl-compute-05.internal (MEProxy); Thu, 26 Dec 2024 18:17:17 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=barre.sh; h=cc :cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm1; t=1735255037; x=1735341437; bh=KMaysyXbx4 V+gx1ZEQqbxeRXzljMkMJ7QDSqQCHZmfU=; b=TgClUld7hDrEvNtvDPZDhe0DLR ja5GJORBH6DY4nhsomuCY/6c2hD1wW2b/piSNbMnPvPV9vMuwqKTN1IjYQx/od04 CCZk1t80Af97a5skxgZ8e8PVbuZGK4I4gwBM8emh4bwHMgzynGdG1UJ4pr3+1DBN fipXpej/D+gM6CYtanNTt+qg1GuYz6noc/TRyuhZQQouESMH6jpNOPbk5vLI8V1K Dcwnkqwdqhk13ugCUub3eVJY1MCc7vbJ2rpdjrC2pfRJVMrua4RMBS5cLyTDQizG +FL0m7XI6obGhINNnQk5MjG1DzIZOrOVRl/5qNHjhZPa0SSkIRYWMWL6WuOw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t= 1735255037; x=1735341437; bh=KMaysyXbx4V+gx1ZEQqbxeRXzljMkMJ7QDS qQCHZmfU=; b=V7YhH7ggIgRzGvs36+fpQIwnkTG6TLUgG55vsJEjw9IsEV66nwm U9yF5DcaimKCKdmuXMWi2pNneTTwcmFSulUWA2PAUO9/8q31KVR5v5AgdUyWaQhA +k1UHOKsuevvK5QqXihp0aCQQGxR8tExAqg5IoNjmi6GFOzDucvXu0k7zPFj1ODN 7mCgy/nLnkcb/hBlaeA4NuUqx3lOdEs91LDsTF/e1FHrSkXMShUAATUz4oXc0xCg s46+CmjqyXsEMP1UV9yYLy9mvFcFGRfSHmIj5SCvQTqRu4NxfwON6v1ILQ6uR1K3 OdpftDJppSYwxVMkshESw78XBgyfUlN5C5Q== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefuddrudduledgtdekucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdggtfgfnhhsuhgsshgtrhhisggvpdfu rfetoffkrfgpnffqhgenuceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnh htshculddquddttddmnecujfgurhepofggfffhvfevkfgjfhfutgesrgdtreerredtjeen ucfhrhhomhepfdfrihgvrhhrvgcuuegrrhhrvgdfuceophhivghrrhgvsegsrghrrhgvrd hshheqnecuggftrfgrthhtvghrnhepjeetjefffeehkedtfedukeegleehteeijeeftdeh ffejgfeiiefffeeuteehveevnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpe hmrghilhhfrhhomhepphhivghrrhgvsegsrghrrhgvrdhshhdpnhgspghrtghpthhtohep fedpmhhouggvpehsmhhtphhouhhtpdhrtghpthhtoheprhhikhhurdhikhhirdigsehgmh grihhlrdgtohhmpdhrtghpthhtohepthhhohhmrghsrdhmuhhnrhhosehgmhgrihhlrdgt ohhmpdhrtghpthhtohepphhgshhqlhdqghgvnhgvrhgrlheslhhishhtshdrphhoshhtgh hrvghsqhhlrdhorhhg X-ME-Proxy: Feedback-ID: i97614980:Fastmail Received: by mailuser.phl.internal (Postfix, from userid 501) id 0D58B29C006F; Thu, 26 Dec 2024 18:17:17 -0500 (EST) X-Mailer: MessagingEngine.com Webmail Interface MIME-Version: 1.0 Date: Fri, 27 Dec 2024 00:16:53 +0100 From: "Pierre Barre" To: "Riku Iki" , "Thomas Munro" Cc: pgsql-general@lists.postgresql.org Message-Id: <90e2e672-947f-493d-a4b4-fdb0add7e33c@app.fastmail.com> In-Reply-To: References: Subject: Re: Preallocation changes in Postgresql 16 Content-Type: multipart/alternative; boundary=5f3ae241df24428b8bfa3f30b4777af5 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --5f3ae241df24428b8bfa3f30b4777af5 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hello, It seems that I am running into this issue as well.=20 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 did the testing and confirmed that this was the issue. >=20 > I run following query: >=20 > create table t as select '1234567890' from generate_series(1, 1000000= 000); >=20 > I commented if (numblocks > 8) codeblock, and see the following result= s from "compsize /dbdir/" command. >=20 >=20 > Before my changes: >=20 > Processed 1381 files, 90007 regular extents (90010 refs), 15 inline. > Type Perc Disk Usage Uncompressed Referenced =20 > TOTAL 97% 41G 42G 42G =20 > none 100% 41G 41G 41G =20 > zstd 14% 157M 1.0G 1.0G =20 > prealloc 100% 16M 16M 16M >=20 >=20 >=20 > After the changes: >=20 > Processed 1381 files, 347328 regular extents (347331 refs), 15 inline. > Type Perc Disk Usage Uncompressed Referenced =20 > TOTAL 3% 1.4G 42G 42G =20 > none 100% 80K 80K 80K =20 > zstd 3% 1.4G 42G 42G >=20 > It is clearly visible that files created with fallocate are not compre= ssed, 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.. =20 >=20 > On Fri, Apr 26, 2024 at 4:15=E2=80=AFPM Riku Iki wrote: >> 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 witho= ut that code block. >>=20 >> On Thu, Apr 25, 2024 at 2:25=E2=80=AFPM Thomas Munro wrote: >>> On Fri, Apr 26, 2024 at 4:37=E2=80=AFAM Riku Iki wrote: >>> > I am wondering if there were preallocation related changes in PG16= , and if it is possible to disable preallocation in PostgreSQL 16? >>>=20 >>> I have no opinion on the btrfs details, but I was wondering if someo= ne >>> might show up with a system that doesn't like that change. Here is a >>> magic 8, tuned on "some filesystems": >>>=20 >>> /* >>> * 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 extension= s, as it >>> * defeats delayed allocation on some filesystems. Not clear= where >>> * that decision should be made though? For now just use a c= utoff of >>> * 8, anything between 4 and 8 worked OK in some local testi= ng. >>> */ >>> if (numblocks > 8) >>>=20 >>> I wonder if it wants to be a GUC. --5f3ae241df24428b8bfa3f30b4777af5 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hello,

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

Best,
Pierre Barre

On Fri, May 3, 2024, at 05:11, Riku Iki wrote:
I= did the testing and confirmed that this was the issue.
I run following query:

 c= reate table t as select '1234567890' from generate_series(1, 1000000000)= ;

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


Before my changes:
=
Processed 1381 files, 90007 regular extents (90010 refs),= 15 inline.
Type       Perc     D= isk Usage   Uncompressed Referenced  
TOTAL &nbs= p;     97%       41G        = ;  42G          42G      
none       100%       41G &nb= sp;        41G          41G=      
zstd        14% =      157M         1.0G     =     1.0G      
prealloc   10= 0%       16M          16M  =        16M


<= div>
After the changes:

Proce= ssed 1381 files, 347328 regular extents (347331 refs), 15 inline.
Type       Perc     Disk Usage   U= ncompressed Referenced  
TOTAL       &= nbsp;3%      1.4G          42G &= nbsp;        42G      
= none       100%       80K     &n= bsp;    80K          80K    = ;  
zstd         3%    =  1.4G          42G       &= nbsp;  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..  <= br>

On Fri, Apr 26, 2024 at 4:15=E2=80=AFPM Riku Iki = <riku.iki.x@gmail.com>= wrote:
Thank you, I have such a system. I thi= nk 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=E2=80=AFPM Thomas Munro <thomas.munro@gmail.com> wrote:
> I am wondering if ther= e were preallocation related changes in PG16, and if it is possible to d= isable 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.&n= bsp; Here is a
magic 8, tuned on "some filesystems":
<= /div>

        /*
=          * If available and useful, use posix_= fallocate() (via
         * File= Fallocate()) to extend the relation. That's often more
&n= bsp;        * efficient than using write(), as it co= mmonly won't cause the kernel
       =  * to allocate page cache space for the extended pages.
<= div>          *
    &n= bsp;    * However, we don't use FileFallocate() for small exte= nsions, 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.

--5f3ae241df24428b8bfa3f30b4777af5--