@yawlabs/postgres-mcp

Database health snapshot

pg_health

Read-onlyIdempotent

Check PostgreSQL health: connection usage vs max_connections, active queries with wait events, database stats, and table count to identify connection pressure, runaway queries, and lock/IO waits.

Instructions

Quick health snapshot: server version, database size, connection counts measured against max_connections, active queries with their wait events, a pg_stat_database rollup, and table count. Useful as a connection sanity check and to spot runaway queries, connection-cap pressure, and lock/IO waits.

connections: total for the CURRENT database, broken down into active / idle / idle_in_transaction / idle_in_transaction_aborted / other (starting, fastpath function call, disabled) / state_unavailable -- those six sum to total. idle_in_transaction_aborted is called out separately because it holds locks and blocks vacuum while doing no work and will never commit. state_unavailable counts sessions whose state reads NULL because the role lacks pg_read_all_stats / pg_monitor membership; a non-zero value means every other bucket is under-counted by at least that much, so do NOT read active: 0 next to it as an idle database. Plus cluster_client_backends (client backends across ALL databases -- those are what actually consume connection slots), max_connections, superuser_reserved_connections, and used_fraction (cluster_client_backends / max_connections). A raw connection count means nothing without the cap; read used_fraction first.
active_queries: pid, state, query, application_name, backend_type, wait_event_type / wait_event (both NULL when the backend is running rather than waiting -- the single most diagnostic pair in pg_stat_activity). Both are reported verbatim as the server spells them, and that spelling changes between majors: a backend waiting on a buffer pin reports wait_event_type 'BufferPin' through PostgreSQL 18 and 'Buffer' from 19 on, with the wait_event names beneath it changing to match. Read them against the reported version rather than hard-coding a literal. duration_seconds (since query_start) and transaction_age_seconds (since xact_start). A large transaction_age_seconds next to a small duration_seconds is a long-open transaction, the usual root cause behind lock waits, bloat, and stalled autovacuum.
database_stats: pg_stat_database for the current database -- deadlocks, temp_files / temp_bytes (work_mem spills), conflicts (recovery conflicts, only ever non-zero on a replica), blks_hit / blks_read / cache_hit_ratio, and stats_reset. Every counter is CUMULATIVE since stats_reset, not a rate -- interpret them against that timestamp. Sub-queries that fail (several of these are permission-gated on managed providers) append to _warnings and leave their field null; the rest of the snapshot still returns.

Input Schema

TableJSON Schema

Name	Required	Description	Default
`activeQueryLimit`	No	Max active queries to return (default 10, max 100).

Output Schema

TableJSON Schema

Name	Required	Description
`version`	No	Full `version()` banner. Absent (with a `_warnings` entry) if the row came back without it.
`database`	No
`_warnings`	No
`connected`	Yes	Always true on a success response -- the version probe answered.
`connections`	No
`table_count`	No	User tables and partitioned tables, as a decimal string.
`active_queries`	Yes	Empty array both when nothing is running and when the fetch failed -- check `_warnings`.
`database_stats`	Yes	Null when pg_stat_database is unreadable OR has no row for this database.

Schema Changelog

Changes observed during successful MCP inspections.

Changed16 schema fields changedv0.12.1Sep 4, 2026

removedOutput schema / properties / active_queries / items / properties / duration_seconds / anyOf
```
Removed value: -[
-  {
-    "type": "number"
-  },
-  {
-    "type": "null"
-  }
-]
```
addedOutput schema / properties / active_queries / items / properties / duration_seconds / type
```
Added value: +[
+  "number",
+  "null"
+]
```
removedOutput schema / properties / active_queries / items / properties / state / anyOf
```
Removed value: -[
-  {
-    "type": "string"
-  },
-  {
-    "type": "null"
-  }
-]
```
addedOutput schema / properties / active_queries / items / properties / state / type
```
Added value: +[
+  "string",
+  "null"
+]
```
removedOutput schema / properties / active_queries / items / properties / transaction_age_seconds / anyOf
```
Removed value: -[
-  {
-    "type": "number"
-  },
-  {
-    "type": "null"
-  }
-]
```
addedOutput schema / properties / active_queries / items / properties / transaction_age_seconds / type
```
Added value: +[
+  "number",
+  "null"
+]
```
removedOutput schema / properties / active_queries / items / properties / wait_event / anyOf
```
Removed value: -[
-  {
-    "type": "string"
-  },
-  {
-    "type": "null"
-  }
-]
```
addedOutput schema / properties / active_queries / items / properties / wait_event / type
```
Added value: +[
+  "string",
+  "null"
+]
```
removedOutput schema / properties / active_queries / items / properties / wait_event_type / anyOf
```
Removed value: -[
-  {
-    "type": "string"
-  },
-  {
-    "type": "null"
-  }
-]
```
addedOutput schema / properties / active_queries / items / properties / wait_event_type / type
```
Added value: +[
+  "string",
+  "null"
+]
```
removedOutput schema / properties / active_queries / items / properties / xact_start / anyOf
```
Removed value: -[
-  {
-    "type": "string"
-  },
-  {
-    "type": "null"
-  }
-]
```
addedOutput schema / properties / active_queries / items / properties / xact_start / type
```
Added value: +[
+  "string",
+  "null"
+]
```

changedOutput schema / properties / connections / anyOf

Previous value: -[
-  {
-    "additionalProperties": false,
-    "properties": {
-      "active": {
-        "type": "string"
-      },
-      "cluster_client_backends": {
-        "description": "Client backends across ALL databases -- the ones that actually consume connection slots.",
-        "type": "string"
-      },
-      "idle": {
-        "type": "string"
-      },
-      "idle_in_transaction": {
-        "type": "string"
-      },
-      "idle_in_transaction_aborted": {
-        "description": "Holds locks and blocks vacuum while doing no work, and can never commit.",
-        "type": "string"
-      },
-      "max_connections": {
-        "type": "number"
-      },
-      "other": {
-        "description": "Catch-all: starting, fastpath function call, disabled, plus any future state.",
-        "type": "string"
-      },
-      "state_unavailable": {
-        "description": "Sessions whose `state` read NULL for lack of pg_read_all_stats / pg_monitor. Non-zero means every other bucket is under-counted -- do NOT read `active: 0` beside it as an idle database.",
-        "type": "string"
-      },
-      "superuser_reserved_connections": {
-        "type": "number"
-      },
-      "total": {
-        "description": "Sessions in the CURRENT database. The six state buckets below sum to this.",
-        "type": "string"
-      },
-      "used_fraction": {
-        "anyOf": [
-          {
-            "type": "number"
-          },
-          {
-            "type": "null"
-          }
-        ],
-        "description": "cluster_client_backends / max_connections. Null only if max_connections read as 0."
-      }
-    },
-    "required": [
-      "total",
-      "active",
-      "idle",
-      "idle_in_transaction",
-      "idle_in_transaction_aborted",
-      "other",
-      "state_unavailable",
-      "cluster_client_backends",
-      "max_connections",
-      "superuser_reserved_connections",
-      "used_fraction"
-    ],
-    "type": "object"
-  },
-  {
-    "type": "null"
-  }
-]New value: +[
+  {
+    "additionalProperties": false,
+    "properties": {
+      "active": {
+        "type": "string"
+      },
+      "cluster_client_backends": {
+        "description": "Client backends across ALL databases -- the ones that actually consume connection slots.",
+        "type": "string"
+      },
+      "idle": {
+        "type": "string"
+      },
+      "idle_in_transaction": {
+        "type": "string"
+      },
+      "idle_in_transaction_aborted": {
+        "description": "Holds locks and blocks vacuum while doing no work, and can never commit.",
+        "type": "string"
+      },
+      "max_connections": {
+        "type": "number"
+      },
+      "other": {
+        "description": "Catch-all: starting, fastpath function call, disabled, plus any future state.",
+        "type": "string"
+      },
+      "state_unavailable": {
+        "description": "Sessions whose `state` read NULL for lack of pg_read_all_stats / pg_monitor. Non-zero means every other bucket is under-counted -- do NOT read `active: 0` beside it as an idle database.",
+        "type": "string"
+      },
+      "superuser_reserved_connections": {
+        "type": "number"
+      },
+      "total": {
+        "description": "Sessions in the CURRENT database. The six state buckets below sum to this.",
+        "type": "string"
+      },
+      "used_fraction": {
+        "description": "cluster_client_backends / max_connections. Null only if max_connections read as 0.",
+        "type": [
+          "number",
+          "null"
+        ]
+      }
+    },
+    "required": [
+      "total",
+      "active",
+      "idle",
+      "idle_in_transaction",
+      "idle_in_transaction_aborted",
+      "other",
+      "state_unavailable",
+      "cluster_client_backends",
+      "max_connections",
+      "superuser_reserved_connections",
+      "used_fraction"
+    ],
+    "type": "object"
+  },
+  {
+    "type": "null"
+  }
+]

changedOutput schema / properties / database_stats / anyOf

Previous value: -[
-  {
-    "additionalProperties": false,
-    "properties": {
-      "blks_hit": {
-        "type": "string"
-      },
-      "blks_read": {
-        "type": "string"
-      },
-      "cache_hit_ratio": {
-        "anyOf": [
-          {
-            "type": "number"
-          },
-          {
-            "type": "null"
-          }
-        ],
-        "description": "Null on a freshly reset database (both counters 0)."
-      },
-      "conflicts": {
-        "description": "Recovery conflicts; only ever non-zero on a replica.",
-        "type": "string"
-      },
-      "deadlocks": {
-        "type": "string"
-      },
-      "stats_reset": {
-        "anyOf": [
-          {
-            "type": "string"
-          },
-          {
-            "type": "null"
-          }
-        ],
-        "description": "Every counter here is cumulative SINCE this timestamp. Null = never reset."
-      },
-      "temp_bytes": {
-        "type": "string"
-      },
-      "temp_bytes_pretty": {
-        "type": "string"
-      },
-      "temp_files": {
-        "type": "string"
-      }
-    },
-    "required": [
-      "deadlocks",
-      "temp_files",
-      "temp_bytes",
-      "temp_bytes_pretty",
-      "conflicts",
-      "blks_hit",
-      "blks_read",
-      "cache_hit_ratio",
-      "stats_reset"
-    ],
-    "type": "object"
-  },
-  {
-    "type": "null"
-  }
-]New value: +[
+  {
+    "additionalProperties": false,
+    "properties": {
+      "blks_hit": {
+        "type": "string"
+      },
+      "blks_read": {
+        "type": "string"
+      },
+      "cache_hit_ratio": {
+        "description": "Null on a freshly reset database (both counters 0).",
+        "type": [
+          "number",
+          "null"
+        ]
+      },
+      "conflicts": {
+        "description": "Recovery conflicts; only ever non-zero on a replica.",
+        "type": "string"
+      },
+      "deadlocks": {
+        "type": "string"
+      },
+      "stats_reset": {
+        "description": "Every counter here is cumulative SINCE this timestamp. Null = never reset.",
+        "type": [
+          "string",
+          "null"
+        ]
+      },
+      "temp_bytes": {
+        "type": "string"
+      },
+      "temp_bytes_pretty": {
+        "type": "string"
+      },
+      "temp_files": {
+        "type": "string"
+      }
+    },
+    "required": [
+      "deadlocks",
+      "temp_files",
+      "temp_bytes",
+      "temp_bytes_pretty",
+      "conflicts",
+      "blks_hit",
+      "blks_read",
+      "cache_hit_ratio",
+      "stats_reset"
+    ],
+    "type": "object"
+  },
+  {
+    "type": "null"
+  }
+]

removedOutput schema / properties / table_count / anyOf

Removed value: -[
-  {
-    "type": "string"
-  },
-  {
-    "type": "null"
-  }
-]

addedOutput schema / properties / table_count / type
```
Added value: +[
+  "string",
+  "null"
+]
```

Changed2 schema fields changedv0.12.0Aug 28, 2026

changedInput schema / $schema

Previous value: -"http://json-schema.org/draft-07/schema#"New value: +"https://json-schema.org/draft/2020-12/schema"

changedOutput schema / (root)

Previous value: -nullNew value: +{
+  "$schema": "https://json-schema.org/draft/2020-12/schema",
+  "additionalProperties": false,
+  "properties": {
+    "_warnings": {
+      "items": {
+        "type": "string"
+      },
+      "type": "array"
+    },
+    "active_queries": {
+      "description": "Empty array both when nothing is running and when the fetch failed -- check `_warnings`.",
+      "items": {
+        "additionalProperties": false,
+        "properties": {
+          "application_name": {
+            "type": "string"
+          },
+          "backend_type": {
+            "type": "string"
+          },
+          "duration_seconds": {
+            "anyOf": [
+              {
+                "type": "number"
+              },
+              {
+                "type": "null"
+              }
+            ],
+            "description": "Since query_start."
+          },
+          "pid": {
+            "type": "number"
+          },
+          "query": {
+            "type": "string"
+          },
+          "state": {
+            "anyOf": [
+              {
+                "type": "string"
+              },
+              {
+                "type": "null"
+              }
+            ]
+          },
+          "transaction_age_seconds": {
+            "anyOf": [
+              {
+                "type": "number"
+              },
+              {
+                "type": "null"
+              }
+            ],
+            "description": "Since xact_start; null outside a transaction block. Large here beside a small duration_seconds is a long-open transaction."
+          },
+          "wait_event": {
+            "anyOf": [
+              {
+                "type": "string"
+              },
+              {
+                "type": "null"
+              }
+            ]
+          },
+          "wait_event_type": {
+            "anyOf": [
+              {
+                "type": "string"
+              },
+              {
+                "type": "null"
+              }
+            ],
+            "description": "Null when the backend is RUNNING rather than waiting. Spelling changes between majors ('BufferPin' through PG18, 'Buffer' from 19) -- read it against `version`."
+          },
+          "xact_start": {
+            "anyOf": [
+              {
+                "type": "string"
+              },
+              {
+                "type": "null"
+              }
+            ]
+          }
+        },
+        "required": [
+          "pid",
+          "state",
+          "duration_seconds",
+          "transaction_age_seconds",
+          "xact_start",
+          "wait_event_type",
+          "wait_event",
+          "backend_type",
+          "query",
+          "application_name"
+        ],
+        "type": "object"
+      },
+      "type": "array"
+    },
+    "connected": {
+      "description": "Always true on a success response -- the version probe answered.",
+      "type": "boolean"
+    },
+    "connections": {
+      "anyOf": [
+        {
+          "additionalProperties": false,
+          "properties": {
+            "active": {
+              "type": "string"
+            },
+            "cluster_client_backends": {
+              "description": "Client backends across ALL databases -- the ones that actually consume connection slots.",
+              "type": "string"
+            },
+            "idle": {
+              "type": "string"
+            },
+            "idle_in_transaction": {
+              "type": "string"
+            },
+            "idle_in_transaction_aborted": {
+              "description": "Holds locks and blocks vacuum while doing no work, and can never commit.",
+              "type": "string"
+            },
+            "max_connections": {
+              "type": "number"
+            },
+            "other": {
+              "description": "Catch-all: starting, fastpath function call, disabled, plus any future state.",
+              "type": "string"
+            },
+            "state_unavailable": {
+              "description": "Sessions whose `state` read NULL for lack of pg_read_all_stats / pg_monitor. Non-zero means every other bucket is under-counted -- do NOT read `active: 0` beside it as an idle database.",
+              "type": "string"
+            },
+            "superuser_reserved_connections": {
+              "type": "number"
+            },
+            "total": {
+              "description": "Sessions in the CURRENT database. The six state buckets below sum to this.",
+              "type": "string"
+            },
+            "used_fraction": {
+              "anyOf": [
+                {
+                  "type": "number"
+                },
+                {
+                  "type": "null"
+                }
+              ],
+              "description": "cluster_client_backends / max_connections. Null only if max_connections read as 0."
+            }
+          },
+          "required": [
+            "total",
+            "active",
+            "idle",
+            "idle_in_transaction",
+            "idle_in_transaction_aborted",
+            "other",
+            "state_unavailable",
+            "cluster_client_backends",
+            "max_connections",
+            "superuser_reserved_connections",
+            "used_fraction"
+          ],
+          "type": "object"
+        },
+        {
+          "type": "null"
+        }
+      ]
+    },
+    "database": {
+      "anyOf": [
+        {
+          "additionalProperties": false,
+          "properties": {
+            "database": {
+              "type": "string"
+            },
+            "size_bytes": {
+              "description": "Bigint as a decimal string -- past 2^53 a JS number would lose digits.",
+              "type": "string"
+            },
+            "size_pretty": {
+              "type": "string"
+            }
+          },
+          "required": [
+            "database",
+            "size_pretty",
+            "size_bytes"
+          ],
+          "type": "object"
+        },
+        {
+          "type": "null"
+        }
+      ]
+    },
+    "database_stats": {
+      "anyOf": [
+        {
+          "additionalProperties": false,
+          "properties": {
+            "blks_hit": {
+              "type": "string"
+            },
+            "blks_read": {
+              "type": "string"
+            },
+            "cache_hit_ratio": {
+              "anyOf": [
+                {
+                  "type": "number"
+                },
+                {
+                  "type": "null"
+                }
+              ],
+              "description": "Null on a freshly reset database (both counters 0)."
+            },
+            "conflicts": {
+              "description": "Recovery conflicts; only ever non-zero on a replica.",
+              "type": "string"
+            },
+            "deadlocks": {
+              "type": "string"
+            },
+            "stats_reset": {
+              "anyOf": [
+                {
+                  "type": "string"
+                },
+                {
+                  "type": "null"
+                }
+              ],
+              "description": "Every counter here is cumulative SINCE this timestamp. Null = never reset."
+            },
+            "temp_bytes": {
+              "type": "string"
+            },
+            "temp_bytes_pretty": {
+              "type": "string"
+            },
+            "temp_files": {
+              "type": "string"
+            }
+          },
+          "required": [
+            "deadlocks",
+            "temp_files",
+            "temp_bytes",
+            "temp_bytes_pretty",
+            "conflicts",
+            "blks_hit",
+            "blks_read",
+            "cache_hit_ratio",
+            "stats_reset"
+          ],
+          "type": "object"
+        },
+        {
+          "type": "null"
+        }
+      ],
+      "description": "Null when pg_stat_database is unreadable OR has no row for this database."
+    },
+    "table_count": {
+      "anyOf": [
+        {
+          "type": "string"
+        },
+        {
+          "type": "null"
+        }
+      ],
+      "description": "User tables and partitioned tables, as a decimal string."
+    },
+    "version": {
+      "description": "Full `version()` banner. Absent (with a `_warnings` entry) if the row came back without it.",
+      "type": "string"
+    }
+  },
+  "required": [
+    "connected",
+    "active_queries",
+    "database_stats"
+  ],
+  "type": "object"
+}

First observedv0.7.0Jul 29, 2026

TDQS

A4.3/5.0

Behavior5/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

The description goes far beyond the readOnly/idempotent annotations. It explains partial failure via _warnings, cumulative counters, permission-related under-counting, wait_event naming differences across PostgreSQL versions, and the significance of idle_in_transaction_aborted. These are exactly the behavioral quirks an agent needs to correctly interpret results.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is long but dense and well-structured, with a front-loaded summary followed by organized bullets. Every subsection carries diagnostic value, and the caveats about permissions, cumulative counters, and version-dependent wait events earn their place.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness5/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

For a complex read-only diagnostic tool, the description is remarkably complete: it covers the three main output sections, key fields, interpretation guidance, failure behavior, and permission caveats. Since an output schema is present, the description does not need to restate return shapes, and it supplies the contextual semantics that schemas cannot convey.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The input schema already fully documents activeQueryLimit with default, minimum, maximum, and a clear description. The tool description does not add further parameter-level meaning, so the baseline of 3 applies because schema coverage is 100%.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose4/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly identifies the tool as a health snapshot covering server version, database size, connection counts, active queries, wait events, database stats, and table count. This distinguishes it from query-focused siblings like pg_query or pg_top_queries, but it does not explicitly name alternative tools or draw sharp boundaries with siblings that also surface wait events or connection-related metrics.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description gives explicit use cases: 'a connection sanity check' and to 'spot runaway queries, connection-cap pressure, and lock/IO waits.' It does not, however, mention when not to use this tool or point to specific sibling tools for deeper diagnostics, so it stops short of full alternative routing.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.