一般我們對RDBMS cache的了解,想讓大表格的資料進cache,執行類似select count(*) from "your table",應該就會進到資料庫的buffer cache。 但對postgres來說,劇本不是這樣演的。
我以一個556MB 的表格WORDS 來談postgres cache 好了
來看這個表格在buffer cache 佔據多少空間....
阿...只佔了256 KB, 來....我們再看來他的執行計畫是否也顯示只用256KB 的cache
warehouse_db=# explain (buffers, analyze) select count(*) from words;
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的數據
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/