Designing and implementing a monitoring feature in PostgreSQL (PGConf.dev 2025)

Описание: Presented by Rahila Syed at PGConf.dev 2025 (https://2025.pgconf.dev)

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 start by describing the monitoring subsystem in PostgreSQL, focusing on the different types of statistics it provides, such as cumulative and dynamic statistics systems.
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. Additionally, I will explore the use of dynamic shared memory for managing non-fixed-amount statistics, with a comparison between Dynamic Shared Areas (DSAs) and Dynamic Shared Memory (DSMs). I will illustrate methods for measuring and 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. While doing so, 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.

The talk will include details of the interface functions used for creating, utilizing, and destroying DSAs and DSMs, along with the underlying system calls that facilitate these operations. I will discuss the various signaling mechanisms employed for inter-process communication in PostgreSQL, particularly in the context of this monitoring feature for reporting memory context statistics. Furthermore, I will explain memory contexts, their types—public and user-defined—and the associated statistics counters. This will include a discussion on how these counters are collected and subsequently reported.

https://www.pgevents.ca/events/pgconf...

🎬 See more PGConf.dev 2025 videos at    • PGConf.dev 2025  

Connect with us:
LinkedIn:   / post  
X   / pgconfdev  
Mastodon: https://mastodon.social/@pgconfdev
Bluesky: https://bsky.app/profile/pgconf.dev
Web: https://pgconf.dev

#postgresql #PGConfDev