PostgreSQL 效能調校實戰

資料庫效能往往是系統瓶頸所在。本文分享我在實際專案中優化 PostgreSQL 的經驗。

問題診斷

識別慢查詢

首先要找出問題所在:

-- 開啟慢查詢日誌
ALTER SYSTEM SET log_min_duration_statement = 1000;
SELECT pg_reload_conf();

-- 查看最耗時的查詢
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

使用 EXPLAIN ANALYZE

分析查詢執行計畫:

EXPLAIN ANALYZE
SELECT * FROM users
WHERE created_at > NOW() - INTERVAL '1 month';

索引優化

選擇正確的索引類型

不同場景需要不同的索引:

B-Tree 索引

最常用的索引類型:

CREATE INDEX idx_users_email ON users(email);

適用於:

相等性查詢
範圍查詢
排序操作

GIN 索引

用於全文搜尋和 JSONB:

CREATE INDEX idx_articles_content_gin 
ON articles USING GIN (to_tsvector('english', content));

部分索引

只索引符合條件的資料:

CREATE INDEX idx_active_users 
ON users(created_at) 
WHERE is_active = true;

複合索引設計

順序很重要:

-- 好的複合索引
CREATE INDEX idx_orders_user_date 
ON orders(user_id, created_at DESC);

-- 可以服務這些查詢:
-- WHERE user_id = X
-- WHERE user_id = X ORDER BY created_at DESC

查詢優化技巧

避免 SELECT *

只選擇需要的欄位:

-- 差
SELECT * FROM users WHERE id = 1;

-- 好
SELECT id, email, name FROM users WHERE id = 1;

使用 JOIN 而非子查詢

某些情況下 JOIN 更快:

-- 可能較慢
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE active = true);

-- 通常更快
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.active = true;

分頁優化

避免使用 OFFSET:

-- 慢(特別是大 offset)
SELECT * FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

-- 快(使用游標)
SELECT * FROM articles
WHERE created_at < '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

系統配置優化

記憶體設定

根據可用記憶體調整:

-- shared_buffers: 系統記憶體的 25%
ALTER SYSTEM SET shared_buffers = '4GB';

-- effective_cache_size: 系統記憶體的 50-75%
ALTER SYSTEM SET effective_cache_size = '12GB';

-- work_mem: 根據併發連線數調整
ALTER SYSTEM SET work_mem = '64MB';

連線池

使用 pgBouncer 或應用層連線池:

減少連線開銷
控制資料庫負載
提高資源利用率

監控與維護

定期 VACUUM

自動清理死亡元組:

-- 手動 VACUUM
VACUUM ANALYZE users;

-- 設定自動 VACUUM
ALTER TABLE users SET (autovacuum_vacuum_scale_factor = 0.1);

統計資料更新

保持統計資料最新:

ANALYZE users;

實戰案例

案例:文章列表慢查詢

問題:文章列表頁面載入需要 3 秒

解決方案:

加入複合索引
只選擇列表需要的欄位
使用游標分頁
加入 Redis 快取

結果:載入時間降至 50ms

總結

效能優化是個持續的過程:

先測量再優化:不要盲目優化
索引不是萬能:過多索引會拖慢寫入
定期維護:VACUUM 和 ANALYZE 很重要
監控告警:及早發現問題

記住:過早優化是萬惡之源,但適時優化能帶來巨大收益。