Multixact, and multixact wraparound

A multixact is the identifier PostgreSQL issues when more than one transaction holds a lock on the same row at the same time. The row cannot store two locker ids in the space it has for one, so the server allocates a multixact, writes the member list to disk, and stores the multixact id in the row instead. Multixacts have their own counter, their own per-table age in pg_class.relminmxid, and their own wraparound, entirely separate from transaction ids.

Two counters, two thresholds, one confusing log line

Almost every treatment of wraparound describes the transaction id counter and stops. That leaves operators with one mental model and two things that can exhaust, and the server’s own messages do not always make clear which one is speaking. autovacuum_freeze_max_age governs transaction ids; autovacuum_multixact_freeze_max_age governs multixacts; a table can sit comfortably under the first and be forced into an anti-wraparound pass by the second.

The workloads that reach the multixact limit are recognisable. Heavy use of foreign keys creates row-share locks on parent rows, and a busy parent row locked by many children at once becomes a multixact that gets replaced each time the membership changes. So does SELECT ... FOR SHARE in an application that holds a row while it validates something. The distinguishing symptom is disk: the member file grows out of proportion to everything else, and the offsets cache starts missing, which shows up as a wait event rather than as a vacuum complaint.

The other half of the confusion is that a multixact needs two transactions only in the sense that it needs two lockers holding incompatible modes. A transaction and its own subtransaction count as two, which is why an application that wraps every statement in a savepoint can allocate them at a rate nobody has budgeted for and without any concurrency at all.

Making one, and finding the counter

A savepoint is enough to allocate a multixact from a single session, which is the cheapest way to see the counter move.

CREATE TABLE seat (id int PRIMARY KEY, holder text);
INSERT INTO seat VALUES (1, 'a');
SELECT next_multixact_id FROM pg_control_checkpoint();
BEGIN;
SELECT id FROM seat WHERE id = 1 FOR KEY SHARE;
SAVEPOINT s1;
SELECT id FROM seat WHERE id = 1 FOR UPDATE;
COMMIT;
CHECKPOINT;
SELECT next_multixact_id, mxid_age(relminmxid) AS multixact_age
FROM pg_control_checkpoint(), pg_class WHERE relname = 'seat';

CREATE TABLE
INSERT 0 1
 next_multixact_id 
-------------------
                 1
(1 row)

BEGIN
 id 
----
  1
(1 row)

SAVEPOINT
 id 
----
  1
(1 row)

COMMIT
CHECKPOINT
 next_multixact_id | multixact_age 
-------------------+---------------
                 2 |             1
(1 row)

One session, one savepoint, two lock modes, and the counter has advanced. The CHECKPOINT is there because pg_control_checkpoint() reports the values as of the last checkpoint rather than as of now, and running one by hand is itself a requested checkpoint, which is worth knowing before you run this on something you are also graphing. mxid_age is the multixact equivalent of age, and it is the number to alert on.

What to do when the age climbs

Multixact freezing is part of the same machinery as transaction id freezing and it is tuned with the same instincts, so the guide on transaction id wraparound is where the thresholds, the failsafe and the emergency procedure live. Read it with the multixact settings substituted for the transaction id ones; the arithmetic is the same and the workload that causes it is not.