DEV Community

Raja Rakshak
Raja Rakshak

Posted on

1

Buffer Manager: PostgreSQL

The database management system (DBMS)'s performance is significantly impacted by the buffer manager in PostgreSQL's management of data transfers between shared memory and persistent storage. The buffer manager effectively manages data file pages, including tables and indexes, and keeps track of the metadata for the pages that are being stored.

Buffer Manager Structure:

  • A buffer pool, buffer descriptors, and a buffer table make up the buffer manager.
  • The relationships between buffer tags and IDs are stored in the buffer table.
  • Metadata about the pages that are kept in the buffer pool is saved in buffer descriptors.
  • Data file pages are kept in an array called the buffer pool.

Image description

Buffer Tag:

  • A specific buffer tag made up of the RelFileNode, fork number, and block number is given to each page in PostgreSQL.
  • A specific page within a relation can be identified using the buffer tag.

What Pages a Backend Process Reads

  • Backend processes make requests to the buffer manager with the appropriate buffer tag whenever they want to read a table or index page.
  • The slot that houses the requested page's buffer ID is returned by the buffer manager.
  • The buffer manager loads the page from persistent storage into a buffer pool slot and returns the buffer ID if the page is not already in the buffer pool.
  • The buffer ID can then be used by the backend process to view the appropriate page.

Image description

Page replacement algorithm:

The buffer manager chooses a victim page to replace itself when all buffer pool slots are filled and a new page needs to be loaded.
Page replacement in PostgreSQL uses the clock sweep technique, which is more straightforward and effective than the LRU algorithm used in previous iterations.

Flushing dirty pages

Eventually, dirty pages that have undergone modification but have not yet been flushed to storage must be written back.
Checkpointer and background writer are two background processes used by PostgreSQL to handle the flushing of dirty pages.

Buffer Manager Locks

For the objectives of synchronization and data integrity, the buffer manager uses a variety of locks.
When performing lookups, insertions, and deletions, the BufMappingLock is utilized to safeguard the buffer table's integrity.
The content_lock function, which can be acquired in shared or exclusive mode, is used to restrict access to the stored page.
To wait for I/O operations on a buffer to finish, use io_in_progress_lock.
For effective buffer descriptor field inspection and manipulation, spinlocks are used.

API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry 🕒

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (0)

Billboard image

Try REST API Generation for Snowflake

DevOps for Private APIs. Automate the building, securing, and documenting of internal/private REST APIs with built-in enterprise security on bare-metal, VMs, or containers.

  • Auto-generated live APIs mapped from Snowflake database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay