Are the autovacuum_vacuum_cost_* settings handled any differently for 'to avoid wraparound' vacuums?
I understand that it won't give up a lock but I was expecting it to still back off due to cost and allow the query with conflicting lock to proceed.
IIUC "conflicting lock to proceed" is just a different way to say "give up a lock".
In any case the cost-based stuff throttles I/O only (per the docs at least) but even while sleeping it still holds its lock. And it won't be kicked off of the lock by other processes. I don't see where it is documented that the autovacuum cost settings are altered during the anti-wraparound vacuum so I presume it will still sleep by default.
Is there any benefit to manually running a vacuum every so many inserts as opposed to using autovacuum_freeze_max_age. And in this case should it be a vacuum freeze. Rows are never updated or deleted except for the occasional roll back due to dropped network connections.
You might consider creating a security definer function (that performs vacuum freeze on the table) owned by the table owner and grant your inserting process the ability to execute it.