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 1uKHgQ-000S7v-A5 for pgsql-hackers@arkaria.postgresql.org; Wed, 28 May 2025 14:23:06 +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 1uKHgN-000QpR-Ua for pgsql-hackers@arkaria.postgresql.org; Wed, 28 May 2025 14:23:03 +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 1uKHgN-000QpG-JM for pgsql-hackers@lists.postgresql.org; Wed, 28 May 2025 14:23:03 +0000 Received: from mout.gmx.net ([212.227.17.22]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uKHgL-000O9N-1T for pgsql-hackers@lists.postgresql.org; Wed, 28 May 2025 14:23:02 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1748442174; x=1749046974; i=jimis@gmx.net; bh=zOs1emHLa/5od3ZK4axldDNlrC2B3V8Pibb0+SgcPwg=; h=X-UI-Sender-Class:Date:From:To:cc:Subject:Message-ID: MIME-Version:Content-Type:Content-Transfer-Encoding:cc: content-transfer-encoding:content-type:date:from:message-id: mime-version:reply-to:subject:to; b=slSBBNNVKEQ3MVoLWux4s/eBFb+YiK6Rl8N0mUiAYdLsVXBZqzFSDQKInJ8i16GG xaaOWMI5gxuOh8P+ps3zKewGY9nnhjuZ25ijCVZS9rPBAsTr0ASkJVTQOqMkZJ+3L mxGDp9vMVeE9e32lEqSQeVWgjBCKDPeIgsg3jwmVZXEgAxfr0jxHuZOpdlvOL+8bM V9Um5+fTf+BzoJYCGU+y5iRY252A4czgQ8gpLLD32D/9O7LrV9kDky3v6MlI4SGfM 79s4H52oMGGCS6N2qi7/gUeYvj8kBbwATVea65I+MARR+uNEhUtjdVjOIlB+Bhq1T YQjG9ojVhWMHAA42rQ== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.9.71.56] ([185.55.106.54]) by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1MulmF-1vASxa2j8l-012fBv; Wed, 28 May 2025 16:22:53 +0200 Date: Wed, 28 May 2025 16:22:51 +0200 (CEST) From: Dimitrios Apostolou To: pgsql-hackers@lists.postgresql.org cc: Andres Freund , Melanie Plageman , Heikki Linnakangas , Kyotaro Horiguchi , David Rowley , John Naylor Subject: [PING] fallocate() causes btrfs to never compress postgresql files Message-ID: MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset=US-ASCII X-Provags-ID: V03:K1:4qSCkyBVt0emYQP+4Rq0dAkaDO2j93lO0rNw++mu2gi2sJb+aTz VKu8jNy7oCCkVj4Y7N9roc2TqXh8fatgyxvtD9l+mWsPcaJ1VwyuVnUSxJGj+hkUZpAuNhv eFQ2EODu0cmPrD2U+UUxyOipJbwSQ45CTyqiFlH6uObZzXGqXJTjkgnJRFdrAVAn6IVrqwW NldXnG3Bvq5goGp2j5Mhg== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:QKsDoetKxIM=;AinThqRVjXScEygqmZyizmVrIM3 3dvarI7Cz8WEVIJe4mBgc8MQqC5Ps5LPIv4BQg+Rxt00u0i/yJSQudjdSSAU4xR+UlpXoUwga 6LVjCxCxVOLrPfqmwHzT3CU02HWZyZgy/R2j1RY6jKE1tgPexh4afDysnqyFfOldAwK6yY4oM K4qTOScs/x9WqIV+j4jYHcU3SN4GZvfnML5E3q37jUDoN/odMwJSyD54cgoVFh+SQn+oF9lbU zw0NHqyUMI5a55Jaf2jrv0MOBHVrLaxcMMyJBt5isl6pLzoXa/2Yt5K4gLm7I+SE2h7vilVwL yQ7l/LEjtVbkH0rhFmpvsIiw4l2IH01KF+iLwR6wDVq301u6JwZ6uM4n7EHenwoX8BRJfTZNQ QtnO1x6zZKpcQ1kJnhRf2gEhBsOz2eAThcKBw6frrX5uiT1yfHetP73cZjxT7JDYCB+gGZKdM oAfjDe61DBVZ2iCZCdJ4TuaGDMfm00J7H5tM0Bq4HzCBpbkYiszt+uUYd/YMkzodBCE8oJBPK sSlGz9XTLQWyCcwdOCzF7HRgTOAhwAfo9JWKDTPX1y6c1SNZ8B4tda8TnRo7gJ5D0kh53GRZX nWUneLO04dyH/Wx1FO5rbAu3J7yQoK7elzduE63f0qaYtaHMS3pUFwCcUHMYXKcZ6aEcibvwH 7tPQ0ojZoFAKB7WhEX6QMxmeVuToBkXouypGElE3r1k3xXMRRM9GJzDxDx2ist1ld8r79rro3 FU8ldrdqwcqQh7mJrX+E0YS0GyXE73I0IZ6EdGJiUj+CwtULhUTYghFa4k62MZEmnc3iSa14/ MpPkISXQ6EAoHoFxjdatcQ217uvijLjq4pt80U+1r+JirVE7+HWrlaZ5edu2COthsCVw0ePsn 0Itpb5zxbDkQFOrzuUAjQSqIfwUyW1rwRk2VX+NysVDW9YKfSYqC0UCcm1Cm9Jj1/eP9KfhcL CpFgiaRhD8S7AG5Bs5O/B/Dy+TpC6SWCyXp/W9WPzy+aNChIDCFXVTrSC5zCgUw3LPXVuEQn8 IWxVVC9FycZQl7whtN7mjyaDxsY3cKQnIZ++9APpzH4ETHdy4iXHq+8Hy+930xKkBXx4mdki2 K6LrwhMO0D+fZzwBl4wZcmgjhTXLnywTIdVpY6fQrIgHKX8NtycAZuKWWjTEkJ13Dxwiqgu26 ZQLzRuDJnC49HhN9GtKCK1rZPjTIodNvtcdo6p2Ygc/dH5YGz9S9YGvrdC3xGuyIKgrIanWSw MM6yimFPPYVgJz8xcwocqeAQC7nYCdM41VpepCBf1mtwB2wjYiY1/pQ8cYvnhhHO+jj3jQG1Z C139JsGZdv/40jZgfBU6GS/c31TkN0rgY0RjPuLSNkcKaGqkutX4uULqRe7HxjD8j7Tfzey62 ImEnip92Cgx6X/a/tOhrS8Ty7Gr8LN58aTERA3f3OhlV6pXm5MfGmPMP7DeaAnVCmGesk5Y4e WhY+2xnZ9lHnZgQkmcKfq8i8KajTlCVpPIFXJsAOQyMpwr6+ABAdJqUpdZTkv2nzB2v750oKg ur5kl/v23LfUiGsTmL4Wi1+y3unylsXQ++6nP3t4nmAXCd59qi665DNUkFO3HCNPGLkCgnkVj w2dCeIAbuS6XJEEtQJ7agZTT2opwxn3RwyqwhWyEMtl8BMxuQ0CAeRiVdiip5cl+C5FowXGov byfl+GcHcORfkN+Il0mAM23Hmh8wDpxyRj6DC7KU9jq+08o2hkal8tj+uSSihMo7ljfS6pK+V 5LfqaGERa6azhoLtChKkS1Pc/4dr9qeuX/gPKKmZLFMUM/9ubJbHVZLkLCUcXPTug06hg/Sgc QQ1jBG/UunjFq502ZSPsvD5hFNwhQl94WaLhVZkIPoHpWoRKGTSDr9LMuz4TqQkF8OaI6PBgC YZH0BcbUoJQh0sJ52Gmc8njocL+XZe4zzV53BSyTSIm/KlUWFwNxsPVrMh0xnE18VpgDhtKD4 oMvlDoe0sbQM3/nYzS5dbseDwiWZ5lSz7JGAp19b69aRwXd3IPSAcPReXIIHhyWR40/nlOFxn o2XEqno80jJYXmwCkEN1/nYqaR2s3NLPxpTZNK3lfSo42cQ1oEXMM09Q+IvtIKj2F2rAK4uPw 3c8Rde+20N78ucElcbWHV7RoHY3C/GqS8LzJVn4oZJO60Dgf+BqGgZNOxMncefX6zwJighOk9 xjg0YW20f643SbDZ+iSkXq/l7ClucA7t33X3EoZrzHDdvxaOeIALMxi40BL0EANLzP9Z87D5+ fsFcZow5k6yB83cDxgVEVmmDvjEVS5jFUWiWvVKQv3uboB7BIcv+VtpukPvGnYjO4Ub5RCPBJ 7vrd4S3DNyH84AhWOb92nUpr1X52zrhc/8a2mjTEO70n0KaO77E7AEHf3qmRlOUWAVr0IltD2 D+2rDpxHBYTvTwAVF+VN+lOg7d4vQv3T3lNm7jfmXuQmTO35ZaAY0b53lky6pwnSKqvpTtGR1 oo6cr3lcr/Twm2gK8pi7O+qkBADblDKZSAkCTABrg6iHWEQAZnTufjB7Urf2su2iDRsvsMjMm abQrxhNNmvATRsYqsYEFnfLlyeMb6XGUDXNDUAwhJOWjJcoDtTHkgRhoPwlE5N+ThPUbItP4g inM18BjGxdG3Nx9O5GefCtGqcmVXQ1zZ6DDLl5q/r01qA6olY/jodpIy9ns3jIGRysr/O75Fa zfL7rCl+PSj8OKEzQKk1x75z+vIwcNvYt76LXVDOHucAjepavooGo4d6MzLm1pi6+DDA6o75J WiGjHo7JhVeQmgLBqpDAmTDaEj2LBIAklqJfHjqJIJlTjp/BsCoZAx/2bSGIOWpdJUTEfsVTZ oKw4N6DRJXd9MWO4M6erpPe1yN162whuh8tzolM+itUH8Zq6+2LI77a7tBJmcKMCQ0Ea/uhQP cUssS1ZIP+PyIR/H0+jE1+gIhbhtyf8PY+XfAoEDKYPJf+Imbxwb5ElscfmgpoSK3GeuWbTON l90wGpQZm82KKcjBcEtB1iwiUengQxG7KScYFXCoEl4qLnaM+lAJwlr+UruYxtNwdY+uarSbf wJ6XOPdj6VTF6hjiDhoGAmhajHLfFdrOvd34giFQouRMHTrXhpt/sgJDHmfYMs+kVRAZLNuLM KRCFPM6owJQeadXO5ChR1kMWvro2otsySLRCZ6A4cxlzoHef3bq0XSeWDOv00KSOloQxsWHaX K9PM+7ju21KV46LTbReKFpUhr Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hello, sorry for mass sending this, but I didn't get any response to my=20 first email [1] so I'm now CC'ing the commit's 4d330a6 [2] author and the= =20 reviewers. I think it's an important issue, because I need to=20 custom-compile postgresql to have what I had before: a transparently=20 compressed database. [1] https://www.postgresql.org/message-id/d0f4fc11-969d-7b3a-aacf-00f86450= e738@gmx.net [2] https://github.com/postgres/postgres/commit/4d330a61bb1969df31f2cebfe1= ba9d1d004346d8 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=3Dno 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