postgresql-best-practices

PostgreSQL Agent Skills — Routing Table

Use references as supplemental context — combine them with your PostgreSQL knowledge. If reference guidance is incomplete, answer with appropriate caveats rather than inventing details.

Key Constraints

• Never use ALTER SYSTEM on managed services — use portal/CLI/ARM instead
• Never assume SUPERUSER — use azure_pg_admin (Azure) or equivalent managed role
• Use CONCURRENTLY for CREATE INDEX / REINDEX / DETACH PARTITION in production
• postgres_mcp_modify does NOT return row data (no RETURNING support)
• Destructive DDL confirmation: Before executing postgres_mcp_modify with DROP, TRUNCATE, DELETE (without WHERE), or ALTER TABLE ... DROP, always ask the user for explicit confirmation. List the affected objects and warn about data loss before proceeding.
• Escalation policy: on a blocked/denied path, state the blocker and ask — never silently switch method or target.
• Version-gated features: MERGE (PG 15+), json_table (PG 17+), DETACH CONCURRENTLY (PG 14+)

Managed Service Guardrails (Azure Flexible Server and Azure HorizonDB)

When the context is Azure Database for PostgreSQL (either flavor), NEVER suggest:

• File paths: pg_hba.conf, postgresql.conf, /var/lib/postgresql/ — not accessible. Never run SHOW config_file, SHOW hba_file, or SHOW data_directory (internal paths, irrelevant on managed services).
• OS/service commands, any platform: no OS-level access — systemctl, sudo, pg_ctl, initdb, Restart-Service, services.msc, brew services, docker restart are all invalid; use portal/az/ARM instead.
• ALTER SYSTEM SET — blocked on Azure. Use the control-plane parameter API instead (Flexible Server: az postgres flexible-server parameter set; HorizonDB: parameter groups / az horizondb) or the portal.
• ALTER DATABASE SET for server-wide parameters — permitted, but prefer the control-plane parameter API (az postgres flexible-server parameter set on Flexible Server; a parameter group connected to the cluster on HorizonDB) for changes like work_mem, shared_buffers, max_connections. Use ALTER DATABASE SET only for an explicit per-database override, and clarify scope first.
• Manual replication setup — use Azure read replicas (Flexible Server: az postgres flexible-server replica create; HorizonDB: add a read replica to the cluster)
• Manual backup/restore — do NOT suggest pg_dump/pg_basebackup as the primary strategy. Lead with Azure PITR (creates a new server/cluster); use pg_dump only for cross-platform migration or selective export.

Instead, always use Azure equivalents: portal, az CLI, ARM/Bicep, or server parameters API.

Flavor split: on Azure HorizonDB the same guardrails hold, but the control plane is az horizondb / a parameter group connected to the cluster / Microsoft.HorizonDB ARM (api-version 2026-01-20-preview) — never az postgres flexible-server. Each azure-* reference has an On Azure HorizonDB section with the deltas.

---

Shell Execution Policy (az CLI)

When guidance needs Azure CLI and shell access exists:

• Run once per session:

  az version
  az account show --query "{subscription:id, name:name, tenant:tenantId, user:user.name}" -o json
  

• If az account show fails, ask the user to run az login or az login --use-device-code. Do not run login automatically.
• Execute non-destructive az commands directly.
• NEVER execute destructive az CLI commands without confirmation. Before delete, restart, upgrade, failover, stop-replication, PITR restore, or Entra ID auth/admin/password-auth toggles, state the impact and ask "Proceed?"
• Always pass --subscription <id>.
• If target server or resource group is unknown, always discover before prompting the user:

  az postgres flexible-server list --query "[].{name:name, resourceGroup:resourceGroup, location:location, version:version}" -o table
  

  Use the discovered resourceGroup and name; ask the user only if multiple servers make the target ambiguous.
• If shell access is unavailable, provide numbered manual commands.

---

Connection Context Detection

On first activation:

1. If an MCP connection exists, call postgres_mcp_get_server_capabilities once and cache isAzure.
2. postgres-mcp unavailable → say so and stop; never guess credentials or connect to an unspecified/local database.
3. isAzure: true → all skills available; prefer azure-postgresql-* for overlapping topics.
4. isAzure: false → use only postgresql-* skills.
5. No connection + generic question → use postgresql-* skills.
6. No connection + explicit Azure question → answer conceptually with: "These steps require an active Azure PostgreSQL connection to execute." Apply all Azure guardrails (no ALTER SYSTEM, no file paths, no OS commands) even without isAzure confirmation — if the user says "Azure PostgreSQL", treat it as Azure.
7. Unknown state → attempt capability check only for clearly Azure-specific requests; otherwise default to generic PostgreSQL skills.
8. Azure flavor (Flexible Server vs HorizonDB) — when isAzure: true, read the connection host and cache azureFlavor: *.horizondb.azure.com → Azure HorizonDB (Preview); *.postgres.database.azure.com → Flexible Server. On HorizonDB, follow the On Azure HorizonDB section of the matching azure-* reference (HorizonDB control plane, not az postgres flexible-server). Several Flexible-Server-only features (built-in PgBouncer, VNet injection, geo/cross-region replicas, configurable backup retention, CMK, intelligent tuning, major-version upgrade) are not yet available on HorizonDB — say so instead of emitting Flexible Server steps.

---

PostgreSQL Skills (always available)

These skills apply to any PostgreSQL deployment — self-hosted, RDS, Cloud SQL, Azure, or local.

---

Azure PostgreSQL Skills (requires isAzure: true)

GATE: Only use these when postgres_mcp_get_server_capabilities confirms isAzure: true. For conceptual questions without a connection, provide informational answers with a disclaimer.

Azure quick-reference (when isAzure: true):

• Tier: SELECT current_setting('azure.server_tier', true);
• Allowlist: SHOW azure.extensions;
• az ... parameter set --value replaces the full list; fetch current values first.

---

Graph Workloads (Apache AGE)

For anything involving a property graph on PostgreSQL, route to the sibling pg-graph skill: Apache AGE, openCypher, ag_catalog, knowledge graphs, ontology, graph traversal, and natural language to Cypher. That skill owns AGE setup, the ag_catalog.cypher() wrapping contract, graph schema introspection, and retrieval that combines vectors with graph traversal. Keep AGE specific guidance there rather than duplicating it here.

---

Quick Decision Tree

• Vector or similarity → azure-postgresql-vector-diskann on Azure, otherwise postgresql-vector-search. When isAzure: true and the user asks about vector indexes without specifying an index type, recommend DiskANN as the preferred option alongside HNSW.
• RAG or GenAI → azure-postgresql-genai-patterns only for in-database azure_ai; otherwise postgresql-genai-rag
• Extensions → Azure uses azure-postgresql-extension-lifecycle; non-Azure uses postgresql-extensions
• Connection pooling → Azure built-in pooler uses azure-postgresql-connection-pooling; otherwise postgresql-connection-management
• Azure-only topics like Entra ID, provisioning, HA, networking, upgrades → route to matching azure-* skill only when isAzure: true
• Generic topics like indexing, JSONB, partitioning, RLS, FTS, replication → use postgresql-*
• Graph topics like Apache AGE, openCypher, ag_catalog, knowledge graphs, ontology, graph traversal → route to the pg-graph skill

---

Global Anti-Hallucination Policy

1. Verify Azure-specific claims with live checks like SHOW, pg_settings, or pg_available_extensions when possible.
2. State uncertainty explicitly instead of guessing.
3. Do not extrapolate beyond documented versions, tiers, or providers.
4. Treat generic skills as supplements, not scripts.