Multixact wraparound monitoring

[bruno da silva]

Hello.

I just had an outage on postgres 14 due to multixact members limit exceeded.

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."

Questions:
having a 32-bit counter on this separated storage means that there is a
global limit of multixact IDs for a database OID?

Is there a way to monitor this storage limit or counter using any pg_stat
table/view?

are foreign keys a big source of multixact IDs so not recommended on tables
with a lot of data and a lot of churn?

Thanks

-- 
Bruno da Silva

Re: Multixact wraparound monitoring

[Wyatt Alt]

On Wed, Sep 13, 2023 at 8:29 AM bruno da silva  wrote:

>
> are foreign keys a big source of multixact IDs so not recommended on
> tables with a lot of data and a lot of churn?
>

I am curious to hear other answers or if anything has changed. I
experienced this problem a couple of times on PG 11. In each situation my
setup looked something like this:

create table small(id int primary key, data text);
create table large(id bigint primary key, small_id int references
small(id));

Table large is hundreds of GB or more and accepting heavy writes in batches
of 1K records, and every batch contains exactly one reference to each row
in small (every batch references the same 1K rows in small).

The only way I was able to get around problems with multixact member ID
wraparound was dropping the foreign key constraint.