Vacuuming the free space map considered harmful?

From: Christophe Pettus <xof(at)thebuild(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Vacuuming the free space map considered harmful?
Date: 2025-03-19 08:53:37
Message-ID: EEE26A9D-E41A-4BD7-90BE-F1DF545B7AEB@thebuild.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

We're tracking down an issue that we've seen in two separate installations so far, which is that, at the very end of a vacuum, the vacuum operation starts using *very* high levels of CPU and (sometimes) I/O, often to the point that the system becomes unable to service other requests. We've seen this on versions 15, 16, and 17 so far.

The common data points are:

1. The table being vacuumed is large (>250 million rows, often in the >10 billion row level).
2. The table has a relatively high churn rate.
3. The number of updated / deleted rows before that particular vacuum cycle are very high.

Everything seems to point to the vacuum free space map operation, since it would have a lot of work to do in that particular situation, it happens at just the right place in the vacuum cycle, and its resource consumption is not throttled the way the regular vacuum operation is.

Assuming this analysis is correct, our current proposal as a temporary fix is to increase the frequency of autovacuum on those tables, so that the free space map vacuum operation has less to do, and is less likely to consume the system. In the longer run, is it worth considering implementing a cost delay inside of the free space map update operations?

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message vignesh C 2025-03-19 08:58:58 Re: Enhance 'pg_createsubscriber' to retrieve databases automatically when no database is provided.
Previous Message Amit Kapila 2025-03-19 08:44:09 Re: Adding a '--clean-publisher-objects' option to 'pg_createsubscriber' utility.