Horton
Menu

Re: 64-bit XIDs in deleted nbtree pages

Victor Yegorov <vyegorov@gmail.com>

View thread
From: Victor Yegorov <vyegorov@gmail.com>
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Heikki Linnakangas <hlinnaka@iki.fi>
Date: 2021-02-13T06:26:50Z
Lists: pgsql-hackers 
сб, 13 февр. 2021 г. в 05:39, Masahiko Sawada <sawada.mshk@gmail.com>:

> > (BTW, I've been using txid_current() for my own "laptop testing", as a
> > way to work around this issue.)
> >
> > * More generally, if you really can't do recycling of pages that you
> > deleted during the last VACUUM during this VACUUM (perhaps because of
> > the presence of a long-running xact that holds open a snapshot), then
> > you have lots of *huge* problems already, and this is the least of
> > your concerns. Besides, at that point an affected VACUUM will be doing
> > work for an affected index through a btbulkdelete() call, so the
> > behavior of _bt_vacuum_needs_cleanup() becomes irrelevant.
> >
>
> I agree that there already are huge problems in that case. But I think
> we need to consider an append-only case as well; after bulk deletion
> on an append-only table, vacuum deletes heap tuples and index tuples,
> marking some index pages as dead and setting an XID into btpo.xact.
> Since we trigger autovacuums even by insertions based on
> autovacuum_vacuum_insert_scale_factor/threshold autovacuum will run on
> the table again. But if there is a long-running query a "wasted"
> cleanup scan could happen many times depending on the values of
> autovacuum_vacuum_insert_scale_factor/threshold and
> vacuum_cleanup_index_scale_factor. This should not happen in the old
> code. I agree this is DBA problem but it also means this could bring
> another new problem in a long-running query case.
>

I'd like to outline one relevant case.

Quite often bulk deletes are done on a time series data (oldest) and
effectively
removes a continuous chunk of data at the (physical) beginning of the table,
this is especially true for the append-only tables.
After the delete, planning queries takes a long time, due to MergeJoin
estimates
are using IndexScans ( see
https://postgr.es/m/17467.1426090533@sss.pgh.pa.us )
Right now we have to disable MergeJoins via the ALTER SYSTEM to mitigate
this.

So I would, actually, like it very much for VACUUM to kick in sooner in
such cases.

-- 
Victor Yegorov

Commits

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

  1. Recycle nbtree pages deleted during same VACUUM.

  2. VACUUM VERBOSE: Count "newly deleted" index pages.

  3. Use full 64-bit XIDs in deleted nbtree pages.

  4. Fix nbtree cleanup-only VACUUM stats inaccuracies.

  5. Fix undercounting in VACUUM VERBOSE output.

  6. Delete empty pages in each pass during GIST VACUUM.

  7. Use full 64-bit XID for checking if a deleted GiST page is old enough.

  8. Allow VACUUM to be run with index cleanup disabled.

  9. Skip full index scan during cleanup of B-tree indexes when possible

  10. Do index FSM vacuuming sooner.

  11. Avoid early reuse of btree pages, causing incorrect query results.

  12. Clean up API for ambulkdelete/amvacuumcleanup as per today's discussion.