PostgreSQL

我常忘记创建的索引

• 部分索引WHERE active = true—当大多数行不活跃时，索引大小可减少80%；建议用于状态字段
• 表达式索引ON lower(email)—必须与查询条件完全匹配；若无此索引，WHERE lower(email)将触发全表扫描
• 覆盖索引INCLUDE (name, email)—支持仅索引扫描；通过EXPLAIN检查"Heap Fetches"指标
• 外键字段—PostgreSQL不会自动创建索引；JOIN操作和ON DELETE CASCADE需要此类索引
• 复合索引顺序至关重要—(a, b)能优化WHERE a = ?但无法优化WHERE b = ?

索引陷阱

• 无用索引会拖累每次INSERT/UPDATE操作—建议查询pg_stat_user_indexes系统表进行检测idx_scan = 0，删除它们
• 写入频繁的表上索引过多——需谨慎权衡
• 低基数列（布尔值、状态）上的索引通常无用——PostgreSQL 倾向于顺序扫描
• LIKE '%后缀'无法使用B树索引——需要 pg_trgm GIN 索引或 reverse() 表达式索引

我未充分利用的查询模式

• SELECT FOR UPDATE SKIP LOCKED——无需外部工具的作业队列；跳过正在处理的行
• pg_advisory_lock(键)——无需表的应用级互斥锁；显式解锁或在断开连接时解锁
• IS NOT DISTINCT FROM——NULL安全的相等性比较；比以下方式更简洁(a = b OR (a IS NULL AND b IS NULL))
• DISTINCT ON (x) ORDER BY x, y——无需子查询即可获取每组的第一行；PostgreSQL特有但功能强大

连接管理（常被忽视）

• 连接数超过50时，PgBouncer至关重要——每个PostgreSQL连接约占用10MB内存；建议在事务级别进行连接池管理
• statement_timeout = '30s'每个角色设置超时——防止失控查询拖垮数据库
• idle_in_transaction_session_timeout = '5分钟'——终止持有锁的废弃事务
• 默认100的最大连接数在生产环境中太低，过高则浪费内存——需根据内存容量调整

我常弄错的数据类型

• SERIAL已弃用——改用GENERATED ALWAYS AS IDENTITY
• TIMESTAMP不带时区——几乎总是错的；改用TIMESTAMPTZ，PostgreSQL以UTC格式存储
• 用浮点数表示金额——改用NUMERIC(12,2)或整数分币单位；浮点数计算会出错：0.1 + 0.2 ≠ 0.3
• VARCHAR(n)与TEXT——在PostgreSQL中性能无差异；除非需要约束，否则用TEXT

清理与膨胀（永远别忽略）

• 高频UPDATE表会膨胀——死亡元组堆积；pg_repack无需锁即可回收空间
• VACUUM ANALYZE批量插入后更新统计信息；查询规划器需要当前数据
• 大表自动清理滞后——调整autovacuum_vacuum_cost_delay参数或手动执行清理
• 事务ID回卷：若事务ID耗尽，数据库将停止——自动清理可预防但需监控

不要直接阅读EXPLAIN输出

• 始终使用EXPLAIN (ANALYZE, BUFFERS)命令——获取实际执行时间与I/O数据；仅依赖估算值易导致误判
• 索引查询出现"堆读取次数：1000"——缺失字段，需在索引中添加INCLUDE子句
• 顺序扫描并非绝对低效——当查询超过10-20%表数据时可能快于索引；需核查行数估算
• "行数"估算严重偏差——执行ANALYZE或检查统计目标值是否过低

全文检索常见错误

• 动态生成tsvector——应预计算为存储生成列并建立GIN索引
• 对用户输入使用plainto_tsquery函数——可处理空格且避免语法错误；勿用
• 缺少语言参数—'english' 会进行词干提取；'simple' 为精确匹配
• 全文检索是基于单词的LIKE '%精确短语%'对于子字符串匹配仍然需要

事务隔离级别

• 默认为读已提交—在报表中可能出现幻读；为保持一致性可使用可重复读
• 可序列化能捕获冲突—但必须通过重试循环处理 40001 错误
• 长事务会阻塞清理操作并持有锁—应保持在秒级，而非分钟级