pgdoctor

package module
v0.2.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Apr 5, 2026 License: MIT Imports: 33 Imported by: 0

README

pgdoctor

pgdoctor logo

A command-line tool and Go library for running health checks against PostgreSQL databases. It identifies misconfigurations, performance issues, and areas for optimization through read-only checks that are safe to run against production.


Installation

Pre-built binaries

Download from GitHub Releases.

Go install
go install github.com/emancu/pgdoctor/cmd/pgdoctor@latest
Build from source
git clone https://github.com/emancu/pgdoctor.git
cd pgdoctor
go build -o pgdoctor ./cmd/pgdoctor

Quick Start

# List all available checks
pgdoctor list

# Get detailed documentation for a check
pgdoctor explain index-bloat

# Run all checks
pgdoctor run "postgres://user:pass@localhost:5432/mydb"

# Or use an environment variable
export PGDOCTOR_DSN="postgres://user:pass@localhost:5432/mydb"
pgdoctor run

# Run only specific checks
pgdoctor run "postgres://..." --only connection-health,indexes

# Explore all the flags
pgdoctor run "postgres://..." --help

Commands

pgdoctor run <DSN>

Run health checks against a PostgreSQL database. The DSN can be passed as a positional argument or via the PGDOCTOR_DSN environment variable.

Flag Description
--only Only run these checks or categories
--ignore Skip these checks or categories
--preset Check preset: all (default), triage
--detail Detail level: summary, brief (default), verbose, debug
--output Output format: text (default), json
--hide-passing Hide passing checks

Exit codes: 0 = all checks pass, 1 = failures found, 2 = connection error.

pgdoctor list

List all available checks organized by category.

pgdoctor explain <check-id>

Show detailed documentation for a specific check, including what it checks, why it matters, and how to fix issues.

Use --sql-only to display just the SQL query used by the check.

pgdoctor completion

Generate shell completion scripts for bash, zsh, fish, or powershell:

pgdoctor completion zsh > "${fpath[1]}/_pgdoctor"
pgdoctor completion bash > /etc/bash_completion.d/pgdoctor
Global Flags
Flag Description
--no-color Disable colored output
--no-colour Alias for --no-color
-v, --version Print version

Available Checks

configs
Check Description
pg-version PostgreSQL version support status
session-settings Role-level timeout and logging configurations
vacuum-settings Autovacuum, maintenance memory, and vacuum cost settings
replication-slots Replication slot configuration and health
connection-health Connection pool saturation, idle ratios, stuck transactions
connection-efficiency Session statistics for connection pool efficiency (PG 14+)
replication-lag Active replication stream lag
temp-usage Temporary file creation indicating work_mem exhaustion
statistics-freshness Statistics maturity for usage-based analysis
indexes
Check Description
invalid-indexes Indexes in invalid state needing rebuild
duplicate-indexes Exact and prefix duplicate indexes
index-usage Unused and inefficient indexes
index-bloat B-tree index bloat estimates
vacuum
Check Description
freeze-age Transaction ID age approaching wraparound
table-bloat Dead tuple percentages indicating vacuum issues
table-vacuum-health Per-table autovacuum configuration and activity
schema
Check Description
pk-types Primary keys using bigint or UUID for growth capacity
uuid-types UUID columns using native uuid type vs varchar/text
uuid-defaults UUID columns using v4 random defaults (B-tree bloat)
sequence-health Sequences approaching exhaustion
toast-storage TOAST storage usage optimization
partitioning Large/transient tables needing partitioning
performance
Check Description
cache-efficiency Buffer cache hit ratio
table-seq-scans Tables with excessive sequential scans
partition-usage Queries not using partition keys
table-activity Table write activity and HOT update efficiency

Using as a Library

pgdoctor can be used as a Go library in your own tools:

package main

import (
    "context"
    "fmt"

    "github.com/emancu/pgdoctor"
    "github.com/jackc/pgx/v5"
)

func main() {
    ctx := context.Background()
    conn, _ := pgx.Connect(ctx, "postgres://localhost:5432/mydb")
    defer conn.Close(ctx)

    pgdoctor.Run(ctx, conn, pgdoctor.Options{
        OnReport: func(report *check.Report) {
            fmt.Printf("[%s] %s\n", report.CheckID, report.Name)
        },
    })
}
Key API
// Run checks with the given options
pgdoctor.Run(ctx, conn, pgdoctor.Options{...})

// Collect reports into a slice (built-in handler)
pgdoctor.Collect(&reports) pgdoctor.ReportHandler

// List all built-in checks
pgdoctor.AllChecks() []check.Package

// Validate filter strings against a check set
pgdoctor.ValidateFilters(checks, filters) (valid, invalid []string)

The db.DBTX interface matches pgx.Conn, so pgdoctor works with any pgx-compatible connection.

Architecture

Each check is a self-contained Go package under checks/:

checks/indexbloat/
├── check.go      # Check logic
├── check_test.go # Unit tests
├── query.sql     # SQL query (embedded via go:embed)
└── README.md     # Documentation (embedded via go:embed)

Checks implement the check.Checker interface:

type Checker interface {
    Metadata() Metadata
    Check(context.Context) (*Report, error)
}

All SQL queries are read-only and use PostgreSQL system catalogs (pg_stat_*, pg_catalog). No data is modified.

Contributing

Contributions are welcome! See CONTRIBUTING.md for how to add checks and work with the codebase. For a detailed architecture and conventions reference, see AGENTS.md.

License

MIT

Documentation

Overview

Package pgdoctor implements health checks for common misconfiguration and issues of PostgreSQL databases.

Index

Constants

View Source 
const DefaultStatementTimeoutMs = 2000

DefaultStatementTimeoutMs is the PostgreSQL statement_timeout in milliseconds. Callers should SET this on the connection before calling Run().

Variables

This section is empty.

Functions

func AllChecks 

func AllChecks() []check.Package

AllChecks returns all available check packages. Consumers call .Metadata() for check information or .New(conn, cfg) to instantiate checkers.

func AllFilters 

func AllFilters() []string

AllFilters returns all valid filter values (check IDs and categories).

func Filter added in v0.2.0 

func Filter(checks []check.Package, only, ignored []string) []check.Package

Filter returns checks matching the only/ignored filters. If only is non-empty, only checks matching those check IDs or categories are included. Checks matching ignored check IDs or categories are excluded.

func Run 

func Run(ctx context.Context, conn db.DBTX, opts Options)

Run executes checks sequentially against the given connection.

Important: callers should SET statement_timeout on the connection before calling Run() to prevent slow queries from blocking the database. See DefaultStatementTimeoutMs.

func ValidateFilters 

func ValidateFilters(checks []check.Package, filters []string) (valid, invalid []string)

ValidateFilters normalizes filter strings and validates them against available checks. Returns valid filters (normalized to check IDs and categories) and invalid filters.

Normalization:

  • "check-id" -> "check-id" (exact match)
  • "check-id/subcheck-id" -> "check-id" (extracts check ID from subcheck)
  • "category" -> "category" (exact match)

Invalid filters are those that don't match any check ID or category.

Types

type Options added in v0.2.0 

type Options struct {
	Checks   []check.Package
	Config   check.Config
	OnReport ReportHandler
}

Options configures a pgdoctor run.

type ReportHandler added in v0.2.0 

type ReportHandler func(*check.Report)

ReportHandler is called once per check after it completes.

func Collect added in v0.2.0 

func Collect(reports *[]*check.Report) ReportHandler

Collect returns a ReportHandler that appends each report to the given slice.

Source Files

View all Source files

Directories

Path Synopsis
Package check defines the core types and interfaces for pgdoctor health checks.
 Package check defines the core types and interfaces for pgdoctor health checks.
checks
cacheefficiency
Package cacheefficiency implements checks for database buffer cache hit ratio.
 Package cacheefficiency implements checks for database buffer cache hit ratio.
connectionefficiency
Package connectionefficiency implements checks for PostgreSQL connection pool efficiency.
 Package connectionefficiency implements checks for PostgreSQL connection pool efficiency.
connectionhealth
Package connectionhealth implements checks for PostgreSQL connection pool health.
 Package connectionhealth implements checks for PostgreSQL connection pool health.
duplicateindexes
Package duplicateindexes implements checks for identifying duplicate and redundant indexes.
 Package duplicateindexes implements checks for identifying duplicate and redundant indexes.
freezeage
Package freezeage implements checks for PostgreSQL transaction ID wraparound risk.
 Package freezeage implements checks for PostgreSQL transaction ID wraparound risk.
indexbloat
Package indexbloat implements checks for PostgreSQL B-tree index bloat estimation.
 Package indexbloat implements checks for PostgreSQL B-tree index bloat estimation.
indexusage
Package indexusage implements checks for identifying unused and inefficient indexes.
 Package indexusage implements checks for identifying unused and inefficient indexes.
invalidindexes
Package invalidindexes implements a check for identifying PostgreSQL indexes in an invalid state.
 Package invalidindexes implements a check for identifying PostgreSQL indexes in an invalid state.
partitioning
Package partitioning implements checks for table partitioning compliance.
 Package partitioning implements checks for table partitioning compliance.
partitionusage
Package partitionusage implements checks for partition key usage in queries.
 Package partitionusage implements checks for partition key usage in queries.
pgversion
Package pgversion implements a check for PostgreSQL version compliance and support status.
 Package pgversion implements a check for PostgreSQL version compliance and support status.
pktypes
Package pktypes validates primary key types for capacity and growth.
 Package pktypes validates primary key types for capacity and growth.
replicationlag
Package replicationlag implements checks for replication lag monitoring.
 Package replicationlag implements checks for replication lag monitoring.
replicationslots
Package replicationslots implements a check for PostgreSQL replication slot health.
 Package replicationslots implements a check for PostgreSQL replication slot health.
sequencehealth
Package sequencehealth implements checks for PostgreSQL sequence capacity and type safety.
 Package sequencehealth implements checks for PostgreSQL sequence capacity and type safety.
sessionsettings
Package sessionsettings implements a check for validating role-level timeout and logging configurations.
 Package sessionsettings implements a check for validating role-level timeout and logging configurations.
statisticsfreshness
Package statisticsfreshness validates that PostgreSQL statistics are mature enough for accurate analysis.
 Package statisticsfreshness validates that PostgreSQL statistics are mature enough for accurate analysis.
tableactivity
Package tableactivity implements checks for table write activity patterns.
 Package tableactivity implements checks for table write activity patterns.
tablebloat
Package tablebloat implements checks for PostgreSQL table bloat from dead tuples.
 Package tablebloat implements checks for PostgreSQL table bloat from dead tuples.
tableseqscans
Package tableseqscans implements checks for identifying tables with excessive sequential scan activity.
 Package tableseqscans implements checks for identifying tables with excessive sequential scan activity.
tablevacuumhealth
Package tablevacuumhealth implements checks for per-table autovacuum configuration.
 Package tablevacuumhealth implements checks for per-table autovacuum configuration.
tempusage
Package tempusage implements checks for PostgreSQL temporary file creation.
 Package tempusage implements checks for PostgreSQL temporary file creation.
toaststorage
Package toaststorage implements checks for PostgreSQL TOAST storage analysis.
 Package toaststorage implements checks for PostgreSQL TOAST storage analysis.
uuiddefaults
Package uuiddefaults detects UUID columns using random UUIDs (v4) as defaults.
 Package uuiddefaults detects UUID columns using random UUIDs (v4) as defaults.
uuidtypes
Package uuidtypes validates UUID columns use native uuid type.
 Package uuidtypes validates UUID columns use native uuid type.
vacuumsettings
Package vacuumsettings implements a check for validating autovacuum, maintenance memory, and vacuum cost settings.
 Package vacuumsettings implements a check for validating autovacuum, maintenance memory, and vacuum cost settings.
cmd
pgdoctor command
Package main is the entry point for the pgdoctor CLI.
 Package main is the entry point for the pgdoctor CLI.
internal
cli
Package cli implements the pgdoctor command-line interface.
 Package cli implements the pgdoctor command-line interface.
gen command
Package main generates the checks.go file for pgdoctor CLI by discovering check packages.
 Package main generates the checks.go file for pgdoctor CLI by discovering check packages.
gendocs command
Package main generates the docs/ directory for pgdoctor's GitHub Pages landing page.
 Package main generates the docs/ directory for pgdoctor's GitHub Pages landing page.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.