-- 查看所有活动连接
SELECT
    pid,
    usename,
    datname,
    client_addr,
    state,
    wait_event_type,
    wait_event,
    query,
    query_start,
    now() - query_start AS duration
FROM pg_stat_activity
WHERE state IS NOT NULL
ORDER BY duration DESC;

-- 统计连接状态
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- 查找长查询（超过 5 分钟）
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > INTERVAL '5 minutes';

-- 终止长查询
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > INTERVAL '10 minutes';

-- 空闲事务（idle in transaction）
SELECT pid, now() - state_change AS idle_duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > INTERVAL '5 minutes';

SELECT
    datname,
    numbackends AS connections,
    xact_commit AS commits,
    xact_rollback AS rollbacks,
    blks_read,
    blks_hit,
    round(blks_hit::numeric / NULLIF(blks_hit + blks_read, 0) * 100, 2) AS cache_hit_pct,
    tup_returned,
    tup_fetched,
    tup_inserted,
    tup_updated,
    tup_deleted,
    deadlocks,
    temp_files,
    pg_size_pretty(temp_bytes) AS temp_bytes
FROM pg_stat_database
WHERE datname = current_database();

SELECT
    relname AS table_name,
    seq_scan,           -- 全表扫描次数
    seq_tup_read,       -- 全表扫描读取行数
    idx_scan,           -- 索引扫描次数
    idx_tup_fetch,      -- 索引扫描返回行数
    n_tup_ins AS inserts,
    n_tup_upd AS updates,
    n_tup_del AS deletes,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

SELECT
    checkpoints_timed,
    checkpoints_req,
    buffers_checkpoint,
    buffers_backend,
    buffers_alloc,
    checkpoint_write_time,
    checkpoint_sync_time
FROM pg_stat_bgwriter;

-- 安装
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.max = 10000
-- pg_stat_statements.track = all

-- 最耗时的查询 Top 20
SELECT
    queryid,
    left(query, 100) AS query_short,
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round(max_exec_time::numeric, 2) AS max_time_ms,
    rows,
    shared_blks_hit,
    shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 频率最高的查询
SELECT queryid, left(query, 100), calls, rows
FROM pg_stat_statements
ORDER BY calls DESC LIMIT 20;

-- 重置统计
SELECT pg_stat_statements_reset();

-- 表膨胀率估算
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- 索引膨胀（需要 pgstattuple 扩展）
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('idx_emp_email');

# docker-compose.yml
version: "3.8"
services:
  postgres:
    image: postgres:17
    environment:
      POSTGRES_PASSWORD: password
    ports:
      - "5432:5432"

  postgres-exporter:
    image: prometheuscommunity/postgres-exporter
    environment:
      DATA_SOURCE_NAME: "postgresql://postgres:password@postgres:5432/postgres?sslmode=disable"
    ports:
      - "9187:9187"

  prometheus:
    image: prom/prometheus
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
    ports:
      - "9090:9090"

  grafana:
    image: grafana/grafana
    ports:
      - "3000:3000"

# prometheus.yml
scrape_configs:
  - job_name: "postgres"
    static_configs:
      - targets: ["postgres-exporter:9187"]

-- 查看当前等待
SELECT
    pid,
    wait_event_type,
    wait_event,
    state,
    query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL AND state = 'active';

-- 等待事件类型
-- LWLock: 轻量锁（内部锁）
-- Lock: 重量锁（行锁、表锁）
-- BufferPin: 缓冲区固定
-- Activity: 后台进程等待
-- Client: 等待客户端
-- Extension: 扩展等待
-- IO: 磁盘 I/O
-- IPC: 进程间通信
-- Timeout: 超时等待