MIME-Version: 1.0
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>
 <20211210015616.o242b4xchhpglfcy@alap3.anarazel.de>
In-Reply-To: <20211210015616.o242b4xchhpglfcy@alap3.anarazel.de>
From: Peter Geoghegan <pg@bowt.ie>
Date: Thu, 9 Dec 2021 18:32:12 -0800
Message-ID: 
 <CAH2-Wzki0HX4=sJxyqTMz=C8KhqNxsTFvDTOoeaQj07mMeLHtg@mail.gmail.com>
Subject: Re: do only critical work during single-user vacuum?
To: Andres Freund <andres@anarazel.de>
Cc: John Naylor <john.naylor@enterprisedb.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAH2-Wzki0HX4%3DsJxyqTMz%3DC8KhqNxsTFvDTOoeaQj07mMeLHtg%40mail.gmail.com>
Precedence: bulk

On Thu, Dec 9, 2021 at 5:56 PM Andres Freund <andres@anarazel.de> wrote:
> 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 wouldn't say that this is moving closer to single user mode.

> 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).

I don't disagree with any of that. My suggestions were based on the
assumption that it might be unrealistic to expect somebody to spend a
huge amount of time on this, given that (in a certain sense) it's
never really supposed to be used. Even a very simple approach would be
a big improvement.

> 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.

I didn't give too much consideration to what it would take to keep the
system partially online, without introducing excessive complexity.
Maybe it wouldn't be that hard to teach the system to stop allocating
XIDs, while still allowing autovacuum workers to continue to get the
system functioning again. With the av workers taking a particular
emphasis on doing whatever work is required for the system to be able
to allocate XIDs again -- but not too much more (not until things are
back to normal). Now the plan is starting to get ambitious relative to
how often it'll be seen by users, though.

-- 
Peter Geoghegan