PostgreSQL -- 性能优化的几个小tip

一、回收磁盘空间

在PostgreSQL中，使用delete和update语句删除或更新的数据行并没有被实际删除，而只是在旧版本数据行的物理地址上将该行的状态置为已删除或已过期。因此当数据表中的数据变化极为频繁时，那么在一段时间之后该表所占用的空间将会变得很大，然而数据量却可能变化不大。要解决该问题，需要定期对数据变化频繁的数据表执行VACUUM操作。现在新版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%)。