MIME-Version: 1.0
References: <CAJ+5Ff6WtFCzamrRZqN3u3htvkGcmob7VaYWYBd+sRx6jKpHuA@mail.gmail.com>
 <66D17234-043D-457E-8607-5126B4FBC3DD@thebuild.com>
In-Reply-To: <66D17234-043D-457E-8607-5126B4FBC3DD@thebuild.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Fri, 9 Aug 2024 11:02:19 +1200
Message-ID: <CAApHDvrLiwiB8o+3R2M4JbwyovhD7Btt0h=+8_d85Lt+oxoy7Q@mail.gmail.com>
Subject: Re: Vacuum full connection exhaustion
To: Christophe Pettus <xof@thebuild.com>
Cc: Costa Alexoglou <costa@dbtune.com>, pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAApHDvrLiwiB8o%2B3R2M4JbwyovhD7Btt0h%3D%2B8_d85Lt%2Boxoy7Q%40mail.gmail.com>
Precedence: bulk

On Fri, 9 Aug 2024 at 02:12, Christophe Pettus <xof@thebuild.com> wrote:
> VACUUM FULL takes an exclusive lock on the table that it is operating on.=
  It's possible that a connection becomes blocked on that exclusive lock wa=
iting for the VACUUM FULL to finish, the application sees the connection st=
opped and fires up another one (this is common in container-based applicati=
ons), that one blocks... until all of the connections are full of queries w=
aiting on that VACUUM FULL.

I also imagine this is the cause. One way to test would be to do:
BEGIN; LOCK TABLE <name of table>; and see if the connections pile up
in a similar way to when the VACUUM FULL command is used.

David