This python program determines whether a vacuum/analyze/freeze should be done and if so, which one.
(c) 2018-2021 SQLEXEC LLC
GNU V3 and MIT licenses are conveyed accordingly.
Bugs can be reported @ [email protected]
The first version of this program was created in 2018.
Program renamed from optimize_db.py to pg_vacuum.py (December 2020)
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 --maxdaysA Analyzes older than this will be considered
-x --maxdaysV Vacuums older than this will be considered
-t --mindeadtups minimum dead tups before considering a vacuum
-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
- python 2.7 or above
- python packages: psycopg2
- Only when a table is within 25 million of reaching the wraparound threshold is it considered a FREEZE candidate.
- By default, catalog tables are ignored unless specified explicitly with the --schema option.
pg_vacuum.py -H localhost -d testing -p 5432 -u postgres --maxsize 40000000 --maxdays 1 --mindeadtups 1000 --schema public --dryrun
pg_vacuum.py -H localhost -d testing -p 5432 -u postgres -s 400000000000 -y 1 -t 1000 -m public --pctfreeze 90 --freeze