像专家一样用 PostgreSQL:不必离开 VS Code

你正在 VS Code 中构建一个应用。接着,你需要查看某张表、弄清两张表之间如何关联,或者查明某个查询为什么慢。
这往往意味着要打开另一个工具,然后再找回代码的位置。
VS Code 的 PostgreSQL 扩展把常见的数据库任务带入了你的编辑器。你可以浏览数据库对象、编写并运行 SQL,并在工作中结合 GitHub Copilot 使用数据库上下文。
以下四种做法可以把这一环节融入你的开发工作流,从找到你的第一张表,到查看性能建议。如果你想跟着操作,每个部分都附有一段来自 PostgreSQL Like a Pro: VS Code Edition 的视频。
在编写查询之前,你需要知道数据库里有什么。
安装 Microsoft 的 PostgreSQL 扩展,然后在活动栏中选择 PostgreSQL 图标以添加连接。
你可以连接到本地或云端数据库,并保存连接配置以便日后复用。对于 Azure 上的数据库,Azure 连接路径让你可以使用 Microsoft Entra ID 登录,并选择你的订阅、服务器和数据库。
连接完成后,展开服务器、数据库和架构,即可浏览表、视图、函数、序列和扩展。从你的应用使用的一张表开始。你不需要了解整个数据库,就能迈出下一步。
试试这个:保存连接配置文件,然后找到你的应用程序使用的一张表,并探索其结构。
你知道应用程序需要什么。现在你需要一个能返回正确数据的查询。
假设你需要找出某个季度哪些客户贡献了最多收入。这意味着要联接客户数据和支付数据,添加日期筛选条件,并对结果进行分组和排序。
查询编辑器的 IntelliSense 可帮助处理 SQL 语法、表名和列名以及联接表达式。一旦有了一个起始查询,你可以让 Copilot 帮助完善筛选条件,或利用查询计划和执行指标来分析其性能。
对于这样的查询,在联接之前先聚合支付数据是一种可以研究一下的可能改写方式,而不是一条到处都适用的规则。
既要检查结果,也要检查执行计划。更快的查询仍然需要回答正确的问题。在运行生成的 SQL 之前先审查它,并比较原始结果和修改后的结果。
试着问:“分析此查询的性能。执行计划告诉了我们什么,在更改它之前我应该检查什么?”
哪张表包含你需要的数据?它如何与应用程序的其余部分连接?
接手一个项目时,这些问题往往是最先要问的。
先在 Object Explorer 中做宽泛的浏览。寻找 schema 名称、命名模式,以及与你要开发的功能相匹配的表或视图。然后用 schema 可视化来追踪相关对象之间的连接关系。
借助 Copilot Chat 中可用的已连接数据库上下文,提出一个有针对性的问题。对于影片租赁应用,可能是这样:
把答案当作起点,而不是经过验证的地图。检查树中的对象,在可视化器中查看关系,并运行一个小的只读查询,确认你正沿着正确的数据路径前进。
可以试试这样做:先问一个探索性问题,然后对照数据库验证答案,再问下一个。
连接中断和延迟上升说明出了问题。要弄清原因,通常需要收集日志、对比指标并梳理时间线。
在做出任何改动之前,先寻找一条有用的证据链:发生了什么变化、何时变化、涉及哪类工作负载,以及为什么某个操作可能有帮助。
例如,在一张大事务表上反复查询客户历史记录,可能会引出一条复合索引建议。下一步并不是直接创建该索引。先查看支持性指标、预期结果和回滚指引。
Database Agent 在 VS Code 中展示的 Azure PostgreSQL 体验里将这些部分整合在一起。你还可以向 Copilot 寻求帮助,以理解某项建议,然后再决定怎么做。
可用性说明:随附视频将 Database Agent 介绍为一项即将推出的体验,操作需手动应用。请查看当前可用性和 Azure 要求;自动修复被讨论为未来的一项能力。
行动之前:你能解释为什么这项更改会有帮助、你将如何检查结果,以及如果不起作用你会怎么做吗?
哪项数据库任务最需要你像侦探一样排查:理解数据库模式、编写查询,还是调查性能?请在评论中告诉我们。
编码愉快!
博文《PostgreSQL Like A Pro:无需离开 VS Code 即可构建、查询和排查故障》最初发布于 Microsoft for Developers。
译文由英文原文机器翻译,术语按行业惯例统一,可在文末展开原文核对。
查看英文原文(PostgreSQL Like A Pro: Build, query, and troubleshoot without leaving VS Code)
You’re building an app in VS Code. Then you need to check a table, understand how two tables connect, or figure out why a query is slow.
That often means opening another tool and finding your way back to the code.
The PostgreSQL extension for VS Code brings common database tasks into your editor. You can explore database objects, write and run SQL, and use database context with GitHub Copilot as you work.
Here are four ways to make that part of your development workflow, from finding your first table to reviewing a performance recommendation. Each section includes a video from PostgreSQL Like a Pro: VS Code Edition if you’d like to follow along.
1. Get connected and find your way around 
Before writing a query, you need to know what’s in the database.
Install Microsoft’s PostgreSQL extension, then select the PostgreSQL icon in the Activity Bar to add a connection.
You can connect to on-premises or cloud databases and save connection profiles to reuse later. For a database on Azure, the Azure connection path lets you sign in with Microsoft Entra ID and select your subscription, server, and database.
Once connected, expand the server, database, and schemas to browse tables, views, functions, sequences, and extensions. Start with one table your application uses. You don’t need to understand the entire database to take the next step.
Try this: Save a connection profile, then find one table your application uses and explore its structure.
2. Write SQL you can explain 
You know what the application needs. Now you need a query that returns the right data.
Suppose you need to find which customers generated the most revenue in a quarter. That means joining customer and payment data, adding a date filter, and grouping and sorting the results.
The query editor’s IntelliSense helps with SQL syntax, table and column names, and join expressions. Once you have a starting query, you can ask Copilot to help refine a filter or analyze its performance using query plans and execution metrics.
For a query like this, aggregating payments before joining is one possible rewrite to investigate, not a rule to apply everywhere.
Check the results as well as the execution plan. A faster query still needs to answer the right question. Review generated SQL before running it, and compare the original and revised results.
Try asking: “Analyze this query’s performance. What does the execution plan tell us, and what should I check before changing it?”
3. Understand a database you didn’t build 
Which table contains the data you need? How does it connect to the rest of the application?
Those are often the first questions when you inherit a project.
Start broad in Object Explorer. Look for schema names, naming patterns, and tables or views that match the feature you’re working on. Then use schema visualization to trace how the relevant objects connect.
With the connected database context available in Copilot Chat, ask a focused question. For a film rental application, that might be:
- Which tables contain actor and film data?
- How are they connected?
- Where is rental status stored?
Treat the answer as a starting point, not a verified map. Check the objects in the tree, review the relationships in the visualizer, and run a small read-only query to confirm that you’re following the right data path.
Try this: Ask one discovery question, then verify the answer against the database before asking the next.
4. Turn a performance issue into a decision you can review 
Connection drops and rising latency tell you something is wrong. Figuring out why usually means gathering logs, comparing metrics, and building a timeline.
Before changing anything, look for a useful chain of evidence: what changed, when it changed, which workload is involved, and why a particular action might help.
For example, repeated customer-history lookups on a large transactions table might lead to a composite-index recommendation. The next step isn’t simply to create the index. Review the supporting metrics, expected outcome, and rollback guidance first.
Database Agent brings these pieces together in the Azure PostgreSQL experience shown in VS Code. You can also ask Copilot for help understanding a recommendation before deciding what to do.
Availability note: The accompanying video introduces Database Agent as an upcoming experience, with actions applied manually. Check current availability and Azure requirements; automated remediation is discussed as a future capability.
Before acting: Can you explain why the change should help, how you’ll check the result, and what you’ll do if it doesn’t?
Try it 
- Build with the PostgreSQL extension for VS Code: https://aka.ms/pg-pro-vs-code-repo
- Explore one table your application uses. Inspect its structure and relationships before writing a query.
- Write and review a read-only query. Check the tables, joins, and filters, then run it and confirm the results match your requirement.
Which database task takes the most detective work for you: understanding the schema, writing queries, or investigating performance? Let us know in the comments.
Happy coding! 
The post PostgreSQL Like A Pro: Build, query, and troubleshoot without leaving VS Code appeared first on Microsoft for Developers.
正文保留英文原文,标题与摘要由机器翻译。
原文发布于 Microsoft DevBlogs(PostgreSQL Like A Pro: Build, query, and troubleshoot without leaving VS Code),标题、摘要与正文由机器翻译,术语按行业惯例统一,未经人工逐句校订。
Try this: Save a connection profile, then find one table your application uses and explore its structure.
Availability note: The accompanying video introduces Database Agent as an upcoming experience, with actions applied manually. Check current availability and Azure requirements; automated remediation is discussed as a future capability.
Which database task takes the most detective work for you: understanding the schema, writing queries, or investigating performance? Let us know in the comments.