"Jim Hurne" <jhurne@us.ibm.com> wrote on 06/24/2020 03:44:26 PM:
> That brings us to transactions. I looked for any long-running idle
> transaction (transactions that have been idle for more than 15 or 5
> minutes), but found none.
It turns out I didn't see any long running transactions because I was
using a user who didn't have permissions to view most of the data in the
pg_stat_activity view. When we looked using a more powerful user, we
found four transactions that had been idle for a long time. Killing those
transactions allowed vacuums to clean up the dead tuples.
This is most likely the root of all of our problems, so we can essentially
consider this mystery solved for now.
Thanks to all who gave us advice and information. It's been highly
educational. I'm just sorry it turned out to be a stupid "long running
transaction" problem. We'll know to look for that first in the future.