pg_vacuum

This python program determines whether a vacuum/analyze/freeze should be done and if so, which one.

(c) 2018-2022 SQLEXEC LLC
GNU V3 and MIT licenses are conveyed accordingly.
Bugs can be reported @ [email protected]

History

The first version of this program was created in 2018.
Program renamed from optimize_db.py to pg_vacuum.py (December 2020)

Overview

This program is useful to identify and vacuum tables. Most inputs are optional, and either an optional parameter is not used or a default value is used if not provided. That means you can override internal parameters by specifying them on the command line. Here are the parameters:
-H --host host name
-d --dbname database name
-p --dbport database port
-U --dbuser database user
-s --maxsize max table size that will be considered
-y --analyzemaxdays Analyzes older than this will be considered
-x --vacuummaxdays Vacuums older than this will be considered
-t --mindeadtups minimum dead tups before considering a vacuum
-b --maxtables max number of tables to vacuum, default 9999
-m --schema if provided, perform actions only on this schema
-z --pctfreeze specifies how close to wraparound before FREEZE is done.
-f --freeze perform freeze if necessary
-r --dryrun do a dry run for analysis before actually running it.
-q --inquiry show stats to validate run. Best used with dryrun. Values: "all" | "found" | not specified
-i --ignoreparts ignore partitioned tables
-a --async run async jobs ignoring thresholds
-n --nullsonly Only consider tables with no vacuum or analyze history
-c --check Only Check stats on tables
-v --verbose Used primarily for debugging

Requirements

1. python 2.7 or above
2. python packages: psycopg2
3. Works on Linux and Windows.

Assumptions

1. Only when a table is within 25 million of reaching the wraparound threshold is it considered a FREEZE candidate.
2. By default, catalog tables are ignored unless specified explicitly with the --schema option.
3. If passwords are required (authentication <> trust), then you must define credentials in the .pgpass (linux)/pgpass.conf (windows) files.

Examples

pg_vacuum.py -H localhost -d testing -p 5432 -U postgres --maxsize 40000000 -y 10 -x 2 -t 1000 --schema concept --ignoreparts --dryrun

pg_vacuum.py -H localhost -d testing -p 5432 -U postgres -s 400000000000 -y 1 -t 1000 -m public --pctfreeze 90 --freeze

pg_vacuum.py -H localhost -d testing -p 5432 -U postgres -s 400000000000 -n -b 300 -m public --ignoreparts

pg_vacuum.py -H localhost -d testing -p 5432 -U postgres -s 400000000000 --check --ignoreparts --dryrun

pg_vacuum.py -H localhost -d testing -p 5432 -U postgres -s 400000000000 --nullsonly --ignoreparts --dryrun