PostgreSQL 完全指南

官方文档：https://www.postgresql.org/docs/
适用版本：PostgreSQL 16（2026-05-07 核实）

相关文档：MySQL基础完全指南 SQLAlchemy完全指南 SQLModel完全指南

1. 基础概念

PostgreSQL vs MySQL

安装（Docker，推荐）

# 启动 PostgreSQL 容器
docker run -d \
  --name postgres \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_PASSWORD=mypassword \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  postgres:16

# 连接
psql -h localhost -U myuser -d mydb
# 或
docker exec -it postgres psql -U myuser -d mydb

2. 数据库与表管理

常用 psql 命令

DDL 基础

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

-- 创建表
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,              -- 自增主键（等同于 INTEGER + SEQUENCE）
    -- 或用 BIGSERIAL（大整数）/ IDENTITY（SQL 标准写法）
    uuid        UUID DEFAULT gen_random_uuid(),  -- UUID 主键
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    age         SMALLINT CHECK (age >= 0 AND age <= 150),
    bio         TEXT,
    score       NUMERIC(5, 2),                   -- 总5位，小数2位
    tags        TEXT[],                          -- 数组类型
    metadata    JSONB,                           -- JSON（推荐 JSONB，可索引）
    is_active   BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMPTZ DEFAULT NOW(),       -- 带时区时间戳（推荐）
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

-- 修改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN bio TO description;

3. 数据类型速查

4. 索引

创建索引

-- B-Tree 索引（默认，适合等值/范围查询）
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created ON users(created_at DESC);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 复合索引（字段顺序很重要：等值条件列在前，范围条件列在后）
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 部分索引（只索引满足条件的行，节省空间）
CREATE INDEX idx_active_users ON users(email) WHERE is_active = TRUE;

-- 表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- 配合查询：WHERE LOWER(email) = '[email protected]'

-- 并发创建索引（不锁表，生产环境推荐）
CREATE INDEX CONCURRENTLY idx_users_name ON users(name);

GIN 索引（JSONB / 数组 / 全文搜索）

-- JSONB 字段的 GIN 索引
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

-- 数组字段的 GIN 索引
CREATE INDEX idx_users_tags ON users USING GIN(tags);

-- 全文搜索 GIN 索引
CREATE INDEX idx_articles_fts ON articles USING GIN(to_tsvector('chinese', title || ' ' || content));

5. 常用查询技巧

JSONB 查询

-- 查询 JSONB 字段中的值
SELECT * FROM users WHERE metadata->>'city' = '北京';
SELECT * FROM users WHERE metadata->'address'->>'city' = '北京';

-- 检查 key 是否存在
SELECT * FROM users WHERE metadata ? 'phone';

-- 包含查询（需要 GIN 索引）
SELECT * FROM users WHERE metadata @> '{"role": "admin"}';

-- 更新 JSONB 字段
UPDATE users SET metadata = metadata || '{"verified": true}' WHERE id = 1;
UPDATE users SET metadata = jsonb_set(metadata, '{address,city}', '"上海"') WHERE id = 1;

数组查询

-- 包含某个元素
SELECT * FROM users WHERE 'python' = ANY(tags);

-- 包含所有指定元素（数组包含）
SELECT * FROM users WHERE tags @> ARRAY['python', 'sql'];

-- 数组有交集
SELECT * FROM users WHERE tags && ARRAY['python', 'golang'];

-- 追加元素
UPDATE users SET tags = array_append(tags, 'rust') WHERE id = 1;

-- 移除元素
UPDATE users SET tags = array_remove(tags, 'php') WHERE id = 1;

CTE（WITH 子句）

-- 基础 CTE
WITH recent_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT u.name, ro.order_count, ro.total
FROM users u
JOIN recent_orders ro ON u.id = ro.user_id
ORDER BY ro.total DESC
LIMIT 10;

-- 递归 CTE（用于树形结构）
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;

窗口函数

-- 排名
SELECT
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM students;

-- 分组排名（每个部门内排名）
SELECT
    department,
    name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- 移动平均
SELECT
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- 过去 7 天均值
    ) AS moving_avg_7d
FROM daily_stats;

-- 累计求和
SELECT
    date,
    revenue,
    SUM(revenue) OVER (ORDER BY date) AS cumulative_revenue
FROM daily_stats;

-- LAG / LEAD（访问相邻行）
SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) AS day_over_day_change
FROM daily_stats;

UPSERT（INSERT ON CONFLICT）

-- 存在则更新，不存在则插入
INSERT INTO users (email, name, updated_at)
VALUES ('[email protected]', 'Alice', NOW())
ON CONFLICT (email) DO UPDATE SET
    name = EXCLUDED.name,
    updated_at = EXCLUDED.updated_at;

-- 存在则忽略
INSERT INTO user_tags (user_id, tag)
VALUES (1, 'python')
ON CONFLICT (user_id, tag) DO NOTHING;

6. 全文搜索

-- 创建全文搜索向量列
ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

-- 填充向量（英文）
UPDATE articles SET search_vector = to_tsvector('english', title || ' ' || content);

-- 创建 GIN 索引
CREATE INDEX idx_articles_fts 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 title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'python & tutorial') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

7. 性能优化

EXPLAIN ANALYZE

-- 查看执行计划（ANALYZE 实际执行，Buffers 查看缓存命中）
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = '[email protected]';

-- 关键指标：
-- Seq Scan：全表扫描（慢），应改为 Index Scan
-- Index Scan：走索引（好）
-- rows=1000 actual rows=50000：估算偏差大，需要 ANALYZE 更新统计
-- cost=0.00..100.00：第一行..最后一行的代价

统计信息更新

-- 更新表统计信息（优化器依赖）
ANALYZE users;
ANALYZE;  -- 更新所有表

-- 整理表（回收死元组，更新统计）
VACUUM ANALYZE users;

常见优化点

-- 慢查询日志（postgresql.conf）
-- log_min_duration_statement = 1000  -- 记录超过 1 秒的查询

-- 查看当前正在执行的查询
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND query_start < NOW() - INTERVAL '5 seconds'
ORDER BY duration DESC;

-- 查看表大小
SELECT
    relname AS table,
    pg_size_pretty(pg_total_relation_size(relid)) AS total,
    pg_size_pretty(pg_relation_size(relid)) AS data,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS indexes
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

8. Python 连接（asyncpg + SQLAlchemy）

# 推荐：通过 SQLAlchemy / SQLModel 使用
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://user:password@localhost:5432/mydb",
    pool_size=10,          # 连接池大小
    max_overflow=20,       # 超出 pool_size 后最多额外创建
    pool_timeout=30,       # 等待连接的超时（秒）
    pool_recycle=1800,     # 连接最大存活时间（秒），防止被服务器断开
    echo=False,
)

# 直接用 asyncpg（更底层，性能更好）
import asyncpg

async def main():
    conn = await asyncpg.connect("postgresql://user:pass@localhost/mydb")
    rows = await conn.fetch("SELECT * FROM users WHERE age > $1", 18)
    for row in rows:
        print(dict(row))
    await conn.close()

# 连接池（推荐）
pool = await asyncpg.create_pool(
    "postgresql://user:pass@localhost/mydb",
    min_size=5,
    max_size=20,
)
async with pool.acquire() as conn:
    await conn.execute("INSERT INTO users(name) VALUES($1)", "Alice")

最佳实践

始终使用 TIMESTAMPTZ 而非 TIMESTAMP：TIMESTAMP 不存储时区，跨时区系统会出现时间错乱。TIMESTAMPTZ 内部统一存储为 UTC，读取时按连接时区自动转换。

-- 正确：带时区时间戳
created_at TIMESTAMPTZ DEFAULT NOW()

-- 错误：无时区，跨时区部署时出问题
created_at TIMESTAMP DEFAULT NOW()

用 JSONB 而非 JSON：JSON 存储原始文本，每次访问都需解析；JSONB 存储二进制格式，支持 GIN 索引，查询性能高出数倍。唯一区别是 JSONB 不保留键的顺序和重复键。

-- 正确
metadata JSONB

-- 低效
metadata JSON

建索引使用 CONCURRENTLY 避免锁表：在生产库大表上 CREATE INDEX 会锁住整张表，用 CONCURRENTLY 可以在不阻塞读写的情况下构建索引（耗时更长，但对业务零影响）。

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

UPSERT 用 ON CONFLICT 替代先查后插：先 SELECT 后 INSERT 存在竞态条件（两个并发事务都查到不存在然后都插入），ON CONFLICT 是原子操作。

-- 正确：原子操作
INSERT INTO users (email, name) VALUES ($1, $2)
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- 错误：有竞态条件
-- SELECT → (不存在) → INSERT  并发时可能两个都插入

复合索引列顺序遵循左前缀原则：复合索引 (a, b, c) 可以加速 WHERE a=?、WHERE a=? AND b=?、WHERE a=? AND b=? AND c=?，但无法加速单独的 WHERE b=? 或 WHERE c=?。等值条件列放前面，范围条件列放后面。

-- 正确：等值在前，范围在后
CREATE INDEX idx_orders ON orders(user_id, status, created_at DESC);

-- 低效：范围在前，等值在后
CREATE INDEX idx_orders ON orders(created_at, user_id);

asyncpg 连接池关键参数：

pool = await asyncpg.create_pool(
    "postgresql://user:pass@localhost/mydb",
    min_size=5,
    max_size=20,
    max_inactive_connection_lifetime=300.0,
    command_timeout=60.0,
)

常见陷阱

陷阱：LIKE 前缀通配符导致全表扫描

现象： WHERE name LIKE '%alice%' 查询慢，EXPLAIN 显示 Seq Scan。

原因： B-Tree 索引是按字典序排列的有序结构，前缀通配符 % 意味着不知道从哪里开始扫描，索引失效。

解决： 后缀通配符 LIKE 'alice%' 可以走 B-Tree。任意位置模糊搜索需要 pg_trgm 扩展的 GIN 索引，或使用全文搜索。

-- 走索引
WHERE name LIKE 'alice%'

-- 不走索引 → 改用 pg_trgm
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING GIN(name gin_trgm_ops);
WHERE name ILIKE '%alice%'   -- 此时走 GIN 索引

陷阱：SERIAL 主键有间隙被误以为数据丢失

现象： 表中主键不连续（1, 2, 5, 6...），怀疑数据被删除或丢失。

原因： SERIAL 底层是独立的 SEQUENCE 对象，序列号一旦分配，事务回滚也不会归还。高并发插入、失败事务都会消耗序列号，产生间隙。

解决： 这是 PostgreSQL 的设计行为，不是 bug。业务逻辑不应依赖主键连续。如需严格连续编号，用应用层生成或额外的计数表（会带来并发瓶颈）。

陷阱：忘记 VACUUM 导致表膨胀

现象： UPDATE / DELETE 大量数据后，pg_total_relation_size() 返回的表大小不减反增，查询越来越慢。

原因： PostgreSQL 的 MVCC 机制不会立即删除旧版本行（死元组），而是等待 VACUUM 回收。高频更新的表若 autovacuum 不及时，死元组堆积会使索引和表文件持续膨胀。

解决： 监控死元组数量，必要时手动执行 VACUUM ANALYZE；对高频更新表调低 autovacuum 阈值。

-- 查看死元组数量
SELECT relname, n_dead_tup, n_live_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- 手动清理
VACUUM ANALYZE users;

-- 对特定表强化 autovacuum
ALTER TABLE high_update_table SET (
    autovacuum_vacuum_scale_factor = 0.01,   -- 默认 0.2（20% 死元组才触发）
    autovacuum_analyze_scale_factor = 0.005
);

陷阱：连接数耗尽导致服务拒绝新连接

现象： 应用日志报 FATAL: sorry, too many clients already。

原因： PostgreSQL 默认 max_connections=100，每个连接占用约 5–10MB 内存。应用未使用连接池，或连接池 max_size 设置过大。

解决： 使用 asyncpg / SQLAlchemy 的连接池，并在数据库前部署 PgBouncer 连接池代理。

# postgresql.conf
max_connections = 200          # 根据内存调整，通常不超过 300

# PgBouncer 配置（应对高并发）
pool_mode = transaction        # 事务级连接复用
max_client_conn = 1000
default_pool_size = 20

参见

• MySQL基础完全指南
• MySQL高级优化
• SQLAlchemy完全指南
• SQLModel完全指南
• 数据库设计规范
• DuckDB完全指南