1.永遠(yuǎn)不要添加帶默認(rèn)值的列

從 PostgreSQL 11 開始，添加帶默認(rèn)值的列不再重寫表。本博文中的其他提示仍然有效！

PostgreSQL 的黃金法則是：在生產(chǎn)中向表添加列時(shí)，千萬不要指定默認(rèn)值。

添加列會對表加上鎖，從而阻塞讀寫。如果添加的列有默認(rèn)值，PostgreSQL 會重寫整個(gè)表，為每一行填寫默認(rèn)值，這對大型表來說可能需要數(shù)小時(shí)。在此期間，所有查詢都會阻塞，數(shù)據(jù)庫將不可用。

不要這樣做：

-- 阻塞讀寫一直到完全重寫（以小時(shí)計(jì)）
ALTER TABLE items ADD COLUMN last_update timestamptz DEFAULT now();

改用這個(gè)方法：

-- 阻塞查詢，更新，插入，刪除直到 catalog 被更新 (毫秒計(jì))
ALTER TABLE items ADD COLUMN last_update timestamptz;
-- 查詢，插入可以執(zhí)行，一些更新和刪除在重寫表時(shí)，會被阻塞
UPDATE items SET last_update = now();

或者更好的辦法是，通過小批量更新來避免長時(shí)間阻塞更新和刪除，例如：

do {
  numRowsUpdated = executeUpdate(
    "UPDATE items SET last_update = ? " +
    "WHERE ctid IN (SELECT ctid FROM items WHERE last_update IS NULL LIMIT 5000)",
    now);
} while (numRowsUpdate > 0);

這樣，您就可以在盡量不影響用戶的情況下添加和填充新列。

譯者按，在 Bytebase 中對應(yīng)的是這條 SQL 審核規(guī)則：

圖片

2.當(dāng)心鎖隊(duì)列，使用 lock timeouts （鎖超時(shí)）

在 PostgreSQL 中，每個(gè)鎖都有一個(gè)隊(duì)列。如果事務(wù) B 試圖獲取一個(gè)已經(jīng)被事務(wù) A 持有的有沖突的鎖，那么事務(wù) B 將會在鎖隊(duì)列中等待?，F(xiàn)在有趣的是：如果另一個(gè)事務(wù) C 加入，它不僅需要檢查與事務(wù) A 的沖突，還需要檢查與事務(wù) B 以及鎖隊(duì)列中其他所有事務(wù)的沖突。

這意味著即使你的 DDL 命令可以非?？焖俚剡\(yùn)行，它也可能在隊(duì)列中等待很長時(shí)間，因?yàn)樾枰却渌樵兺瓿桑⑶以谒髥拥牟樵儗凰枞?/p>

如果表上可能長時(shí)間運(yùn)行 SELECT 查詢，就不要這樣做：

ALTER TABLE items ADD COLUMN last_update timestamptz;

而應(yīng)該這樣做：

SET lock_timeout TO '2s'
ALTER TABLE items ADD COLUMN last_update timestamptz;

通過設(shè)置 lock_timeout 參數(shù)，如果 DDL 命令因?yàn)榈却i而阻塞查詢超過 2 秒，該命令將會失敗。這樣做的缺點(diǎn)是 ALTER TABLE 可能不會成功，但可以稍后再試。在開始 DDL 命令之前，建議先查詢 pg_stat_activity，查看是否有長時(shí)間運(yùn)行的查詢。

3.CONCURRENTLY （并行地）創(chuàng)建索引

PostgreSQL 的另一條黃金法則是：始終并行地創(chuàng)建索引。

在大型數(shù)據(jù)集上創(chuàng)建索引可能需要數(shù)小時(shí)甚至數(shù)天，而常規(guī)的 CREATE INDEX 命令會在命令執(zhí)行期間阻止所有寫入操作。雖然不會阻塞 SELECT，但這仍然很糟糕，而且還有更好的方法：CREATE INDEX CONCURRENTLY。

不要這樣做：

-- 阻塞所有寫
CREATE INDEX items_value_idx ON items USING GIN (value jsonb_path_ops);

而應(yīng)這樣做：

-- 只阻塞其他 DDL 操作
CREATE INDEX CONCURRENTLY items_value_idx ON items USING GIN (value jsonb_path_ops);

并發(fā)地創(chuàng)建索引有一個(gè)缺點(diǎn)。如果出了問題，它不會回滾，而是留下一個(gè)未完成（invalid）的索引。如果出現(xiàn)這種情況，不用擔(dān)心，只需運(yùn)行 DROP INDEX CONCURRENTLY items_value_idx，然后再嘗試創(chuàng)建一次即可。

譯者按，在 Bytebase 中對應(yīng)的是這條 SQL 審核規(guī)則：

圖片

4.盡可能晚地獲取高級別的鎖

當(dāng)需要運(yùn)行命令獲取表上高級別的鎖時(shí)，應(yīng)盡量在事務(wù)的較晚階段執(zhí)行，以允許查詢盡可能長時(shí)間地進(jìn)行。

例如，如果要完全替換表的內(nèi)容。不要這樣做：

BEGIN;
-- 阻塞讀寫:
TRUNCATE items;
-- 長時(shí)間操作:
\COPY items FROM 'newdata.csv' WITH CSV 
COMMIT;

取而代之的是，將數(shù)據(jù)加載到新表中，然后替換舊表：

BEGIN;
CREATE TABLE items_new (LIKE items INCLUDING ALL);
-- 長時(shí)間操作:
\COPY items_new FROM 'newdata.csv' WITH CSV
-- 阻塞讀寫:
DROP TABLE items;
ALTER TABLE items_new RENAME TO items;
COMMIT;

有一個(gè)問題，我們沒有從一開始就阻止寫入，因此當(dāng)我們刪除舊的 items 表時(shí)，它可能已經(jīng)發(fā)生了變化。為了防止出現(xiàn)這種情況，我們可以顯式鎖表，阻止寫入，但不阻止讀?。?/p>

BEGIN;
LOCK items IN EXCLUSIVE MODE;
...

有時(shí)，自己動手加鎖更為保險(xiǎn)。

5.添加主鍵并盡量減少加鎖

在表中添加主鍵通常是個(gè)好主意。例如，當(dāng)你想使用邏輯復(fù)制遷移數(shù)據(jù)庫時(shí)。

Postgres 讓使用 ALTER TABLE 創(chuàng)建主鍵變得非常簡單，但在為主鍵建立索引的過程中（如果表很大，可能需要很長時(shí)間），所有查詢都會被阻塞。

ALTER TABLE items ADD PRIMARY KEY (id); -- 長時(shí)間阻塞查詢

幸運(yùn)的是，你可以先使用 CREATE UNIQUE INDEX CONCURRENTLY 完成所有繁重的工作，然后使用唯一索引作為主鍵，這是一種快速操作。

CREATE UNIQUE INDEX CONCURRENTLY items_pk ON items (id); -- 會很長，但不會阻塞查詢
ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY USING INDEX items_pk;  -- 會阻塞查詢，但很快

通過將創(chuàng)建主鍵分解為兩個(gè)步驟，幾乎不會對用戶造成影響。

6.永遠(yuǎn)不要使用 VACUUM FULL

Postgres 的用戶體驗(yàn)有時(shí)會讓人吃驚。雖然 VACUUM FULL 聽起來像是清除數(shù)據(jù)庫灰塵的命令，但更合適的命令應(yīng)該是：請凍結(jié)我的數(shù)據(jù)庫數(shù)小時(shí)。

PLEASE FREEZE MY DATABASE FOR HOURS;

VACUUM FULL
VACUUM FULL

雖然你應(yīng)該調(diào)整 AUTO VACUUM 設(shè)置并使用索引來加快查詢速度，但你可能有時(shí)需要運(yùn)行 VACUUM，而不是 VACUUM FULL。

7.通過重排指令避免死鎖

如果你已經(jīng)使用 PostgreSQL 有一段時(shí)間了，你很可能見過類似的錯(cuò)誤：

ERROR:  deadlock detected
DETAIL:  Process 13661 waits for ShareLock on transaction 45942; blocked by process 13483.
Process 13483 waits for ShareLock on transaction 45937; blocked by process 13661.

當(dāng)并發(fā)事務(wù)以不同順序獲取相同鎖時(shí)，就會出現(xiàn)這種情況。例如，一個(gè)事務(wù)發(fā)出以下命令。

BEGIN;
UPDATE items SET counter = counter + 1 WHERE key = 'hello'; -- 在 hello 上加鎖
UPDATE items SET counter = counter + 1 WHERE key = 'world'; -- 一邊阻塞 hello，一邊等著 world
END;

與此同時(shí)，另一個(gè)事務(wù)可能會發(fā)出相同的命令，但順序不同。

BEGIN
UPDATE items SET counter = counter + 1 WHERE key = 'world'; -- 在 world 上加鎖
UPDATE items SET counter = counter + 1 WHERE key = 'hello';  -- 一邊阻塞 world，一邊等著 hello
END;

如果這些事務(wù)同時(shí)運(yùn)行，它們很可能會互相卡住，永遠(yuǎn)無法完成。Postgres 會在一秒鐘左右后識別出這種情況，并取消其中一個(gè)事務(wù)，讓另一個(gè)事務(wù)完成。出現(xiàn)這種情況時(shí)，你應(yīng)該檢查一下自己的應(yīng)用程序，看看能否讓事務(wù)始終按照相同的順序進(jìn)行。如果兩個(gè)事務(wù)都先修改 hello，再修改 world，那么第一個(gè)事務(wù)就會在搶到其他鎖之前阻塞第二個(gè)事務(wù)的 hello 鎖。