十一、监控与诊断
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
