VS Code 调 PostgreSQL：连接、执行计划与锁等待排查

结论先行：VS Code 只是窗口，真正定位问题靠三件固定动作——带超时的只读连接串、看 rows 估算偏差的 EXPLAIN、还原阻塞链的 pg_blocking_pids。三者串起来，单次排查通常 5 分钟内出结论。

这套东西我用了一年多，覆盖过订单库的慢查询、一次 ALTER TABLE 引发的全站超时，也踩过在只读副本上误判执行计划的坑。下面写的都是能直接复制的配置和 SQL，版本以 PostgreSQL 17.6 为主，涉及 16.x / 18.x 的差异会单独点出。

为什么 VS Code 直连 PostgreSQL 之前要先把连接串固定下来？#

因为排查动作本身会制造故障。我在 2025 年遇到过一次：同事用 DBeaver 连生产主库跑了个没加条件的 COUNT，连接池 3 分钟内打满，应用侧 P99 从 40ms 涨到 9 秒。问题不在于他查了什么，而在于那条连接没有 statement_timeout、没有 application_name，在 pg_stat_activity 里显示成一串空白，谁都认不出是谁。

VS Code 侧的扩展选择不是重点。Microsoft 官方的 PostgreSQL 扩展（ms-ossdata.vscode-pgsql）能连库、跑查询、开 Notebook，也在往 Copilot 辅助写 SQL 的方向走，微软在 Microsoft Developer Blog 里介绍过这套“不离开编辑器”的流程。但扩展换哪个都行，配置得自己写。

对比表：三种连接入口的取舍。

我的做法是两边都留一份，命令行侧用 libpq 的服务名（service）文件，保证任何一台机器上 psql service=orders_ro 就是同一套参数。

规范模板：~/.pg_service.conf（Windows 放在 %APPDATA%\postgresql\.pg_service.conf，或用 PGSERVICEFILE 指定）。

[orders_ro]
host=db-replica.internal
port=5432
dbname=orders
user=app_ro
sslmode=verify-full
sslrootcert=/home/dev/.postgresql/root.crt
connect_timeout=5
application_name=vscode-readonly

• sslmode=verify-full：校验证书链和主机名。require 只加密不验证，中间人照样能截。
• connect_timeout=5：单位秒，DNS 卡住或安全组没放行时 5 秒就报错，不会干等 75 秒（libpq 默认行为）。
• application_name：出现在 pg_stat_activity.application_name，排查时一眼能认出“这条是 VS Code 开的”。

文件权限必须 chmod 600，否则 libpq 会警告；密码别写进去，用 ~/.pgpass 或 PGPASSFILE 指向独立文件。

可执行脚本：确认网络出口和数据库端口两件事要分开验证，否则你分不清是“库挂了”还是“整个办公网断了”。

# 探测 1：外部 HTTP 出口是否正常，用一个小响应体的接口做对照
curl -sS --max-time 5 --retry 2 --retry-delay 1 \
  -o /tmp/probe.json \
  -w 'http_code=%{http_code} dns=%{time_namelookup}s total=%{time_total}s\n' \
  'https://www.api.plus/api/get?apikey=YOUR_KEY&ip=8.8.8.8'

# 探测 2：数据库端口是否可达（不建立会话）
timeout 5 bash -c 'cat  /dev/tcp/db-replica.internal/5432' \
  && echo "5432 reachable" || echo "5432 blocked"

参数说明：--max-time 5 是整请求硬超时；--retry 2 --retry-delay 1 只对连接失败和 5xx 重试；-w 把耗时打出来，time_namelookup 偏高说明是 DNS 问题而不是网络丢包。

预期输出：http_code=200 dns=0.012s total=0.183s，紧接着一条 JSON，形如 {"code":200,"data":{"ip":"8.8.8.8","country":"美国",...}}，具体字段以 GET 示例接口 文档为准。如果返回的是 http_code=000，说明连接根本没建起来，这时候再去查数据库端口才有意义。需要换一个响应体更小的目标时，随机动漫壁纸接口（/api/dongman?type=json）也可以，返回只有几 KB。

注意：/dev/tcp 是 bash 特性，zsh 下不生效。Windows 用 Test-NetConnection db-replica.internal -Port 5432。

执行计划怎么看，才不会把慢查询误判成“索引没建”？#

先看估算行数与实际行数的倍差。这一步能解决我遇到的八成问题。倍差在 10 倍以内，计划基本可信，慢就是真慢；超过 10 倍，先怀疑统计信息，别急着加索引。

可执行脚本：在只读副本上用 psql 跑，全部参数都在会话内生效。

#!/usr/bin/env bash
# 示例仅用于逻辑验证，生产环境需补充日志、监控与降级
set -euo pipefail
export PGCONNECT_TIMEOUT=5
export PGAPPNAME=vs-code-explain

psql service=orders_ro -v ON_ERROR_STOP=1 \
  -c "SET statement_timeout = '15s';" \
  -c "SET default_transaction_read_only = on;" \
  -c "SET work_mem = '64MB';" \
  -c "EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
      SELECT id, user_id, amount, created_at
      FROM orders
      WHERE status = 'paid'
        AND created_at >= now() - interval '7 days'
      ORDER BY created_at DESC
      LIMIT 100;"

参数含义：ON_ERROR_STOP=1 让 psql 遇错立刻退出并返回非零码，CI 里能用；BUFFERS 输出共享缓存命中情况；VERBOSE 显示实际下发的列；SETTINGS 把影响计划的非默认参数（如 work_mem）一并打印，避免复现时对不上。SET work_mem 只在当前会话有效，别写进全局配置——它是每连接每排序节点分配的，200 个连接各给 64MB 就是 12GB 起步。

预期输出片段（真实形态）：

Limit  (cost=0.56..2214.08 rows=100 width=48) (actual time=0.091..9.412 rows=100 loops=1)
  Buffers: shared hit=214 read=1173
  ->  Index Scan Backward using orders_created_at_idx on public.orders
        (cost=0.56..98142.31 rows=4310 width=48) (actual time=0.089..9.371 rows=100 loops=1)
        Filter: ((status = 'paid'::text) AND (created_at >= (now() - '7 days'::interval)))
        Rows Removed by Filter: 41208
Planning Time: 0.312 ms
Execution Time: 9.487 ms

读法：rows=4310 是估算，actual ... rows=100 是实际返回。这条计划本身没问题——9.4ms，但为了拿 100 行扫掉了 41208 行。如果这个查询 QPS 高，正确做法是把 status 放进索引：(status, created_at DESC) 复合索引，让 Index Scan 直接命中，扫描量掉到 100 行左右。

再看另一类输出，这是真正的估算失真：

Sort  (cost=18842.10..18842.35 rows=100 width=64) (actual time=2104.881..2104.902 rows=100 loops=1)
  Sort Key: created_at DESC
  Sort Method: external merge  Disk: 118432kB
  Buffers: shared hit=102, read=98411
  ->  Bitmap Heap Scan on public.orders  (cost=1204.31..18839.02 rows=100 width=64)
        (actual time=18.204..1987.331 rows=84213 loops=1)
        Recheck Cond: (user_id = 8821337)
        Filter: (status = 'refunded'::text)
        Rows Removed by Filter: 12

• 估算 rows=100，实际 84213，差 842 倍。这不是索引问题，是统计信息问题。
• Sort Method: external merge Disk: 118432kB，说明 4MB 的 work_mem 完全不够，排序落盘。
• 耗时 2.1 秒里，1.9 秒花在 Sort 节点上，Bitmap Heap Scan 只占 18ms。

处理顺序：先 ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500; 再 ANALYZE orders;，估算会回到 12000 上下，计划往往自己就切成更合理的形态；然后才考虑会话级 work_mem。这两步做完同一条查询落到 380ms 左右，比盲目加索引有效得多。统计目标（Statistics Target）默认 100，倾斜明显的列调到 500 是常规操作，代价只是 ANALYZE 稍慢。

多列相关性强的场景（比如 city 和 province），ANALYZE 对单列的直方图无能为力，得上 CREATE STATISTICS 建扩展统计（Extended Statistics），这属于另一条线。

锁等待排查：从 pg_stat_activity 到 pg_blocking_pids 的三步法#

锁等待（Lock Wait）最坑的地方不是它本身，而是锁队列效应。一条 ALTER TABLE 在等 ACCESS EXCLUSIVE，后面来的所有 SELECT 都会排在它之后——即使 SELECT 之间完全不冲突。结果是单条 DDL 把一个库打成 502。

我处理过一次：P99 从 40ms 跳到 12s，应用侧连接池 60 个连接全卡住。根因是一个开发同事在 VS Code 里开了事务、UPDATE 了一行没提交，state 显示 idle in transaction，已经挂了 8 分 12 秒；另一个窗口执行 ALTER TABLE orders ADD COLUMN channel text; 排在他后面；再后面的查询全部排在 ALTER 后面。

编号步骤：

1. 先确认有没有锁等待在发生，看 wait_event_type = 'Lock' 的会话数。
2. 用 pg_blocking_pids() 拿到阻塞者的 pid 数组，逐个看它们的状态。
3. 区分“阻塞者在干活”和“阻塞者在挂机”：idle in transaction 且 state_change 很久以前，直接终止；active 则先看 SQL 内容再决定。

可执行脚本：一条 SQL 直接把阻塞链展开。

-- 示例仅用于逻辑验证，生产环境需补充日志、监控与降级
WITH blocked AS (
  SELECT a.pid,
         a.usename,
         a.application_name,
         a.state,
         pg_blocking_pids(a.pid)      AS blockers,
         now() - a.query_start        AS wait_time,
         a.wait_event_type,
         a.wait_event,
         a.query
  FROM pg_stat_activity a
  WHERE a.datname = current_database()
    AND cardinality(pg_blocking_pids(a.pid)) > 0
)
SELECT b.pid,
       b.usename,
       b.application_name,
       round(extract(epoch FROM b.wait_time)::numeric, 1) AS waited_s,
       b.blockers,
       left(b.query, 60) AS blocked_query,
       array_agg(DISTINCT left(k.query, 60)) AS blocker_queries
FROM blocked b
JOIN pg_stat_activity k ON k.pid = ANY (b.blockers)
GROUP BY b.pid, b.usename, b.application_name, b.wait_time, b.blockers, blocked_query
ORDER BY waited_s DESC
LIMIT 20;

参数与边界说明：datname = current_database() 过滤掉其他库的噪音；cardinality() 判断数组非空，比 array_length() > 0 更安全（空数组返回 0 而不是 NULL）；LIMIT 20 是保护，锁雪崩时可能有几百个等待者，全量拉回来会撑爆客户端。query_start 在 idle in transaction 状态下不更新，要看 state_change 才准。

预期输出：

 pid   | usename | application_name | waited_s | blockers |        blocked_query
-------+---------+------------------+----------+----------+-----------------------------
 41207 | app_rw  | psql             |     41.3 | {38912}  | ALTER TABLE orders ADD COLUMN...
 41255 | app     | orders-api       |     38.9 | {41207}  | SELECT id, amount FROM orders...

注意第二行：orders-api 的阻塞者是 41207，而 41207 自己也在等 38912。这就是间接阻塞，手工 join pg_locks 时很容易只看到第一层，然后去杀一个无辜的 SELECT。

正确处置顺序：

1. SELECT pg_cancel_backend(38912); 先礼后兵。cancel 只中断当前语句，事务还留着。
2. 取消无效或对方进程不响应，再 SELECT pg_terminate_backend(38912);。这会断连接，应用侧必须能重连。
3. 杀完后立刻看 ALTER 是否继续；如果 DDL 也要放弃，同样 cancel 掉 41207。

坑点：pg_terminate_backend 对 idle in transaction 的连接有效，但连接池（PgBouncer / 应用侧池）可能把它当成可重试错误立刻重建连接，如果业务代码没退避，会形成每秒几十次重连风暴。终止前先确认应用侧的重试策略。

预防：所有 DDL 模板前面强制加两行——

SET lock_timeout = '2s';            -- 等不到锁就报错，绝不无限排队
SET statement_timeout = '30s';

报 ERROR: canceling statement due to lock timeout 是好事，重试就是。同时给业务侧配 idle_in_transaction_session_timeout = '60s'，让挂机事务自己死掉。这个参数在 PostgreSQL 9.6+ 就有，但默认是 0（关闭），绝大多数库没开。

如何把这三件事串成日常动作？#

我的 VS Code 布局是固定三栏：左边 SQL 文件、中间执行计划结果、右边一个钉住的锁等待查询标签页。扩展负责看结果集，脚本负责可复现。

可执行脚本：用 Python 定时采集锁等待，对接告警。依赖 psycopg[binary]==3.2.3（pip install "psycopg[binary]==3.2.3"），Python 3.11+。

#!/usr/bin/env python3
# 示例仅用于逻辑验证，生产环境需补充日志、监控与降级
import os, time, json, sys
import psycopg

DSN = os.environ.get("PG_DSN", "")
if not DSN:
    sys.exit("PG_DSN 未设置")          # 边界保护：不静默连默认库

SQL = """
SELECT pid, application_name, state,
       round(extract(epoch FROM now() - state_change)::numeric, 1) AS state_age_s,
       cardinality(pg_blocking_pids(pid)) AS blocker_cnt,
       left(query, 100) AS query_head
FROM pg_stat_activity
WHERE datname = current_database()
  AND cardinality(pg_blocking_pids(pid)) > 0
ORDER BY state_age_s DESC
LIMIT 10
"""

def collect(retries: int = 3) -> list:
    for i in range(retries):
        try:
            with psycopg.connect(DSN, connect_timeout=3) as conn:
                conn.execute("SET statement_timeout = '3000ms'")
                conn.execute("SET default_transaction_read_only = on")
                return conn.execute(SQL).fetchall()
        except psycopg.OperationalError as e:      # 连接类错误才重试
            wait = 0.5 * (2 ** i)
            print(f"[warn] attempt={i+1} err={e} sleep={wait:.1f}s", file=sys.stderr)
            time.sleep(wait)
        except psycopg.Error as e:                 # 权限/SQL 错误不该重试
            print(f"[error] non-retryable: {e}", file=sys.stderr)
            return []
    return []

if __name__ == "__main__":
    t0 = time.monotonic()
    rows = collect()
    print(f"waiter_count={len(rows)} elapsed={time.monotonic()-t0:.2f}s")
    for r in rows:
        print(json.dumps(r, ensure_ascii=False, default=str))

几个刻意的设计：connect_timeout=3 比 psql 的 5 秒更紧，因为这是监控进程，不能拖；只对 OperationalError（连不上、超时）做指数退避重试，权限错误重试一万次也一样；default_transaction_read_only=on 是硬保险，防止未来有人往这条 SQL 里加写操作。实测单次采集 12~18ms，输出 waiter_count=0 elapsed=0.02s。

采集结果按 30 秒一次落时序库，就能画出锁等待随时间的变化曲线。原始 SQL 和踩坑记录我会整理成文发到 API 文章中心，比散在聊天记录里靠谱。

监控与告警清单#

告警表：阈值基于单实例 8C16G、连接数上限 200 的订单库，按自己环境等比调整。

pg_stat_statements 需要在 postgresql.conf 里 shared_preload_libraries = 'pg_stat_statements' 并重启，没开的话以上慢查询指标都拿不到。这是排查体系的前置条件，不是可选项。

风险清单#

风险表：每条都是我自己或同事踩过的。

总结#

这套流程的价值不在工具，而在于把“猜”变成“看”。连接串固定下来，你才知道谁在连；执行计划的 rows 倍差看一眼，能省掉一半加索引的冲动；pg_blocking_pids 展开阻塞链，比翻 pg_locks 快十倍。VS Code 在这里的角色是让你不用切窗口，仅此而已。

再补一句主观判断：绝大多数“数据库慢”的工单，最后落点都是三件事之一——统计信息过期、锁等待、连接池打满。执行计划只是把它们显影出来。

适用范围：

• PostgreSQL 14 及以上（pg_blocking_pids 自 9.6 可用，query_id 需 14+），单实例或主备架构。
• 团队已经有只读副本，且副本延迟可控在 10 秒内。
• 开发者具备 pg_monitor 或等价只读权限，能查 pg_stat_activity。

不适用范围：

• 分库分表中间件之后的逻辑库——pg_stat_activity 只反映单个物理实例，跨分片的锁等待看不到。
• Serverless 型托管库（连接经由代理、实例随时伸缩），application_name 与 pid 的对应关系不稳定，阻塞链可能断在代理层。
• 需要做容量规划的场景。本文只做定位，压测与容量模型是另一套方法。

本站实测数据（近 24 小时）#

以下数据来自 API.PLUS 线上调用日志（统计时间 2026-10-30 17:06，口径：仅统计已上线接口，不含扫描与无效路径），可作为容量规划与稳定性判断的现实参考。

• 接口调用总数：143 次；成功返回 141 次，鉴权/参数校验拦截 2 次
• 最活跃接口：随机动漫壁纸（97 次）、Menhera 动漫图片（41 次）、IP 地址查询（4 次）
• 近 7 天累计调用：899 次