ECC 项目中的 PostgreSQL 实践指南索引、模式设计、RLS 与性能优化速查手册【免费下载链接】ECCThe agent harness performance optimization system. Skills, instincts, memory, security, and research-first development for Claude Code, Codex, Opencode, Cursor and beyond.项目地址: https://gitcode.com/GitHub_Trending/ev/ECC导读本文是 ECCAgent Harness Performance Optimization System仓库中postgres-patterns技能 的深度解读与实践指南。该技能以 Supabase 的 PostgreSQL 最佳实践为基础为编写 SQL 查询与迁移、设计数据库 schema、诊断慢查询、实现 Row Level SecurityRLS以及配置 connection pooling 提供了一套可复制的速查框架。读完本文你将掌握索引类型选择、复合索引列序、覆盖/部分索引、RLS 优化写法、游标分页、SKIP LOCKED队列处理等实战技巧并能借助仓库中的database-reviewer智能体自动执行完整的数据审查流程。技能定位与触发场景在 ECC 仓库中postgres-patterns/SKILL.md是一个快速参考型技能Quick Reference其定位是当开发者需要处理数据库相关任务时第一时间调用本技能获得权威的模式清单而详细的深度审查则由 数据库审查智能体database-reviewer完成。根据技能定义以下场景应当激活本技能编写 SQL 查询或数据库迁移migration设计数据库 Schema表结构、约束、外键诊断慢查询与性能瓶颈实现行级安全Row Level Security, RLS配置连接池connection pooling技能的元数据frontmatter也明确了其来源与归属name: postgres-patterns description: Patrones de base de datos PostgreSQL para optimización de consultas, diseño de esquemas, indexación y seguridad. Basado en las buenas prácticas de Supabase. origin: ECC注本文引用的关联文档为仓库的西班牙语版本docs/es/skills/postgres-patterns/SKILL.md其内容与英文原版skills/postgres-patterns/SKILL.md完全一致仓库还提供了docs/zh-CN/skills/postgres-patterns/SKILL.md、docs/ja-JP/skills/postgres-patterns/SKILL.md、docs/ko-KR/skills/postgres-patterns/SKILL.md等多个语言版本便于多语言团队使用。索引速查表Index Cheat Sheet索引是查询性能的第一道防线。技能提供了一张按查询模式选择索引类型的速查表覆盖了日常开发中最常见的六种场景查询模式索引类型示例WHERE col value等值匹配B-tree默认CREATE INDEX idx ON t (col)WHERE col value范围查询B-treeCREATE INDEX idx ON t (col)WHERE a x AND b y等值范围复合索引CREATE INDEX idx ON t (a, b)WHERE jsonb {}JSONB 包含GINCREATE INDEX idx ON t USING gin (col)WHERE tsv query全文检索GINCREATE INDEX idx ON t USING gin (col)时间序列范围查询BRINCREATE INDEX idx ON t USING brin (col)理解这张表的关键在于 PostgreSQL 各类索引的工作机理B-tree是 PostgreSQL 的默认索引类型同时支持等值匹配和范围扫描、、BETWEEN、LIKE prefix%。绝大多数业务查询都靠它解决日常建索引无需显式声明类型。复合索引Composite用于多个列同时出现在 WHERE 条件的场景。索引列的排列顺序直接决定索引能否被利用——必须遵循等值列在前、范围列在后的原则详见下文复合索引列序。GINGeneralized Inverted Index专为一个值可能对应多行的倒排结构设计是 JSONB 包含操作符、?、?|、?以及全文检索tsvector tsquery的唯一正确选择。注意普通 B-tree 无法加速jsonb 这类操作。BRINBlock Range Index适用于物理上天然有序的数据例如按时间递增写入的日志表、事件表。它只记录每个块区间的最小/最大值体积极小在海量时间序列数据上比 B-tree 更省空间、更易常驻内存。但如果数据是随机写入的BRIN 会失效此时应回归 B-tree。结合仓库证据审查智能体的索引原则在 database-reviewer 智能体 的职责定义中索引审查被列为CRITICAL关键级别其核心原则包括外键必须建索引——Index foreign keys — Always, no exceptions无条件、无例外。因为外键列是 JOIN 和级联删除的必经之路无索引的外键会导致父表删除时触发全表扫描。检查 WHERE/JOIN 涉及的列是否已建索引。对复杂查询执行EXPLAIN ANALYZE重点排查大表上的 Seq Scan顺序扫描。警惕 N1 查询模式。验证复合索引的列顺序是否遵循等值在前、范围在后。也就是说本技能提供该建什么索引的模式清单而智能体负责在真实代码审查中验证是否真的建了、建得对不对。数据类型速查Data Type Quick Reference错误的类型选择是长期性能与正确性隐患的根源。技能给出了一份用对类型、避开误区的对照表使用场景正确类型避免使用主键/IDbigintint、随机 UUID字符串textvarchar(255)时间戳timestamptztimestamp金额numeric(10,2)float布尔标志booleanvarchar、int各条选择的背后原因值得展开ID 用bigint而非intint上限约 21 亿对于增长中的业务表很容易触顶bigint8 字节提供了几乎无限的增量空间。同时避免用随机 UUID 做主键——随机值破坏了 B-tree 的物理局部性导致索引页频繁分裂、写放大严重。如果业务确实需要 UUID应优先使用 UUIDv7时间有序或IDENTITY自增列。这一点在 database-reviewer 的反模式清单 中被明确列为Random UUIDs as PKs使用 UUIDv7 或 IDENTITY。字符串统一用textPostgreSQL 中varchar(n)的长度限制除了做校验外并不带来任何性能优势反而引入了长度算错→迁移改列的风险text没有长度上限行为一致且更简洁。时间戳必须带时区timestamptz存储的是绝对时间点内部按 UTC 存储展示时按会话时区转换而timestamp不带时区在跨时区部署时极易产生少 8 小时之类的灾难性错误。任何生产系统都应使用timestamptz。金额用numeric而非floatfloat是二进制浮点无法精确表示十进制小数做累计求和时会产生舍入误差numeric(10,2)提供精确的定点十进制运算是财务数据的唯一正确选择。标志位用boolean语义清晰、存储紧凑1 字节避免用varchar/int表达布尔值带来的约束缺失与歧义。高频 SQL 模式Common Patterns技能正文的核心是七个可直接复制的 SQL 模式它们覆盖了索引设计、安全策略、写入与分页等日常高频场景。复合索引列序等值列在前范围列在后-- 等值列在前范围列在后 CREATE INDEX idx ON orders (status, created_at); -- 适用于WHERE status pending AND created_at 2024-01-01复合索引的列顺序决定其可用性。B-tree 复合索引只能从第一列开始按序匹配把等值列status pending放在前面可以让索引精确收敛到少量候选行再对范围列created_at ...做有序扫描。反例是把范围列放前面此时等值列无法被索引利用索引退化为无用的全扫描。这也是 database-reviewer 审查清单 中Composite indexes in correct column order一项的检查对象。覆盖索引Covering Index用 INCLUDE 消除回表CREATE INDEX idx ON users (email) INCLUDE (name, created_at); -- 避免对 SELECT email, name, created_at 进行表查找回表覆盖索引把查询需要的非索引列附带进索引的叶子节点。当查询的投影列全部包含在索引中时PostgreSQL 可以直接从索引返回结果完全跳过堆表访问Index-Only Scan大幅降低 IO。适合宽表 高频固定投影的查询。注意INCLUDE列不参与索引排序与匹配只做顺带携带因此不会增大索引的搜索结构。部分索引Partial Index只为活跃数据建索引CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL; -- 更小的索引只包含活跃用户部分索引通过在CREATE INDEX末尾追加WHERE子句只为满足条件的行建索引。对软删除soft delete场景活跃行通常只占全表的少部分部分索引体积更小、维护成本更低、扫描更快。查询侧只需保证 WHERE 条件语义上包含索引的过滤条件PostgreSQL 会自动识别即可命中该索引。技能与 database-reviewer 的关键原则Use partial indexes —WHERE deleted_at IS NULLfor soft deletes完全一致。优化的 RLS 策略把函数调用包进 SELECTCREATE POLICY policy ON orders USING ((SELECT auth.uid()) user_id); -- 注意包在 SELECT 里这是技能中最值得注意的一行注释。在 PostgreSQL 中策略表达式会在每行被检查时求值如果直接写auth.uid() user_idPostgreSQL 可能把该函数视为IMMUTABLE不可变而错误地进行表达式预计算/每行重复调用优化不当导致结果错误或性能灾难。将其改写为(SELECT auth.uid())的子查询形式可强制 PostgreSQL 将auth.uid()视为STABLE稳定函数——即在同一语句内只计算一次且禁止被下推或重排从而保证语义正确与性能可控。在 database-reviewer 的反模式清单 中对应的反面写法被明确列为需要标记的问题RLS policies calling functions per-row (not wrapped inSELECT)RLS 策略逐行调用函数、未包裹在 SELECT 中。此外智能体的安全审查还要求RLS 策略中引用的列必须建立索引、多租户表必须启用 RLS、遵循最小权限原则不给应用用户GRANT ALL、回收 public schema 的默认权限。UPSERTON CONFLICT 原子写入INSERT INTO settings (user_id, key, value) VALUES (123, theme, dark) ON CONFLICT (user_id, key) DO UPDATE SET value EXCLUDED.value;ON CONFLICT把先查后写的竞态窗口彻底消除当(user_id, key)上的唯一约束/索引冲突时直接执行DO UPDATE通过EXCLUDED引用本次插入尝试的值。整个操作是原子的无需应用层加锁或捕获唯一键异常也不会产生 TOCTOU检查后使用竞态。注意ON CONFLICT的目标列必须被唯一约束或唯一索引覆盖。游标分页O(1) 替代 O(n) 的 OFFSETSELECT * FROM products WHERE id $last_id ORDER BY id LIMIT 20; -- O(1) 复杂度而 OFFSET 是 O(n)OFFSET分页的代价是数据库必须扫描并丢弃前OFFSET行在深分页时呈 O(n) 增长且无法利用索引跳过。游标分页通过记住上一页最后一条记录的锚点WHERE id $last_id让查询直接利用主键索引从锚点开始有序取LIMIT行复杂度恒为 O(1)。它天然避免了并发写入导致的分页重复/遗漏问题。代价是失去了直接跳到第 N 页的能力因此适合 feed 流、消息列表等顺序浏览场景——这正是 database-reviewer 明确要求 的Cursor pagination ——WHERE id $lastinstead ofOFFSET。队列处理SKIP LOCKED 高并发抢任务UPDATE jobs SET status processing WHERE id ( SELECT id FROM jobs WHERE status pending ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED ) RETURNING *;这是技能中最具生产实战价值的一个模式。多个 worker 并发消费任务表时普通的SELECT ... FOR UPDATE会让后来的 worker阻塞等待已被锁定的行形成排队瓶颈SKIP LOCKED则明确告诉数据库跳过已被其他事务锁定的行直接取下一个可用任务。配合子查询取出后立即 UPDATE RETURNING的写法整个领取过程是原子的多个 worker 互不阻塞。这在 database-reviewer 的关键原则 中被量化描述为SKIP LOCKED for queues — 10x throughput for worker patterns对 worker 模式可带来数量级的吞吐提升。反模式检测 SQL三张系统视图排查隐患技能提供了一组直接可跑的检测查询用于在真实数据库中扫描三类典型问题未索引的外键、慢查询、表膨胀。找出没有索引的外键-- 找出未建立索引的外键 SELECT conrelid::regclass, a.attname 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) );这段查询遍历pg_constraint中所有外键约束contype f并通过pg_index反查该列是否出现在任何索引中。凡是返回的行都是外键未建索引的隐患点——它们在父表 UPDATE/DELETE 时会触发子表全表扫描。通过 pg_stat_statements 定位慢查询-- 找出慢查询 SELECT query, mean_exec_time, calls FROM pg_stat_statements WHERE mean_exec_time 100 ORDER BY mean_exec_time DESC;pg_stat_statements是 PostgreSQL 官方扩展聚合记录每条 SQL 的执行次数、总耗时与平均耗时单位毫秒。上述查询直接列出平均执行时间超过 100ms的语句按耗时降序排列是定位哪条 SQL 最值得优化的第一手依据。使用前需在配置中启用该扩展见下文配置模板。检查表膨胀bloat-- 检查表的膨胀情况 SELECT relname, n_dead_tup, last_vacuum FROM pg_stat_user_tables WHERE n_dead_tup 1000 ORDER BY n_dead_tup DESC;PostgreSQL 的 MVCC 机制会让被更新/删除的行留下死亡版本dead tuples若VACUUM未能及时回收表会持续膨胀查询变慢、磁盘占用升高、索引失效。n_dead_tup表示当前死亡元组数量last_vacuum显示上次 vacuum 时间超过阈值示例中为 1000的行即提示需要关注 autovacuum 配置或手工VACUUM。值得一提的是database-reviewer 的诊断命令 还提供了与之配套的表体积排行与索引使用率查询可与上述检测组合成完整的健康巡检psql -c SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC; psql -c SELECT indexrelname, idx_scan, idx_tup_read FROM pg_stat_user_indexes ORDER BY idx_scan DESC;后者的价值在于发现建了却从未被使用的冗余索引idx_scan长期为 0这类索引只增写放大、毫无收益应当删除。服务配置模板连接、超时、监控与安全默认值技能最后给出了一份开箱即用的ALTER SYSTEM配置模板覆盖连接管理、超时保护、监控与安全基线四大维度-- 连接数限制根据内存调整 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; -- 监控 CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- 安全默认值 REVOKE ALL ON SCHEMA public FROM public; SELECT pg_reload_conf();逐项说明其作用与调参思路max_connections 100PostgreSQL 每个连接都是一个独立进程连接数过高会耗尽内存与 CPU 调度资源。该值应结合机器内存调整并在应用层搭配连接池connection pooling如 PgBouncer使用——这也是技能何时激活中明确提到的配置场景。当连接数紧张时优先压应用层池化而非粗暴调大max_connections。work_mem 8MB单个排序/哈希操作可用的内存上限按操作而非按连接分配。设得太小会导致排序落盘temp file设得太大则在高并发下内存迅速被吃光。8MB 是安全的保守起点需结合EXPLAIN ANALYZE观察到的实际排序规模逐步调整。idle_in_transaction_session_timeout 30s终止开启事务后长时间空闲的连接。这类连接会长期持有锁是生产环境中莫名其妙锁等待的头号来源30 秒超时能自动兜底清理。statement_timeout 30s单条语句的硬性超时防止失控查询笛卡尔积、漏条件全表扫描拖垮整个实例。对批处理/ETL 任务可按需放宽。CREATE EXTENSION IF NOT EXISTS pg_stat_statements;启用语句级统计扩展是上文慢查询检测与智能体诊断命令的数据基础必须在 postgresql.conf 的shared_preload_libraries中加入pg_stat_statements并重启后才可创建。REVOKE ALL ON SCHEMA public FROM public;默认情况下PostgreSQL 允许public角色在 public schema 中创建对象这是长期以来的安全隐患显式回收后对象创建必须显式授权是最小权限原则的第一道闸门。对应 database-reviewer 安全审查 中Public schema permissions revoked的检查项。SELECT pg_reload_conf();以上ALTER SYSTEM写入的配置项大多无需重启即可热加载部分参数如shared_preload_libraries仍需重启。在 ECC 工作流中落地与智能体和相关技能的协同postgres-patterns不是孤立存在的它位于 ECC 技能体系的数据库域中与以下资源构成完整的协同链路database-reviewer智能体agents/database-reviewer.md技能的深度版。当快速参考不足以解决复杂问题时该智能体承担完整的数据库审查工作流——查询性能CRITICAL、Schema 设计HIGH、安全与 RLSCRITICAL、连接管理、并发、监控六大职责并内置了反模式标记清单与最终审查 checklist。技能是字典智能体是使用字典的专家。database-migrations技能skills/database-migrations/SKILL.md负责 schema 变更与数据迁移的安全流程其 PostgreSQL 章节与本技能的索引/类型模式直接呼应例如大表加索引用CONCURRENTLY、新增列必须带默认值或可空。编写迁移时两者应同时激活。backend-patterns技能skills/backend-patterns/SKILL.md提供 API 与后端层模式与数据库模式共同构成从 SQL 到服务的完整链路。clickhouse-io技能skills/clickhouse-io/SKILL.md面向 ClickHouse 分析型场景。当数据量超出单机 PostgreSQL 的在线分析能力、需要列式分析引擎时作为补充。这些技能均以多语言文档随仓库发布如docs/zh-CN/skills/postgres-patterns/SKILL.md、docs/ja-JP/skills/postgres-patterns/SKILL.md、docs/es/skills/postgres-patterns/SKILL.md等开发者可以直接将对应 SKILL.md 引入自己的 Agent 配置Claude Code、Codex、Opencode、Cursor 等让 Agent 在写 SQL 时自动遵循本文所述的全部模式。实践清单把本文直接用于日常开发将技能与智能体的要求汇总成一张可直接对照的检查清单WHERE / JOIN 涉及的列都已建立索引复合索引列序正确等值列在前范围列在后外键列全部建立索引无条件、无例外软删除表使用部分索引WHERE deleted_at IS NULL高频固定投影查询使用覆盖索引INCLUDEID 使用bigint或时间有序的 UUIDv7/IDENTITY不用int与随机 UUID字符串用text时间戳用timestamptz金额用numeric标志用boolean多租户表启用 RLS策略使用(SELECT auth.uid())写法策略列已建索引队列消费使用FOR UPDATE SKIP LOCKED大表分页使用游标分页不用 OFFSET复杂查询已用EXPLAIN ANALYZE验证无 Seq Scan / N1已通过pg_stat_statements排查慢查询通过pg_stat_user_tables排查膨胀已配置statement_timeout与idle_in_transaction_session_timeout已回收 public schema 默认权限总结postgres-patterns技能的价值在于把 PostgreSQL 生产实践中最高频、最有代表性的模式浓缩成一份可直接检索、可直接执行的速查表——从索引类型选择、数据类型规范到 RLS 优化写法、UPSERT、游标分页与SKIP LOCKED队列处理再到反模式检测 SQL 与服务端配置基线。配合仓库中的database-reviewer智能体开发者既能查字典也能让 Agent 自动完成从查询审查到安全加固的完整闭环。正如智能体文档末尾的提醒数据库问题往往是应用性能问题的根源尽早优化查询与 Schema 设计始终为外键和 RLS 策略列建立索引并用EXPLAIN ANALYZE验证每一个假设。本技能基于 Supabase Agent Skills 改编致谢 Supabase 团队遵循 MIT 许可见 LICENSE。【免费下载链接】ECCThe agent harness performance optimization system. Skills, instincts, memory, security, and research-first development for Claude Code, Codex, Opencode, Cursor and beyond.项目地址: https://gitcode.com/GitHub_Trending/ev/ECC创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考