Horton
Menu

Re: 64-bit XIDs in deleted nbtree pages

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Heikki Linnakangas <hlinnaka@iki.fi>
Date: 2021-03-09T02:03:43Z
Lists: pgsql-hackers 
On Sun, Mar 7, 2021 at 8:52 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> Yeah, I think that's right.
>
> Perhaps we can do something so that autovacuums triggered by
> autovacuum_vacuum_insert_scale_factor are triggered on only a true
> insert-only case (e.g., by checking if n_dead_tup is 0).

Right -- that's really what it would mean to "remove
vacuum_cleanup_index_scale_factor in the backbranches".

I now think that it won't even be necessary to make many changes
within VACUUM ANALYZE to avoid unwanted side-effects from removing
vacuum_cleanup_index_scale_factor, per my mail to Tom today:

https://postgr.es/m/CAH2-WzknxdComjhqo4SUxVFk_Q1171GJO2ZgHZ1Y6pion6u8rA@mail.gmail.com

I'm starting to lean towards "removing
vacuum_cleanup_index_scale_factor" in Postgres 13 and master only,
purely to fix the two issues in Postgres 13 (the insert-driven vacuum
issue and the deduplication stats issue I go into in the mail I link
to). A much more conservative approach should be used to fix the more
superficial issue -- the issue of getting an accurate value (for
pg_class.teltuples) from "info->num_heap_tuples". As discussed
already, the conservative fix is to delay reading
"info->num_heap_tuples" until btvacuumcleanup(), even in cases where
there are btbulkdelete() calls for the VACUUM.

Then we can then revisit your patch to make vacuumlazy.c skip index
vacuuming when there are very few dead tuples, but more than 0 dead
tuples [1]. I should be able to commit that for Postgres 14.

(I will probably finish off my other patch to make nbtree VACUUM
recycle pages deleted during the same VACUUM operation last of all.)

[1] https://postgr.es/m/CAD21AoAtZb4+HJT_8RoOXvu4HM-Zd4HKS3YSMCH6+-W=bDyh-w@mail.gmail.com
--
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.