I hadn't found a satisfactory explanation about the top limit related to SLRU, so this document will be useful. It's a nice development that the relevant limit has been increased in pg17; I hope I don't encounter a situation where this limit is exceeded in large systems.
There is no foreign keys, but there is one session who did transactions to tables with savepoints, one savepoints/per sql in same transaction. But sessions with query "SELECT “ do not use savepoints , just with a lot of sessions running same query and hang on MultiXact suddenly. even only one session doing DML with savepoints , and all other queries sessions can see this kind of "MultiXact" waiting ,right?
There is no foreign keys, but there are several sessions who did transactions to tables with savepoints, one savepoints/per sql in same transaction. But sessions with query "SELECT “ do not use savepoints , just with a lot of sessions running same query and hang on MultiXact suddenly.
> Hi, > > I encountered this in a project we migrated to PostgreSQL > before, and unfortunately, it’s a situation that completely degrades > performance. We identified the cause as savepoints being used excessively > and without control. Once they reduced the number of savepoints, the issue > was resolved. However, the documentation also mentions that it could be > caused by foreign keys.
Yeah, it's exactly the same problem; when it comes from savepoints the issue is pg_subtrans, and when foreign keys are involved, it is pg_multixact. Both of those use the SLRU subsystem, which was heavily modified in pg17 as I mentioned in my reply to James.
-- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "I think my standards have lowered enough that now I think 'good design' is when the page doesn't irritate the living f*ck out of me." (JWZ)
