A Postgres MCP server with index tuning, explain plans, health checks, and safe sql execution. Overview • Quick Start • Technical Notes • Related Projects • Postgres MCP Pro is an open source Model Context Protocol (MCP) server built to support you and your AI agents throughout the entire development process—from initial coding, through testing and deployment, and to production tuning and maintena
Add this skill
npx mdskills install crystaldba/postgres-mcpComprehensive PostgreSQL MCP server with 14 tools, strong security defaults, and excellent documentation
1<div align="center">23<img src="assets/postgres-mcp-pro.png" alt="Postgres MCP Pro Logo" width="600"/>45[](https://opensource.org/licenses/MIT)6[](https://pypi.org/project/postgres-mcp/)7[](https://discord.gg/4BEHC7ZM)8[](https://x.com/auto_dba)9[](https://github.com/crystaldba/postgres-mcp/graphs/contributors)1011<h3>A Postgres MCP server with index tuning, explain plans, health checks, and safe sql execution.</h3>1213<div class="toc">14 <a href="#overview">Overview</a> •15 <a href="#demo">Demo</a> •16 <a href="#quick-start">Quick Start</a> •17 <a href="#technical-notes">Technical Notes</a> •18 <a href="#mcp-server-api">MCP API</a> •19 <a href="#related-projects">Related Projects</a> •20 <a href="#frequently-asked-questions">FAQ</a>21</div>2223</div>2425## Overview2627**Postgres MCP Pro** is an open source Model Context Protocol (MCP) server built to support you and your AI agents throughout the **entire development process**—from initial coding, through testing and deployment, and to production tuning and maintenance.2829Postgres MCP Pro does much more than wrap a database connection.3031Features include:3233- **🔍 Database Health** - analyze index health, connection utilization, buffer cache, vacuum health, sequence limits, replication lag, and more.34- **⚡ Index Tuning** - explore thousands of possible indexes to find the best solution for your workload, using industrial-strength algorithms.35- **📈 Query Plans** - validate and optimize performance by reviewing EXPLAIN plans and simulating the impact of hypothetical indexes.36- **🧠 Schema Intelligence** - context-aware SQL generation based on detailed understanding of the database schema.37- **🛡️ Safe SQL Execution** - configurable access control, including support for read-only mode and safe SQL parsing, making it usable for both development and production.3839Postgres MCP Pro supports both the [Standard Input/Output (stdio)](https://modelcontextprotocol.io/docs/concepts/transports#standard-input%2Foutput-stdio) and [Server-Sent Events (SSE)](https://modelcontextprotocol.io/docs/concepts/transports#server-sent-events-sse) transports, for flexibility in different environments.4041For additional background on why we built Postgres MCP Pro, see [our launch blog post](https://www.crystaldba.ai/blog/post/announcing-postgres-mcp-server-pro).4243## Demo4445*From Unusable to Lightning Fast*46- **Challenge:** We generated a movie app using an AI assistant, but the SQLAlchemy ORM code ran painfully slow.47- **Solution:** Using Postgres MCP Pro with Cursor, we fixed the performance issues in minutes.4849What we did:50- 🚀 Fixed performance - including ORM queries, indexing, and caching51- 🛠️ Fixed a broken page - by prompting the agent to explore the data, fix queries, and add related content.52- 🧠 Improved the top movies - by exploring the data and fixing the ORM query to surface more relevant results.5354See the video below or read the [play-by-play](examples/movie-app.md).5556https://github.com/user-attachments/assets/24e05745-65e9-4998-b877-a368f1eadc135758596061## Quick Start6263### Prerequisites6465Before getting started, ensure you have:661. Access credentials for your database.672. Docker *or* Python 3.12 or higher.6869#### Access Credentials70 You can confirm your access credentials are valid by using `psql` or a GUI tool such as [pgAdmin](https://www.pgadmin.org/).717273#### Docker or Python7475The choice to use Docker or Python is yours.76We generally recommend Docker because Python users can encounter more environment-specific issues.77However, it often makes sense to use whichever method you are most familiar with.787980### Installation8182Choose one of the following methods to install Postgres MCP Pro:8384#### Option 1: Using Docker8586Pull the Postgres MCP Pro MCP server Docker image.87This image contains all necessary dependencies, providing a reliable way to run Postgres MCP Pro in a variety of environments.8889```bash90docker pull crystaldba/postgres-mcp91```929394#### Option 2: Using Python9596If you have `pipx` installed you can install Postgres MCP Pro with:9798```bash99pipx install postgres-mcp100```101102Otherwise, install Postgres MCP Pro with `uv`:103104```bash105uv pip install postgres-mcp106```107108If you need to install `uv`, see the [uv installation instructions](https://docs.astral.sh/uv/getting-started/installation/).109110111### Configure Your AI Assistant112113We provide full instructions for configuring Postgres MCP Pro with Claude Desktop.114Many MCP clients have similar configuration files, you can adapt these steps to work with the client of your choice.115116#### Claude Desktop Configuration117118You will need to edit the Claude Desktop configuration file to add Postgres MCP Pro.119The location of this file depends on your operating system:120- MacOS: `~/Library/Application Support/Claude/claude_desktop_config.json`121- Windows: `%APPDATA%/Claude/claude_desktop_config.json`122123You can also use `Settings` menu item in Claude Desktop to locate the configuration file.124125You will now edit the `mcpServers` section of the configuration file.126127##### If you are using Docker128129```json130{131 "mcpServers": {132 "postgres": {133 "command": "docker",134 "args": [135 "run",136 "-i",137 "--rm",138 "-e",139 "DATABASE_URI",140 "crystaldba/postgres-mcp",141 "--access-mode=unrestricted"142 ],143 "env": {144 "DATABASE_URI": "postgresql://username:password@localhost:5432/dbname"145 }146 }147 }148}149```150151The Postgres MCP Pro Docker image will automatically remap the hostname `localhost` to work from inside of the container.152153- MacOS/Windows: Uses `host.docker.internal` automatically154- Linux: Uses `172.17.0.1` or the appropriate host address automatically155156##### If you are using `uvx`157158```json159{160 "mcpServers": {161 "postgres": {162 "command": "uvx",163 "args": [164 "postgres-mcp",165 "--access-mode=unrestricted"166 ],167 "env": {168 "DATABASE_URI": "postgresql://username:password@localhost:5432/dbname"169 }170 }171 }172}173```174175176##### If you are using `pipx`177178```json179{180 "mcpServers": {181 "postgres": {182 "command": "postgres-mcp",183 "args": [184 "--access-mode=unrestricted"185 ],186 "env": {187 "DATABASE_URI": "postgresql://username:password@localhost:5432/dbname"188 }189 }190 }191}192```193194195##### If you are using `uv`196197```json198{199 "mcpServers": {200 "postgres": {201 "command": "uv",202 "args": [203 "run",204 "postgres-mcp",205 "--access-mode=unrestricted"206 ],207 "env": {208 "DATABASE_URI": "postgresql://username:password@localhost:5432/dbname"209 }210 }211 }212}213```214215216##### Connection URI217218Replace `postgresql://...` with your [Postgres database connection URI](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING-URIS).219220221##### Access Mode222223Postgres MCP Pro supports multiple *access modes* to give you control over the operations that the AI agent can perform on the database:224- **Unrestricted Mode**: Allows full read/write access to modify data and schema. It is suitable for development environments.225- **Restricted Mode**: Limits operations to read-only transactions and imposes constraints on resource utilization (presently only execution time). It is suitable for production environments.226227To use restricted mode, replace `--access-mode=unrestricted` with `--access-mode=restricted` in the configuration examples above.228229230#### Other MCP Clients231232Many MCP clients have similar configuration files to Claude Desktop, and you can adapt the examples above to work with the client of your choice.233234- If you are using Cursor, you can use navigate from the `Command Palette` to `Cursor Settings`, then open the `MCP` tab to access the configuration file.235- If you are using Windsurf, you can navigate to from the `Command Palette` to `Open Windsurf Settings Page` to access the configuration file.236- If you are using Goose run `goose configure`, then select `Add Extension`.237- If you are using Qodo Gen, open the Chat panel, click `Connect more tools`, click `+ Add new MCP`, then add the new configuration.238239## SSE Transport240241Postgres MCP Pro supports the [SSE transport](https://modelcontextprotocol.io/docs/concepts/transports#server-sent-events-sse), which allows multiple MCP clients to share one server, possibly a remote server.242To use the SSE transport, you need to start the server with the `--transport=sse` option.243244For example, with Docker run:245246```bash247docker run -p 8000:8000 \248 -e DATABASE_URI=postgresql://username:password@localhost:5432/dbname \249 crystaldba/postgres-mcp --access-mode=unrestricted --transport=sse250```251252Then update your MCP client configuration to call the the MCP server.253For example, in Cursor's `mcp.json` or Cline's `cline_mcp_settings.json` you can put:254255```json256{257 "mcpServers": {258 "postgres": {259 "type": "sse",260 "url": "http://localhost:8000/sse"261 }262 }263}264```265266For Windsurf, the format in `mcp_config.json` is slightly different:267268```json269{270 "mcpServers": {271 "postgres": {272 "type": "sse",273 "serverUrl": "http://localhost:8000/sse"274 }275 }276}277```278279## Postgres Extension Installation (Optional)280281To enable index tuning and comprehensive performance analysis you need to load the `pg_stat_statements` and `hypopg` extensions on your database.282283- The `pg_stat_statements` extension allows Postgres MCP Pro to analyze query execution statistics.284For example, this allows it to understand which queries are running slow or consuming significant resources.285- The `hypopg` extension allows Postgres MCP Pro to simulate the behavior of the Postgres query planner after adding indexes.286287### Installing extensions on AWS RDS, Azure SQL, or Google Cloud SQL288289If your Postgres database is running on a cloud provider managed service, the `pg_stat_statements` and `hypopg` extensions should already be available on the system.290In this case, you can just run `CREATE EXTENSION` commands using a role with sufficient privileges:291292```sql293CREATE EXTENSION IF NOT EXISTS pg_stat_statements;294CREATE EXTENSION IF NOT EXISTS hypopg;295```296297### Installing extensions on self-managed Postgres298299If you are managing your own Postgres installation, you may need to do additional work.300Before loading the `pg_stat_statements` extension you must ensure that it is listed in the `shared_preload_libraries` in the Postgres configuration file.301The `hypopg` extension may also require additional system-level installation (e.g., via your package manager) because it does not always ship with Postgres.302303## Usage Examples304305### Get Database Health Overview306307Ask:308> Check the health of my database and identify any issues.309310### Analyze Slow Queries311312Ask:313> What are the slowest queries in my database? And how can I speed them up?314315### Get Recommendations On How To Speed Things Up316317Ask:318> My app is slow. How can I make it faster?319320### Generate Index Recommendations321322Ask:323> Analyze my database workload and suggest indexes to improve performance.324325### Optimize a Specific Query326327Ask:328> Help me optimize this query: SELECT \* FROM orders JOIN customers ON orders.customer_id = customers.id WHERE orders.created_at > '2023-01-01';329330## MCP Server API331332The [MCP standard](https://modelcontextprotocol.io/) defines various types of endpoints: Tools, Resources, Prompts, and others.333334Postgres MCP Pro provides functionality via [MCP tools](https://modelcontextprotocol.io/docs/concepts/tools) alone.335We chose this approach because the [MCP client ecosystem](https://modelcontextprotocol.io/clients) has widespread support for MCP tools.336This contrasts with the approach of other Postgres MCP servers, including the [Reference Postgres MCP Server](https://github.com/modelcontextprotocol/servers-archived/tree/main/src/postgres), which use [MCP resources](https://modelcontextprotocol.io/docs/concepts/resources) to expose schema information.337338339Postgres MCP Pro Tools:340341| Tool Name | Description |342|-----------|-------------|343| `list_schemas` | Lists all database schemas available in the PostgreSQL instance. |344| `list_objects` | Lists database objects (tables, views, sequences, extensions) within a specified schema. |345| `get_object_details` | Provides information about a specific database object, for example, a table's columns, constraints, and indexes. |346| `execute_sql` | Executes SQL statements on the database, with read-only limitations when connected in restricted mode. |347| `explain_query` | Gets the execution plan for a SQL query describing how PostgreSQL will process it and exposing the query planner's cost model. Can be invoked with hypothetical indexes to simulate the behavior after adding indexes. |348| `get_top_queries` | Reports the slowest SQL queries based on total execution time using `pg_stat_statements` data. |349| `analyze_workload_indexes` | Analyzes the database workload to identify resource-intensive queries, then recommends optimal indexes for them. |350| `analyze_query_indexes` | Analyzes a list of specific SQL queries (up to 10) and recommends optimal indexes for them. |351| `analyze_db_health` | Performs comprehensive health checks including: buffer cache hit rates, connection health, constraint validation, index health (duplicate/unused/invalid), sequence limits, and vacuum health. |352353354## Related Projects355356**Postgres MCP Servers**357- [Query MCP](https://github.com/alexander-zuev/supabase-mcp-server). An MCP server for Supabase Postgres with a three-tier safety architecture and Supabase management API support.358- [PG-MCP](https://github.com/stuzero/pg-mcp-server). An MCP server for PostgreSQL with flexible connection options, explain plans, extension context, and more.359- [Reference PostgreSQL MCP Server](https://github.com/modelcontextprotocol/servers-archived/tree/main/src/postgres). A simple MCP Server implementation exposing schema information as MCP resources and executing read-only queries.360- [Supabase Postgres MCP Server](https://github.com/supabase-community/supabase-mcp). This MCP Server provides Supabase management features and is actively maintained by the Supabase community.361- [Nile MCP Server](https://github.com/niledatabase/nile-mcp-server). An MCP server providing access to the management API for the Nile's multi-tenant Postgres service.362- [Neon MCP Server](https://github.com/neondatabase-labs/mcp-server-neon). An MCP server providing access to the management API for Neon's serverless Postgres service.363- [Wren MCP Server](https://github.com/Canner/wren-engine). Provides a semantic engine powering business intelligence for Postgres and other databases.364365**DBA Tools (including commercial offerings)**366- [Aiven Database Optimizer](https://aiven.io/solutions/aiven-ai-database-optimizer). A tool that provides holistic database workload analysis, query optimizations, and other performance improvements.367- [dba.ai](https://www.dba.ai/). An AI-powered database administration assistant that integrates with GitHub to resolve code issues.368- [pgAnalyze](https://pganalyze.com/). A comprehensive monitoring and analytics platform for identifying performance bottlenecks, optimizing queries, and real-time alerting.369- [Postgres.ai](https://postgres.ai/). An interactive chat experience combining an extensive Postgres knowledge base and GPT-4.370- [Xata Agent](https://github.com/xataio/agent). An open-source AI agent that automatically monitors database health, diagnoses issues, and provides recommendations using LLM-powered reasoning and playbooks.371372**Postgres Utilities**373- [Dexter](https://github.com/DexterDB/dexter). A tool for generating and testing hypothetical indexes on PostgreSQL.374- [PgHero](https://github.com/ankane/pghero). A performance dashboard for Postgres, with recommendations.375Postgres MCP Pro incorporates health checks from PgHero.376- [PgTune](https://github.com/le0pard/pgtune?tab=readme-ov-file). Heuristics for tuning Postgres configuration.377378## Frequently Asked Questions379380*How is Postgres MCP Pro different from other Postgres MCP servers?*381There are many MCP servers allow an AI agent to run queries against a Postgres database.382Postgres MCP Pro does that too, but also adds tools for understanding and improving the performance of your Postgres database.383For example, it implements a version of the [Anytime Algorithm of Database Tuning Advisor for Microsoft SQL Server](https://www.microsoft.com/en-us/research/wp-content/uploads/2020/06/Anytime-Algorithm-of-Database-Tuning-Advisor-for-Microsoft-SQL-Server.pdf), a modern industrial-strength algorithm for automatic index tuning.384385| Postgres MCP Pro | Other Postgres MCP Servers |386|--------------|----------------------------|387| ✅ Deterministic database health checks | ❌ Unrepeatable LLM-generated health queries |388| ✅ Principled indexing search strategies | ❌ Gen-AI guesses at indexing improvements |389| ✅ Workload analysis to find top problems | ❌ Inconsistent problem analysis |390| ✅ Simulates performance improvements | ❌ Try it yourself and see if it works |391392Postgres MCP Pro complements generative AI by adding deterministic tools and classical optimization algorithms393The combination is both reliable and flexible.394395396*Why are MCP tools needed when the LLM can reason, generate SQL, etc?*397LLMs are invaluable for tasks that involve ambiguity, reasoning, or natural language.398When compared to procedural code, however, they can be slow, expensive, non-deterministic, and sometimes produce unreliable results.399In the case of database tuning, we have well established algorithms, developed over decades, that are proven to work.400Postgres MCP Pro lets you combine the best of both worlds by pairing LLMs with classical optimization algorithms and other procedural tools.401402*How do you test Postgres MCP Pro?*403Testing is critical to ensuring that Postgres MCP Pro is reliable and accurate.404We are building out a suite of AI-generated adversarial workloads designed to challenge Postgres MCP Pro and ensure it performs under a broad variety of scenarios.405406*What Postgres versions are supported?*407Our testing presently focuses on Postgres 15, 16, and 17.408We plan to support Postgres versions 13 through 17.409410*Who created this project?*411This project is created and maintained by [Crystal DBA](https://www.crystaldba.ai).412413## Roadmap414415*TBD*416417You and your needs are a critical driver for what we build.418Tell us what you want to see by opening an [issue](https://github.com/crystaldba/postgres-mcp/issues) or a [pull request](https://github.com/crystaldba/postgres-mcp/pulls).419You can also contact us on [Discord](https://discord.gg/4BEHC7ZM).420421## Technical Notes422423This section includes a high-level overview technical considerations that influenced the design of Postgres MCP Pro.424425### Index Tuning426427Developers know that missing indexes are one of the most common causes of database performance issues.428Indexes provide access methods that allow Postgres to quickly locate data that is required to execute a query.429When tables are small, indexes make little difference, but as the size of the data grows, the difference in algorithmic complexity between a table scan and an index lookup becomes significant (typically *O*(*n*) vs *O*(*log* *n*), potentially more if joins on multiple tables are involved).430431Generating suggested indexes in Postgres MCP Pro proceeds in several stages:4324331. *Identify SQL queries in need of tuning*.434 If you know you are having a problem with a specific SQL query you can provide it.435 Postgres MCP Pro can also analyze the workload to identify index tuning targets.436 To do this, it relies on the `pg_stat_statements` extension, which records the runtime and resource consumption of each query.437438 A query is a candidate for index tuning if it is a top resource consumer, either on a per-execution basis or in aggregate.439 At present, we use execution time as a proxy for cumulative resource consumption, but it may also make sense to look at specifics resources, e.g., the number of blocks accessed or the number of blocks read from disk.440 The `analyze_query_workload` tool focuses on slow queries, using the mean time per execution with thresholds for execution count and mean execution time.441 Agents may also call `get_top_queries`, which accepts a parameter for mean vs. total execution time, then pass these queries `analyze_query_indexes` to get index recommendations.442443 Sophisticated index tuning systems use "workload compression" to produce a representative subset of queries that reflects the characteristics of the workload as a whole, reducing the problem for downstream algorithms.444 Postgres MCP Pro performs a limited form of workload compression by normalizing queries so that those generated from the same template appear as one.445 It weights each query equally, a simplification that works when the benefits to indexing are large.4464472. *Generate candidate indexes*448 Once we have a list of SQL queries that we want to improve through indexing, we generate a list of indexes that we might want to add.449 To do this, we parse the SQL and identify any columns used in filters, joins, grouping, or sorting.450451 To generate all possible indexes we need to consider combinations of these columns, because Postgres supports [multicolumn indexes](https://www.postgresql.org/docs/current/indexes-multicolumn.html).452 In the present implementation, we include only one permutation of each possible multicolumn index, which is selected at random.453 We make this simplification to reduce the search space because permutations often have equivalent performance.454 However, we hope to improve in this area.4554563. *Search for the optimal index configuration*.457 Our objective is to find the combination of indexes that optimally balances the performance benefits against the costs of storing and maintaining those indexes.458 We estimate the performance improvement by using the "what if?" capabilities provided by the `hypopg` extension.459 This simulates how the Postgres query optimizer will execute a query after the addition of indexes, and reports changes based on the actual Postgres cost model.460461 One challenge is that generating query plans generally requires knowledge of the specific parameter values used in the query.462 Query normalization, which is necessary to reduce the queries under consideration, removes parameter constants.463 Parameter values provided via bind variables are similarly not available to us.464465 To address this problem, we produce realistic constants that we can provide as parameters by sampling from the table statistics.466 In version 16, Postgres added [generic explain plan functionality](https://www.postgresql.org/docs/current/sql-explain.html), but it has limitations, for example around `LIKE` clauses, which our implementation does not have.467468 Search strategy is critical because evaluating all possible index combinations feasible only in simple situations.469 This is what most sets apart various indexing approaches.470 Adapting the approach of Microsoft's Anytime algorithm, we employ a greedy search strategy, i.e., find the best one-index solution, then find the best index to add to that to produce a two-index solution.471 Our search terminates when the time budget is exhausted or when a round of exploration fails to produce any gains above the minimum improvement threshold of 10%.4724734. *Cost-benefit analysis*.474 When posed with two indexing alternatives, one which produces better performance and one which requires more space, how do we decide which to choose?475 Traditionally, index advisors ask for a storage budget and optimize performance with respect to that storage budget.476 We also take a storage budget, but perform a cost-benefit analysis throughout the optimization.477478 We frame this as the problem of selecting a point along the [Pareto front](https://en.wikipedia.org/wiki/Pareto_front)—the set of choices for which improving one quality metric necessarily worsens another.479 In an ideal world, we might want to assess the cost of the storage and the benefit of improved performance in monetary terms.480 However, there is a simpler and more practical approach: to look at the changes in relative terms.481 Most people would agree that a 100x performance improvement is worth it, even if the storage cost is 2x.482 In our implementation, we use a configurable parameter to set this threshold.483 By default, we require the change in the log (base 10) of the performance improvement to be 2x the difference in the log of the space cost.484 This works out to allowing a maximum 10x increase in space for a 100x performance improvement.485486Our implementation is most closely related to the [Anytime Algorithm](https://www.microsoft.com/en-us/research/wp-content/uploads/2020/06/Anytime-Algorithm-of-Database-Tuning-Advisor-for-Microsoft-SQL-Server.pdf) found in Microsoft SQL Server.487Compared to [Dexter](https://github.com/ankane/dexter/), an automatic indexing tool for Postgres, we search a larger space and use different heuristics.488This allows us to generate better solutions at the cost of longer runtime.489490We also show the work done in each round of the search, including a comparison of the query plans before and after the addition of each index.491This give the LLM additional context that it can use when responding to the indexing recommendations.492493### Experimental: Index Tuning by LLM494495Postgres MCP Pro includes an experimental index tuning feature based on [Optimization by LLM](https://arxiv.org/abs/2309.03409).496Instead of using heuristics to explore possible index configurations, we provide the database schema and query plans to an LLM and ask it to propose index configurations.497We then use `hypopg` to predict performance with the proposed indexes, then feed those results back into the LLM to produce a new set of suggestions.498We repeat this process until multiple rounds of iteration produce no further improvements.499500Index optimization by LLM is has advantages when the index search space is large, or when indexes with many columns need to be considered.501Like traditional search-based approaches, it relies on the accuracy of the `hypopg` performance predictions.502503In order to perform index optimization by LLM, you must provide an OpenAI API key by setting the `OPENAI_API_KEY` environment variable.504505506### Database Health507508Database health checks identify tuning opportunities and maintenance needs before they lead to critical issues.509In the present release, Postgres MCP Pro adapts the database health checks directly from [PgHero](https://github.com/ankane/pghero).510We are working to fully validate these checks and may extend them in the future.511512- *Index Health*. Looks for unused indexes, duplicate indexes, and indexes that are bloated. Bloated indexes make inefficient use of database pages.513 Postgres autovacuum cleans up index entries pointing to dead tuples, and marks the entries as reusable. However, it does not compact the index pages and, eventually, index pages may contain few live tuple references.514- *Buffer Cache Hit Rate*. Measures the proportion of database reads that are served from the buffer cache instead of disk.515 A low buffer cache hit rate must be investigated as it is often not cost-optimal and leads to degraded application performance.516- *Connection Health*. Checks the number of connections to the database and reports on their utilization.517 The biggest risk is running out of connections, but a high number of idle or blocked connections can also indicate issues.518- *Vacuum Health*. Vacuum is important for many reasons.519 A critical one is preventing transaction id wraparound, which can cause the database to stop accepting writes.520 The Postgres multi-version concurrency control (MVCC) mechanism requires a unique transaction id for each transaction.521 However, because Postgres uses a 32-bit signed integer for transaction ids, it needs to reuse transaction ids after after a maximum of 2 billion transactions.522 To do this it "freezes" the transaction ids of historical transactions, setting them all to a special value that indicates distant past.523 When records first go to disk, they are written visibility for a range of transaction ids.524 Before re-using these transaction ids, Postgres must update any on-disk records, "freezing" them to remove the references to the transaction ids to be reused.525 This check looks for tables that require vacuuming to prevent transaction id wraparound.526- *Replication Health*. Checks replication health by monitoring lag between primary and replicas, verifying replication status, and tracking usage of replication slots.527- *Constraint Health*. During normal operation, Postgres rejects any transactions that would cause a constraint violation.528 However, invalid constraints may occur after loading data or in recovery scenarios. This check looks for any invalid constraints.529- *Sequence Health*. Looks for sequences that are at risk of exceeding their maximum value.530531532### Postgres Client Library533534Postgres MCP Pro uses [psycopg3](https://www.psycopg.org/) to connect to Postgres using asynchronous I/O.535Under the hood, psycopg3 uses the [libpq](https://www.postgresql.org/docs/current/libpq.html) library to connect to Postgres, providing access to the full Postgres feature set and an underlying implementation fully supported by the Postgres community.536537Some other Python-based MCP servers use [asyncpg](https://github.com/MagicStack/asyncpg), which may simplify installation by eliminating the `libpq` dependency.538Asyncpg is also probably [faster](https://fernandoarteaga.dev/blog/psycopg-vs-asyncpg/) than psycopg3, but we have not validated this ourselves.539[Older benchmarks](https://gistpreview.github.io/?0ed296e93523831ea0918d42dd1258c2) report a larger performance gap, suggesting that the newer psycopg3 has closed the gap as it matures.540541Balancing these considerations, we selected `psycopg3` over `asyncpg`.542We remain open to revising this decision in the future.543544545### Connection Configuration546547Like the [Reference PostgreSQL MCP Server](https://github.com/modelcontextprotocol/servers-archived/tree/main/src/postgres), Postgres MCP Pro takes Postgres connection information at startup.548This is convenient for users who always connect to the same database but can be cumbersome when users switch databases.549550An alternative approach, taken by [PG-MCP](https://github.com/stuzero/pg-mcp-server), is provide connection details via MCP tool calls at the time of use.551This is more convenient for users who switch databases, and allows a single MCP server to simultaneously support multiple end-users.552553There must be a better approach than either of these.554Both have security weaknesses—few MCP clients store the MCP server configuration securely (an exception is Goose), and credentials provided via MCP tools are passed through the LLM and stored in the chat history.555Both also have usability issues in some scenarios.556557558### Schema Information559560The purpose of the schema information tool is to provide the calling AI agent with the information it needs to generate correct and performant SQL.561For example, suppose a user asks, "How many flights took off from San Francisco and landed in Paris during the past year?"562The AI agent needs to find the table that stores the flights, the columns that store the origin and destinations, and perhaps a table that maps between airport codes and airport locations.563564565*Why provide schema information tools when LLMs are generally capable of generating the SQL to retrieve this information from Postgres directly?*566567Our experience using Claude indicates that the calling LLM is very good at generating SQL to explore the Postgres schema by querying the [Postgres system catalog](https://www.postgresql.org/docs/current/catalogs.html) and the [information schema](https://www.postgresql.org/docs/current/information-schema.html) (an ANSI-standardized database metadata view).568However, we do not know whether other LLMs do so as reliably and capably.569570*Would it be better to provide schema information using [MCP resources](https://modelcontextprotocol.io/docs/concepts/resources) rather than [MCP tools](https://modelcontextprotocol.io/docs/concepts/tools)?*571572The [Reference PostgreSQL MCP Server](https://github.com/modelcontextprotocol/servers-archived/tree/main/src/postgres) uses resources to expose schema information rather than tools.573Navigating resources is similar to navigating a file system, so this approach is natural in many ways.574However, resource support is less widespread than tool support in the MCP client ecosystem (see [example clients](https://modelcontextprotocol.io/clients)).575In addition, while the MCP standard says that resources can be accessed by either AI agents or end-user humans, some clients only support human navigation of the resource tree.576577578### Protected SQL Execution579580AI amplifies longstanding challenges of protecting databases from a range of threats, ranging from simple mistakes to sophisticated attacks by malicious actors.581Whether the threat is accidental or malicious, a similar security framework applies, with aims that fall into three categories: confidentiality, integrity, and availability.582The familiar tension between convenience and safety is also evident and pronounced.583584Postgres MCP Pro's protected SQL execution mode focuses on integrity.585In the context of MCP, we are most concerned with LLM-generated SQL causing damage—for example, unintended data modification or deletion, or other changes that might circumvent an organization's change management process.586587The simplest way to provide integrity is to ensure that all SQL executed against the database is read-only.588One way to do this is by creating a database user with read-only access permissions.589While this is a good approach, many find this cumbersome in practice.590Postgres does not provide a way to place a connection or session into read-only mode, so Postgres MCP Pro uses a more complex approach to ensure read-only SQL execution on top of a read-write connection.591592Postgres MCP Provides a read-only transaction mode that prevents data and schema modifications.593Like the [Reference PostgreSQL MCP Server](https://github.com/modelcontextprotocol/servers-archived/tree/main/src/postgres), we use read-only transactions to provide protected SQL execution.594595To make this mechanism robust, we need to ensure that the SQL does not somehow circumvent the read-only transaction mode, say by issuing a `COMMIT` or `ROLLBACK` statement and then beginning a new transaction.596597For example, the LLM can circumvent the read-only transaction mode by issuing a `ROLLBACK` statement and then beginning a new transaction.598For example:599```sql600ROLLBACK; DROP TABLE users;601```602603To prevent cases like this, we parse the SQL before execution using the [pglast](https://pglast.readthedocs.io/) library.604We reject any SQL that contains `commit` or `rollback` statements.605Helpfully, the popular Postgres stored procedure languages, including PL/pgSQL and PL/Python, do not allow for `COMMIT` or `ROLLBACK` statements.606If you have unsafe stored procedure languages enabled on your database, then our read-only protections could be circumvented.607608At present, Postgres MCP Pro provides two levels of protection for the database, one at either extreme of the convenience/safety spectrum.609- "Unrestricted" provides maximum flexibility.610It is suitable for development environments where speed and flexibility are paramount, and where there is no need to protect valuable or sensitive data.611- "Restricted" provides a balance between flexibility and safety.612It is suitable for production environments where the database is exposed to untrusted users, and where it is important to protect valuable or sensitive data.613614Unrestricted mode aligns with the approach of [Cursor's auto-run mode](https://docs.cursor.com/chat/tools#auto-run), where the AI agent operates with limited human oversight or approvals.615We expect auto-run to be deployed in development environments where the consequences of mistakes are low, where databases do not contain valuable or sensitive data, and where they can be recreated or restored from backups when needed.616617We designed restricted mode to be conservative, erring on the side of safety even though it may be inconvenient.618Restricted mode is limited to read-only operations, and we limit query execution time to prevent long-running queries from impacting system performance.619We may add measures in the future to make sure that restricted mode is safe to use with production databases.620621622## Postgres MCP Pro Development623624The instructions below are for developers who want to work on Postgres MCP Pro, or users who prefer to install Postgres MCP Pro from source.625626### Local Development Setup6276281. **Install uv**:629630 ```bash631 curl -sSL https://astral.sh/uv/install.sh | sh632 ```6336342. **Clone the repository**:635636 ```bash637 git clone https://github.com/crystaldba/postgres-mcp.git638 cd postgres-mcp639 ```6406413. **Install dependencies**:642643 ```bash644 uv pip install -e .645 uv sync646 ```6476484. **Run the server**:649 ```bash650 uv run postgres-mcp "postgres://user:password@localhost:5432/dbname"651 ```652
Full transparency — inspect the skill content before installing.