PostgreSQL學習手冊(數據庫維護) 轉

原文：?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%)。

三、VACUUM和ANALYZE的示例：
?? ?

??? #1. 創建測試數據表。
????postgres=# CREATE TABLE testtable (i integer);
??? CREATE TABLE
????#2. 為測試表創建索引。
????postgres=# CREATE INDEX testtable_idx ON testtable(i);
??? CREATE INDEX
????#3. 創建批量插入測試數據的函數。
??? postgres=# CREATE OR REPLACE FUNCTION test_insert() returns integer AS $$
??? DECLARE
??? ??? min integer;
??? ??? max integer;
??? BEGIN
??? ??? SELECT COUNT(*) INTO min from testtable;
??? ??? max := min + 10000;
??? ??? FOR i IN min..max LOOP
??? ??????? INSERT INTO testtable VALUES(i);
??? ??? END LOOP;
??? ??? RETURN 0;
??? END;
??? $$ LANGUAGE plpgsql;
??? CREATE FUNCTION
????#4. 批量插入數據到測試表(執行四次)
????postgres=# SELECT test_insert();
??? ?test_insert
??? -------------
??? ?????????? 0
??? (1 row)
????#5. 確認四次批量插入都成功。
????postgres=# SELECT COUNT(*) FROM testtable;
??? ?count
??? -------
??? ?40004
??? (1 row)
????#6. 分析測試表，以便有關該表的統計信息被更新到PostgreSQL的系統表。
????postgres=# ANALYZE testtable;
??? ANALYZE
????#7. 查看測試表和索引當前占用的頁面數量(通常每個頁面為8k)。
????postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
??? ??? relname?? ? ? | relfilenode??? | relpages
??? ---------------+-------------+----------
??? ?testtable??????? |?????? 17601 ? |????? 157
??? ?testtable_idx? |?????? 17604?? |?????? 90
????#8. 批量刪除數據。
??? postgres=# DELETE FROM testtable WHERE i < 30000;
??? DELETE 30003
??? #9. 執行vacuum和analyze，以便更新系統表，同時為該表和索引記錄高水標記。
??? #10. 這里需要額外說明的是，上面刪除的數據均位于數據表的前部，如果刪除的是末尾部分，
??? #????? 如where i > 10000，那么在執行VACUUM ANALYZE的時候，數據表將會被物理的縮小。
??? postgres=# VACUUM ANALYZE testtable;
??? ANALYZE
????#11. 查看測試表和索引在刪除后，再通過VACUUM ANALYZE更新系統統計信息后的結果(保持不變)。
??? postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
??? ??? relname? ? ? | relfilenode???? | relpages
??? ---------------+-------------+----------
??? ?testtable??????? |?????? 17601?? |????? 157
??? ?testtable_idx? |?????? 17604?? |?????? 90
??? (2 rows)
????#12. 再重新批量插入兩次，之后在分析該表以更新其統計信息。
??? postgres=# SELECT test_insert(); --執行兩次。
??? ?test_insert
??? -------------
??? ?????????? 0
??? (1 row)
??? postgres=# ANALYZE testtable;
??? ANALYZE
??? #13. 此時可以看到數據表中的頁面數量仍然為之前的高水標記數量，索引頁面數量的增加
??? #?? ?? 是和其內部實現方式有關，但是在后面的插入中，索引所占的頁面數量就不會繼續增加。
??? postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
??? ??? relname?????? | relfilenode??? | relpages
??? ---------------+-------------+----------
??? ?testtable??? ? ? |?????? 17601?? |????? 157
??? ?testtable_idx? |?????? 17604 ? |????? 173
??? (2 rows)
??? postgres=# SELECT test_insert();
??? ?test_insert
??? -------------
??? ?????????? 0
??? (1 row)
??? postgres=# ANALYZE testtable;
??? ANALYZE
????#14. 可以看到索引的頁面數量確實沒有繼續增加。
??? postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
??? ??? relname????? | relfilenode??? | relpages
??? ---------------+-------------+----------
??? ?testtable??? ? ? |?????? 17601?? |????? 157
??? ?testtable_idx? |?????? 17604?? |????? 173
??? (2 rows)
????#15. 重新批量刪除數據。
??? postgres=# DELETE FROM testtable WHERE i < 30000;
??? DELETE 19996
??? #16. 從后面的查詢可以看出，在執行VACUUM FULL命令之后，測試表和索引所占用的頁面數量
??? #????? 確實降低了，說明它們占用的物理空間已經縮小了。
??? postgres=# VACUUM FULL testtable;
??? VACUUM
??? postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
??? ??? relname?? ?? | relfilenode???? | relpages
??? ---------------+-------------+----------
??? ?testtable??? ? ? |?????? 17602 ? |????? 118
??? ?testtable_idx? |?????? 17605 ? |?????? 68
??? (2 rows)

四、定期重建索引：

?? ?在PostgreSQL中，為數據更新頻繁的數據表定期重建索引(REINDEX INDEX)是非常有必要的。對于B-Tree索引，只有那些已經完全清空的索引頁才會得到重復使用，對于那些僅部分空間可用的索引頁將不會得到重用，如果一個頁面中大多數索引鍵值都被刪除，只留下很少的一部分，那么該頁將不會被釋放并重用。在這種極端的情況下，由于每個索引頁面的利用率極低，一旦數據量顯著增加，將會導致索引文件變得極為龐大，不僅降低了查詢效率，而且還存在整個磁盤空間被完全填滿的危險。
?? ?對于重建后的索引還存在另外一個性能上的優勢，因為在新建立的索引上，邏輯上相互連接的頁面在物理上往往也是連在一起的，這樣可以提高磁盤頁面被連續讀取的幾率，從而提高整個操作的IO效率。見如下示例：
????#1. 此時已經在該表中插入了大約6萬條數據，下面的SQL語句將查詢該索引所占用的磁盤空間。????
????postgres=# SELECT relname, pg_relation_size(oid)/1024 || 'K' AS size FROM pg_class WHERE relkind='i' AND relname = 'testtable_idx';
?? ??? ?relname?? ? | size
?? ?----------------+------
?? ? testtable_idx | 1240K
?? ?(1 row)
?? ?#2. 刪除數據表中大多數的數據。
?? ?postgres=# DELETE FROM testtable WHERE i > 20000;
?? ?DELETE 50006
?? ?#3. 分析一個該表，以便于后面的SQL語句繼續查看該索引占用的空間。
?? ?postgres=# ANALYZE testtable;
?? ?ANALYZE
?? ?#4. 從該查詢結果可以看出，該索引所占用的空間并未減少，而是和之前的完全一樣。
?? ?postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;
?? ? size
?? ?------
?? ? 1240K
?? ?(1 row)
????#5. 重建索引。
?? ?postgres=# REINDEX INDEX testtable_idx;
?? ?REINDEX
?? ?#6. 查看重建后的索引實際占用的空間，從結果中可以看出索引的尺寸已經減少。
?? ?postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;
?? ? size
?? ?------
?? ? 368K
?? ?(1 row)
?? ?#7. 最后一點需要記住的是，在索引重建后一定要分析數據表。
?? ?postgres=# ANALYZE testtable;
?? ?ANALYZE

?五、觀察磁盤使用情況：

?? ?1. 查看數據表所占用的磁盤頁面數量。
?? ?#relpages只能被VACUUM、ANALYZE和幾個DDL命令更新，如CREATE INDEX。通常一個頁面的長度為8K字節。
?? ?postgres=# SELECT relfilenode, relpages FROM pg_class WHERE relname = 'testtable';
?? ? relfilenode | relpages
?? ?-------------+----------
?? ??????? 16412 |?????? 79
?? ?(1 row)
?? ?
?? ?2. 查看指定數據表的索引名稱和索引占用的磁盤頁面數量。
?? ?postgres=# SELECT c2.relname, c2.relpages FROM pg_class c, pg_class c2, pg_index i
?? ???? WHERE c.relname = 'testtable' AND c.oid = i.indrelid AND c2.oid = i.indexrelid
?? ???? ORDER BY c2.relname;
?? ???? relname??? | relpages
?? ?---------------+----------
?? ? testtable_idx |?????? 46
?? ?(1 row)