postgres-patterns

postgres-patterns Skill 多维度简评

类别：数据库 / 后端 / Supabase 仓库：affaan-m/everything-claude-code 维护者：Affaan Mustafa / ECC 社区

一、核心定位与价值

postgres-patterns 是 ECC 中最实用的数据库 Skill 之一——PostgreSQL 是 web 应用最主流的 OLTP 数据库，AI 写 SQL 时经常犯的错（错误数据类型、缺索引、低效分页）会被本 Skill 强制纠正。

覆盖范围：

• 索引策略（B-tree、GIN、BRIN、partial、covering）
• 数据类型选型（bigint、timestamptz、numeric、text、uuid）
• 反模式检测（SQL 一键查询）
• Supabase 安全（RLS、连接池、安全默认值）
• 性能参数（work_mem、max_connections、timeout）
• 高效分页（游标 vs OFFSET）
• UPSERT（ON CONFLICT）

适用场景

• 写或重构 SQL / 视图 / 存储过程
• 设计 Schema（字段类型、主键、外键、索引）
• 解决慢查询、表膨胀
• 实现 Row Level Security（Supabase）
• 配合 database-reviewer Agent 全库审查

不适用场景

• OLAP 场景（用 clickhouse-io）
• NoSQL（用 MongoDB / Redis 专属 Skill）
• 嵌入式数据库（SQLite 知识足够）
• 不熟悉 SQL 基础（先学再上 Skill）

二、索引模式速查

2.1 索引类型决策表

2.2 复合索引顺序（黄金规则）

-- ✅ 等值字段在前，范围字段在后
CREATE INDEX idx ON orders (status, created_at);
-- 支持: WHERE status = 'pending' AND created_at > '2024-01-01'

-- ❌ 反了: 范围在前，等值在后，索引失效
CREATE INDEX idx ON orders (created_at, status);

2.3 索引维护

-- 查未使用索引
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey';

-- 查重复索引
SELECT a.indexname, b.indexname
FROM pg_indexes a, pg_indexes b
WHERE a.tablename = b.tablename
  AND a.indexname != b.indexname
  AND a.indexdef = b.indexdef;

-- 重建索引（少用，可能锁表）
REINDEX INDEX CONCURRENTLY idx_name;

三、数据类型选型

3.1 推荐类型速查

3.2 真实案例：避免金钱计算错误

-- ❌ BAD: float 累积误差
ALTER TABLE orders ADD COLUMN amount float;
-- 0.1 + 0.2 = 0.30000000000000004

-- ✅ GOOD: numeric
ALTER TABLE orders ADD COLUMN amount numeric(10,2);
-- 0.1 + 0.2 = 0.30 精确

3.3 真实案例：timestamptz 避免时区 bug

-- ❌ BAD: timestamp，跨时区混乱
CREATE TABLE events (created_at timestamp);

-- 美国用户写：2026-01-15 10:00:00
-- 中国用户读：2026-01-15 10:00:00 (但实际是 23:00 当地时间)

-- ✅ GOOD: timestamptz，存 UTC
CREATE TABLE events (created_at timestamptz DEFAULT now());
-- 美国用户写：2026-01-15 10:00:00-05
-- 中国用户读：2026-01-15 23:00:00+08 (自动转换)

四、反模式检测 SQL

4.1 检测未索引外键

-- 查所有没加索引的外键
SELECT
  conrelid::regclass AS table_name,
  a.attname AS column_name
FROM pg_constraint c
JOIN pg_attribute a
  ON a.attrelid = c.conrelid
  AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1
    FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND a.attnum = ANY(i.indkey)
  );

4.2 查慢查询

-- 需要先启用 pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT pg_reload_conf();

-- Top 10 慢查询
SELECT
  substring(query, 1, 100) AS query_preview,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(total_exec_time::numeric, 2) AS total_ms
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC
LIMIT 10;

4.3 查表膨胀

-- 表膨胀（死元组过多）
SELECT
  schemaname || '.' || relname AS table_name,
  n_live_tup,
  n_dead_tup,
  round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

4.4 查缺失索引

-- 顺序扫描次数多的表（可能缺索引）
SELECT
  schemaname || '.' || relname AS table_name,
  seq_scan,
  seq_tup_read,
  idx_scan,
  round(100.0 * seq_scan / NULLIF(seq_scan + idx_scan, 0), 2) AS seq_pct
FROM pg_stat_user_tables
WHERE seq_scan > 1000
ORDER BY seq_scan DESC;

4.5 查未使用索引

-- 几乎没被使用的索引（浪费）
SELECT
  schemaname || '.' || indexrelname AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan < 50
ORDER BY pg_relation_size(indexrelid) DESC;

五、Supabase 安全配置

5.1 安全默认值

-- 撤销 public schema 权限
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO authenticated;

-- 撤销默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

5.2 RLS（Row Level Security）

-- 启用 RLS
ALTER TABLE user_posts ENABLE ROW LEVEL SECURITY;

-- 用户只能看自己的
CREATE POLICY "Users can view own posts"
  ON user_posts
  FOR SELECT
  USING (auth.uid() = user_id);

-- 用户只能改自己的
CREATE POLICY "Users can update own posts"
  ON user_posts
  FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- 公开帖子所有人都能看
CREATE POLICY "Public posts are viewable by everyone"
  ON user_posts
  FOR SELECT
  USING (is_public = true);

5.3 性能配置

-- 限制最大连接数
ALTER SYSTEM SET max_connections = 100;

-- 每个查询的内存
ALTER SYSTEM SET work_mem = '8MB';

-- 事务超时
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';

-- 防止长查询
ALTER SYSTEM SET statement_timeout = '30s';

-- 重启或 reload
SELECT pg_reload_conf();

5.4 启用监控

-- pg_stat_statements（必备）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT pg_reload_conf();

-- pgstattuple（膨胀分析）
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- pg_trgm（模糊搜索）
CREATE EXTENSION IF NOT EXISTS pg_trgm;

六、高效分页与 UPSERT

6.1 游标分页（推荐）

-- ❌ BAD: OFFSET 慢
SELECT * FROM products
ORDER BY id
LIMIT 20 OFFSET 10000;
-- 扫描前 10020 行，扔掉前 10000

-- ✅ GOOD: 游标分页
SELECT * FROM products
WHERE id > $last_id
ORDER BY id
LIMIT 20;
-- 直接定位，无扫描浪费

6.2 UPSERT（INSERT or UPDATE）

INSERT INTO settings (user_id, key, value)
VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET
  value = EXCLUDED.value,
  updated_at = now();

6.3 批量 UPSERT

INSERT INTO products (sku, name, price)
VALUES
  ('sku1', 'Product 1', 9.99),
  ('sku2', 'Product 2', 19.99),
  ('sku3', 'Product 3', 29.99)
ON CONFLICT (sku)
DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price;

七、真实踩坑案例

案例 1：char vs varchar 误解

现象：用 char(10) 存用户名，查询不带空格出错。 根因：char(n) 总是补足到 n 个空格。 解决：用 text 或 varchar，PG 没性能差异。

案例 2：UUID v4 做主键性能差

现象：B-tree 索引膨胀，写入慢。 根因：UUID v4 完全随机，导致 B-tree 频繁分裂。 解决：用 UUID v7（带时间戳前缀），或 bigint + sequence。

案例 3：JSONB 字段没索引

现象：WHERE payload->>'country' = 'US' 全表扫描。 根因：JSONB 字段默认无索引。 解决：建 GIN 索引 CREATE INDEX i ON t USING gin (payload).

案例 4：RLS 性能灾难

现象：开了 RLS 后查询慢 10 倍。 根因：RLS 是 row-by-row 检查；复杂的 USING 表达式未走索引。 解决：用 (select auth.uid()) 包一层，让 planner 物化。

-- ❌ BAD: 每次 row 都调用 auth.uid()
USING (auth.uid() = user_id)

-- ✅ GOOD: 物化
USING ((select auth.uid()) = user_id)

案例 5：vacuum 没跑导致表膨胀

现象：磁盘满，查询慢。 根因：autovacuum 没启用或被禁用。 解决：检查 autovacuum 设置，手动 VACUUM FULL（锁表）或 VACUUM（不锁）。

案例 6：连接数爆掉

现象：FATAL: too many clients already。 根因：max_connections 设置过大或连接池未用。 解决：用 PgBouncer 限制连接数，应用层用连接池。

案例 7：full text search 慢

现象：LIKE '%keyword%' 跑了 30 秒。 根因：LIKE 前缀模糊不走索引。 解决：用 tsvector + GIN 索引。

ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;

CREATE INDEX idx_articles_search ON articles USING gin (search_vector);

-- 查询
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & performance');

案例 8：timestamp 范围查询走错索引

现象：WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31' 慢。 根因：复合索引顺序错了（status, created_at，但 WHERE 没有 status）。 解决：加 CREATE INDEX i ON t (created_at)，或用 partial index 优化。

案例 9：null 处理 bug

现象：WHERE deleted_at IS NULL 没走索引。 根因：NULL 在 PG 索引中是特殊值。 解决：用 partial index WHERE deleted_at IS NULL，或改用 deleted boolean 字段。

案例 10：money 类型陷阱

现象：用 money 类型，跨币种出错。 根因：money 是单币种固定类型。 解决：用 numeric(20, 4) + 单独 currency 字段。

八、性能基准

8.1 索引 vs 全表扫描（100 万行）

8.2 OFFSET vs 游标分页（10 亿行）

结论：深度分页必须用游标。

8.3 UPSERT vs INSERT+UPDATE

九、与其他 Skills 配合

完整工作流：

database-reviewer (全库扫描)
  ↓
postgres-patterns (匹配规则)
  ↓
输出报告 (按严重度)
  ↓
人工确认 + 修复
  ↓
verification-loop (验证)

十、5 条反合理化

十一、5 条实战技巧

1. 生产前必跑反模式检测 SQL：把上面的 4 条 SQL 集成到 CI
2. 启用 pg_stat_statements + auto_explain：自动捕获慢查询
3. 所有外键必加索引：写 migration 时强制
4. 数字主键用 bigint：避免 2038 年问题
5. 金额一律 numeric：不存为 float

十二、Q&A

Q: 必须订阅 Claude Code 吗？ A: Skill 在 Claude Sonnet/Opus 上效果最佳，其他 LLM 也可借鉴。

Q: 和 backend-patterns 区别？ A: postgres-patterns 聚焦 SQL 和数据库本身；backend-patterns 聚焦 API 层。

Q: 跟 clickhouse-io 关系？ A: Postgres = OLTP（事务），ClickHouse = OLAP（分析），可组合使用。

Q: 跟 Supabase CLI 关系？ A: Skill 提供”知识”，Supabase CLI 提供”工具”（migration、db push）。

Q: 老的 schema 改造适用吗？ A: 适合，配合 database-reviewer Agent 渐进式改造。

Q: 中文支持？ A: SQL 关键字英文，注释和字段名可中文。

Q: 学习曲线？ A: 中等。需懂 SQL 基础和 PG 概念。

十三、安装

# Claude Code
/plugin marketplace add affaan-m/everything-claude-code
/plugin install everything-claude-code@everything-claude-code
cp -r everything-claude-code/rules/common ~/.claude/rules/

# 通用
npx skills add affaan-m/everything-claude-code --skill postgres-patterns

十四、总结

核心价值：

• PostgreSQL 模式速查 + 反模式检测 SQL
• Supabase RLS 安全配置
• 索引策略、数据类型、高效分页
• 真实踩坑案例

适用人群：

• 后端开发者（Node + Supabase / Rails / Django / Spring）
• 数据工程师
• Supabase 项目
• AI 辅助生成 SQL 场景

投入产出比：⭐⭐⭐⭐⭐（5/5）—— 所有 PG 项目必装。

何时不要用：

• OLAP（用 clickhouse-io）
• NoSQL（MongoDB / Redis 专属）
• 嵌入式（SQLite 够用）
• 没用过 PG（先学基础）

配套文档：clickhouse-io 分析 | backend-patterns 后端 | database-reviewer Agent

参考资料

1. PostgreSQL 官方文档 — Index Types
2. Supabase Docs — Managing Indexes in Postgres
3. Supabase Row Level Security 指南
4. PostgreSQL Wiki — Index Maintenance
5. Use the Index, Luke! — PostgreSQL 索引教程