PGCon2019 - 3.4

PGCon 2019
The PostgreSQL Conference

Kuntal Ghosh
Day Talks - Day 2 - 2019-05-31
Room DMS 1160
Start time 16:00
Duration 00:45
ID 1332
Event type Lecture
Track Hacking
Language used for presentation English

Tuple Locking redesigned

In PostgreSQL, tuple locking is an important mechanism to maintain consistency in database. However, the current tuple locking system (also known as MultiXact locking system) comes with a few limitations, such as each new locker on a tuple combines the existing lockers along with itself and creates a new MultiXact entry in pgmultixact relation, thus increasing the pgmultixact relation size. Additionally, a 32 bit counter MultiXact Id is maintained for each MultiXact entry which requires efficient aging management, storage cleanup, and wraparound handling. In EnterpriseDB, as a part of new storage engine namely 'zheap', we redesigned the multi-locker mechanism to reduce the effects of afore-mentioned limitations.

Since zheap is an undo-based storage, it stores the locking information of each locker in the separate undo records. Any transaction that intends to lock a tuple, it traverses all undo records corresponding to the page where the tuple is stored and collects all the lockers. Since, undo records are eventually discarded, 'zheap' multi-locker mechanism doesn't need any separate storage cleanup management. In this talk, we shall present the detailed design of locking mechanism in zheap and discuss how we've avoided a few limitations of current MultiXact system guided by some case studies.