Designing a monitoring feature in PostgreSQL

This talk will provide a detailed explanation of designing, implementing, and evaluating a new monitoring feature that delivers dynamic, on-demand statistics in core PostgreSQL. I will describe the monitoring subsystem in PostgreSQL, focusing on the different types of statistics systems such as cumulative and dynamic.

I will then demonstrate how to design and implement a monitoring feature for reporting memory context statistics. This includes discussing how and when to collect statistics, updating shared memory, and defining SQL views for the collected data. I will explore the use of dynamic shared memory for managing non-fixed-amount statistics, with a comparison between DSAs and DSMs. I will illustrate methods for minimizing the overhead associated with collecting such statistics, highlighting trade-offs such as response time of the monitoring query versus its memory consumption. Finally, I will describe the iterative journey of designing this system, examining various design iterations and the pros and cons of each approach. I will describe how to encapsulate certain functionalities, such as sending a request to copy statistics to a process, waiting for new statistics into interfaces. This approach aims to simplify adding similar on-demand statistics in the future.