Will the allocation of transaction IDs be exhausted or wraparound?

In a database management system, a transaction is a single unit of logic or work, sometimes made up of multiple operations. During database operation, each transaction needs to be assigned a unique transaction ID. So, will the allocation of transaction IDs be exhausted or wraparound?

The allocation of transaction IDs will not be exhausted or wraparound.

In Redrock Postgres, a transaction ID represents a transaction and the data type xid stores information about the undo segment number, transaction slot number, and sequence number. Both the undo segment number and transaction slot number are 2 bytes long, and the sequence number is 4 bytes long. The number of transactions that an undo segment supports depends on the number of transaction slots in the undo segment. The first page of the undo segment is used to store transaction items, and a single undo segment can hold up to about 200 transaction items at the same time. In addition, when the sequence number in the transaction item overflows, the 2-byte field used to represent the transaction slot number can also store the overflow part of the sequence number.

After a database instance is initialized, 8 database level undo segments are generated in the database by default. So the default number of transaction IDs available for a database is: \(8 * 2^{16} * 2^{32}\). Assuming that the database processes 1 million transactions per second, the database transaction ID allocation cycle can reach 71 years.

The available time of transaction IDs is close to the average human lifespan.