Grafana PostgreSQL Dashboards

Links#

1. Dashboard Rule#

Folder: Database
Dashboard: PostgreSQL - Overview

Rows:
    Overview:
        database 是否 up，connections 是否耗尽，query 是否变慢，error / lock / replication 是否异常

    Drilldown:
        按 database / user / query / table / index / wait event 拆开看

    Capacity:
        connection、storage、WAL、table/index growth、cache hit 的趋势

    Debug:
        vacuum、checkpoint、lock tree、slow query fingerprint，不放第一屏；排查数据库内部问题时查看

2. Overview#

Panel	Metrics	Why
Database up	exporter `up` / connection check	第一优先级可用性信号
Connections	active / idle / idle in transaction / max connections	connection pool 和 DB 资源风险
Transaction rate	commits / rollbacks from `pg_stat_database`	workload 基线和 rollback 异常
Query latency	`pg_stat_statements` mean / p95 if collected	用户侧慢查询信号
Locks / deadlocks	lock count, `deadlocks`	blocking 会直接影响请求
Replication lag	replication lag metrics if replica exists	read replica / HA 场景必看

3. Drilldown#

Panel	Metrics	Why
Connections by state	`pg_stat_activity` state	区分 active、idle、idle in transaction
Wait events	`pg_stat_activity` wait_event_type / wait_event	判断 CPU、IO、lock、client wait
Slow query fingerprints	`pg_stat_statements` calls / total_time / mean_time	定位具体 SQL pattern
Table I/O	`pg_stat_user_tables`, `pg_statio_user_tables`	找到热点表
Index usage	`idx_scan`, sequential scan counters	判断索引是否有效

4. Capacity#

Panel	Metrics	Why
Connection usage trend	connections / max_connections	判断 pool 和 max_connections 是否合理
Database size	database size by db	storage 规划
Table / index growth	relation size by table/index	找到增长最快对象
Cache hit ratio	`blks_hit`, `blks_read`	判断内存和 workload locality
WAL generation	WAL bytes / files if collected	replication、backup、storage 压力
Temp files / bytes	`temp_files`, `temp_bytes`	排查 sort/hash spill 和 work_mem 问题

5. Debug#

Panel	Metrics	Why
Vacuum health	last autovacuum / dead tuples	bloat / autovacuum 问题
Checkpoint behavior	checkpoint count / write time / sync time	写入抖动排查
Lock details	blocking query / blocked query	只在 lock incident 中使用
Query plan sample	sampled slow query + EXPLAIN outside dashboard	dashboard 不应承载完整 query plan

6. YACE Metrics#

自建 PostgreSQL dashboard 不应该用 YACE。PostgreSQL 内部指标来自 postgres_exporter、pg_stat_database、pg_stat_activity、pg_stat_statements。

如果你的 PostgreSQL 是 AWS RDS / Aurora PostgreSQL，可以额外用 YACE 采集 AWS/RDS 的云平台层指标，但它不能替代 postgres_exporter。

YACE config for RDS / Aurora PostgreSQL:

apiVersion: v1alpha1
sts-region: ap-northeast-1
discovery:
  jobs:
    - type: AWS/RDS
      regions:
        - ap-northeast-1
      customTags:
        - key: environment
          value: uat
      period: 300
      length: 600
      nilToZero: true
      metrics:
        - name: CPUUtilization
          statistics: [Average]
        - name: FreeableMemory
          statistics: [Average]
        - name: DatabaseConnections
          statistics: [Average]
        - name: ReadLatency
          statistics: [Average, p95]
        - name: WriteLatency
          statistics: [Average, p95]
        - name: ReadIOPS
          statistics: [Average]
        - name: WriteIOPS
          statistics: [Average]
        - name: VolumeBytesUsed
          statistics: [Average]
          period: 300
          length: 900
        - name: AuroraReplicaLagMaximum
          statistics: [Maximum]
        - name: Deadlocks
          statistics: [Sum]

Aurora cluster note:

如果你采到的 label 是 dimension_DBClusterIdentifier，而不是 DB instance 维度，
优先按 Aurora cluster 来做 dashboard 和告警。

这种情况下更常用:
    VolumeBytesUsed
    AuroraReplicaLagMaximum

而不是:
    FreeStorageSpace
    ReplicaLag

VolumeBytesUsed 建议单独配置:
    period: 300
    length: 900

这样可以降低短窗口没拿到 datapoint 时被补成 0 的概率。

确认 metric name:

{__name__=~"aws_rds_.*"}

7. Do Not Put In Overview#

不要放:
    每张表的所有统计
    每条 SQL 文本
    vacuum / checkpoint 全量细节

原因:
    PostgreSQL overview 重点是 up、connections、latency、locks、replication、storage risk