Skip to Content
Home └Database System Internals (PostgreSQL) └Project 2: Forensic Analysis of MVCC and Vacuuming

Database System Internals (PostgreSQL)

0 %

Completed

Course content

Project 2: Forensic Analysis of MVCC and Vacuuming

Prev Next
Fullscreen Share

Project 2: Forensic Analysis of MVCC and Vacuuming

PostgreSQL's Multiversion Concurrency Control (MVCC) provides excellent concurrent performance but requires diligent maintenance to prevent physical storage bloat. In this project, you will deliberately cause and then resolve massive table bloat.

Create a test database and use a pgbench custom script (provided) to simulate an intense Online Transaction Processing (OLTP) workload consisting of rapid, continuous UPDATE and DELETE statements on a single table.

Using the pageinspect extension, you will inspect the physical 8KB pages on disk. You must capture and explain the state of the tuple headers (xmin, xmax, ctid) to prove the existence of "dead" tuples. Calculate the exact bloat percentage of your table and its indexes. Next, you will design a custom autovacuum strategy specifically for this table by tuning parameters like autovacuum_vacuum_scale_factor to aggressively reclaim space. You must demonstrate the resolution of the bloat and explain how the Free Space Map (FSM) was updated.

Rubric:
CriteriaExcellentProficientNeeds Improvement
MVCC Mechanics ApplicationPerfectly explains row visibility and the lifecycle of a tuple using xmin, xmax, and snapshot isolation concepts.Explains xmin/xmax generally but struggles to explain edge cases or concurrent visibility.Misunderstands how PostgreSQL determines if a row is dead or alive.
Physical Page InspectionSuccessfully uses pageinspect to map physical page layouts and accurately calculates exact bloat metrics.Uses pageinspect but relies on generic queries rather than calculating specific byte-level bloat.Unable to successfully use the extension or interpret the binary page data.
Autovacuum TuningSets highly optimized, table-specific autovacuum parameters and proves their mathematical efficiency.Alters autovacuum settings globally or uses arbitrary numbers without mathematical justification.Fails to trigger autovacuum effectively or relies exclusively on manual VACUUM FULL.
Documentation of FSMClearly demonstrates how the Free Space Map changes before and after the vacuuming process.Mentions the FSM but does not provide empirical evidence of its changes.Ignores the Free Space Map entirely.
Rating
0 0

There are no comments for now.

Join this Course
to be the first to leave a comment.