No video of the event yet, sorry!

Where is My Memory? (Postgres Edition)

Speakers: Mohit Agarwal, Pushkar Khadilkar

This talk delves into the intricate world of PostgreSQL memory management, exploring the various components that contribute to its memory footprint and offering insights into advanced optimization techniques. The session will empower developers and DBAs to gain a comprehensive understanding of where PostgreSQL allocates and utilizes memory, enabling them to build more efficient and performant database systems.

We will start with an overview of PostgreSQL's key memory components before diving into the next-generation tools essential for analyzing its performance.

1. Shared Buffers: This is the primary disk cache where PostgreSQL holds copies of table and index pages in RAM to speed up data access. We'll explore the pg_buffercache extension, a vital tool for inspecting the cache's contents and identifying which relations are consuming the most memory.
2. Memory Contexts: This section delves into PostgreSQL's hierarchical memory allocation system. We will explore different types of memory contexts, observe how they expand to meet query demands. We will also cover the basics of using custom memory contexts for developing leak-free extensions.
3. Process's Private Memory: Each backend process allocates its own memory. We will discuss "Connection Bloat," where numerous idle connections can lead to substantial memory use, and address the complexities that arise when extensions introduce external libraries with their own memory allocators.
4. Per-Query Memory: We will also examine the work_mem and maintenance_work_mem parameters and their direct impact on the performance of individual queries.

Advanced Tools: While SQL queries and standard Linux commands are a good starting point, we will focus on advanced tools that offer deeper insights. We will demonstrate how to use eBPF/BCC tools like memleak to trace memory allocations in real-time, pinpointing the exact code responsible for memory growth, and then visualize this data with Flame Graphs.

Attendees will leave this session with a clear map of PostgreSQL's memory components, from shared buffers to private process memory. They will also be equipped with the skills to use advanced diagnostic tools like eBPF, pprof, and Flame Graphs to pinpoint and resolve complex memory performance issues.