转载 https://www.cnblogs.com/star521/p/13385181.html --PGSQL-脏数据清理,频繁delete\update高水位线问题,vacuum full 、vacuum
查看表大小
-- 查出所有表(包含索引)并排序 -- 查出所有表(包含索引)并排序 SELECT table_schema , table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables
where table_schema ='ioc_dm' and table_name ='m_ss_index_event' ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
vacuum full 之前 1386MB
查看数据量 105466
vacuum full之后 74MB
方法2 :创建临时表存储数据,并truncate 源表已解决高水位线问题
创建测试表
CREATE SEQUENCE "ioc_dw_second"."test0001_seq" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 CYCLE ; CREATE TABLE "ioc_dw_second"."test0001" ( "rid" int4 NOT NULL DEFAULT nextval('"ioc_dw_second".test0001_seq'::regclass), "name" varchar(20) COLLATE "pg_catalog"."default", "update_time" timestamp(6) DEFAULT pg_systimestamp(), CONSTRAINT "test0001_pkey" PRIMARY KEY ("rid") ) ;
创建存储过程,插入测试数据 --- while loop循环 declare 变量定义
CREATE OR REPLACE FUNCTION "ioc_dw_second"."proc_test0001"(IN "functime" varchar, OUT "v_rowline" varchar, OUT "v_retcode" varchar, OUT "v_retinfo" varchar) RETURNS "pg_catalog"."record" AS $BODY$ declare cou int; BEGIN cou := 0; while cou< 1000000 LOOP insert into ioc_dw_second.test0001 ( name ) values ('new'), ('new'), ('new'), ('new'), ('new'), ('new'); cou := cou +1; END LOOP ; END $BODY$ LANGUAGE plpgsql VOLATILE COST 100
调取存储过程
call ioc_dw_second.proc_test0001(null,null,null,null);
数据大小
重复删除、插入数据 数据大小
truncate table ioc_dw_second.test0001;
vacuum full ioc_dw_second.test0001;
vacuum \vacuum full 原理
VACUUM 功能描述VACUUM回收表或B-Tree索引中已经删除的行所占据的存储空间。在一般的数据库操作里,那些已经DELETE的行并没有从它们所属的表中物理删除;在完成VACUUM之前它们仍然存在。因此有必要周期地运行VACUUM,特别是在经常更新的表上。 如果没有参数,VACUUM处理当前数据库里用户拥有相应权限的每个表。如果参数指定了一个表,VACUUM只处理指定的那个表。 VACUUM ANALYZE先执行一个VACUUM操作,然后给每个选定的表执行一个ANALYZE。对于日常维护脚本而言,这是一个很方便的组合。 简单的VACUUM(不带FULL选项)只是简单地回收空间并且令其可以再次使用。这种形式的命令可以和对表的普通读写并发操作,因为没有请求排他锁。VACUUM FULL执行更广泛的处理,包括跨块移动行,以便把表压缩到最少的磁盘块数目里。这种形式要慢许多并且在处理的时候需要在表上施加一个排他锁。 注意事项要对一个表进行VACUUM操作,通常用户必须是表的所有者或系统管理员。数据库的所有者允许对数据库中除了共享目录以外的所有表进行VACUUM操作(该限制意味着只有系统管理员才能真正对一个数据库进行VACUUM操作)。VACUUM命令会跳过那些用户没有权限的表进行垃圾回收操作。 VACUUM不能在事务块内执行。 建议生产数据库经常清理(至少每晚一次),以保证不断地删除失效的行。尤其是在增删了大量记录之后,对受影响的表执行VACUUM ANALYZE命令是一个很好的习惯。这样将更新系统目录为最近的更改,并且允许查询优化器在规划用户查询时有更好的选择。 不建议日常使用FULL选项,但是可以在特殊情况下使用。例如在用户删除了一个表的大部分行之后,希望从物理上缩小该表以减少磁盘空间占用。VACUUM FULL通常要比单纯的VACUUM收缩更多的表尺寸。FULL选项并不清理索引,所以推荐周期性的运行REINDEX命令。实际上,首先删除所有索引,再运行VACUUM FULL命令,最后重建索引通常是更快的选择。如果执行此命令后所占用物理空间无变化(未减少),请确认是否有其他活跃事务(删除数据事务开始之前开始的事务,并在vacuum full执行前未结束)存在,如果有等其他活跃事务退出进行重试。 VACUUM会导致I/O流量的大幅增加,这可能会影响其他活动会话的性能。因此,有时候会建议使用基于开销的vacuum延迟特性。 如果指定了VERBOSE选项,VACUUM将打印处理过程中的信息,以表明当前正在处理的表。各种有关当前表的统计信息也会打印出来。但是对于列存表执行VACUUM操作,指定了VERBOSE选项,无信息输出。 当含有带括号的选项列表时,选项可以以任何顺序写入。如果没有括号,则选项必须按语法显示的顺序给出。 语法格式回收空间并更新统计信息,对关键字顺序无要求。 VACUUM [ ( { FULL | FREEZE | VERBOSE | {ANALYZE | ANALYSE }} [,...] ) ] [ table_name [ (column_name [, ...] ) ] ] [ PARTITION ( partition_name ) ];仅回收空间,不更新统计信息。 VACUUM [ FULL [COMPACT] ] [ FREEZE ] [ VERBOSE ] [ table_name ] [ PARTITION ( partition_name ) ];回收空间并更新统计信息,且对关键字顺序有要求。 VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] { ANALYZE | ANALYSE } [ VERBOSE ] [ table_name [ (column_name [, ...] ) ] ] [ PARTITION ( partition_name ) ];针对HDFS表,将delta table中的数据转移到HDFS存储。 VACUUM DELTAMERGE [ table_name ];针对HDFS表,删除HDFS表在HDFS存储上的空值分区目录。 VACUUM HDFSDIRECTORY [ table_name ];参数说明FULL 选择“FULL”清理,这样可以恢复更多的空间,但是需要耗时更多,并且在表上施加了排他锁。 FULL选项还可以带有COMPACT参数,该参数只针对HDFS表,指定该参数的VACUUM FULL操作性能要好于未指定该参数的VACUUM FULL操作。 COMPACT和PARTITION参数不能同时使用。 说明: 使用FULL参数会导致统计信息丢失,如果需要收集统计信息,请在vacuum full语句中加上analyze关键字。 FREEZE 指定FREEZE相当于执行VACUUM时将vacuum_freeze_min_age参数设为0。 VERBOSE 为每个表打印一份详细的清理工作报告。 ANALYZE | ANALYSE 更新用于优化器的统计信息,以决定执行查询的最有效方法。 table_name 要清理的表的名称(可以有模式修饰)。 取值范围:要清理的表的名称。缺省时为当前数据库中的所有表。 column_name 要分析的具体的字段名称。 取值范围:要分析的具体的字段名称。缺省时为所有字段。 PARTITION HDFS表不支持PARTITION参数,COMPACT和PARTITION参数不能同时使用。 partition_name 要清理的表的分区名称。缺省时为所有分区。 DELTAMERGE 只针对HDFS表,将HDFS表的delta table中的数据转移到HDFS存储上。此操作受cstore_insert_mode和enable_upgrade_merge_lock_mode两个参数的影响。 HDFSDIRECTORY 只针对HDFS表,删除HDFS表在HDFS存储上表目录下的空值分区目录。 示例--在表tpcds.reason上创建索引 CREATE UNIQUE INDEX ds_reason_index1 ON tpcds.reason(r_reason_sk); --对带索引的表tpcds.reason执行VACUUM操作。 VACUUM (VERBOSE, ANALYZE) tpcds.reason; --删除索引 DROP INDEX ds_reason_index1 CASCADE; DROP TABLE tpcds.reason;父主题: SQL语法:SET ROLE to VALUES