mcp_postgres/actions/
monitoring.rs

1use serde_json::{json, Value};
2use tokio_postgres::Client;
3use crate::errors::Result as MCPResult;
4
5/// 11. Get table stats
6pub async fn get_table_stats(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
7    let rows = client
8        .query(
9            "SELECT schemaname, relname, n_live_tup, n_dead_tup,
10                    last_vacuum::text, last_autovacuum::text
11             FROM pg_stat_user_tables
12             ORDER BY schemaname, relname",
13            &[],
14        )
15        .await?;
16
17    let stats: Vec<Value> = rows
18        .iter()
19        .map(|row| {
20            json!({
21                "schema": row.get::<_, String>(0),
22                "table": row.get::<_, String>(1),
23                "live_tuples": row.get::<_, i64>(2),
24                "dead_tuples": row.get::<_, i64>(3),
25                "last_vacuum": row.get::<_, Option<String>>(4),
26                "last_autovacuum": row.get::<_, Option<String>>(5),
27            })
28        })
29        .collect();
30
31    Ok(json!({ "tables": stats }))
32}
33
34/// 12. Get index stats
35pub async fn get_index_stats(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
36    let rows = client
37        .query(
38            "SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
39             FROM pg_stat_user_indexes
40             ORDER BY schemaname, relname, indexrelname",
41            &[],
42        )
43        .await?;
44
45    let stats: Vec<Value> = rows
46        .iter()
47        .map(|row| {
48            json!({
49                "schema": row.get::<_, String>(0),
50                "table": row.get::<_, String>(1),
51                "index": row.get::<_, String>(2),
52                "scans": row.get::<_, i64>(3),
53                "tuples_read": row.get::<_, i64>(4),
54                "tuples_fetched": row.get::<_, i64>(5),
55            })
56        })
57        .collect();
58
59    Ok(json!({ "indexes": stats }))
60}
61
62/// 13. Show database size
63pub async fn show_database_size(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
64    let rows = client
65        .query(
66            "SELECT datname, pg_size_pretty(pg_database_size(datname)) as size_pretty,
67                    pg_database_size(datname) as size_bytes
68             FROM pg_database
69             WHERE datistemplate = false
70             ORDER BY pg_database_size(datname) DESC",
71            &[],
72        )
73        .await?;
74
75    let databases: Vec<Value> = rows
76        .iter()
77        .map(|row| {
78            json!({
79                "name": row.get::<_, String>(0),
80                "size": row.get::<_, String>(1),
81                "size_bytes": row.get::<_, i64>(2),
82            })
83        })
84        .collect();
85
86    Ok(json!({ "databases": databases }))
87}
88
89/// 14. Show table size
90pub async fn show_table_size(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
91    let rows = client
92        .query(
93            "SELECT schemaname, relname,
94                    pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)) as total_size,
95                    pg_total_relation_size(schemaname||'.'||relname) as total_bytes
96             FROM pg_stat_user_tables
97             ORDER BY pg_total_relation_size(schemaname||'.'||relname) DESC",
98            &[],
99        )
100        .await?;
101
102    let tables: Vec<Value> = rows
103        .iter()
104        .map(|row| {
105            json!({
106                "schema": row.get::<_, String>(0),
107                "table": row.get::<_, String>(1),
108                "size": row.get::<_, String>(2),
109                "size_bytes": row.get::<_, i64>(3),
110            })
111        })
112        .collect();
113
114    Ok(json!({ "tables": tables }))
115}
116
117/// 15. Get cache hit ratio
118pub async fn get_cache_hit_ratio(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
119    let rows = client
120        .query(
121            "SELECT CASE
122                WHEN (sum(heap_blks_hit) + sum(heap_blks_read)) > 0
123                THEN sum(heap_blks_hit)::float / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0)
124                ELSE NULL
125             END as ratio
126             FROM pg_statio_user_tables",
127            &[],
128        )
129        .await?;
130
131    let ratio = if rows.is_empty() {
132        None
133    } else {
134        rows[0].get::<_, Option<f64>>(0)
135    };
136
137    Ok(json!({
138        "cache_hit_ratio": ratio,
139        "percentage": ratio.map(|r| r * 100.0)
140    }))
141}
mcp_postgres/actions/monitoring.rs

mcp_postgres/actions/
monitoring.rs
