Collection of Demonstrations with PostgreSQL for Teaching and Learning Database System Internals
Relational database systems implement fundamental concepts, such as the relational model, physical design independence, query optimization, and transaction processing. However, teaching and learning these concepts– alongside system design and implementation– can be challenging as database systems are among the most complex software systems, particularly when aiming for a practical and hands-on educational approach. A powerful way to bridge this gap is through demonstrations that showcase database internals using actual systems. PostgreSQL, in particular, provides extensive capabilities for inspecting internal structures and behavior, making it an ideal system for such demonstrations. In this paper, we present a comprehensive collection of demonstrations designed to support the teaching and learning of database system internals using PostgreSQL. These demonstrations cover concepts from database storage representation (e.g., file organization, page structure, and tuple representation) and query processing (e.g., cardinality estimation based on statistics and transaction processing). In addition, we present a novel application for visually inspecting the structure of PostgreSQL data pages and the effects of data modifications. Finally, we discuss how we use the demonstrations in our courses at TU Berlin. Our paper, including the accompanying online artifacts, builds a novel collection of hands-on demonstration proposals for teaching and learning database system internals
