PostgreSQL教程(十四)：数据库维护

一、恢复磁盘空间：

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

VACUUM命令存在两种形式，VACUUM和VACUUM FULL，它们之间的区别见如下表格：

二、更新规划器统计：

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)