1.永遠不要添加帶默認值的列

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

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

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

不要這樣做：

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

改用這個方法：

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

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

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 中對應的是這條 SQL 審核規(guī)則：

圖片

2.當心鎖隊列，使用 lock timeouts （鎖超時）

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

這意味著即使你的 DDL 命令可以非?？焖俚剡\行，它也可能在隊列中等待很長時間，因為需要等待其他查詢完成，并且在它之后啟動的查詢將會被它阻塞。

如果表上可能長時間運行 SELECT 查詢，就不要這樣做：

ALTER TABLE items ADD COLUMN last_update timestamptz;

而應該這樣做：

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

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

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

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

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

不要這樣做：

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

而應這樣做：

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

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

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

圖片

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

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

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

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

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

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

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

BEGIN;
LOCK items IN EXCLUSIVE MODE;
...

有時，自己動手加鎖更為保險。

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

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

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

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

幸運的是，你可以先使用 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)建主鍵分解為兩個步驟，幾乎不會對用戶造成影響。

6.永遠不要使用 VACUUM FULL

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

PLEASE FREEZE MY DATABASE FOR HOURS;

VACUUM FULL
VACUUM FULL

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

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

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

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.

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

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

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

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

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