postgresql-best-practices/source-context/README.upstream.md

Postgres Skills

<img alt="License: MIT" src="https://img.shields.io/badge/License-MIT-blue.svg" /> <img alt="Platforms: 3" src="https://img.shields.io/badge/Platforms-Claude_|_Copilot_|_Codex-purple.svg" />

Build, tune, and operate PostgreSQL with an AI assistant that can act, not just advise. Postgres Skills gives developers 33 expert-curated sub-skills for PostgreSQL anywhere: local, self-hosted, other clouds, Azure Database for PostgreSQL, and Azure HorizonDB. It covers application development, query performance, indexing, vector search, RAG, security, operations, and the full Apache AGE graph lifecycle. With postgres-mcp for database actions and the Azure CLI for Azure resource management, your assistant can inspect the real environment and safely execute approved changes instead of returning generic snippets.

What you get

A default AI assistant gives you plausible-looking PostgreSQL advice. This plugin gives you expert guidance and runs it against your actual database, safely:

A default assistant	With this plugin
Suggests generic SQL and hopes it fits	Reads your live schema, tier, and settings first, then tailors the answer
Confidently recommends commands that break managed databases	Applies managed-service guardrails and the correct Azure workflow
Explains what you could do	Executes it — query, index, provision, restore — with confirmation before anything destructive
Can't tell self-hosted from Azure	Detects the connection and routes to the right generic or Azure guidance
Hand-waves "just use a graph database"	Derives an ontology from your data, builds the graph on Apache AGE, and answers questions with openCypher — all inside PostgreSQL

How it works

This repo is the plugin. Installing it gives your agent three things that work together:

• The skill — a lightweight routing table that reads your question and connection, then loads the matching sub-skill (generic, Azure, or graph). A dedicated pg-graph skill owns the full Apache AGE lifecycle — ontology derivation, graph construction, and openCypher querying.
• The postgres-mcp MCP server — executes inside your database: runs queries, applies changes, inspects schema, builds and traverses graphs, and detects whether you're on Azure.
• The Azure CLI (az) — executes on the managed service: provisioning, scaling, parameters, HA and failover, replicas, point-in-time restore, networking, and upgrades.

Security, permissions, and privacy

Installing the plugin launches @microsoft/postgres-mcp locally through npx. The MCP server can access the database selected by the user and has the permissions of that PostgreSQL role. Query results and diagnostics returned by the server become part of the user's AI-assistant interaction.

Use a dedicated, least-privilege role and a read-only connection profile for exploration. The skills require explicit confirmation before database writes, CSV imports, graph writes, or Azure resource changes. They treat database content as untrusted data and minimize retrieval to what the task needs.

The bundled MCP configuration disables postgres-mcp telemetry and does not transmit database content to Microsoft for plugin telemetry. Package installation requires access to the npm registry, and Azure workflows may use the Azure CLI to communicate with Azure after the user authenticates. See the plugin setup and data-access disclosure before connecting a sensitive or production database.

Get started

Starting with nothing

You need an internet connection, an account for one supported AI agent, and Node.js LTS. Node.js includes npx, which runs the bundled PostgreSQL MCP server.

1. Install Node.js LTS, then open a new terminal and verify:

text node --version npx --version

1. Install one AI agent:

Claude Code — requires a Pro, Max, Team, Enterprise, or Console account. The free Claude plan does not include Claude Code.

Operating system	Installation command
Windows PowerShell	irm https://claude.ai/install.ps1 \| iex
macOS, Linux, or WSL	curl -fsSL https://claude.ai/install.sh \| bash

GitHub Copilot CLI — requires an active GitHub Copilot subscription. Organization administrators must allow Copilot CLI. The npm installation requires Node.js 22 or later; Windows also requires PowerShell 6 or later.

text npm install -g @github/copilot

Codex CLI — supports ChatGPT Plus, Pro, Business, Edu, or Enterprise, or an OpenAI API key.

Operating system	Installation command
Windows PowerShell	powershell -ExecutionPolicy ByPass -c "irm https://chatgpt.com/codex/install.ps1 \| iex"
macOS or Linux	curl -fsSL https://chatgpt.com/codex/install.sh \| sh

On Windows, Git for Windows is optional but recommended for Claude Code and Codex.

1. Start the agent and sign in:

Agent	Start command	First-time sign-in
Claude Code	claude	Follow the browser prompt.
GitHub Copilot CLI	copilot	Enter /login and follow the prompts.
Codex CLI	codex	Select Sign in with ChatGPT, or configure an API key.

Exit the agent after sign-in so you are back at your normal terminal prompt.

1. Install Postgres Skills from that terminal:

Agent	Installation commands
Claude Code	claude plugin marketplace add microsoft/postgres-skills claude plugin install postgres-skills@postgres-skills
GitHub Copilot CLI	copilot plugin marketplace add microsoft/postgres-skills copilot plugin install postgres-skills@postgres-skills
Codex CLI	codex plugin marketplace add microsoft/postgres-skills codex plugin install postgres-skills@postgres-skills

Already have an agent installed? Start here.

1. Start your agent again and connect safely. Ask:

text Help me connect postgres-skills to my PostgreSQL database using a read-only profile.

Have your database host, port, database name, and user name ready. Enter the password only in the hidden password prompt, never in chat. See the setup and security guide for details.

Install skills through skills.sh

To install the guidance without the full plugin, use the skills.sh CLI:

npx skills add microsoft/postgres-skills --full-depth

The CLI discovers both postgresql-best-practices and pg-graph and lets you choose which to install. To install one directly:

npx skills add microsoft/postgres-skills --skill postgresql-best-practices --full-depth
npx skills add microsoft/postgres-skills --skill pg-graph --full-depth

This installs the skills only. Use the plugin installation above when you also want the bundled postgres-mcp server and live database tools.

Real-world examples

PostgreSQL anywhere

"What tables do I have, and which are the biggest?" → Introspects your live schema and reports tables, row estimates, and sizes.

"Set up vector search for my product catalog" → Selects the right vector index for your environment and applies the extension and index after you confirm.

"My query went from 200ms to 8 seconds after deployment" → Analyzes the live execution plan and buffer usage to identify the regression.

"Add multi-tenant isolation to these tables" → Designs row-level security policies for your tenancy model and applies them after confirmation.

"Partition my 500M-row events table by month" → Designs the partition layout, checks pruning requirements, and applies the DDL with confirmation.

"Index my JSONB documents for containment queries" → Examines the query pattern and creates the appropriate GIN index.

Azure Database for PostgreSQL and HorizonDB

"Batch-embed 1 million rows without leaving the database" → Processes embeddings safely in batches with the azure_ai extension while respecting service limits.

"Provision a General Purpose server and scale it to 8 vCores" → Selects the right Azure resources and provisions or scales the server after confirmation.

"Change work_mem on my Azure server" → Clarifies server-wide vs per-database scope, validates the current configuration, and safely applies the parameter change.

"Set up zone-redundant HA and add a read replica" → Configures high availability and a read replica with the required topology and safety checks.

"Roll my database back to 2pm yesterday" → Validates the restore window and creates a point-in-time restored server after confirming the target time.

"I accidentally deleted my server yesterday — get it back" → Finds the delete event in the activity log and, after confirmation, revives the server from its retained backup within the 5-day window.

"I can't connect — 'SSL connection is required'" → Diagnoses firewall, private endpoint, VNet, DNS, and certificate configuration.

"Add passwordless Entra ID authentication to my app" → Generates the application connection code and managed-identity configuration.

"Upgrade my server from PG13 to PG16" → Validates extension and application compatibility and performs the required pre-check before the upgrade.

"Create an Azure HorizonDB cluster and enable pgvector" → Recognizes the HorizonDB environment and uses cluster and parameter-group guidance instead of applying Flexible Server workflows.

Graph workloads with Apache AGE

"Turn my documents into a knowledge graph" → Samples your data, proposes node labels, edge types, and properties for review, then safely MERGE-loads the approved graph into Apache AGE.

"Generate an ontology from my existing tables" → Reads your schema and foreign keys and proposes tables as node labels and relationships as edges for review before anything is built.

"Answer this by traversing my graph" → Introspects the graph schema, then generates and runs a validated openCypher query.

"Visualize my graph in VS Code" → Returns complete vertices and edges with display labels so VS Code renders an interactive node-edge graph.

"Why did the graph recommend this?" → Returns the reasoning path, provenance, and confidence bounded by the weakest supporting edge.

Sub-skill Catalog

PostgreSQL Foundational (11 sub-skills)

These sub-skills work with any PostgreSQL deployment — self-hosted, RDS, Cloud SQL, Azure, or local.

Sub-skill	Helps you with	What the agent learns that LLMs get wrong
postgresql-vector-search	pgvector setup, HNSW indexes, distance operators	Index type selection, recall tuning, operator/index mismatch
postgresql-genai-rag	RAG pipelines, hybrid search, chunking	RRF scoring, dimension mismatch, FTS + vector combination
postgresql-extensions	Extension install/upgrade, troubleshooting	shared_preload_libraries restart requirement, version compatibility
postgresql-advanced-indexing	B-tree, GIN, GiST, BRIN, partial indexes	Covering indexes, index-only scan prerequisites, deduplication (PG13+)
postgresql-jsonb-patterns	JSONB operators, indexing, query patterns	jsonb_path_query (PG12+), GIN trigram anti-patterns, containment vs existence
postgresql-table-partitioning	Range/list/hash partitioning	Partition pruning failures, default partition traps, PG14+ DETACH CONCURRENTLY
postgresql-row-level-security	Multi-tenant RLS policies	Policy stacking, leaky view anti-patterns, performance with 1000+ tenants
postgresql-full-text-search	tsvector, tsquery, ranking	Phrase search (PG9.6+), custom dictionaries, weighted ranking
postgresql-connection-management	Pooling, timeouts, connection lifecycle	work_mem multiplication in pools, prepared statement mode traps
postgresql-replication	Publications, subscriptions, CDC	Row filter (PG15+), conflict resolution, initial data sync strategies
postgresql-query-performance	EXPLAIN analysis, vacuum, statistics	JIT thresholds, parallel query pitfalls, vacuum/bloat tuning

Azure Database for PostgreSQL (12 sub-skills)

These sub-skills are gated by the connection capability check. They cover managed-service workflows, Azure AI integrations, and platform-specific safety guardrails.

Each Azure sub-skill also covers Azure HorizonDB (Preview) in an On Azure HorizonDB section. When your connection is a HorizonDB cluster (*.horizondb.azure.com), the agent routes to the HorizonDB-specific guidance — clusters, az horizondb, and parameter groups — instead of falling back to Flexible Server steps, and flags features not yet available on HorizonDB (VNet injection, built-in PgBouncer, index tuning, major-version upgrades, configurable backup retention, cross-region replicas). See Azure HorizonDB.

Sub-skill	Helps you with	What the agent learns that LLMs get wrong
azure-postgresql-vector-diskann	DiskANN for billion-scale vector search	lists vs m/ef_construction tuning, DiskANN is Azure-only
azure-postgresql-genai-patterns	In-database embeddings + RAG with azure_ai	Batch processing limits, rate limit handling, Path A vs B decision
azure-postgresql-azure-ai	azure_ai extension setup, AI functions	create() vs create_embeddings(), managed identity config
azure-postgresql-intelligent-tuning	Query Store, index recommendations	Query Store must be enabled first, pg_qs.query_capture_mode
azure-postgresql-entra-id-auth	Passwordless auth with Entra ID	Token refresh before 5-min expiry, managed identity setup
azure-postgresql-connection-pooling	Built-in PgBouncer configuration	Prepared statements break in transaction mode
azure-postgresql-ha-disaster-recovery	Zone-redundant HA, PITR, geo-replicas	Forced vs planned failover, PITR creates NEW server
azure-postgresql-restore-deleted-server	Recovering a deleted server, delete locks	5-day window, ReviveDropped via az rest (no az postgres flexible-server subcommand), same subscription/region, HorizonDB clusters unrecoverable
azure-postgresql-networking-ssl	Private endpoints, VNet, SSL	VNet chosen at creation (can't change), DigiCert G2 cert
azure-postgresql-provisioning	IaC, SKU selection, scaling	Burstable limits, storage can't shrink, IOPS scaling
azure-postgresql-extension-lifecycle	Extension allowlisting on Azure	azure.extensions param, azure_pg_admin role requirement
azure-postgresql-upgrades-maintenance	Major version upgrades, maintenance	MVU is one-way, no skip-version, --validate-only pre-check

Graph on PostgreSQL — Apache AGE (pg-graph, 10 sub-skills)

The pg-graph skill is the single home for graph work on PostgreSQL, covering the full lifecycle: derive an ontology from your data (with a human feedback loop), build the graph, and query it with openCypher, natural-language-to-Cypher, and graph-augmented retrieval. It uses only capabilities available today — agent-driven extraction over the MCP query tools (any PostgreSQL with AGE) or the azure_ai extension for in-database work at scale on Azure.

Sub-skill	Helps you with	What the agent learns that LLMs get wrong
ontology-derivation	Deriving a graph ontology from structured or unstructured data with a human feedback loop	Never finalize an ontology without user approval; agent-driven vs azure_ai modes; no unreleased ai.* primitives
extract-to-graph	Extracting, deduplicating, and MERGE-loading entities into AGE from a finalized ontology	Idempotent MERGE on stable business keys to avoid duplicate vertices across repeated extraction
context-dedup	Entity resolution and canonicalization of aliases	Blocking for scale, persistent canonical map, resolving via type + graph neighborhood + source snippet
opencypher-age-patterns	AGE setup, Cypher wrapping, MATCH/MERGE/CREATE, indexing vertices and edges	Cypher must be wrapped in ag_catalog.cypher(...) with a column definition list; search_path ordering
text-to-cypher	Turning a natural-language question into a validated openCypher query	Ground on schema first, agtype casting, no host bind parameters inside $$...$$; return full objects + disp_label for VS Code graph visualization
graph-schema-introspection	Discovering labels, edge types, and properties	Use the ag_label catalog so generated Cypher is grounded, not hallucinated
graph-augmented-rag	Retrieval combining vector similarity with graph traversal and reranking	Hybrid graph retrieval that goes beyond flat vector search
graph-explainability	Provenance, reasoning paths, and explainable recommendations	Confidence bounded by the weakest edge on the path; reproducible reasoning trace
azure-ai-semantic-search	Enabling and configuring azure_ai and generating embeddings for semantic search	Read current settings and ask for endpoint/key/deployment — never invent them
examples	End-to-end worked graph examples spanning schema, query, and results	Grounded in the wrapping and safety rules from the other pg-graph sub-skills

Contributing

See CONTRIBUTING.md for guidelines on adding or improving skills and sub-skills.

License

MIT

Trademarks

This project may contain trademarks or logos for projects, products, or services. Authorized use of Microsoft trademarks or logos is subject to and must follow Microsoft's Trademark & Brand Guidelines. Use of Microsoft trademarks or logos in modified versions of this project must not cause confusion or imply Microsoft sponsorship. Any use of third-party trademarks or logos are subject to those third-party's policies.