PostgreSQL数据源统计信息查看_PostgreSQL数据源统计监控方法-SQL-PHP中文网

答案是通过查询pg_stat_activity、pg_locks、pg_stat_statements等内置视图可诊断PostgreSQL性能瓶颈。首先查看pg_stat_activity定位长时间运行或等待的查询，结合pg_locks分析锁争用情况；再利用pg_stat_statements识别高耗时SQL；并通过pg_stat_database和pg_stat_tables评估数据库整体负载与表访问模式，最终结合ANALYZE更新统计信息确保执行计划准确性，实现系统性能优化。

postgresql数据源统计信息查看_postgresql数据源统计监控方法

PostgreSQL数据源的统计信息查看，核心在于利用其强大的内置系统视图，这些视图提供了数据库内部运行状态的实时快照和历史数据。而统计监控，则是在此基础上，通过自动化工具、自定义脚本或集成专业监控系统，实现对这些关键指标的持续跟踪、趋势分析和异常告警，从而确保数据库的健康运行和性能优化。

解决方案

要深入理解和有效监控PostgreSQL数据源，我们需要从两个层面着手：直接查询内置视图以获取即时信息，以及构建持续监控体系以追踪长期趋势和发现潜在问题。

1. 直接查询内置视图： PostgreSQL提供了一系列

pg_stat_*

登录后复制

和

pg_locks

登录后复制

等视图，它们是了解数据库运行状态的金矿。

pg_stat_activity
登录后复制
: 这是我个人最常用，也是最直观的视图。它展示了当前所有活跃的会话信息，包括用户、客户端IP、当前执行的查询、查询状态（idle, active, waiting）、启动时间等。通过它，你可以快速发现长时间运行的查询、被阻塞的会话，甚至是死锁的迹象。
```
SELECT pid, datname, usename, client_addr, application_name, backend_start, state, query_start, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;
```
登录后复制
我常常会关注
```
state
```
登录后复制
和
```
wait_event
```
登录后复制
字段，这能告诉我查询是在执行、等待I/O还是被锁住了。
pg_stat_database
登录后复制
: 提供了数据库级别的统计信息，比如事务提交/回滚次数、读取/写入的数据块数量、冲突次数等。这对于评估整个数据库的负载和健康状况非常有帮助。
```
SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted
FROM pg_stat_database
WHERE datname = 'your_database_name';
```
登录后复制
```
blks_hit
```
登录后复制
和
```
blks_read
```
登录后复制
的比例可以初步判断缓存命中率。
pg_stat_tables
登录后复制
/
pg_stat_indexes
登录后复制
: 这些视图提供了表和索引层面的详细统计，比如扫描次数、插入/更新/删除行数、上次分析/清理时间等。通过它们，你可以识别出哪些表或索引是热点，哪些可能需要重新索引或优化查询。
```
-- 查看最常被扫描的表
SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;
```
登录后复制
pg_stat_statements
登录后复制
(需要安装扩展): 这是我诊断慢查询的利器。它会记录所有执行过的SQL语句及其统计信息，包括执行次数、总耗时、平均耗时、I/O时间等。安装并启用这个扩展后，你能清晰地看到哪些查询是性能瓶颈。
```
-- 安装扩展 (如果尚未安装)
-- CREATE EXTENSION pg_stat_statements;
-- 查看耗时最长的查询
SELECT query, calls, total_time, mean_time, rows, stddev_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
```
登录后复制
这比在应用日志里大海捞针效率高多了。
pg_locks
登录后复制
: 实时显示当前所有的锁信息。当数据库出现性能瓶颈，尤其是有大量查询等待时，
```
pg_locks
```
登录后复制
是排查锁冲突的关键。
```
SELECT pid, mode, granted, relation::regclass, virtualtransaction, client_addr, query
FROM pg_locks pl
JOIN pg_stat_activity psa ON pl.pid = psa.pid
WHERE NOT granted; -- 只看等待中的锁
```
登录后复制
我一般会结合
```
pg_stat_activity
```
登录后复制
一起看，找到是哪个查询持有锁，哪个查询在等待。

2. 构建持续监控体系： 手动查询毕竟是点状的，对于长期趋势和异常告警，我们需要更自动化的方案。

Prometheus + Grafana: 这是目前我最推荐的组合。
```
postgres_exporter
```
登录后复制
可以从PostgreSQL收集大量指标（包括上述大部分
```
pg_stat_*
```
登录后复制
视图的数据），Prometheus负责存储和告警，Grafana则提供美观且功能强大的可视化面板。这种方式能让你轻松构建出数据库的实时仪表盘，监控CPU、内存、磁盘I/O、连接数、慢查询趋势、缓存命中率等。
Zabbix/Nagios: 这些传统的监控系统也提供了PostgreSQL的监控模板。它们通过Agent或SNMP收集数据，并支持灵活的告警配置。对于已经在使用这些系统的团队来说，集成起来比较方便。
云服务商的监控服务: 如果你的PostgreSQL运行在AWS RDS、Azure Database for PostgreSQL或Google Cloud SQL上，云服务商通常会提供开箱即用的监控和告警功能，这些服务往往集成了日志分析、性能洞察等高级特性。
自定义脚本: 对于一些特殊需求或轻量级监控，我也会写一些Python或Shell脚本，定期查询
```
pg_stat_*
```
登录后复制
视图，然后将数据写入日志文件、时序数据库，或者通过邮件/Slack发送告警。这虽然需要一些开发工作，但灵活性最高。

如何通过PostgreSQL内置视图诊断数据库性能瓶颈？

诊断PostgreSQL数据库性能瓶颈，内置视图是我们的第一手资料，也是最直接的工具。我通常会从宏观到微观，逐步深入。

一开始，我会先看看

pg_stat_activity

登录后复制

，这就像是数据库的“心电图”。如果看到大量

active

登录后复制

状态的查询，并且

query_start

登录后复制

时间很长，那么这些查询本身就是嫌疑犯。特别是当

wait_event_type

登录后复制

或

wait_event

登录后复制

显示为

Lock

登录后复制

、

IO

登录后复制

或

ClientRead

登录后复制

时，就意味着查询可能被锁阻塞、等待磁盘I/O，或者客户端没有及时读取结果。我经常会把

pg_stat_activity

登录后复制

的结果按照

query_start

登录后复制

排序，找出那些“老油条”查询。

接着，如果

pg_stat_activity

登录后复制

显示有锁等待，我会立即转向

pg_locks

登录后复制

。结合

pg_stat_activity

登录后复制

，我可以定位到哪个

pid

登录后复制

（进程ID）持有锁，哪个

pid

登录后复制

在等待。这能帮助我判断是某个事务持有锁时间过长，还是有不合理的并发操作导致了死锁或活锁。比如，我曾遇到过一个长时间运行的

UPDATE

登录后复制

语句，它锁住了整个表，导致后续所有对该表的读写操作都陷入等待。

然后，对于那些执行时间长、资源消耗大的查询，

pg_stat_statements

登录后复制

就派上大用场了。我个人觉得，这个视图是PostgreSQL性能诊断的“杀手锏”。它不仅能告诉你哪些查询耗时最多、执行频率最高，还能提供平均执行时间、I/O时间等详细数据。通过分析

total_time

登录后复制

和

mean_time

登录后复制

，我可以找出那些“慢查询”，然后通过

EXPLAIN (ANALYZE, BUFFERS)

登录后复制

去分析它们的执行计划，看看是索引没用上、数据扫描量过大，还是连接方式不合理。我曾通过它发现过很多由于缺少索引或SQL写法不当导致的性能问题。

此外，

pg_stat_database

登录后复制

能提供数据库整体的健康概览。如果

xact_rollback

登录后复制

（事务回滚次数）很高，可能意味着应用层存在大量错误或死锁。而

blks_read

登录后复制

和

blks_hit

登录后复制

的比例，则能大致反映共享缓冲区的使用效率。如果

blks_read

登录后复制

远高于

blks_hit

登录后复制

，说明大量数据需要从磁盘读取，缓存命中率低，这可能是内存不足或查询优化不佳的信号。

最后，如果表或索引的性能出现问题，

pg_stat_tables

登录后复制

和

pg_stat_indexes

登录后复制

能提供线索。例如，一个表

seq_scan

登录后复制

（全表扫描）次数远高于

idx_scan

登录后复制

（索引扫描）次数，但其数据量又很大，那很可能意味着某些查询没有正确使用索引，或者根本就没有合适的索引。这时候，我就会考虑创建新索引或优化现有索引。

总的来说，诊断瓶颈是一个迭代的过程：从整体概览到具体查询，从实时状态到历史趋势，结合这些视图提供的数据，逐步缩小问题范围，最终定位到根源。

PostgreSQL数据库长期性能趋势分析与自动化监控实践

仅仅在问题发生时才去查看统计信息是远远不够的。我个人经验告诉我，建立一套自动化监控体系，对PostgreSQL数据库的长期性能趋势进行分析，才是预防问题、优化性能的关键。这就像给数据库配备了一个全天候的健康监测仪。

怪兽AI数字人

数字人短视频创作，数字人直播，实时驱动数字人

查看详情

我最推崇的实践是结合Prometheus和Grafana。首先，你需要在数据库服务器上部署

node_exporter

登录后复制

来监控操作系统层面的指标（CPU、内存、磁盘I/O、网络），然后部署

postgres_exporter

登录后复制

来收集PostgreSQL自身的各种统计信息。

postgres_exporter

登录后复制

非常强大，它能从

pg_stat_activity

登录后复制

、

pg_stat_database

登录后复制

、

pg_stat_tables

登录后复制

、

pg_stat_statements

登录后复制

等视图中抓取数据，并以Prometheus可识别的格式暴露出来。

Prometheus作为时序数据库，会定期从这些exporter那里拉取（pull）数据，并存储起来。它的查询语言PromQL非常灵活，可以对这些指标进行聚合、计算、趋势分析。例如，你可以计算每秒的事务提交数、平均查询延迟、缓存命中率等。Prometheus的告警管理器Alertmanager则负责根据预设的规则发送告警通知，比如当某个数据库的CPU使用率连续5分钟超过80%时，或者

pg_stat_activity

登录后复制

中等待锁的会话数超过某个阈值时，立即通知相关人员。

Grafana则是数据可视化的利器。它能连接到Prometheus，通过各种图表（折线图、柱状图、仪表盘等）直观地展示数据库的各项性能指标。你可以创建多个仪表盘，分别用于概览、慢查询分析、连接池状态、存储使用等。通过Grafana，我们可以轻松地看到过去24小时、7天甚至更长时间的性能走势。这对于容量规划、识别周期性负载高峰、评估优化效果都至关重要。比如，我曾通过Grafana发现某个数据库在每周一上午9点都会出现I/O高峰，经过分析，原来是某个批处理任务在那个时间点启动，于是我们调整了任务的调度策略，有效缓解了瓶颈。

除了Prometheus/Grafana，对于一些特定的场景，我也使用过Zabbix或自定义脚本。Zabbix的模板化配置对于大规模部署非常方便，但它的时序数据存储和查询能力相对Prometheus稍弱。自定义脚本则提供了最大的灵活性，你可以编写Python脚本，定期查询

pg_stat_statements

登录后复制

，将慢查询日志写入文件，或者分析

pg_locks

登录后复制

，发现长时间的锁等待并自动发送告警。

重要的是，无论是哪种工具，核心都是要持续地收集数据，并对其进行分析。长期趋势分析能帮助我们：

容量规划：根据数据增长、连接数、CPU/内存使用趋势，预估未来的资源需求。
问题预测：在性能指标出现缓慢恶化趋势时，提前介入，避免问题爆发。
优化效果评估：任何数据库优化（如索引调整、SQL重写、配置更改）后，通过监控数据来验证其效果。
基线建立：了解数据库在正常负载下的“健康”指标范围，以便在异常发生时能快速识别。

在我看来，自动化监控不仅仅是技术，更是一种运维理念，它将我们从被动救火的模式中解放出来，转向主动预防和持续优化。

PostgreSQL统计信息收集机制及其对查询优化的深远影响

PostgreSQL的统计信息收集机制远不止是提供一些视图供我们查看，它更深层次地影响着数据库的查询优化器，直接决定了查询的执行效率。理解这一点，对于数据库管理员和开发人员来说至关重要。

核心在于

ANALYZE

登录后复制

命令和

autovacuum

登录后复制

进程。

ANALYZE

登录后复制

命令会收集表和索引的统计信息，包括每个列中值的分布、NULL值的比例、不同值的数量等。这些信息存储在系统目录中，供查询优化器（Query Planner）使用。优化器的任务是为每个SQL查询找到最有效率的执行计划。它会根据这些统计信息来估计不同执行路径（如全表扫描、索引扫描、哈希连接、嵌套循环连接等）的成本，并选择成本最低的那个。

举个例子，如果一个列的统计信息显示其值分布非常均匀，且不同值数量很多，优化器可能会倾向于使用该列上的索引进行查找。但如果统计信息显示该列大部分值都相同（比如一个状态字段，99%都是'active'），那么即使有索引，优化器也可能判断全表扫描的成本更低，因为它知道索引扫描会带来大量的随机I/O，效率反而不如顺序扫描。

autovacuum

登录后复制

守护进程在后台默默工作，它不仅负责清理死元组（已删除或更新的旧行版本），还会定期自动执行

ANALYZE

登录后复制

。这意味着，随着数据的不断变化（插入、更新、删除），

autovacuum

登录后复制

会确保统计信息保持最新。如果统计信息过时，优化器可能会做出错误的判断，选择一个低效的执行计划，导致查询变慢。我个人曾遇到过这样的情况：一个大表在导入大量数据后，

autovacuum

登录后复制

还没来得及对它进行

ANALYZE

登录后复制

，导致所有针对该表的查询都变得异常缓慢，手动执行

ANALYZE

登录后复制

后，性能立即恢复。

PostgreSQL中有一些配置参数与统计信息收集密切相关，它们能够影响优化器的决策：

track_counts
登录后复制
: 默认开启，控制是否收集表和索引的访问统计信息（如扫描次数、插入/更新/删除行数）。这是
```
pg_stat_tables
```
登录后复制
等视图的数据来源。
track_functions
登录后复制
: 控制是否收集用户定义函数的调用统计信息。
track_io_timing
登录后复制
: 默认关闭，开启后可以收集I/O操作的耗时，这对于
```
pg_stat_statements
```
登录后复制
统计I/O时间非常有用。开启它会带来轻微的性能开销，但在诊断I/O密集型查询时非常有价值。
default_statistics_target
登录后复制
: 这个参数控制
```
ANALYZE
```
登录后复制
收集统计信息的详细程度。值越大，收集的统计信息越详细，优化器做决策的依据越精确，但
```
ANALYZE
```
登录后复制
的执行时间也会相应增加。对于一些数据分布复杂或查询性能敏感的列，可以针对性地提高其
```
ALTER TABLE ... ALTER COLUMN ... SET STATISTICS TARGET
```
登录后复制
。