Monitoring The PostgreSQL Buffer Cache

Talk Type: 
45 Minute Talk
Technical Level: 
Creative Commons - Attribution Only

When you give your database server memory, you expect it's going to use it. But for what? A look inside PostgreSQL's buffer cache can tell you exactly what that memory is doing for you. Every systematic database tuning effort should include a look at this critical resource. When it comes to optimization work, profiling beats guessing every time. And understanding the buffer cache lets you tune complicated parts of the database like checkpoint writes in a much more robust way.

PostgreSQL keeps most of its working data inside a block of shared memory allocated when the server starts, used for caching disk reads and writes. Looking at the contents of that cache can give you valuable clues to how your database application really works. The best ways to handle many types of optimization tasks involve carefully measuring the variables you're changing, but most people change the size of this cache without any plan for measuring the impact. Information about data moving in and out of the cache is useful for performance tuning, query optimization, system monitoring, and even predicting the future requirements of your system.

This presentation aims to describe the basics of how the cache is organized, how to query its contents, and how to interpret the results of those queries. By monitoring what goes in and out of the cache, you get a unique window into what's really happening inside your database when it's running your application.