PostgreSQL中文學習手冊(資料庫維護)

一、恢複磁碟空間：

    在PostgreSQL中，使用delete和update語句刪除或更新的資料行並沒有被實際刪除，而只是在舊版本資料行的物理地址上將該行的狀態置為已刪除或已到期。因此當資料表中的資料變化極為頻繁時，那麼在一段時間之後該表所佔用的空間將會變得很大，然而資料量卻可能變化不大。要解決該問題，需要定期對資料變化頻繁的資料表執行VACUUM操作。
    VACUUM命令存在兩種形式，VACUUM和VACUUM FULL，它們之間的區別見如下表格：

	無VACUUM	VACUUM	VACUUM FULL
刪除大量資料之後	只是將刪除資料的狀態置為已刪除，該空間不能記錄被重新使用。	如果刪除的記錄位於表的末端，其所佔用的空間將會被物理釋放並歸還作業系統。如果不是末端資料，該命令會將指定表或索引中被刪除資料所佔用空間重新置為可用狀態，那麼在今後有新資料插入時，將優先使用該空間，直到所有被重用的空間用完時，再考慮使用新增的磁碟頁面。	不論被刪除的資料是否處於資料表的末端，這些資料所佔用的空間都將被物理的釋放並歸還於作業系統。之後再有新資料插入時，將分配新的磁碟頁面以供使用。
執行效率		由於只是狀態置為操作，因此效率較高。	在目前的版本的PostgreSQL(v9.1)中，該命令會為指定的表或索引重建一個資料檔案，並將原有檔案中可用的資料匯入到新檔案中，之後再刪除原來的資料檔案。因此在匯入處理程序中，要求當前磁碟有更多的空間可用於此操作。由此可見，該命令的執行效率相對較低。
被刪除的資料所佔用的物理空間是否被重新規劃給作業系統。	不會	不會	會
在執行VACUUM命令時，是否可以並發執行針對該表的其他動作。		由於該操作是共用鎖定，因此可以與其他動作並行進行。	由於該操作需要在指定的表上應用排它鎖，因此在執行該操作期間，任何基於該表的操作都將被掛起，知道該操作完成。
推薦使用方式	在進行資料清空是，可以使用truncate操作，因為該操作將會物理的清空資料表，並將其所佔用的空間直接歸還於作業系統。	為了保證資料表的磁碟頁面數量能夠保持在一個相對穩定值，可以定期執行該操作，如每天或每周中資料操作相對較少的時段。	考慮到該操作的開銷，以及對其他錯誤的排斥，推薦的方式是，定期監控資料量變化較大的表，只有確認其磁碟頁面佔有量接近臨界值時，才考慮執行一次該操作。即便如此，也需要注意盡量選擇資料操作較少的時段來完成該操作。
執行後其它操作的效率	對於查詢而言，由於存在大量的磁碟頁面片段，因此效率會逐步降低。	相比於不執行任何VACUUM操作，其效率更高，但是插入的效率會有所降低。	在執行完該操作後，所有基於該表的操作效率都會得到極大的提升。

 

二、更新規劃器統計：

    PostgreSQL查詢規劃器在選擇最優路徑時，需要參照相關資料表的統計資訊用以為查詢產生最合理的規劃。這些統計是通過ANALYZE命令獲得的，你可以直接調用該命令，或者把它當做VACUUM命令裡的一個可選步驟來調用，如VACUUM ANAYLYZE table_name，該命令將會先執行VACUUM再執行ANALYZE。與回收空間(VACUUM)一樣，對資料更新頻繁的表保持一定頻度的ANALYZE，從而使該表的統計資訊始終處於相對較新的狀態，這樣對於基於該表的查詢最佳化將是極為有利的。然而對於更新並不頻繁的資料表，則不需要執行該操作。
    我們可以為特定的表，甚至是表中特定的欄位運行ANALYZE命令，這樣我們就可以根據實際情況，只對更新比較頻繁的部分資訊執行ANALYZE操作，這樣不僅可以節省統計資訊所佔用的空間，也可以提高本次ANALYZE操作的執行效率。這裡需要額外說明的是，ANALYZE是一項相當快的操作，即使是在資料量較大的表上也是如此，因為它使用了統計學上的隨機採樣的方法進行行採樣，而不是把每一行資料都讀取進來並進行分析。因此，可以考慮定期對整個資料庫執行該命令。
    事實上，我們甚至可以通過下面的命令來調整指定欄位的抽樣率，如：
    ALTER TABLE testtable ALTER COLUMN test_col SET STATISTICS 200
    注意：該值的取值範圍是0--1000，其中值越低採樣比例就越低，分析結果的準確性也就越低，但是ANALYZE命令執行的速度卻更快。如果將該值設定為-1，那麼該欄位的採樣比率將恢複到系統當前預設的採樣值，我們可以通過下面的命令擷取當前系統的預設採樣值。
    postgres=# show default_statistics_target;
     default_statistics_target
    ---------------------------
     100
    (1 row)
    從上面的結果可以看出，該資料庫的預設採樣值為100(10%)。

1 2 3 下一頁