pg-lens-mcp
Provides read-only PostgreSQL database inspection and query analysis: lists tables with approximate row counts, describes table columns, primary keys, and indexes, explains SELECT queries without executing them, suggests indexes based on explain plans, and reports table/index size and scan statistics.
pg-lens-mcp
A read-only PostgreSQL explainer MCP server, written in TypeScript.
Point an AI client (Claude Code, Cursor, Claude Desktop) at a Postgres database and ask questions like "what tables are here?", "what does the orders table look like?" — and, from Evening 2, "why is this query slow?"
The server can never write to the database. That is a design choice, not a missing feature — see Safety below.
All five tools are built and verified end-to-end against a live seeded database (below), with unit + integration tests and two layers of evals.
Tools
Tool | What it does |
| Tables in the database (system schemas excluded), with approximate row counts |
| Columns and types, primary key, and existing indexes for one table |
| EXPLAIN (FORMAT JSON) for a SELECT, summarised: cost, sequential scans, index scans. Planned, never executed |
| Reads an explain plan and suggests an index, with reasoning — never applies it |
| Table/index sizes, live/dead rows, sequential vs index scan counts |
Related MCP server: postgres-mcp
Verified end-to-end (live, seeded shop DB)
Query: SELECT * FROM orders WHERE customer_id = 42 (20,000 orders)
Before: plan cost 417, sequential scan on
orderssuggest_indexes→CREATE INDEX idx_orders_customer_id ON public.orders (customer_id);After a human applies that index (the tool never does): cost 157.46, sequential scan gone, Bitmap Index Scan on the new index, and
suggest_indexesnow reports nothing to suggest
Safety — defence in depth
Most Postgres MCP servers expose a generic "run any SQL" tool. pg-lens deliberately does not. Three layers enforce read-only access:
SQL guard (
src/sql-guard.ts) — a single statement only, starting with SELECT / WITH / EXPLAIN; write and DDL keywords rejected;EXPLAIN ANALYZErejected because it executes the query, while plain EXPLAIN only plans it.Read-only transactions (
src/db.ts) — every query runs insideBEGIN READ ONLYwith a 5-second statement timeout, so Postgres itself refuses writes even if a statement slipped past the guard.Read-only database user (recommended setup) — the server only needs a user that cannot write in the first place.
Quick start
# 1. Start the sample database (a small "shop" schema, seeded)
docker compose up -d
# 2. Install and run the server
npm install
cp .env.example .env # sets DATABASE_URL for the sample DB
npm startThe sample schema contains a deliberate flaw: orders.customer_id has no
index, so orders-by-customer queries do a sequential scan. That is the
slow query the demo diagnoses.
Use it from Claude Code
claude mcp add pg-lens --env DATABASE_URL=postgres://pglens:pglens@localhost:5433/shop -- npx tsx src/index.tsUse it from Claude Desktop (free plan works)
Build the single-file bundle once (npm run bundle), then add to
~/Library/Application Support/Claude/claude_desktop_config.json:
{
"mcpServers": {
"pg-lens": {
"command": "node",
"args": ["/path/to/pg-lens-mcp/dist/pg-lens.mjs"],
"env": {
"DATABASE_URL": "postgres://pglens:pglens@localhost:5433/shop"
}
}
}
}Full walkthrough: docs/claude-desktop.md.
Evals
Two layers, both run against the seeded database:
Tool-level evals —
evals/cases.jsonholds 11 fixed cases: a question, the tool that should answer it, and the expected diagnosis (e.g. "orders by customer must seq-scan, and the suggestion must beCREATE INDEX idx_orders_customer_id …"; "a DELETE must be rejected").DATABASE_URL=postgres://pglens:pglens@localhost:5433/shop npm run eval # 11/11 eval cases passedProtocol evals —
evals/protocol-evals.mjsspawns the server and speaks raw MCP over stdio, exactly as a client does: 12 cases covering every tool plus the safety rejections.DATABASE_URL=postgres://pglens:pglens@localhost:5433/shop npm run eval:protocol # 12/12 evals passed
This is the eval-first check: the tools are scored on known questions instead of only looking right in a demo.
Development
npm test # vitest — SQL guard, plan parser, tool logic (no database needed)
npm run typecheck
DATABASE_URL=postgres://pglens:pglens@localhost:5433/shop npm test
# also runs the integration tests against a real seeded
# Postgres (docker compose up -d first); CI does the same
# with a Postgres service — see .github/workflows/ci.ymlBuilt on the official MCP TypeScript SDK v2
(@modelcontextprotocol/server 2.4.0). Most tutorials online still use
the v1 package (@modelcontextprotocol/sdk); this project starts on v2
deliberately.
Layout
src/server.ts— MCP server and tool registrationsrc/tools.ts— tool logic (unit-testable without a database)src/plan.ts— EXPLAIN plan parser and index-suggestion logicsrc/sql-guard.ts— read-only SQL guardsrc/db.ts— connection pool + read-only transaction wrapperdb/init.sql— sample shop schema + seed datatest/— vitest testsevals/— fixed eval cases (tool-level + protocol-level)docs/— client setup walkthroughs
Available Tools
5 toolsdescribe_tableDescribe tableA
Describe one table: columns and types, primary key, and existing indexes.
Inputs: table (required), schema.
explain_queryExplain queryA
Run EXPLAIN (FORMAT JSON) for a SELECT query and return a plain summary: total cost, node types, sequential scans, and index scans. The query is planned, never executed, and writes are rejected.
Inputs: query (required).
list_tablesList tablesA
List the tables in the database (excluding system schemas), with approximate row counts.
Inputs: schema.
suggest_indexesSuggest indexesA
Analyse a SELECT query's plan and suggest indexes for sequential scans, with reasoning and the CREATE INDEX statement. Suggestions only — this tool never creates an index.
Inputs: query (required).
table_statsTable statsB
Statistics for one table: live/dead rows, sequential vs index scan counts, and table/index sizes.
Inputs: table (required), schema.
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
TDQS
Scored across 5 tools
Each tool targets a distinct step in a query-tuning workflow: enumeration, schema inspection, plan explanation, index suggestion, and table runtime stats. The only mild overlap is between describe_table and table_stats, which both operate on a single table, but their descriptions clearly separate schema structure from runtime statistics.
Four of five names follow a clean verb_noun snake_case pattern (list_tables, describe_table, explain_query, suggest_indexes). table_stats deviates as noun_noun, but the overall convention remains predictable and readable.
Five tools is well-scoped for a read-only Postgres diagnostics server; each tool earns its place in the list → describe → explain → suggest chain. No redundant or filler tools.
The core workflow of discovering tables, inspecting structure, analysing plans, and proposing indexes is fully covered. Minor gaps exist: no tool to enumerate schemas/views or list all indexes globally (e.g. unused or duplicate index detection), and no pg_stat_statements-style slow-query discovery.
Maintenance
Related MCP Connectors
Hosted MCP server for PostgreSQL diagnostics: slow queries, missing indexes, connection pressure.
Text-to-SQL MCP server: read-only queries on PostgreSQL, MySQL and SQL Server with your real schema
Hosted PostgreSQL MCP server for Claude, ChatGPT and Cursor, with each action switched per account.
Managed Postgres MCP: projects, SQL, docs search, and storage buckets via OAuth.
Related MCP Servers
- AlicenseBqualityDmaintenanceA lightweight Postgres MCP server for safe database exploration and query analysis, read-only by default, with multi-database support.44MIT
- AlicenseAqualityDmaintenanceA self-hostable PostgreSQL MCP server for exploring database schemas and running guarded read/write queries with selectable access modes (readonly, readwrite, admin), plus a dry-run confirm workflow for safety.141MIT
- AlicenseNot gradedqualityDmaintenanceA read-only MCP server for PostgreSQL that enables safe database introspection and querying via natural language.MIT
- AlicenseNot gradedqualityDmaintenanceRead-only MCP server for PostgreSQL, enabling schema discovery, table metadata, and safe SELECT queries via READ ONLY transactions.11 npmMIT