From: Nathan Bossart <nathandbossart@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Peter Geoghegan <pg@bowt.ie>
Cc: 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)
Date: Thu, 29 Dec 2022 13:37:19 -0800
Message-ID: <20221229213719.GA301584@nathanxps13> (raw)
In-Reply-To: <599741.1672345755@sss.pgh.pa.us>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: nathandbossart@gmail.com, tgl@sss.pgh.pa.us, pryzby@telsasoft.com, pg@bowt.ie, michael@paquier.xyz
Subject: Re: BUG #17717: Regression in vacuumdb (15 is slower than 10/11 and possible memory issue)
In-Reply-To: <20221229213719.GA301584@nathanxps13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox