Horton

Re: BUG #17717: Regression in vacuumdb (15 is slower than 10/11 and possible memory issue)

Nathan Bossart <nathandbossart@gmail.com>

View thread
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>
Date: 2022-12-29T21:37:19Z
Lists: pgsql-bugs, pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add options to control whether VACUUM runs vac_update_datfrozenxid.

  2. Use catalog query to discover tables to process in vacuumdb

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