从零入门到高级特性实战

1. 什么是 PostgreSQL？

PostgreSQL 常被称为"最先进的开源数据库"。与 MySQL 相比，它更严格遵循 SQL 标准，同时支持对象关系型特性（表继承、自定义类型、JSON 原生操作等）。

为什么选择 PostgreSQL？

• ACID 完全兼容：事务可靠性优于 MySQL 的 MyISAM
• JSONB：JSON 可建索引，兼具关系型和文档型优势
• 窗口函数：高级数据分析不可或缺
• PostGIS：地理空间数据的事实标准
• 自定义扩展：支持 Python/R/JavaScript 写函数

2. Schema 设计实战

CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    content TEXT,
    tags VARCHAR(50)[] DEFAULT '{}',        -- 数组字段
    metadata JSONB DEFAULT '{}',            -- 灵活 JSON
    published BOOLEAN DEFAULT false,
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- GIN 索引加速 JSONB 和数组查询
CREATE INDEX idx_posts_tags ON blog_posts USING GIN(tags);
CREATE INDEX idx_posts_metadata ON blog_posts USING GIN(metadata);

3. JSONB 高级操作

-- 插入含 JSON 元数据
INSERT INTO blog_posts (title, slug, content, tags, metadata)
VALUES (
    'PostgreSQL 入门指南',
    'pg-intro-2024',
    'PostgreSQL 是全球最先进的开源数据库...',
    ARRAY['PostgreSQL', 'SQL', '数据库'],
    '{"read_time": 15, "difficulty": "beginner", "series": "DB Basics"}'
);

-- JSONB 字段查询
SELECT title FROM blog_posts
WHERE metadata @> '{"difficulty": "beginner"}';

-- JSONB 提取
SELECT
    title,
    metadata->>'read_time' AS read_minutes,
    metadata->>'difficulty' AS level
FROM blog_posts;

-- JSONB 更新
UPDATE blog_posts
SET metadata = metadata || '{"updated": true, "version": 2}'::jsonb
WHERE id = 1;

4. 窗口函数数据排名

-- 模拟学生分数
CREATE TABLE scores (student VARCHAR(20), subject VARCHAR(20), score INT);
INSERT INTO scores VALUES
    ('小明', '语文', 88), ('小明', '数学', 92), ('小明', '英语', 85),
    ('小红', '语文', 95), ('小红', '数学', 89), ('小红', '英语', 91),
    ('小刚', '语文', 76), ('小刚', '数学', 82), ('小刚', '英语', 78);

-- RANK：各科排名（同分同排名，后续跳跃）
SELECT student, subject, score,
    RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rank
FROM scores;

-- 累积分布
SELECT student,
    SUM(score) AS total,
    PERCENT_RANK() OVER (ORDER BY SUM(score)) AS pct
FROM scores
GROUP BY student;

5. CTE 递归查询（组织架构树）

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'CEO', NULL), (2, 'CTO', 1), (3, 'CFO', 1),
    (4, '工程师A', 2), (5, '工程师B', 2), (6, '会计A', 3);

-- 递归查询所有下属
WITH RECURSIVE hierarchy AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE id = 1  -- CEO
    UNION ALL
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
)
SELECT repeat('  ', level-1) || name AS org_chart
FROM hierarchy;

6. 性能优化

-- 查看慢查询
-- 在 postgresql.conf 中设置：log_min_duration_statement = 1000

-- 分析查询计划
EXPLAIN ANALYZE
SELECT * FROM blog_posts WHERE tags @> ARRAY['PostgreSQL'];

-- 常用维护
VACUUM ANALYZE;           -- 回收空间 + 更新统计
REINDEX DATABASE hello_pg; -- 重建索引

思考题

1. PostgreSQL 的 JSONB 和 JSON 类型有什么区别？何时用哪个？
2. CTE 递归查询 vs 应用层循环，各有什么优劣？
3. VACUUM 为什么在 PostgreSQL 中如此重要？

推荐资源

• 官方文档：https://www.postgresql.org/docs/current/
• PostGIS：https://postgis.net/
• pgAdmin（图形管理工具）