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

一、回收磁盤空間

在PostgreSQL中，使用delete和update語句删除或更新的資料行并沒有被實際删除，而隻是在舊版本資料行的實體位址上将該行的狀态置為已删除或已過期。是以當資料表中的資料變化極為頻繁時，那麼在一段時間之後該表所占用的空間将會變得很大，然而資料量卻可能變化不大。要解決該問題，需要定期對資料變化頻繁的資料表執行VACUUM操作。現在新版PostgreSQL是自動執行VACUUM的

建立測試資料：
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)           

二、重建索引

postgres=# REINDEX INDEX testtable_idx;           

三、重新收集統計資訊

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