PostgreSQL笔记37：数据库性能瓶颈排查方法论与工具链

纲要

• 性能衡量指标体系
  • QPS（每秒查询数）与 TPS（每秒事务数）
  • 延迟（平均响应时间）
  • 饱和度（CPU、内存、磁盘 IO、网络 IO）
  • 错误数（应用报错、排队等待）
  • 缓存命中率（shared_buffers 命中率）
• 硬件指标基础
  • CPU 缓存与内存访问延迟
  • 存储设备（HDD vs SSD vs NVMe）
  • 网络带宽与延迟
• 操作系统层面排查工具
  • top / htop / perf（CPU 热点分析）
  • iostat / sar / blktrace（磁盘 IO 分析）
  • sar / iperf（网络分析）
  • /proc/meminfo 与 smem（内存分析）
  • 系统负载（load average）解读
• PostgreSQL 数据库层面排查
  • 核心统计视图：pg_stat_activity、pg_stat_user_tables、pg_stat_user_indexes
  • 慢查询日志：log_min_duration_statement
  • 执行计划自动记录：auto_explain
  • 语句级统计：pg_stat_statements
  • 缓存命中率计算：pg_statio_user_tables
• 第三方监控与工具
  • pgBadger（日志分析）
  • PgHero（监控仪表盘）
  • PGAWR / PGProfile / PGStateInfo（AWR 风格报告）
  • Prometheus + postgres_exporter + Grafana
  • PoWA（PostgreSQL Workload Analyzer）

性能衡量指标体系

数据库性能排查的第一步，是建立可量化的衡量标准。仅凭“感觉数据库变慢了”无法指导有效的排查工作，必须依赖明确的性能指标进行定位。

吞吐量：QPS 与 TPS

QPS（Queries Per Second）和 TPS（Transactions Per Second）是衡量数据库吞吐量的两个核心指标。

• QPS：数据库每秒执行的 SQL 语句数量（包括 SELECT、INSERT、UPDATE、DELETE 等）
• TPS：数据库每秒完成的事务数量（包含 COMMIT 和 ROLLBACK）

需要辩证看待这两个指标——一个简单的点查 SELECT 1 与一个关联 10 张表的复杂报表查询，其 QPS 数值不具备可比性。同样，一个仅包含单行 INSERT 的事务与一个包含大量 DML 操作的长事务，其 TPS 也无法直接比较。因此，QPS 和 TPS 更适合在同类负载下进行纵向对比，而非跨场景横向比较。

PostgreSQL 原生并未直接提供 QPS 和 TPS 的统计指标，但可以通过以下方式获取：

-- 通过 pg_stat_database 计算 TPS（需要两次采样做差值）
SELECT datname,
       xact_commit + xact_rollback AS total_xact
FROM pg_stat_database;

-- 通过 pg_stat_statements 统计 QPS
SELECT sum(calls) AS total_queries
FROM pg_stat_statements;

延迟

延迟即查询的平均响应时间。在不同业务场景下，对延迟的容忍度截然不同：

• OLTP 场景：对延迟高度敏感，面向用户的查询应低于 10 毫秒，超过 100-200 毫秒即应视为慢查询
• OLAP 场景：更关注吞吐量，对单个查询的延迟容忍度较高

判断一个查询是“快”还是“慢”，必须结合业务上下文，不能一刀切。例如，外卖下单场景要求毫秒级响应，而隔夜报表查询运行数分钟可能也是可接受的。

饱和度

饱和度反映系统资源是否已到达瓶颈：

• CPU 饱和度：通过 top 或 htop 观察，若长期接近 100%，说明 CPU 是瓶颈
• 内存饱和度：观察是否频繁使用 swap，是否存在大量内存回收
• 磁盘 IO 饱和度：观察 IOPS 和吞吐量是否达到硬件上限（如机械硬盘约 200MB/s 顺序读写，IOPS 约 200）
• 网络 IO 饱和度：观察网卡带宽是否打满（千兆网卡理论带宽约 100MB/s）

错误数

错误数是一个侧面指标。当数据库达到处理上限时，应用端会出现连接超时、查询排队、获取连接失败等现象。这些错误数量可以反映数据库当前的处理压力。

缓存命中率

缓存命中率是评估 shared_buffers 配置是否合理的关键指标。在理想的 OLTP 环境中，缓存命中率应达到 99% 以上。

-- 计算整个数据库的缓存命中率
SELECT
    sum(heap_blks_hit) / nullif(sum(heap_blks_hit + heap_blks_read), 0) AS cache_hit_ratio
FROM pg_statio_user_tables;

硬件指标基础

数据库最终运行在硬件之上，硬件能力直接决定了软件性能的上限。理解不同硬件组件的延迟量级，有助于快速定位瓶颈层级：

硬件组件	典型延迟	说明
CPU L1/L2 缓存	1 纳秒级别	最快
内存访问	100 纳秒级别	带宽数十至数百 GB/s
同机房网络	100 微秒级别	超过此值需排查网络层
NVMe SSD	微秒级别	高端 SSD 延迟仅数微秒
机械硬盘 HDD	毫秒级别	旋转延迟 + 寻道延迟

硬件技术的演进速度极快——当前单核可达 3.5GHz，双路服务器可达 512 核；NVMe SSD 的 4K 随机写 IOPS 可达 270 万，相较机械硬盘的数十 IOPS 提升了数个数量级。因此，在排查性能问题时，应首先确认硬件层面是否已成为瓶颈。

操作系统层面排查工具

数据库运行在操作系统之上，操作系统层面的异常会直接影响数据库性能。

CPU 分析：top / htop / perf

• top / htop：实时查看 CPU 使用率、进程列表、内存占用
• perf：抓取热点函数，定位 CPU 周期消耗在哪个内核模块（如锁管理、排序、哈希连接等）

磁盘 IO 分析：iostat / sar / blktrace

• iostat -x 1：查看磁盘利用率、await、svctm 等指标
• sar -d：历史 IO 统计
• blktrace：从驱动层到设备层的完整 IO 时间追踪，精确定位 IO 耗时

网络分析：sar / iperf

• sar -n DEV 1：实时查看网络流量、丢包情况
• iperf：测试 TCP/UDP 带宽，支持不同包大小和并发数
• 网络丢包排查需区分硬件层、驱动层、协议层

内存分析：/proc/meminfo / smem

• /proc/meminfo：查看详细内存信息，重点关注 CommitLimit 与 Committed_AS
  • CommitLimit：系统在不出 OOM 的前提下可分配的最大内存
  • Committed_AS：当前所有进程已申请的内存总量
  • 若 Committed_AS 超过 CommitLimit，系统存在 OOM 风险
• smem：查看进程的 USS（Unique Set Size，独占内存），比 top 中的 RES 更准确（RES 包含共享库）

系统负载解读

系统负载（load average）是衡量系统“温度”的综合指标，它同时考量了 CPU 和 IO 的等待队列。

将系统比作一座大桥：

• 负载为 1.0：桥面满载，车辆正常通行
• 负载为 1.7：桥面 100% 占用，另有 70% 的车辆在等待
• 负载为 2.0：等待车辆与桥面车辆数量相同
• 负载为 3.0：等待车辆是桥面车辆的两倍

经验法则：

• 负载 > 0.7：需要关注
• 负载 > 1.0：建议排查
• 负载 > 5.0：系统可能已极度卡顿

负载的趋势同样重要——持续下降说明峰值已过，持续上升说明系统正在进入阻塞状态。

PostgreSQL 数据库层面排查

核心统计视图

PostgreSQL 通过累积统计系统提供丰富的性能数据。

pg_stat_activity —— 当前活动会话与查询

-- 查看当前正在运行的查询及其状态
SELECT pid,
       usename,
       application_name,
       client_addr,
       state,
       now() - query_start AS duration,
       query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

pg_stat_user_tables —— 用户表访问统计

-- 查看表的扫描与更新情况
SELECT relname,
       seq_scan,
       seq_tup_read,
       idx_scan,
       idx_tup_fetch,
       n_tup_ins,
       n_tup_upd,
       n_tup_del
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

pg_stat_user_indexes —— 用户索引使用统计

-- 查找未使用的索引（idx_scan = 0）
SELECT schemaname,
       relname,
       indexrelname,
       idx_scan,
       idx_tup_read,
       idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

慢查询日志：log_min_duration_statement

通过配置 log_min_duration_statement 参数，可以记录所有执行时间超过指定阈值的 SQL 语句。

-- 记录所有超过 100 毫秒的查询
ALTER SYSTEM SET log_min_duration_statement = '100ms';

-- 记录所有超过 3 秒的查询
ALTER SYSTEM SET log_min_duration_statement = '3s';

-- 记录所有查询（调试用）
ALTER SYSTEM SET log_min_duration_statement = 0;

-- 禁用（默认值 -1）
ALTER SYSTEM SET log_min_duration_statement = -1;

配置后需执行 SELECT pg_reload_conf(); 使配置生效。

执行计划自动记录：auto_explain

auto_explain 模块可以自动记录慢查询的执行计划，无需手动执行 EXPLAIN。

启用方式（在 postgresql.conf 中）：

shared_preload_libraries = 'auto_explain'
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = true
auto_explain.log_buffers = true
auto_explain.log_timing = true

会话级启用：

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_analyze = true;

配置参数说明：

参数	说明	默认值
auto_explain.log_min_duration	记录执行计划的最小执行时间（毫秒），0 记录所有，-1 禁用	-1
auto_explain.log_analyze	是否输出 EXPLAIN ANALYZE 结果	off
auto_explain.log_buffers	是否输出缓冲区使用统计	off
auto_explain.log_timing	是否输出节点级计时信息	on
auto_explain.log_nested_statements	是否记录嵌套语句（函数内执行的查询）	off

注意：启用 auto_explain.log_analyze 会对所有语句进行节点级计时，即使未达到记录阈值也会产生性能开销。在生产环境中建议合理设置 log_min_duration 阈值，避免过大的性能影响。

语句级统计：pg_stat_statements

pg_stat_statements 是 PostgreSQL 最强大的语句级性能分析工具，提供所有 SQL 语句的计划和执行统计。

启用方式（在 postgresql.conf 中）：

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

然后执行：

CREATE EXTENSION pg_stat_statements;

核心查询：

-- 按总执行时间排序，找出最耗时的查询
SELECT queryid,
       query,
       calls,
       total_exec_time,
       mean_exec_time,
       min_exec_time,
       max_exec_time,
       rows,
       shared_blks_hit,
       shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 按平均执行时间排序
SELECT queryid,
       query,
       calls,
       mean_exec_time
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC
LIMIT 20;

-- 重置统计信息
SELECT pg_stat_statements_reset();

pg_stat_statements 视图的核心字段：

字段	类型	说明
userid	oid	执行语句的用户 OID
dbid	oid	执行语句的数据库 OID
queryid	bigint	查询的内部哈希码
query	text	查询文本
calls	bigint	执行次数
total_exec_time	double precision	总执行时间（毫秒）
mean_exec_time	double precision	平均执行时间（毫秒）
rows	bigint	检索或影响的总行数
shared_blks_hit	bigint	共享块缓存命中总数
shared_blks_read	bigint	共享块读取总数

第三方监控与工具

日志分析：pgBadger

pgBadger 是一个高性能的 PostgreSQL 日志分析工具，可生成 HTML 格式的详细报告，包含慢查询分布、执行计划、锁等待等维度的分析。

监控仪表盘：PgHero

PgHero 提供直观的 Web 界面，展示数据库性能指标、慢查询、未使用索引、连接状态等。

AWR 风格报告：PGAWR / PGProfile / PGStateInfo

PGStateInfo 结合了 pg_stat_statements 和 pg_stat_kcache 插件，生成类似 Oracle AWR 格式的性能报告，是 DBA 进行深度性能分析的有力工具。

Prometheus + postgres_exporter + Grafana

这是目前最流行的开源监控方案：

• postgres_exporter：采集 PostgreSQL 性能指标并暴露给 Prometheus
• Prometheus：时序数据库，存储指标数据
• Grafana：可视化仪表盘

PoWA（PostgreSQL Workload Analyzer）

PoWA 是官方推荐的性能分析工具，兼容所有受支持的 PostgreSQL 版本，支持从多个实例采集和聚合指标，提供实时图表。

API 速览

pg_stat_statements

所属库：pg_stat_statements 扩展

核心视图：pg_stat_statements

核心函数：

-- 重置所有统计信息
pg_stat_statements_reset() RETURNS void

-- 重置指定查询的统计信息
pg_stat_statements_reset(userid OID, dbid OID, queryid bigint) RETURNS void

使用示例：

-- 查看最耗时的 10 条查询
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

auto_explain

所属库：auto_explain 预加载模块

配置参数（postgresql.conf）：

auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = true
auto_explain.log_buffers = true
auto_explain.log_timing = true
auto_explain.log_nested_statements = false
auto_explain.log_triggers = false
auto_explain.log_verbose = false
auto_explain.log_format = 'text'
auto_explain.log_level = 'LOG'

会话级使用：

LOAD 'auto_explain';
SET auto_explain.log_min_duration = '500ms';
SET auto_explain.log_analyze = true;

缓存命中率计算

涉及视图：pg_statio_user_tables

-- 全库缓存命中率
SELECT
    sum(heap_blks_hit) / nullif(sum(heap_blks_hit + heap_blks_read), 0) AS hit_ratio
FROM pg_statio_user_tables;

-- 按表查看缓存命中率
SELECT
    schemaname,
    relname,
    heap_blks_hit,
    heap_blks_read,
    CASE
        WHEN heap_blks_hit + heap_blks_read = 0 THEN NULL
        ELSE heap_blks_hit * 100.0 / (heap_blks_hit + heap_blks_read)
    END AS hit_ratio_percent
FROM pg_statio_user_tables
WHERE heap_blks_hit + heap_blks_read > 0
ORDER BY hit_ratio_percent ASC
LIMIT 20;

Demo 简单示例

以下是一个使用 Node.js + pg 驱动构建的 PostgreSQL 性能监控 Demo，演示了如何通过系统视图采集关键性能指标。

postgres-performance-demo/
├── package.json
├── index.js
└── .env

运行说明：

1. 安装依赖：npm install pg dotenv
2. 配置 .env 文件中的数据库连接信息
3. 运行：node index.js

package.json：

{
  "name": "postgres-performance-demo",
  "version": "1.0.0",
  "description": "PostgreSQL 性能监控演示",
  "main": "index.js",
  "scripts": {
    "start": "node index.js"
  },
  "dependencies": {
    "pg": "^8.11.0",
    "dotenv": "^16.0.3"
  }
}

index.js：

const { Pool } = require('pg');
require('dotenv').config();

const pool = new Pool({
    host: process.env.PGHOST || 'localhost',
    port: parseInt(process.env.PGPORT || '5432'),
    database: process.env.PGDATABASE || 'postgres',
    user: process.env.PGUSER || 'postgres',
    password: process.env.PGPASSWORD || '',
});

async function collectMetrics() {
    const client = await pool.connect();
    try {
        console.log('=== PostgreSQL 性能指标采集 ===\n');

        // 1. 缓存命中率
        const hitResult = await client.query(`
            SELECT
                sum(heap_blks_hit) / nullif(sum(heap_blks_hit + heap_blks_read), 0) AS hit_ratio
            FROM pg_statio_user_tables
        `);
        console.log(`缓存命中率: ${(hitResult.rows[0]?.hit_ratio * 100 || 0).toFixed(2)}%`);

        // 2. Top 5 最耗时查询
        const slowResult = await client.query(`
            SELECT
                query,
                calls,
                total_exec_time,
                mean_exec_time,
                rows
            FROM pg_stat_statements
            ORDER BY total_exec_time DESC
            LIMIT 5
        `);
        console.log('\n--- Top 5 最耗时查询 ---');
        slowResult.rows.forEach((row, i) => {
            console.log(`${i + 1}. 调用次数: ${row.calls}, 总耗时: ${row.total_exec_time}ms, 平均: ${row.mean_exec_time}ms`);
            console.log(`   SQL: ${row.query.substring(0, 100)}...`);
        });

        // 3. 当前活动查询
        const activeResult = await client.query(`
            SELECT
                pid,
                usename,
                state,
                now() - query_start AS duration,
                query
            FROM pg_stat_activity
            WHERE state = 'active'
            ORDER BY query_start
        `);
        console.log(`\n当前活跃查询数: ${activeResult.rowCount}`);
        activeResult.rows.forEach(row => {
            console.log(`  PID: ${row.pid}, 用户: ${row.usename}, 执行时长: ${row.duration}`);
        });

        // 4. 数据库级统计
        const dbResult = await client.query(`
            SELECT
                datname,
                xact_commit,
                xact_rollback,
                blks_hit,
                blks_read
            FROM pg_stat_database
            WHERE datname NOT IN ('template0', 'template1')
        `);
        console.log('\n--- 数据库统计 ---');
        dbResult.rows.forEach(row => {
            const hitRatio = row.blks_hit + row.blks_read > 0
                ? (row.blks_hit * 100 / (row.blks_hit + row.blks_read)).toFixed(2)
                : 0;
            console.log(`${row.datname}: 提交事务 ${row.xact_commit}, 回滚事务 ${row.xact_rollback}, 缓存命中 ${hitRatio}%`);
        });

    } catch (err) {
        console.error('采集指标失败:', err.message);
    } finally {
        client.release();
        await pool.end();
    }
}

collectMetrics();

对应的 PostgreSQL 原生指令注释：

-- 缓存命中率（对应上述 hitResult 查询）
SELECT
    sum(heap_blks_hit) / nullif(sum(heap_blks_hit + heap_blks_read), 0) AS hit_ratio
FROM pg_statio_user_tables;

-- Top 5 最耗时查询（对应 slowResult 查询）
SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

-- 当前活跃查询（对应 activeResult 查询）
SELECT
    pid,
    usename,
    state,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

-- 数据库级统计（对应 dbResult 查询）
SELECT
    datname,
    xact_commit,
    xact_rollback,
    blks_hit,
    blks_read
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1');

技术点总结：

• pg 驱动的连接池管理（Pool）
• 通过 pg_stat_statio_user_tables 计算缓存命中率
• 通过 pg_stat_statements 定位 Top N 慢查询
• 通过 pg_stat_activity 监控当前活跃查询
• 通过 pg_stat_database 获取数据库级事务与 IO 统计

多语言示例

以下基于前述 Node.js 示例，提供 Go、Python、Java 三种语言的完整实现，均使用对应生态中最主流的 PostgreSQL 驱动，并保持相同的功能：采集缓存命中率、Top 5 慢查询、当前活跃查询及数据库级统计。

Go 示例

使用 pgx 作为驱动（支持连接池和 pgxpool）。

项目结构：

go-postgres-demo/
├── go.mod
├── go.sum
└── main.go

go.mod：

module go-postgres-demo

go 1.21

require github.com/jackc/pgx/v5 v5.5.0

main.go：

package main

import (
    "context"
    "fmt"
    "log"
    "os"
    "time"

    "github.com/jackc/pgx/v5/pgxpool"
)

func main() {
    connStr := os.Getenv("PG_URL")
    if connStr == "" {
        connStr = "postgres://postgres:password@localhost:5432/postgres"
    }

    config, err := pgxpool.ParseConfig(connStr)
    if err != nil {
        log.Fatal("解析连接字符串失败:", err)
    }
    config.MaxConns = 10
    config.MinConns = 2

    pool, err := pgxpool.NewWithConfig(context.Background(), config)
    if err != nil {
        log.Fatal("连接池创建失败:", err)
    }
    defer pool.Close()

    ctx := context.Background()

    fmt.Println("=== PostgreSQL 性能指标采集 (Go) ===\n")

    // 1. 缓存命中率
    var hitRatio float64
    err = pool.QueryRow(ctx, `
        SELECT COALESCE(
            sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit + heap_blks_read), 0),
            0
        ) FROM pg_statio_user_tables
    `).Scan(&hitRatio)
    if err != nil {
        log.Println("缓存命中率查询失败:", err)
    } else {
        fmt.Printf("缓存命中率: %.2f%%\n", hitRatio*100)
    }

    // 2. Top 5 最耗时查询（依赖 pg_stat_statements）
    type SlowQuery struct {
        Query         string
        Calls         int64
        TotalExecTime float64
        MeanExecTime  float64
        Rows          int64
    }
    rows, err := pool.Query(ctx, `
        SELECT query, calls, total_exec_time, mean_exec_time, rows
        FROM pg_stat_statements
        ORDER BY total_exec_time DESC
        LIMIT 5
    `)
    if err != nil {
        log.Println("慢查询查询失败:", err)
    } else {
        var slowQueries []SlowQuery
        for rows.Next() {
            var sq SlowQuery
            if err := rows.Scan(&sq.Query, &sq.Calls, &sq.TotalExecTime, &sq.MeanExecTime, &sq.Rows); err != nil {
                log.Println("扫描慢查询失败:", err)
                continue
            }
            slowQueries = append(slowQueries, sq)
        }
        rows.Close()
        fmt.Println("\n--- Top 5 最耗时查询 ---")
        for i, sq := range slowQueries {
            fmt.Printf("%d. 调用次数: %d, 总耗时: %.2fms, 平均: %.2fms\n", i+1, sq.Calls, sq.TotalExecTime, sq.MeanExecTime)
            if len(sq.Query) > 100 {
                fmt.Printf("   SQL: %s...\n", sq.Query[:100])
            } else {
                fmt.Printf("   SQL: %s\n", sq.Query)
            }
        }
    }

    // 3. 当前活跃查询
    type ActiveQuery struct {
        Pid      int
        Username string
        State    string
        Duration time.Duration
        Query    string
    }
    rows2, err := pool.Query(ctx, `
        SELECT pid, usename, state, now() - query_start AS duration, query
        FROM pg_stat_activity
        WHERE state = 'active'
        ORDER BY query_start
    `)
    if err != nil {
        log.Println("活跃查询查询失败:", err)
    } else {
        var activeQueries []ActiveQuery
        for rows2.Next() {
            var aq ActiveQuery
            var duration float64 // 秒
            if err := rows2.Scan(&aq.Pid, &aq.Username, &aq.State, &duration, &aq.Query); err != nil {
                log.Println("扫描活跃查询失败:", err)
                continue
            }
            aq.Duration = time.Duration(duration * float64(time.Second))
            activeQueries = append(activeQueries, aq)
        }
        rows2.Close()
        fmt.Printf("\n当前活跃查询数: %d\n", len(activeQueries))
        for _, aq := range activeQueries {
            fmt.Printf("  PID: %d, 用户: %s, 执行时长: %v\n", aq.Pid, aq.Username, aq.Duration)
        }
    }

    // 4. 数据库级统计
    type DBStat struct {
        Datname      string
        XactCommit   int64
        XactRollback int64
        BlksHit      int64
        BlksRead     int64
    }
    rows3, err := pool.Query(ctx, `
        SELECT datname, xact_commit, xact_rollback, blks_hit, blks_read
        FROM pg_stat_database
        WHERE datname NOT IN ('template0', 'template1')
    `)
    if err != nil {
        log.Println("数据库统计查询失败:", err)
    } else {
        var dbStats []DBStat
        for rows3.Next() {
            var ds DBStat
            if err := rows3.Scan(&ds.Datname, &ds.XactCommit, &ds.XactRollback, &ds.BlksHit, &ds.BlksRead); err != nil {
                log.Println("扫描数据库统计失败:", err)
                continue
            }
            dbStats = append(dbStats, ds)
        }
        rows3.Close()
        fmt.Println("\n--- 数据库统计 ---")
        for _, ds := range dbStats {
            hitRatioDB := 0.0
            if ds.BlksHit+ds.BlksRead > 0 {
                hitRatioDB = float64(ds.BlksHit) / float64(ds.BlksHit+ds.BlksRead) * 100
            }
            fmt.Printf("%s: 提交事务 %d, 回滚事务 %d, 缓存命中 %.2f%%\n",
                ds.Datname, ds.XactCommit, ds.XactRollback, hitRatioDB)
        }
    }
}

对应的 PostgreSQL 原生 SQL（与 Node.js 示例相同，此处不重复）。

运行说明：

• 设置环境变量 PG_URL 或修改代码中的连接字符串
• 执行 go mod tidy 下载依赖
• 运行 go run main.go

Python 示例

使用 psycopg2（同步驱动）配合连接池 SimpleConnectionPool。

项目结构：

python-postgres-demo/
├── requirements.txt
└── main.py

requirements.txt：

psycopg2-binary==2.9.9
python-dotenv==1.0.0

main.py：

import os
import time
from psycopg2 import pool
from dotenv import load_dotenv

load_dotenv()

PG_URL = os.getenv("PG_URL", "postgresql://postgres:password@localhost:5432/postgres")

# 解析连接参数（简单处理）
import urllib.parse
result = urllib.parse.urlparse(PG_URL)
dbname = result.path[1:]
user = result.username
password = result.password
host = result.hostname
port = result.port or 5432

connection_pool = pool.SimpleConnectionPool(
    minconn=2,
    maxconn=10,
    dbname=dbname,
    user=user,
    password=password,
    host=host,
    port=port
)

def collect_metrics():
    conn = connection_pool.getconn()
    try:
        cur = conn.cursor()
        print("=== PostgreSQL 性能指标采集 (Python) ===\n")

        # 1. 缓存命中率
        cur.execute("""
            SELECT COALESCE(
                sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit + heap_blks_read), 0),
                0
            ) FROM pg_statio_user_tables
        """)
        hit_ratio = cur.fetchone()[0]
        print(f"缓存命中率: {hit_ratio * 100:.2f}%")

        # 2. Top 5 最耗时查询
        cur.execute("""
            SELECT query, calls, total_exec_time, mean_exec_time, rows
            FROM pg_stat_statements
            ORDER BY total_exec_time DESC
            LIMIT 5
        """)
        slow_queries = cur.fetchall()
        print("\n--- Top 5 最耗时查询 ---")
        for i, row in enumerate(slow_queries):
            query, calls, total_time, mean_time, rows = row
            print(f"{i+1}. 调用次数: {calls}, 总耗时: {total_time:.2f}ms, 平均: {mean_time:.2f}ms")
            if len(query) > 100:
                print(f"   SQL: {query[:100]}...")
            else:
                print(f"   SQL: {query}")

        # 3. 当前活跃查询
        cur.execute("""
            SELECT pid, usename, state, EXTRACT(EPOCH FROM (now() - query_start)) AS duration_sec, query
            FROM pg_stat_activity
            WHERE state = 'active'
            ORDER BY query_start
        """)
        active_queries = cur.fetchall()
        print(f"\n当前活跃查询数: {len(active_queries)}")
        for pid, usename, state, duration_sec, query in active_queries:
            print(f"  PID: {pid}, 用户: {usename}, 执行时长: {duration_sec:.2f}s")

        # 4. 数据库级统计
        cur.execute("""
            SELECT datname, xact_commit, xact_rollback, blks_hit, blks_read
            FROM pg_stat_database
            WHERE datname NOT IN ('template0', 'template1')
        """)
        db_stats = cur.fetchall()
        print("\n--- 数据库统计 ---")
        for datname, xact_commit, xact_rollback, blks_hit, blks_read in db_stats:
            hit_ratio_db = 0.0
            if blks_hit + blks_read > 0:
                hit_ratio_db = blks_hit / (blks_hit + blks_read) * 100
            print(f"{datname}: 提交事务 {xact_commit}, 回滚事务 {xact_rollback}, 缓存命中 {hit_ratio_db:.2f}%")

    finally:
        connection_pool.putconn(conn)

if __name__ == "__main__":
    collect_metrics()

运行说明：

• 安装依赖：pip install -r requirements.txt
• 设置环境变量 PG_URL 或修改默认值
• 运行 python main.py

Java 示例

使用 HikariCP 连接池 + PostgreSQL JDBC 驱动。

项目结构：

java-postgres-demo/
├── pom.xml
└── src/main/java/com/example/Main.java

pom.xml：

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
         http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.example</groupId>
    <artifactId>java-postgres-demo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <properties>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.6.0</version>
        </dependency>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>5.0.1</version>
        </dependency>
    </dependencies>
</project>

Main.java：

package com.example;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Main {
    public static void main(String[] args) {
        String pgUrl = System.getenv("PG_URL");
        if (pgUrl == null) {
            pgUrl = "jdbc:postgresql://localhost:5432/postgres?user=postgres&password=password";
        }

        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(pgUrl);
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(2);

        try (HikariDataSource dataSource = new HikariDataSource(config)) {
            System.out.println("=== PostgreSQL 性能指标采集 (Java) ===\n");

            // 1. 缓存命中率
            try (Connection conn = dataSource.getConnection();
                 Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(
                     "SELECT COALESCE(sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit + heap_blks_read), 0), 0) " +
                     "FROM pg_statio_user_tables")) {
                if (rs.next()) {
                    double hitRatio = rs.getDouble(1);
                    System.out.printf("缓存命中率: %.2f%%\n", hitRatio * 100);
                }
            } catch (SQLException e) {
                System.err.println("缓存命中率查询失败: " + e.getMessage());
            }

            // 2. Top 5 最耗时查询
            try (Connection conn = dataSource.getConnection();
                 Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(
                     "SELECT query, calls, total_exec_time, mean_exec_time, rows " +
                     "FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5")) {
                System.out.println("\n--- Top 5 最耗时查询 ---");
                int i = 1;
                while (rs.next()) {
                    String query = rs.getString("query");
                    long calls = rs.getLong("calls");
                    double totalTime = rs.getDouble("total_exec_time");
                    double meanTime = rs.getDouble("mean_exec_time");
                    long rows = rs.getLong("rows");
                    System.out.printf("%d. 调用次数: %d, 总耗时: %.2fms, 平均: %.2fms\n", i++, calls, totalTime, meanTime);
                    String shortQuery = query.length() > 100 ? query.substring(0, 100) + "..." : query;
                    System.out.println("   SQL: " + shortQuery);
                }
            } catch (SQLException e) {
                System.err.println("慢查询查询失败: " + e.getMessage());
            }

            // 3. 当前活跃查询
            try (Connection conn = dataSource.getConnection();
                 Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(
                     "SELECT pid, usename, state, EXTRACT(EPOCH FROM (now() - query_start)) AS duration_sec, query " +
                     "FROM pg_stat_activity WHERE state = 'active' ORDER BY query_start")) {
                List<ActiveQuery> activeQueries = new ArrayList<>();
                while (rs.next()) {
                    ActiveQuery aq = new ActiveQuery();
                    aq.pid = rs.getInt("pid");
                    aq.username = rs.getString("usename");
                    aq.state = rs.getString("state");
                    aq.durationSec = rs.getDouble("duration_sec");
                    aq.query = rs.getString("query");
                    activeQueries.add(aq);
                }
                System.out.printf("\n当前活跃查询数: %d\n", activeQueries.size());
                for (ActiveQuery aq : activeQueries) {
                    System.out.printf("  PID: %d, 用户: %s, 执行时长: %.2fs\n", aq.pid, aq.username, aq.durationSec);
                }
            } catch (SQLException e) {
                System.err.println("活跃查询查询失败: " + e.getMessage());
            }

            // 4. 数据库级统计
            try (Connection conn = dataSource.getConnection();
                 Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(
                     "SELECT datname, xact_commit, xact_rollback, blks_hit, blks_read " +
                     "FROM pg_stat_database WHERE datname NOT IN ('template0', 'template1')")) {
                System.out.println("\n--- 数据库统计 ---");
                while (rs.next()) {
                    String datname = rs.getString("datname");
                    long xactCommit = rs.getLong("xact_commit");
                    long xactRollback = rs.getLong("xact_rollback");
                    long blksHit = rs.getLong("blks_hit");
                    long blksRead = rs.getLong("blks_read");
                    double hitRatioDb = 0.0;
                    if (blksHit + blksRead > 0) {
                        hitRatioDb = (double) blksHit / (blksHit + blksRead) * 100;
                    }
                    System.out.printf("%s: 提交事务 %d, 回滚事务 %d, 缓存命中 %.2f%%\n",
                            datname, xactCommit, xactRollback, hitRatioDb);
                }
            } catch (SQLException e) {
                System.err.println("数据库统计查询失败: " + e.getMessage());
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    static class ActiveQuery {
        int pid;
        String username;
        String state;
        double durationSec;
        String query;
    }
}

运行说明：

• 使用 Maven 编译：mvn clean compile
• 运行：mvn exec:java -Dexec.mainClass="com.example.Main" 或直接运行编译后的 class（需设置 classpath）
• 设置环境变量 PG_URL（JDBC URL 格式）

多语言实现对比

维度	Node.js (原示例)	Go	Python	Java
驱动/库	pg (node-postgres)	pgx/v5	psycopg2	PostgreSQL JDBC + HikariCP
连接池	内置 Pool	pgxpool	SimpleConnectionPool	HikariCP
异步支持	原生 async/await	原生 goroutine + context	同步（可换 asyncpg 异步）	同步（可换 R2DBC 异步）
连接字符串格式	postgresql://user:pass@host/db	同左	postgresql:// 或独立参数	JDBC URL
依赖管理	npm	go.mod	pip / requirements.txt	Maven/Gradle
编译/运行	解释执行	编译为二进制	解释执行	编译为字节码
性能特点	事件驱动，适合 I/O 密集型	高并发，低内存占用	生态丰富，开发快速	成熟稳定，企业级应用广泛
错误处理	try-catch	显式 error 返回	try-except	try-catch
上下文传递	闭包	context.Context	无（同步）	无（同步）
适用场景	快速原型、前端全栈	高并发微服务	数据分析、脚本	大型企业应用

所有实现均使用相同的 SQL 查询和相同的 PostgreSQL 统计视图，因此结果具有一致性。开发者可根据团队技术栈选择合适的语言版本。

官方文档

• PostgreSQL 官方文档：监控数据库活动
• PostgreSQL 官方文档：pg_stat_statements
• PostgreSQL 官方文档：auto_explain
• PostgreSQL 官方文档：统计信息视图
• PostgreSQL 官方文档：运行时配置-日志

参考链接

• PostgreSQL Wiki：性能监控
• PgHero：PostgreSQL 性能仪表盘
• pgBadger：PostgreSQL 日志分析器
• PoWA：PostgreSQL Workload Analyzer
• postgres_exporter：Prometheus Exporter for PostgreSQL

总结

本文系统梳理了 PostgreSQL 性能瓶颈排查的完整方法论，涵盖从性能指标体系、硬件基础、操作系统工具到数据库层面监控的全链路技术栈：

1. 性能衡量：QPS/TPS、延迟、饱和度、错误数、缓存命中率五大维度构成了性能评估的基础框架
2. 硬件认知：从 CPU 缓存（纳秒）到机械硬盘（毫秒），理解不同硬件的延迟量级是定位瓶颈的前提
3. 操作系统工具：top/perf/iostat/sar/blktrace/smem/load average 构成系统层排查的完整工具链
4. PostgreSQL 内核监控：pg_stat_activity、pg_stat_user_tables、pg_stat_user_indexes 提供实时运行状态；log_min_duration_statement 实现慢查询日志；auto_explain 自动记录执行计划；pg_stat_statements 提供语句级精细统计
5. 第三方生态：pgBadger、PgHero、PGAWR、PoWA、Prometheus + postgres_exporter + Grafana 构成从日志分析到可视化监控的完整闭环

性能排查的核心思路是：先操作系统、后数据库；先全局指标、后局部细节。系统负载、CPU、内存、磁盘 IO、网络 IO 等操作系统层面的指标，往往是数据库性能问题的“因”，而数据库层面的慢查询、锁等待等则是“果”。只有厘清因果链条，才能精准定位瓶颈并采取有效的优化措施。