第一章：PostgreSQL 为什么仍然是最好的通用数据库

在数据库选型的讨论中，PostgreSQL 几乎总是出现在最终候选里。这不是惯性，而是有充分理由的。

---

一、数据库选型的真实问题

每当团队开始一个新项目，总有人问：用 PostgreSQL 还是 MySQL？用关系型还是 MongoDB？用 Supabase 还是 PlanetScale？

这些问题的答案取决于你的具体需求，但有一个规律：绝大多数 Web 应用（> 90%）的数据层需求，PostgreSQL 都能胜任，且通常比其他选择更好。

理解为什么，需要从 PostgreSQL 的架构设计说起。

---

二、MVCC——PostgreSQL 并发的基础

MVCC（多版本并发控制，Multi-Version Concurrency Control）是 PostgreSQL 并发模型的核心。

传统锁机制 vs MVCC

传统锁机制：

读操作：加共享锁（其他读可以，写阻塞）
写操作：加排他锁（读和写都阻塞）

问题：高并发时，读写互相阻塞，性能下降

MVCC 的思路：

每次修改不是覆盖旧数据，而是创建新版本
每个事务看到的是事务开始时的快照
读操作不阻塞写，写操作不阻塞读

具体实现

-- PostgreSQL 的每一行都有两个隐藏字段
-- xmin: 创建这个版本的事务 ID
-- xmax: 删除（或覆盖）这个版本的事务 ID（0 表示未删除）

-- 当你执行 UPDATE
UPDATE users SET name = 'Bob' WHERE id = 1;

-- PostgreSQL 实际做的是：
-- 1. 给旧行设置 xmax = 当前事务ID（标记为删除）
-- 2. 创建新行，xmin = 当前事务ID，name = 'Bob'
-- 3. 旧行不立即物理删除，由 VACUUM 异步清理

对开发者的影响

好处：

-- 长时间的只读查询不会被写操作阻塞
-- 这对报表查询极其重要

-- 事务A：正在运行复杂报表（可能需要 30 秒）
SELECT COUNT(*), SUM(amount) FROM orders WHERE created_at > '2024-01-01';

-- 同时，事务B：正常写入订单（不会被报表查询阻塞）
INSERT INTO orders VALUES (...);

需要注意的副作用：

-- 问题：MVCC 导致表膨胀
-- 已删除/已更新的行版本不会立即释放空间
-- 需要 VACUUM 定期清理

-- 查看表膨胀情况
SELECT
    schemaname,
    tablename,
    n_dead_tup,           -- 死行数量
    n_live_tup,           -- 活跃行数量
    round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) as dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- 手动触发 VACUUM（通常自动 VACUUM 足够）
VACUUM ANALYZE orders;

---

三、PostgreSQL vs MySQL——关键差异

查询功能

PostgreSQL 支持比 MySQL 更丰富的查询特性：

-- 1. Window Functions（MySQL 8.0 才支持，PG 很早支持）
SELECT
    user_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS running_total,
    RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS amount_rank
FROM orders;

-- 2. Recursive CTE（MySQL 8.0+ 支持，PG 一直支持）
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 AS depth
    FROM categories WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

-- 3. LATERAL JOIN（MySQL 不支持）
SELECT u.id, u.name, recent_orders.*
FROM users u
CROSS JOIN LATERAL (
    SELECT id, amount, created_at
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) recent_orders;

数据类型

PostgreSQL 的数据类型比 MySQL 丰富得多：

-- JSONB：真正的 JSON 数据库能力
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB  -- 可以建索引，可以查询内部字段
);

-- 在 JSONB 字段上建索引
CREATE INDEX idx_products_category ON products USING GIN (attributes);

-- 查询 JSONB 内部字段
SELECT name, attributes->>'color' AS color
FROM products
WHERE attributes @> '{"category": "electronics"}';

-- 数组类型
CREATE TABLE tags (
    post_id INT,
    tags TEXT[]  -- 原生数组类型
);

-- 数组操作
SELECT * FROM tags WHERE 'postgresql' = ANY(tags);
SELECT * FROM tags WHERE tags && ARRAY['database', 'sql'];  -- 数组交集

-- UUID
CREATE TABLE users (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    ...
);

-- 范围类型
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INT,
    during TSTZRANGE  -- 时间范围类型
);

-- 查询重叠的预约（不需要写复杂的 BETWEEN 逻辑）
SELECT * FROM reservations
WHERE during && '[2024-01-15, 2024-01-20)'::tstzrange;

-- 自动防止冲突（使用 EXCLUDE 约束）
ALTER TABLE reservations ADD CONSTRAINT no_overlap
EXCLUDE USING GIST (room_id WITH =, during WITH &&);

全文搜索

-- PostgreSQL 内置全文搜索，很多情况下不需要 Elasticsearch
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector TSVECTOR  -- 预计算的搜索向量
);

-- 自动更新搜索向量
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);
CREATE TRIGGER update_search_vector
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION
    tsvector_update_trigger(search_vector, 'pg_catalog.english', title, content);

-- 全文搜索
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'postgresql database performance') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

---

四、JSONB——PostgreSQL 作为文档数据库

很多团队使用 MongoDB 是因为需要灵活的 Schema，但 PostgreSQL 的 JSONB 类型提供了同等灵活性，同时保留关系型数据库的优势。

JSONB vs JSON

-- JSON: 存储原始文本，读取时重新解析
-- JSONB: 解析后以二进制存储，支持索引，查询更快

-- 推荐始终用 JSONB，除非需要保留原始 JSON 格式（如保留空格）

实际使用场景

-- 电商产品的动态属性
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    base_price DECIMAL(10,2),
    specs JSONB DEFAULT '{}'
);

INSERT INTO products (name, base_price, specs) VALUES (
    'MacBook Pro M3',
    1999.00,
    '{
        "cpu": "Apple M3 Pro",
        "ram_gb": 18,
        "storage_gb": 512,
        "display": "16.2 inch",
        "colors": ["Space Black", "Silver"],
        "ports": {
            "usb_c": 3,
            "hdmi": 1,
            "sd_card": true
        }
    }'
);

-- 查询特定规格
SELECT name, specs->>'cpu' AS cpu
FROM products
WHERE (specs->>'ram_gb')::int >= 16;

-- 查询嵌套字段
SELECT name, specs->'ports'->>'hdmi' AS hdmi_count
FROM products
WHERE specs->'ports'->>'sd_card' = 'true';

-- 包含查询（使用 GIN 索引，速度快）
SELECT name FROM products
WHERE specs @> '{"colors": ["Silver"]}';

-- 更新 JSONB 中的特定字段
UPDATE products
SET specs = jsonb_set(specs, '{ram_gb}', '24')
WHERE id = 1;

---

五、PostGIS——地理信息数据库

PostgreSQL 通过 PostGIS 扩展成为世界上最强大的开源地理信息数据库：

-- 安装 PostGIS 扩展
CREATE EXTENSION postgis;

-- 创建包含地理信息的表
CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    name TEXT,
    location GEOMETRY(POINT, 4326)  -- 经纬度坐标
);

-- 插入数据
INSERT INTO stores (name, location) VALUES
    ('Store A', ST_MakePoint(-122.4194, 37.7749)),  -- 旧金山
    ('Store B', ST_MakePoint(-118.2437, 34.0522));   -- 洛杉矶

-- 查找 10 公里内的门店
SELECT name,
    ST_Distance(
        location::geography,
        ST_MakePoint(-122.4, 37.78)::geography
    ) / 1000 AS distance_km
FROM stores
WHERE ST_DWithin(
    location::geography,
    ST_MakePoint(-122.4, 37.78)::geography,
    10000  -- 10 公里，单位米
)
ORDER BY distance_km;

---

六、PostgreSQL 的商业生态

Supabase

Supabase 是 PostgreSQL 的 Backend-as-a-Service，提供：

• PostgreSQL 实例（自动扩缩容）
• 实时订阅（通过 PG 的 Logical Replication）
• 内置认证（Row Level Security）
• Edge Functions（Deno 运行时）

-- Supabase Row Level Security 示例
-- 每个用户只能看到自己的数据
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own orders"
    ON orders FOR SELECT
    USING (user_id = auth.uid());  -- auth.uid() 由 Supabase 注入

定价对比：

• 自建 PG（AWS RDS）：$50-200/月（单实例）
• Supabase Pro：$25/月（含 8GB 存储 + 自动备份）
• Neon（Serverless PG）：按使用量计费，最低 $0

Neon

Neon 是"Serverless PostgreSQL"，特点是存储与计算分离：

• 冷启动时间约 500ms（适合低频访问的应用）
• 存储按实际使用量计费（不是预分配）
• 分支功能（类似 Git 分支，每个 PR 可以有独立数据库）

# Neon CLI 创建数据库分支（开发工作流）
neon branches create --name feat-new-schema --parent main

# 在分支上测试迁移
neon connection-string feat-new-schema

# 测试通过后，合并到主分支
neon branches merge feat-new-schema

---

七、PostgreSQL vs 其他数据库的选型矩阵

需求	推荐方案	理由
通用 Web 应用	PostgreSQL	功能全面，生态成熟
简单 CRUD，团队熟悉 MySQL	MySQL 8.0	迁移成本低，功能差距已缩小
文档存储，Schema 极度灵活	PostgreSQL JSONB	无需引入 MongoDB
全文搜索（大规模）	PostgreSQL FTS 或 Elasticsearch	PG FTS 满足 80% 场景
时序数据	TimescaleDB（PG 扩展）或 InfluxDB	TimescaleDB 无需学习新查询语言
地理信息	PostgreSQL + PostGIS	业界标准
纯键值缓存	Redis	不要用 PG 做缓存
OLAP 分析（TB 级）	ClickHouse 或 Snowflake	PG 不是列存储，大规模分析慢
向量搜索（AI 嵌入）	PostgreSQL + pgvector	无需引入专门的向量数据库

---

八、快速上手：生产级 PostgreSQL 配置

默认的 PostgreSQL 配置是为了在各种硬件上安全启动，不是为了性能优化。生产环境必须调整：

-- postgresql.conf 关键配置（以 16GB RAM 的服务器为例）

-- 内存配置
shared_buffers = 4GB              -- 约 25% 的 RAM
effective_cache_size = 12GB       -- 约 75% 的 RAM（告诉查询优化器可用的缓存）
work_mem = 128MB                  -- 每个排序操作/Hash 操作的内存（注意：并发时累积）
maintenance_work_mem = 1GB        -- VACUUM、CREATE INDEX 的内存

-- WAL 配置（提高写入性能）
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

-- 连接配置
max_connections = 200             -- 不要设太高，每个连接占 ~5-10MB
-- 使用连接池（PgBouncer）而不是直接增加 max_connections

-- 查询规划
random_page_cost = 1.1           -- SSD 时设为 1.1（HDD 默认 4.0）
effective_io_concurrency = 200   -- SSD 并发 IO 能力

# 使用 pgbench 验证配置效果
pgbench -c 20 -j 4 -T 60 mydb

---

关键认知

PostgreSQL 的护城河是它的广度，而不是某一个特定功能：关系型查询、JSON 文档、全文搜索、地理信息、时序数据、向量搜索——它全都能做，且每个都做得足够好。

这意味着对于 90% 的应用，你可以用一个数据库满足所有需求，而不是维护一个 MySQL + MongoDB + Elasticsearch + Redis 的复杂栈。

“如果你不确定该用什么数据库，先用 PostgreSQL。需要换的时候，再换。”