Skip to content

cloudthinker-ai/CloudSkills / skills/connections/analyzing-postgres

analyzing-postgres

Unverified

PostgreSQL database analysis, performance tuning, and health monitoring. You MUST read this entire skill document before executing any PostgreSQL operations — it contains mandatory workflows, safety constraints, and two-phase execution rules that prevent common errors like hallucinated column names and unsafe queries.

Analyzing PostgreSQL

Discovery

What discovery provides:

  • Database overview from get_database_overview():
    • schemas: List of database schemas
    • tables: Tables with names, row counts, sizes, and column information
    • indexes: Index information and statistics
    • relationships: Foreign key relationships between tables
    • size_info: Database and table size metrics
    • security: Security score (0-100), superuser count, SSL status, issues and recommendations
    • performance_hotspots: Tables with high sequential scans, dead tuples, bloat, or high modification rates

Discovery options (script defaults, NOT MCP server defaults):

  • --max-tables N: Limit tables analyzed (script default: 50, MCP server default: 500)
  • --sampling true: Use sampling for large tables (script default: false, MCP server default: true)
  • --timeout N: Timeout in seconds (script default: 60, MCP server default: 300)
  • --alias <name>: Target a specific postgres instance (required for multi-instance workspaces)

Why run discovery:

  • Get actual table/column names (never guess - they vary between databases)
  • Understand relationships before writing JOINs
  • Identify large tables that need sampling
  • Know schema structure before executing SQL

ALWAYS use format() for output (40-60% token savings):

import { format } from "@connections/_utils/format";
console.log(format(result)); // CORRECT
console.log(JSON.stringify(result, null, 2)); // WRONG

Two-Phase Execution (MANDATORY)

Phase 1 - Discovery Script:

const schema = await get_object_details({ schema_name: "public", object_name: "user", object_type: "table" });
console.log(format(schema));
// ⛔ STOP - End script here, read output

[CHECKPOINT: Read output, identify actual column names]

Phase 2 - Query Script (NEW execution):

// Use ONLY verified column names from Phase 1
const results = await execute_sql({ sql: `SELECT verified_col FROM ...` });

❌ FORBIDDEN: get_object_details() + execute_sql() in same script

Bad ❌Good ✅Why
Discovery + query in one scriptTwo separate scriptsPrevents hallucinated column names
Assuming user_id existsRun get_object_details() firstForeign key naming varies
created_at, updated_atVerify columns existMay be named differently
Writing JOINs without discoveryDiscover ALL tables firstRelationships vary

Tools (13 total)

Schema (3): list_schemas(), list_objects(schema, type?), get_object_details(schema, name, type?)

Query (1): execute_sql(sql) - read-only in restricted mode

Performance (4):

  • explain_query(sql, analyze?, hypothetical_indexes?) - CONSTRAINT: analyze + hypothetical_indexes cannot be used together; hypothetical_indexes requires HypoPG extension
  • get_top_queries(sort_by?, limit?) - default sort_by: "resources" (multi-dimensional blend, not just time)
  • analyze_workload_indexes(max_size_mb?, method?) - analyzes top queries from pg_stat_statements
  • analyze_query_indexes(queries[], max_size_mb?, method?) - CONSTRAINT: max 10 queries per call

Health (3):

  • analyze_db_health(type?) - accepts comma-separated types (e.g. "index,vacuum")
  • get_blocking_queries() - works even without active blocking (returns deadlock analysis, contention hotspots, proactive recommendations)
  • analyze_vacuum_requirements() - 6-phase analysis with severity levels

Advanced (2):

  • get_database_overview(max_tables?, sampling_mode?, timeout?) - includes security score, performance hotspots, relationship mapping
  • analyze_schema_relationships() - inter-schema dependency analysis with visual representation data

Quick Patterns

Discover:

const schemas = await list_schemas();
const tables = await list_objects({ schema_name: "public", object_type: "table" });
const details = await get_object_details({ schema_name: "public", object_name: "users", object_type: "table" });

Top resource-intensive queries (recommended default):

const top = await get_top_queries({ sort_by: "resources" });
// Returns queries consuming >5% of ANY resource dimension (CPU, I/O, WAL, etc.)

Hypothetical Index (requires HypoPG, cannot use with analyze:true):

const baseline = await explain_query({ sql: "SELECT * FROM orders WHERE customer_id = $1" });
const withIndex = await explain_query({
  sql: "SELECT * FROM orders WHERE customer_id = $1",
  hypothetical_indexes: [{ table: "orders", columns: ["customer_id"], using: "btree" }]
});
// Use $1, $2 etc. for parameterized queries (bind variables)

Targeted health check:

const health = await analyze_db_health({ health_type: "index,vacuum,buffer" });
// Only run specific checks instead of "all" to reduce noise

Schema relationships:

const rels = await analyze_schema_relationships();
// Cross-schema FK dependencies, hub tables, isolated schemas

Workflows

Slow query (decision tree):

  1. explain_query({ sql, analyze: false }) - get estimated plan
  2. If total cost < 1000: query is likely fine, check if issue is elsewhere
  3. If cost 1000-50000: explain_query({ sql, analyze: true }) - get actual timings
  4. If cost > 50000 or seq scan on large table: test with hypothetical_indexes (requires HypoPG)
  5. If index helps: analyze_query_indexes({ queries: [sql] }) for formal recommendation
  6. If no index helps: escalate to query rewrite or schema change

Workload optimization (use resources sort):

  1. get_top_queries({ sort_by: "resources" }) - find queries consuming >5% of any resource (CPU, buffer reads, dirty pages, WAL)
  2. For each flagged query: explain_query() to identify bottleneck
  3. analyze_workload_indexes({ method: "dta" }) for index recommendations across workload

Health check (targeted deep-dives):

  1. analyze_db_health({ health_type: "all" }) - initial scan
  2. Based on findings, deep-dive with targeted tools:
    • Index issues → analyze_db_health({ health_type: "index" }) shows invalid/duplicate/bloated/unused indexes
    • Vacuum issues → analyze_vacuum_requirements() for 6-phase bloat analysis with maintenance commands
    • Connection issues → analyze_db_health({ health_type: "connection" }) for pool utilization
    • Buffer issues → analyze_db_health({ health_type: "buffer" }) for cache hit rates (index + table)
    • Replication issues → analyze_db_health({ health_type: "replication" }) for lag and slot health

New database assessment:

  1. Run discovery script (auto-calls get_database_overview())
  2. Review security score and recommendations from discovery output
  3. analyze_schema_relationships() - understand cross-schema dependencies
  4. get_top_queries({ sort_by: "resources" }) - identify workload hotspots
  5. analyze_db_health({ health_type: "all" }) - full health scan
  6. analyze_workload_indexes({ method: "dta" }) - index optimization opportunities

Blocking queries: get_blocking_queries() - run immediately when investigating locks; also useful proactively (returns deadlock stats, contention hotspots, and recommendations even when no active blocking exists)

Multi-table query: Phase 1: get_object_details() for ALL tables → [CHECKPOINT] → Phase 2: query with verified columns

Incident triage (general entry point):

  1. get_blocking_queries() — check active lock contention first (time-sensitive, always safe)
  2. analyze_db_health({ health_type: "connection,buffer,vacuum" }) — quick health snapshot
  3. If slow queries, timeouts, high CPU → follow Performance incident below
  4. If missing data, wrong counts, usage checks → follow Data investigation below
  5. If unclear → run both in sequence

Performance incident:

  1. get_blocking_queries() — active locks and deadlock stats
  2. analyze_db_health({ health_type: "connection" }) — connection pool saturation
  3. get_top_queries({ sort_by: "resources" }) — resource-heavy queries
  4. For suspect queries: explain_query({ sql, analyze: false }) — plan without adding load
  5. If vacuum/bloat suspected: analyze_vacuum_requirements() — maintenance recommendations
  6. Report findings with severity and recommended actions

Data investigation (usage checks, missing data, verification):

  1. Run discovery script if not cached — confirm table names and schema
  2. get_object_details() for ALL relevant tables — [CHECKPOINT: verify actual column names]
  3. execute_sql() — query using ONLY verified columns from step 2
  4. If investigating relationships: analyze_schema_relationships() — FK chains, cascade effects
  5. If multi-replica and stale data suspected: analyze_db_health({ health_type: "replication" }) — check lag
  6. Report with evidence (query results, row counts)

Parameters

explain_query:

  • sql (string) - supports bind variables: $1, $2 for parameterized queries
  • analyze (bool, default: false) - runs query for real statistics
  • hypothetical_indexes ([{table, columns, using?}]) - requires HypoPG extension
  • CONSTRAINT: analyze: true + hypothetical_indexes cannot be used together (returns error)

get_top_queries:

  • sort_by (default: "resources") - resources uses multi-dimensional blend: includes queries where ANY of 5 fractions (exec time, buffer access, buffer reads, dirty pages, WAL bytes) exceeds 5% of workload total. total_time and mean_time rank by execution time only
  • limit (int, default: 10) - only applies to total_time and mean_time sorts

analyze_db_health:

  • health_type (default: "all") - comma-separated list of checks:
    • index: invalid, duplicate, bloated, and unused indexes (4 sub-checks)
    • connection: connection count and pool utilization
    • vacuum: transaction ID wraparound danger
    • sequence: sequences approaching max value
    • replication: replication lag and slot health
    • buffer: cache hit rates for both indexes and tables (2 sub-checks)
    • constraint: invalid (not-validated) constraints
    • all: runs all above

analyze_vacuum_requirements (6 phases):

  1. Vacuum summary: total tables, never-vacuumed count, dead tuples aggregate
  2. Table bloat analysis with severity: CRITICAL (>40%), HIGH (>20%), MEDIUM (>10%), LOW (>5%), HEALTHY (<=5%)
  3. Autovacuum config: per-table threshold vs actual dead tuples, status (OVERDUE/APPROACHING/HEALTHY)
  4. Vacuum performance: counts, modifications per vacuum, time since last vacuum
  5. Maintenance recommendations: generates VACUUM FULL, VACUUM, or ANALYZE commands with priority
  6. Critical issues: transaction ID wraparound risk (XID age > 1.5B), config tuning suggestions

analyze_query_indexes:

  • queries (string[]) - max 10 queries per call
  • max_index_size_mb (int, default: 10000)
  • method ("dta" | "llm", default: "dta")

analyze_*_indexes: method = dta (Pareto-optimal) | llm

Safety

RiskOperationsBehavior
LOWlist_*, get_object_details, explain(analyze:false), get_blocking_queriesAlways safe
MEDIUMget_database_overview, analyze_db_health, analyze_schema_relationshipsUse sampling on large DBs
HIGHexplain(analyze:true)Check cost first with analyze:false
CRITICALexecute_sql(INSERT/UPDATE/DELETE/DDL)Require confirmation

Common Errors

ErrorFix
column X does not existRun get_object_details() - never guess column names
relation does not existRefresh table list with list_objects()
permission deniedUse read-only queries
pg_stat_statements not foundRequest DBA to enable extension
timeoutReduce scope: max_tables: 100, use sampling
HypoPG not installedhypothetical_indexes requires the HypoPG extension - request DBA to install, or skip hypothetical analysis
Cannot use analyze and hypothetical indexes togetherRemove analyze: true when using hypothetical_indexes - they are mutually exclusive
up to 10 queries to analyzeanalyze_query_indexes accepts max 10 queries - split larger batches into multiple calls

Output Format

Present results as a structured report:

Analyzing Postgres Report
═════════════════════════
Resources discovered: [count]

Resource       Status    Key Metric    Issues
──────────────────────────────────────────────
[name]         [ok/warn] [value]       [findings]

Summary: [total] resources | [ok] healthy | [warn] warnings | [crit] critical
Action Items: [list of prioritized findings]

Target ≤50 lines of output. Use tables for multi-resource comparisons.

Anti-Hallucination Rules

  1. NEVER assume resource names — always discover via CLI/API in Phase 1 before referencing in Phase 2.
  2. NEVER fabricate metric names or dimensions — verify against the service documentation or --help output.
  3. NEVER mix CLI commands between service versions — confirm which version/API you are targeting.
  4. ALWAYS use the discovery → verify → analyze chain — every resource referenced must have been discovered first.
  5. ALWAYS handle empty results gracefully — an empty response is valid data, not an error to retry.

Counter-Rationalizations

ShortcutCounterWhy
"I'll skip discovery and check known resources"Always run Phase 1 discovery firstResource names change, new resources appear — assumed names cause errors
"The user only asked for a quick check"Follow the full discovery → analysis flowQuick checks miss critical issues; structured analysis catches silent failures
"Default configuration is probably fine"Audit configuration explicitlyDefaults often leave logging, security, and optimization features disabled
"Metrics aren't needed for this"Always check relevant metrics when availableAPI/CLI responses show current state; metrics reveal trends and intermittent issues
"I don't have access to that"Try the command and report the actual errorAssumed permission failures prevent useful investigation; actual errors are informative
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 117kUpdated Claude CodeCodexUnverifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 117kUpdated Claude CodeCodexUnverifiedInstall