Horton
Menu

Re: New IndexAM API controlling index vacuum strategies

Masahiko Sawada <sawada.mshk@gmail.com>

View thread
From: Masahiko Sawada <sawada.mshk@gmail.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2021-03-03T04:49:08Z
Lists: pgsql-hackers 
On Tue, Mar 2, 2021 at 2:34 PM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Mon, Mar 1, 2021 at 7:00 PM Peter Geoghegan <pg@bowt.ie> wrote:
> > I think that you're right. However, in practice it isn't harmful
> > because has_dead_tuples is only used when "all_visible = true", and
> > only to detect corruption (which should never happen). I think that it
> > should be fixed as part of this work, though.
>
> Currently the first callsite that calls the new
> lazy_vacuum_table_and_indexes() function in the patch
> ("skip_index_vacuum.patch") skips index vacuuming in exactly the same
> way as the second and final lazy_vacuum_table_and_indexes() call site.
> Don't we need to account for maintenance_work_mem in some way?
>
> lazy_vacuum_table_and_indexes() should probably not skip index
> vacuuming when we're close to exceeding the space allocated for the
> LVDeadTuples array. Maybe we should not skip when
> vacrelstats->dead_tuples->num_tuples is greater than 50% of
> dead_tuples->max_tuples? Of course, this would only need to be
> considered when lazy_vacuum_table_and_indexes() is only called once
> for the entire VACUUM operation (otherwise we have far too little
> maintenance_work_mem/dead_tuples->max_tuples anyway).

Doesn't it actually mean we consider how many dead *tuples* we
collected during a vacuum? I’m not sure how important the fact we’re
close to exceeding the maintenance_work_mem space. Suppose
maintenance_work_mem is 64MB, we will not skip both index vacuum and
heap vacuum if the number of dead tuples exceeds 5592404 (we can
collect 11184809 tuples with 64MB memory). But those tuples could be
concentrated in a small number of blocks, for example in a very large
table case. It seems to contradict the current strategy that we want
to skip vacuum if relatively few blocks are modified. No?

Regards,

-- 
Masahiko Sawada
EDB:  https://www.enterprisedb.com/

Commits

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

  1. Don't truncate heap when VACUUM's failsafe is in effect.

  2. Teach VACUUM to bypass unnecessary index vacuuming.

  3. Add wraparound failsafe to VACUUM.

  4. Truncate line pointer array during VACUUM.

  5. Remove tupgone special case from vacuumlazy.c.

  6. Refactor lazy_scan_heap() loop.

  7. Propagate parallel VACUUM's buffer access strategy.

  8. Simplify state managed by VACUUM.

  9. Notice that heap page has dead items during VACUUM.

  10. Adjust lazy_scan_heap() accounting comments.

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

  12. Fix some problems with VACUUM (INDEX_CLEANUP FALSE).