PostgreSQL模式Skill postgres-patterns

PostgreSQL模式

问题陈述

Alembic生成迁移，但不了解PostgreSQL性能影响。这项技能涵盖了审查PostgreSQL特定问题的迁移和编写高效查询。

---

模式：索引审查

何时添加索引

-- ✅ 添加索引：外键（几乎总是）
CREATE INDEX ix_assessments_user_id ON assessments (user_id);

-- ✅ 添加索引：经常过滤的列
CREATE INDEX ix_assessments_status ON assessments (status);

-- ✅ 添加索引：WHERE + ORDER BY中的列一起
CREATE INDEX ix_assessments_user_status ON assessments (user_id, status);

-- ✅ 添加索引：JOIN条件中使用的列
CREATE INDEX ix_answers_question_id ON answers (question_id);

何时不添加索引

-- ❌ 跳过：小表（< 1000行）
-- ❌ 跳过：写入繁重的表，很少读取
-- ❌ 跳过：单独的低基数列（布尔值，3个值的状态）
-- ❌ 跳过：很少用于WHERE/JOIN/ORDER BY的列

索引列顺序很重要

-- 对于查询：WHERE user_id = ? AND status = ? ORDER BY created_at
-- ✅ 正确：最有选择性的列先，ORDER BY列最后
CREATE INDEX ix_assessments_user_status_created 
ON assessments (user_id, status, created_at);

-- ❌ 错误：顺序与查询模式不匹配
CREATE INDEX ix_assessments_created_status_user 
ON assessments (created_at, status, user_id);

---

模式：部分索引

问题： 全部索引在列上，你只查询值的子集。

-- 全部索引（索引所有行）
CREATE INDEX ix_assessments_status ON assessments (status);

-- ✅ 更好：部分索引（仅活动评估）
CREATE INDEX ix_assessments_active 
ON assessments (user_id, created_at) 
WHERE status = 'active';

-- 使用案例："按日期排序获取用户的活动评估"
-- 部分索引更小且更快

-- 常见模式：
-- WHERE deleted_at IS NULL（软删除）
-- WHERE status != 'archived'
-- WHERE is_active = true

在Alembic中：

op.execute("""
    CREATE INDEX ix_assessments_active 
    ON assessments (user_id, created_at) 
    WHERE status = 'active'
""")

---

模式：JSONB索引

-- GIN索引用于@>（包含）查询
CREATE INDEX ix_settings_data ON user_settings USING GIN (data);

-- 查询：查找具有特定设置的用户
SELECT * FROM user_settings WHERE data @> '{"theme": "dark"}';

-- 表达式索引用于特定JSON路径
CREATE INDEX ix_settings_theme ON user_settings ((data->>'theme'));

-- 查询：按特定键查找
SELECT * FROM user_settings WHERE data->>'theme' = 'dark';

---

模式：并发索引创建

问题： CREATE INDEX锁定表。在大表上，这会阻塞写入。

-- ❌ 在创建期间阻塞写入
CREATE INDEX ix_events_user_id ON events (user_id);

-- ✅ 不阻塞（但创建速度慢）
CREATE INDEX CONCURRENTLY ix_events_user_id ON events (user_id);

在Alembic中：

# 必须禁用CONCURRENTLY的事务
def upgrade():
    op.execute("COMMIT")  # 结束当前事务
    op.execute(
        "CREATE INDEX CONCURRENTLY ix_events_user_id ON events (user_id)"
    )

---

模式：查询性能分析

-- EXPLAIN ANALYZE显示实际执行
EXPLAIN ANALYZE 
SELECT * FROM assessments 
WHERE user_id = 'abc-123' AND status = 'active';

-- 需要查找的内容：
-- ✅ "Index Scan"或"Index Only Scan" - 好的
-- ❌ 大表上的"Seq Scan" - 需要索引
-- ❌ 高成本的"Sort" - 考虑在ORDER BY列上添加索引
-- ❌ 许多行的"Nested Loop" - 可能需要不同的连接策略

关键指标：

• cost: 估计单位（越低越好）
• rows: 估计行数
• actual time: 实际毫秒数
• loops: 执行次数

---

模式：UUID性能

-- UUID作为主键有权衡
-- ❌ 随机UUID（uuid4）导致索引碎片化
-- ✅ 时间顺序UUID（uuid7）保持插入顺序

-- 如果使用uuid4，考虑：
-- 1. BRIN索引用于时间顺序查询（如果有created_at）
-- 2. 覆盖索引以避免堆取回
-- 3. 接受一些碎片化（通常在1000万行以下没问题）

---

模式：约束审查

-- ✅ 好的：命名约束（可以删除/修改）
ALTER TABLE assessments 
ADD CONSTRAINT fk_assessments_user_id 
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- ❌ 坏的：未命名约束（自动生成的名字很丑）
ALTER TABLE assessments 
ADD FOREIGN KEY (user_id) REFERENCES users(id);

-- ✅ 好的：数据完整性的CHECK约束
ALTER TABLE assessments 
ADD CONSTRAINT chk_assessments_rating 
CHECK (rating >= 1.0 AND rating <= 5.5);

-- ✅ 好的：有意义的名称的唯一约束
ALTER TABLE users 
ADD CONSTRAINT uq_users_email UNIQUE (email);

---

模式：批量操作

-- ❌ 慢：逐行更新
UPDATE users SET role = 'member' WHERE id = 'id1';
UPDATE users SET role = 'member' WHERE id = 'id2';
-- ... 数千更多

-- ✅ 快：批量更新
UPDATE users SET role = 'member' 
WHERE id IN ('id1', 'id2', 'id3', ...);

-- ✅ 快：使用子查询更新
UPDATE users SET role = 'member'
WHERE id IN (
    SELECT user_id FROM legacy_members WHERE migrated = false
);

-- 对于非常大的更新，分批以避免长时间锁定：
UPDATE users SET role = 'member'
WHERE id IN (
    SELECT id FROM users 
    WHERE role IS NULL 
    LIMIT 10000
);
-- 直到没有行受影响为止循环运行

---

模式：表锁定意识

知道什么锁定了什么：

操作	锁定类型	阻塞
SELECT	AccessShare	无
INSERT/UPDATE/DELETE	RowExclusive	无（行级）
CREATE INDEX	ShareLock	INSERT/UPDATE/DELETE
CREATE INDEX CONCURRENTLY	ShareUpdateExclusive	其他模式更改
ALTER TABLE （大多数）	AccessExclusive	一切
DROP TABLE	AccessExclusive	一切

危险区域：

-- ❌ 锁定整个表
ALTER TABLE users ADD COLUMN bio TEXT NOT NULL DEFAULT '';

-- ✅ 最小锁定（PostgreSQL 11+）
ALTER TABLE users ADD COLUMN bio TEXT;  -- 快速，可空
-- 然后分批回填UPDATE
-- 然后：ALTER TABLE users ALTER COLUMN bio SET NOT NULL;

---

模式：连接管理

-- 检查活动连接
SELECT 
    datname,
    usename,
    application_name,
    state,
    query_start,
    query
FROM pg_stat_activity
WHERE datname = 'your_db';

-- 终止长时间运行的查询
SELECT pg_cancel_backend(pid);  -- 优雅
SELECT pg_terminate_backend(pid);  -- 强制

-- 检查锁定
SELECT 
    l.locktype,
    l.relation::regclass,
    l.mode,
    l.granted,
    a.usename,
    a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

---

模式：数据类型选择

使用案例	类型	注释
主键	UUID	如果可能，使用uuid7进行排序
外键	匹配父类型
时间戳	TIMESTAMPTZ	总是带时区
货币	NUMERIC(12,2)	从不使用FLOAT
JSON数据	JSONB	不是JSON（JSONB更快）
短字符串	VARCHAR(n)	合理限制
长文本	TEXT	无长度限制
布尔值	BOOLEAN	不是整数
枚举类	VARCHAR或原生ENUM	VARCHAR更灵活

---

迁移审查清单（PostgreSQL特定）

• [ ] 大表索引使用CONCURRENTLY
• [ ] 外键指定ON DELETE行为
• [ ] 约束有显式名称
• [ ] 现有表的非空列使用3步过程
• [ ] 索引与实际查询模式匹配
• [ ] 考虑为过滤查询添加部分索引
• [ ] 小表上没有不必要的索引
• [ ] JSONB列有适当的GIN索引，如果被查询
• [ ] UUID：意识到碎片化的影响
• [ ] 所有时间戳使用TIMESTAMPTZ（而不是TIMESTAMP）

---

有用的诊断查询

-- 表大小
SELECT 
    relname as table,
    pg_size_pretty(pg_total_relation_size(relid)) as total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- 索引使用情况
SELECT 
    indexrelname as index,
    idx_scan as times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;  -- 未使用的索引在顶部

-- 慢查询（如果启用了pg_stat_statements）
SELECT 
    query,
    calls,
    mean_exec_time,
    total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;