Understanding the shared buffer

When there are thousands of users trying to read/write data to many different tables, reading from the directories/files (which we saw getting created when we installed PostgreSQL and created a database with a couple of tables) will result in a miserably non-scalable system. The reads and writes will result in searching for many files, opening these files, using fseek() for specific data records, locking, editing, and unlocking. To make this a lot more scalable and faster, the concept of shared buffers (memory area) is introduced. Now, the backend processes are no longer reading from the files and writing to the files, but dealing with buffers or RAM, with significant improvement in performance. The amount of memory to be allocated is decided by the shared_buffers parameter in postgresql.conf. This fixed-size block of shared memory is allocated when the server is started.

It's not this memory chunk alone that is responsible for improving the response times, but the OS cache also helps quite a bit by keeping a lot of data ready-to-serve. Together, these two caches result in a significant reduction in the actual number and volume of physical reads and writes. In addition to these two levels of caching, there could be a disk controller cache, disk drive cache, and so on. The bottom line is that these caches improve performance by reducing the physical I/O necessary.

There are also risks associated with huge caches, such as the spikes in I/O, when large volumes of data get flushed from the buffer to disk. For a detailed understanding of the risks and benefits of caches, disks, and physical and logical I/O tuning, PostgreSQL 9.0 High Performance, Gregory Smith, Packt Publishing, is recommended.

Let's just consider the possible routes a simple SELECT statement might take, considering the shared buffer cache and OS cache alone.

The first thing the process will check is whether the data it wants is available in the database buffer cache. If it is not available in the database buffer cache, a request goes to the OS to fetch the specific file/block(s). There is a chance that the OS cache already has the file/block(s) and passes it to the database cache. In both these cases, a physical I/O is avoided. It's only when the data is not present in either of these caches (or other caches), that a user initialized read/write will really result in a physical I/O. These three possibilities are shown in the following diagram:

It's evident that most of the user-driven data fetches and writes will happen via buffers. Exceptions to this would be databases where the buffer is minuscule compared to the data that is usually read and written. Even in cases where the number of transactions per second is very high, the physical I/O will be limited if these transactions are mostly working with the same datasets. It's only when different transactions are accessing data from really different areas in the file system that the issue of frequent buffer flushes to disk and reads from disk will occur.

Even in a scenario where a user makes a lot of changes to table data and issues a commit, it might not immediately result in writes to the underlying data files. This might just result in ensuring that Write Ahead Log (WAL) files are synchronized with the WAL buffer.

WAL forms a critical component in ensuring the Durability (D) and, to some extent, the Atomicity (A) of ACID properties of transactions. However, first let's continue with the buffer and see how user-changed data finally reaches the data files.

CREATE DATABASE test;
CREATE DATABASE mydb;

CREATE EXTENSION pg_buffercache;
CREATE EXTENSION

[postgres@MyCentOS ~]$ cd /usr/local/pgsql/share/extension
[postgres@MyCentOS extension]$ pwd
/usr/local/pgsql/share/extension
[postgres@MyCentOS extension]$ more pg_buffercache--1.0.sql
/* contrib/pg_buffercache/pg_buffercache--1.0.sql */

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pg_buffercache" to load this file. \quit

-- Register the function.
CREATE FUNCTION pg_buffercache_pages()
RETURNS SETOF RECORD
AS 'MODULE_PATHNAME', 'pg_buffercache_pages'
LANGUAGE C;

-- Create a view for convenient access.
CREATE VIEW pg_buffercache AS
 SELECT P.* FROM pg_buffercache_pages() AS P
 (bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid,
 relforknumber int2, relblocknumber int8, isdirty bool, usagecount int2);

-- Don't want these to be available to public.
REVOKE ALL ON FUNCTION pg_buffercache_pages() FROM PUBLIC;
REVOKE ALL ON pg_buffercache FROM PUBLIC;

[postgres@MyCentOS extension]$ psql -d test
Type "help" for help.

test=# SELECT DISTINCT reldatabase FROM pg_buffercache;
 reldatabase 
-------------
            
       12896
           0
       24741
(4 rows)

test=# \! oid2name
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  16440           mydb  pg_default
  12896       postgres  pg_default
  12891      template0  pg_default
      1      template1  pg_default
  24741           test  pg_default

SELECT
c.relname,
count(*) AS buffers
FROM pg_class c
JOIN pg_buffercache b
ON b.relfilenode=c.relfilenode
INNER JOIN pg_database d
ON (b.reldatabase=d.oid AND d.datname=current_database())
GROUP BY c.relname
ORDER BY 2 DESC;

              relname              | buffers 
-----------------------------------+---------
 pg_operator                       |      13
 pg_depend_reference_index         |      11
 pg_depend                         |       9

SELECT
c.relname,
count(*) AS buffers
FROM pg_class c
JOIN pg_buffercache b
ON b.relfilenode=c.relfilenode
JOIN pg_database d
ON (b.reldatabase=d.oid AND d.datname=current_database())
WHERE c.relname NOT LIKE 'pg%' GROUP BY c.relname
ORDER BY 2 DESC;
 relname | buffers 
---------+---------
(0 rows)

[postgres@MyCentOS ~]$ psql -d test
CREATE TABLE emp(id serial, first_name varchar(50));
INSERT INTO emp(first_name) VALUES('Jayadeva');
SELECT * FROM emp;
 id | first_name 
----+------------
  1 | Jayadeva
(1 row)

  relname   | buffers 
------------+---------
 emp_id_seq |       1
 emp        |       1

SELECT 
c.relname,
b.isdirty 
FROM pg_class c JOIN pg_buffercache b
ON b.relfilenode=c.relfilenode 
JOIN pg_database d
ON (b.reldatabase=d.oid AND d.datname=current_database())
WHERE c.relname not like 'pg%';
  relname   | isdirty 
------------+---------
 emp_id_seq | f
 emp        | f

UPDATE emp SET first_name ='Newname';
UPDATE 1

  relname   | isdirty 
------------+---------
 emp_id_seq | f
 emp        | t

CHECKPOINT;
CHECKPOINT

  relname   | isdirty 
------------+---------
 emp_id_seq | f
 emp        | f
(1 row)