Just to add to this. Looking at the active queries in PostgreSQL there appear to be a bunch of commits that haven't finished. I don't know how it all fits together but it looks like the commits aren't finishing and transactions are staying open, so when I hit an open transaction all write operations for the DB hang until the transaction has finished.
postgres=# SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
pid | age | usename |
query
--------+-----------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
15767 | 00:01:58.30353 | mailman | SELECT member.id AS member_id, member._member_id AS member__member_id, member.role AS member_role, member.list_id AS member_list_id, member.moderation_action AS member_moderation_action, mem
ber.address_id AS member_address_id, member.preferences_id AS member_preferences_id, member.user_id AS member_user_id, member.bounce_score AS member_bounce_score, member.last_bounce_received AS member_last_bounce_received, member.la
st_warning_sent AS member_last_warning_sent, member.total_warnings_sent AS member_total_warnings_sent +
| | | FROM member JOIN mailinglist ON member.list_id = mailinglist.list_id JOIN preferences ON preferences.id = member.preferences_id
+
| | | WHERE mailinglist.process_bounces = true AND member.total_warnings_sent >= mailinglist.bounce_you_are_disabled_warnings AND preferences.delivery_status = 3
18033 | 00:05:28.255636 | mailman | DELETE FROM member WHERE member.id = 169331
15762 | 00:11:42.842237 | mailman | COMMIT
15740 | 00:11:59.12324 | mailman | COMMIT
15760 | 00:12:01.212976 | mailman | COMMIT
15757 | 00:12:01.542144 | mailman | COMMIT
15755 | 00:12:01.708328 | mailman | COMMIT
15754 | 00:12:01.804737 | mailman | COMMIT
15751 | 00:12:01.921644 | mailman | COMMIT
15745 | 00:12:02.72934 | mailman | COMMIT
15743 | 00:12:02.795885 | mailman | COMMIT
15742 | 00:12:02.948795 | mailman | COMMIT
15738 | 00:12:03.3471 | mailman | COMMIT
15664 | 00:12:08.480084 | mailman | COMMIT
(21 rows)
I've checked my production system and I can see the same COMMIT queries running. Some for 2 days (since the last restart).