Date: Thu, 29 Dec 2022 13:37:19 -0800
From: Nathan Bossart <nathandbossart@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Justin Pryzby <pryzby@telsasoft.com>, Peter Geoghegan <pg@bowt.ie>,
	postgresql@taljaren.se, pgsql-hackers@lists.postgresql.org,
	Michael Paquier <michael@paquier.xyz>
Subject: Re: BUG #17717: Regression in vacuumdb (15 is slower than 10/11 and
 possible memory issue)
Message-ID: <20221229213719.GA301584@nathanxps13>
References: <3544447.1671141436@sss.pgh.pa.us>
 <CAH2-WzkXmYt-K+pQqMqjFgwU7jiykD3amngd5-jkqQF=gST7Og@mail.gmail.com>
 <3672943.1671202149@sss.pgh.pa.us>
 <CAH2-WzkHRxcE-0Mub9N0Rr09SGEEF1Ex4pUbcOBSRoNGNVy93w@mail.gmail.com>
 <407579.1672258403@sss.pgh.pa.us>
 <20221229012354.GF1153@telsasoft.com>
 <451605.1672280274@sss.pgh.pa.us>
 <576868.1672334578@sss.pgh.pa.us>
 <20221229182657.GA165622@nathanxps13>
 <599741.1672345755@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <599741.1672345755@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/20221229213719.GA301584%40nathanxps13>
Precedence: bulk

On Thu, Dec 29, 2022 at 03:29:15PM -0500, Tom Lane wrote:
> Nathan Bossart <nathandbossart@gmail.com> writes:
>> On Thu, Dec 29, 2022 at 12:22:58PM -0500, Tom Lane wrote:
>>> Justin Pryzby <pryzby@telsasoft.com> writes:
>>>> VACUUM (UPDATE_DATABASE_STATS {yes,no,only})
>>>>> VACUUM (DATABASE_STATS {UPDATE,SKIP,ONLY})
> 
>> +1 for only introducing one option.  IMHO UPDATE_DATABASE_STATS fits a
>> little better since it states the action like most of the other options,
>> but I think both choices are sufficiently clear.
> 
> I tried to make a patch along these lines, and soon hit a stumbling
> block: ONLY is a fully-reserved SQL keyword.  I don't think this
> syntax is attractive enough to justify requiring people to
> double-quote the option, so we are back to square one.  Anybody
> have a different suggestion?

Hm.  I thought about using PreventInTransactionBlock() for the function,
but that probably won't work for a few reasons.  AFAICT we'd need to
restrict it to only be callable via "SELECT update_database_stats()", which
feels a bit unnatural.

There was some discussion elsewhere [0] about adding a
PROCESS_MAIN_RELATION option or expanding PROCESS_TOAST to simplify
vacuuming the TOAST table directly.  If such an option existed, you could
call

	VACUUM (PROCESS_MAIN_RELATION FALSE, PROCESS_TOAST FALSE, UPDATE_DATABASE_STATES TRUE) pg_class;

to achieve roughly what we need.  I'll admit this is hacky, though.

So, adding both SKIP_DATABASE_STATS and ONLY_DATABASE_STATS might be the
best bet.

[0] https://postgr.es/m/20221215191246.GA252861%40nathanxps13

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com