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 1s2jKm-005WTA-H1 for pgsql-general@arkaria.postgresql.org; Fri, 03 May 2024 03:11:40 +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 1s2jKj-005FKB-SR for pgsql-general@arkaria.postgresql.org; Fri, 03 May 2024 03:11:38 +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 1s2jKj-005FJz-Cu for pgsql-general@lists.postgresql.org; Fri, 03 May 2024 03:11:38 +0000 Received: from mail-pf1-x42f.google.com ([2607:f8b0:4864:20::42f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s2jKe-001B1I-4G for pgsql-general@lists.postgresql.org; Fri, 03 May 2024 03:11:36 +0000 Received: by mail-pf1-x42f.google.com with SMTP id d2e1a72fcca58-6f44b296e1fso264237b3a.3 for ; Thu, 02 May 2024 20:11:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1714705891; x=1715310691; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=JrdDAqEz3J4QyoG9S0PgB0V/ZdVMNoNtzhO2juLXpTY=; b=OPTGWNSmkx/T4TwVsQgWoGeF0WyrLNiRz+/7ZO0ieHjT+GwGuYLghJzKSIhbgIqIpQ U3MRkrr0gURYfz4diAcfGB4R+1hc5UxNDsojJYRQAcjmz2YHKTkTBuUqU6DGCBUpQMi9 VqQoStVPg6MMU97BbbgkeQBy54lH8naD4utvrZJ5pRIjW2UFbq3C5/dfIQsvpMEICWw4 7yacYj8NNmc+OCvPYsLroju7DRHvkmo9MJAPv/xK7a8ULRABJ5FNR79sSrI3PE/52GX9 BvPpxSJUjzd/F8JQ5jHij9AyyqKKW1UNVvYcsGXN0UwjF/HolHKyQXT+t/QSnc7jJVR/ VP/Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1714705891; x=1715310691; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=JrdDAqEz3J4QyoG9S0PgB0V/ZdVMNoNtzhO2juLXpTY=; b=Nm/mUewJkKP86jgwmasgjyePSew7Kg8kxJHP9UGWXRQZtdof2rIhiyZD1b3GIdjGvZ frAhK2btxLqRADspdppB59zmc5xyJEUkPKYFWuqsCkTxV0NhU/pNMJhpUN8IywTO+ems Yw5lbgZPNJx5yAugQEvE/mgw8pIxLp05VHe+PpApqC8ydFKJjx811hVJXQiLulZ70J7D vnBh3NNEC+3QPTrZ+FWOYYXP0XZlbVPxbw3S6DDHEYfIlXNjJg2WMXyrVNPzeEqZ3sT1 s7yQK4Bst/FcfTXB1s0kC7azolI7XvUqhLZkIF+crVDRa9XZMRGYKRjXGzes6NGobNM3 dzSA== X-Gm-Message-State: AOJu0YyGxzGNk2SVOalqIh3IByG3i6DxA/BbcLF7SX4tGPQjllxx+a02 r13xXlFyUE7zPp9YxMyXoywfMGoHQksw/WRY0ihpRU8/oTDjZqQzL2EMCCbxrvsMZWlpQ8uepnU CI/RW5a9vV9kfURQZ5LFEPgZegys= X-Google-Smtp-Source: AGHT+IFzEsNahDJ3S17Ya2QigRwH5b5VPT0LFVXdlUKEDxIGhs3BA2GrzPcnBp3oO3bMvnypaqXASRh1KB4qhpgyQ9k= X-Received: by 2002:a05:6a21:2726:b0:1a9:4570:2d3b with SMTP id rm38-20020a056a21272600b001a945702d3bmr1575856pzb.7.1714705891044; Thu, 02 May 2024 20:11:31 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Riku Iki Date: Thu, 2 May 2024 20:11:19 -0700 Message-ID: Subject: Re: Preallocation changes in Postgresql 16 To: Thomas Munro Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000087ba0c06178413a7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000087ba0c06178413a7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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.. On Fri, Apr 26, 2024 at 4:15=E2=80=AFPM Riku Iki wro= te: > 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=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, an= d >> 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, a= s >> it >> * defeats delayed allocation on some filesystems. Not clear whe= re >> * that decision should be made though? For now just use a cutof= f >> 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. >> > --00000000000087ba0c06178413a7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I did the testing and confirmed that this was the issue.
I run following query:

=C2=A0create table t as select '123= 4567890' from generate_series(1, 1000000000);

I commented if (nu= mblocks > 8) codeblock, and see the following results from "compsiz= e /dbdir/" command.


Before my changes:

Processed 138= 1 files, 90007 regular extents (90010 refs), 15 inline.
Type =C2=A0 =C2= =A0 =C2=A0 Perc =C2=A0 =C2=A0 Disk Usage =C2=A0 Uncompressed Referenced =C2= =A0
TOTAL =C2=A0 =C2=A0 =C2=A0 97% =C2=A0 =C2=A0 =C2=A0 41G =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A042G =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A042G =C2=A0 = =C2=A0 =C2=A0
none =C2=A0 =C2=A0 =C2=A0 100% =C2=A0 =C2=A0 =C2=A0 41G = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A041G =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A041G= =C2=A0 =C2=A0 =C2=A0
zstd =C2=A0 =C2=A0 =C2=A0 =C2=A014% =C2=A0 =C2=A0 = =C2=A0157M =C2=A0 =C2=A0 =C2=A0 =C2=A0 1.0G =C2=A0 =C2=A0 =C2=A0 =C2=A0 1.0= G =C2=A0 =C2=A0 =C2=A0
prealloc =C2=A0 100% =C2=A0 =C2=A0 =C2=A0 16M =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A016M =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A016M


After the changes:

Processed 1381 files, 347328 regular= extents (347331 refs), 15 inline.
Type =C2=A0 =C2=A0 =C2=A0 Perc =C2=A0= =C2=A0 Disk Usage =C2=A0 Uncompressed Referenced =C2=A0
TOTAL =C2=A0 = =C2=A0 =C2=A0 =C2=A03% =C2=A0 =C2=A0 =C2=A01.4G =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A042G =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A042G =C2=A0 =C2=A0 =C2=A0
no= ne =C2=A0 =C2=A0 =C2=A0 100% =C2=A0 =C2=A0 =C2=A0 80K =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A080K =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A080K =C2=A0 =C2=A0 =C2=A0=
zstd =C2=A0 =C2=A0 =C2=A0 =C2=A0 3% =C2=A0 =C2=A0 =C2=A01.4G =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A042G =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A042G

<= /div>
It is clearly visible that files created with fallocate are not c= ompressed, and disk usage is much larger.
I am wondering if there= is a way to have some feature request to have this parameter user configur= able..=C2=A0=C2=A0

On Fri, Apr 26, 2024 at 4:15=E2=80=AFPM Riku Iki <riku.iki.x@gmail.com> wrote:
Than= k you, I have such a system. I think my task would be to compile PG from so= urces(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&= gt; wrote:
On Fr= i, Apr 26, 2024 at 4:37=E2=80=AFAM Riku Iki <riku.iki.x@gmail.com> wrote:
> I am wondering if there were preallocation related changes in PG16, an= d 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.=C2=A0 Here i= s a
magic 8, tuned on "some filesystems":

=C2=A0 =C2=A0 =C2=A0 =C2=A0 /*
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* If available and useful, use posix_fall= ocate() (via
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* FileFallocate()) to extend the relation= . That's often more
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* efficient than using write(), as it com= monly won't cause the kernel
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* to allocate page cache space for the ex= tended pages.
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0*
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* However, we don't use FileFallocate= () for small extensions, as it
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* defeats delayed allocation on some file= systems. Not clear where
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* that decision should be made though? Fo= r now just use a cutoff of
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* 8, anything between 4 and 8 worked OK i= n some local testing.
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0*/
=C2=A0 =C2=A0 =C2=A0 =C2=A0 if (numblocks > 8)

I wonder if it wants to be a GUC.
--00000000000087ba0c06178413a7--