關(guān)系型數(shù)據(jù)庫 PostgreSQL（也被稱為 Postgres）已經(jīng)越來越流行，全球各地的企業(yè)和公共部門都在使用它。隨著這種廣泛的采用，數(shù)據(jù)庫已經(jīng)變得比以前更大了。在 Crunchy Data，我們經(jīng)常與 20TB 以上的數(shù)據(jù)庫打交道，而且我們現(xiàn)有的數(shù)據(jù)庫還在繼續(xù)增長。我的同事 David Christensen 和我收集了一些關(guān)于管理擁有巨大表的數(shù)據(jù)庫的技巧。

大表

生產(chǎn)數(shù)據(jù)庫通常由許多具有不同數(shù)據(jù)、大小和模式的表組成。常見的情況是，最終有一個巨大的、無序的數(shù)據(jù)庫表，遠(yuǎn)遠(yuǎn)大于你數(shù)據(jù)庫中的任何其他表。這個表經(jīng)常存儲活動日志或有時間戳的事件，而且對你的應(yīng)用或用戶來說是必要的。

真正的大表會因?yàn)楹芏嘣蛟斐商魬?zhàn)，但一個常見的原因是鎖。對表的定期維護(hù)往往需要鎖，但對大表的鎖可能會使你的應(yīng)用癱瘓，或?qū)е露氯驮S多令人頭痛的問題。我有一些做基本維護(hù)的技巧，比如添加列或索引，同時避免長期運(yùn)行的鎖。

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

CREATE INDEX ON customers (last_name)

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

CREATE INDEX CONCURRENTLY ON customers (last_name)

添加列

在數(shù)據(jù)庫的使用過程中，添加列是一個常見的請求，但是對于一個巨大的表來說，這可能是很棘手的，同樣是由于鎖的問題。

問題：當(dāng)你添加一個新的默認(rèn)值為一個函數(shù)的列時，Postgres 需要重寫表。對于大表，這可能需要幾個小時。

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

添加列：

ALTER TABLE all_my_exes ADD COLUMN location text

添加默認(rèn)值：

ALTER TABLE all_my_exes ALTER COLUMN location SET DEFAULT texas()

使用 ??UPDATE?? 來添加默認(rèn)值：

UPDATE all_my_exes SET location = DEFAULT

添加約束條件

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

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

方案：告訴 Postgres 這個約束，但不要驗(yàn)證它。在第二步中進(jìn)行驗(yàn)證。這將在第一步中進(jìn)行短暫的鎖定，確保所有新的/修改過的行都符合約束條件，然后在另一步驟中進(jìn)行驗(yàn)證，以確認(rèn)所有現(xiàn)有的數(shù)據(jù)都通過約束條件。

告訴 Postgres 這個約束，但不要強(qiáng)制執(zhí)行它：

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

然后在創(chuàng)建后驗(yàn)證它：

ALTER TABLE favorite_bands VALIDATE CONSTRAINT name_check

想了解更多？

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