staffCloud Postgres platformPro answerTested in a controlled lab, with the output captured

Give me a multixact scenario and take it deep, when does one get created, and how does it bite you in production?

This is a depth probe, not a definition check. The interviewer already assumes you can say the words shared row lock; what they are testing is whether you know why a second locker forces PostgreSQL to change representation, and whether you know that the structure it switches to carries its own wraparound clock. The production half of the question is the part that separates readers from operators, because the most common source of multixacts in real systems is not an explicit lock at all, it is ordinary foreign-key traffic against a popular parent row. This page covers a ninety-second version that stays concrete, the follow-ups that test whether you really understand the trigger, and the monitoring point worth naming.

What the interviewer is scoring

Score points for foreign-key key-share locks as the sneaky source, for multixacts having their own freeze and wraparound clock, and for naming member-space exhaustion rather than reciting lock syntax.

In short: They want to hear that a multixact appears when several transactions lock the SAME row in a way a single transaction id can't represent, that it lives in its own 32-bit space with its own wraparound, and the real failure modes.

ProFrom a real interview loop

The spoken answer, backed by captured output

The question, the intro, what the interviewer is scoring, the reasoning outline, what you would verify and the follow-up probes are all open above. Pro unlocks the spoken answer to “Give me a multixact scenario and take it deep, when does one get created, and how does it bite you in production?”, the reasoning behind it, and the verification that backs it.
  • The full 90-second answer, written first person, the way you would actually say it
  • How I reason through it: the mechanism, the decision points, and where the claim stops
  • Verification steps with output captured verbatim from a controlled PostgreSQL 18 lab run.
  • Worked responses to the 3 follow-up probes listed above, plus the traps that lose the point

Card required. Cancel before day 7 and you are not charged.

Compare plans

How to reason through it

  • Define the trigger: more than one locker on one row at the same time, not concurrency in general.
  • Explain the mechanism: the row's xmax stops being a single transaction id and becomes a multixact id pointing at a member list.
  • Stress the twist: multixacts have their OWN 32-bit space, wraparound, and freezing.
  • Name the sneaky source: foreign-key checks take a key-share lock on the parent row.
  • Name the failure modes: cache contention on hot rows, member-space exhaustion halting writes, surprise anti-wraparound vacuums.

What I would verify

  • Read the row's xmax and check whether it resolves to a transaction or to a multixact member list.
  • Compare multixact age against ordinary transaction age; they move independently.
  • Look for a hot parent row with heavy child insert traffic before blaming explicit locking.
  • Separate member-space growth from cache contention; they present similarly and have different fixes.

Follow-ups they push on

  • Does a single FOR KEY SHARE lock create a multixact?
  • How would you reduce multixact churn without changing the schema?
  • What actually stops writes in the worst case?

The probes are open. Pro carries the spoken answer, the reasoning behind it, and the verification steps, including a worked response to each of these.

Concepts tested

MultiXactSELECT FOR SHAREFK KEY SHARE locksMultiXact wraparound

Learn it, run it, then say it

Three steps, in order. Nothing here is a detour.

Questions that go with this one