MIME-Version: 1.0
References: <AS1PR05MB9105F5A24271EEC357DA5D919F9B2@AS1PR05MB9105.eurprd05.prod.outlook.com>
 <202409111239.n2dybum5hcwj@alvherre.pgsql> <CA+hUKGKM16mbwCvyRCJZHM-LdC+ufK0b09Wnmh9ivGQDi6bv6g@mail.gmail.com>
 <AS1PR05MB9105E3395555A70408D027309F642@AS1PR05MB9105.eurprd05.prod.outlook.com>
In-Reply-To: <AS1PR05MB9105E3395555A70408D027309F642@AS1PR05MB9105.eurprd05.prod.outlook.com>
From: Thomas Munro <thomas.munro@gmail.com>
Date: Fri, 13 Sep 2024 10:01:53 +1200
Message-ID: <CA+hUKGJ=4YuLZ7YEkNz3Xst_r4NfEM7Hk=1x0B4Le1bhO5nQgQ@mail.gmail.com>
Subject: Re: Error:could not extend file " with FileFallocate(): No space left
 on device
To: =?UTF-8?B?UGVjc8O2ayBKw6Fu?= <jan.pecsok@profinit.eu>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>, Andres Freund <andres@anarazel.de>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BhUKGJ%3D4YuLZ7YEkNz3Xst_r4NfEM7Hk%3D1x0B4Le1bhO5nQgQ%40mail.gmail.com>
Precedence: bulk

On Thu, Sep 12, 2024 at 8:54=E2=80=AFPM Pecs=C3=B6k J=C3=A1n <jan.pecsok@pr=
ofinit.eu> wrote:
> In link you provided there is mention, that in PostgreSQL 16 data is not =
being
> compressed for PostgreSQL 16 server. Does it mean, that PosgreSQL 16 use =
much more space while computing queries?
> If that is the case, it can be our problem, because our queries use somet=
imes several TB of disk space for computation and if there is considerable =
increase in disk usage during the queries, it can happen, that sometimes 27=
TB is not enough.

The kind of compression discussed there is a btrfs feature.  Xfs
doesn't have compression.

> I have 2 questions,
>
> Is there any workaround, that Posgres wont use FileFallocate? Maybe set s=
omething in Linux not to allow Posgres to use it?

Not currently.  I was thinking of proposing to introduce a setting and
back-patching it into 16, because it's a sort of regression for btrfs
users (and a hard one to foresee).  It is not at all clear what
exactly is happening on this xfs system, but something else...

> The change was introduced in Posgres 16, does it mean, that Posgres 15.8 =
should have old behaviour?

Yes.

> We dont use COPY in our queries.

OK so it's presumably due to having lots of concurrent DML operations
(most likely INSERT, could also be UPDATE) that need to extend the
relation.  I'm not sure of the exact behaviour of the heuristics
off the top of my head (but basically it's driven by waitcount[1])...
perhaps if you had only 7 concurrent DML operations and not 8+, it
would be less likely to take the fallocate path, something like
that...  That "8" is the threshold I was thinking of turning into a
GUC, perhaps in the November minor release, but it's not exactly clear
that posix_fallocate() is really the problem.  (I see that there have
been bugs in xfs's posix_fallocate() space accounting, but the one
that I found was about redundant posix_fallocate() over a region that
is already allocated, which PostgreSQL doesn't do.)

However it is far from clear what is actually going wrong here.
Although it seems to imply a pretty weird/bogus use of ENOSPC by the
kernel, that link I posted seems to be hinting that something a bit
different is going on.  It may be clutching at straws, but you might
try increasing those ulimits.  I'm not sure how to try to reproduce it
in lab conditions since it's apparently pretty hard to hit, based on
your 1-2 week MTBF on what sounds like a massive and busy system.
Hmm...

[1] https://github.com/postgres/postgres/commit/00d1e02be24987180115e371aba=
eb84738257ae2