Horton
Menu

Re: 64-bit XIDs in deleted nbtree pages

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Victor Yegorov <vyegorov@gmail.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Heikki Linnakangas <hlinnaka@iki.fi>
Date: 2021-02-14T05:02:12Z
Lists: pgsql-hackers 
On Fri, Feb 12, 2021 at 10:27 PM Victor Yegorov <vyegorov@gmail.com> wrote:
> 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.

Masahiko was specifically concerned about workloads with
bursty/uneven/mixed VACUUM triggering conditions -- he mentioned
autovacuum_vacuum_insert_scale_factor/threshold as being applied to
trigger a second VACUUM (which follows from an initial VACUUM that
performs deletions following a bulk DELETE).

A VACUUM that needs to delete index tuples will do its btvacuumscan()
through the btbulkdelete() path, not through the btvacuumcleanup()
"cleanup only" path. The btbulkdelete() path won't ever call
_bt_vacuum_needs_cleanup() in the first place, and so there can be no
risk that the relevant changes (changes that the patch makes to that
function) will have some new bad effect. The problem that you have
described seems very real, but it doesn't seem relevant to the
specific scenario that Masahiko expressed concern about. Nor does it
seem relevant to this patch more generally.

-- 
Peter Geoghegan

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.