[postgres] VACUUM 與 VACUUM FULL

簡單說明 postgres 中 VACUUM 與 VACUUM FULL 的差別。
VACUUM 的作用是將空間釋放，等到下次有新資料寫入時，可以放到該位址，但從 OS 的角度，系統空間並沒有變大。而 VACUUM FULL 則是會將空間釋放到 OS，亦需要較多的時間與 lock，與其他指令平行化的程度也不高。

來實際試試兩者之間的差異，先建立一 table，新增資料後再刪除資料，然後用 VACUUM 與 VACUUM FULL 來釋放空間。ctid 是 postgres 裡面每筆資料的 record_id，可以對應到 page 及 offset。

CREATE TABLE r (id INT PRIMARY KEY, val VARCHAR(6));
INSERT INTO r VALUES (101, 'aaa'),(102, 'bbb'),(103, 'ccc');
SELECT r.ctid, r.* FROM r;
 ctid  | id  | val 
-------+-----+-----
 (0,1) | 101 | aaa
 (0,2) | 102 | bbb
 (0,3) | 103 | ccc
-- 刪除一筆資料後，tuple 一樣是往下放
DELETE FROM r WHERE id = 102; 
INSERT INTO r VALUES (104, 'ddd');
SELECT r.ctid, r.* FROM r;
 ctid  | id  | val 
-------+-----+-----
 (0,1) | 101 | aaa
 (0,3) | 103 | ccc
 (0,4) | 104 | ddd

執行 VACUUM FULL 後，發現資料的位址往前移動。

VACUUM FULL;
SELECT r.ctid, r.* FROM r;
 ctid  | id  | val 
-------+-----+-----
 (0,1) | 101 | aaa
 (0,2) | 103 | ccc
 (0,3) | 104 | ddd

再次異動資料，刪除一筆，然後使用 VACUUM，可以發現資料沒有往前移動。

DELETE FROM r WHERE id = 103; 
-- 執行 VACUUM 後，發現新資料塞到 2 的位址
VACUUM;
INSERT INTO r VALUES (105, 'eee');
SELECT r.ctid, r.* FROM r;
 ctid  | id  | val 
-------+-----+-----
 (0,1) | 101 | aaa
 (0,2) | 105 | eee
 (0,3) | 104 | ddd

Reference
https://www.postgresql.org/docs/9.6/sql-vacuum.html
https://www.postgresql.org/docs/9.6/routine-vacuuming.html