# 1. 清理系统表
$ psql -U fgedu -d fgedudb -c “VACUUM FULL pg_catalog.pg_class;”

VACUUM

# 2. 分析系统表
$ psql -U fgedu -d fgedudb -c “ANALYZE pg_catalog.pg_class;”

ANALYZE

# 3. 检查系统表大小
$ psql -U fgedu -d fgedudb -c ”
SELECT
relname,
pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = ‘pg_catalog’
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 10;
”

relname | size
——————–+——–
pg_attribute | 8192 kB
pg_depend | 4096 kB
pg_proc | 4096 kB
pg_class | 2048 kB
pg_type | 2048 kB
pg_attribute_encoding | 1024 kB
pg_statistic | 1024 kB
pg_rewrite | 1024 kB
pg_trigger | 1024 kB
pg_constraint | 1024 kB
(10 rows)

# 1. 检查统计信息状态
$ psql -U fgedu -d fgedudb -c ”
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_analyze
FROM pg_stat_user_tables
ORDER BY last_analyze NULLS FIRST;
”

schemaname | relname | n_live_tup | n_dead_tup | last_vacuum | last_analyze
————+—————-+————+————+——————————-+——————————-
public | fgedu_test | 10 | 0 | |
public | fgedu_orders | 100000 | 0 | 2026-04-07 10:00:00.123456+08 | 2026-04-07 10:00:00.123456+08
public | fgedu_customers | 50000 | 0 | 2026-04-07 10:00:00.123456+08 | 2026-04-07 10:00:00.123456+08
(3 rows)

# 2. 手动分析表
$ psql -U fgedu -d fgedudb -c “ANALYZE VERBOSE fgedu_test;”

INFO: analyzing “public.fgedu_test”
INFO: “fgedu_test”: scanned 1 of 1 pages, containing 10 live rows and 0 dead rows; 10 rows in sample, 10 estimated total rows
ANALYZE

# 3. 调整autovacuum参数
$ psql -U fgedu -d fgedudb -c ”
ALTER TABLE fgedu_orders SET (
autovacuum_enabled = true,
autovacuum_vacuum_threshold = 5000,
autovacuum_analyze_threshold = 2500,
autovacuum_vacuum_scale_factor = 0.1,
autovacuum_analyze_scale_factor = 0.05
);
”

ALTER TABLE

# 1. 检查共享缓冲区使用情况
$ psql -U fgedu -d fgedudb -c ”
SELECT
name,
setting,
unit
FROM pg_settings
WHERE name LIKE ‘%shared_buffers%’ OR name LIKE ‘%work_mem%’;
”

name | setting | unit
————————+———+——
shared_buffers | 4GB |
work_mem | 32MB |
maintenance_work_mem | 1GB |
shared_memory_type | mmap |
(4 rows)

# 2. 检查元数据缓存命中率
$ psql -U fgedu -d fgedudb -c ”
SELECT
name,
hits,
misses,
round((hits::numeric / (hits + misses)) * 100, 2) AS hit_rate
FROM pg_stat_bgwriter
UNION ALL
SELECT
‘buffer_cache’ AS name,
sum(blks_hit) AS hits,
sum(blks_read) AS misses,
round((sum(blks_hit)::numeric / (sum(blks_hit) + sum(blks_read))) * 100, 2) AS hit_rate
FROM pg_stat_database;
”

name | hits | misses | hit_rate
—————-+———-+———-+———-
bgwriter | 125000 | 5000 | 96.15
buffer_cache | 15000000 | 1000000 | 93.75
(2 rows)

# 3. 优化连接池配置
# 使用pgBouncer作为连接池
# 配置文件示例：
# [databases]
# fgedudb = host=192.168.1.100 port=5432 dbname=fgedudb
#
# [pgbouncer]
# pool_mode = transaction
# max_client_conn = 1000
# default_pool_size = 20
# min_pool_size = 5
# reserve_pool_size = 10
# reserve_pool_timeout = 5

# 1. 优化前的查询
$ psql -U fgedu -d fgedudb -c ”
EXPLAIN ANALYZE
SELECT
table_schema,
table_name,
column_name,
data_type
FROM information_schema.columns
WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’);
”

Execution Time: 10.523 ms

# 2. 优化后的查询（使用系统表）
$ psql -U fgedu -d fgedudb -c ”
EXPLAIN ANALYZE
SELECT
n.nspname AS table_schema,
c.relname AS table_name,
a.attname AS column_name,
t.typname AS data_type
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_type t ON a.atttypid = t.oid
WHERE n.nspname NOT IN (‘pg_catalog’, ‘information_schema’)
AND a.attnum > 0
ORDER BY n.nspname, c.relname, a.attnum;
”

Execution Time: 2.345 ms

# 3. 创建元数据视图
$ psql -U fgedu -d fgedudb -c ”
CREATE OR REPLACE VIEW v_metadata_columns AS
SELECT
n.nspname AS table_schema,
c.relname AS table_name,
a.attname AS column_name,
t.typname AS data_type,
a.attnotnull AS is_nullable,
a.attlen AS length
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_type t ON a.atttypid = t.oid
WHERE n.nspname NOT IN (‘pg_catalog’, ‘information_schema’)
AND a.attnum > 0;
”

CREATE VIEW

# 4. 使用物化视图提高性能
$ psql -U fgedu -d fgedudb -c ”
CREATE MATERIALIZED VIEW mv_metadata_columns AS
SELECT * FROM v_metadata_columns;

CREATE INDEX idx_mv_metadata_schema ON mv_metadata_columns(table_schema);
CREATE INDEX idx_mv_metadata_table ON mv_metadata_columns(table_name);
”

CREATE MATERIALIZED VIEW
CREATE INDEX
CREATE INDEX

# 1. 使用应用层缓存
# Python示例
import psycopg2
import time

class MetadataCache:
def __init__(self):
self.cache = {}
self.expiry = {}
self.ttl = 3600 # 缓存1小时

def get_columns(self, conn, table_name):
cache_key = f”columns:{table_name}”
now = time.time()

# 检查缓存
if cache_key in self.cache and now < self.expiry.get(cache_key, 0): return self.cache[cache_key] # 从数据库查询 cursor = conn.cursor() cursor.execute(""" SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name = %s ORDER BY ordinal_position """, (table_name,)) columns = cursor.fetchall() cursor.close() # 更新缓存 self.cache[cache_key] = columns self.expiry[cache_key] = now + self.ttl return columns # 使用缓存 cache = MetadataCache() conn = psycopg2.connect( host="192.168.1.100", port="5432", database="fgedudb", user="fgedu", password="Fgedu@2026" ) # 第一次查询（从数据库） start_time = time.time() columns = cache.get_columns(conn, "fgedu_orders") print(f"First query time: {time.time() - start_time:.4f}s") # 第二次查询（从缓存） start_time = time.time() columns = cache.get_columns(conn, "fgedu_orders") print(f"Second query time: {time.time() - start_time:.4f}s") conn.close() # 输出示例 # First query time: 0.0123s # Second query time: 0.0001s

# 1. pgAdmin 4
# 功能：
# – 图形化元数据管理
# – 表结构查看和编辑
# – 索引管理
# – 权限管理
# – 统计信息查看

# 2. psql命令行工具
# 常用命令：
# \d: 查看表结构
# \dt: 查看表
# \di: 查看索引
# \dv: 查看视图
# \du: 查看用户
# \dn: 查看模式
# \df: 查看函数

# 3. pg_metadata工具
# 自定义元数据管理脚本

#!/bin/bash
# metadata_manager.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# 元数据管理工具

echo “PostgreSQL元数据管理工具”
echo “==========================================”
echo “1. 查看表信息”
echo “2. 查看索引信息”
echo “3. 查看权限信息”
echo “4. 查看统计信息”
echo “5. 分析所有表”
echo “6. 清理所有表”
echo “7. 退出”
echo “==========================================”

read -p “请选择操作: ” choice

case $choice in
1)
psql -U fgedu -d fgedudb -c ”
SELECT
table_schema,
table_name,
pg_size_pretty(pg_total_relation_size(table_schema || ‘.’ || table_name)) AS size
FROM information_schema.tables
WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY pg_total_relation_size(table_schema || ‘.’ || table_name) DESC;
”
;;
2)
psql -U fgedu -d fgedudb -c ”
SELECT
schemaname,
tablename,
indexname,
indexdef
FROM pg_indexes
WHERE schemaname NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY schemaname, tablename, indexname;
”
;;
3)
psql -U fgedu -d fgedudb -c ”
SELECT
table_schema,
table_name,
grantee,
privilege_type
FROM information_schema.role_table_grants
WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY table_schema, table_name, grantee;
”
;;
4)
psql -U fgedu -d fgedudb -c ”
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
”
;;
5)
psql -U fgedu -d fgedudb -c “ANALYZE VERBOSE;”
;;
6)
psql -U fgedu -d fgedudb -c “VACUUM VERBOSE;”
;;
7)
echo “退出工具”
exit 0
;;
*)
echo “无效选择”
;;
esac