🩺 pgvitals

When PostgreSQL is slow, the answer is always in the catalog — if you know where to look.

pgvitals is a curated, production-tested collection of diagnostic queries that cover every common performance bottleneck: slow queries, index health, table bloat, vacuum lag, connection exhaustion, lock contention, replication lag, wraparound risk, and more. Each query comes with a header that tells you exactly what to look for and what to do about it.

Run a single section during an incident. Run all of them before a release. Wire up the snapshot framework to collect metrics during a load test.

# Single section — e.g. what's blocking right now
psql -d mydb -f sql/21_lock_wait_tree.sql

# Full diagnostic sweep
for f in sql/*.sql; do echo "=== $f ==="; psql -d mydb -f "$f"; done

# Or load the combined master file
psql -d mydb -f master.sql

# Health score — 0-100 with per-check breakdown (no extensions needed)
psql -d mydb -f health_score.sql

pip install pgvitals
pgvitals init                                         # download SQL files
pgvitals --host db.example.com --database prod        # full diagnostic run

Prerequisites (one-time setup):

-- postgresql.conf (requires restart)
shared_preload_libraries = 'pg_stat_statements'

-- per database
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
GRANT pg_monitor TO your_user;   -- PostgreSQL 10+

Every section follows the same four-line header:

-- ============================================================
-- 21 · LOCK WAIT TREE (BLOCKING CHAINS)
-- ============================================================
-- What    : Full chain of who is blocking whom
-- Look for: Any row — every lock wait degrades throughput
-- Action  : Find root blocker (blocking_pids = '{}') and
--           investigate or terminate: pg_terminate_backend(pid)
-- ============================================================

No hunting through docs. The threshold and next step are right there.

For stress testing, pgvitals includes a snapshot framework that records metrics over time so you can diff baseline vs peak.

\i monitoring/schema.sql           -- creates perf_monitor schema
\i monitoring/capture_snapshot.sql -- installs capture_snapshot()

-- Before your load test
SELECT perf_monitor.capture_snapshot('baseline');

-- During the test — or automate with \watch
SELECT perf_monitor.capture_snapshot('peak'); \watch 30

-- After the test
SELECT perf_monitor.capture_snapshot('cooldown');

\i monitoring/trend_queries.sql

Queries included: connection saturation over time · lock wait spikes · dead tuple growth · cache hit ratio trend · deadlock count · checkpoint pressure · temp file spill · rollback rate.

DROP SCHEMA perf_monitor CASCADE;

pgvitals includes a Python-based runner that executes all sections against a live database and generates a Markdown health report.

pip install pgvitals
pgvitals init                                               # download SQL files once
pgvitals --host db.example.com --user dba --database prod  # full diagnostic run
pgvitals --profile staging                                  # use a named config profile
pgvitals --sections 01,03,19,26,32                         # run specific sections only
pgvitals --skip 05,36                                       # skip specific sections
pgvitals --output ./report.md                               # custom output path
pgvitals --format html -o report.html                       # self-contained shareable HTML report

cd runner

# Copy sample config and edit your connection details
cp pgvitals.conf.example pgvitals.conf

# Run all diagnostics
python run_diagnostics.py

# Use a named profile
python run_diagnostics.py --profile staging

# Override connection via CLI
python run_diagnostics.py --host db.example.com -U monitor -d production

The image bundles the SQL sections and psql — nothing to install but Docker:

# Full diagnostic run
docker run --rm -e PGPASSWORD=secret ghcr.io/pgvitals/pgvitals \
  --host db.example.com --user dba --database prod

# Save a self-contained HTML report to the current directory
docker run --rm -e PGPASSWORD=secret -v "$PWD:/out" ghcr.io/pgvitals/pgvitals \
  --host db.example.com --user dba --database prod \
  --format html --output /out/report.html

The runner uses a layered config system (lowest → highest priority):

1. pgvitals.conf (or --config <path>)
2. Named profiles (--profile staging)
3. Environment variables (PGPASSWORD, PGHOST, etc.)
4. CLI arguments (--host, --user, --database)

See pgvitals.conf.example for all available options.

Reports are written to runner/reports/ with auto-generated filenames and include:

• Executive summary table with status badges
• Health score breakdown by area
• Detailed output for every section
• Prioritized recommendations (critical → medium → info)

Pass --format html for a self-contained, shareable HTML report — a single file (inline CSS/JS, no external assets, works from file://) with a 0–100 health-score gauge, color-coded collapsible section cards, and a one-click Print / Save PDF button. Markdown remains the default.

--format json emits a stable, machine-readable document (schema_version 1.0) with the score/grade, per-area rollups, and one object per section (num, area, risk, status, rows, header, raw output):

pgvitals --host db.example.com --database prod --format json -o pgvitals.json
jq '.score, (.sections[] | select(.status=="findings" and .risk=="critical").title)' pgvitals.json

--format prometheus turns a one-shot run into a scrapeable, alertable signal in the text exposition format — ideal for the node_exporter textfile collector:

pgvitals --host db.example.com --database prod \
  --format prometheus -o /var/lib/node_exporter/textfile_collector/pgvitals.prom

Key series (every metric is labeled by database; sections also carry area, risk, title):

pgvitals_health_score{database="prod"}                     82
pgvitals_section_findings{database="prod",section="26",risk="critical",...}  1
pgvitals_section_up{database="prod",section="36"}           0     # errored / unavailable
pgvitals_sections{database="prod",status="findings"}        7

Because risk is a label, alerting is one expression — e.g. page on any critical finding:

max(pgvitals_section_findings{risk="critical"}) > 0

Add --explain to get a prioritized, plain-English analysis of the findings — "what's wrong → fix in this order", with the exact SQL/commands — generated by Claude and embedded at the top of the report (both Markdown and HTML).

export ANTHROPIC_API_KEY=sk-ant-...
pgvitals --host db.example.com --database prod --explain --format html -o report.html
pgvitals --host db.example.com --database prod --explain --explain-model claude-opus-4-8

• Uses the Anthropic API directly over urllib — no extra dependencies to install.
• Default model: claude-sonnet-4-6 (override with --explain-model).
• Privacy: --explain sends the diagnostic findings (section titles, suggested actions, truncated query output, and the database name) to Anthropic. It is strictly opt-in and does nothing unless ANTHROPIC_API_KEY is set — a run without the flag never leaves your machine. If the key is missing or the API call fails, the diagnostic report is still produced as normal.

Note: runner/pgvitals.conf is gitignored to protect credentials. Never commit database passwords.

• Python 3.8+ (no third-party dependencies)
• psql on PATH or configured in pgvitals.conf

Get a single 0–100 score for your database with a per-check breakdown — no extensions, no config, just SQL:

psql -d mydb -f health_score.sql

╔════════════════════════╤═════════╤══════════════════════════════╤══════════╤══════════╤══════════╗
║ Score                  │ Grade   │ Status                       │ ✓ Passed │ ~ Warned │ ✗ Failed ║
╠════════════════════════╪═════════╪══════════════════════════════╪══════════╪══════════╪══════════╣
║ 87 / 100               │ B       │ Good — minor issues to review│        8 │        1 │        0 ║
╚════════════════════════╧═════════╧══════════════════════════════╧══════════╧══════════╧══════════╝

╔═══╤══════════════════════════╤═══════════╤═══════════╤════════════════════════════════════════╗
║ # │ Check                    │ Score     │ Status    │ Detail                                 ║
╠═══╪══════════════════════════╪═══════════╪═══════════╪════════════════════════════════════════╣
║ 1 │ XID Wraparound           │ 20 / 20   │ ✓ Clear   │ max age: 2,933 txns (limit ~2 billion) ║
║ 2 │ Dead Tuple Bloat         │ 15 / 15   │ ✓ Clear   │ 0 table(s) with >10% dead tuples       ║
║ 3 │ Connection Saturation    │ 15 / 15   │ ✓ Clear   │ 63.0% of max_connections in use        ║
║ 4 │ Lock Waits               │ 10 / 10   │ ✓ Clear   │ 0 session(s) currently blocked         ║
║ 5 │ Buffer Cache Hit Ratio   │  5 / 10   │ ~ Warning │ 93.20% hit rate (target ≥ 95%)         ║
║ 6 │ Invalid Indexes          │ 10 / 10   │ ✓ Clear   │ 0 invalid index(es) detected           ║
║ 7 │ Idle-in-Transaction      │ 10 / 10   │ ✓ Clear   │ 0 session(s) stuck in open transaction ║
║ 8 │ Replication Lag          │  5 /  5   │ ✓ Clear   │ 0 standby(s) configured                ║
║ 9 │ Sequence Exhaustion      │  5 /  5   │ ✓ Clear   │ 0 sequence(s) above 80% capacity       ║
╚═══╧══════════════════════════╧═══════════╧═══════════╧════════════════════════════════════════╝

Add a scheduled health check to any repo — copies the workflow template and wires up your DB secrets:

# Copy the template to your repo
cp .github/workflows/pgvitals-healthcheck.yml path/to/your-repo/.github/workflows/

# Set these secrets in Settings → Secrets → Actions:
#   PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD

The health score runs on every push and on a weekly schedule. Results appear in the Actions job summary. The job fails if the grade drops to D or F.

See the full template: .github/workflows/pgvitals-healthcheck.yml

Prefer a one-liner over copying a template? Use the composite action directly — it runs the health score, posts it to the job summary, exposes score/grade outputs, and fails the job below a threshold:

- name: PostgreSQL health check
  uses: pgvitals/pgvitals@v1
  with:
    host: ${{ secrets.PGHOST }}
    database: ${{ secrets.PGDATABASE }}
    user: ${{ secrets.PGUSER }}
    password: ${{ secrets.PGPASSWORD }}
    fail-under: '60'      # fail the job if score < 60 (set 0 to never fail)

Inputs: host (required), database (required), user (required), password, port (default 5432), sslmode (default prefer), fail-under (default 60). Outputs: score, grade.

The workflow also generates a self-contained SVG badge (e.g. postgres health: B · 87), coloured by grade — no shields.io or third-party service. It's uploaded as a build artifact; to show it in your README, uncomment the Publish badge to badges branch step in the template and embed:

![pgvitals](https://raw.githubusercontent.com/<owner>/<repo>/badges/pgvitals-badge.svg)

Generate one locally too: python runner/make_badge.py --score 87 --grade B --out badge.svg

pgvitals/
├── sql/                        40 individual diagnostic queries
│   ├── 00_prerequisites.sql
│   ├── 01_slow_queries.sql
│   └── ...
├── health_score.sql            0-100 health score (standalone)
├── monitoring/                 Load test snapshot framework
│   ├── schema.sql
│   ├── capture_snapshot.sql
│   └── trend_queries.sql
├── runner/                     Diagnostic runner & report generator
│   ├── run_diagnostics.py      Main runner script (Python 3.8+)
│   ├── pgvitals.conf.example
│   └── reports/
├── pgvitals/                   pip-installable package
│   ├── __init__.py
│   └── cli.py
├── pyproject.toml              pip package config
├── docs/
│   └── SECTIONS.md
├── master.sql
└── README.md

Every query in sql/ (and master.sql, health_score.sql) is read-only: plain SELECTs against system catalogs and statistics views. There is no INSERT/UPDATE/DELETE/DDL, no table locks beyond the momentary AccessShareLock any SELECT takes, and nothing that blocks your workload — safe to run on production during an incident.

This is enforced in CI, not just promised:

• The build fails if any section file contains a DML/DDL keyword.
• Every section is executed against PostgreSQL 13 – 17 on each push (version-gated sections are skipped only where the underlying view doesn't exist yet).

The only components that write are opt-in and clearly separated: monitoring/ creates a dedicated perf_monitor schema for load-test snapshots, which you install explicitly and remove with DROP SCHEMA perf_monitor CASCADE.

Most diagnostic tools require installation, a running agent, or a specific language runtime. pgvitals is just SQL — it works anywhere psql works, requires no dependencies beyond pg_stat_statements, and every query is readable and auditable.

Contributions are welcome — new sections, improved queries, fixes, and documentation improvements.

Please read CONTRIBUTING.md before opening a PR. Every new section should follow the standard header format so the collection stays consistent.

MIT — see LICENSE.