Postgres Tools for FastPluggy

A powerful PostgreSQL database monitoring and management plugin for FastPluggy applications. This plugin provides a user-friendly interface to monitor and manage various aspects of your PostgreSQL databases.

Features

• PostgreSQL Sequences Monitoring:

  • View comprehensive sequence information including name, last value, max value, and remaining capacity
  • Monitor sequence usage with percentage metrics (percent used and percent remaining)
  • Identify sequences that are close to reaching their maximum value
  • Filter sequences by schema (excludes system schemas like pg_catalog and information_schema)
  • Sort sequences by remaining capacity to prioritize attention

• Database Size & Tablespace Usage:

  • View total database size and breakdown by schema and tablespace
  • Identify the largest tables in your database
  • See detailed size information for tables and their indexes
  • Filter by schema and include/exclude system schemas
  • Sort by size to identify storage usage patterns

• Index Usage Statistics:

  • Track per-index scan counts and rows read
  • Highlight unused indexes below a scan threshold
  • Show index sizes and offer one-click drop operations
  • Filter by schema (excludes system schemas like pg_catalog and information_schema)
  • Sort by scan count to identify unused indexes

• Table Statistics & Bloat:

  • Monitor table health by reporting live vs. dead rows
  • Estimate bloat percentage and show disk size
  • Surface last vacuum/analyze times
  • Optional pgstattuple integration for exact bloat measurement
  • Highlight tables with high bloat percentage
  • Filter by schema (excludes system schemas like pg_catalog and information_schema)

• Query Performance Analysis:

  • Leverage pg_stat_statements to list top slow queries
  • View queries by total/mean time
  • Option to include 95th percentile statistics
  • Automatic detection of pg_stat_statements extension
  • Installation instructions if extension is not available

• Connection & Lock Monitoring:

  • Display all active sessions (state, duration, query)
  • Show current lock contention
  • Flag long-running queries
  • Filter connections by minimum duration and state
  • View detailed information about blocking and waiting queries

• Vacuum/Autovacuum Status:

  • Show last manual/autovacuum times per table
  • Display dead-tuple counts
  • Monitor live progress of ongoing autovacuum jobs
  • View autovacuum settings
  • Highlight tables that haven't been vacuumed recently
  • NEW: Trigger manual vacuum operations with one-click buttons
  • NEW: Support for both regular VACUUM ANALYZE and VACUUM FULL ANALYZE operations
  • NEW: Built-in confirmation dialogs for safety

• Replication & Backups:

  • Report replication status and lag metrics (write_lag, replay_lag)
  • Track backup history and recency
  • Alert for stale backups or high lag
  • Detect primary/replica status automatically
  • Show replication slots and retained WAL size

• Custom Hooks & Webhooks:

  • Define threshold-based alerts on any metric
  • Post notifications to Slack, Discord, Microsoft Teams, PagerDuty, or custom endpoints
  • Test webhook functionality
  • Enable/disable alerts individually
  • Store alert configuration in the database

• Global Dashboard & Recommendations:

  • One-page health overview with combined alert list
  • Actionable recommendations for database improvements
  • Extension installation suggestions
  • Database configuration recommendations
  • Key metrics summary

For detailed specifications of each feature, see the individual files in the docs/feature/ directory.

Installation

Install the official plugin package:

pip install fastpluggy-postgres-tools

Configuration

The plugin uses the same database connection that is configured for your FastPluggy application. No additional configuration is required if your application is already connected to a PostgreSQL database.

Usage

Web Interface

Once installed, access the PostgreSQL tools at /postgres/ in your FastPluggy application. The interface allows you to:

1. View detailed information about PostgreSQL sequences
2. Monitor sequence usage and remaining capacity
3. Identify sequences that may need attention (approaching maximum value)
4. View database size information and breakdown by schema and tablespace
5. Identify the largest tables in your database
6. Monitor storage usage patterns

API Access

The plugin also provides API endpoints for programmatic access to all features. See the API Endpoints Documentation for details on available endpoints, parameters, and response formats.

Development

Requirements

• Python 3.10+
• FastPluggy
• SQLAlchemy 2.0.0+
• PostgreSQL database

Setup

1. Clone the repository
2. Install dependencies: pip install -e .
3. Run your FastPluggy application with this plugin enabled

Building

python -m build

License

This project is licensed under the MIT License.

Recommended PostgreSQL extensions

These extensions enable or enhance specific features in this plugin. If you run on a managed PostgreSQL service, you may need to enable them from your provider’s control panel and/or restart the database.

• pg_stat_statements — Required for the Query Performance view

  • Purpose: Collects per-SQL statistics such as total/mean execution time and call counts.
  • How to enable:
    1. Ensure it is preloaded in postgresql.conf: shared_preload_libraries = 'pg_stat_statements' (may require a restart).
    2. Then in each database where you want stats, run: CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
  • In-app shortcut: Go to /postgres/queries and use the “Install Extension” button if available.

• pgstattuple — Optional, for more accurate table bloat metrics

  • Purpose: Provides functions to inspect the exact amount of bloat and tuple statistics.
  • How to enable: CREATE EXTENSION IF NOT EXISTS pgstattuple;

Notes

• You need sufficient privileges (often a superuser or a role with CREATE privilege) to install extensions.
• If an extension cannot be installed, the corresponding views will still work in a degraded mode (e.g., approximate bloat estimates without pgstattuple, or no query performance data without pg_stat_statements).

Required database privileges

Don't make the app's own DB role a superuser, even though several views (query stats, connection/lock monitoring, vacuum status) want cluster-wide visibility. On a shared Postgres instance — one Postgres serving several apps' databases — a superuser role can read/write/drop any database on that instance, not just this app's own. A bug anywhere in the app then means a full compromise of every other app's data on that instance, not just this one's. This bit us for real on the shared homelab Postgres (shared_tools app, 2026-07): the plan started as "give it superuser to inspect everything" before catching this.

Instead, grant the built-in pg_monitor role, which exists specifically for monitoring tools like this plugin:

GRANT pg_monitor TO <app_db_role>;

pg_monitor bundles pg_read_all_settings + pg_read_all_stats + pg_stat_scan_tables — read access to pg_stat_statements, pg_stat_activity, all settings, and table-level stats, across every database on the instance, with no write and no superuser power. That covers everything this plugin inspects: the queries view, dashboard health/vacuum stats, connection monitoring.

What it does not cover: installing a non-trusted extension (CREATE EXTENSION pg_stat_statements / vector / …) still needs a genuine superuser, one time, per database — see "Recommended PostgreSQL extensions" above. That's fine as a deliberate, rare admin action; it doesn't justify making the app's day-to-day role a superuser. The dashboard's ALTER SYSTEM recommendations are informational text only today (no button executes them), so no extra privilege is needed for those either.