I'd like to propose to add $SUBJECT for performance improvement.
When VACUUM tries to truncate the trailing empty pages, it scans shared_buffers to invalidate the pages-to-truncate during holding an AccessExclusive lock on the relation. So if shared_buffers is huge, other transactions need to wait for a very long time before accessing to the relation. Which would cause the response-time spikes, for example, I observed such spikes several times on the server with shared_buffers = 300GB while running the benchmark. Therefore, I'm thinking to propose $SUBJECT and enable it to avoid such spikes for that relation.
Alvaro reminded me that we already have a mechanism in place which forces VACUUM to give up the exclusive lock if another backend is waiting on the lock for more than certain pre-defined duration. AFAICS we give up the lock, but again retry truncation from the previously left off position. What if we make that lock-wait duration configurable on a per-table basis? And may be a special value to never truncate (though it seems quite excessive to me and a possible footgun)
I was actually thinking in the other direction. So between the time VACUUM figures out it can possibly truncate last K pages, some backend may insert a tuple in some page and make the truncation impossible. What if we truncate the FSM before starting the backward scan so that new inserts go into the pages prior to the truncation point, if possible. That will increase the chances of VACUUM being able to truncate all the empty pages. Though I think in some cases it might lead to unnecessary further extension of the relation. May be we use some heuristic based on available free space in the table prior to the truncation point?
