你知道 PostgreSQL 執行一次 UPDATE，背後其實不是修改資料，而是「新增一筆資料」嗎？
這個設計決定了資料表為什麼會愈來愈大，以及為什麼需要 VACUUM 來清理。
這篇文章會帶你實際觀察 PostgreSQL 更新資料時發生了什麼事，從現象出發，一步步揭開背後的原理。
PostgreSQL 怎麼把資料存在硬碟上？
在開始之前，先建立一個重要的觀念。
很多人剛開始學資料庫，會覺得「資料庫就像 Excel，一個格子放一個值」。
這個想法沒有錯，但資料庫在硬碟上的實際運作方式跟 Excel 有一個關鍵差異：每一筆資料都會佔用硬碟上的儲存空間。
欄位愈多、內容愈長，這筆資料佔用的空間就愈大。
PostgreSQL 管理儲存空間的方式，是把硬碟空間切成一格一格，每格固定 8KB，這個單位叫做頁面（page）。
資料就一筆一筆依序放進這些格子裡。
你可以把它想像成一個一個的箱子，每個箱子只能裝 8KB 的東西。
當一個箱子裝滿了，PostgreSQL 就會拿出新的箱子繼續裝。
建立 PostgreSQL 測試資料表
為了方便觀察，我們先建一張精簡版的使用者資料表 users_mini，只放幾個欄位：id、display_name、reputation，再加一個填充欄位 padding，讓每筆資料佔用更多硬碟空間。
這樣做是為了讓每個頁面放不下太多筆資料，方便我們觀察資料是怎麼分佈在不同頁面上的。
如果每筆資料很小，可能要幾百筆才能塞滿一頁，觀察起來不直覺。
CREATE TABLE users_mini (
id SERIAL PRIMARY KEY,
display_name VARCHAR(50),
reputation INT,
last_login TIMESTAMP,
padding CHAR(500)
);插入 26 筆資料，對應 26 個英文字母：Alex、Billy、Charlie、Devin……以此類推。
INSERT INTO users_mini (display_name, reputation, padding)
VALUES
('Alex', 100, ''),
('Billy', 200, ''),
('Charlie', 150, ''),
('Devin', 300, ''),
-- … 以此類推，共 26 筆
;插入完成後，查詢這張表：
SELECT ctid, * FROM users_mini ORDER BY id;結果長這樣：
你會看到最左邊有一個叫做 CTID 的欄位，這是 PostgreSQL 自動幫每筆資料加上的系統欄位，不需要你自己設定。
CTID 是什麼？
CTID 裡面有兩個數字，格式是 (頁面編號, 列編號)。
還記得前面提到的頁面嗎？CTID 就是用來記錄這筆資料放在哪一頁、第幾個位置。
(0, 1)代表：第 0 頁，第 1 筆資料(0, 2)代表：第 0 頁，第 2 筆資料
頁面編號從 0 開始，列編號從 1 開始。這是 PostgreSQL 的設計，不需要太糾結。
因為每筆資料有填充欄位，每頁大約只能放 10 筆資料。
26 筆資料會分佈在 3 個頁面上：
page 0：Alex (0,1)、Billy (0,2)、Charlie (0,3)… 共 10 筆
page 1：第 11～20 筆
page 2：第 21～26 筆（剩餘 6 筆）查看資料表大小，會顯示 24KB（3 頁 × 8KB）。
UPDATE 之後 CTID 為什麼會改變？
現在對所有資料執行一次更新，模擬所有人同時登入的情況：
UPDATE users_mini SET last_login = NOW();更新完成後，再查一次 CTID：
SELECT ctid, * FROM users_mini ORDER BY id;結果長這樣：
更新前 Alex 在 (0, 1)，更新後跑到 (2, 7) 去了。
Billy 原本在 (0, 2)，現在跑到 (2, 8)。
所有人的 CTID 都變了，而且全部都跑到 page 2 之後的頁面去了。
為什麼執行完 UPDATE 之後，Alex 的位置會改變？
PostgreSQL 的更新不是「覆蓋」，而是「新增一個版本」
原來 PostgreSQL 執行 UPDATE 的方式，跟你想的不一樣。
一般人直覺會以為：UPDATE 就是把原本的資料找到、改掉，就像在 Excel 裡直接修改一格的內容。
但 PostgreSQL 不是這樣運作的。
它的做法是：
- 把舊版本留在原地 — 原本在
(0, 1)的 Alex 繼續待在那裡，完全沒有被動到 - 在新的位置寫入新版本 — 把更新後的 Alex 寫到一個新的位置，也就是
(2, 7) - 把舊版本標記為「已失效」 — 告訴 PostgreSQL：這個版本已經過期了，之後可以清掉
所以執行完 UPDATE 之後，資料表裡其實同時存在兩個版本的 Alex：
你平常用 SELECT 查資料，只會看到新版本。
但舊版本其實還在硬碟上，悄悄地佔著空間。
你會開始好奇：為什麼要這樣做？直接覆蓋不就好了？
為什麼要保留舊版本？因為有人正在讀
如果 PostgreSQL 直接覆蓋資料，會發生什麼事？
假設你執行了一個大型 UPDATE，要更新幾千筆使用者的登入時間，這個操作需要跑好幾秒鐘。
就在你改到第 500 筆的時候，另一個人同時執行 SELECT，想查出所有使用者的資料。
如果 PostgreSQL 直接覆蓋，那個人查到的結果會長這樣：
前 500 筆的 last_login 已經被更新成 2024-01-01 12:00:00，第 501 筆之後還是更新前的 2023-06-15 08:30:00。
同一次查詢，讀到的資料有新有舊，這在資料庫裡叫做髒讀（Dirty Read），是非常嚴重的問題。
PostgreSQL 的解法：保留舊版本
PostgreSQL 的做法是：寫的人在寫新版本的時候，舊版本繼續留著不動。
這樣一來：
- 讀的人查到的是一個完整、一致的版本，不會讀到寫到一半的資料
- 寫的人可以放心地把新版本寫到新的位置，不用擔心影響到正在讀資料的人
當 UPDATE 完成之後，下一個人執行 SELECT 就會讀到新版本了。
在 UPDATE 還沒完成之前讀到舊版本，UPDATE 完成之後讀到新版本，每個人看到的資料永遠是完整一致的，不會出現讀到一半的情況。
這個機制就叫做 MVCC（Multi-Version Concurrency Control，多版本並行控制）。
「Multi-Version」指的是同一筆資料可以同時存在多個版本，「Concurrency Control」指的是讓讀和寫可以同時發生、不互相阻塞。
如果你用過 Microsoft SQL Server，它有類似的功能叫做 RCSI（Read Committed Snapshot Isolation），但預設是關閉的，需要手動開啟。PostgreSQL 的 MVCC 則是預設開啟，而且無法關閉。
MVCC 的代價：Dead Tuple 讓資料表變大
MVCC 雖然解決了讀寫衝突，但帶來了一個新問題。
更新 26 筆資料後，再查一次資料表大小：
- 原本：24KB（3 頁）
- 更新後：48KB（6 頁）
因為舊版本還留著，新版本又需要額外的頁面來存放，資料表就從 3 頁膨脹成 6 頁。
這裡要介紹一個 PostgreSQL 的術語：Tuple（元組）
你和我平常說的「一筆資料」，在 PostgreSQL 內部叫做 tuple。
兩個詞指的是同一件事，只是當一筆資料同時存在多個版本時，「列（row）」這個詞就說不清楚了——你說的是哪一個版本？
所以 PostgreSQL 用 tuple 來指「磁碟上實際儲存的那一筆」，每個版本都是一個獨立的 tuple。
以 Alex 為例，執行完 UPDATE 之後，磁碟上同時有兩個 tuple：
(0, 1) 那個已經沒有人會用到了，但它還是靜靜地躺在 page 0 上佔著空間。
PostgreSQL 把這種沒人使用的舊版本稱為 Dead Tuple（死元組）。
Dead Tuple 會造成查詢變慢
PostgreSQL 執行 SELECT 的時候，需要一頁一頁掃描資料。
頁面愈多，要掃描的範圍就愈大，查詢就愈慢。
原本 3 頁可以掃完，現在要掃 6 頁，如果資料表有幾百萬筆資料，這個差距就非常明顯了。
Dead Tuple 會讓備份變大
如果你用的是儲存快照備份（直接備份硬碟上的檔案），備份的對象是整個資料表的實際大小，Dead Tuple 佔的空間也會一起被備份進去，備份檔案就跟著變大了。
用 VACUUM 清除 Dead Tuple
Dead Tuple 不會自己消失，需要靠一個叫做 VACUUM 的指令來清除。
VACUUM FULL users_mini;VACUUM FULL 的運作方式，是把整張資料表從頭重建一次。
它會讀取所有舊頁面，把每一個 tuple 檢查一遍，只保留還活著的 live tuple，跳過所有 dead tuple，然後從頭寫出一份全新、乾淨的資料表。
你可以把它想像成整理房間：不是把垃圾藏起來，而是把所有東西搬出去，只把有用的搬回來，重新擺放整齊。
執行完之後，再查一次 CTID 和資料表大小，你會發現一切都回到了起點：
- Alex 回到了
(0, 1) - 資料表大小回到 24KB
- 所有 dead tuple 消失了
不過 VACUUM FULL 有一個嚴重的缺點：執行期間會鎖住整張資料表，沒有人能讀或寫。
這是因為它需要把整張資料表搬走再重建，這段期間任何人想讀或寫都必須等待。
24KB 的小資料表幾乎是瞬間完成，感覺不出來。但如果你的資料表有幾十 GB 甚至 TB，VACUUM FULL 可能要跑好幾個小時，這段時間整個系統就停擺了。
所以在正式環境中，不會頻繁手動執行 VACUUM FULL。
PostgreSQL 提供了一個背景程序叫做 Auto Vacuum，它會自動偵測 dead tuple 的數量，累積到一定程度就在背後幫你清理。
Auto Vacuum 比 VACUUM FULL 輕量，不會完全鎖住資料表，系統可以繼續正常運作，也不需要你手動執行。
小結
核心邏輯串起來就是：
UPDATE 不覆蓋 → 舊版本保留（Dead Tuple）→ 資料表膨脹 → 需要 VACUUM 清理 → Auto Vacuum 自動幫你做。
下一步，可以進一步研究：當資料表有索引時，MVCC 和 VACUUM 的行為有什麼不同？