在PostgreSQL中，使用delete和update語句刪除或更新的數據行并沒有被實際刪除，而只是在舊版本數據行的物理地址上將該行的狀態置為已刪除或已過期。因此當數據表中的數據變化極為頻繁時，那么在一段時間之后該表所占用的空間將會變得很大，然而數據量卻可能變化不大。要解決該問題，需要定期對數據變化頻繁的數據表執行VACUUM操作。
PostgreSQL查詢規劃器在選擇最優路徑時，需要參照相關數據表的統計信息用以為查詢生成最合理的規劃。這些統計是通過ANALYZE命令獲得的，你可以直接調用該命令，或者把它當做VACUUM命令里的一個可選步驟來調用，如VACUUM ANAYLYZE table_name，該命令將會先執行VACUUM再執行ANALYZE。與回收空間(VACUUM)一樣，對數據更新頻繁的表保持一定頻度的ANALYZE，從而使該表的統計信息始終處于相對較新的狀態，這樣對于基于該表的查詢優化將是極為有利的。然而對于更新并不頻繁的數據表，則不需要執行該操作。
我們可以為特定的表，甚至是表中特定的字段運行ANALYZE命令，這樣我們就可以根據實際情況，只對更新比較頻繁的部分信息執行ANALYZE操作，這樣不僅可以節省統計信息所占用的空間，也可以提高本次ANALYZE操作的執行效率。這里需要額外說明的是，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語句將查詢該索引所占用的磁盤空間。
1. 查看數據表所占用的磁盤頁面數量。
