正確加索引:大型資料表上線不出事
Adding Indexes Safely: Indexing Large Tables Without an Outage
在正在接單的大型資料表上加索引,做錯一步就會擋住寫入、拖垮整個服務。這篇從原理與 EXPLAIN 的證據出發,設計出正確的索引,再用 CONCURRENTLY、lock_timeout 與可重試的 Go 遷移程式安全上線。
這篇的步驟
上一篇〈從單體到微服務:十萬、百萬、千萬級的架構怎麼演進〉在十萬級那一步提到:資料庫的慢查詢,通常是少了某個索引;在加快取、加機器之前,先把索引與查詢做對,這一步花費最少、效果常常最大。這篇就把這件事做完整:什麼時候該加索引、該加什麼樣的索引,以及最容易被輕忽的一件事——怎麼在一張幾千萬列、正在接單的資料表上把索引加上去,而不讓服務停擺。
這篇要解決什麼
「查詢慢就加索引」這句話只對了一半。實際上常見的狀況是:
- 憑感覺加了索引,查詢一樣慢,因為那個查詢根本用不到它;資料表上卻多了一個每次寫入都要維護的結構。
- 複合索引的欄位順序放錯,只有一部分查詢用得到。
- 在尖峰時段對大表直接執行
CREATE INDEX,整張表的寫入被擋住好幾分鐘,下單全部逾時。 - 改用
CREATE INDEX CONCURRENTLY,遷移工具卻把它包在交易裡,直接報錯;或是建到一半失敗,留下一個沒人發現的INVALID索引,之後每次寫入都在白白維護它。 - 一個看起來只要幾毫秒的 DDL 卡在等鎖,後面所有的查詢排成一條長隊,連線池在幾秒內耗盡。
這篇以系列一直使用的網路商店為例(example.com/shop,服務有 api-gateway、order、payment、inventory),資料庫是 PostgreSQL,主角是〈database/sql、交易與連線池〉建立的 orders 表。順序是:
- 索引怎麼運作,懂到足以做判斷就好。
- 用證據(
pg_stat_statements、慢查詢紀錄、EXPLAIN (ANALYZE, BUFFERS))找出真正該加的索引。 - 把索引設計對:複合索引的欄位順序、覆蓋索引、部分索引、運算式索引、唯一索引,以及清掉沒用的索引。
- 核心:在大型線上資料表加索引而不出事。
- 用 Go 寫一個安全的遷移程式,處理交易外執行、逾時、重試與失敗殘留。
- MySQL(InnoDB)的差異。
- 上線前檢查表、退場計畫,以及一個沒設
lock_timeout的事故時間軸。
提示:這篇的 SQL 以 PostgreSQL 為準,
EXPLAIN的輸出是在一台開發機上、用五百萬筆測試資料跑出來的,數字只用來對照前後差異,不代表任何正式環境的效能。
第 1 步:索引怎麼運作,懂到能做判斷就好
B-tree:一份排好序、可以快速定位的目錄
PostgreSQL 預設的索引是 B-tree。可以把它想成一本書最後的索引頁:依照「鍵」排好序,每個項目指向資料在表裡的實際位置(PostgreSQL 稱為 TID,也就是哪一個資料頁的第幾個項目)。樹的上層只用來導航,最底層的葉節點依序串起來,所以 B-tree 擅長三件事:
- 等值查詢:
WHERE user_id = 42,從根往下幾層就找到。 - 範圍查詢:
WHERE created_at >= ... AND created_at < ...,找到起點後沿著葉節點往後讀。 - 排序:索引本身就是排好序的,
ORDER BY created_at DESC LIMIT 20可以直接從一端讀二十筆就停,不必把全部資料拿出來排序。
資料表本身(heap)則沒有順序。沒有可用的索引時,資料庫只能做循序掃描(Seq Scan):把整張表每一頁都讀過一遍,逐列檢查條件。
複合索引的鍵由多個欄位組成,排序規則就像電話簿:先依第一個欄位排,第一個欄位相同時再依第二個欄位排,依此類推。這決定了它能幫上哪些查詢,第 3 步會再回來談。
圖:複合索引 (user_id, status, created_at DESC) 的葉節點依序排列;user_id = 42 AND status = 'paid' 落在連續的一段,段內已經依 created_at 由新到舊排好,LIMIT 20 讀前二十筆就停。
索引不是免費的
每一個索引都是一份額外的資料結構,資料庫要一直讓它和資料表保持一致:
- 寫入放大。 每一次
INSERT都要在每一個索引裡各插入一筆;UPDATE改到被索引的欄位時,也要在索引裡加入新的項目。一張表有八個索引,一次寫入就是九個地方要寫,還要多寫對應的 WAL(預寫式日誌),唯讀副本也要重放同樣的量。 - 儲存空間與快取。 索引佔磁碟,也搶共享記憶體(shared buffers)與作業系統的頁快取。上面的測試資料裡,
orders表本身約 330 MB,一個(user_id, id DESC)的索引就有約 190 MB。索引越多,真正常用的資料越難全部留在記憶體裡。 - VACUUM 與 HOT 更新。 PostgreSQL 的
UPDATE是寫一份新版本的資料列。如果這次更新沒有改到任何被索引的欄位、而且同一個資料頁還有空間,就可以做 HOT(heap-only tuple)更新:只在資料頁裡串起新舊版本,所有索引都不用動。反過來說,替一個經常被更新的欄位(例如status、updated_at)加上索引,就會讓這些更新失去 HOT,每次都要寫索引,也讓 VACUUM 要清理的東西變多。部分索引的WHERE條件用到的欄位,同樣算「被索引的欄位」。 - 規劃器的選擇。 索引越多,查詢規劃器(planner)要考慮的路徑越多;更常見的問題是規劃器依統計資料選錯了索引。統計資料過舊(大量寫入後還沒
ANALYZE)時,估計的列數和實際差很多,選出來的計畫就可能很差。
pg_stat_user_tables 可以看出一張表的更新有多少是 HOT:
SELECT relname, n_tup_upd, n_tup_hot_upd,
round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
WHERE relname = 'orders';替經常更新的表加索引之前,先記下這個比例,加完之後再看一次。
什麼時候索引幫得上忙,什麼時候幫不上
選擇性(selectivity)。 條件篩掉的資料越多,索引越有用。orders 裡九成五的訂單是 paid,WHERE status = 'paid' 就算有索引,規劃器也會選擇循序掃描:要拿回大部分的資料列時,依索引一筆一筆跳到資料頁去讀,比整張表循序讀一遍還慢。相反地,status = 'pending' 只佔很少數,索引就非常有用——第 3 步的部分索引正是為這種情況設計的。
小表。 只有幾十頁的表,循序掃描一次就讀完,用索引反而多了一層查找。看到小表的 Seq Scan 不必緊張。
對欄位套函式或轉型。 索引裡存的是欄位的原始值,條件寫成 created_at::date = '2026-11-01' 時,資料庫要先替每一列算出 created_at::date 才能比較,created_at 上的索引用不到:
-- 用不到 created_at 的索引:要先對每一列做轉型
EXPLAIN SELECT id FROM orders WHERE created_at::date = '2026-11-01';
-- Parallel Seq Scan on orders
-- Filter: ((created_at)::date = '2026-11-01'::date)
-- 改寫成範圍條件,索引就用得上
EXPLAIN SELECT id FROM orders
WHERE created_at >= '2026-11-01' AND created_at < '2026-11-02';
-- Index Scan using orders_created_at_idx on orders隱含轉型。 型別不一致時,被轉型的可能是欄位那一邊。user_id 是 BIGINT,條件卻寫成和 NUMERIC 比較,PostgreSQL 會把每一列的 user_id 轉成 NUMERIC 再比,索引同樣用不到:
EXPLAIN SELECT id FROM orders WHERE user_id = 42::numeric;
-- Parallel Seq Scan on orders
-- Filter: ((user_id)::numeric = '42'::numeric)
EXPLAIN SELECT id FROM orders WHERE user_id = 42;
-- Index Only Scan using orders_user_id_idx on orders
-- Index Cond: (user_id = 42)用 Go 的 database/sql 搭配佔位符時,參數的型別通常由伺服器依欄位推斷,比較少踩到這個問題;常見的來源是 SQL 裡手寫的轉型,或是 ORM、查詢產生器替參數指定了不同的型別。
前置萬用字元的 LIKE。 LIKE 'abc%' 有固定的開頭,理論上可以用 B-tree 的範圍找;LIKE '%abc' 或 '%abc%' 沒有開頭可以定位,B-tree 幫不上忙。需要「包含某段文字」的搜尋,要用 pg_trgm 擴充套件搭配 GIN 索引,或交給專門的搜尋引擎。另外要注意,非 C 定序(collation)的文字欄位,即使是 LIKE 'abc%' 也需要用 text_pattern_ops 運算子類別建的索引才能使用。
OR。 WHERE user_id = 42 OR total = 1234 這種條件,一個複合索引通常服務不了;規劃器可以用兩個各自的索引做 BitmapOr 再合併,但前提是兩邊都有可用的索引,任何一邊沒有就整張表掃描。有時改寫成兩個查詢的 UNION 更清楚。
第 2 步:用證據找出該加的索引
不要猜。要加哪個索引,應該由三種證據決定:哪些查詢最花時間、它們現在的執行計畫長什麼樣、加了之後計畫變成什麼樣。
找出最花時間的查詢:pg_stat_statements
pg_stat_statements 擴充套件會依「正規化後的查詢文字」(參數換成 $1、$2)累計每一種查詢的次數、總時間、平均時間與讀取的資料頁。它需要在 shared_preload_libraries 裡載入(要重啟資料庫),再到資料庫裡建立擴充套件;託管的 PostgreSQL 服務通常已經開好或可以在設定裡開啟。
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT queryid,
calls,
round(total_exec_time) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows,
shared_blks_hit,
shared_blks_read,
left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;先依 total_exec_time 排序,而不是 mean_exec_time:一個平均 2 毫秒、每秒執行三千次的查詢,對資料庫的負擔遠大於一個平均 3 秒、一天只跑一次的報表。平均時間高、次數少的查詢也要看,但順序是先處理總時間最多的。shared_blks_read 很高代表它常常要從磁碟(或作業系統快取)讀資料,通常就是讀了太多不需要的資料頁。
慢查詢紀錄與 auto_explain
pg_stat_statements 告訴你是哪一種查詢,但不會告訴你某一次特別慢的時候用了哪些參數。慢查詢紀錄補上這一塊:
-- 超過 500 毫秒的陳述式寫進資料庫日誌(只對新的連線生效)
ALTER DATABASE shop SET log_min_duration_statement = '500ms';auto_explain 擴充套件更進一步,會把慢查詢的執行計畫也寫進日誌,適合抓「只有某些參數才慢」的查詢。開啟 auto_explain.log_analyze 會讓每個查詢都多一些量測的開銷,正式環境要先評估,或只在一段時間內開啟。
讀懂 EXPLAIN (ANALYZE, BUFFERS)
以商店的一個背景工作為例:每分鐘找出建立超過三十分鐘、還沒付款的訂單,取消並釋放庫存。查詢是這樣:
SELECT id, user_id, total, created_at
FROM orders
WHERE status = 'pending'
AND created_at < now() - interval '30 minutes'
ORDER BY created_at
LIMIT 100;EXPLAIN 只顯示規劃器預估的計畫;EXPLAIN (ANALYZE, BUFFERS) 會真的執行查詢,並附上實際的時間、列數與讀了多少資料頁。因為會真的執行,分析 UPDATE、DELETE 時要包在交易裡再回滾:BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE ...; ROLLBACK;。
加索引之前,五百萬筆的 orders 上得到的計畫:
Limit (cost=84784.96..84796.63 rows=100 width=32) (actual time=86.613..88.846 rows=100 loops=1)
Buffers: shared hit=997 read=41113
-> Gather Merge (cost=84784.96..85287.13 rows=4304 width=32) (actual time=86.611..88.839 rows=100 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=83784.94..83790.32 rows=2152 width=32) (actual time=84.628..84.630 rows=67 loops=3)
Sort Key: created_at
Sort Method: top-N heapsort Memory: 32kB
-> Parallel Seq Scan on orders (cost=0.00..83702.69 rows=2152 width=32) (actual time=84.267..84.491 rows=1568 loops=3)
Filter: ((status = 'pending'::text) AND (created_at < (now() - '00:30:00'::interval)))
Rows Removed by Filter: 1665098
Buffers: shared hit=923 read=41113
Planning Time: 0.341 ms
Execution Time: 88.870 ms一步一步讀:
- 由內往外、由下往上讀。 縮排最深的節點最先執行,把結果交給上一層。這裡最底層是
Parallel Seq Scan:三個行程(一個主行程加兩個 worker)平行地掃描整張表。 cost是預估,actual是實際。cost=啟動成本..總成本是規劃器用來比較計畫的抽象單位,不是毫秒;actual time=第一筆..最後一筆才是毫秒。loops。actual的時間與列數是每一次迴圈的平均,要乘上loops才是總量。rows=1568 loops=3代表三個行程一共找到約 4,700 筆符合的資料。- 比較預估列數和實際列數。 預估
rows=2152(每個行程),實際 1568,差距不大,代表統計資料是準的。如果預估 10 筆、實際十萬筆,問題就是統計資料,而不是索引——先ANALYZE。 Rows Removed by Filter。 每個行程丟掉了約 166 萬筆,三個加起來就是整張表。為了 100 筆結果讀了五百萬筆,這是最明顯的訊號:缺一個能直接定位的索引。Buffers。shared hit是在共享記憶體裡找到的資料頁,read是要另外讀進來的(可能來自作業系統快取,也可能真的讀磁碟)。四萬一千多頁、約 330 MB,就是整張表。- 最上層的
Sort與Limit。 因為資料沒有順序,每個行程要先把符合的資料排序(top-N heapsort),再合併取前 100 筆。
這個查詢只需要「狀態是 pending、依建立時間排序」的那一小部分資料。加上一個部分索引(第 3 步會解釋)之後:
CREATE INDEX CONCURRENTLY orders_pending_created_idx
ON orders (created_at)
WHERE status = 'pending';Limit (cost=0.29..4.16 rows=100 width=32) (actual time=0.008..0.020 rows=100 loops=1)
Buffers: shared hit=3
-> Index Scan using orders_pending_created_idx on orders (cost=0.29..200.45 rows=5166 width=32) (actual time=0.008..0.016 rows=100 loops=1)
Index Cond: (created_at < (now() - '00:30:00'::interval))
Buffers: shared hit=3
Planning Time: 0.636 ms
Execution Time: 0.030 ms讀了 3 個資料頁、0.03 毫秒,沒有排序節點(索引本身已經依 created_at 排好),條件 status = 'pending' 也不見了——因為這個索引裡只有 pending 的訂單,規劃器知道不需要再檢查。這個索引只有 128 kB,而前面那個涵蓋所有訂單的 (user_id, id DESC) 索引是 190 MB。
圖:同一個查詢,加索引前是平行循序掃描整張表、篩掉約五百萬筆再排序;加上部分索引後直接依序讀出 100 筆,讀取的資料頁從約四萬一千頁降到 3 頁。
提示:加完索引之後一定要再跑一次
EXPLAIN (ANALYZE, BUFFERS),確認計畫真的用到了新索引。最好用正式環境實際出現過的參數,或在接近正式資料量的副本上測:同一個查詢在一千筆的開發資料庫上,規劃器的選擇可能完全不同。
第 3 步:把索引設計對
複合索引的欄位順序:等值在前,範圍與排序在後
「我的訂單」頁面要列出某個使用者某種狀態的訂單,由新到舊,每頁 20 筆:
SELECT id, status, total, created_at
FROM orders
WHERE user_id = $1 AND status = $2
ORDER BY created_at DESC, id DESC
LIMIT 20;決定欄位順序的規則:
- 等值條件的欄位放最前面(
user_id、status)。等值條件把範圍縮成索引裡連續的一段。 - 接著放範圍條件或排序的欄位(
created_at DESC)。在前面欄位都相等的那一段裡,資料已經依這個欄位排好,排序與LIMIT可以直接利用。 - 範圍條件之後的欄位幫助有限。 一旦某個欄位用的是範圍條件,後面的欄位在索引裡就不再是連續的,只能逐筆過濾。
所以索引是 (user_id, status, created_at DESC, id DESC)。把 created_at 放在 status 前面,status = $2 就只能在掃過的資料裡逐筆篩選;把 status 放最前面,選擇性太低,對單一使用者的查詢幫助也較小。
複合索引也遵守最左前綴:(user_id, status, created_at) 可以服務 WHERE user_id = $1、WHERE user_id = $1 AND status = $2,但通常服務不了只有 WHERE status = $2 或只有 ORDER BY created_at 的查詢。PostgreSQL 18 起,B-tree 在某些情況下可以「跳過」前面的欄位(skip scan),但這只在前導欄位的不同值很少時才划算,不要把設計建立在它上面。
分頁要用鍵集(keyset),而且排序要和索引一致。 OFFSET 10000 會讓資料庫先讀過一萬筆再丟掉,越後面越慢。鍵集分頁記住上一頁最後一筆的 (created_at, id),下一頁從它之後開始:
SELECT id, status, total, created_at
FROM orders
WHERE user_id = $1 AND status = $2
AND (created_at, id) < ($3, $4) -- 上一頁最後一筆
ORDER BY created_at DESC, id DESC
LIMIT 20;id 是為了讓排序唯一(同一秒可能有多筆訂單),所以它也要放進索引,方向要和 ORDER BY 一致。如果索引只有 (user_id, status, created_at DESC),資料庫還是能用,但要再做一次「依 id 補排序」(計畫裡會出現 Incremental Sort);索引和 ORDER BY 完全一致時,(created_at, id) < ($3, $4) 整個成為索引條件,直接從那一點往下讀二十筆。
覆蓋索引:INCLUDE
如果查詢需要的欄位全部都在索引裡,PostgreSQL 可以做 Index Only Scan,不必回資料表讀資料列。上面的查詢還需要 total,它不是篩選或排序條件,放進鍵裡沒有意義,可以用 INCLUDE 附帶在索引的葉節點上:
CREATE INDEX CONCURRENTLY orders_user_status_created_idx
ON orders (user_id, status, created_at DESC, id DESC)
INCLUDE (total);Limit (actual time=0.004..0.006 rows=16 loops=1)
Buffers: shared hit=6
-> Index Only Scan using orders_user_status_created_idx on orders (actual time=0.004..0.005 rows=16 loops=1)
Index Cond: ((user_id = 4242) AND (status = 'paid'::text) AND (ROW(created_at, id) < ROW('2026-10-01 00:00:00+08'::timestamp with time zone, 4000000)))
Heap Fetches: 0Heap Fetches: 0 表示完全沒有回資料表。Index Only Scan 依賴可見性對照表(visibility map):剛被大量更新、還沒被 VACUUM 處理過的資料頁,仍然要回表確認可見性,Heap Fetches 會上升。INCLUDE 也要節制:附帶的欄位會讓索引變大,寬欄位(長文字、JSON)更不適合放進去。
部分索引:只索引需要的那一小部分
第 2 步的 orders_pending_created_idx 就是部分索引:WHERE status = 'pending' 讓索引只包含 pending 的訂單。大部分訂單很快就付款或取消,索引裡永遠只有一小部分資料,所以又小又快;已付款的訂單也不會出現在這個索引裡,不需要維護。
要讓規劃器使用部分索引,查詢的條件必須能推論出索引的條件。WHERE status = 'pending' 可以;WHERE status = $1 在規劃時不知道參數的值,就不一定能用上(使用預備陳述式的通用計畫時尤其如此)。所以部分索引適合條件是固定常數的查詢,例如這個背景工作。
前面提過的代價也在這裡:status 出現在部分索引的條件裡,訂單從 pending 變成 paid 的那次更新就不是 HOT 更新。對這張表來說這是值得的取捨,但要知道有這回事。
運算式索引:索引計算後的值
如果業務上真的需要「依台北時間的日期」查訂單,可以把運算式本身做成索引,查詢要寫成完全相同的運算式:
CREATE INDEX CONCURRENTLY orders_created_tw_date_idx
ON orders (((created_at AT TIME ZONE 'Asia/Taipei')::date));
SELECT id FROM orders
WHERE (created_at AT TIME ZONE 'Asia/Taipei')::date = '2026-11-01';索引的運算式必須是 IMMUTABLE(同樣的輸入永遠得到同樣的結果)。created_at::date 對 TIMESTAMPTZ 來說取決於連線的 TimeZone 設定,所以直接拿它建索引會得到 functions in index expression must be marked IMMUTABLE 錯誤;明確指定時區之後就沒有這個問題。不過,能改寫成範圍條件的,優先改寫成範圍條件,就不需要多一個索引。
唯一索引:讓資料庫守住冪等鍵
〈冪等與分散式鎖〉提到,重試的請求要靠冪等鍵辨認,而最後一道防線是資料庫的唯一約束。假設 orders 加上一個客戶端產生的 idempotency_key 欄位,同一個使用者的同一個鍵只能建立一筆訂單:
CREATE UNIQUE INDEX CONCURRENTLY orders_user_idem_uidx
ON orders (user_id, idempotency_key);idempotency_key 是 NULL 的舊訂單不受影響:PostgreSQL 預設把多個 NULL 視為彼此不同。在已經有資料的表上加唯一索引時,還要先處理既有的重複資料,第 4 步會講。
避免重複與多餘的索引
- 完全重複:同樣的欄位、同樣的順序建了兩次,常見於不同的人在不同的遷移裡各加一次。
- 前綴多餘:已經有
(user_id, id DESC),又加一個(user_id)。前者已經可以服務只用user_id的查詢,後者只是多一份寫入成本。例外是唯一索引與主鍵,它們有約束的意義,不能只看欄位。 - 從來沒被用過:當初為某個功能加的,功能下線了,索引還在。
找出候選的查詢:
-- 從統計開始到現在,一次都沒有被掃描過的索引(不含唯一索引與主鍵)
SELECT s.indexrelid::regclass AS index_name,
s.relid::regclass AS table_name,
s.idx_scan,
s.last_idx_scan, -- PostgreSQL 16 起才有
pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
AND NOT i.indisunique
AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;
-- 鍵是另一個索引的前綴(或完全相同)的索引:只是候選,要人工確認排序方向與運算子類別
SELECT a.indrelid::regclass AS table_name,
a.indexrelid::regclass AS candidate,
b.indexrelid::regclass AS covered_by,
pg_size_pretty(pg_relation_size(a.indexrelid)) AS size
FROM pg_index a
JOIN pg_index b ON b.indrelid = a.indrelid AND b.indexrelid <> a.indexrelid
WHERE (b.indkey::text = a.indkey::text OR b.indkey::text LIKE a.indkey::text || ' %')
AND a.indpred IS NULL AND b.indpred IS NULL
AND a.indexprs IS NULL AND b.indexprs IS NULL
AND NOT a.indisunique
AND NOT a.indisprimary;看 idx_scan = 0 時有三件事要確認:統計是從什麼時候開始累計的(SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();,重啟或手動重設都可能讓數字歸零);觀察期間有沒有涵蓋月結、年度報表這種低頻但重要的工作;以及每一台唯讀副本上的統計——副本上的查詢只會算在副本自己的 pg_stat_user_indexes 裡,主庫看到 0,不代表副本上沒人用。
第 4 步:在大型線上資料表加索引而不出事
這是整篇的核心。前面設計出來的索引,要加到一張正在接單、幾千萬列的 orders 上。
為什麼一般的 CREATE INDEX 會擋住寫入
一般的 CREATE INDEX 會對資料表取得 SHARE 鎖。它和 SELECT 取得的 ACCESS SHARE 不衝突,所以讀取照常;但它和 INSERT、UPDATE、DELETE 取得的 ROW EXCLUSIVE 衝突,所以在整個建索引的期間,所有寫入都要等。小表建索引只要幾秒,沒有人察覺;一張幾千萬列的表可能要幾分鐘到幾十分鐘,下單、付款回呼、庫存扣減全部卡住,請求逾時、連線池耗盡。
更可怕的是:等鎖的 DDL 會讓後面的人一起排隊
PostgreSQL 的鎖請求是排隊的。一個 DDL 在等鎖時,之後進來、和它衝突的鎖請求,一般會排在它的後面,即使這些請求和目前持有鎖的人並不衝突。
設想一個情況:有一個跑了二十分鐘的報表查詢正在讀 orders(持有 ACCESS SHARE)。這時有人執行一個需要 ACCESS EXCLUSIVE 鎖的操作——例如不加 CONCURRENTLY 的 DROP INDEX、ALTER TABLE ... ADD CONSTRAINT,即使它本身只要幾毫秒。它和報表衝突,所以開始等;接著,每一個新的 SELECT ... FROM orders 都要 ACCESS SHARE,和正在排隊的 ACCESS EXCLUSIVE 衝突,於是排在它後面。結果是:一個本來只要幾毫秒的 DDL,讓整張表的讀寫停擺,直到那個報表跑完。
圖:長交易持有 ACCESS SHARE;等待 ACCESS EXCLUSIVE 的 DDL 排在它後面;之後所有和 DDL 衝突的讀寫又排在 DDL 後面,連線池很快就被等待的請求占滿。設了 lock_timeout,DDL 等不到鎖就自己放棄,佇列隨即消散。
一般的 CREATE INDEX 也有同樣的問題:它要的 SHARE 鎖和寫入衝突,如果有一個寫過 orders、還沒提交的交易(例如程式忘了提交,或一個卡住的批次作業),CREATE INDEX 就在等它,後面所有的寫入又排在 CREATE INDEX 後面。
解法是 lock_timeout:限制每一次取鎖最多等多久,等不到就放棄、報錯(SQLSTATE 55P03),讓佇列散掉,稍後再試。它只計算等鎖的時間,和限制整個陳述式執行時間的 statement_timeout 是兩回事。兩者都可以只在當次連線設定:
SET lock_timeout = '3s'; -- 等鎖最多 3 秒
SET statement_timeout = '60s'; -- 整個陳述式最多 60 秒官方文件也提醒,不建議把這兩個值設在 postgresql.conf,因為會影響所有連線。要在遷移的那一條連線上設定。
CREATE INDEX CONCURRENTLY:不擋寫入的建法
加上 CONCURRENTLY,PostgreSQL 改用 SHARE UPDATE EXCLUSIVE 鎖,它不和讀寫衝突,建索引的期間 INSERT、UPDATE、DELETE 都照常進行:
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_user_status_created_idx
ON orders (user_id, status, created_at DESC, id DESC)
INCLUDE (total);代價與限制要一次記清楚:
- 不能在交易區塊裡執行。
BEGIN; CREATE INDEX CONCURRENTLY ...; COMMIT;會直接報錯:CREATE INDEX CONCURRENTLY cannot run inside a transaction block。很多遷移工具預設把每個遷移檔包在交易裡,所以要確認工具有「這個遷移不要包交易」的設定。一次送出多個陳述式時,PostgreSQL 也會把它們放在同一個隱含的交易裡,結果一樣報錯——一個遷移只放一個CONCURRENTLY陳述式。 - 比較久、比較重。 它要掃描資料表兩次,中間還要等待舊的交易結束,總工作量比一般的建法多,花的時間明顯更長;多出來的 CPU 與 I/O 也可能拖慢其他查詢。
- 要等所有舊交易。 在每一次掃描之前,它要等所有已經修改過這張表的交易結束;第二次掃描之後,還要等所有快照早於第二次掃描的交易結束。一個開了兩小時沒提交的交易,就會讓
CONCURRENTLY停在那裡等兩小時。等待期間不擋一般讀寫,但遷移就卡住了。 - 同一張表同一時間只能有一個並行的索引建置。 它的
SHARE UPDATE EXCLUSIVE鎖也會和 VACUUM、ANALYZE與其他 DDL 衝突,建置期間這張表的自動清理會被延後。 - 失敗會留下 INVALID 索引。 下一節詳談。
- 分割表(partitioned table)的父表不支援。 要對每個分割區各自
CONCURRENTLY建好,最後在父表上建立(這時只是中繼資料操作)。
圖:先在系統目錄登記一個 INVALID 的索引,等待寫入者、第一次掃描建置、再等待寫入者、第二次掃描驗證、等待舊快照,最後才標記為 VALID;任何一步失敗都會留下 INVALID 索引,要先 DROP INDEX CONCURRENTLY 再重試。
失敗之後:INVALID 索引的偵測與清理
CREATE INDEX CONCURRENTLY 中途失敗——死結、唯一性衝突、被取消、lock_timeout 觸發——不會自動清掉建了一半的索引,而是留下一個 INVALID 索引。查詢不會使用它(因為它可能不完整),但每一次寫入仍然要維護它;如果是唯一索引,它甚至可能繼續檢查唯一性。在 psql 裡 \d orders 會在它後面標上 INVALID;用 SQL 查:
SELECT c.relname AS index_name,
i.indrelid::regclass AS table_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;要注意:一個正在用 CONCURRENTLY 建置中的索引,在完成之前也是 indisvalid = false。看到 INVALID 索引時,先確認沒有建置還在進行(pg_stat_progress_create_index 是空的),才判斷它是失敗的殘留。
官方建議的處理方式是刪掉再重建:
DROP INDEX CONCURRENTLY IF EXISTS orders_user_status_created_idx;
CREATE INDEX CONCURRENTLY orders_user_status_created_idx
ON orders (user_id, status, created_at DESC, id DESC)
INCLUDE (total);(也可以用 REINDEX INDEX CONCURRENTLY 直接重建這個 INVALID 索引。)
有一個很容易踩到的陷阱:CREATE INDEX CONCURRENTLY IF NOT EXISTS 看到同名的 INVALID 索引時,會以為索引已經存在而直接跳過,回報成功。遷移工具於是把這個版本記為已完成,資料表上卻只有一個沒用、還要維護的索引。所以重試之前,一定要先檢查並清掉同名的 INVALID 索引——第 5 步的 Go 程式會做這件事。
另外,lock_timeout 不只會在一開始取鎖時觸發,在 CONCURRENTLY 等待舊交易的階段也可能觸發,同樣留下 INVALID 索引(第 5 步的整合測試在 PostgreSQL 17 上重現了這個情況)。所以替 CONCURRENTLY 設 lock_timeout 時,失敗後的清理是必要的,而且這個值要比一般 DDL 長得多。
唯一索引:先找出並修正重複資料
CREATE UNIQUE INDEX CONCURRENTLY 碰到既有的重複資料會失敗,訊息是 could not create unique index,並留下 INVALID 索引。更麻煩的是:唯一性的檢查在第二次掃描開始時就已經對其他交易生效,所以建置期間(甚至建置最後失敗時),應用程式的寫入就可能先收到唯一性衝突的錯誤;如果失敗發生在第二次掃描,殘留的 INVALID 索引還會繼續檢查唯一性。
所以要先找出重複資料,決定怎麼處理(合併、保留最新的一筆,或把舊資料的鍵清成 NULL),處理完再建:
SELECT user_id, idempotency_key, count(*)
FROM orders
WHERE idempotency_key IS NOT NULL
GROUP BY user_id, idempotency_key
HAVING count(*) > 1;在建置期間,應用程式仍然可能寫入新的重複資料,所以要先讓程式在寫入前就檢查冪等鍵(或先上線「寫入時用 ON CONFLICT 處理」的版本),再建索引。
用既有的唯一索引加上約束
唯一索引和唯一約束(UNIQUE constraint)在 PostgreSQL 裡效果幾乎一樣,但約束會出現在資料表的定義裡,ORM 與 ON CONFLICT ON CONSTRAINT 也會用到。直接 ALTER TABLE ... ADD CONSTRAINT ... UNIQUE (...) 會在持有強鎖的情況下建整個索引;比較好的做法是先用 CONCURRENTLY 建好唯一索引,再把它「升級」成約束:
SET lock_timeout = '3s';
ALTER TABLE orders
ADD CONSTRAINT orders_user_idem_key UNIQUE USING INDEX orders_user_idem_uidx;這一步不需要重新掃描資料,很快就完成,但 ALTER TABLE 仍然要取得 ACCESS EXCLUSIVE 鎖,所以照樣要設 lock_timeout。索引會被改名成約束的名稱。限制是:這個索引必須是 B-tree、預設排序、沒有運算式、也不是部分索引。
執行之前:時段、空間、記憶體與副本
選離峰時段。 CONCURRENTLY 不擋寫入,但兩次全表掃描與排序會吃 CPU 與 I/O,WAL 的量也會暴增。
檢查磁碟空間。 需要的空間包括:新索引本身、排序時超過記憶體的暫存檔,以及建置期間產生的 WAL。新索引的大小可以用類似的既有索引估計(上面五百萬筆的測試資料,兩個 BIGINT 欄位的索引約 190 MB),更可靠的做法是在接近正式資料量的副本上實際建一次。
SELECT pg_size_pretty(pg_table_size('orders')) AS table_size,
pg_size_pretty(pg_indexes_size('orders')) AS indexes_size;maintenance_work_mem。 建索引的速度很大程度取決於它:越大,排序越能在記憶體裡完成。可以只在遷移的連線上調高(例如 SET maintenance_work_mem = '1GB';),但不要超過機器真正有的記憶體,否則會開始使用 swap。支援平行建置的索引(B-tree 等),這個值是整個建置所有 worker 共用的上限。
副本與複寫延遲。 在實體複寫(streaming replication)下,主庫建索引產生的 WAL 會在每台唯讀副本上重放,副本也要寫出同樣大小的索引,所以建置期間副本的複寫延遲可能上升;依賴副本讀取的功能,要能容忍這段延遲(上一篇談過複寫延遲的處理)。在主庫上觀察:
SELECT application_name, client_addr, state, replay_lag,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_behind
FROM pg_stat_replication;邏輯複寫則不會複製 DDL,訂閱端如果需要同樣的索引,要另外建。
長交易。 動手之前,先看有沒有跑很久的交易,它們會讓 CONCURRENTLY 一直等下去:
SELECT pid, now() - xact_start AS xact_age, state, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid()
ORDER BY xact_start
LIMIT 10;監看進度:pg_stat_progress_create_index
建置期間,在另一條連線上查:
SELECT p.pid,
p.phase,
round(100.0 * p.blocks_done / nullif(p.blocks_total, 0), 1) AS blocks_pct,
p.lockers_done, p.lockers_total,
p.current_locker_pid,
a.query AS waiting_on
FROM pg_stat_progress_create_index p
LEFT JOIN pg_stat_activity a ON a.pid = p.current_locker_pid;phase 會依序經過 initializing、waiting for writers before build、building index、waiting for writers before validation、index validation: scanning index、index validation: sorting tuples、index validation: scanning table、waiting for old snapshots。停在某個 waiting for ... 階段很久時,current_locker_pid 就是它正在等的那個行程,waiting_on 會顯示那個行程在做什麼——通常就是那個忘了提交的交易。
索引膨脹:REINDEX CONCURRENTLY
經過大量更新與刪除,B-tree 裡會有很多半空的頁,索引比實際需要的大,這叫膨脹(bloat)。pgstattuple 擴充套件的 pgstatindex('索引名稱') 可以看葉節點的平均密度。重建可以用:
REINDEX INDEX CONCURRENTLY orders_user_id_idx;它同樣不能在交易裡執行、同樣比較慢。失敗時會留下名稱加上 _ccnew 或 _ccold 的 INVALID 索引:_ccnew 是建到一半的新索引,刪掉之後重試;_ccold 代表新索引已經建好、只是舊的沒刪成,直接刪掉它即可。
安全地刪除索引
一般的 DROP INDEX 要對資料表取得 ACCESS EXCLUSIVE 鎖,正是前面「等鎖的 DDL 讓所有人排隊」的那種操作。刪除要用:
SET lock_timeout = '3s';
DROP INDEX CONCURRENTLY IF EXISTS orders_user_only_idx;它同樣不能在交易裡執行、一次只能刪一個索引;支撐 UNIQUE 或 PRIMARY KEY 約束的索引不能用這種方式刪除(那也通常不該刪)。
刪之前:
- 確認主庫和每一台副本上的
idx_scan在足夠長的觀察期內都沒有增加。 - 用
pg_get_indexdef把建立語法存下來,當作退場計畫:
SELECT pg_get_indexdef('orders_user_only_idx'::regclass);
-- CREATE INDEX orders_user_only_idx ON public.orders USING btree (user_id)刪錯了,就用這個語法加上 CONCURRENTLY 建回來——但在幾千萬列的表上,「建回來」本身要花不少時間,這段期間依賴它的查詢會很慢。所以刪除要比新增更謹慎。
第 5 步:用 Go 寫一個安全的遷移程式
上面的規則很多,最好寫進遷移程式裡,讓每一次上線都自動遵守,而不是靠執行的人記得。這個遷移程式要做到:
- 偵測必須在交易外執行的陳述式(
CREATE INDEX CONCURRENTLY、DROP INDEX CONCURRENTLY、REINDEX ... CONCURRENTLY),不要包交易,而且一個遷移只能有一個陳述式。 - 其他遷移包在交易裡,用
SET LOCAL設定lock_timeout與statement_timeout,只在這個交易內有效。 - 所有設定都在同一條連線上:
*sql.DB是連線池,對它下SET只會落在池裡的某一條連線上,下一個陳述式可能換了一條連線,設定就不見了。 - 鎖逾時(SQLSTATE
55P03)就用指數退避加抖動重試,次數有上限。 - 重試
CONCURRENTLY之前,先清掉上一次失敗留下的同名 INVALID 索引,避免被IF NOT EXISTS跳過。 - 用 advisory lock 確保同一時間只有一個遷移程式在執行——多個 Pod 同時啟動時,不會有兩個程式同時建同一個索引,也不會誤刪另一個程式正在建置中的索引。
程式沿用〈database/sql、交易與連線池〉的做法:業務程式碼只依賴 database/sql,驅動程式是 github.com/jackc/pgx/v5/stdlib;這裡另外用到 pgx 的 pgconn.PgError 來讀 SQLSTATE。
先是不碰資料庫、可以單獨測試的部分:判斷陳述式、取出索引名稱、辨認鎖逾時與計算退避時間。
internal/platform/migrate/statement.go
package migrate
import (
"errors"
"math/rand/v2"
"regexp"
"strings"
"time"
"github.com/jackc/pgx/v5/pgconn"
)
var (
lineComment = regexp.MustCompile(`--[^\n]*`)
// 這三種陳述式不能在交易區塊裡執行。
noTx = regexp.MustCompile(`(?is)^\s*(CREATE\s+(UNIQUE\s+)?INDEX\s+CONCURRENTLY|DROP\s+INDEX\s+CONCURRENTLY|REINDEX\b.*\bCONCURRENTLY)\b`)
// shortcut: 只認得沒有加引號的名稱;要用引號名稱時再擴充。
concurrentIndex = regexp.MustCompile(`(?is)^\s*CREATE\s+(?:UNIQUE\s+)?INDEX\s+CONCURRENTLY\s+(?:IF\s+NOT\s+EXISTS\s+)?([\w.]+)\s+ON\b`)
)
func stripComments(stmt string) string {
return strings.TrimSpace(lineComment.ReplaceAllString(stmt, ""))
}
// NeedsNoTx 回報這段 SQL 是否必須在交易之外執行。
func NeedsNoTx(stmt string) bool {
return noTx.MatchString(stripComments(stmt))
}
// IndexName 從 CREATE INDEX CONCURRENTLY 取出索引名稱,用來在重試前清掉失敗留下的 INVALID 索引。
func IndexName(stmt string) (string, bool) {
m := concurrentIndex.FindStringSubmatch(stripComments(stmt))
if m == nil {
return "", false
}
return m[1], true
}
// statementCount 粗略計算陳述式數量。
// shortcut: 不處理字串與 $$ 裡的分號;遷移檔裡出現函式定義時改用真正的 SQL 解析器。
func statementCount(sql string) int {
n := 0
for part := range strings.SplitSeq(stripComments(sql), ";") {
if strings.TrimSpace(part) != "" {
n++
}
}
return n
}
// lockNotAvailable 是 lock_timeout 觸發時的 SQLSTATE。
const lockNotAvailable = "55P03"
// IsLockTimeout 判斷錯誤是不是「等鎖等太久」。
func IsLockTimeout(err error) bool {
var pgErr *pgconn.PgError
return errors.As(err, &pgErr) && pgErr.Code == lockNotAvailable
}
// Backoff 回傳第 attempt 次(從 1 開始)失敗後要等多久:指數成長、不超過 maxDelay,
// 並在後半段加上隨機抖動,避免多個實例同時重試。
func Backoff(attempt int, base, maxDelay time.Duration) time.Duration {
d := maxDelay
if attempt < 1 {
attempt = 1
}
if attempt <= 30 && base<<(attempt-1) < maxDelay {
d = base << (attempt - 1)
}
return d/2 + rand.N(d/2+1)
}Backoff 的抖動取在 [d/2, d] 之間:多個實例同時遇到鎖逾時,不會在同一個時間點一起重試。statementCount 是刻意簡化過的:它不認得字串或 $$ 裡的分號,註解裡寫明了這個限制與什麼時候該升級。
接著是執行遷移的部分:
internal/platform/migrate/migrate.go
// Package migrate 依序套用資料庫遷移:交易內的遷移有 statement_timeout,
// CONCURRENTLY 陳述式在交易外執行,每一步都帶著 lock_timeout,等不到鎖就退避重試。
package migrate
import (
"context"
"database/sql"
"errors"
"fmt"
"log/slog"
"strings"
"time"
"github.com/jackc/pgx/v5"
)
type Migration struct {
Version int64
Name string
SQL string
}
type Options struct {
LockTimeout time.Duration // 一般 DDL 每一次取鎖最多等多久,建議幾秒
StatementTimeout time.Duration // 交易內遷移的時間上限;CONCURRENTLY 陳述式不設上限
// ConcurrentLockTimeout 給 CONCURRENTLY 陳述式用:它們要等所有舊交易結束,
// 鎖本身不擋一般讀寫,所以可以等久一點。
ConcurrentLockTimeout time.Duration
MaxAttempts int
BaseDelay time.Duration
MaxDelay time.Duration
}
// advisoryKey 是任意固定的數字:同一時間只讓一個遷移程式執行。
const advisoryKey = 240001
func Run(ctx context.Context, db *sql.DB, opts Options, ms []Migration) error {
if opts.LockTimeout <= 0 || opts.ConcurrentLockTimeout <= 0 || opts.MaxAttempts < 1 {
return errors.New("migrate: lock timeouts and MaxAttempts must be set")
}
// SET 只對一條連線有效,所以整個過程固定用同一條連線,而不是直接用連線池。
// 這條連線還回池子時仍帶著下面的設定,所以 db 只給遷移程式用,不要和服務共用。
conn, err := db.Conn(ctx)
if err != nil {
return err
}
defer conn.Close()
if _, err := conn.ExecContext(ctx, `SELECT pg_advisory_lock($1)`, advisoryKey); err != nil {
return err
}
defer conn.ExecContext(context.WithoutCancel(ctx), `SELECT pg_advisory_unlock($1)`, advisoryKey)
if _, err := conn.ExecContext(ctx,
fmt.Sprintf(`SET lock_timeout = %d`, opts.LockTimeout.Milliseconds())); err != nil {
return err
}
if _, err := conn.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS schema_migrations (
version BIGINT PRIMARY KEY,
applied_at TIMESTAMPTZ NOT NULL DEFAULT now())`); err != nil {
return err
}
for _, m := range ms {
var done bool
if err := conn.QueryRowContext(ctx,
`SELECT EXISTS (SELECT 1 FROM schema_migrations WHERE version = $1)`, m.Version,
).Scan(&done); err != nil {
return err
}
if done {
continue
}
if err := applyWithRetry(ctx, conn, opts, m); err != nil {
return fmt.Errorf("migration %d %s: %w", m.Version, m.Name, err)
}
slog.Info("migration applied", "version", m.Version, "name", m.Name)
}
return nil
}
func applyWithRetry(ctx context.Context, conn *sql.Conn, opts Options, m Migration) error {
for attempt := 1; ; attempt++ {
err := apply(ctx, conn, opts, m)
if err == nil || !IsLockTimeout(err) || attempt >= opts.MaxAttempts {
return err
}
wait := Backoff(attempt, opts.BaseDelay, opts.MaxDelay)
slog.Warn("lock timeout, retrying", "version", m.Version, "attempt", attempt, "wait", wait)
select {
case <-time.After(wait):
case <-ctx.Done():
return ctx.Err()
}
}
}
func apply(ctx context.Context, conn *sql.Conn, opts Options, m Migration) error {
if NeedsNoTx(m.SQL) {
return applyNoTx(ctx, conn, opts, m)
}
tx, err := conn.BeginTx(ctx, nil)
if err != nil {
return err
}
defer tx.Rollback()
// SET LOCAL 只在這個交易內有效,交易結束就恢復。
if _, err := tx.ExecContext(ctx,
fmt.Sprintf(`SET LOCAL lock_timeout = %d`, opts.LockTimeout.Milliseconds())); err != nil {
return err
}
if _, err := tx.ExecContext(ctx,
fmt.Sprintf(`SET LOCAL statement_timeout = %d`, opts.StatementTimeout.Milliseconds())); err != nil {
return err
}
if _, err := tx.ExecContext(ctx, m.SQL); err != nil {
return err
}
if _, err := tx.ExecContext(ctx,
`INSERT INTO schema_migrations (version) VALUES ($1)`, m.Version); err != nil {
return err
}
return tx.Commit()
}
func applyNoTx(ctx context.Context, conn *sql.Conn, opts Options, m Migration) error {
// 一次送出多個陳述式時,PostgreSQL 會把它們包在同一個隱含交易裡,CONCURRENTLY 就會失敗。
if statementCount(m.SQL) != 1 {
return errors.New("a CONCURRENTLY migration must contain exactly one statement")
}
// 建索引可能要跑很久,不設 statement_timeout;等鎖與等舊交易仍然有上限。
if _, err := conn.ExecContext(ctx,
fmt.Sprintf(`SET lock_timeout = %d`, opts.ConcurrentLockTimeout.Milliseconds())); err != nil {
return err
}
if _, err := conn.ExecContext(ctx, `SET statement_timeout = 0`); err != nil {
return err
}
if name, ok := IndexName(m.SQL); ok {
if err := dropInvalid(ctx, conn, name); err != nil {
return err
}
}
if _, err := conn.ExecContext(ctx, m.SQL); err != nil {
return err
}
// 建好之後才記錄版本;中間當掉的話會重跑,所以遷移要寫成 IF NOT EXISTS。
_, err := conn.ExecContext(ctx,
`INSERT INTO schema_migrations (version) VALUES ($1)`, m.Version)
return err
}
// dropInvalid 清掉上一次失敗留下、同名的 INVALID 索引;
// 否則 CREATE INDEX CONCURRENTLY IF NOT EXISTS 會以為索引已經存在而直接跳過。
func dropInvalid(ctx context.Context, conn *sql.Conn, name string) error {
var invalid bool
err := conn.QueryRowContext(ctx,
`SELECT NOT indisvalid FROM pg_index WHERE indexrelid = to_regclass($1)`, name,
).Scan(&invalid)
if errors.Is(err, sql.ErrNoRows) || (err == nil && !invalid) {
return nil
}
if err != nil {
return err
}
slog.Warn("dropping invalid index left by a failed build", "index", name)
_, err = conn.ExecContext(ctx,
`DROP INDEX CONCURRENTLY IF EXISTS `+pgx.Identifier(strings.Split(name, ".")).Sanitize())
return err
}幾個重點:
- 兩種逾時分開設。 一般 DDL 的
LockTimeout要短(幾秒),因為它們等鎖時會讓後面的讀寫排隊;CONCURRENTLY的鎖不擋一般讀寫,但要等所有舊交易結束,所以ConcurrentLockTimeout給長一點(例如一分鐘),statement_timeout則設為 0,建索引要多久就跑多久。 SET LOCAL只在交易內有效,交易結束就恢復,不會影響之後的遷移。交易外的SET會一直留在這條連線上,所以applyNoTx每次都重新設定,而這個*sql.DB也只給遷移程式用。- 先建索引、再記版本。
CONCURRENTLY不在交易裡,「建索引」和「寫入schema_migrations」沒辦法一起成功或一起失敗。如果剛好在兩者之間當掉,下次會重跑同一個遷移,所以遷移本身要寫成IF NOT EXISTS,而dropInvalid保證IF NOT EXISTS不會被 INVALID 的殘留騙過。 - advisory lock 是 PostgreSQL 提供的應用程式層級鎖,和資料表無關;
pg_advisory_lock會等到拿到為止,連線關閉時也會自動釋放。
最後是組裝根。遷移程式是一個獨立的執行檔,在部署流程裡、新版服務啟動之前執行(例如 Kubernetes 的 Job,〈在 Kubernetes 部署整套微服務〉談過這種做法):
cmd/migrate/main.go
package main
import (
"context"
"database/sql"
"log/slog"
"os"
"os/signal"
"time"
_ "github.com/jackc/pgx/v5/stdlib"
"example.com/shop/internal/platform/migrate"
)
var migrations = []migrate.Migration{
{Version: 24001, Name: "orders_pending_created_idx", SQL: `
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_pending_created_idx
ON orders (created_at) WHERE status = 'pending'`},
{Version: 24002, Name: "orders_user_status_created_idx", SQL: `
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_user_status_created_idx
ON orders (user_id, status, created_at DESC, id DESC)`},
}
func main() {
ctx, stop := signal.NotifyContext(context.Background(), os.Interrupt)
defer stop()
db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
if err != nil {
slog.Error("open database", "err", err)
os.Exit(1)
}
defer db.Close()
err = migrate.Run(ctx, db, migrate.Options{
LockTimeout: 3 * time.Second,
StatementTimeout: time.Minute,
ConcurrentLockTimeout: time.Minute,
MaxAttempts: 8,
BaseDelay: 2 * time.Second,
MaxDelay: time.Minute,
}, migrations)
if err != nil {
slog.Error("migrate", "err", err)
os.Exit(1)
}
}測試
不碰資料庫的部分用表格驅動測試,涵蓋大小寫、換行、註解裡出現關鍵字、包裝過的錯誤,以及退避時間的上下限:
internal/platform/migrate/statement_test.go
package migrate
import (
"fmt"
"testing"
"time"
"github.com/jackc/pgx/v5/pgconn"
)
func TestNeedsNoTx(t *testing.T) {
tests := []struct {
sql string
want bool
}{
{"CREATE INDEX CONCURRENTLY orders_pending_idx ON orders (created_at)", true},
{"create unique index concurrently if not exists k ON orders (idempotency_key)", true},
{"-- 先建索引\nCREATE INDEX\n CONCURRENTLY x ON orders (total)", true},
{"DROP INDEX CONCURRENTLY IF EXISTS orders_user_only_idx", true},
{"REINDEX INDEX CONCURRENTLY orders_user_id_idx", true},
{"CREATE INDEX orders_total_idx ON orders (total)", false},
{"ALTER TABLE orders ADD COLUMN note TEXT", false},
{"-- CREATE INDEX CONCURRENTLY 以後再做\nALTER TABLE orders ADD COLUMN note TEXT", false},
}
for _, tt := range tests {
if got := NeedsNoTx(tt.sql); got != tt.want {
t.Errorf("NeedsNoTx(%q) = %v, want %v", tt.sql, got, tt.want)
}
}
}
func TestIndexName(t *testing.T) {
tests := []struct {
sql string
want string
wantOK bool
}{
{"CREATE INDEX CONCURRENTLY orders_pending_idx ON orders (created_at)", "orders_pending_idx", true},
{"CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS shop.orders_key ON shop.orders (k)", "shop.orders_key", true},
{"DROP INDEX CONCURRENTLY orders_pending_idx", "", false},
{"CREATE INDEX orders_total_idx ON orders (total)", "", false},
}
for _, tt := range tests {
got, ok := IndexName(tt.sql)
if got != tt.want || ok != tt.wantOK {
t.Errorf("IndexName(%q) = %q, %v; want %q, %v", tt.sql, got, ok, tt.want, tt.wantOK)
}
}
}
func TestStatementCount(t *testing.T) {
if n := statementCount("CREATE INDEX CONCURRENTLY a ON t (x);\n-- done;\n"); n != 1 {
t.Errorf("got %d, want 1", n)
}
if n := statementCount("CREATE INDEX CONCURRENTLY a ON t (x); CREATE INDEX CONCURRENTLY b ON t (y);"); n != 2 {
t.Errorf("got %d, want 2", n)
}
}
func TestIsLockTimeout(t *testing.T) {
lock := fmt.Errorf("apply: %w", &pgconn.PgError{Code: "55P03"})
cancelled := &pgconn.PgError{Code: "57014"} // statement_timeout
if !IsLockTimeout(lock) {
t.Error("wrapped 55P03 should be a lock timeout")
}
if IsLockTimeout(cancelled) || IsLockTimeout(nil) {
t.Error("only 55P03 is a lock timeout")
}
}
func TestBackoff(t *testing.T) {
base, maxDelay := 500*time.Millisecond, 30*time.Second
for attempt := 1; attempt <= 40; attempt++ {
ceil := min(base<<min(attempt-1, 30), maxDelay)
for range 100 {
d := Backoff(attempt, base, maxDelay)
if d < ceil/2 || d > ceil {
t.Fatalf("attempt %d: %v outside [%v, %v]", attempt, d, ceil/2, ceil)
}
}
}
}真正的行為——INVALID 索引的殘留、清理後重建、等待舊交易時逾時並重試、一般 DDL 等不到鎖就放棄——只有對著真的 PostgreSQL 才測得出來。這個整合測試在沒有設定 MIGRATE_TEST_DSN 時會跳過,CI 裡可以搭配測試用的資料庫容器執行(〈測試:表格驅動、httptest、整合測試〉介紹過這種分法):
internal/platform/migrate/migrate_integration_test.go
package migrate
import (
"context"
"database/sql"
"os"
"testing"
"time"
_ "github.com/jackc/pgx/v5/stdlib"
)
// 需要一個可以隨意建表的 PostgreSQL:
// MIGRATE_TEST_DSN=postgres://... go test -race ./internal/platform/migrate/
func TestRunAgainstPostgres(t *testing.T) {
dsn := os.Getenv("MIGRATE_TEST_DSN")
if dsn == "" {
t.Skip("MIGRATE_TEST_DSN not set")
}
ctx := context.Background()
db, err := sql.Open("pgx", dsn)
if err != nil {
t.Fatal(err)
}
defer db.Close()
for _, s := range []string{
`DROP TABLE IF EXISTS mig_orders, schema_migrations`,
`CREATE TABLE mig_orders (id BIGINT PRIMARY KEY, k TEXT)`,
`INSERT INTO mig_orders VALUES (1, 'a'), (2, 'a')`, // 重複的鍵:唯一索引會建失敗
} {
if _, err := db.ExecContext(ctx, s); err != nil {
t.Fatal(err)
}
}
opts := Options{
LockTimeout: 200 * time.Millisecond,
StatementTimeout: 5 * time.Second,
ConcurrentLockTimeout: 200 * time.Millisecond,
MaxAttempts: 4,
BaseDelay: 50 * time.Millisecond,
MaxDelay: 200 * time.Millisecond,
}
unique := Migration{Version: 1, Name: "k", SQL: `CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS mig_orders_k_uidx ON mig_orders (k)`}
if err := Run(ctx, db, opts, []Migration{unique}); err == nil {
t.Fatal("unique index on duplicate keys should fail")
}
var valid bool
if err := db.QueryRowContext(ctx,
`SELECT indisvalid FROM pg_index WHERE indexrelid = to_regclass('mig_orders_k_uidx')`,
).Scan(&valid); err != nil || valid {
t.Fatalf("want an INVALID leftover, got valid=%v err=%v", valid, err)
}
// 修好資料再跑一次:殘留的 INVALID 索引要被清掉並重建,而不是被 IF NOT EXISTS 跳過。
if _, err := db.ExecContext(ctx, `UPDATE mig_orders SET k = 'b' WHERE id = 2`); err != nil {
t.Fatal(err)
}
if err := Run(ctx, db, opts, []Migration{unique}); err != nil {
t.Fatal(err)
}
if err := db.QueryRowContext(ctx,
`SELECT indisvalid FROM pg_index WHERE indexrelid = to_regclass('mig_orders_k_uidx')`,
).Scan(&valid); err != nil || !valid {
t.Fatalf("want a valid index, got valid=%v err=%v", valid, err)
}
// 一個還沒結束的寫入交易:CONCURRENTLY 會等它,逾時後清掉 INVALID 索引再重試,
// 交易結束之後就建得起來。
writer, err := db.BeginTx(ctx, nil)
if err != nil {
t.Fatal(err)
}
if _, err := writer.ExecContext(ctx, `INSERT INTO mig_orders VALUES (3, 'c')`); err != nil {
t.Fatal(err)
}
time.AfterFunc(300*time.Millisecond, func() { _ = writer.Rollback() })
byID := Migration{Version: 2, Name: "id k", SQL: `CREATE INDEX CONCURRENTLY IF NOT EXISTS mig_orders_id_k_idx ON mig_orders (id, k)`}
if err := Run(ctx, db, opts, []Migration{byID}); err != nil {
t.Fatalf("should succeed after the writer finishes: %v", err)
}
// 另一個交易拿著鎖不放:一般 DDL 在 lock_timeout 後放棄、重試,最後回報鎖逾時。
holder, err := db.BeginTx(ctx, nil)
if err != nil {
t.Fatal(err)
}
defer holder.Rollback()
if _, err := holder.ExecContext(ctx, `LOCK TABLE mig_orders IN ACCESS SHARE MODE`); err != nil {
t.Fatal(err)
}
drop := Migration{Version: 3, Name: "drop k", SQL: `ALTER TABLE mig_orders DROP COLUMN k`}
if err := Run(ctx, db, opts, []Migration{drop}); !IsLockTimeout(err) {
t.Fatalf("want a lock timeout, got %v", err)
}
}執行:
go vet ./...
go test -race ./...
MIGRATE_TEST_DSN='postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable' \
go test -race ./internal/platform/migrate/整合測試的日誌會看到預期中的事情依序發生:第一次建唯一索引失敗、清掉 INVALID 索引後重建成功;CONCURRENTLY 等舊交易逾時、重試後成功;ALTER TABLE 等不到鎖,重試三次後回報鎖逾時。
提示:如果團隊用的是現成的遷移工具,先確認三件事:能不能讓單一遷移不包交易、能不能設定每個遷移的
lock_timeout與statement_timeout、失敗後會不會檢查 INVALID 索引。前兩項多數工具都有設定方式;第三項常常沒有,要自己在部署流程裡補上一個檢查。
第 6 步:MySQL(InnoDB)有什麼不同
觀念大致相通,細節不同。以下以 MySQL 8.0 之後的 InnoDB 為準。
線上 DDL。 InnoDB 新增或刪除次要索引時,可以「就地」(in-place)進行,不重建資料表,建置期間允許並行的讀寫。建議明確寫出想要的演算法與鎖定等級:
ALTER TABLE orders
ADD INDEX orders_user_status_created_idx (user_id, status, created_at),
ALGORITHM=INPLACE, LOCK=NONE;明確指定的好處是:如果這個操作做不到 LOCK=NONE,MySQL 會直接報錯,而不是默默改用會鎖表的方式。建置期間並行的寫入會先記在一份線上變更紀錄裡,最後再套用;寫入量很大時,這份紀錄超過上限(innodb_online_alter_log_max_size)會讓操作失敗。並不是所有的索引操作都能這樣做:例如新增第一個 FULLTEXT 索引、或更動主鍵,可能要重建資料表或不允許並行寫入,要查官方文件中每種操作的對照表。
中繼資料鎖(metadata lock)是經典的事故原因。 線上 DDL 在開始與結束時,仍然需要短暫取得資料表的排他中繼資料鎖。任何還沒結束、碰過這張表的交易(即使只是 SELECT)都持有共享的中繼資料鎖,DDL 必須等它結束;而正在等待的排他中繼資料鎖,會擋住之後所有對這張表的查詢。這和 PostgreSQL 的鎖佇列是同一種事故:SHOW PROCESSLIST 會看到一長串 Waiting for table metadata lock。對策也一樣,給等待設上限:
SET SESSION lock_wait_timeout = 5; -- 秒;預設值非常長performance_schema.metadata_locks 可以查出是誰持有鎖、誰在等。
副本的複寫延遲。 在傳統的非同步複寫中,DDL 在來源端完成之後才會寫進二進位日誌,副本收到後再執行一次,大約要花同樣長的時間,這段期間後面的變更可能都要等它,複寫延遲就一路上升。大表的結構變更要把這一段算進去。
線上改表工具。 對於線上 DDL 做不到、或想更精細控制負載與切換時機的變更,常用的是 gh-ost 與 pt-online-schema-change。兩者的共同概念是:建立一張新結構的影子表、把資料分批複製過去、同時把複製期間的變更也同步過去,最後用改名完成切換。差別在同步方式:pt-online-schema-change 在原表上建觸發器,gh-ost 讀二進位日誌、不使用觸發器。它們都需要額外一份資料表的空間,切換的那一刻仍然要取得短暫的鎖,所以前面講的鎖等待上限一樣適用。
其他差異。 MySQL 沒有部分索引,也沒有 INCLUDE;InnoDB 的次要索引本身就帶著主鍵欄位,查詢只用到索引欄位加主鍵時可以直接從索引回答。8.0 起支援遞減索引與函式索引(functional key parts)。8.0 也提供隱藏索引(invisible index):ALTER TABLE orders ALTER INDEX orders_user_only_idx INVISIBLE; 讓最佳化器不再使用它,但仍然維護資料,觀察一段時間沒問題再真的刪除,有問題立刻改回 VISIBLE——這是刪除索引時很好用的退場方式,PostgreSQL 沒有內建的對應功能。隱含轉型在 MySQL 一樣致命:VARCHAR 欄位拿去和數字比較時,會把欄位轉成數字,索引就用不到了。
第 7 步:上線前檢查表、退場計畫與一個事故時間軸
圖:從證據出發、設計並在接近正式資料量的環境預演,執行前逐項檢查,離峰時段以 CONCURRENTLY 加上逾時執行並監看進度與複寫延遲,最後確認計畫真的用到新索引;任何一步不符合預期,就照退場計畫處理。
上線前檢查表
- 證據:有
pg_stat_statements或慢查詢紀錄指出要改善的查詢,並且留下加索引前的EXPLAIN (ANALYZE, BUFFERS)。 - 設計:欄位順序符合「等值在前、範圍與排序在後」;確認不和既有索引重複或成為前綴;評估對寫入與 HOT 更新的影響。
- 預演:在接近正式資料量的副本上建一次,記下花了多久、索引多大、計畫是否如預期。
- 語法:使用
CONCURRENTLY與IF NOT EXISTS;遷移不包交易、只有一個陳述式。 - 逾時:遷移連線設好
lock_timeout(一般 DDL 幾秒);CONCURRENTLY的statement_timeout不設上限或給足時間。 - 長交易:
pg_stat_activity沒有跑很久的交易;有的話先處理。 - 空間:磁碟足以容納新索引、排序暫存檔與 WAL;副本也一樣。
- 時段:選離峰時段,並通知值班的人。
- 唯一索引:已經找出並處理重複資料,應用程式寫入時也已經會檢查。
- 監看:準備好
pg_stat_progress_create_index、pg_stat_replication與服務的延遲、錯誤率儀表板。 - 驗證:完成後確認索引是 VALID,並用
EXPLAIN確認查詢真的用到它。
退場計畫
- 建置中要中止:取消那個陳述式(
SELECT pg_cancel_backend(pid);),接著確認並清掉留下的 INVALID 索引(DROP INDEX CONCURRENTLY)。 - 建好之後讓寫入變慢:新索引增加了寫入成本、或讓某個查詢的計畫變差時,用
DROP INDEX CONCURRENTLY刪除;先準備好lock_timeout。 - 刪除索引後查詢變慢:用事先存下的
pg_get_indexdef語法加上CONCURRENTLY建回來,並知道這要花多少時間(預演時量過)。MySQL 可以先設成INVISIBLE觀察,把刪除的風險降到最低。 - 副本延遲過大:建置本身沒辦法暫停,但可以把依賴副本的讀取暫時改走主庫或降級,這個開關要事先準備好。
一個沒設 lock_timeout 的事故時間軸(虛構的示意)
以下是一個虛構的情境,用來說明鎖佇列怎麼在幾秒內把服務拖垮。時間以分:秒表示:
| 時間 | 發生了什麼 |
|---|---|
| 00:00 | 營運人員在主庫上執行一個月結報表,查詢 orders 預計要跑 15 分鐘,持有 ACCESS SHARE 鎖。 |
| 03:00 | 部署流程執行遷移:清掉一個沒用的索引,寫的是 DROP INDEX orders_user_only_idx;,沒有 CONCURRENTLY,也沒有設 lock_timeout。 |
| 03:00 | DROP INDEX 需要 ACCESS EXCLUSIVE,和報表衝突,開始等待。 |
| 03:01 | 之後每一個讀寫 orders 的請求,都排在等待中的 DROP INDEX 後面。order 服務的連線池在一兩秒內被等待中的查詢占滿。 |
| 03:05 | api-gateway 對 order 的呼叫開始逾時;重試讓請求量翻倍。依賴 orders 的 payment 回呼也開始失敗。 |
| 03:30 | 健康檢查會查資料庫,order 的 Pod 被判定為不健康而重啟,重啟後的新連線同樣卡在佇列裡。 |
| 09:00 | 值班的人從 pg_stat_activity 看到大量 wait_event_type = Lock 的連線,順著 pg_blocking_pids() 找到 DROP INDEX 與背後的報表。 |
| 09:30 | 取消報表(或取消 DROP INDEX),DROP INDEX 在幾毫秒內完成或放棄,佇列消散,服務在一兩分鐘內恢復。 |
同樣的遷移,如果寫成 SET lock_timeout = '3s'; DROP INDEX CONCURRENTLY ...:CONCURRENTLY 本身就不會取得擋住讀寫的鎖;就算是一般的 DDL,它也只會讓後面的請求多等 3 秒,就自己放棄、報錯,遷移程式稍後重試。最壞的結果從「整個下單停擺六分鐘以上」,變成「有幾秒的延遲上升,遷移晚一點完成」。事後的改善也很明確:報表移到唯讀副本執行、遷移程式強制設 lock_timeout、在 CI 裡檢查不帶 CONCURRENTLY 的 CREATE INDEX 與 DROP INDEX。
找出誰擋住誰的查詢,值班時很有用:
SELECT pid,
pg_blocking_pids(pid) AS blocked_by,
wait_event_type,
now() - query_start AS waiting_for,
left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0
ORDER BY query_start;常見陷阱
- 憑感覺加索引。 沒有
pg_stat_statements與EXPLAIN (ANALYZE, BUFFERS)的證據,加了可能用不到,只多了寫入成本。 - 加完不驗證。 沒有再跑一次
EXPLAIN,不知道規劃器到底有沒有用新索引。 - 複合索引順序放錯。 範圍條件的欄位放在等值條件前面,後面的欄位就幫不上忙;排序方向和
ORDER BY不一致,還要額外排序。 - 在欄位上套函式或轉型。
created_at::date = ...、和不同型別比較,索引都用不到。 - 替低選擇性或經常更新的欄位加完整索引。 規劃器不會用,卻讓更新失去 HOT。考慮部分索引。
- 在大表上直接
CREATE INDEX。 整段建置期間寫入全部被擋住。 - 遷移工具把
CONCURRENTLY包進交易。 直接報錯;或一次送出多個陳述式,同樣報錯。 - 不設
lock_timeout。 一個等鎖的 DDL 讓整張表的讀寫排隊,連線池耗盡。 - 忽略 INVALID 索引。 失敗的殘留繼續消耗寫入成本;
IF NOT EXISTS還會因為它而跳過重建。 - 唯一索引前沒處理重複資料。 建置失敗,而且建置期間應用程式可能先收到唯一性衝突。
- 只看主庫的
idx_scan就刪索引。 副本上的查詢只算在副本的統計裡。 - 沒算磁碟與副本。 索引、暫存檔與 WAL 把磁碟寫滿,或副本延遲讓讀取到過舊的資料。
- 用
DROP INDEX而不是DROP INDEX CONCURRENTLY。 刪除需要ACCESS EXCLUSIVE,比新增更容易引發鎖佇列。
重點整理
- B-tree 擅長等值、範圍與排序;每個索引都有寫入放大、儲存、快取、VACUUM 與 HOT 更新的成本。選擇性低、小表、對欄位套函式或轉型、前置萬用字元與 OR,都可能讓索引幫不上忙。
- 用證據決定:
pg_stat_statements依總時間找出最重的查詢,慢查詢紀錄與auto_explain補上參數,EXPLAIN (ANALYZE, BUFFERS)由內往外讀,看實際列數、loops、Rows Removed by Filter與Buffers;加完再驗證一次。 - 複合索引:等值在前、範圍與排序在後,排序方向與鍵集分頁的
ORDER BY一致;INCLUDE做覆蓋索引;部分索引只索引需要的部分;運算式索引要IMMUTABLE;唯一索引守住冪等鍵;定期找出沒用、重複與前綴多餘的索引,並檢查每一台副本。 - 一般的
CREATE INDEX會擋住寫入;等鎖中的 DDL 會讓後面的讀寫排隊。所有 DDL 都要設lock_timeout。 CREATE INDEX CONCURRENTLY不擋讀寫,但不能在交易裡、要掃兩次、要等舊交易、失敗會留下 INVALID 索引;重試前先DROP INDEX CONCURRENTLY,小心IF NOT EXISTS被殘留騙過。唯一索引先處理重複資料,再用ADD CONSTRAINT ... USING INDEX升級成約束。- 執行前檢查長交易、磁碟、
maintenance_work_mem與副本延遲;執行中用pg_stat_progress_create_index監看;膨脹用REINDEX CONCURRENTLY,刪除用DROP INDEX CONCURRENTLY並先存下建立語法。 - Go 的遷移程式把規則寫死:固定一條連線、CONCURRENTLY 不包交易且只有一個陳述式、
SET LOCAL設逾時、鎖逾時退避重試、清理 INVALID 殘留、advisory lock 防止並行執行;純邏輯用表格測試,行為用對真資料庫的整合測試驗證。 - MySQL:
ALGORITHM=INPLACE, LOCK=NONE明確指定,中繼資料鎖的等待是經典事故,設lock_wait_timeout;大表變更要算副本延遲,必要時用gh-ost或pt-online-schema-change;隱藏索引讓刪除可以退回。
下一篇
索引是讓查詢變快最便宜的方法,但它不會讓資料變少。訂單表一年比一年大,索引也跟著長大,大部分的查詢只碰最近幾個月,備份、VACUUM 與每一次加索引卻都要處理全部的資料。下一篇〈冷熱資料與分庫分表:資料長大之後怎麼拆〉接著往下走:先把冷熱資料分開、把舊資料歸檔,真的需要時再垂直拆庫、水平分片,並且在不停機的情況下搬過去。