Analyzing Postgres

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

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

Common Errors

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