Show HN: AI-Driven Index Tuning With Postgres MCP Server

Show HN: AI-Driven Index Tuning With Postgres MCP Server(github.com)

11 points by jssmith 1 year ago | 4 comments

Hey HN,

Postgres Pro is an open source Model Context Protocol (MCP) server built to support every stage of the development process.

It does a lot more than help generate and run SQL—it helps you make sure your application and database actually run well.

Features:

- Industrial-strength tuning algorithms similar to those in MS SQL Server

- “What if?” tool to evaluate LLM-generated index suggestions

- Works with Cursor, etc., to make changes in ORM code

- Ensures safe SQL execution with restricted mode

- Includes health checks to stay ahead of production problems

crazygringo 1 year ago |

Using AI to improve query performance seems inevitable, kind of how LLM's have made ffmpeg so much more usable.

There are just so many factors that go into writing a query that will be performant, not just basic indexes but joins vs. subqueries, single-column indexes vs. multi-column indexes with various sort orders, etc. And even if you write something that's performant at 1,000 rows in your test database, it falls apart with 1,000,000 rows in production.

But reliable AI that can actually understand all your queries together with the database structure, in order to find performance improvements, feels like it's going to become as helpful on the database side as Copilot etc. is on the regular coding side.

jssmith 1 year ago | |

We've been focusing on indexing but query rewrites are probably next.

It looks like LLMs can do a pretty good job at generating ideas, particularly if you prompt them right.

After that, you have to: 1/ make sure that the SQL produces the same results, 2/ see what the query planner will actually do with it, 3/ see whether it improves performance.

As you point out, the data makes a difference, so you need production data statistics, at a minimum, to do this well.

rahulcap 1 year ago |

There are lots of these MCP servers out there. The index analysis and health checks make it stick out.

jssmith 1 year ago | |

Right. Why build another MCP server for Postgres?

There's a lot of value in just getting a database connection. But the LLMs hit limits pretty quickly when it comes to index tuning, etc.

Classical algorithms still have their place.