AI-Powered PostgreSQL Performance Tuning with MCP - Introducing pgtuner_mcp

Database performance optimization is one of the most critical yet challenging aspects of maintaining production systems. Identifying slow queries, optimizing indexes, and monitoring database health requires deep expertise and constant vigilance. Today, I’m excited to introduce pgtuner_mcp, a Model Context Protocol (MCP) server that brings AI-powered PostgreSQL performance tuning capabilities directly into your development workflow.

What is pgtuner_mcp?

pgtuner_mcp is an intelligent PostgreSQL performance analysis server built on the Model Context Protocol (MCP). It bridges the gap between AI assistants (like Claude) and your PostgreSQL database, enabling natural language interactions for complex database optimization tasks.

GitHub Repository

Key Capabilities

• Intelligent Query Analysis: Identify slow queries with detailed statistics from pg_stat_statements
• AI-Powered Index Recommendations: Get smart indexing suggestions based on actual workload patterns
• Hypothetical Index Testing: Test indexes without creating them using HypoPG
• Comprehensive Health Checks: Monitor connections, cache efficiency, locks, and replication
• Bloat Detection: Identify and quantify table/index bloat for maintenance
• Vacuum Monitoring: Track vacuum operations and autovacuum effectiveness
• I/O Analysis: Analyze disk read/write patterns and identify bottlenecks
• Configuration Review: Get recommendations for memory, checkpoint, and connection settings

Architecture Overview

pgtuner_mcp leverages several PostgreSQL extensions and Python libraries to provide comprehensive analysi

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

┌─────────────────┐
│  AI Assistant   │
│  (Claude, etc)  │
└────────┬────────┘
         │ MCP Protocol
         ▼
┌─────────────────┐    ┌──────────────────┐
│  pgtuner_mcp    │───▶│   PostgreSQL     │
│  (Python MCP    │    │   + Extensions   │
│   Server)       │    │  - pg_stat_      │
└─────────────────┘    │    statements    │
         │              │  - hypopg        │
         │              │  - pgstattuple   │
         ▼              └──────────────────┘
┌─────────────────┐
│  Analysis &     │
│  Recommendations│
└─────────────────┘

Building Safe PostgreSQL Extensions with Rust - Introducing pg_where_guard

Database safety is a critical concern for any production system. Accidental data loss from DELETE or UPDATE statements without WHERE clauses can be catastrophic. Today, I’ll introduce pg_where_guard, a PostgreSQL extension built with Rust and the pgrx framework that prevents these dangerous operations.

What is pg_where_guard?

pg_where_guard is a PostgreSQL extension that acts as a safety net for your database by intercepting and blocking potentially dangerous SQL operations:

• DELETE Protection: Prevents DELETE FROM table without WHERE clause
• UPDATE Protection: Prevents UPDATE table SET ... without WHERE clause
• CTE Support: Recursively checks Common Table Expressions
• Hook Integration: Uses PostgreSQL’s post_parse_analyze_hook for query interception
• Memory Safe: Written in Rust with pgrx for safety and performance

GitHub Repository

Why Rust for PostgreSQL Extensions?

Building PostgreSQL extensions traditionally meant working with C and dealing with manual memory management, potential segmentation faults, and complex debugging. Rust changes this paradigm by offering:

Performance

Zero-cost abstractions mean Rust code performs as well as equivalent C code while being much safer.

PostgreSQL CDC Example

A Rust-based Change Data Capture (CDC) application that streams real-time data changes from PostgreSQL to other databases (MySQL, SQL Server, etc.) using logical replication.

Overview

This project demonstrates how to build a production-ready CDC pipeline using the pg2any_lib crate. It captures changes from PostgreSQL using logical replication and streams them to destination databases in real-time.

pg2any lib source code

Github example.

PostgreSQL Streaming logical Replication POC replication_checker_rs

Features

• Real-time streaming from PostgreSQL to multiple database types
• Logical replication with configurable replication slots and publications
• Comprehensive monitoring with Prometheus metrics and health checks
• Docker containerization for easy deployment
• Structured logging with configurable log levels
• Graceful error handling and automatic recovery mechanisms

Quick Start

大家好，今天要和大家介紹我近期開發的一個開源專案 —— redis_fdw_rs，這是一個使用 Rust 語言與 pgrx 框架實作的 Redis Foreign Data Wrapper (FDW)，讓你能夠在 PostgreSQL 中直接查詢 Redis 資料，就像操作一般的資料表一樣。

為什麼需要 Redis FDW？

Redis 是高效的快取資料庫，常被用於儲存 session、排行榜、事件流等資料。但當你想從 PostgreSQL 中同步存取 Redis 資料，就必須透過額外程式碼或 ETL 工具，相對麻煩。

redis_fdw_rs 就是為了解決這個痛點而生：透過 FDW 介面，讓 PostgreSQL 能用 SQL 查 Redis！

🚀 專案特色與支援功能

這個 FDW 專案目前已經支援以下功能，適合實際部署與使用：

• ✅ 支援 Redis Cluster
• ✅ WHERE 條件下推（Pushdown）：減少資料搬移量，提升查詢效率
• ✅ 連線池管理：避免反覆連線 Redis 的開銷
• ✅ Stream 大量資料支援：批次查詢、分頁等場景皆可處理
• ✅ 支援 PostgreSQL 14~17
• ✅ Unit Test & Integration Test：專案有測試覆蓋，確保穩定性

使用範例（超簡單）

前言

大家好，今天要和大家介紹我近期開發的一個開源專案 replication_checker_rs 如果你想用一個輕量、可讀性高的工具實時觀察 PostgreSQL 邏輯複寫（logical replication）流，或想把複寫協議的學習變成可執行的實驗場，replication_checker_rs 是一個不錯的起點：它是 Rust 實現 PostgreSQL logical replication protocol 使用 libpq-sys 的實作，能連上資料庫、建立 replication slot，並將 INSERT / UPDATE / DELETE / TRUNCATE 等變更以可讀格式顯示出來。(https://github.com/isdaniel/replication_checker_rs)

為什麼會想用它？

• 學習角度：它直接實作 PostgreSQL 的 logical replication protocol（WAL message parsing、relation/tuple 格式）以可讀、可改造的 Rust 程式碼呈現，適合希望理解底層協議的人。
• 快速驗證：想知道 publication 有沒有正確產生事件、或某些 schema 變更會如何呈現時，可以直接跑這個工具觀察實際輸出。
• Rust + libpq 真實範例：展示如何用 libpq-sys 與 Tokio 做低階連線管理與 parser 實作
• 延伸空間大：可以把它當作 PoC（proof of concept），加上 JSON 化、推到 Kafka/Redis、做到可重啟的 consumer。

功能與限制（快速掃描）

主要功能

• 用作邏輯複寫客戶端（logical replication client），可建立 replication slot 並接收變更。
• 支援顯示 BEGIN、COMMIT、INSERT、UPDATE、DELETE、TRUNCATE 以及 relation／tuple 資訊，並能處理 streaming（大型）交易。

目前限制

為什麼修改 MySQL 的 character_set_server 後仍需重啟？從 mysql-connector-net 探討字元集的陷阱

在近期處理一個與 MySQL 字元集相關的問題時，我深入研究了 MySQL Server 的 Handshake 機制以及 mysql-connector-net 原始碼，發現了一個容易被忽略但可能會造成重大錯誤的細節——即使 character_set_server 是動態參數，但實際上修改後仍需要重啟 MySQL Server，否則會造成驅動端的解碼錯誤。

問題背景：為什麼驅動程式仍使用舊的字元集？

根據 MySQL Server 的設計，當 client 端連線時，Server 會在 Handshake Initial Packet 中回傳一些基本資訊，其中就包括伺服器的預設字元集（character_set_server）。這段資訊是透過以下的程式碼取得：

🔗 MySQL Source Code 參考連結

1

packet->append_int1(default_charset_info->number);

根據 MySQL 官方文件，這段資訊會被封裝在 Handshake v10 Protocol 中傳送（官方文件）。

然而，這個值其實是在 MySQL Server 啟動時就被載入的，即使你在執行中動態修改 character_set_server，重新連線後 greeting packet 裡的字元集值仍然不會更新。

驅動程式的行為：根據 Greeting 決定後續欄位解碼

Here’s a well-structured draft for your technical blog post based on the provided Rust + pgrx Foreign Data Wrapper (FDW) code.

🚀 Building a Simple PostgreSQL FDW with Rust and pgrx

PostgreSQL Foreign Data Wrappers (FDW) enable PostgreSQL to query external data sources as if they were regular tables. Traditionally, FDWs are written in C, but with pgrx, we can now build PostgreSQL extensions — including FDWs — in Rust, unlocking safety and modern tooling.

In this post, we’ll walk through creating a simple FDW using Rust and pgrx that simulates reading rows from an external source (e.g., Redis or API). While it’s a stub, it demonstrates how to implement the core FDW lifecycle.

🛠️ What Can You Build with pgrx?

• ✅ SQL Functions: Scalar, aggregate, and set-returning functions.
• ✅ Custom Types: Define composite types or enums in Rust.
• ✅ Foreign Data Wrappers (FDWs): Like the one in your example — connect PostgreSQL to external systems (Redis, APIs, file systems, etc.).
• ✅ Index Access Methods: Implement new index types.
• ✅ Background Workers: Run tasks in the background inside PostgreSQL.
• ✅ Hooks: Intercept or modify PostgreSQL internal behavior (like planner or executor hooks).

🌐 Under the hood:

• PostgreSQL communicates via C APIs.
• pgrx provides Rust-safe bindings to these APIs.
• Memory management is handled carefully via PgMemoryContexts, matching PostgreSQL’s memory context model.
• Rust functions are exposed to PostgreSQL as SQL-callable functions with the #[pg_extern] macro.

🏗️ Key Components of an default_fdw

PostgreSQL FDWs consist of several callback functions that handle different phases of query planning and execution:

什麼是 MCP?

MCP(Model Context Protocol)是一種協定，用於在工具之間進行通訊與協作。透過 MCP，可以讓各種獨立的工具(如模型、插件、服務)以一致的格式互相交換資料與指令。MCP Server 是提供特定功能的伺服器端程式，能與支援 MCP 的前端進行互動。

Weather MCP Server 是什麼？

Weather MCP Server 是一個基於 MCP 協定開發的天氣資訊伺服器，利用 Open-Meteo API 提供免費的天氣資料。透過這個伺服器，你可以查詢：

• 某城市的即時天氣資訊
• 某城市在特定時間範圍內的天氣預報
• 指定時區的目前時間

mcp_weather_server source code
smithery AI

使用此 MCP Server 搭配 AI Model 可以輕易搭建出即時天氣小助手, 如下我的 AI Bot

功能特色

• 查詢指定城市的即時天氣
• 查詢指定日期區間的天氣預測
• 查詢目前時間(支援指定時區)

簡介

最近我開發了 MessageWorkerPool 專案。其主要概念是提供一個平台框架，使使用者能夠快速且輕鬆地在 Worker 內實作邏輯。該設計高度靈活，允許基於我創建的 Worker 通訊協議，以多種程式語言實作 Worker。目前，我已提供使用 C#、Rust 和 Python 編寫的 Worker 範例。

這個函式庫在多進程環境中處理任務表現優異。此外，它還支援優雅關閉 (graceful shutdown)，確保在隨時 consumer worker 能順利終止處理程序。

MessageWorkerPool GitHub

為什麼選擇 ProcessPool 而非 ThreadPool?

當你需要強大的隔離性，以防止某個任務影響其他任務時，應該選擇 ProcessPool，特別是針對關鍵操作或容易崩潰的任務。雖然 ThreadPool 較為輕量（因為執行緒共用記憶體並且具有較低的上下文切換開銷），但 ProcessPool 能夠提供更靈活的解決方案，允許使用不同的程式語言來實作 Worker。

安裝

要安裝 MessageWorkerPool 套件，請使用以下 NuGet 指令：

1

PM > Install-Package MessageWorkerPool

若要手動安裝此函式庫，可克隆儲存庫並建置專案：

RustBox

A Docker-like container runtime written in Rust with daemon architecture, supporting multi-container orchestration, persistent state management, and comprehensive CLI commands.

Overview

RustBox is a container runtime that isn’t competing with (Docker or Kubernetes), we return to the core and build a simplest “Sandbox/isolated runtime environment” from the lowest level Linux kernel mechanisms (namespaces, cgroups, OverlayFS, etc.), provides Docker-like functionality using:

• Daemon Architecture with Unix domain socket communication
• Multi-container Management with persistent state
• OverlayFS for isolated container filesystems
• Cgroups v2 for resource limits (memory, CPU)
• Linux namespaces for complete process isolation
• Comprehensive CLI with run, stop, list, inspect, remove, logs, and attach commands

This tool is designed for container orchestration, testing environments, and secure code execution.

Architecture

Daemon-Client Model

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102

┌──────────────────────────────────────────────────────────────────────────────┐
│                                RustBox Architecture                          │
└──────────────────────────────────────────────────────────────────────────────┘

[rustbox CLI]                                 [rustboxd Daemon]
     │                                               │
     │  Unix Socket                                  │
     │  /tmp/rustbox-daemon.sock                     │
     │                                               │
     │  IPC Protocol (JSON messages)                 │
     │  ───────────────────────────────────────────▶ │
     │  Commands:                                   │
     │   • run                                      │
     │   • stop                                     │
     │   • list                                     │
     │   • inspect                                  │
     │   • remove                                   │
     │   • logs                                     │
     │   • attach                                   │
     │                                               ▼
     │                                    ┌────────────────────────────┐
     │                                    │  Container Manager         │
     │                                    │  ───────────────────────── │
     │                                    │  • Controls lifecycle      │
     │                                    │  • Creates sandbox env     │
     │                                    │  • Manages PTY + process   │
     │                                    └────────────────────────────┘
     │                                               │
     │                                               ▼
     │                                    ┌────────────────────────────┐
     │                                    │  Registry (HashMap<ID, Container>)│
     │                                    └────────────────────────────┘
     │                                               │
     │                     ┌────────────────────────────────────────────┐
     │                     │           Container Instances              │
     │                     └────────────────────────────────────────────┘
     │                         │              │              │
     │                         ▼              ▼              ▼
     │                    [Container 1]  [Container 2]  [Container N]
     │                         │              │              │
     │                  ┌──────────────────────────────────────────────┐
     │                  │              Sandbox Components               │
     │                  │  overlayfs + cgroups + namespaces             │
     │                  └──────────────────────────────────────────────┘
     │                         │
     │                         │
     │  (When attaching)       │
     │  ───────────────────────────────────────────────────────────────────────────
    ┌─────────────────────────────────────────────────────────────────────────────┐
    │                            Container Attach Flow                            │
    └─────────────────────────────────────────────────────────────────────────────┘

    Client (e.g. docker attach, CLI, web terminal)
    │
    │  1. Send/receive stdin/stdout over Unix socket
    ▼
    ┌───────────────────────────────────────────────────────────┐
    │ Daemon Process                                            │
    │ ───────────────────────────────────────────────────────── │
    │  • Manages container lifecycle                            │
    │  • Holds PTY master side                                  │
    │  • Forwards data between client and container             │
    │                                                           │
    │  ┌──────────────────────────────────────────────────────┐ │
    │  │ Unix Socket (Client ↔ Daemon)                        │ │
    │  │  - AttachStdin  (client → daemon)                    │ │
    │  │  - AttachStdout (daemon → client)                    │ │
    │  └──────────────────────────────────────────────────────┘ │
    │                              │
    │                              │ (I/O forwarding loop)
    │                              ▼
    │  ┌──────────────────────────────────────────────────────┐ │
    │  │ PTY Master                                           │ │
    │  │  - Pseudo terminal device endpoint controlled by     │ │
    │  │    the daemon                                        │ │
    │  │  - Reads container output                            │ │
    │  │  - Writes client input                               │ │
    │  └──────────────────────────────────────────────────────┘ │
    │                              │
    │                              │ (kernel-level link)
    │                              ▼
    │  ┌──────────────────────────────────────────────────────┐ │
    │  │ PTY Slave                                            │ │
    │  │  - Exposed inside the container as /dev/tty or stdin │ │
    │  │  - Attached to the container’s process (e.g. /bin/bash)││
    │  │  - Container writes stdout/stderr → goes to Master   │ │
    │  │  - Container reads stdin ← comes from Master         │ │
    │  └──────────────────────────────────────────────────────┘ │
    └───────────────────────────────────────────────────────────┘
    │
    ▼
    Container Process (e.g. /bin/bash, sh)
    • Reads from stdin (/dev/tty)
    • Writes to stdout/stderr (/dev/tty)

    ───────────────────────────────────────────────────────────────
    Summary:
    - PTY Master: controlled by the daemon, mediates all I/O
    - PTY Slave : presented to the container process as its terminal
    - Unix Socket: transports attach stream between client ↔ daemon
    ───────────────────────────────────────────────────────────────

Container Isolation