记录一些自己关注的知识点不会每条都记可能有部分补充原文请看 postgres-howto一、 查询性能与观测1. 建议用 EXPLAIN (ANALYZE, BUFFERS)可以看到SQL每步实际的IO次数乘以8K则为字节数pg 18开始buffer成为默认值2. pg_stat_statements速通用途top sql 宏观分析范围不包含在执行的语句、执行失败的语句指标核心为累积指标calls、total_time、rows 等部分为统计类指标stddev_plan_time、stddev_exec_time、min_exec_time完整指标https://www.postgresql.org/docs/current/pgstatstatements.html怎么用取快照看两个快照间的差异数据。如果真的分析DB重启至今的数据可以直接看如今AI已经非常强大其实更重要的是知道要看什么给它指标名想看的数据我们负责拿分析结果3. 临时收集会话数据每秒收集一次pg_stat_activity样本并无限次地记录日志 (直到手动中断)导出的csv文件原文是导回pg分析现在更好的方式直接喂给AI获取目标结果while sleep 1; do psql -XAtc copy ( with samples as ( select clock_timestamp(), clock_timestamp() - xact_start as xact_duration, * from pg_stat_activity ) select * from samples where xact_duration interval 1 minute order by xact_duration desc ) to stdout delimiter , csv 21 \ | tee -a long_tx_$(date %Y%m%d).log.csv done4. 什么查询叫慢查询最近刚好也被业务同事问到是否有慢查询监控多慢的语句算慢会影响业务其实应该是由业务定义但作为DBA可以给出一些建议范围OLAP一般看与均值的差异均值1小时某次突发2小时算慢有增量不用用全量用增量但没索引十有八九都算慢会有较频繁DDL的表如果查询运行分钟至小时级易高频阻塞业务一般也算慢OLTP10ms以下高性能100ms以下推荐的性能100~200ms若面向工厂流水线等可以为慢查询1s若面向用户基本属于有感的慢查询高频查询即使是高性能查询在过高执行频率下也会导致负载崩溃需要注意二、 运维排障1. pg关闭慢的常见原因存在大事务/长事务大量缓冲区是脏的 导致关闭时的检查点时间过长WAL 归档 (archive_command) 滞后从库延迟之前停库也遇到过停不下来的问题当时是归档异常及存在发送延迟可以参考PG fast模式停库 在归档过慢及有发送延迟 会被阻塞-CSDN博客2. pg长时间无法启动如何处理不该做的事情 (非专家常见的做法)不明所以就开始担心或等待很长时间多次尝试停止/重新启动该做的事情保持冷静首先查看日志了解它正在做什么了解你的配置和工作负载配置max_wal_size和checkpoint_timeout 主要是检查点相关配置常见于强制关机或从备份恢复工作负载主要是wal日志的产生量了解并观察 REDO 进程比较长后续单独记一篇学习3. 如何加速pg_dump压缩pg_dump ... | gzip不保存到磁盘的转储/恢复pg_dump -h ... | pg_restore并行化的pg_dump通过指定-j数值启动 指定数量的并行pg_dump进程来加速pg_dump -Fd -j8 -f ./test_dump test自定义的高级并行化pg_dump 的并行化是在表级别进行的无法并行转储单个表。要并行转储单个大表需要使用自定义解决方案。为此我们需要使用多个 SQL 客户端如 psql每个客户端在 REPEATABLE READ 隔离级别下工作 (pg_dump 也是在此隔离级别下工作的参见文档)且 (十分重要) 所有转储事务需要使用相同的快照。4. 监控索引创建/重建进度pg 12开始此查询的核心依赖于 pg_stat_progress_create_indexpg 12 引入PostgreSQL: Documentation: 17: 27.4. Progress Reporting配合\watch 5命令可以循环执行查看进度select now(), query_start as started_at, now() - query_start as query_duration, format([%s] %s, a.pid, a.query) as pid_and_query, index_relid::regclass as index_name, relid::regclass as table_name, (pg_size_pretty(pg_relation_size(relid))) as table_size, nullif(wait_event_type, ) || : || wait_event as wait_type_and_event, phase, format( %s (%s of %s), coalesce((round(100 * blocks_done::numeric / nullif(blocks_total, 0), 2))::text || %, N/A), coalesce(blocks_done::text, ?), coalesce(blocks_total::text, ?) ) as blocks_progress, format( %s (%s of %s), coalesce((round(100 * tuples_done::numeric / nullif(tuples_total, 0), 2))::text || %, N/A), coalesce(tuples_done::text, ?), coalesce(tuples_total::text, ?) ) as tuples_progress, current_locker_pid, (select nullif(left(query, 150), ) || ... from pg_stat_activity a where a.pid current_locker_pid) as current_locker_query, format( %s (%s of %s), coalesce((round(100 * lockers_done::numeric / nullif(lockers_total, 0), 2))::text || %, N/A), coalesce(lockers_done::text, ?), coalesce(lockers_total::text, ?) ) as lockers_progress, format( %s (%s of %s), coalesce((round(100 * partitions_done::numeric / nullif(partitions_total, 0), 2))::text || %, N/A), coalesce(partitions_done::text, ?), coalesce(partitions_total::text, ?) ) as partitions_progress, ( select format( %s (%s of %s), coalesce((round(100 * n_dead_tup::numeric / nullif(reltuples::numeric, 0), 2))::text || %, N/A), coalesce(n_dead_tup::text, ?), coalesce(reltuples::int8::text, ?) ) from pg_stat_all_tables t, pg_class tc where t.relid p.relid and tc.oid p.relid ) as table_dead_tuples from pg_stat_progress_create_index p left join pg_stat_activity a on a.pid p.pid order by p.index_relid ; -- in psql, use \watch 5 instead of semicolon未完待续...
postgres-howto 学习笔记
发布时间：2026/5/18 23:42:03
记录一些自己关注的知识点不会每条都记可能有部分补充原文请看 postgres-howto一、 查询性能与观测1. 建议用 EXPLAIN (ANALYZE, BUFFERS)可以看到SQL每步实际的IO次数乘以8K则为字节数pg 18开始buffer成为默认值2. pg_stat_statements速通用途top sql 宏观分析范围不包含在执行的语句、执行失败的语句指标核心为累积指标calls、total_time、rows 等部分为统计类指标stddev_plan_time、stddev_exec_time、min_exec_time完整指标https://www.postgresql.org/docs/current/pgstatstatements.html怎么用取快照看两个快照间的差异数据。如果真的分析DB重启至今的数据可以直接看如今AI已经非常强大其实更重要的是知道要看什么给它指标名想看的数据我们负责拿分析结果3. 临时收集会话数据每秒收集一次pg_stat_activity样本并无限次地记录日志 (直到手动中断)导出的csv文件原文是导回pg分析现在更好的方式直接喂给AI获取目标结果while sleep 1; do psql -XAtc copy ( with samples as ( select clock_timestamp(), clock_timestamp() - xact_start as xact_duration, * from pg_stat_activity ) select * from samples where xact_duration interval 1 minute order by xact_duration desc ) to stdout delimiter , csv 21 \ | tee -a long_tx_$(date %Y%m%d).log.csv done4. 什么查询叫慢查询最近刚好也被业务同事问到是否有慢查询监控多慢的语句算慢会影响业务其实应该是由业务定义但作为DBA可以给出一些建议范围OLAP一般看与均值的差异均值1小时某次突发2小时算慢有增量不用用全量用增量但没索引十有八九都算慢会有较频繁DDL的表如果查询运行分钟至小时级易高频阻塞业务一般也算慢OLTP10ms以下高性能100ms以下推荐的性能100~200ms若面向工厂流水线等可以为慢查询1s若面向用户基本属于有感的慢查询高频查询即使是高性能查询在过高执行频率下也会导致负载崩溃需要注意二、 运维排障1. pg关闭慢的常见原因存在大事务/长事务大量缓冲区是脏的 导致关闭时的检查点时间过长WAL 归档 (archive_command) 滞后从库延迟之前停库也遇到过停不下来的问题当时是归档异常及存在发送延迟可以参考PG fast模式停库 在归档过慢及有发送延迟 会被阻塞-CSDN博客2. pg长时间无法启动如何处理不该做的事情 (非专家常见的做法)不明所以就开始担心或等待很长时间多次尝试停止/重新启动该做的事情保持冷静首先查看日志了解它正在做什么了解你的配置和工作负载配置max_wal_size和checkpoint_timeout 主要是检查点相关配置常见于强制关机或从备份恢复工作负载主要是wal日志的产生量了解并观察 REDO 进程比较长后续单独记一篇学习3. 如何加速pg_dump压缩pg_dump ... | gzip不保存到磁盘的转储/恢复pg_dump -h ... | pg_restore并行化的pg_dump通过指定-j数值启动 指定数量的并行pg_dump进程来加速pg_dump -Fd -j8 -f ./test_dump test自定义的高级并行化pg_dump 的并行化是在表级别进行的无法并行转储单个表。要并行转储单个大表需要使用自定义解决方案。为此我们需要使用多个 SQL 客户端如 psql每个客户端在 REPEATABLE READ 隔离级别下工作 (pg_dump 也是在此隔离级别下工作的参见文档)且 (十分重要) 所有转储事务需要使用相同的快照。4. 监控索引创建/重建进度pg 12开始此查询的核心依赖于 pg_stat_progress_create_indexpg 12 引入PostgreSQL: Documentation: 17: 27.4. Progress Reporting配合\watch 5命令可以循环执行查看进度select now(), query_start as started_at, now() - query_start as query_duration, format([%s] %s, a.pid, a.query) as pid_and_query, index_relid::regclass as index_name, relid::regclass as table_name, (pg_size_pretty(pg_relation_size(relid))) as table_size, nullif(wait_event_type, ) || : || wait_event as wait_type_and_event, phase, format( %s (%s of %s), coalesce((round(100 * blocks_done::numeric / nullif(blocks_total, 0), 2))::text || %, N/A), coalesce(blocks_done::text, ?), coalesce(blocks_total::text, ?) ) as blocks_progress, format( %s (%s of %s), coalesce((round(100 * tuples_done::numeric / nullif(tuples_total, 0), 2))::text || %, N/A), coalesce(tuples_done::text, ?), coalesce(tuples_total::text, ?) ) as tuples_progress, current_locker_pid, (select nullif(left(query, 150), ) || ... from pg_stat_activity a where a.pid current_locker_pid) as current_locker_query, format( %s (%s of %s), coalesce((round(100 * lockers_done::numeric / nullif(lockers_total, 0), 2))::text || %, N/A), coalesce(lockers_done::text, ?), coalesce(lockers_total::text, ?) ) as lockers_progress, format( %s (%s of %s), coalesce((round(100 * partitions_done::numeric / nullif(partitions_total, 0), 2))::text || %, N/A), coalesce(partitions_done::text, ?), coalesce(partitions_total::text, ?) ) as partitions_progress, ( select format( %s (%s of %s), coalesce((round(100 * n_dead_tup::numeric / nullif(reltuples::numeric, 0), 2))::text || %, N/A), coalesce(n_dead_tup::text, ?), coalesce(reltuples::int8::text, ?) ) from pg_stat_all_tables t, pg_class tc where t.relid p.relid and tc.oid p.relid ) as table_dead_tuples from pg_stat_progress_create_index p left join pg_stat_activity a on a.pid p.pid order by p.index_relid ; -- in psql, use \watch 5 instead of semicolon未完待续...
