This resume is also available in English.

六、實戰部署第 4 / 5 篇 · 全部目錄

專欄 › 六、實戰部署 › 第 24 篇

正確加索引:大型資料表上線不出事

Adding Indexes Safely: Indexing Large Tables Without an Outage

在正在接單的大型資料表上加索引,做錯一步就會擋住寫入、拖垮整個服務。這篇從原理與 EXPLAIN 的證據出發,設計出正確的索引,再用 CONCURRENTLY、lock_timeout 與可重試的 Go 遷移程式安全上線。

  • 資料庫
  • PostgreSQL
  • 索引
  • Go
這篇的步驟
  1. 這篇要解決什麼
  2. 第 1 步:索引怎麼運作,懂到能做判斷就好
  3. 第 2 步:用證據找出該加的索引
  4. 第 3 步:把索引設計對
  5. 第 4 步:在大型線上資料表加索引而不出事
  6. 第 5 步:用 Go 寫一個安全的遷移程式
  7. 第 6 步:MySQL(InnoDB)有什麼不同
  8. 第 7 步:上線前檢查表、退場計畫與一個事故時間軸
  9. 常見陷阱
  10. 重點整理
  11. 下一篇

上一篇〈從單體到微服務:十萬、百萬、千萬級的架構怎麼演進〉在十萬級那一步提到:資料庫的慢查詢,通常是少了某個索引;在加快取、加機器之前,先把索引與查詢做對,這一步花費最少、效果常常最大。這篇就把這件事做完整:什麼時候該加索引、該加什麼樣的索引,以及最容易被輕忽的一件事——怎麼在一張幾千萬列、正在接單的資料表上把索引加上去,而不讓服務停擺。

這篇要解決什麼

「查詢慢就加索引」這句話只對了一半。實際上常見的狀況是:

  • 憑感覺加了索引,查詢一樣慢,因為那個查詢根本用不到它;資料表上卻多了一個每次寫入都要維護的結構。
  • 複合索引的欄位順序放錯,只有一部分查詢用得到。
  • 在尖峰時段對大表直接執行 CREATE INDEX,整張表的寫入被擋住好幾分鐘,下單全部逾時。
  • 改用 CREATE INDEX CONCURRENTLY,遷移工具卻把它包在交易裡,直接報錯;或是建到一半失敗,留下一個沒人發現的 INVALID 索引,之後每次寫入都在白白維護它。
  • 一個看起來只要幾毫秒的 DDL 卡在等鎖,後面所有的查詢排成一條長隊,連線池在幾秒內耗盡。

這篇以系列一直使用的網路商店為例(example.com/shop,服務有 api-gateway、order、payment、inventory),資料庫是 PostgreSQL,主角是〈database/sql、交易與連線池〉建立的 orders 表。順序是:

  1. 索引怎麼運作,懂到足以做判斷就好。
  2. 用證據(pg_stat_statements、慢查詢紀錄、EXPLAIN (ANALYZE, BUFFERS))找出真正該加的索引。
  3. 把索引設計對:複合索引的欄位順序、覆蓋索引、部分索引、運算式索引、唯一索引,以及清掉沒用的索引。
  4. 核心:在大型線上資料表加索引而不出事。
  5. 用 Go 寫一個安全的遷移程式,處理交易外執行、逾時、重試與失敗殘留。
  6. MySQL(InnoDB)的差異。
  7. 上線前檢查表、退場計畫,以及一個沒設 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) 根節點 內部節點:只用來導航 內部節點 41 · paid 11-02 42 · cancelled 10-30 42 · paid 11-14 42 · paid 11-09 42 · paid 10-21 42 · pending 11-15 user_id = 42 AND status = 'paid':連續的一段 段內已依 created_at 由新到舊,LIMIT 讀夠就停 TID 資料表(heap) 資料列沒有順序;沒有可用的索引時,只能把每一頁都讀過一遍(Seq Scan)

圖:複合索引 (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

一步一步讀:

  1. 由內往外、由下往上讀。 縮排最深的節點最先執行,把結果交給上一層。這裡最底層是 Parallel Seq Scan:三個行程(一個主行程加兩個 worker)平行地掃描整張表。
  2. cost 是預估,actual 是實際。 cost=啟動成本..總成本 是規劃器用來比較計畫的抽象單位,不是毫秒;actual time=第一筆..最後一筆 才是毫秒。
  3. loops。 actual 的時間與列數是每一次迴圈的平均,要乘上 loops 才是總量。rows=1568 loops=3 代表三個行程一共找到約 4,700 筆符合的資料。
  4. 比較預估列數和實際列數。 預估 rows=2152(每個行程),實際 1568,差距不大,代表統計資料是準的。如果預估 10 筆、實際十萬筆,問題就是統計資料,而不是索引——先 ANALYZE。
  5. Rows Removed by Filter。 每個行程丟掉了約 166 萬筆,三個加起來就是整張表。為了 100 筆結果讀了五百萬筆,這是最明顯的訊號:缺一個能直接定位的索引。
  6. Buffers。 shared hit 是在共享記憶體裡找到的資料頁,read 是要另外讀進來的(可能來自作業系統快取,也可能真的讀磁碟)。四萬一千多頁、約 330 MB,就是整張表。
  7. 最上層的 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。

加索引前 加上部分索引後 Limit(100 筆) Gather Merge ← Sort 每個行程先排序,再合併 Parallel Seq Scan on orders 三個行程讀完整張表 Rows Removed by Filter ≈ 5,000,000 Buffers ≈ 42,110 頁(約 330 MB) Execution Time ≈ 89 ms 為了 100 筆讀了整張表 Limit(100 筆) Index Scan using orders_pending_created_idx 索引只含 status = 'pending' 已依 created_at 排好,不必排序 讀到第 100 筆就停 Buffers = 3 頁 Execution Time ≈ 0.03 ms 索引本身只有 128 kB

圖:同一個查詢,加索引前是平行循序掃描整張表、篩掉約五百萬筆再排序;加上部分索引後直接依序讀出 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;

決定欄位順序的規則:

  1. 等值條件的欄位放最前面(user_id、status)。等值條件把範圍縮成索引裡連續的一段。
  2. 接著放範圍條件或排序的欄位(created_at DESC)。在前面欄位都相等的那一段裡,資料已經依這個欄位排好,排序與 LIMIT 可以直接利用。
  3. 範圍條件之後的欄位幫助有限。 一旦某個欄位用的是範圍條件,後面的欄位在索引裡就不再是連續的,只能逐筆過濾。

所以索引是 (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: 0

Heap 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,讓整張表的讀寫停擺,直到那個報表跑完。

orders 資料表的鎖佇列 已持有鎖 跑了 15 分鐘的報表 ACCESS SHARE 等待中的 DDL 例如一般的 DROP INDEX 要 ACCESS EXCLUSIVE 之後的每個請求 新的讀取與寫入 和等待中的 DDL 衝突 等它結束 排在 DDL 後面 連線池占滿 請求逾時、重試加倍 沒有 lock_timeout:DDL 一直等到報表結束 一個只要幾毫秒的操作,讓整張表停擺好幾分鐘 SET lock_timeout = '3s' DDL 等了 3 秒還拿不到鎖就放棄並報錯(55P03),排在後面的請求立刻繼續 遷移程式稍後退避重試;能用 CONCURRENTLY 的,就不要取得會擋讀寫的鎖

圖:長交易持有 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 建好,最後在父表上建立(這時只是中繼資料操作)。
CREATE INDEX CONCURRENTLY 的階段 1. 登記索引 系統目錄裡先是 INVALID 2. 等待寫入者 修改過這張表的交易結束 3. 第一次掃描 building index 4. 再等待寫入者 before validation 5. 第二次掃描驗證 補上建置期間的變更 6. 等待舊快照 waiting for old snapshots 7. 標記為 VALID 任何一步失敗:死結、唯一性衝突、被取消、lock_timeout 指令報錯,但建了一半的索引留下來 INVALID 索引:查詢不用它,寫入仍要維護它 IF NOT EXISTS 還會因為它而跳過重建 清理後重試 查 pg_index.indisvalid DROP INDEX CONCURRENTLY 再 CREATE ... CONCURRENTLY 注意:建置中的索引同樣是 INVALID,先確認沒有建置在進行

圖:先在系統目錄登記一個 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 約束的索引不能用這種方式刪除(那也通常不該刪)。

刪之前:

  1. 確認主庫和每一台副本上的 idx_scan 在足夠長的觀察期內都沒有增加。
  2. 用 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 步:上線前檢查表、退場計畫與一個事故時間軸

安全加索引的流程 1. 證據 pg_stat_statements EXPLAIN (ANALYZE, BUFFERS) 2. 設計 欄位順序、部分、覆蓋 不和既有索引重複 3. 預演 接近正式資料量的副本 記下時間、大小、計畫 4. 執行前檢查 長交易、磁碟、WAL 重複資料、離峰時段 5. 執行 CONCURRENTLY、不包交易 lock_timeout、退避重試 6. 監看 progress_create_index 副本延遲、錯誤率 7. 驗證 索引是 VALID EXPLAIN 真的用到它 退場計畫(任何一步不符合預期) 建置中:pg_cancel_backend,再清掉 INVALID 索引 寫入變慢或計畫變差:DROP INDEX CONCURRENTLY 刪錯索引:用 pg_get_indexdef 存下的語法建回 副本延遲過大:讀取暫時改走主庫或降級 MySQL:先設成 INVISIBLE 再刪除

圖:從證據出發、設計並在接近正式資料量的環境預演,執行前逐項檢查,離峰時段以 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:00DROP INDEX 需要 ACCESS EXCLUSIVE,和報表衝突,開始等待。
03:01之後每一個讀寫 orders 的請求,都排在等待中的 DROP INDEX 後面。order 服務的連線池在一兩秒內被等待中的查詢占滿。
03:05api-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 與每一次加索引卻都要處理全部的資料。下一篇〈冷熱資料與分庫分表:資料長大之後怎麼拆〉接著往下走:先把冷熱資料分開、把舊資料歸檔,真的需要時再垂直拆庫、水平分片,並且在不停機的情況下搬過去。