例行维护表

为了保证数据库的有效运行，数据库必须在插入/删除操作后，基于客户场景，定期做VACUUM FULL和ANALYZE，更新统计信息，以便获得更优的性能。

相关概念

使用VACUUM、VACUUM FULL和ANALYZE命令定期对每个表进行维护，主要有以下原因：

• VACUUM对每个表维护了一个可视化映射来跟踪包含对别的活动事务可见的数组的页。一个普通的索引扫描首先通过可视化映射来获取对应的数组，来检查是否对当前事务可见。若无法获取，再通过堆数组抓取的方式来检查。因此更新表的可视化映射，可加速唯一索引扫描。
• VACUUM可避免执行的事务数超过数据库阈值时，事务ID重叠造成的原有数据丢失。
• VACUUM FULL可回收已更新或已删除的数据所占据的磁盘空间，同时将小数据文件合并。
• ANALYZE可收集与数据库中表内容相关的统计信息。统计结果存储在系统表PG_STATISTIC中。查询优化器会使用这些统计数据，生成最有效的执行计划。

操作步骤

1、创建RANGE范围分区表table_p1。

create table table_p1
(id int,
name varchar(100)) 
partition by range(id)(
partition p1 values less than (10), 
partition p2 values less than (20), 
partition p3 values less than (30), 
partition p4 values less than (40), 
partition p5 values less than (50), 
partition p6 values less than (MAXVALUE)
)
ENABLE ROW MOVEMENT;

2、使用VACUUM或VACUUM FULL命令，进行磁盘空间回收。

• VACUUM：

  • 对表执行VACUUM操作

    VACUUM table_p1;
    

  • 可以与数据库操作命令并行运行。（执行期间，可正常使用的语句：SELECT、INSERT、UPDATE和DELETE。不可正常使用的语句：ALTER TABLE）。 对表分区执行VACUUM操作

    vacuum table_p1 partition(p1);
    

• VACUUM FULL：

  VACUUM FULL table_p1;
  

需要向正在执行的表增加排他锁，且需要停止其他所有数据库操作。

3、使用ANALYZE语句更新统计信息。

ANALYZE table_p1;

使用ANALYZE VERBOSE语句更新统计信息，并输出表的相关信息。

ANALYZE VERBOSE table_p1;

输出结果为：

INFO:  analyzing "public.table_p1"( pid=38979)
INFO:  ANALYZE INFO : "table_p1": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows( pid=38979)
INFO:  ANALYZE INFO : "table_p1": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows( pid=38979)
INFO:  ANALYZE INFO : "table_p1": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows( pid=38979)
INFO:  ANALYZE INFO : "table_p1": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows( pid=38979)
INFO:  ANALYZE INFO : "table_p1": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows( pid=38979)
INFO:  ANALYZE INFO : "table_p1": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows( pid=38979)
ANALYZE

也可以同时执行VACUUM ANALYZE命令进行查询优化。

VACUUM ANALYZE table_p1;

说明： VACUUM和ANALYZE会导致I/O流量的大幅增加，这可能会影响其他活动会话的性能。

4、删除表。

DROP TABLE table_p1;