This problem is more acute when the FK Table stores a small number of rows like types or codes. I think in those cases an enum type should be used instead of a column with a FK. Thanks.
On Thu, Sep 14, 2023 at 7:23 AM Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > On 2023-Sep-13, bruno da silva wrote: > > > I just had an outage on postgres 14 due to multixact members limit > exceeded. > > Sadly, that's not as uncommon as we would like. > > > So the documentation says "There is a separate storage area which holds > the > > list of members in each multixact, which also uses a 32-bit counter and > > which must also be managed." > > Right. > > > Questions: > > having a 32-bit counter on this separated storage means that there is a > > global limit of multixact IDs for a database OID? > > A global limit of multixact members (each multixact ID can have one or > more members), across the whole instance. It is a shared resource for > all databases in an instance. > > > Is there a way to monitor this storage limit or counter using any pg_stat > > table/view? > > Not at present. > > > are foreign keys a big source of multixact IDs so not recommended on > tables > > with a lot of data and a lot of churn? > > Well, ideally you shouldn't consider operating without foreign keys at > any rate, but yes, foreign keys are one of the most common causes of > multixacts being used, and removing FKs may mean a decrease in multixact > usage. (The other use case of multixact usage is tuples being locked > and updated with an intervening savepoint.) > > > We could have a mode that we can set on tables with little movement and > many incoming FKs, that tells the system something like "in this table, > deletes/updates are disallowed, so FKs don't need to lock rows". Or > maybe "in this table, deletes are disallowed and updates can only change > columns that aren't used by UNIQUE NOT NULL indexes, so FKs don't need > to lock rows". This might save a ton of multixact traffic. > > -- > Álvaro Herrera Breisgau, Deutschland — > https://www.EnterpriseDB.com/ > "Hay dos momentos en la vida de un hombre en los que no debería > especular: cuando puede permitírselo y cuando no puede" (Mark Twain) > -- Bruno da Silva
