在資料庫系統中為了能夠提高并發處理并加快資料處理過程，shared buffer的概念也應用而生。
前言
通常，在任何資料庫系統中，都需要面臨海量使用者對數資料庫進行讀寫操作。而對資料庫中的資料最直接的讀寫操作是讀取資料庫中對應的資料目錄中的檔案。這些檔案裡面包含了資料庫的任何對象，如表、視圖、索引、序列、函數等。PostgreSQL 也不例外，在對資料庫初始化時，我們能夠看到由PostgreSQL 服務端工具 initdb 建立的相關表和目錄，但在一個不具有彈性伸縮的資料庫系統中，海量使用者對資料庫的讀寫操作并不是很理想。因為對資料庫的資料對象進行讀寫操作需要資料庫系統查找相關檔案，打開檔案，然後通過相關的函數對這些對象進行鎖定，編輯和釋放鎖。是以在資料庫系統中為了能夠提高并發處理并加快資料處理過程，shared buffer的概念也應用而生。有了shared buffer ，背景程序從使用者接收到請求後将不會再直接去讀取資料庫對象檔案和寫入資料庫對象檔案，而是通過在 RAM 中的buffer 中處理資料，進而極大地提高了資料庫性能。
一、shared buffer
shared buffer 記憶體大小通過參數 shared_buffers 參數來進行設定。改參數之在PostgreSQL 中預設值為 128MB，初始化大小可以通過 initdb 來進行設定，但是不少于128KB。如果在對該參數不進行設定，那麼該值的大小以塊位元組為機關，即設定為整數的位元組大小。同時該參數變動後，需要重新啟動資料庫伺服器。在資料庫伺服器上，如果RAM大小超過1GB，該值的大小通常為系統RAM的25%。在進行讀寫頻繁的資料庫環境中，該參數可以調整到大于25%的值，但不建議将該值設定太高，如果設定太高，需要同時增加max_wal_size 參數的大小，否則會造成系統性能下降。
該原理并不是通過記憶體 chunk 克隆的方式來提高系統響應時間，而是 OS 中的 RAM 将大量資料駐留在其中，以保證資料随時可用來提高系統響應時間。當然，除了緩存之外，還有磁盤緩存也可以提高資料處理性能，但原理都是通過減少不必要的實體 I/O 來提高性能。
在一個正常的簡單查詢中，系統第一步将會檢查在 buffer cache 中是否有資料可用，如果資料庫 buffer cache 中無可用緩存資料，使用者請求将會從 OS 緩存中擷取需要的檔案或者塊。作業系統緩存很可能已經擁有需要查找的資料塊或者檔案并将其駐留在資料庫緩沖中，在這種情況下，資料庫系統将會避免出現實體I/O請求，這在資料庫中也叫做邏輯讀，會消耗一定的 CPU 資源。如果需要查找的資料都不在這兩個緩存中或者其它緩存中，那麼使用者讀取資料都需要從磁盤去讀取，而第一次資料讀取必然要通過讀取實體檔案來查找。
二、buffer cache 檢查
PostgreSQL 提供了一個擴充可以用來檢視 buffer cache。以下将介紹buffer cache 擴充子產品。
2.1 建立資料庫
postgres=# CREATE DATABASE mydb1;
CREATE DATABASE
postgres=# CREATE DATABASE mydb2;
CREATE DATABASE
2.2 buffer cache 擴充模版
pg_buffercache 子產品位于 $PGDATA/../share/postgresql/extension 目錄。
提供了一個buffer cache 函數和一個視圖。如下：
CREATE FUNCTION pg_buffercache_pages() RETURNS
SETOF RECORD AS 'MODULE_PATHNAME',
'pg_buffercache_pages' LANGUAGE C PARALLEL SAFE;
CREATE OR REPLACE 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,
pinning_backends int4
);
建立 pg_buffercache 擴充
postgres=# \c mydb1 postgres
You are now connected to database "mydb1" as user "postgres".
mydb1=# CREATE EXTENSION pg_buffercache ;
CREATE EXTENSION
連接配接到資料庫 mydb1
mydb1=# \c mydb1 postgres
You are now connected to database "mydb1" as user "postgres".
mydb1=# \d+ pg_buffercache
View "public.pg_buffercache"
Column | Type | Collation | Nullable | Default | Storage | Descri
ption
------------------+----------+-----------+----------+---------+---------+-------
bufferid | integer | | | | plain |
relfilenode | oid | | | | plain |
reltablespace | oid | | | | plain |
reldatabase | oid | | | | plain |
relforknumber | smallint | | | | plain |
relblocknumber | bigint | | | | plain |
isdirty | boolean | | | | plain |
usagecount | smallint | | | | plain |
pinning_backends | integer | | | | plain |
View definition:
SELECT p.bufferid,
p.relfilenode,
p.reltablespace,
p.reldatabase,
p.relforknumber,
p.relblocknumber,
p.isdirty,
p.usagecount,
p.pinning_backends
FROM pg_buffercache_pages() p(bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid, relforknumber smallint, relblocknumber bigint, isdirty bool
ean, usagecount smallint, pinning_backends integer);
檢視視圖驗證結果
SELECT distinct a.reldatabase,
b.datname 資料庫名稱
FROM pg_buffercache b
RIGHT JOIN pg_database a
ON a.oid = b.reldatabase;
mydb1=# SELECT DISTINCT reldatabase
FROM pg_buffercache ;
reldatabase
-------------
14187
24576
24577
0
上面 reldatabase 列中出現為0的記錄的資料庫，表示沒有使用 buffer。
mydb1=# \! oid2name
All databases:
Oid Database Name Tablespace
----------------------------------
24576 mydb1 pg_default
24577 mydb2 pg_default
14187 postgres pg_default
14186 template0 pg_default
1 template1 pg_default
通過 oid2name 用戶端指令可以檢視到資料庫相關的 oid 資訊和名稱及使用的表空間。
建立視圖
CREATE OR REPLACE VIEW v_buf_info AS
SELECT c.datname 資料庫名稱,
d.spcname 表空間名稱,
a.relname 對象名稱,
count(*) AS 緩沖數量
FROM pg_class a
JOIN pg_buffercache b ON a.relfilenode = b.relfilenode
JOIN pg_database c ON (b.reldatabase = c.oid
AND c.datname = current_database())
JOIN pg_tablespace d ON d.oid = b.reltablespace
GROUP BY a.relname,c.datname,d.spcname
ORDER BY 2 DESC;
mydb1=# SELECT * FROM v_buf_info ;
資料庫名稱 | 表空間名稱 | 對象名稱 | 緩沖數量
------------+------------+-----------------------------------------------+----------
mydb1 | pg_default | pg_aggregate | 2
mydb1 | pg_default | pg_aggregate_fnoid_index | 2
mydb1 | pg_default | pg_am
通過該視圖可以檢視到資料庫 mydb1 中目前的緩存使用資訊。
上面視圖中看到的都是系統表在 buffer 中的緩存，如果需要過濾系統表使用的緩存資訊，在視圖中加入 where 條件中即可。
CREATE OR REPLACE VIEW v_buf_info AS
SELECT c.datname 資料庫名稱,
d.spcname 表空間名稱,
a.relname 對象名稱,
count(*) AS 緩沖數量
FROM pg_class a
JOIN pg_buffercache b ON a.relfilenode = b.relfilenode
JOIN pg_database c ON (b.reldatabase = c.oid
AND c.datname = current_database())
JOIN pg_tablespace d ON d.oid = b.reltablespace
WHERE a.relname !~ '^pg'
GROUP BY a.relname,c.datname,d.spcname
ORDER BY 2 DESC;
測試使用者資料在 buffer 中的資訊
mydb1=# --插入資料
mydb1=# INSERT INTO tab_buffer VALUES(1,'PostgreSQL');
INSERT 0 1
mydb1=# INSERT INTO tab_buffer VALUES(2,'MySQL');
INSERT 0 1
mydb1=# --檢視資料
mydb1=# SELECT * FROM tab_buffer;
id | name
----+------------
1 | PostgreSQL
2 | MySQL
在視圖中增加更多的列，來查詢緩存資訊
--查詢視圖
DROP VIEW IF EXISTS v_buf_info;
CREATE OR REPLACE VIEW v_buf_info AS
SELECT c.datname 資料庫名稱,
d.spcname 表空間名稱,
a.relname 對象名稱,
case
b.isdirty WHEN 't' THEN '含有髒塊' ELSE '沒有髒塊' END AS 是否包含髒塊,
count(*) AS 緩沖數量
FROM pg_class a
JOIN pg_buffercache b ON a.relfilenode = b.relfilenode
JOIN pg_database c ON (b.reldatabase = c.oid
AND c.datname = current_database())
JOIN pg_tablespace d ON d.oid = b.reltablespace
WHERE a.relname !~ '^pg'
GROUP BY a.relname,c.datname,d.spcname,b.isdirty
ORDER BY 2 DESC;
mydb1=# SELECT * FROM v_buf_info ;
資料庫名稱 | 表空間名稱 | 對象名稱 | 是否包含髒塊 | 緩沖數量
------------+------------+------------+--------------+----------
mydb1 | pg_default | tab_buffer | 含有髒塊 | 1
(1 row)
mydb1=# checkpoint ;
CHECKPOINT
mydb1=# SELECT * FROM v_buf_info ;
資料庫名稱 | 表空間名稱 | 對象名稱 | 是否包含髒塊 | 緩沖數量
------------+------------+------------+--------------+----------
mydb1 | pg_default | tab_buffer | 沒有髒塊 | 1
(1 row)
mydb1=# INSERT INTO tab_buffer VALUES(4,'Sungsasong');
INSERT 0 1
mydb1=# SELECT * FROM v_buf_info ;
資料庫名稱 | 表空間名稱 | 對象名稱 | 是否包含髒塊 | 緩沖數量
------------+------------+------------+--------------+----------
mydb1 | pg_default | tab_buffer | 含有髒塊 | 1
作者：宋少華
PostgreSQL分會教育訓練認證委員會委員、晟數科技首席技術專家、晟數學苑金牌講師、oracle 11g OCM、PostgreSQL首批PGCE。
曾服務于國家電網冀北電力有限公司建設大資料平台，為人社局和北京市衛計委建構IT基礎服務，為多家銀行和證券公司建構web 伺服器，系統及資料庫維護；具有對稅務局、國家電網、銀行等政府行業和民營企業的IT教育訓練經驗；為相關安全行業設計DW資料倉庫模型，使用PostgreSQL,Greenplum,HUAWEIGaussDB,Vertica和Clickhouse 做資料基礎服務，開發TB級資料落地程式及百TB級别資料遷移程式。
![PostgreSQL表的查詢（難度适中）[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)