一、與ORACLE和MySQL的區別

在使用PostgreSQL要設定shared_buffers的大小的時候，我們通常的建議值是作業系統記憶體的25%，過大或者過小的值都會影響資料庫的效能。此時我們有時候會產生一個疑問，為什麼類似的引數，ORACLE和MYSQL都分配了作業系統大部分記憶體，例如ORACLE的sga通常設定為實體記憶體的80%，或者MySQL的innodb_buffer_pool_size 通常也設定為80%，而PostgreSQL的shared_buffers確設定的比例這麼小呢。

其實是因為Postgresql採用資料庫例項buffer(shared_buffers)和作業系統buffer雙快取(effective_cache_size)的工作模式，PostgreSQL資料庫必須高度依賴作業系統快取，它依賴於作業系統來了解檔案系統、磁碟佈局以及如何讀寫資料檔案。快取作為資料庫的一個核心元件，shared_buffers決定了資料庫例項層面的可用記憶體，而系統中預計有多少快取是effective_cache_size決定的。而且effective_cache_size不僅是快取經常訪問的資料，它同時幫助最佳化器確定實際存在多少快取，指導最佳化器生成最佳執行計劃。

而ORACLE和MYSQL把大部分系統記憶體給到了資料庫快取，傾向於不使用OS cache，支援使用 direct IO——在應用層Buffer和磁碟之間直接建立通道，繞過作業系統快取。這樣在讀寫資料的時候就能夠減少上下文切換次數，同時也能夠減少資料複製次數，從而提高效率。而原生PostgreSQL是不支援direct IO的，這一點和ORACLE、MySQL還是有著比較本質上的差異的。（事情也不絕對,例如Aurora PostgreSQL消除了雙快取，並且不使用檔案系統快取）

二、PostgreSQL讀寫資料的過程

當我們日常在資料庫裡寫入資料後，bgwriter程式將髒緩衝區重新整理到磁碟時，頁面實際上是先重新整理到OS快取，然後再重新整理到磁碟。

而執行查詢，會先從shared_buffers裡查詢，一旦在shared_buffers裡命中了資料頁，就永遠不會再到作業系統快取裡進行查詢。但如果在shared_buffers裡沒命中，則會繼續從OS cache裡找尋，如果在OS cache裡命中了，則把資料載入到shared_buffers裡去。
如果在shared_buffers和OS cache裡都沒有命中的話，則會把資料先載入到作業系統快取（OS cache ），然後再載入到shared buffers。

這種雙快取的工作模式意味著OS cache和shared_buffers可以儲存相同的頁面。有一定可能可能會導致空間浪費，但OS快取使用的是LRU演算法，而不是shared_buffers的時鐘掃描演算法（clock sweep algorithm.）。一旦在shared_buffers裡命中了資料頁，就永遠不會到作業系統快取裡進行查詢，因此，在shared_buffers里長期使用到的部分，在OS cache裡實際上會很容易就被清理掉了。

三、當shared_buffers設定過大或過小

1.shared_buffers過小、OS cache較大

當我們給shared_buffers過小而OS cache較大的時候，雖然資料會集中在OS cache裡，但實際的資料庫的操作都是在共享緩衝區裡執行的，所以做一些複雜查詢的時候，效能是很差的。

除此之外，shared_buffers採用的時鐘掃描演算法（clock sweep algorithm.）演算法為每個最近被使用的頁面增加了權重，使用越頻繁越不容易被替換出去，比OS cache的LRU演算法更加符合真實的場景，shared_buffers裡其實比OS cache更加容易快取到常用的資料。

2.shared_buffers過大、OS cache較小

而當我們給OS cache很小，但是shared_buffers很大的時候，shared_buffers裡一旦頁被標記成了髒頁，就會被重新整理到OS cache裡，如果OS cache過小的話，它就不能重新排序寫操作以及最佳化IO，可能導致大量的離散寫，對於有大量繁重寫入操作的資料庫而言，這一點十分的不友好。

此外PostgreSQL資料目錄裡pg_clog目錄下儲存了提交日誌資訊，是定期讀取和寫入的，因此OS cache的大小還和clog的讀寫任務效能息息相關，透過OS cache會更直接。並且，shared_buffers管理記憶體也需要代價，檢查點、髒頁判斷的代價也會隨著shared_buffers的增大而增大。

四、如何檢視shared_buffers或OS cache裡快取資料量

可以使用pg_buffercache和pgfincore這兩個外掛去檢視快取裡的資料量。
pgfincore工具github的地址如下

git clone git://git.postgresql.org/git/pgfincore.git
make 
make install
[xmaster@mogdb-kernel-0005 pgfincore]$ psql
psql (14.1)
Type "help" for help.
postgres=# create extension pg_buffercache;
CREATE EXTENSION
postgres=# CREATE EXTENSION pgfincore;
CREATE EXTENSION

使用如下語句檢視快取資料量及比例。

postgres=# select c.relname,pg_size_pretty(count(*) * 8192) as pg_buffered, 
postgres-#  round(100.0 * count(*) / 
postgres(#            (select setting 
postgres(#             from pg_settings 
postgres(#             where name='shared_buffers')::integer,1)
postgres-#        as pgbuffer_percent,
postgres-#        round(100.0*count(*)*8192 / pg_table_size(c.oid),1) as percent_of_relation,
postgres-#        ( select round( sum(pages_mem) * 4 /1024,0 )
postgres(#          from pgfincore(c.relname::text) ) 
postgres-#          as os_cache_MB , 
postgres-#          round(100 * ( 
postgres(#                select sum(pages_mem)*4096 
postgres(#                from pgfincore(c.relname::text) )/ pg_table_size(c.oid),1) 
postgres-#          as os_cache_percent_of_relation,
postgres-#          pg_size_pretty(pg_table_size(c.oid)) as rel_size 
postgres-#  from pg_class c 
postgres-#  inner join pg_buffercache b on b.relfilenode=c.relfilenode 
postgres-#  inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database()
postgres(#             and c.relnamespace=(select oid from pg_namespace where nspname='public')) 
postgres-#  group by c.oid,c.relname 
postgres-#  order by 3 desc limit 30;
  relname  | pg_buffered | pgbuffer_percent | percent_of_relation | os_cache_mb | os_cache_percent_of_relation | rel_size 
-----------+-------------+------------------+---------------------+-------------+------------------------------+----------
 demotable | 43 MB       |             33.5 |               100.0 |          43 |                         99.9 | 43 MB
 demoidx   | 64 kB       |              0.0 |                 0.2 |          30 |                        100.0 | 30 MB
(2 rows)

以demotable表為例

pg_buffered表示在PostgreSQL的shared_buffers中快取了該表多少資料，這裡是43MB
pgbuffer_percent 表示是該表佔用的shared_buffers的比例，也就是33.5%
percent_of_relation表示表在share_buffers的命中率，這裡是100%，
os_cache_mb 表示OS cache中快取了該表多少資料，這裡是43MB
os_cache_percent_of_relation 表示表在OS cache中命中率，這裡是99.9%
rel_size 表示這個表的真實大小,這裡是43MB