十一、监控与诊断

11.1 系统视图

-- 查看活动会话
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query,
    query_start,
    state_change,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE state != 'idle';

-- 查看表统计
SELECT 
    schemaname,
    tablename,
    n_live_tup AS live_tuples,
    n_dead_tup AS dead_tuples,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- 查看索引统计
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan;

-- 查看数据库大小
SELECT 
    datname,
    pg_database_size(datname) AS size_bytes,
    pg_size_pretty(pg_database_size(datname)) AS size_pretty
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- 查看表大小
SELECT 
    tablename,
    pg_size_pretty(pg_table_size(tablename)) AS table_size,
    pg_size_pretty(pg_indexes_size(tablename)) AS indexes_size,
    pg_size_pretty(pg_total_relation_size(tablename)) AS total_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename) DESC;

-- 查看WAL信息
SELECT 
    pg_current_wal_lsn(),
    pg_walfile_name(pg_current_wal_lsn());

-- 查看数据库活动
SELECT 
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit
FROM pg_stat_database;

11.2 性能诊断

-- 查看慢查询
SELECT 
    query,
    calls,
    total_time / 1000 AS total_seconds,
    mean_time / 1000 AS mean_ms,
    max_time / 1000 AS max_ms,
    rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

-- 启用pg_stat_statements
-- 在postgresql.conf中添加
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

-- 重置统计
SELECT pg_stat_statements_reset();

-- 查看缓存命中率
SELECT 
    datname,
    blks_hit,
    blks_read,
    CASE 
        WHEN blks_hit + blks_read = 0 THEN 0
        ELSE blks_hit * 100.0 / (blks_hit + blks_read)
    END AS cache_hit_ratio
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1');

-- 查看表膨胀
SELECT 
    schemaname,
    tablename,
    n_live_tup,
    n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_tuple_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_tuple_ratio DESC;

-- 查看阻塞锁
SELECT 
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking 
    ON blocked.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

11.3 自动清理

-- 查看自动清理配置
SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name LIKE '%autovacuum%';

-- 调整自动清理参数
ALTER SYSTEM SET autovacuum = on;
ALTER SYSTEM SET autovacuum_naptime = '1min';
ALTER SYSTEM SET autovacuum_vacuum_threshold = 50;
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.2;
ALTER SYSTEM SET autovacuum_analyze_threshold = 50;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.1;
SELECT pg_reload_conf();

-- 手动清理
VACUUM (VERBOSE, ANALYZE) employees;
VACUUM FULL employees;  -- 锁定表，回收空间
ANALYZE employees;

-- 查看清理进度
SELECT 
    pid,
    datname,
    relid::regclass,
    phase,
    heap_blks_total,
    heap_blks_scanned,
    heap_blks_vacuumed,
    index_vacuum_count
FROM pg_stat_progress_vacuum;

十二、扩展与工具

12.1 常用扩展

-- 查看已安装扩展
SELECT * FROM pg_available_extensions;
SELECT * FROM pg_extension;

-- 创建扩展
CREATE EXTENSION IF NOT EXISTS hstore;  -- 键值存储
CREATE EXTENSION IF NOT EXISTS uuid-ossp;  -- UUID生成
CREATE EXTENSION IF NOT EXISTS pgcrypto;  -- 加密函数
CREATE EXTENSION IF NOT EXISTS tablefunc;  -- 交叉表
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;  -- 模糊匹配
CREATE EXTENSION IF NOT EXISTS citext;  -- 不区分大小写文本
CREATE EXTENSION IF NOT EXISTS ltree;  -- 树形结构
CREATE EXTENSION IF NOT EXISTS cube;  -- 多维立方体
CREATE EXTENSION IF NOT EXISTS earthdistance;  -- 地球距离计算

-- 使用uuid-ossp
SELECT uuid_generate_v4();
SELECT uuid_generate_v1();

-- 使用pgcrypto
SELECT crypt('password', gen_salt('bf'));
SELECT crypt('password', stored_hash) = stored_hash;

-- 使用hstore
CREATE TABLE products_hstore (id SERIAL, attributes HSTORE);
INSERT INTO products_hstore (attributes) VALUES ('"brand"=>"Dell", "ram"=>"16GB"');
SELECT attributes -> 'brand' FROM products_hstore;

-- 使用PostGIS（地理空间）
CREATE EXTENSION postgis;
CREATE TABLE locations (id SERIAL, name TEXT, geom GEOMETRY);
INSERT INTO locations (name, geom) VALUES 
('北京', ST_GeomFromText('POINT(116.4 39.9)', 4326));

SELECT ST_Distance(
    ST_GeomFromText('POINT(116.4 39.9)', 4326),
    ST_GeomFromText('POINT(121.5 31.2)', 4326)
);

12.2 性能调优工具

# pg_stat_statements - SQL统计
# 已在11.1中介绍

# pgBadger - 日志分析
pgbadger /var/log/postgresql/postgresql.log -o report.html

# pg_top - 实时监控
pg_top -d mydb

# pgbouncer - 连接池
# 配置 pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

# pgcluu - 性能报告
pgcluu -d /var/lib/postgresql/16/main -o /tmp/pgcluu_report

# pg_activity - 实时活动监控
pg_activity -U postgres -d mydb

十三、PostgreSQL 16 新特性

13.1 性能改进

-- 并行查询增强
-- 设置并行度
SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 1000;
SET parallel_tuple_cost = 0.1;

-- 增量排序
SET enable_incremental_sort = on;

-- 使用增量排序
EXPLAIN SELECT * FROM employees ORDER BY department_id, salary;

-- 聚合下推
SET enable_partitionwise_aggregate = on;

-- 提高预写日志性能
ALTER SYSTEM SET wal_sync_method = fdatasync;
ALTER SYSTEM SET wal_compression = on;

13.2 SQL/JSON支持

-- JSON_TABLE函数
SELECT *
FROM JSON_TABLE(
    '[{"id": 1, "name": "张三"}, {"id": 2, "name": "李四"}]',
    '$[*]' COLUMNS (
        id INT PATH '$.id',
        name TEXT PATH '$.name'
    )
) AS jt;

-- IS JSON 约束
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    data JSONB,
    CONSTRAINT data_is_json CHECK (data IS JSON)
);

13.3 其他新特性

-- 并行VACUUM
VACUUM (PARALLEL 4) employees;

-- 逻辑复制性能提升
-- 支持部分复制槽
-- 支持双向复制

-- 系统视图增强
-- pg_stat_io 视图
SELECT * FROM pg_stat_io;

-- pg_stat_subscription_stats
SELECT * FROM pg_stat_subscription_stats;

-- 冻结映射（Freeze Map）
-- 减少VACUUM开销

PostgreSQL的世界充满无限可能，愿本文成为你数据库学习之路上的重要指南。持续学习，深入实践，你一定能成为优秀的PostgreSQL数据库专家！
来源：
https://app-ac8abncezqpt.appmiaoda.com