Help Your AI Coding Assistant Write Better PostgreSQL

We've all been there. You're in the flow, asking your AI pair-programmer to write a quick PostgreSQL query or function. It gives you something that looks right, but it's missing a key optimization, uses an inefficient pattern, or just feels a bit off. You end up spending more time correcting the AI's SQL than if you'd written it yourself.

What if you could nudge the AI toward better, more idiomatic PostgreSQL from the start? That's the idea behind a new open-source project from Timescale.

What It Does

The pg-aiguide is a set of structured guidelines and examples designed to be fed into the context window of Large Language Models (LLMs) like ChatGPT, Claude, or GitHub Copilot. Think of it as a detailed style guide and best-practices manual, but formatted specifically for AI consumption. It teaches the AI how to write PostgreSQL code that is performant, secure, and follows community-accepted patterns.

It doesn't run your code; it guides the tool that writes your code.

Why It's Cool

The clever part is in the implementation. This isn't just a vague list of tips. The repository breaks down PostgreSQL wisdom into clear, structured categories that an LLM can easily understand and apply:

• Core Principles: Establishes the mindset for writing good SQL—thinking in sets, prioritizing clarity, and planning for performance from the start.
• Performance & Efficiency: This is the gold. It pushes the AI toward using EXPLAIN ANALYZE, proper indexing strategies, avoiding N+1 query patterns, and choosing the right JOIN types.
• Readability & Maintainability: Encourages consistent formatting, meaningful aliases, and the use of Common Table Expressions (CTEs) to structure complex queries logically.
• Security: Guides the AI to use parameterized queries over string concatenation to prevent SQL injection, a critical and often overlooked point.
• Use-Case Specifics: Provides patterns for common operations like upserts, pagination, and working with JSONB data.

By priming your AI assistant with this context, you're essentially giving it a senior PostgreSQL developer's playbook. The result is that the initial code suggestions you get are significantly closer to production-ready, saving you the back-and-forth refinement cycle.

How to Try It

Getting started is straightforward. The project is documentation-based, so there's no installation or server to run.

1. Head over to the pg-aiguide GitHub repository.
2. Browse the guidelines/ directory. Each markdown file covers a specific topic (e.g., performance.md, security.md).
3. To use it, you copy the relevant sections (or the entire guide for a major project) and paste them into a prompt for your LLM of choice. For example:

   "You are an expert PostgreSQL developer. Follow these guidelines for all code you write: [Paste guidelines here]. Now, write a query to find the top 10 active users by engagement from the users and activity_log tables."

You can experiment by adding different sections of the guide to your context and seeing how it changes the AI's output.

Final Thoughts

As AI coding tools become a staple in the developer workflow, the quality of their output is only as good as the context we give them. pg-aiguide is a smart, practical approach to this problem. It's a force multiplier, letting you encode years of database experience into a form your AI assistant can actually use.

For anyone regularly using Copilot or ChatGPT to draft database logic, spending ten minutes to integrate parts of this guide into your prompts is a no-brainer. It won't replace deep database knowledge, but it will make your AI pairing sessions much more productive, letting you focus on the bigger architectural picture instead of correcting basic SQL missteps.

@githubprojects