PostgreSQL finds the vacuum table information that needs to be collected

preface

In general, in PostgreSQL, tables often need to be updated and deleted, so they will generate fragmented space. In PostgreSQL, the use of vacuum only marks the deleted space as unused when the vacuum table needs to be executed, so as to reuse the space in the future. However, the occupied space cannot be returned to the operating system immediately. Therefore, vacuum full is needed to release the space and return the space to the operating system immediately.

Implementation script

Record collection table creation

CREATE TABLE IF NOT EXISTS tab_vacuum_record
(sqltext text);

Collection requires vacuum table functions

CREATE OR REPLACE FUNCTION f_vacuum_tables()
RETURNS void AS
$FUNCTION$
 DECLARE
  v_tablename  text;
  v_dead_cond  bigint;
  v_sql        text;
  cur_tablename REFCURSOR;
  v_vacuum_record text;
 BEGIN
  v_vacuum_record := 'tab_vacuum_record';
  OPEN cur_tablename FOR SELECT tablename FROM pg_tables WHERE tablename !~ '^pg|^sql';
  LOOP
   FETCH cur_tablename INTO v_tablename;
      SELECT n_dead_tup INTO v_dead_cond FROM pg_stat_user_tables WHERE relname = v_tablename;
          IF v_dead_cond > 0 THEN
            v_sql := 'INSERT INTO ' || v_vacuum_record || ' VALUES(' || chr(39) ||'VACUUM FULL ' || v_tablename ||';'|| chr(39) ||')';
        EXECUTE v_sql;
          END IF;
      EXIT WHEN NOT FOUND;
  END LOOP;
  CLOSE cur_tablename;
 END;
$FUNCTION$
LANGUAGE PLPGSQL;

shell scripts

#!/bin/bash
#Get environment variables
CURRDIR=$(cd "$(dirname $0)";pwd)
TOPDIR=$(cd $CURRDIR/..;pwd)
CONFIG=$TOPDIR/conf/host.ini
CT_FILE=${TOPDIR}/sql/CREATE_VACCUM_TABLE_RECORD.sql
CT_FUNCTION=${TOPDIR}/sql/CHECK_NEEDS_VACUUM_TABLE_FUNCTION.sql
source $CONFIG
CONNINFO="psql -U $USER -d $DBNAME -h $HOSTADDR -p $PORT"

function check_status()
{
        Echo "check whether the database server status is normal!"
        stat=`$CONNINFO -Aqt -c 'SELECT 1'`
        if [ "${stat}" == "1" ];then
                Echo "server connection OK"
        else
                Echo "server connection abnormal, exit"
                exit -1;
        fi
}
function create_table()
{
        Echo "create a table that needs vacuum"
        $CONNINFO -f $CT_FILE
}

function create_function()
{
        Echo "create a function to collect the vacuum table"
        $CONNINFO -f $CT_FUNCTION
}
check_status

create_table

create_function

Mode of implementation

postgres=# SELECT * FROM f_vacuum_tables();
 f_vacuum_tables 
-----------------
 
(1 row)
--Create test table
postgres=# CREATE TABLE tab_test(id int);

--Insert data
postgres=# INSERT INTO tab_test SELECT id FROM generate_series(1,100000) as id;
INSERT 0 100000
--Delete data
postgres=# DELETE FROM tab_Test WHERE id <= 10000;
DELETE 10002

postgres=# SELECT * FROM tab_vacuum_record ;
        sqltext        
-----------------------
 VACUUM FULL tab_test;
(1 row)

The script can also be modified as needed. See GitHub for details
https://github.com/OpenSource…