In case this helps anyone else, I found a simple way to get a rough idea of what's going on, which is to run: select (select count(distinct virtualtransaction) from pg_locks) as tx_with_locks, (select count(*) from pg_stat_activity where state = 'active') as active_tx, (select count(*) from pg_locks where virtualtransaction = '-1/0') as summarized_locks; I disabled the part of my application that seemed to be causing problems with too many writes (a background cleanup task) and then triggered it from a separate process. I can see the number of transactions with locks climbing when it hits a problematic item, while the number of active transactions (of course) stays low. Mike On Fri, Apr 30, 2021 at 4:53 PM Mike Beachy <mbeachy@xxxxxxxxx> wrote: > > On Fri, Apr 30, 2021 at 7:12 AM Thomas Munro <thomas.munro@xxxxxxxxx> wrote: > > But do you have lots of short overlapping transactions so that there > > is never a moment where there are zero transactions running? > > Yeah, that almost certainly explains it. > > Thanks very much for the explanation about the summarized locks. > > > The number of SERIALIZABLEXACT objects is (max_connections + > > max_prepared_transactions) * 10. So, you could try increasing > > max_connections (without increasing the actual number of connections) > > to see if you can get to a point where you don't see these invalid > > virtual xids, and then maybe it'll be able to clean up locks more > > aggressively. > > Aha! I hadn't considered that some parameter besides > max_pred_locks_per_transaction would come into play. I'll give this a > shot. > > Thanks, > Mike
