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

一、恢复磁盘空间:

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

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

  无VACUUM VACUUM VACUUM FULL
删除大量数据之后 只是将删除数据的状态置为已删除,该空间不能记录被重新使用。 如果删除的记录位于表的末端,其所占用的空间将会被物理释放并归还操作系统。如果不是末端数据,该命令会将指定表或索引中被删除数据所占用空间重新置为可用状态,那么在今后有新数据插入时,将优先使用该空间,直到所有被重用的空间用完时,再考虑使用新增的磁盘页面。 不论被删除的数据是否处于数据表的末端,这些数据所占用的空间都将被物理的释放并归还于操作系统。之后再有新数据插入时,将分配新的磁盘页面以供使用。
执行效率   由于只是状态置为操作,因此效率较高。 在当前版本的PostgreSQL(v9.1)中,该命令会为指定的表或索引重新生成一个数据文件,并将原有文件中可用的数据导入到新文件中,之后再删除原来的数据文件。因此在导入过程中,要求当前磁盘有更多的空间可用于此操作。由此可见,该命令的执行效率相对较低。
被删除的数据所占用的物理空间是否被重新规划给操作系统。 不会 不会
在执行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;
     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)

时间: 2015-05-01

PostgreSQL教程(六):函数和操作符详解(2)

六.模式匹配: PostgreSQL中提供了三种实现模式匹配的方法:SQL LIKE操作符,更近一些的SIMILAR TO操作符,和POSIX-风格正则表达式.     1. LIKE:   复制代码 代码如下: string LIKE pattern [ ESCAPE escape-character ]     string NOT LIKE pattern [ ESCAPE escape-character ] 每个pattern定义一个字串的集合.如果该string包含在pattern代

PostgreSQL教程(十三):数据库管理详解

一.概述: 数据库可以被看成是SQL对象(数据库对象)的命名集合,通常而言,每个数据库对象(表.函数等)只属于一个数据库.不过对于部分系统表而言,如pg_database,是属于整个集群的.更准确地说,数据库是模式的集合,而模式包含表.函数等SQL对象.因此完整的对象层次应该是这样的:服务器.数据库.模式.表或其他类型的对象. 在与数据库服务器建立连接时,该连接只能与一个数据库形成关联,不允许在一个会话中进行多个数据库的访问.如以postgres用户登录,该用户可以访问的缺省数据库为postgr

PostgreSQL教程(八):索引详解

一.索引的类型: PostgreSQL提供了多 种索引类型:B-Tree.Hash.GiST和GIN,由于它们使用了不同的算法,因此每种索引类型都有其适合的查询类型,缺省时,CREATE INDEX命令将创建B-Tree索引.         1. B-Tree:   复制代码 代码如下: CREATE TABLE test1 (         id integer,         content varchar     );     CREATE INDEX test1_id_index

PostgreSQL教程(七):函数和操作符详解(3)

九.序列操作函数: 序列对象(也叫序列生成器)都是用CREATE SEQUENCE创建的特殊的单行表.一个序列对象通常用于为行或者表生成唯一的标识符.下面序列函数,为我们从序列对象中获取最新的序列值提供了简单和并发读取安全的方法. 函数 返回类型 描述 nextval(regclass) bigint 递增序列对象到它的下一个数值并且返回该值.这个动作是自动完成的.即使多个会话并发运行nextval,每个进程也会安全地收到一个唯一的序列值. currval(regclass) bigint 在当

PostgreSQL教程(十二):角色和权限管理介绍

PostgreSQL是通过角色来管理数据库访问权限的,我们可以将一个角色看成是一个数据库用户,或者一组数据库用户.角色可以拥有数据库对象,如表.索引,也可以把这些对象上的权限赋予其它角色,以控制哪些用户对哪些对象拥有哪些权限.     一.数据库角色: 1. 创建角色:   复制代码 代码如下: CREATE ROLE role_name; 2. 删除角色:   复制代码 代码如下: DROP ROLE role_name; 3. 查询角色: 检查系统表pg_role,如:   复制代码 代码如

PostgreSQL教程(十):性能提升技巧

一.使用EXPLAIN: PostgreSQL为每个查询都生成一个查询规划,因为选择正确的查询路径对性能的影响是极为关键的.PostgreSQL本身已经包含了一个规划器用于寻找最优规划,我们可以通过使用EXPLAIN命令来查看规划器为每个查询生成的查询规划.     PostgreSQL中生成的查询规划是由1到n个规划节点构成的规划树,其中最底层的节点为表扫描节点,用于从数据表中返回检索出的数据行.然而,不同的扫描节点类型代表着不同的表访问模式,如:顺序扫描.索引扫描,以及位图索引扫描等.如果查

PostgreSQL教程(四):数据类型详解

一.数值类型: 下面是PostgreSQL所支持的数值类型的列表和简单说明: 1. 整数类型: 类型smallint.integer和bigint存储各种范围的全部是数字的数,也就是没有小数部分的数字.试图存储超出范围以外的数值将导致一个错误.常用的类型是integer,因为它提供了在范围.存储空间和性能之间的最佳平衡.一般只有在磁盘空间紧张的时候才使用smallint.而只有在integer的范围不够的时候才使用bigint,因为前者(integer)绝对快得多.     2. 任意精度数值:

PostgreSQL教程(一):数据表详解

一.表的定义: 对于任何一种关系型数据库而言,表都是数据存储的最核心.最基础的对象单元.现在就让我们从这里起步吧.     1. 创建表:   复制代码 代码如下: CREATE TABLE products (         product_no integer,         name text,         price numeric     );     2. 删除表:   复制代码 代码如下: DROP TABLE products;     3. 创建带有缺省值的表:   复

PostgreSQL教程(十一):服务器配置

一.服务器进程的启动和关闭: 下面是pg_ctl命令的使用方法和常用选项,需要指出的是,该命令是postgres命令的封装体,因此在使用上比直接使用postgres更加方便. 复制代码 代码如下: pg_ctl init[db] [-D DATADIR] [-s] [-o "OPTIONS"]     pg_ctl start     [-w] [-t SECS] [-D DATADIR] [-s] [-l FILENAME] [-o "OPTIONS"]    

PostgreSQL教程(五):函数和操作符详解(1)

一.逻辑操作符: 常用的逻辑操作符有:AND.OR和NOT.其语义与其它编程语言中的逻辑操作符完全相同. 二.比较操作符: 下面是PostgreSQL中提供的比较操作符列表: 比较操作符可以用于所有可以比较的数据类型.所有比较操作符都是双目操作符,且返回boolean类型.除了比较操作符以外,我们还可以使用BETWEEN语句,如:     a BETWEEN x AND y 等效于 a >= x AND a <= y        a NOT BETWEEN x AND y 等效于 a <

PostgreSQL教程(三):表的继承和分区表详解

一.表的继承: 这个概念对于很多已经熟悉其他数据库编程的开发人员而言会多少有些陌生,然而它的实现方式和设计原理却是简单易懂,现在就让我们从一个简单的例子开始吧.     1. 第一个继承表:   复制代码 代码如下: CREATE TABLE cities (   --父表         name        text,         population float,         altitude     int     );     CREATE TABLE capitals (

PostgreSQL教程(九):事物隔离介绍

在SQL的标准中事物隔离级别分为以下四种:     1. 读未提交(Read uncommitted)     2. 读已提交(Read committed)     3. 可重复读(Repeatable read)     4. 可串行化(Serializable)     然而PostgreSQL在9.1之前的版本中只是实现了其中两种,即读已提交和可串行化,如果在实际应用中选择了另外两种,那么PostgreSQL将会自动向更严格的隔离级别调整.在PostgreSQL v9.1的版本中提供了三

PostgreSQL教程(二):模式Schema详解

一个数据库包含一个或多个命名的模式,模式又包含表.模式还包含其它命名的对象,包括数据类型.函数,以及操作符.同一个对象名可以在不同的模式里使用而不会导致冲突: 比如,schema1和myschema都可以包含叫做mytable的表.和数据库不同,模式不是严格分离的:一个用户可以访问他所连接的数据库中的任意模式中的对象,只要他有权限. 我们需要模式有以下几个主要原因: 1). 允许多个用户使用一个数据库而不会干扰其它用户.     2). 把数据库对象组织成逻辑组,让它们更便于管理.     3)

Svg.js实例教程及使用手册详解(一)

什么是SVG? SVG 指可伸缩矢量图形 (Scalable Vector Graphics) SVG 用来定义用于网络的基于矢量的图形 SVG 使用 XML 格式定义图形 SVG 图像在放大或改变尺寸的情况下其图形质量不会有所损失 SVG 是万维网联盟的标准 SVG 与诸如 DOM 和 XSL 之类的 W3C 标准是一个整体 简介: SVG.js是一个轻量级的JavaScript库,允许你轻松操作SVG和定义动画. SVG(Scalable Vector Graphics,可缩放矢量图形)是基

C++基础教程之指针拷贝详解

C++基础教程之指针拷贝详解 指针是编程人员的梦魇,对C语言的开发者是如此,对C++的开发者也是如此.特别是在C++中,如果不注意处理类中的指针,非常容易出问题.如果朋友们不相信可以看看下面的代码: class data { int* value; public: data(int num){ if(num > 0) value = (int*)malloc(sizeof(int)* num); } ~data(){ if(value) free(value); } }; void proces

Java 中组合模型之对象结构模式的详解

Java 中组合模型之对象结构模式的详解 一.意图 将对象组合成树形结构以表示"部分-整体"的层次结构.Composite使得用户对单个对象和组合对象的使用具有一致性. 二.适用性 你想表示对象的部分-整体层次结构 你希望用户忽略组合对象与单个对象的不同,用户将统一使用组合结构中的所有对象. 三.结构 四.代码 public abstract class Component { protected String name; //节点名 public Component(String n

IOS开发基础之二维数组详解

IOS开发基础之二维数组详解 首先我们知道OC中是没有二维数组的,二维数组是通过一位数组的嵌套实现的,但是别忘了我们有字面量,实际上可以和C/C++类似的简洁地创建和使用二维数组.这里总结了创建二维数组的两种方法以及数组的访问方式. 通过字面量创建和使用二维数组(推荐) // 1.字面量创建二维数组并访问(推荐) NSArray *array2d = @[ @[@11,@12,@13], @[@21,@22,@23], @[@31,@32,@33] ]; // 字面量访问方式(推荐) NSLog

Angular指令之restict匹配模式的详解

Angular指令之restict匹配模式的详解 <body data-ng-app="myapp"> <runn2></runn2> <div data-runn2></div> <div class="runn2"></div> <!-- directive: runn2 --> <script> var app=angular.module("

kotlin 官方学习教程之基础语法详解

kotlin 官方学习教程之基础语法详解 Google 在今天的举行了 I/O 大会,大会主要主要展示内有容 Android O(Android 8.0)系统.Google Assistant 语音助手.Google 智能音箱.人工智能.机器学习.虚拟现实等.作为一个 Android 开发者,我关心的当然是 Android O(Android 8.0)系统了,那么关于 Android O 系统的一个重要消息是全面支持 Kotlin 编程语言,使得 Kotlin 成为了 Android 开发的官方