Date: Thu, 9 Dec 2021 17:56:16 -0800
From: Andres Freund <andres@anarazel.de>
To: Peter Geoghegan <pg@bowt.ie>
Cc: John Naylor <john.naylor@enterprisedb.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: do only critical work during single-user vacuum?
Message-ID: <20211210015616.o242b4xchhpglfcy@alap3.anarazel.de>
References: 
 <CAFBsxsFL5LoWanSLDjt_SjS9=FU_9XWi22iHMexRS2SubkX-Vw@mail.gmail.com>
 <CAH2-Wz=Z5Jjd1y8kCfXd6e-GSvKiL8XEmxoJJFNHcekM4aw-Vg@mail.gmail.com>
 <CAH2-Wzn3pE8rNzBxh-Qpmq=zxFWFpoC7LWLbyDg_1XZ3wJOtUA@mail.gmail.com>
 <CAFBsxsG-VDhXYa25uERfDGD4dO_AZ3d+Dv2NF+DJBADZiXejpw@mail.gmail.com>
 <CAH2-Wznop1jM3j6E1xTcG--kFQ1hW5cMmM8B1965Mz+uUg6HSg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAH2-Wznop1jM3j6E1xTcG--kFQ1hW5cMmM8B1965Mz+uUg6HSg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/20211210015616.o242b4xchhpglfcy%40alap3.anarazel.de>
Precedence: bulk

Hi,

On 2021-12-09 16:34:53 -0800, Peter Geoghegan wrote:
> But even still, why not have some variant of single-user mode just for
> this task?

> Something that's easy to use when the DBA is rudely
> awakened at 4am -- something a little bit like a big red button that
> fixes the exact problem of XID exhaustion, in a reasonably targeted
> way? I don't think that this needs to involve the VACUUM command
> itself.

I think we should move *away* from single user mode, rather than the
opposite. It's a substantial code burden and it's hard to use.

I don't think single user mode is a good fit for this anyway - it's inherently
focussed on connecting to a single database. But wraparound issues often
involve more than one database (often just because of shared catalogs).


Also, requiring a restart will often exascerbate the problem - the cache will
be cold, there's no walwriter, etc, making the vacuum slower. Making vacuum
not consume an xid seems like a lot more promising - and quite doable. Then
there's no need to restart at all.

Greetings,

Andres Freund