In PostgreSQL, how can MultiXact IDs wrap around independently of transaction IDs, and how do their limits and recovery differ?
answer
- more than one locker per row
- a second 32-bit counter
- members storage runs out too
- relminmxid and mxid_age()
- slots don't pin them
basics
~20 sMultiXact IDs, created when several transactions lock one row, are a separate 32-bit counter with their own member storage. VACUUM freezes them by relminmxid age; exhaustion blocks only writes needing a new MultiXact, and slots do not pin them.
solid answer
~40 sWhen more than one transaction locks a row at once, for example concurrent foreign-key checks taking `FOR KEY SHARE` on the same parent row, PostgreSQL stores a **MultiXact ID** in `xmax` and keeps the member XIDs in `pg_multixact`. MXIDs are a separate 32-bit counter, and the members area has its own 32-bit space. VACUUM replaces MXIDs older than `vacuum_multixact_freeze_min_age` (5 million); a `relminmxid` older than `vacuum_multixact_freeze_table_age` (150 million) makes VACUUM aggressive, and `autovacuum_multixact_freeze_max_age` (400 million) forces autovacuum. Members storage over about 10 GB triggers aggressive vacuums early. Warnings start at 40 million MXIDs left and new MXIDs stop at 3 million; measure with `mxid_age(relminmxid)` and `mxid_age(datminmxid)`. Recovery mirrors XID recovery, but slots are not a factor and only writes needing a new MXID fail.
code
sql · 14 lines-- Per database: MultiXact age next to XID age
SELECT datname,
mxid_age(datminmxid) AS mxid_age,
age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY mxid_age DESC;
-- Per relation in the current database, including TOAST tables
SELECT c.oid::regclass AS relation,
mxid_age(c.relminmxid) AS mxid_age
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY mxid_age DESC
LIMIT 10;go deeper
Know that PostgreSQL has a second ID counter, MultiXact IDs, used when several transactions lock one row, and that it can wrap around too.
Explain where MXIDs live (xmax plus pg_multixact), how relminmxid and mxid_age() measure them, and the 5-million, 150-million and 400-million settings.
Diagnose a MultiXact stop: why only lock-taking writes fail, why slots are not the culprit, how members storage triggers early vacuums, and how foreign keys on hot parent rows create MXIDs.
Decide whether schema or ingest design should avoid hot parent rows, weighing foreign-key integrity against MultiXact churn on very high-write tables.
## What a MultiXact is A PostgreSQL row header has a single `xmax` field. When one transaction locks or deletes a row, `xmax` holds its XID. When **more than one transaction** locks the same row at the same time, there is no room for all of them, so PostgreSQL allocates a **MultiXact ID (MXID)**, stores it in `xmax`, and records the member XIDs and their lock modes in the `pg_multixact` directory. Which lock modes exist and how they conflict belongs to the row-locking leaf; what matters here is that MXIDs, like XIDs, come from a finite counter that VACUUM must freeze. A common source in an IoT schema is the foreign key. Each `INSERT INTO telemetry.readings` checks its parent `telemetry.devices` row by taking a `FOR KEY SHARE` lock on it. When several sessions insert readings for the same device at the same moment, their locks overlap on one `devices` row, and the overlap is recorded as a MultiXact. A lone locker records a plain XID instead. ## Two spaces that can run out - **MultiXact IDs** are a separate 32-bit counter, compared in the same circular way as XIDs. - **Members storage**, the list of member XIDs for each MXID, uses its own 32-bit counter. Workloads with many lockers per row consume it faster than IDs. Once it exceeds about **10 GB**, aggressive vacuums run more often on all tables, oldest multixact age first, even with autovacuum nominally disabled; the members area reaches wraparound at about **20 GB**. ## The settings ladder | Setting | Default | Effect | |---|---|---| | `vacuum_multixact_freeze_min_age` | 5 million | VACUUM replaces older MXIDs in pages it scans with nothing, a single XID or a newer MXID | | `vacuum_multixact_freeze_table_age` | 150 million | a table whose `relminmxid` is older gets an aggressive vacuum | | `autovacuum_multixact_freeze_max_age` | 400 million | autovacuum forces an anti-wraparound vacuum; set only at server start | | `vacuum_multixact_failsafe_age` | 1.6 billion | VACUUM drops cost delay and skips index vacuuming | | warning and stop limits | 40 million and 3 million MXIDs left | warnings, then no new MXIDs | Aggressive vacuums are guaranteed to advance `relminmxid`; once every table in every database has moved past old MXIDs, the old `pg_multixact` segments can be removed. ## Measuring - **`mxid_age()`** is the MultiXact counterpart of `age()`: apply it to `pg_class.relminmxid` per table and `pg_database.datminmxid` per database. - **`pg_get_multixact_members(mxid)`** lists the XIDs and lock modes inside one MXID, useful when you have an `xmax` value in hand and want to know who held the lock. - **`vacuumdb --min-mxid-age`** vacuums only relations past a given MultiXact age, counting each table's TOAST relation, which is how to prioritise work during an incident. - MXIDs never appear in `pg_stat_activity`, so session views only help indirectly. ## How recovery differs from XID wraparound The stop message is `ERROR: database is not accepting commands that assign new MultiXactIds to avoid wraparound data loss in database "iot"`. Recovery follows the XID steps, clear what holds the horizon back and run a plain `VACUUM` as a superuser, with four differences listed in the manual: 1. **Running and prepared transactions** can be ignored if there is no chance they appear in a multixact. 2. **Replication slots do not directly hold back** multixact cleanup, so dropping stale slots is usually not relevant. 3. **MXID information is not visible in views such as `pg_stat_activity`**; looking for old XIDs there is still the practical way to find the transactions involved. 4. **Only some writes fail.** XID exhaustion blocks every write, but MXID exhaustion blocks only writes that need a row lock requiring a new MXID. In the IoT case that is the inserts whose foreign-key check finds another locker already on the parent row, so ingestion stalls while unrelated updates may still succeed. ## Prevention - Monitor `mxid_age(datminmxid)` beside `age(datfrozenxid)`; dashboards that track only XIDs miss this failure entirely. - Watch the size of `pg_multixact`: members growth can force aggressive vacuums before the IDs look old. - Reduce needless lock overlap, for example by grouping each device's readings into one transaction so fewer concurrent lockers meet on the same parent row. - Keep the forced-vacuum path healthy for MXIDs exactly as for XIDs: long transactions that hold old members alive delay everything.
- Why can PostgreSQL MultiXact members storage force vacuums before mxid_age looks dangerous?Each MultiXact stores a list of member XIDs in a separate area with its own 32-bit counter. Rows with many simultaneous lockers consume members much faster than IDs. Once members storage passes about 10 GB, PostgreSQL runs aggressive vacuums more often on all tables, oldest multixact age first, even with autovacuum nominally off; members wrap at about 20 GB.
- How do you see which transactions belong to a given PostgreSQL MultiXact ID?`pg_get_multixact_members(mxid)` returns one row per member with its XID and lock mode. MXIDs do not appear in `pg_stat_activity`, so during an incident the practical route is to look for old XIDs in `pg_stat_activity` and `pg_prepared_xacts`, because an old member transaction is what keeps old MultiXacts needed.
- Dropping stale replication slots is the key step in MultiXact wraparound recovery.
- MultiXact IDs are drawn from the same counter as transaction IDs.
- MultiXact exhaustion stops every write transaction in the database.
- MultiXacts only come from explicit SELECT ... FOR UPDATE in application code.
- autovacuum_multixact_freeze_max_age defaults to 200 million, like the XID setting.