DBA 必备 SQL 与系统目录

DBA 除业务 SQL 外，需熟悉 系统视图、EXPLAIN 与 锁诊断。与 /roadmaps/postgresql-dba SQL 节点对齐。

学习目标

• 查询 pg_catalog / information_schema 获取元数据
• 使用 EXPLAIN (ANALYZE, BUFFERS) 读执行计划
• 诊断阻塞与长事务

核心概念

元数据查询

SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'app' AND table_name = 'cf_track';

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'app';

执行计划

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM app.cf_track WHERE slug = 'postgresql-dba-essentials';

关注：Seq Scan vs Index Scan、Buffers: shared hit/read、实际行数 vs 估计行数。

锁与长事务

SELECT pid, usename, state, wait_event_type,
       left(query, 80) AS query, now() - xact_start AS age
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY age DESC;

SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid
FROM pg_locks blocked
JOIN pg_locks blocking ON blocking.locktype = blocked.locktype
  AND blocking.database IS NOT DISTINCT FROM blocked.database
  AND blocking.relation IS NOT DISTINCT FROM blocked.relation
  AND blocking.page IS NOT DISTINCT FROM blocked.page
  AND blocking.tuple IS NOT DISTINCT FROM blocked.tuple
  AND blocking.virtualxid IS NOT DISTINCT FROM blocked.virtualxid
  AND blocking.transactionid IS NOT DISTINCT FROM blocked.transactionid
  AND blocking.classid IS NOT DISTINCT FROM blocked.classid
  AND blocking.objid IS NOT DISTINCT FROM blocked.objid
  AND blocking.objsubid IS NOT DISTINCT FROM blocked.objsubid
  AND blocked.granted = false AND blocking.granted = true;

必要时：SELECT pg_cancel_backend(pid); 或 pg_terminate_backend(pid);（慎用）。

与 MySQL/Spring Boot 对照

项	PostgreSQL	MySQL 8
执行计划	EXPLAIN ANALYZE	EXPLAIN ANALYZE
进程列表	pg_stat_activity	SHOW PROCESSLIST / performance_schema
锁	pg_locks	performance_schema.data_locks
ORM 慢 SQL	MyBatis 日志 + PG 计划	同，换数据源

Spring Boot 可开 logging.level.org.postgresql=DEBUG 调试 JDBC；生产用 pg_stat_statements 聚合更稳妥。

练习

• 对 slug 查询跑 EXPLAIN ANALYZE，记录 scan 类型
• 模拟长事务（BEGIN; SELECT pg_sleep(60);），在另一会话查 blocking
• 导出 app schema 下所有索引定义到文本文件

小结

系统目录 + EXPLAIN + 活动会话是 DBA 排障三件套。 下一课数据类型与存储取舍。