PostgreSQL学习手册(数据库维护)

PostgreSQL学习手册(数据库维护)

 

一、恢复磁盘空间：

 

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

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

  无VACUUM VACUUM VACUUM FULL

删除大量数据之后 只是将删除数据的状态置为已删除，该空间不能记录被重新使用。 如果删除的记录位于表的末端，其所占用的空间将会被物理释放并归还操作系统。如果不是末端数据，该命令会将指定表或索引中被删除数据所占用空间重新置为可用状态，那么在今后有新数据插入时，将优先使用该空间，直到所有被重用的空间用完时，再考虑使用新增的磁盘页面。 不论被删除的数据是否处于数据表的末端，这些数据所占用的空间都将被物理的释放并归还于操作系统。之后再有新数据插入时，将分配新的磁盘页面以供使用。
 

执行效率 由于只是状态置为操作，因此效率较高。 在当前版本的PostgreSQL(v9.1)中，该命令会为指定的表或索引重新生成一个数据文件，并将原有文件中可用的数据导入到新文件中，之后再删除原来的数据文件。因此在导入过程中，要求当前磁盘有更多的空间可用于此操作。由此可见，该命令的执行效率相对较低。  www.zzzyk.com  

被删除的数据所占用的物理空间是否被重新规划给操作系统。 不会 不会 会

在执行VACUUM命令时，是否可以并发执行针对该表的其他操作。 由于该操作是共享锁，因此可以与其他操作并行进行。 由于该操作需要在指定的表上应用排它锁，因此在执行该操作期间，任何基于该表的操作都将被挂起，知道该操作完成。
 

推荐使用方式 在进行数据清空是，可以使用truncate操作，因为该操作将会物理的清空数据表，并将其所占用的空间直接归还于操作系统。 为了保证数据表的磁盘页面数量能够保持在一个相对稳定值，可以定期执行该操作，如每天或每周中数据操作相对较少的时段。 考虑到该操作的开销，以及对其他错误的排斥，推荐的方式是，定期监控数据量变化较大的表，只有确认其磁盘页面占有量接近临界值时，才考虑执行一次该操作。即便如此，也需要注意尽量选择数据操作较少的时段来完成该操作。
 

执行后其它操作的效率 对于查询而言，由于存在大量的磁盘页面碎片，因此效率会逐步降低。 相比于不执行任何VACUUM操作，其效率更高，但是插入的效率会有所降低。 在执行完该操作后，所有基于该表的操作效率都会得到极大的提升。

 

二、更新规划器统计：

 

    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;  www.zzzyk.com  

     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';  www.zzzyk.com  

        relname       | relfilenode    | relpages

    ---------------+-------------+----------

     testtable        |       17601   |      157

     testtable_idx  |       17604   |       90

    #8. 批量删除数据。

    postgres=# DELETE FROM testtable WHERE i < 30000;

    DELETE 30003

    #9. 执行vacuum和易做图yze，以便更新系统表，同时为该表和索引记录高水标记。

    #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