Postgres MCP

## Movie Ratings Website Let's do a quick AI-coding session and take an idea from concept to launch! We'll use the [IMDB dataset](https://developer.imdb.com/non-commercial-datasets/) to build a movie ratings website. **Our AI tools:** - **Replit** - for the initial prototype - **Cursor** - as our AI coding agent - **Postgres Pro** - to give Cursor a Postgres expert **What we did:** 1) Create the initial app on Replit - it's slow! 2) Fixed performance - including ORM queries, indexing, and caching 3) Fixed an empty movie details pages 4) Improved the sort for top-rated movies **Full Video** *(play-by-play walkthrough is below)* https://github.com/user-attachments/assets/24e05745-65e9-4998-b877-a368f1eadc13 --- **Let's get started...** <table> <tbody> <tr> <td align="left" valign="top"> <h4>1) Create the initial app on Replit</h4> <p>We prompt Replit with:</p> <blockquote> <p>Create a web app based on flask, python and SQAlchemy ORM</p> <p>It's website that uses the schema from the public IMDB dataset . Assume I've imported the IMDB dataset as-is and add to that. I want people to be able to browse a mobile-friendly page for each movie, with all the IMDB data related to that movie. Additionally, people can rate each movie 1-5 and view top rated movies. The community and these ratings are one of the primary uses cases for the website.</p> </blockquote> <p><b>Boom!</b> We have a fully functional website with ratings, search, browse, auth -- in under an hour. What!! So cool.</p> <p><b>But it's slooooow...</b></br> The AI agent created a bunch of ORM code and what looked like reasonable indexes, but clearly it got it wrong.</p> </td> <td align="center"><a href="https://youtu.be/v09nxLF3QKI"><img src="https://github.com/user-attachments/assets/2609dfcb-2ff3-45b9-89f1-6d991e65c461"/></a></td> </tr> <tr> <td align="left" valign="top"> <h4>2) Fix query performance</h4> <p>Our website looks decent, but it's too slow to ship.<br/> Let's switch to Cursor w/ Postgres Pro to get the app ready for launch.</p> <p>Our prompt:</p> <blockquote> <div>My app is slow!</div> <div>- Look for opportunities to speed up by improving queries, indexes or caching.</div> <div>- For db changes use migration scripts I can apply later.</div> </blockquote> <p>Let's see what all the AI agent did.</p> <ol> <li>Explored the schema and code to identify potential problem queries</li> <li>Used Postgres Pro to diagnose by calling <code>get_top_queries</code>, <code>analyze_db_health</code>, and <code>analyze_query_indexes</code></li> <li>Added multiple indexes to improve query performance</li> <li>Remove unused and bloated indexes to reclaim space</li> <li>Added caching for expensive queries and image loading</li> <li>Created a migration script to apply the changes</li> </ol> <p>That was amazing! The agent was able to connect the dots between the database analysis and the code to create a comprehensive PR in 2.5 minutes.</p> <div>It summarized the expected impact:</div> <ul> <li>Text searches will be 10-100x faster</li> <li>Page loads will be 2-5x faster</li> <li>Database load will be significantly reduced</li> <li>External API calls will be reduced by ~90%</li> </ul> </td> <td align="center"><a href="https://youtu.be/v09nxLF3QKI?t=42"><img src="https://github.com/user-attachments/assets/3e9cdd1d-e93e-4e4a-a043-ffdc6f4feea6"/></a></td> </tr> <tr> <td align="left" valign="top"> <h4>3) Fix empty movie details pages</h4> <p>The movie details looks empty. Let's investigate.</p> <blockquote> <div>The movie details page looks awful.</div> <div>- no cast/crew. Are we missing the data or is the query wrong?</div> <div>- The ratings looks misplaced. move it closer to the title</div> <div>- Do we have additional data we can include like a description? Check the schema.</div> </blockquote> <div>The result?</div> <ol> <li>It used Postgres Pro to inspect the schema and compare it against the code.</li> <li>It fixed the query in the route to join with <code>name_basics</code>.</li> <li>It identified additional data in <code>title_basics</code> to create a new About section with genre, runtime, and release years.</li> </ol> Let's ask: <blockquote>Am I missing any data?</blockquote> <p>The AI Agent runs the sql queries and figures out we are indeed missing the cast/crew data. It writes a script to import it in a more reliable way.</p> <div><em>(it turned out my original script aborted on errors)</em></p> </td> <td align="center"> <a href="https://youtu.be/v09nxLF3QKI?t=184"><img src="https://github.com/user-attachments/assets/a5727fd5-3845-4110-998d-5af4f386ce0e"/></a> </td> </tr> <tr> <td align="left" valign="top"> <h4>4) Improve the sort for top-rated movies</h4> <p>The top-rated page is showing the classics like "Sisters of the Shrink 4" and "Zhuchok", etc. Something is wrong!</p> <blockquote> <div>How are the top-rated sorted? It seems random. Do we have data in those tables? Is the query it uses working?</div> </blockquote> <div>The Agent checks the data and code dentifies that the issue is there is no minimum on the <code>num_votes</code></div> <br/> <div>So I ask:</div> <blockquote> <div>help me find a good minimum of reviews</div> </blockquote> <div>The AI Agent gets the distribution of data and some sample results to determine that a 10K vote minimum would give the best results. It's great seeing the results are grounded in reality and not just some hallucination.</div> </td> <td align="center"> <a href="https://youtu.be/v09nxLF3QKI?t=327"><img src="https://github.com/user-attachments/assets/05af6f5d-326c-4976-8719-20d4dcb6712c"/></a> </td> </tr> </tbody> </table> ## Want to learn more? - [Overview](../README.md#overview) - [Features](../README.md#features) - [Quick Start](../README.md#quick-start) - [Technical Notes](../README.md#technical-notes) - [Discord Server](https://discord.gg/4BEHC7ZM)