PostgreSQL multixact members limit exceeded: a runbook for MultiXact wraparound and member space exhaustion

#Symptoms

• Writes fail with ERROR: multixact "members" limit exceeded, with a detail like "This command would create a multixact with N members, but the remaining space is only enough for M members."
• The log fills with WARNING: database "app" must be vacuumed before N more multixact members are used, or with the MultiXactId variant: database "app" must be vacuumed before N more MultiXactIds are used.
• Autovacuum workers run (to prevent wraparound) vacuums on many tables at once, even with low n_dead_tup.
• pg_multixact/members on the data volume is many GB and still growing.

Ordinary XID wraparound has its own post. This runbook covers the two MultiXact limits, which are separate and often missed by monitoring that only checks age(datfrozenxid).

#What a MultiXact is and why there are two limits

When more than one transaction locks the same row (SELECT ... FOR SHARE, FOR KEY SHARE, or a foreign key check against a parent row while someone updates it), PostgreSQL cannot put one XID in the row's xmax. It creates a MultiXactId that points to a list of members (XID plus lock mode) stored in pg_multixact/members.

There are two separate counters:

Each new MultiXact that adds a locker copies the existing members plus the new one. A hot parent row that is locked by hundreds of concurrent foreign key checks can use member space far faster than MultiXact IDs. That is how you hit the members limit while mxid_age still looks healthy.

Space is only freed when VACUUM freezes old MultiXacts out of every table and datminmxid moves forward in every database.

Warning

Once the members limit is reached, any statement that needs a new MultiXact fails. That includes ordinary inserts and updates on child tables with foreign keys. Treat it as an outage.

#Step 1: measure both limits

MultiXact age per database:

SELECT datname,
       mxid_age(datminmxid) AS mxid_age,
       age(datfrozenxid)    AS xid_age,
       current_setting('autovacuum_multixact_freeze_max_age')::bigint AS mx_freeze_max
FROM   pg_database
ORDER  BY mxid_age DESC;

Oldest tables by MultiXact age in the current database:

SELECT c.oid::regclass AS table_name,
       mxid_age(c.relminmxid) AS mxid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM   pg_class c
WHERE  c.relkind IN ('r', 'm', 't')
ORDER  BY mxid_age(c.relminmxid) DESC
LIMIT  20;

Member space has no SQL function on most versions. Measure the directory size on self-managed servers:

du -sh "$PGDATA/pg_multixact/members" "$PGDATA/pg_multixact/offsets"

Trend that size over days, not just once. Managed services usually expose this as a metric or not at all; on RDS and Aurora, watch the log warnings and mxid_age.

Read the result against the documented thresholds: when member storage passes roughly 50% of the maximum (about 10 GB), autovacuum starts aggressive anti-wraparound scans on all tables, oldest MultiXact age first. They run even if autovacuum is nominally disabled.

#Step 2: find what is holding the horizon back

VACUUM cannot freeze a MultiXact while any of its member transactions could still be running, or while something older holds the horizon. Check the usual suspects:

-- Oldest transactions and snapshots
SELECT pid, usename, state, backend_xid, backend_xmin,
       now() - xact_start AS xact_age, left(query, 80) AS query
FROM   pg_stat_activity
WHERE  backend_xmin IS NOT NULL OR backend_xid IS NOT NULL
ORDER  BY xact_start
LIMIT  10;

-- Prepared transactions that nobody committed
SELECT gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY prepared;

-- Replication slots pinning xmin
SELECT slot_name, slot_type, active, xmin, catalog_xmin
FROM   pg_replication_slots
WHERE  xmin IS NOT NULL OR catalog_xmin IS NOT NULL;

Also check hot_standby_feedback = on replicas with long queries; their backend_xmin shows up in pg_stat_replication.

#Step 3: freeze the oldest tables

Work through the tables from Step 1 in MultiXact-age order. VACUUM (FREEZE) forces freezing of every tuple and removes old MultiXact references:

SET maintenance_work_mem = '2GB';
SET vacuum_cost_delay = 0;          -- run at full speed for the emergency
VACUUM (FREEZE, VERBOSE) public.order_lines;

For very large tables on version 14 and later, skip index cleanup to finish faster; you will catch up on indexes later:

VACUUM (FREEZE, VERBOSE, INDEX_CLEANUP OFF, TRUNCATE OFF) public.order_lines;

To process a whole database in parallel from the shell:

vacuumdb --dbname=app --freeze --jobs=4 --min-mxid-age=100000000 --verbose

--min-mxid-age limits the run to tables at or above that MultiXact age, which keeps the effort on the tables that matter. Repeat for every database, including template1 and postgres; the cluster-wide minimum only advances when all of them are frozen.

Caution

Do not stop the cluster and run single-user mode as a first move. Single-user mode is only needed when PostgreSQL refuses all new MultiXacts and you cannot connect normally. A normal-mode VACUUM (FREEZE) is faster to start, can run in parallel and keeps the rest of the application up.

Once the oldest tables are frozen, confirm progress:

SELECT datname, mxid_age(datminmxid) FROM pg_database ORDER BY 2 DESC;

Then watch the pg_multixact/members size. Truncation happens after a checkpoint once the oldest needed MultiXact moves forward, so trigger one if you are in a hurry:

CHECKPOINT;

#Step 4: reduce MultiXact creation

Member space blows up because of the workload pattern, not because of autovacuum alone. Find the hot parent rows:

SELECT locktype, relation::regclass, mode, count(*)
FROM   pg_locks
WHERE  locktype IN ('tuple', 'transactionid', 'relation')
  AND  granted
GROUP  BY 1, 2, 3
ORDER  BY 4 DESC
LIMIT  20;

Common culprits and what to change:

• Many concurrent child inserts referencing one parent row (for example every order line referencing one status or tenant row). Each FK check takes FOR KEY SHARE on the parent. If the reference table is static, consider dropping the FK for that column and enforcing it differently, or make sure nothing updates the parent row during peak; an update while sharers exist forces new MultiXacts.
• Application SELECT ... FOR SHARE used as a soft lock. Replace with FOR KEY SHARE if only the key must stay stable, or with advisory locks.
• Long transactions that keep many lockers alive. Shorten them.

#Prevent it next time

• Alert on mxid_age(datminmxid) at 50% and 75% of autovacuum_multixact_freeze_max_age, next to your XID age alert.
• On self-managed servers, alert on pg_multixact/members size; anything above 5 GB deserves a look before autovacuum goes aggressive at about 10 GB.
• Tune autovacuum so it keeps up: raise autovacuum_vacuum_cost_limit, add workers, and set per-table autovacuum_multixact_freeze_max_age lower on known hot tables:

ALTER TABLE public.order_lines SET (autovacuum_multixact_freeze_max_age = 150000000);

• Enforce idle_in_transaction_session_timeout and monitor pg_prepared_xacts and inactive slots.
• Schedule a quarterly vacuumdb --freeze --min-mxid-age=... during low traffic on large, rarely updated tables.
• Read the release notes when you upgrade: newer major releases change how member offsets are stored, which reduces member-driven aggressive vacuuming. VACUUM still has to reclaim the space, so keep the alerts.