Microsoft PostgreSQL VS Code Extension: Copilot Access Modes, Query Plans and Production Safety

What the extension is and what it needs​

The extension is published by Microsoft as ms-ossdata.vscode-pgsql. Microsoft's documentation says it covers connecting to local, on-premises, and cloud PostgreSQL servers. It also covers IntelliSense query writing, object exploration, query-plan and schema visualization, and management of Azure Database for PostgreSQL flexible servers.

Installation is the usual VS Code routine:

1. Open the Extensions view with Ctrl+Shift+X (Cmd+Shift+X on macOS).
2. Search for "postgresql".
3. Pick PostgreSQL by Microsoft and select Install.
4. Look for the PostgreSQL icon that appears in the Activity Bar. That is where connections and objects live.

On Windows, the Marketplace listing says the extension supports x64 and Arm64. Arm64 requires Windows 11. Linux needs glibc 2.35 or later, and macOS needs version 13 or later.

Step 1: Connect and look around​

The post tells you to add a connection from the PostgreSQL view and save the profile so you can reuse it. Microsoft's documentation describes three ways to connect:

• Enter parameters such as host, port, database, and username.
• Paste a connection string.
• Browse your Azure subscriptions to find a server.

For Azure Database for PostgreSQL, the post says you can sign in with Microsoft Entra ID and then pick your subscription, server, and database. The Marketplace page confirms both password and Entra authentication for Azure servers. The documentation also mentions AWS IAM support for RDS and Aurora.

Once connected, you can expand servers, databases, and schemas. That shows tables, views, functions, sequences, types, extensions, and more. The docs also describe a built-in object search and a right-click option to script SELECT, CREATE, ALTER, DROP, or EXECUTE statements into the editor.

The post's advice is to start with one table your app actually uses. You don't need to understand the whole database on day one.

Step 2: Write SQL you can defend​

The post's example is a quarterly "top customers by revenue" query. It joins customer and payment data, filters by date, then groups and sorts. IntelliSense helps with syntax, table and column names, and join clauses. Copilot can help refine filters or analyze performance.

The post is careful about the AI part. It says aggregating payments before joining is "one possible rewrite to investigate." It is not a rule. It also tells you to compare the results of the original and revised queries, because a faster query that answers the wrong question is still wrong.

Here is how the pieces fit together in the documentation:

• AI Query Actions: right-click SQL in the editor for Explain Query, Rewrite Query, or Analyze Query Performance. The docs say the last one runs EXPLAIN ANALYZE against the connected database and sends the plan to the AI.
• Query plan visualizer: it shows EXPLAIN output in a tree view, an icicle view, a table view, and a source view. It reports per-node cost estimates, actual rows, execution time, and buffer usage.
• Copilot analysis: an "Analyze with Copilot" button gives an AI explanation of the plan.

One caution of my own, from general database practice and not from the post. EXPLAIN ANALYZE actually executes the statement. Be careful with it on queries that write data, and be careful with expensive queries on busy production systems.

Step 3: Understand a database you inherited​

This section suits anyone who has joined a project and found a schema with no documentation. The post suggests a sequence:

1. Browse Object Explorer for schema names, naming patterns, and tables or views that match your feature.
2. Open the schema visualizer to trace relationships. The documentation describes it as an interactive entity-relationship diagram. Tables are draggable nodes, and foreign keys appear as connecting edges.
3. Ask Copilot Chat a focused question. The post's example is a film-rental app: "Which tables contain actor and film data? How are they connected? Where is rental status stored?"
4. Treat the answer as a lead, not a verified map. Check the objects in the tree, confirm the relationships in the visualizer, and run a small read-only query.

The post's last step is to ask one discovery question and verify it before asking the next.

Which AI feature do you actually have?​

The post says "Copilot" as if it were a single feature. Microsoft's documentation describes three separate entry points, and they are not available everywhere:

The extension also registers an MCP server provider. It exposes the same tool surface to other MCP-compatible hosts.

In VS Code, the prerequisites are the extension, at least one configured connection, and GitHub Copilot or Copilot Chat signed in with an active subscription. The extension's AI integration is on by default through the pgsql.copilot.enable setting. Setting it to false hides all of the AI surfaces.

The setting to check before you let an agent loose​

The post tells you to review generated SQL and run read-only queries. The documentation gives you a setting that enforces that.

• pgsql.copilot.accessMode controls how much access the AI gets in Agent mode and MCP tools.
• ro (Read Only) allows SELECT, SHOW, EXPLAIN, and schema inspection.
• rw (Read/Write) adds data-modifying statements and DDL, with explicit confirmation.
• The default is rw.
• You can override the mode per connection in the connection dialog. The effective mode resolves from the per-connection value, then the global setting, then the default.

Microsoft's own caution is to use Read Only for production databases unless you intentionally want the AI to be able to change schema or data. In practice, I would set the global value to ro and loosen it only on throwaway local containers.

Another documented setting is pgsql.copilot.autoAttachQuery. It decides whether your SQL text goes along with a plan when you analyze it with AI. The default is to ask every time. Query text can contain sensitive literals, so that prompt is worth reading.

Step 4: Turn a performance problem into a reviewable decision​

The post's fourth section is about discipline more than tooling. Connection drops and rising latency tell you something is wrong, but not why. Before changing anything, it says to collect a chain of evidence:

• What changed.
• When it changed.
• Which workload is involved.
• Why a particular action should help.

Its example is repeated customer-history lookups on a large transactions table. That might lead to a composite-index recommendation. Do not simply create the index. Review the supporting metrics, the expected outcome, and the rollback guidance first. The example is illustrative. It is not a finding about any real system, and the post gives no measured speedup.

The documented tooling for this includes a server dashboard. For Azure-hosted servers, you can correlate query findings with CPU percentage, IOPS, and connection metrics. Microsoft's overview lists these dashboard tabs:

• Queries: top statements ranked by execution time or call count.
• Waits: wait events in a ranked table and an over-time chart.
• Sessions: active sessions, blocking trees, and lock activity.

Dashboard panels also have "Ask Copilot" buttons that start a chat with that context.

Database Agent: read the availability note​

The post says Database Agent "brings these pieces together" in the Azure PostgreSQL experience shown in VS Code. It then adds a note. The accompanying video introduces it as an upcoming experience, actions are applied manually, and automated remediation is discussed only as a future capability. The post tells readers to check current availability and Azure requirements.

I could not find documentation that settles Database Agent's release status or requirements. Don't assume you can use it today. What is documented today is Agent mode, which can connect, inspect schema, and run queries, with confirmation for writes.

Practical takeaways​

• Start read-only. Set pgsql.copilot.accessMode to ro for production connections.
• Verify every AI answer about schema against Object Explorer and the schema visualizer.
• Compare results as well as plans when you rewrite a query.
• Remember that @pgsql is VS Code only. Cursor users need Connect AI or the MCP route.
• Treat Database Agent as something to verify, not something to plan around.

The post comes from Microsoft, so it naturally presents its own tooling favorably. It does not claim performance gains. Its repeated advice to verify, compare, and plan a rollback is also sound practice with any database tool. The extension is a convenience that keeps database work next to your code. The judgment about what to run, and where, is still yours.