postgres-dba

PostgreSQL DBA Skill

A complete skill for DBA agents to monitor, diagnose, and manage PostgreSQL databases using Matimo tools.

Prerequisites

All tools communicate with PostgreSQL via a REST API wrapper. Ensure:

1. A REST API (PostgREST, custom Express/FastAPI, or Supabase) is running and accessible.
2. Two environment variables are set:
   • PG_API_URL — Base URL of the REST API (e.g., http://localhost:3000)
   • PG_API_KEY — Bearer token for authenticating with the REST API

The API must expose a POST /query endpoint accepting { sql, database } and returning { rows, rowCount, fields }.

---

Tools You Will Use

Tool	Purpose
pg_run_query	Execute arbitrary read-only SQL SELECT statements
pg_table_stats	Get table size, live/dead row counts, and VACUUM history
pg_slow_queries	Fetch top slow queries from pg_stat_statements
pg_index_health	Inspect index usage stats and identify unused indexes
pg_active_connections	Monitor live connections and long-running transactions
pg_vacuum_status	Check dead tuple counts and autovacuum status per table

---

Workflow 1: Health Check (Start Here)

When a user asks "how is my database doing?", run this sequence:

Step 1 — Active Connections Snapshot

pg_active_connections({ state: "all", minDurationSeconds: 0 })

Look for: high connection count, idle in transaction states, long-running queries (duration > 30s).

Step 2 — Table Health Overview

pg_table_stats({ schema: "public" })

Look for: high dead_rows relative to live_rows, missing recent last_autovacuum timestamps.

Step 3 — VACUUM Pressure Check

pg_vacuum_status({ schema: "public", deadTupleThreshold: 5000 })

Look for: dead_pct above 10%, stale last_autovacuum on high-write tables.

Step 4 — Index Health

pg_index_health({ schema: "public", showUnused: true })

Look for: large indexes with 0 scans (storage waste and write overhead).