Redrock Postgres maintains records of the actions of transactions, collectively known as undo data. Redrock Postgres uses undo data to do the following:
- Roll back an active transaction
- Recover a terminated transaction
- Provide read consistency
Redrock Postgres stores undo data inside the database rather than in external logs. Undo data is stored in blocks that are updated just like data blocks, with changes to these blocks generating redo. In this way, Redrock Postgres can efficiently access undo data without needing to read external logs.
Undo data is stored in an independent tablespace. Redrock Postgres provides a fully automated mechanism, known as automatic undo segment management mode, for managing undo segments and space.
When a transaction starts, the database binds (assigns) the transaction to an undo segment, and therefore to a transaction table.
Multiple active transactions can write concurrently to the same undo or to different undo segments. For example, transactions T1 and T2 can both write to undo segment U1, or T1 can write to U1 while T2 writes to undo segment U2.
Conceptually, the extents in an undo segment form a ring. Transactions write to one undo extent, and then to the next extent in the ring, and so on in cyclical fashion. Figure 1 shows two transactions, T1 and T2, which begin writing in the third extent (E3) of an undo segment and continue writing to the fourth extent (E4).
At any given time, a transaction writes sequentially to only one extent in an undo segment, known as the current extent for the transaction. Multiple active transactions can write simultaneously to the same current extent or to different current extents. Figure 2 shows transactions T1 and T2 writing simultaneously to extent E3. Within an undo extent, a data block contains data for only one transaction.
As the current undo extent fills, the first transaction needing space checks the availability of the next allocated extent in the ring. If the next extent does not contain data from an active transaction, then this extent becomes the current extent. Now all transactions that need space can write to the new current extent. In Figure 2, when E4 is full, T1 and T2 continue writing to E1, overwriting the nonactive undo data in E1.
If the next extent does contain data from an active transaction, then the database must allocate a new extent. Figure 3 shows a scenario in which T1 and T2 are writing to E4. When E4 fills up, the transactions cannot continue writing to E1 because E1 contains active undo entries. Therefore, the database allocates a new extent (E5) for this undo segment. The transactions continue writing to E5.
ROLLBACK statement is issued, the database uses undo records to roll back changes made to the database by the uncommitted transaction. During recovery, the database rolls back any uncommitted changes applied from the online redo log to the data files. Undo records provide read consistency by maintaining the before image of the data for users accessing data at the same time that another user is changing it.
After the database instance data directory is initialized, 4 instance-level undo segments and 8 database-level undo segments are generated by default in the database, the instance-level undo segments is mainly used to modify the data of the global system catalogs, such as pg_database. The default number of undo segments is selected while initializing your PostgreSQL database instance using
initdb. For example,
initdb -u 20
sets the default number of undo segments to 20, includes 4 instance-level undo segments and 16 database-level undo segments. You can use
--undos instead of
-u if you prefer longer option strings.
undo segments are created with the CREATE UNDO command and destroyed with the DROP UNDO command. To retrieve Information about existing undo segments in the current database, examine the pg_undo system catalog, for example
SELECT c.relname, c.relisshared, u.undoid, u.undrelid, pg_size_pretty(pg_relation_size(c.oid)) AS undsize FROM pg_undo u LEFT JOIN pg_class c ON u.undrelid = c.oid;
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. However, if a single undo segment processes a large number of transactions at the same time (for example, more than 8), a corresponding page access wait event may occur due to concurrent access violations of the transaction table. In this case, you can use the CREATE UNDO command to create more undo segments in the current database to improve the transaction processing performance of the system.