在 Postgres 中将多XactOffset 移动到 64 比特

Introduction One morning, while going through the latest batch of Postgres commits, I saw this: commit bd8d9c9bdfa0c2168bb37edca6fa88168cacbbaa Author: Heikki Linnakangas [email protected] Date: Tue Dec 9 13:53:03 2025 +0200 Just like that, quietly and almost routinely, Postgres moved past one of its annoying limits: the cap on the number of transactions in a multitransaction is now history. Formally, yes, it is now limited by an 8-byte unsigned integer, but that number is so massive that I can’t imagine it being exhausted anytime in the foreseeable future. When you spend years working on something and it finally gets done, it’s hard to believe it’s really over. There was always a small chance the community could still roll the commit back. Now that almost three months have passed (and the commit seems to have settled in), I want to share my thoughts as a direct participant in these events and the patch author. Three juggling brothers In Postgres, there are three bottlenecks tied to 32-bit counters: transaction identifiers, also known as xid or “xids”; multitransaction identifiers, also known as mxid; multitransaction offsets. Users rarely notice this one, but under the wrong conditions, it can become quite nasty. More on that later. It’s worth noting that each of these counters can “wrap around,” meaning they handle overflow normally, and this does not crash the database or cause data loss. Depending on your workload and database size, you might not even notice that, say, after 4 billion, the transaction counter has become 1073. Any of these counters can become a problem, or not. Each one deserves a full article on its own. But the smaller the database and the lower the transactional load, the lower the chance of issues. That part is probably obvious anyway. The multitransaction mechanism In his book PostgreSQL 17 Internals, Egor Rogov defines it like this: a multitransaction is a group of transactions assigned a separate number. By the way, the book is freely available, no signup, no OTP circus. I’d recommend it to anyone interested. It covers the Postgres multitransaction architecture in depth: what it is and why it exists. My focus is on how the multitransaction mechanism is implemented. So at my level of abstraction, a multitransaction is a container that can hold an arbitrary number of real transaction identifiers blocking a write. First, it is explicitly used when calling constructs like SELECT... FOR SHARE, FOR UPDATE. Second, it is implicitly used to maintain foreign key integrity, so users rely on it much more often than they think. Let’s see how this works. When multiple transactions lock the same row, Postgres cannot store all those transaction IDs in a single tuple header field: the t_xmax field is only 4 bytes, so it can hold just one ID. If multiple locking transactions must be stored, the t_xmax field is treated as a multitransaction identifier (inside Postgres, this is the MultiXactId type), and that identifier points to an internal data structure that stores an array of real transactions. Here is a small excerpt from the source code: HeapTupleFields is the tuple header structure. The fields we care about are t_xmax and t_infomask. If the HEAP_XMAX_IS_MULTI flag is set in t_infomask, then the value stored in t_xmax must be treated not as a transaction ID, but as a multitransaction ID, i.e., a container that holds the IDs of real transactions locking this row. Multitransaction data is stored in the $PGDATA/pg_multixact directory. To write multitransaction data to disk (as I said, our goal is to persist an array of real transactions), Postgres uses a two-level storage scheme: $PGDATA/pg_multixact/offsets stores the offset in members that marks the start of the array of real transactions for a multitransaction. $PGDATA/pg_multixact/members stores the transactions themselves. In practice, it stores a MultiXactMember structure, but that is not important for us right now. To write a new multitransaction ID, we call GetNewMultiXactId So, in short: A multitransaction is an arbitrary-length container that stores the IDs of real transactions that have locked a row. The multitransaction ID is stored directly in the row header and is 4 bytes in size. Multitransactions themselves are stored as SLRU segments and consist of two parts. The first part is called offsets and shows the offset into the second part, members, where the real transaction IDs are stored. Offsets have no semantic meaning outside the storage mechanism; they only point to a position in the file. This is, frankly, a non-trivial way to store a variable-length array. Could a different scheme have been chosen? I think yes, but discussing that now is not very useful. This is the reality we have to work with. Problem Why can this become a problem? Sure, the multitransaction storage scheme is not the easiest, but it should still be enough. At any given moment, Postgres cannot run transactions that are more than 231 apart from each other. Yes, but... it is not that simple. A very interesting analysis for anyone working with Postrges administration, so it is definitely worth reading. Multitransactions have two unpleasant characteristics. The first is immutability: once a multitransaction is created, we cannot add new transactions to it. Each time, a new multitransaction is created with its own set of values. The second characteristic is the quadratic growth of offsets when the same row is repeatedly locked. Let's see how a row gets locked sequentially by transactions T₁, T₂, T₃, and T₄. Execute T₁: mxact = [T1]; Total 1. Execute T₂: mxact = [T1, T2]; Total 3. Execute T₃: mxact = [T1, T2, T3]; Total 6. Execute T₄: mxact = [T1, T2, T3, T4]; Total 10. Interestingly, this is the sequence of triangular numbers. As correctly noted in the incident description, these issues are made worse by the lack of a standard way to monitor offset overflow, limited documentation, and unclear error text. Solution: switch to 64 bits After commit bd8d9c9bdfa0c2168bb37edca6 was accepted, Postgres started using 8-byte offsets, which made the overflow issue largely hypothetical. In practice, the overflow risk disappeared. Backward compatibility is preserved. As I wrote above, offsets have no semantic meaning outside the storage mechanism: they only point to a position in the file. In other words, these are just implementation details that leaked “outside”. During the next upgrade, the pg_upgarde utility will perform all required transformations, and the new DB cluster will run on 64-bit offsets. Any technical solution involves trade-offs. What had to be sacrificed to move to 64-bit offsets? First, this is about the size of offset segments. It has doubled. But we should keep in mind that this is not user data; it is part of the system’s internal machinery, and the size of the $PGDATA/pg_multixact directory directly depends on your data model and workload type. Still, I can hardly imagine this becoming a real-world problem. And even if I’m wrong, there is still a way out. Offsets are stored sequentially on the page (even though they don’t land there in order), so compression can be applied: write offsets in groups, assign a shared “base” for each group, and store the rest as deltas relative to that base. The only open question is the group size. We did not implement this right now because it reduces overall system reliability. If even one byte on a page is lost (and, reminder: SLRU pages do not store checksums), we risk losing not just one multitransaction but the whole group. The second compromise is that, as I wrote above, the $PGDATA/pg_multixact format is binary-incompatible with previous versions, which means conversion is required during upgrade. This is handled by the pg_upgarde utility, and it now has an additional step. Overall, I believe database cluster reliability and ease of administration matter more than this relatively small increase in internal metadata storage across the whole cluster. A