・在 activity 表放 seats_taken 計數欄位、靠 UPDATE 維護 → 熱門活動併發下實測比無爭用慢 14 倍,並產生 31,726 個 dead tuple。
・改用兩層:Redis 原子計數(熱讀顯示)+ DB COUNT(active)(權威 + 重建)。
・用 98 萬列壓測:COUNT(active) 走複合索引,小活動 1.15ms、超大活動(2.4 萬 active)76ms,全程無 Seq Scan。
・通用教訓:「會不會 scale」是量出來的,不是讀 schema 猜的。
問題與懷疑
一個醫師社群平台的活動報名模組需要同時做到兩件事:即時顯示剩餘名額,以及防超賣(報名人數絕不能超過活動 capacity)。
最直觀的設計:在 activity 表加一個 seats_taken 欄位,每次報名 UPDATE seats_taken = seats_taken + 1、取消 -1。直觀、SQL 好寫。但工程師的經驗法則是:熱列 UPDATE 是計數的地雷。問題是,「聽說不好」和「有數據說不好」是兩件事。
另一條路線是「Redis 原子計數當熱讀 + DB COUNT() 當權威」。直覺傾向這個,但同樣不確定它在量大時撐不撐得住——尤其大活動累積了幾萬筆歷史 cancelled 紀錄之後。所以我們不靠猜,種資料、跑 EXPLAIN ANALYZE,讓查詢計畫說話。
方法論:為什麼靠讀 schema 猜是錯的
「會不會 scale」是經驗問題,不是靜態分析問題:索引在 schema 上「看起來對」,runtime 可能因型別轉換、查詢條件結構、或 MVCC 可見性確認被繞過;COUNT() 的真實成本取決於符合列數、索引選擇性、buffer 命中——種入真實量級資料前都是謎。
① 種入接近真實量級的資料(不是 1000 列的玩具資料)
② 對真實查詢路徑跑 EXPLAIN (ANALYZE, BUFFERS)
③ 讀計畫形狀,而不只看時間數字
④ 對照「壞設計」與「好設計」的差異資料規模
| 維度 | 數值 |
|---|---|
| 一般活動數量 | 3,000 個 |
| 每個一般活動的報名列數 | ~300 列 |
| 熱門活動(單一) | 80,000 列 |
| 其中已取消(cancelled) | ~56,000 列(70%) |
| 有效佔位(occupying)列數 | ~24,015 列 |
| 合計 registration 列數 | ~98 萬列 |
70% 的 cancelled 比例不是亂設的——它模擬 append-only 設計的現實:取消不刪列、留著當稽核紀錄,加上重複報名與名額釋出再搶,歷史積累很快壓過實際在場人數。索引:(activity_id, status) 複合索引。
量測 A:COUNT(active) 在真實量下要多久?
SELECT COUNT(*) FROM registration
WHERE activity_id = $1 AND status IN ('pending','awaiting_payment','approved');| 情境 | active 列數 | 總列數 | 執行時間 | 計畫 |
|---|---|---|---|---|
| 一般小活動 | ~94 | ~300 | 1.15 ms | Bitmap Index Scan |
| 熱門大活動 | 24,015 | 80,000 | ~76 ms | Bitmap Index Scan |
熱門活動三次 warm run:76.1 / 76.1 / 76.3 ms,非常穩定。計畫細節很重要:全程 Bitmap Index Scan on idx(activity_id,status),Index Cond 同時吃 activity_id 與 status,沒有 Seq Scan。Bitmap Index Scan 本身只花約 1.1ms 找到 24,015 筆;76ms 主要花在 Bitmap Heap Scan 的可見性 recheck(COUNT 要確認每列對當前交易可見,MVCC 的代價),buffers 全是 shared hit(在記憶體,不是磁碟 IO)。
量測 B:熱列 UPDATE 的鎖爭用
驗證「為什麼不能用計數欄位」:8 個並發 client 同時對同一列做計數更新,對照組是對不同列更新(無爭用)。
UPDATE activity_counter SET n = n + 1 WHERE id = $HOT_ID; -- 同一列,模擬 seats_taken| 實驗 | 操作次數 | 總耗時 | 倍率 |
|---|---|---|---|
| 同一列(熱門活動計數欄位) | 16,000 次 | 5.05 秒 | 基準 |
| 不同列(無爭用) | 16,000 次 | 0.36 秒 | 快 ~14× |
同列爭用比無爭用慢 ~14 倍:PostgreSQL 的 row-level lock 讓 8 個連線排隊序列化。再看 MVCC 副作用——16,000 次 UPDATE 後 pg_stat_user_tables:
| 指標 | 數值 |
|---|---|
| dead tuples | 31,726 |
| live tuples | 9 |
每次 UPDATE 產生一個 dead tuple,熱計數欄位以驚人速度積累死列,持續給 autovacuum 施壓並造成表膨脹(table bloat)——它不會在測試期間爆發,但會在上線後第六個月讓你頭痛。
採用的架構:兩層設計 + 刻意的非對稱排序
INCR、取消 DECR。L2 — DB COUNT(active)(權威真理 + 重建來源,~76ms 僅在快取 miss):走 (activity_id,status) 索引。
| 事件 | 操作順序 | 理由 |
|---|---|---|
| 報名 | 先 INCR Redis,再 INSERT DB | 並發者立刻看到槽位被佔,防超收 |
| 取消 | 先 UPDATE DB,再 DECR Redis | 槽位等 DB 真的釋放才放出來 |
非對稱排序是刻意的保守設計——寧願暫時低估可用名額,也不要超賣。顯示端短暫「少一個位子」是可接受的 UX 瑕疵;多賣一個位子是業務問題。快取 miss(TTL 到期或 Redis 重啟)時才從 DB COUNT() 重建——這是 76ms 唯一被付出的時機。事件驅動更新,不輪詢;TTL 讓即使一直在用的熱 key 也會週期性過期重建,把漂移收斂回權威值。
誠實的 Caveat
append-only(取消保留紀錄)的代價是:要得出「有效人數」必須
WHERE status IN (occupying) 過濾掉歷史 cancelled 列。超大活動(8 萬列、2.4 萬 active)的 COUNT 實測 76ms,不是理想的 1ms。但這個代價只在快取 miss 時才付——熱讀走 Redis 是 sub-ms,正常操作使用者感受不到。若未來真有極大活動且 COUNT 進入熱路徑,可加 partial index 把它壓到 ~2ms:
CREATE INDEX idx_registration_active
ON registration (activity_id)
WHERE status IN ('pending','awaiting_payment','approved');但我們現在不加——這是 write-hot 表,每個活躍報名都要維護它,而目前沒有任何真實使用資料顯示需要它。「等真實資料出現再決定」是正確的態度。
結論
| 問題 | 答案 |
|---|---|
| 可以在 activity 表放計數欄位嗎? | 不建議。14× 鎖爭用 + 大量 dead tuple 有實測背書 |
| DB COUNT() 能撐住大活動嗎? | 可以,76ms 走索引、不走 Seq Scan,但不能放熱路徑 |
| Redis + DB 兩層是過度設計嗎? | 不是。L1 顯示、L2 防超賣+重建,職責清晰 |
| append-only 歷史列會讓 COUNT 爆炸嗎? | 不會,複合索引把過濾吸收在 Index Cond 層 |
「會不會 scale」是經驗測出來的,不是讀 schema 想出來的。種 ~100 萬列、跑 EXPLAIN ANALYZE 看真實查詢路徑,10 分鐘就有可以展示的數字,而不是「我覺得應該可以」的猜測。
- 熱列 UPDATE 在並發下一定慢:row-level lock 把並發序列化;並發越高倍率越誇張。
- MVCC dead tuple 是隱性成本:現在看不到(測試資料小),上線後才爆(大流量 vacuum 來不及)。
- 非對稱排序是分散式計數的安全網:報名先佔位、取消後釋放——刻意的業務保守設計,值得寫進 ADR。
發佈留言