通过自然语言，轻松管理 PostgreSQL，利用其强大特性！

🎯 功能特点

核心能力

• 🔍 智能查询 - 自然语言描述，自动生成 SQL
• 📊 高级分析 - 窗口函数、CTE、复杂聚合
• 📝 JSON 操作 - JSONB 查询、更新、索引
• 🔎 全文搜索 - PostgreSQL 强大的全文搜索能力
• ⚡ 性能分析 - EXPLAIN ANALYZE、索引优化
• 💾 备份恢复 - pg_dump/pg_restore 完整方案

📋 使用场景

查询场景

• "查询每个部门薪资前三的员工"（窗口函数）
• "找出重复的订单号"（HAVING 子句）
• "统计用户留存率"（复杂分析查询）

JSON 数据场景

• "查询 JSON 字段中特定路径的值"
• "更新 JSONB 数据中的嵌套字段"
• "为 JSON 字段创建 GIN 索引"

全文搜索场景

• "搜索包含特定关键词的文章"
• "按相关度排序搜索结果"
• "中文全文搜索配置"

🔧 前置条件

1. 安装 PostgreSQL 客户端

Ubuntu/Debian:

sudo apt update
sudo apt install postgresql-client

macOS:

brew install libpq

Windows: 下载并安装 PostgreSQL 官方客户端工具

2. 配置数据库连接

使用环境变量：

export PGHOST=localhost
export PGPORT=5432
export PGUSER=your_username
export PGPASSWORD=your_password
export PGDATABASE=your_database

或使用连接字符串：

postgresql://username:password@localhost:5432/database

💻 常用操作

高级查询示例

窗口函数 - 每部门薪资前3的员工

SELECT department, name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees
QUALIFY rank <= 3;

CTE (Common Table Expression)

WITH monthly_sales AS (
    SELECT DATE_TRUNC('month', order_date) as month,
           SUM(amount) as total
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT month, total,
       LAG(total) OVER (ORDER BY month) as prev_month,
       ROUND(
           (total - LAG(total) OVER (ORDER BY month))::numeric / LAG(total) OVER (ORDER BY month)  100,
           2
       ) as growth_pct
FROM monthly_sales;

JSONB 操作

查询 JSONB 字段

-- 查询 JSON 中的嵌套值
SELECT id,
       metadata->>'user_id' as user_id,
       metadata->'preferences'->>'theme' as theme
FROM users
WHERE metadata->>'status' = 'active';

更新 JSONB 字段

UPDATE users
SET metadata = jsonb_set(
    metadata,
    '{preferences,theme}',
    '"dark"'
)
WHERE id = 123;

为 JSONB 创建索引

-- GIN 索引支持高效的 JSONB 查询
CREATE INDEX idx_users_metadata_gin ON users USING gin (metadata jsonb_path_ops);

全文搜索

配置中文全文搜索

-- 创建全文搜索索引
CREATE INDEX idx_articles_content_fts ON articles USING gin(to_tsvector('chinese', content));
-- 全文搜索
SELECT title,
       ts_headline('chinese', content, query) as snippet
FROM articles, to_tsquery('chinese', '人工智能') as query
WHERE to_tsvector('chinese', content) @@ query
ORDER BY ts_rank(to_tsvector('chinese', content), query) DESC;

性能分析

EXPLAIN ANALYZE 查询计划

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT  FROM orders
WHERE status = 'completed'
ORDER BY created_at DESC
LIMIT 100;

查找缺失的索引

SELECT schemaname, tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'your_table'
ORDER BY n_distinct DESC;

索引使用率分析

SELECT schemaname, tablename, indexname,
       idx_scan as index_scans,
       idx_tup_read as tuples_read,
       idx_tup_fetch as tuples_fetched
FROM pg_stat_user_indexes
WHERE idx_scan < 50  -- 很少使用的索引
ORDER BY idx_scan;

💾 备份恢复

备份

完整备份：

pg_dump -F c -f backup_$(date +%Y%m%d).dump your_database

仅结构备份：

pg_dump -s -f schema_only.sql your_database

仅数据备份：

pg_dump -a -f data_only.sql your_database

单表备份：

pg_dump -t table_name -f table_backup.sql your_database

恢复

恢复完整备份：

pg_restore -d target_database backup_file.dump

列出备份内容：

pg_restore -l backup_file.dump

选择性恢复：

pg_restore -d target_database -t table_name backup_file.dump

🔍 监控与维护

数据库大小查询

SELECT datname as database_name,
       pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

表大小查询

SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

活跃连接查询

SELECT pid, usename, application_name, client_addr, state,
       query_start, state_change, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

⚠️ 注意事项

性能优化建议

• 使用合适的索引 - 为 WHERE、JOIN、ORDER BY 字段创建索引
• 避免 SELECT - 只查询需要的字段
• 使用 EXPLAIN - 复杂查询先分析执行计划
• 定期 VACUUM - 清理死元组，回收空间
• 统计信息更新 - ANALYZE table 更新统计信息

安全建议

• 使用参数化查询 - 防止 SQL 注入
• 最小权限原则 - 使用只读用户进行查询
• 定期备份 - 生产环境必须开启定时备份
• 连接池配置 - 使用 PgBouncer 管理连接

🎓 示例对话

示例 1：复杂分析查询

用户： "查询每个部门员工的薪资排名"

AI： "使用窗口函数查询每个部门的员工薪资排名"

SELECT department, name, salary,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM employees
ORDER BY department, salary_rank;

示例 2：JSONB 查询

用户： "查询用户偏好设置中主题为 dark 的用户"

AI： "查询 JSONB 字段中的嵌套值"

SELECT id, username, metadata->'preferences' as preferences
FROM users
WHERE metadata->>'theme' = 'dark';

示例 3：性能优化

用户： "帮我分析这个查询为什么慢"

AI： "使用 EXPLAIN ANALYZE 分析查询执行计划"

EXPLAIN (ANALYZE, BUFFERS)
SELECT  FROM orders o
JOIN users
WHERE o.user_id = users.id
  AND o.created_at >= NOW() - INTERVAL '7 days';

📚 参考资料

• PostgreSQL 官方文档
• PostgreSQL 性能优化
• PostgreSQL JSONB 使用指南

开始使用： 告诉我你的查询需求，我会利用 PostgreSQL 的强大特性帮你完成！🚀