PostgreSQL教程(十四)：資料庫維護，postgresql教程

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)