管理大型 Postgres 資料庫的 3 個技巧

在處理龐大的資料庫時，請嘗試這些方便的解決方案，以解決常見的問題。

關係型資料庫 PostgreSQL（也被稱為 Postgres）已經越來越流行，全球各地的企業和公共部門都在使用它。隨著這種廣泛的採用，資料庫已經變得比以前更大了。在 Crunchy Data，我們經常與 20TB 以上的資料庫打交道，而且我們現有的資料庫還在繼續增長。我的同事 David Christensen 和我收集了一些關於管理擁有巨大表的資料庫的技巧。

大表

生產資料庫通常由許多具有不同數據、大小和模式的表組成。常見的情況是，最終有一個巨大的、無序的資料庫表，遠遠大於你資料庫中的任何其他表。這個表經常存儲活動日誌或有時間戳的事件，而且對你的應用或用戶來說是必要的。

真正的大表會因為很多原因造成挑戰，但一個常見的原因是鎖。對錶的定期維護往往需要鎖，但對大表的鎖可能會使你的應用癱瘓，或導致堵塞和許多令人頭痛的問題。我有一些做基本維護的技巧，比如添加列或索引，同時避免長期運行的鎖。

添加索引的問題：在創建索引的過程中鎖住表。如果你有一個龐大的表，這可能需要幾個小時。

CREATE INDEX ON customers (last_name)

方案：使用 CREATE INDEX CONCURRENTLY 功能。這種方法將索引創建分成兩部分，一部分是短暫的鎖定，以創建索引，立即開始跟蹤變化，但盡量減少應用阻塞，然後是完全建立該索引，之後查詢可以開始使用它。

CREATE INDEX CONCURRENTLY ON customers (last_name)

添加列

在資料庫的使用過程中，添加列是一個常見的請求，但是對於一個巨大的表來說，這可能是很棘手的，同樣是由於鎖的問題。

問題：當你添加一個新的默認值為一個函數的列時，Postgres 需要重寫表。對於大表，這可能需要幾個小時。

方案：將操作拆分為多條基本語句，總效果一致，但控制鎖的時間。

添加列：

ALTER TABLE all_my_exes ADD COLUMN location text

添加默認值：

ALTER TABLE all_my_exes ALTER COLUMN location SET DEFAULT texas()

使用 UPDATE 來添加默認值：

UPDATE all_my_exes SET location = DEFAULT

添加約束條件

問題: 你想添加一個用於數據驗證的檢查約束。但是如果你使用直接的方法來添加約束，它將鎖定表，同時驗證表中的所有現有數據。另外，如果在驗證的任何時候出現錯誤，它將回滾。

ALTER TABLE favorite_bands ADD CONSTRAINT name_check CHECK (name = 'Led Zeppelin')

方案：告訴 Postgres 這個約束，但不要驗證它。在第二步中進行驗證。這將在第一步中進行短暫的鎖定，確保所有新的/修改過的行都符合約束條件，然後在另一步驟中進行驗證，以確認所有現有的數據都通過約束條件。

告訴 Postgres 這個約束，但不要強制執行它：

ALTER TABLE favorite_bands ADD CONSTRAINT name_check CHECK (name = 'Led Zeppelin') NOT VALID

然後在創建後驗證它：

ALTER TABLE favorite_bands VALIDATE CONSTRAINT name_check

想了解更多？

David Christensen 和我將在 3 月 9 號到 10 到在加州帕薩迪納參加 SCaLE 的 Postgres Days。很多來自 Postgres 社區的優秀人士也會在那裡。加入我們吧！

via: https://opensource.com/article/23/2/manage-large-postgres-databases

作者：Elizabeth Garrett Christensen 選題：lkxed 譯者：geekpi 校對：wxy

本文由 LCTT 原創編譯，Linux中國 榮譽推出

本文轉載來自 Linux 中國: https://github.com/Linux-CN/archive