idspgmaint

Edit online

Run idspgmaint to maintain the ISVD PostgreSQL database.

Description

You can give maintenance (VACUUM / VACUUM FULL + ANALYZE + REINDEX) to the ISVD PostgreSQL database with the ISVD utility idspgmaint.
Note: .
  • Requires privileges to VACUUM/ANALYZE/REINDEX the target objects (plus permission to alter DB connect/limit when doing FULL).
  • Designed for PostgreSQL 14 and later versions.
  • It counts with progress views feature.
  • For VACUUM FULL, the idspgmaint utility enters an "exclusive DB mode". It keeps 1 persistent connection, blocks new connections, terminates others, and runs all FULL tasks in that same session.
  • Bloat heuristic:
    • Runs VACUUM FULL only for tables whose bloat exceeds the threshold (defaults to 20% if you pass --full-only-on-bloat[=N] without a value).
    • Prefers pgstattuple_approx if the pgstattuple extension is installed.
    • It uses the pgstattuple extension for a fast and more accurate estimate of dead tuples.
    • Else falls back to a dead-tuple ratio using pg_stat_all_tables.
  • Decision Tree for VACUUM Usage:
    • Here’s a simplified decision tree

Decision Tree for VACUUM Usage

Key points:

  • VACUUM: Use for routine cleanup; safe online.
  • ANALYZE: Use when data distribution changes significantly.
  • VACUUM FULL: Use only when reclaiming OS disk space or after heavy bloat; requires downtime.

Usage

Connection:

-d <database>, -U <user>, -h <host>, -p <port>

(will use 'idsldap', $PGUSER, $PGHOST, $PGPORT respectively if they are not supplied)

Selection:

-n [,schema...] -t [,table...]

(if neither -n nor -t is supplied => all user schemas/tables)

--exclude [,table...]

Exclude table(s) by name or glob pattern (*, ?). It accepts schema-qualified (schema.table) or unqualified. Can be repeated.

Logging policy:

-l <log-dir> | --log-dir <log-dir>

Log directory (overrides the default)

Default log-dir: /var/isvd/data/logs/pgmaint_logger/

If the specified directory is unusable, the system warns and falls back to the default directory.

If the default directory is also unusable, the system warns again and falls back to writing to stdout-only.

--require-log-dir

Fail only if both the specified --log-dir (if any) and the default log-dir are unusable. In that case, do not fall back to stdout-only

Autovacuum conflict policy:

--autovacuum-conflict {wait|cancel|skip|ignore}

Default policy = cancel (escalates to terminate after timeout)

--autovacuum-timeout

Max time to wait for workers to stop; with policy=cancel (overrides the default 600)

--autovacuum-show-settings

Show autovacuum GUCs and per-table reloptions (optional diagnostics in pre-check)

-Vacuum/Analyze:
--full

Force VACUUM FULL on all targeted tables. For safety, it blocks new connections (REVOKE CONNECTIONS), terminates other sessions, and runs FULL

--full-only-on-bloat[=N]

VACUUM FULL only if bloat >= N% (default 20 when flag passed w/o value)

--analyze

ANALYZE after vacuum/full

--analyze-only

Only ANALYZE (no VACUUM)

REINDEX:

--reindex

Run REINDEX after maintenance (or with --analyze-only)

--reindex-concurrently

Use REINDEX CONCURRENTLY (lower locking, longer runtime)

--reindex-only-on-bloat[=N]

REINDEX only when BTREE index-bloat >= N% (default 20). Uses pgstatindex() if pgstattuple is available; else a catalog-based fallback estimator (no extensions).

Progress:

--watch

Show live progress for VACUUM Online and REINDEX

--watch-quiet

Show more concise progress (implies --watch)

--watch-interval <sec>

Poll interval (default: 15)

Planning:

--dry-run

Preview actions, plan ONLY (no changes). It still connects read-only to enumerate/estimate.

Other:
-V, --version

Print the idspgmaint utility version and last updated date, then exit.

-H, --help

Show the usage of the idspgmaint utility and its help options.

Examples:
For example, maintenance was required on the ibmentryuuid, ldap_desc, ldap_entry, objectclass, and title tables in the idsldap schema. The title table was excluded because its maintenance was less urgent.
Example 1:
To ensure the reported tables truly require maintenance, we will utilize the only-on-bloat options. Always run --dry-run first to test in staging and confirm the plan first. 
$ idspgmaint -n idsldap -t ibmentryuuid,ldap_desc,ldap_entry,objectclass,title --exclude idsldap.title --full-only-on-bloat --reindex-only-on-bloat --dry-run
Example 2:

After checking and confirming the --dry-run plan, execute it, watching the progress with the default every 15 seconds.

$ idspgmaint -n idsldap -t ibmentryuuid,ldap_desc,ldap_entry,objectclass,title --exclude idsldap.title --full-only-on-bloat --reindex-only-on-bloat –watch
Example 3:
Choose a different destination path for the recorded log, and use --require-log-dir if logs are mandatory for compliance. 
$ idspgmaint -n idsldap -t ibmentryuuid,ldap_desc,ldap_entry,objectclass,title --exclude idsldap.title -l /var/isvd/logs --require-log-dir --full-only-on-bloat --reindex-only-on-bloat –watch
Example 4:
After VACUUM and REINDEX, it is always advisable to run ANALYZE last. 
$ idspgmaint -n idsldap -t ibmentryuuid,ldap_desc,ldap_entry,objectclass,title --exclude idsldap.title --analyze-only --watch
Example 5:
When downtime is unacceptable, avoid VACUUM FULL and perform an online VACUUM followed by ANALYZE. 
$ idspgmaint -n idsldap -t ibmentryuuid,ldap_desc,ldap_entry,objectclass,title --exclude idsldap.title --analyze –watch

If you use the --reindex-only-on-bloat option and the pgstattuple extension is not installed, you may need to adjust the reindex bloat estimate. Otherwise, the idspgmaint utility can indicate that a table requires reindexing even after it has already been reindexed. To adjust the reindex bloat estimate, use the following environment variables:

REINDEX_MIN_INDEX_PAGES:
  • Default value is '8'.
  • Skip tables whose largest index < 8 pages (64kB).
REINDEX_MIN_SAVED_PAGES:
  • Default is '3'.
  • Require at least 3 pages (24kB) of potential savings.
REINDEX_TINY_INDEX_PAGES_MAX:
  • Treat indexes with total pages <= <int> as "tiny". Default: 2 (root + 1 leaf).
  • Tiny indexes are treated as 0.00% bloat (and 0 saved pages).
REINDEX_DAMP_MODE:
  • Default value is 'product'.
  • From more to less aggressive damping ordered mode:

    power|product|min|division|max|none

  • Value 'none' won't adjust raw bloat_pct obtained without 'pgstattuple'.
REINDEX_DAMP_GAMMA:
  • Default value is '2.0'.
  • Exponent for 'power' mode (>=1.0). Exponent 1.0 aggressiveness ≈ 'min' mode.
  • Exponent 2.0 aggressiveness ≈ 'product' mode. Exponent greater than 2.0 aggressiveness > 'product' mode.