PostgreSQL -- 性能優化的幾個小tip

一、回收磁盤空間

在PostgreSQL中，使用delete和update語句刪除或更新的數據行并沒有被實際刪除，而只是在舊版本數據行的物理地址上將該行的狀態置為已刪除或已過期。因此當數據表中的數據變化極為頻繁時，那么在一段時間之后該表所占用的空間將會變得很大，然而數據量卻可能變化不大。要解決該問題，需要定期對數據變化頻繁的數據表執行VACUUM操作?，F在新版PostgreSQL是自動執行VACUUM的

使用VACUUM和VACUUM FULL命令回收磁盤空間
postgres=# vacuum arr_test;
postgres=# vacuum full arr_test;

創建測試數據：
postgres=# create table arr (id serial, value int, age int)  #創建測試表
postgres=# insert into arr (value, age) select generate_series(1, 1000000) as value, (random()*(10^2))::integer;  #插入100W測試數據
postgres=# select pg_relation_size('arr');   #查看表大小
 pg_relation_size 
------------------
         44285952
(1 row)
postgres=# delete from arr where id<300000;  #刪除299999條數據
DELETE 299999
postgres=# select pg_relation_size('arr');   #再次查看表大小，沒有變化
pg_relation_size 
------------------
         44285952
(1 row)
postgres=# vacuum full arr;    #vacuum表，再次查看表大小，明顯變小了
VACUUM
postgres=# select pg_relation_size('arr');
 pg_relation_size 
------------------
         30998528
(1 row)
postgres=# update arr set age=10000 where id>=300000 and id<600000;   #更新30W條數據
UPDATE 300000
postgres=# select pg_relation_size('arr');  #查看表大小，明顯再次增大
 pg_relation_size 
------------------
         44285952
(1 row)

二、重建索引

在PostgreSQL中，為數據更新頻繁的數據表定期重建索引(REINDEX INDEX)是非常有必要的。對于B-Tree索引，只有那些已經完全清空的索引頁才會得到重復使用，對于那些僅部分空間可用的索引頁將不會得到重用，如果一個頁面中大多數索引鍵值都被刪除，只留下很少的一部分，那么該頁將不會被釋放并重用。在這種極端的情況下，由于每個索引頁面的利用率極低，一旦數據量顯著增加，將會導致索引文件變得極為龐大，不僅降低了查詢效率，而且還存在整個磁盤空間被完全填滿的危險。
對于重建后的索引還存在另外一個性能上的優勢，因為在新建立的索引上，邏輯上相互連接的頁面在物理上往往也是連在一起的，這樣可以提高磁盤頁面被連續讀取的幾率，從而提高整個操作的IO效率

postgres=# REINDEX INDEX testtable_idx;

三、重新收集統計信息

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%)。