Horton
Menu

Re: New IndexAM API controlling index vacuum strategies

Greg Stark <stark@mit.edu>

View thread
From: Greg Stark <stark@mit.edu>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, Robert Haas <robertmhaas@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Noah Misch <noah@leadboat.com>
Date: 2021-03-21T08:24:15Z
Lists: pgsql-hackers 
On Thu, 18 Mar 2021 at 14:37, Peter Geoghegan <pg@bowt.ie> wrote:

> They usually involve some *combination* of Postgres problems,
> application code problems, and DBA error. Not any one thing. I've seen
> problems with application code that runs DDL at scheduled intervals,
> which interacts badly with vacuum -- but only really on the rare
> occasions when freezing is required!

What I've seen is an application that regularly ran ANALYZE on a
table. This worked fine as long as vacuums took less than the interval
between analyzes (in this case 1h) but once vacuum started taking
longer than that interval autovacuum would cancel it every time due to
the conflicting lock.

That would have just continued until the wraparound vacuum which
wouldn't self-cancel except that there was also a demon running which
would look for sessions stuck on a lock and kill the blocker -- which
included killing the wraparound vacuum.

And yes, this demon is obviously a terrible idea but of course it was
meant for killing buggy user queries. It wasn't expecting to find
autovacuum jobs blocking things.  The real surprise for that user was
that VACUUM could be blocked by things that someone would reasonably
want to run regularly like ANALYZE.




-- 
greg

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).