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 1s027v-000DTo-NY for pgsql-general@arkaria.postgresql.org; Thu, 25 Apr 2024 16:39:15 +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 1s027t-000hZ6-Ba for pgsql-general@arkaria.postgresql.org; Thu, 25 Apr 2024 16:39:14 +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 1rzoAZ-001uTT-6l for pgsql-general@lists.postgresql.org; Thu, 25 Apr 2024 01:45:03 +0000 Received: from mail-pf1-x42b.google.com ([2607:f8b0:4864:20::42b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rzoAV-004VlB-Ld for pgsql-general@lists.postgresql.org; Thu, 25 Apr 2024 01:45:02 +0000 Received: by mail-pf1-x42b.google.com with SMTP id d2e1a72fcca58-6eff2be3b33so535372b3a.2 for ; Wed, 24 Apr 2024 18:44:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1714009498; x=1714614298; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=JKXMfxJWTdV90bRvmzhDczNt/9+rGAKvn/N701yF/WU=; b=XzUIvQ1oAHyIU8rkANnZMkii6YF1MjfdS67ZvP55kgcCGSWLIAKGvcJNC/NncQPipo rkQqp27A9wLNiGK1CjdXOL/iKgTbg8cwcu5h55AX9CBHoUuQwi4QLVL9ow7/Sa7mWRz+ ef9Mngw6kg6kj++h/Zz7oDnd9kkBkCOZ3aCvc/sByLWtk9xxmoAe1fOsyCOUFHYC/ePx 0ETV++5JT4uzERPaa0qP+WOBr2IjKL1+K1DgKy5Q39Fx9EIptVNEXEllkVtPzCub+VHy cpP52RKUklW3N2ggjUBpb4FpJU8ovrKQsB9e5VbzPnXxz92q1ySZ4Fy/Vjl92iJj68d+ OZ2A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1714009498; x=1714614298; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=JKXMfxJWTdV90bRvmzhDczNt/9+rGAKvn/N701yF/WU=; b=RrYiZq1ITtuN72uo30vpVV1DKhf9POz1lAdiv++liCoKGtNYaMt4XDg8ZwXvzUqCv8 TanIIPahVSZvcBxsqP4ZUVwOwfnVX2hVOC07tt2kWAGPuYuUGURrJJbUxSeWXCC34+BH a7ElBbfp5ONg/4+7gU9in8NJqvEuo/vI+vaujui1fKANqrgXIAzuUZty+3ictaS1AZnT xcOwNhYfqQ7dmzKAiHYdmaWdc8f2QUPxP72vcxN76MXlohknd0i1X2GCfvkIiO3LxD2o 6XyYImCgNnHAxen/FNCZOHiWLlNklcTzcQMMjiyINROplGediZLp31bJIWfHHnOr9Dw9 r+sg== X-Gm-Message-State: AOJu0YwMIs5mJzzVqX8BY3BiFQzhH3hAKIYYaxjuU4bjQghhx9lfrtCK KHlRo1kOgWwY1TrUQDf1BvdlXoxuoV4n/SD/4sgQ7YqZiB4JuWwxWfX2MOy2R8x4mm9soN0UoXJ OPM1aqzAUnid4OegkPGzomhIYKaP26GT7 X-Google-Smtp-Source: AGHT+IGcBPaqCkuKkQNrG1NcY4AtvXwrHsV9s+MXNUr477htdfiylbxRzRqYJJ8LgMj4TDKFFHcvh4QG6WLha7PcSQY= X-Received: by 2002:a05:6a20:6a0d:b0:1a9:499c:5778 with SMTP id p13-20020a056a206a0d00b001a9499c5778mr5099429pzk.35.1714009498169; Wed, 24 Apr 2024 18:44:58 -0700 (PDT) MIME-Version: 1.0 From: Riku Iki Date: Wed, 24 Apr 2024 18:44:47 -0700 Message-ID: Subject: Preallocation changes in Postgresql 16 To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000047c7920616e1ef86" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000047c7920616e1ef86 Content-Type: text/plain; charset="UTF-8" Hi, We have PostgreSQL server, which currently runs PostgreSQL 15 on compressed btrfs. I tried to migrate DB to PostgreSQL 16, and found that data is not being compressed for PostgreSQL 16 server. One of the possible reason why btrfs won't compress data is data preallocation. When running "compsize" tool, I indeed see that PostgreSQL preallocating data and it is not compressed (there is separate "preallocated" entry in output). I am wondering if there were preallocation related changes in PG16, and if it is possible to disable preallocation in PostgreSQL 16? I posted this on StackExchange, and someone pointed on this commit as possible reason of such behavior. Long discussion on lore.kernel.org about exactly this issue. --00000000000047c7920616e1ef86 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hi,

We have PostgreSQL = server, which currently runs PostgreSQL 15 on compressed btrfs.

I tried to migrate DB to PostgreSQL 16, and found that data is not = being compressed for PostgreSQL 16 server. One of the possible reason why b= trfs won't compress data is data preallocation.

When= running "compsize" tool, I indeed see that PostgreSQL preallocat= ing data and it is not compressed (there is separate "preallocated&quo= t; entry in output).

I am wondering if there were preall= ocation related changes in PG16, and if it is possible to disable prealloca= tion in PostgreSQL 16?

I posted this on StackExchange, and someone pointed on this commit as possible reason of such behavior.

Long discussion=C2=A0on lore= .kernel.org about exactly this issue.

--00000000000047c7920616e1ef86--