PostgreSQL 数据库

欢迎来到 PostgreSQL 数据库知识库！

🐘 PostgreSQL 简介

PostgreSQL 是世界上最先进的开源关系型数据库，以其功能丰富、标准兼容和可扩展性而著称。它不仅是一个关系型数据库，还支持 JSON、数组、全文搜索等高级特性。

🎯 PostgreSQL vs MySQL

特性	PostgreSQL	MySQL
标准兼容	⭐⭐⭐⭐⭐ 严格遵循 SQL 标准	⭐⭐⭐ 部分兼容
复杂查询	⭐⭐⭐⭐⭐ 窗口函数、CTE	⭐⭐⭐ 基本支持
JSON 支持	⭐⭐⭐⭐⭐ 原生 JSONB	⭐⭐⭐ JSON 字段
全文搜索	⭐⭐⭐⭐⭐ 内置强大	⭐⭐ 基础功能
扩展性	⭐⭐⭐⭐⭐ 自定义类型、函数	⭐⭐⭐ 有限
易用性	⭐⭐⭐ 学习曲线较陡	⭐⭐⭐⭐⭐ 简单易用

🎯 适用场景

✅ 特别适合

• 复杂查询 - 窗口函数、递归查询
• 数据仓库 - OLAP、数据分析
• 地理信息系统 - PostGIS 扩展
• JSON 存储 - 半结构化数据
• 全文搜索 - 内置搜索引擎
• 需要扩展的场景 - 自定义类型、函数

✅ 相比 MySQL 的优势

• 更强大的查询能力
• 更好的并发控制（MVCC）
• 更丰富的数据类型
• 更标准的 SQL 实现

🎯 学习路线

第一阶段：PostgreSQL 基础 (1-2周)

1. 安装配置

   • 安装 PostgreSQL
   • psql 命令行工具
   • pgAdmin 图形工具

2. SQL 基础

   • CRUD 操作
   • 连接查询
   • 聚合函数

第二阶段：高级特性 (2-3周)

1. 高级查询

   • 窗口函数
   • CTE 递归查询
   • 数组操作

2. JSON 支持

   • JSON vs JSONB
   • JSON 查询
   • JSON 索引

3. 全文搜索

   • tsvector/tsquery
   • 中文分词

第三阶段：性能优化 (持续)

1. 索引策略

   • B-tree、Hash、GiST、GIN
   • 部分索引
   • 表达式索引

2. 查询优化

   • EXPLAIN ANALYZE
   • 查询计划
   • 统计信息

✨ PostgreSQL 特点

✅ 核心优势

• 标准兼容 - 严格遵循 SQL 标准
• 功能丰富 - 窗口函数、CTE、数组、JSON
• 可扩展 - 自定义类型、函数、操作符
• ACID 支持 - 完整的事务支持
• MVCC - 多版本并发控制
• 开源 - MIT-like 许可证
• 活跃社区 - 持续更新和改进

🔧 高级数据类型

• JSON/JSONB - 半结构化数据
• Array - 数组类型
• hstore - 键值对
• UUID - 全局唯一标识符
• Range - 范围类型
• Geometric - 几何类型

📖 SQL 速查

数据库操作

-- 创建数据库
CREATE DATABASE mydb 
    WITH ENCODING 'UTF8' 
    LC_COLLATE='zh_CN.UTF-8' 
    LC_CTYPE='zh_CN.UTF-8';

-- 连接数据库
\c mydb

-- 删除数据库
DROP DATABASE mydb;

表操作

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100),
    age INT CHECK (age >= 0),
    tags TEXT[],  -- 数组类型
    metadata JSONB,  -- JSON类型
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_metadata ON users USING GIN(metadata);

-- 添加注释
COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.username IS '用户名';

JSON 操作

-- 插入 JSON 数据
INSERT INTO users (username, metadata) VALUES 
    ('张三', '{"city": "北京", "hobbies": ["读书", "旅游"]}');

-- 查询 JSON
SELECT username, metadata->>'city' AS city FROM users;
SELECT username FROM users WHERE metadata->>'city' = '北京';

-- JSON 数组
SELECT username FROM users WHERE metadata->'hobbies' ? '读书';

-- JSONB 索引
CREATE INDEX idx_metadata_city ON users 
    ((metadata->>'city'));

数组操作

-- 数组查询
SELECT * FROM users WHERE '读书' = ANY(tags);
SELECT * FROM users WHERE tags && ARRAY['读书', '运动'];

-- 数组函数
SELECT array_length(tags, 1) FROM users;
SELECT unnest(tags) FROM users;

🔍 窗口函数

-- 排名
SELECT 
    username, 
    age,
    ROW_NUMBER() OVER (ORDER BY age DESC) as row_num,
    RANK() OVER (ORDER BY age DESC) as rank,
    DENSE_RANK() OVER (ORDER BY age DESC) as dense_rank
FROM users;

-- 分区排名
SELECT 
    department,
    username,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

-- 移动平均
SELECT 
    date,
    sales,
    AVG(sales) OVER (
        ORDER BY date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg
FROM daily_sales;

🔄 CTE 递归查询

-- 递归查询组织架构
WITH RECURSIVE org_tree AS (
    -- 基础查询：顶层
    SELECT id, name, parent_id, 1 as level
    FROM departments
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归查询：子层
    SELECT d.id, d.name, d.parent_id, ot.level + 1
    FROM departments d
    INNER JOIN org_tree ot ON d.parent_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, id;

📊 性能优化

EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

输出解读：

• Seq Scan - 顺序扫描（全表扫描）
• Index Scan - 索引扫描
• Bitmap Index Scan - 位图索引扫描
• cost - 查询代价
• rows - 预估行数
• actual time - 实际执行时间

索引类型

-- B-tree（默认）- 适合等值和范围查询
CREATE INDEX idx_age ON users(age);

-- Hash - 只适合等值查询
CREATE INDEX idx_username ON users USING HASH(username);

-- GIN - 适合数组、JSONB、全文搜索
CREATE INDEX idx_tags ON users USING GIN(tags);
CREATE INDEX idx_metadata ON users USING GIN(metadata);

-- GiST - 适合几何类型、全文搜索
CREATE INDEX idx_location ON places USING GIST(location);

-- 部分索引
CREATE INDEX idx_active_users ON users(username) WHERE active = true;

-- 表达式索引
CREATE INDEX idx_lower_email ON users(LOWER(email));

🔒 事务与锁

-- 事务
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 显式锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;  -- 行锁

🔍 全文搜索

-- 创建全文搜索索引
CREATE INDEX idx_content_search ON articles 
    USING GIN(to_tsvector('chinese', content));

-- 全文搜索
SELECT title, content
FROM articles
WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', '数据库');

-- 排序结果
SELECT 
    title,
    ts_rank(to_tsvector('chinese', content), query) AS rank
FROM articles, to_tsquery('chinese', '数据库') query
WHERE to_tsvector('chinese', content) @@ query
ORDER BY rank DESC;

💡 最佳实践

1. 数据类型选择

   • 使用 SERIAL/BIGSERIAL 作为主键
   • 用 TEXT 代替 VARCHAR（性能相同）
   • JSONB 优于 JSON
   • 时间使用 TIMESTAMPTZ

2. 索引策略

   • 经常查询的字段添加索引
   • 使用部分索引减少索引大小
   • JSONB 字段使用 GIN 索引
   • 定期 VACUUM 和 ANALYZE

3. 查询优化

   • 使用 EXPLAIN ANALYZE 分析
   • 避免 SELECT *
   • 使用 CTE 提高可读性
   • 合理使用窗口函数

4. 安全

   • 使用参数化查询
   • 最小权限原则
   • 定期备份（pg_dump）

📖 学习资源

官方资源

• PostgreSQL 官方文档
• PostgreSQL 中文社区

推荐书籍

• 《PostgreSQL 实战》
• 《PostgreSQL 即学即用》
• 《PostgreSQL 数据库内核分析》

扩展推荐

• PostGIS - 地理信息系统
• TimescaleDB - 时序数据库
• pgvector - 向量搜索
• pg_cron - 定时任务

---

准备好了吗？开始你的 PostgreSQL 学习之旅！