当系统有更新操作（包括INSERT VALUES、UPDATE、DELETE、ALTER TABLE ADD COLUMN等），会在系统表和被更新的数据表中留存不再使用的垃圾数据，造成系统性能下降，并占用大量磁盘空间，因此需要定期进行垃圾回收。本文介绍了垃圾回收的方法。

AUTO VACUUM

AUTO VACUUM可以自动执行VACUUM命令。AUTO VACUUM将检查具有大量插入、更新或删除的表，并在需要的时候主动对表执行VACUUM来回收表中的垃圾数据，提升查询速度。默认情况下， 当表被删除行数超过一半时，AUTO VACUUM会对表执行VACUUM操作来清除垃圾数据。

对于MULTI MASTER实例，当前暂时只能追踪主MASTER上发生的改动行为，辅助MASTER发生的改动行为将不会触发AUTO VACUUM。

说明 云原生数据仓库AnalyticDB PostgreSQL版仅20210527及以后版本支持AUTO VACUUM功能，如何升级小版本，请参见版本升级。

不锁表回收垃圾

在不锁表的情况下，可以回收部分垃圾。具体方式如下：

  • 命令：连接每个数据库，以数据库的所有者身份登录，执行VACUUM命令。
  • 频率：
    • 如果有大批量实时更新的情况（即不断执行INSERT VALUES、UPDATE、DELETE等操作），建议每天执行一次，或每周至少一次。
    • 如果更新是每天一次批量进行的，建议每周执行一次，或不要超过一个月执行一次。
  • 对系统影响：不会锁表，表可以正常读写。会导致CPU、I/O使用率增加，可能影响查询的性能。
  • 示例的脚本文件：可以使用如下的Linux Shell脚本文件，作为crontab定期任务来执行。
#!/bin/bash
export PGHOST=myinst.gpdb.rds.tbsite.net
export PGPORT=3432
export PGUSER=myuser
export PGPASSWORD=mypass
#do not echo command, just get a list of db
dblist=`psql -d postgres -c "copy (select datname from pg_stat_database) to stdout"`
for db in $dblist ; do
    #skip the system databases
    if [[ $db == template0 ]] ||  [[ $db == template1 ]] || [[ $db == postgres ]] || [[ $db == gpdb ]] ; then
        continue
    fi
    echo processing $db
    #vacuum all tables (catalog tables/user tables)
    psql -d $db -e -a -c "VACUUM;"
done

维护窗口回收垃圾

在业务暂停的维护窗口，可以回收所有垃圾。具体方式如下：

  • 命令：连接每个数据库，以数据库的所有者身份登录（需要对所有操作对象有所有者权限）。
    1. 执行REINDEX SYSTEM <database name>。
    2. 对每张数据表，执行VACUUM FULL <table name>，对列存表还需要执行REINDEX TABLE <table name>。
    3. 对于系统表（包括pg_class, pg_attribute, pg_index等），当有频繁建删表，建删索引等操作时，也建议执行 VACUUM FULL <table name>进行定期维护。注意：该操作需要业务停止访问数据库。
  • 频率：至少每周执行一次。如果每天会更新几乎所有数据，需要每天做一次。
  • 对系统影响：会对正在进行VACUUM FULL或REINDEX的表进行锁定，无法读写。会导致CPU、I/O使用率增加。
  • 示例的脚本文件：可以使用如下的Linux Shell脚本文件，作为crontab定期任务来执行。
#!/bin/bash
export PGHOST=myinst.gpdb.rds.tbsite.net
export PGPORT=3432
export PGUSER=myuser
export PGPASSWORD=mypass
#do not echo command, just get a list of db
dblist=`psql -d postgres -c "copy (select datname from pg_stat_database) to stdout"`
for db in $dblist ; do
    #skip system databases
    if [[ $db == template0 ]] ||  [[ $db == template1 ]] || [[ $db == postgres ]] || [[ $db == gpdb ]] ; then
        continue
    fi
    echo processing db "$db"
    #do a normal vacuum
    psql -d $db -e -a -c "VACUUM;"
    #reindex system tables firstly
    psql -d $db -e -a -c "REINDEX SYSTEM $db;"
    #use a temp file to store the table list, which could be vary large
    cp /dev/null tables.txt
    #query out only the normal user tables, excluding partitions of parent tables
    psql -d $db -c "copy (select '\"'||tables.schemaname||'\".' || '\"'||tables.tablename||'\"' from (select nspname as schemaname, relname as tablename from pg_catalog.pg_class, pg_catalog.pg_namespace, pg_catalog.pg_roles where pg_class.relnamespace = pg_namespace.oid and pg_namespace.nspowner = pg_roles.oid and pg_class.relkind='r' and (pg_namespace.nspname = 'public' or pg_roles.rolsuper = 'false' ) ) as tables(schemaname, tablename) left join pg_catalog.pg_partitions on pg_partitions.partitionschemaname=tables.schemaname and pg_partitions.partitiontablename=tables.tablename where pg_partitions.partitiontablename is null) to stdout;" > tables.txt
    while read line; do
        #some table name may contain the $ sign, so escape it
        line=`echo $line |sed 's/\\\$/\\\\\\\$/g'`
        echo processing table "$line"
        #vacuum full this table, which will lock the table
        psql -d $db -e -a -c "VACUUM FULL $line;"
        #reindex the table to reclaim index space
        psql -d $db -e -a -c "REINDEX TABLE $line;"
    done <tables.txt
done