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.
we have a Postgresql v14.8 database, almost thousands of backends hang on MultiXactOffsetSLRU at the same time, all of these sessions running same query "SELECT ....", from OS and postgresql slow log, we found all of these query on "BIND" stage.
LOG: duration: 36631.688 ms bind S_813: SELECT LOG: duration: 36859.786 ms bind S_1111: SELECT LOG: duration: 35868.148 ms bind <unnamed>: SELECT LOG: duration: 36906.471 ms bind <unnamed>: SELECT LOG: duration: 35955.489 ms bind <unnamed>: SELECT LOG: duration: 36833.510 ms bind <unnamed>: SELECT LOG: duration: 36839.535 ms bind S_1219: SELECT
...
this database hang on MultiXactOffsetSLRU and MultiXactOffsetBuffer long time.
could you direct me why they are hanging on 'BIND‘ stage with MultiXactOffsetSLRU ?
