淺談Postgres 的buffer cache

一般我們對RDBMS cache的了解,想讓大表格的資料進cache，執行類似select count(*) from "your table"，應該就會進到資料庫的buffer cache。 但對postgres來說，劇本不是這樣演的。
我以一個556MB 的表格WORDS 來談postgres cache 好了

我們先來count

來看這個表格在buffer cache 佔據多少空間....

阿...只佔了256 KB， 來....我們再看來他的執行計畫是否也顯示只用256KB 的cache
warehouse_db=# explain (buffers, analyze) select count(*) from words;

shared hit = 32  表示cache hit，32 * 8k = 256 KB，還真的只能用到256KB 的db buffer cache
read = 71089，表示disk I/O fetch，71089 * 8k 大約是 555.3 MB，我想大家會質疑為什麼???這是因為postgres 碰到大表I/O時，為了避免大表的資料把已經在cache裏頭的資料給擠了出去，所以設計了一個256KB  ring buffer 供處理大表I/O之用

我寫了一隻function，用一筆一筆的方式撈取資料，你會看到不一樣的地方
create or replace function test_warm() RETURNS void
as $$
declare
 r words%rowtype;
 i tid;
begin
 for i in execute 'select ctid from words'
 loop
   select * into r  from words  where ctid = i;
 end loop;

end;
$$
LANGUAGE plpgsql;

我們來看執行完後, pg_bffercache的數據

warehouse_db=# select test_warm();
 test_warm
-----------

(1 row)

Time: 30707.500 ms

看到了吧.........這就表示它被撈取的時候, 直接進了buffer cache array 裏頭了
我們再來看執行計畫
warehouse_db=# explain (buffers, analyze) select count(*) from words;

shared hit = 68793 *8k 大約就是537MB
這代表甚麼含意 ???
這表示postgres 是一個慢熱的資料庫系統, 如果你不先針對資料預處理, 要讓cache 的利用率提高的話,你得花一段時間去等待, 所以它被指出, 適用於OLTP 的環境
至於你想跑batch....嘿嘿......系統設計的人得想辦法先暖機啦!!
9.4以上的版本出了pg_prewarm的extension,就是用來把表格先pre-liad到記憶體裡頭
https://madusudanan.com/blog/understanding-postgres-caching-in-depth/

Posted in: Postgres,RDBMS