[infra] PostgreSQL Advanced：效能、可靠性與可維運性

cover

PostgreSQL 很強，但「裝好就不管」的 PostgreSQL 不會陪你走太遠。當資料量從百萬級成長到千萬、億級，那些你忽略的索引設計、vacuum 策略、連線管理，會一個一個回來找你算帳。

先講結論

PostgreSQL 的進階維運有四件事比什麼都重要：索引要對、查詢要 EXPLAIN、vacuum 不能關、連線要用 pool。把這四件事做好，PostgreSQL 撐到相當大的規模都不會有問題。

索引：不是越多越好

索引加速讀取、拖慢寫入，這個 trade-off 每個人都知道。但知道跟做對是兩回事。

-- 常見的複合索引，注意欄位順序
CREATE INDEX idx_orders_user_status
ON orders (user_id, status);
 
-- 降序索引，用在 ORDER BY DESC 的場景
CREATE INDEX idx_orders_created_at
ON orders (created_at DESC);

幾個原則：

只對 高選擇性 的欄位建索引——性別欄位建索引沒什麼意義
B-Tree 是預設也是最常用的，GIN 用在陣列跟全文
複合索引的欄位順序很重要——(user_id, status) 跟 (status, user_id) 效能差很多

最常犯的錯是：上線前忘了建索引，然後某天流量一大，資料庫開始全表掃描，CPU 飆到 100%。

EXPLAIN ANALYZE：你的查詢地圖

慢查詢是 PostgreSQL 效能問題的第一元兇。怎麼找到慢查詢？先裝 pg_stat_statements，它會記錄所有查詢的執行統計。

找到可疑的查詢之後，用 EXPLAIN ANALYZE：

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

重點看什麼：

出現 Seq Scan 在大表上？缺索引
Rows Removed by Filter 很高？索引不夠精確
Nested Loop 在大表上？可能需要改寫 JOIN

不要猜效能問題，讓 EXPLAIN 告訴你答案。

分割表：大表的求生之道

當一張表有幾億筆資料，索引本身都變成負擔。這時候需要分割表（Partitioning）。

CREATE TABLE events (
  id bigserial,
  created_at date NOT NULL,
  payload jsonb
) PARTITION BY RANGE (created_at);
 
CREATE TABLE events_2024_09
PARTITION OF events
FOR VALUES FROM ('2024-09-01') TO ('2024-10-01');

分割的前提是：你的查詢模式可預測。如果大部分查詢都帶 created_at 範圍，用時間分割就很有效。如果查詢模式千變萬化，分割反而可能更慢。

Vacuum：PostgreSQL 的命脈

PostgreSQL 的 MVCC 機制會產生 dead tuples（已刪除但還沒清理的資料）。Autovacuum 就是負責清理這些屍體的清潔隊。

如果你把 autovacuum 調太保守，或者有長交易阻塞 vacuum，dead tuples 會堆積到影響效能，甚至導致 transaction ID wraparound——這是一個會讓整個 DB 停止寫入的災難。

不要關 autovacuum。大表需要調整 autovacuum 的 scale factor。定期監控 dead tuples 的數量。

連線池：pgbouncer 救你一命

PostgreSQL 的每個連線都是一個獨立 process，成本很高。如果你的 app 開了 200 個連線，PostgreSQL 就跑 200 個 process。

pgbouncer 在 app 跟 PostgreSQL 之間做連線池：

[databases]
app_db = host=127.0.0.1 port=5432 dbname=app
 
[pgbouncer]
max_client_conn = 1000
pool_mode = transaction
default_pool_size = 50

pool_mode = transaction 是最常用的——每個交易結束後，連線歸還到池裡給其他 client 用。1000 個 app 連線只需要 50 個 DB 連線就能跑。

~~不用 pgbouncer 的 PostgreSQL 就像不穿防彈衣上戰場。~~

Terry Yao's Blog

分類

目錄