pg_statviz

Overview

Cataloged but hidden from default package groups. GitHub release and control are 1.1 while PGXN still serves 1.0. PGDG DEB 1.1 covers active PG14-18 except Ubuntu 22.04 and recommends the separate Python utility, so a normal APT install can pull its Python stack. PGDG RPM remains at 0.9, lacks PG17, and provides PG18 only on EL10; its metadata declares no PostgreSQL dependency, labels GPLv2+ although upstream uses the PostgreSQL License, and describes a CLI although the subpackage contains only extension SQL and control files. The extension itself is pure SQL and PL/pgSQL and needs no preload.

Version

Install

You can install pg_statviz directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_statviz;          # Install for current active PG version

pig ext install -y pg_statviz -v 18  # PG 18
pig ext install -y pg_statviz -v 17  # PG 17
pig ext install -y pg_statviz -v 16  # PG 16
pig ext install -y pg_statviz -v 15  # PG 15
pig ext install -y pg_statviz -v 14  # PG 14

dnf install -y pg_statviz_extension_18       # PG 18
dnf install -y pg_statviz_extension_17       # PG 17
dnf install -y pg_statviz_extension_16       # PG 16
dnf install -y pg_statviz_extension_15       # PG 15
dnf install -y pg_statviz_extension_14       # PG 14

apt install -y postgresql-18-statviz   # PG 18
apt install -y postgresql-17-statviz   # PG 17
apt install -y postgresql-16-statviz   # PG 16
apt install -y postgresql-15-statviz   # PG 15
apt install -y postgresql-14-statviz   # PG 14

Create Extension:

CREATE EXTENSION pg_statviz CASCADE;  -- requires: plpgsql

Usage

Sources:

• pg_statviz v1.1 release
• pg_statviz v1.1 README
• pg_statviz v1.1 installation SQL
• pg_statviz v1.1 control file
• pg_statviz v1.1 metadata
• pg_statviz v1.1 Python package metadata
• pg_statviz v1.1 AI provider implementation
• Official PGXN distribution

pg_statviz v1.1 is a pure SQL and PL/pgSQL statistics snapshot extension plus a separately installed Python visualization utility. The extension stores cumulative and dynamic PostgreSQL statistics in the fixed pgstatviz schema; the utility reads a selected time range and generates charts or optional AI-assisted HTML reports. It requires PostgreSQL 13 or later, needs no shared_preload_libraries, and does not require a restart. The utility requires Python 3.11 or later.

Capture and Retain Snapshots

Have an administrator install the extension, then let a dedicated collection role inherit pg_monitor and schedule pgstatviz.snapshot() with cron or another external job runner.

CREATE EXTENSION pg_statviz;

GRANT pg_monitor TO stats_collector;

SELECT pgstatviz.snapshot();

DELETE FROM pgstatviz.snapshots
WHERE snapshot_tstamp < CURRENT_DATE - 90;

Deleting parent rows cascades to the associated samples. pgstatviz.delete_snapshots() instead truncates the complete history. Pick an interval and retention window based on the shortest event worth observing and the resulting table growth; raw PostgreSQL counters are cumulative and can reset independently, so analyze timestamped deltas rather than treating stored values as rates.

Stored Data and Version Boundaries

The main relations are pgstatviz.snapshots, pgstatviz.buf, pgstatviz.conf, pgstatviz.conn, pgstatviz.db, pgstatviz.io, pgstatviz.lock, pgstatviz.repl, pgstatviz.slru, pgstatviz.wait, and pgstatviz.wal. Samples include configuration values, connection user names and ages, replication application and slot names, waits, locks, I/O, database counters, and WAL counters. Protect the tables, dumps, charts, and reports as operational data.

Configuration is stored only when it changes, so pgstatviz.conf need not contain one row for every snapshot. pg_stat_wal data is collected on PostgreSQL 14 and later; pg_stat_io data is collected on PostgreSQL 16 and later, with PostgreSQL 18’s byte-based fields handled separately. On older supported versions those tables remain part of the schema, but the unavailable collectors are skipped.

The extension marks its snapshot tables for extension-aware dumps. This allows history to be moved with pg_dump, but retention and backup size still need deliberate limits.

Visualize a Time Range

Install the utility separately and pass normal libpq connection options. The analyze command runs every analysis module; individual modules such as conn, io, wait, and wal can be selected when a narrower report is sufficient.

pip install pg_statviz

pg_statviz analyze \
  -h /var/run/postgresql -d mydb -U stats_reader \
  -D 2026-08-01T00:00 2026-08-02T00:00 \
  -O /srv/pg_statviz/reports

Restrict database credentials and report-directory access. A visualization role needs read access to the captured schema but does not need permission to collect or delete snapshots.

Privilege Boundary

The v1.1 installation SQL grants every member of pg_monitor schema usage, function execution, and SELECT, INSERT, DELETE, and TRUNCATE on all pgstatviz tables. Consequently, membership allows both snapshot collection and complete history removal through pgstatviz.delete_snapshots(); it is not a read-only visualization role.

If collection, visualization, and retention administration must be separated, revise the default grants after installation and grant only the required functions and table privileges to dedicated roles. Recheck those grants after an extension update.

Optional AI and Cloud Data Review

Normal chart generation makes no LLM request. AI mode requires the optional pg_statviz[ai] dependencies and an explicit --ai flag. Claude is the default cloud provider and reads ANTHROPIC_API_KEY; Gemini reads GOOGLE_API_KEY; --ai local uses a local Ollama service. The current defaults are claude-sonnet-4-6, gemini-2.5-flash, and gemma4:e4b; these are implementation defaults, not a guarantee that a provider account or local runtime will continue to offer them.

pip install 'pg_statviz[ai]'

pg_statviz analyze \
  -h /var/run/postgresql -d mydb -U stats_reader \
  -D 2026-08-01T00:00 2026-08-02T00:00 \
  -O /srv/pg_statviz/reports \
  --ai gemini

For a cloud provider, the request can include chart images and summarized series together with the captured PostgreSQL version, primary/standby role, hostname, relevant configuration values, deterministic findings, user or role names, and replication identifiers. Treat that as an explicit operational-data export: review provider retention and regional policy, minimize the selected time range, secure generated HTML and PNG files, and use an approved outbound path. The prompt’s data envelopes reduce prompt-injection risk but do not provide confidentiality, authorization, or a substitute for provider governance.