解鎖七大技巧，避免你的 Postgres 掛掉
1.永遠不要添加帶默認值的列
從 PostgreSQL 11 開始，添加帶默認值的列不再重寫表。本博文中的其他提示仍然有效！
PostgreSQL 的黃金法則是：在生產中向表添加列時，千萬不要指定默認值。
添加列會對表加上鎖，從而阻塞讀寫。如果添加的列有默認值，PostgreSQL 會重寫整個表，為每一行填寫默認值，這對大型表來說可能需要數小時。在此期間，所有查詢都會阻塞，數據庫將不可用。
不要這樣做：
-- 阻塞讀寫一直到完全重寫（以小時計）
ALTER TABLE items ADD COLUMN last_update timestamptz DEFAULT now();改用這個方法：
-- 阻塞查詢，更新，插入，刪除直到 catalog 被更新 (毫秒計)
ALTER TABLE items ADD COLUMN last_update timestamptz;
-- 查詢，插入可以執行，一些更新和刪除在重寫表時，會被阻塞
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 審核規則：
圖片
2.當心鎖隊列，使用 lock timeouts （鎖超時）
在 PostgreSQL 中，每個鎖都有一個隊列。如果事務 B 試圖獲取一個已經被事務 A 持有的有沖突的鎖，那么事務 B 將會在鎖隊列中等待。現在有趣的是：如果另一個事務 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 參數，如果 DDL 命令因為等待鎖而阻塞查詢超過 2 秒，該命令將會失敗。這樣做的缺點是 ALTER TABLE 可能不會成功，但可以稍后再試。在開始 DDL 命令之前，建議先查詢 pg_stat_activity，查看是否有長時間運行的查詢。
3.CONCURRENTLY （并行地）創建索引
PostgreSQL 的另一條黃金法則是：始終并行地創建索引。
在大型數據集上創建索引可能需要數小時甚至數天，而常規的 CREATE INDEX 命令會在命令執行期間阻止所有寫入操作。雖然不會阻塞 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);并發地創建索引有一個缺點。如果出了問題，它不會回滾，而是留下一個未完成（invalid）的索引。如果出現這種情況，不用擔心，只需運行 DROP INDEX CONCURRENTLY items_value_idx，然后再嘗試創建一次即可。
譯者按，在 Bytebase 中對應的是這條 SQL 審核規則：
圖片
4.盡可能晚地獲取高級別的鎖
當需要運行命令獲取表上高級別的鎖時，應盡量在事務的較晚階段執行，以允許查詢盡可能長時間地進行。例如，如果要完全替換表的內容。不要這樣做：
BEGIN;
-- 阻塞讀寫:
TRUNCATE items;
-- 長時間操作:
\COPY items FROM 'newdata.csv' WITH CSV
COMMIT;取而代之的是，將數據加載到新表中，然后替換舊表：
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 表時，它可能已經發生了變化。為了防止出現這種情況，我們可以顯式鎖表，阻止寫入，但不阻止讀?。?/p>
BEGIN;
LOCK items IN EXCLUSIVE MODE;
...有時，自己動手加鎖更為保險。
5.添加主鍵并盡量減少加鎖
在表中添加主鍵通常是個好主意。例如，當你想使用邏輯復制遷移數據庫時。
Postgres 讓使用 ALTER TABLE 創建主鍵變得非常簡單，但在為主鍵建立索引的過程中（如果表很大，可能需要很長時間），所有查詢都會被阻塞。
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; -- 會阻塞查詢，但很快通過將創建主鍵分解為兩個步驟，幾乎不會對用戶造成影響。
6.永遠不要使用 VACUUM FULL
Postgres 的用戶體驗有時會讓人吃驚。雖然 VACUUM FULL 聽起來像是清除數據庫灰塵的命令，但更合適的命令應該是：請凍結我的數據庫數小時。
PLEASE FREEZE MY DATABASE FOR HOURS;VACUUM FULL
VACUUM FULL雖然你應該調整 AUTO VACUUM 設置并使用索引來加快查詢速度，但你可能有時需要運行 VACUUM，而不是 VACUUM FULL。
7.通過重排指令避免死鎖
如果你已經使用 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.當并發事務以不同順序獲取相同鎖時，就會出現這種情況。例如，一個事務發出以下命令。
BEGIN;
UPDATE items SET counter = counter + 1 WHERE key = 'hello'; -- 在 hello 上加鎖
UPDATE items SET counter = counter + 1 WHERE key = 'world'; -- 一邊阻塞 hello，一邊等著 world
END;與此同時，另一個事務可能會發出相同的命令，但順序不同。
BEGIN
UPDATE items SET counter = counter + 1 WHERE key = 'world'; -- 在 world 上加鎖
UPDATE items SET counter = counter + 1 WHERE key = 'hello'; -- 一邊阻塞 world，一邊等著 hello
END;如果這些事務同時運行，它們很可能會互相卡住，永遠無法完成。Postgres 會在一秒鐘左右后識別出這種情況，并取消其中一個事務，讓另一個事務完成。出現這種情況時，你應該檢查一下自己的應用程序，看看能否讓事務始終按照相同的順序進行。如果兩個事務都先修改 hello，再修改 world，那么第一個事務就會在搶到其他鎖之前阻塞第二個事務的 hello 鎖。
