Managing autovacuum freezing

PG 12.4

We're observing I/O contention (including long COMMIT times) that are
coinciding with anti-wraparound autovacuums of a particularly busy table.
This table sees roughly 100K updates per hour. There are a handful (~500)
inserts per hour and rarely any deletes at all. Very update-heavy. The
table has just around 5Million rows, so it's not really very big at all,
but there are around 10Million dead rows as well, so there's bloat involved.

Autovacuum has been disabled on this table for a couple years now (before
my time here) and nightly VACUUM ANALYZE jobs are run via crontab. However
every 2-3 days we'll see the anti-wraparound autovacuum run for this table
and really tank I/O.

My understanding is that these manual VACUUM ANALYZE jobs are not freezing
rows that regular autovacuuming would otherwise be doing, which leads up to
the big anti-wraparound job.

We haven't changed any of the vacuum/autovacuum GUC parameters (although
yesterday I noticed our cookbook is using the old
autovacuum_vacuum_cost_delay=20ms default from pre-PG12). I'm assuming
we'll need to do some table-specific tuning for this very active table if
we were to re-enable autovacuum on it. I'm assuming we'll want to do more
frequent (and less intensive) autovacuums throughout the day.

My concern first is whether or not autovacuum would be able to keep up
throughout the day, no matter how well it is tuned. Not sure how I can
gauge that before pulling the trigger though.

If we exhaust all tuning options, we could look at making those nightly
VACUUM ANALYZE jobs into VACUUM FREEZE ANALYZE jobs so at least we take the
I/O hit during off-peak hours.

Anyway, I'm very interested in what others on the list may have experienced
in this area and what solutions you came up with. Thanks!

Don.

PS - Here are some stats I got yesterday in 5 minute samples:

now | inserts | updates | deletes |
live_tuples | dead_tuples
-------------------------------+---------+------------+---------+-------------+-------------
2021-02-10 17:01:30.625908+00 | 1058911 | 3076183381 | 0 |
4950291 | 10479817
2021-02-10 17:06:30.940216+00 | 1058956 | 3076277221 | 0 |
4950336 | 10543123
2021-02-10 17:11:31.246943+00 | 1058995 | 3076370779 | 0 |
4950375 | 10604466
2021-02-10 17:16:31.556107+00 | 1059038 | 3076465333 | 0 |
4950418 | 10666903
2021-02-10 17:21:31.872876+00 | 1059084 | 3076558998 | 0 |
4950464 | 10729782
2021-02-10 17:26:32.184989+00 | 1059118 | 3076652179 | 0 |
4950498 | 10790894
2021-02-10 17:31:32.504895+00 | 1059158 | 3076744046 | 0 |
4950538 | 10851880
2021-02-10 17:36:32.812956+00 | 1059199 | 3076837208 | 0 |
4950579 | 10913580
2021-02-10 17:41:33.126065+00 | 1059238 | 3076928165 | 0 |
4950618 | 10973362
2021-02-10 17:46:33.439005+00 | 1059287 | 3077017101 | 0 |
4950667 | 11032461
2021-02-10 17:51:33.749222+00 | 1059327 | 3077109811 | 0 |
4950707 | 11092947
2021-02-10 17:56:34.064872+00 | 1059371 | 3077199779 | 0 |
4950751 | 11151997
2021-02-10 18:01:34.376887+00 | 1059414 | 3077287739 | 2 |
4950794 | 11209508

--
Don Seiler
www.seiler.us