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 <alvherre@xxxxxxxxxxxxxx> 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
