GitHub - mnafees/pgtk: Pure-SQL diagnostic functions for PostgreSQL - load via psql, no extensions required

3 min read Original article ↗

PostgreSQL Toolkit -- a collection of pure-SQL diagnostic functions you can load into any psql session. No C extensions, no shared libraries, no restart required. Works on any PostgreSQL instance including managed services (RDS, Cloud SQL, AlloyDB, Supabase, Neon).

Inspired by pg_ash.

Requirements

  • PostgreSQL 12+
  • Access to pg_stat_activity, pg_locks, pg_stat_user_tables, and pg_class

Installation

This creates a pgtk schema with all diagnostic functions.

Functions

pgtk.sizes()

All relations ordered by total size (excluding system catalogs and toast tables).

SELECT * FROM pgtk.sizes();

pgtk.long_queries(max_chars int DEFAULT 200)

Active (non-idle) queries ordered by duration. Truncates query text to max_chars.

SELECT * FROM pgtk.long_queries();
SELECT * FROM pgtk.long_queries(max_chars => 500);

pgtk.last_analyze(schema_name text DEFAULT 'public')

Last manual and auto-analyze timestamps per table in a given schema.

SELECT * FROM pgtk.last_analyze();
SELECT * FROM pgtk.last_analyze('myschema');

pgtk.bloat(top_n int DEFAULT 20)

Tables with dead tuples, ordered by dead tuple percentage. Useful for identifying vacuum candidates.

SELECT * FROM pgtk.bloat();
SELECT * FROM pgtk.bloat(top_n => 50);

pgtk.locks()

All current locks with database name, relation, lock type, mode, and grant status.

SELECT * FROM pgtk.locks();

pgtk.idle_tx()

Sessions stuck in idle in transaction state, ordered by how long they have been idle.

SELECT * FROM pgtk.idle_tx();

pgtk.index_usage(min_size bigint DEFAULT 0)

All indexes ordered by scan count (ascending), so unused/underused indexes appear first. Filter by minimum index size in bytes to focus on large unused indexes.

SELECT * FROM pgtk.index_usage();
SELECT * FROM pgtk.index_usage(min_size => 1048576);  -- only indexes >= 1 MB

pgtk.cache_hit()

Buffer cache hit ratio per database. A ratio below 99% usually means you need more shared_buffers.

SELECT * FROM pgtk.cache_hit();

pgtk.seq_scans(top_n int DEFAULT 20)

Tables with the most sequential scans. High seq scan counts on large tables suggest missing indexes.

SELECT * FROM pgtk.seq_scans();
SELECT * FROM pgtk.seq_scans(top_n => 50);

pgtk.connections()

Active connections grouped by database, user, and state.

SELECT * FROM pgtk.connections();

pgtk.duplicate_indexes()

Indexes that share the same column set on the same table. These are redundant and waste disk/write throughput.

SELECT * FROM pgtk.duplicate_indexes();

pgtk.wraparound()

Transaction ID age per database and percentage towards the 2-billion wraparound limit. Databases approaching 50%+ need attention.

SELECT * FROM pgtk.wraparound();

pgtk.replication_lag()

Streaming replication status and lag for each replica. Requires the session to be on the primary.

SELECT * FROM pgtk.replication_lag();

pgtk.non_default_settings()

PostgreSQL settings that have been changed from their defaults. Useful for auditing configuration.

SELECT * FROM pgtk.non_default_settings();

pgtk.vacuum_blockers()

Ungranted locks on relations -- these are the queries preventing vacuum from running.

SELECT * FROM pgtk.vacuum_blockers();

Uninstall

DROP SCHEMA pgtk CASCADE;