线上某个接口突然变慢翻代码怀疑缓存、怀疑网络最后把慢日志捞出来一看拖后腿的是一条limit 20 offset 8000的分页语句另一次是新接手一个库想确认某张表到底多大、有没有索引对着客户端界面点了半天也没找到最后还是回到命令行敲了几句。这类场面我遇到过太多次。PostgreSQL 的常用 SQL 说到底就那么几十条真正决定效率的不是你背下来多少而是你知不知道自己敲下去的这一句数据库会怎么执行。我把这几年在几个 PG 实例上反复敲、反复查文档、反复被教训的内容归拢到一处按摸底、写入、查询、特色类型、排障五块摆好刚接触 PG 的人可以直接挑现成的抄写了两三年业务 SQL 的人可以对着为什么那一层再核对一遍。1. 接手一个库时先把结构摸清楚的几条固定SQL新环境上手最忌讳的就是凭记忆直接写业务语句。表名对不对、字段类型是什么、有没有唯一约束、索引建在哪些列上这几件事没确认之前写出来的 SQL八成要返工。PG 在这方面给的东西很足问题只是大多数人只用了客户端图形界面把命令行那一套更快的路径给忘了。1.1 psql 元命令与系统表的分工psql 自带的反斜杠命令是最省事的入口\l列数据库\c dbname切库\dt列当前 schema 的表\dt *.*连系统表一起列\d tablename看字段、索引、约束、存储参数\di单看索引\df看函数\dn看 schema\x切换成纵向展开——这条在字段多、一行显示不下的时候特别有用查pg_stat_activity时我基本都会先敲\x。还有\timing on打开计时量一些临时排查语句的耗时比在外面套time命令直观。元命令的定位是快速看一眼需要把结果导出、做过滤、跟别的表 join 的时候就得回到 SQL。比如想找出某个 schema 下所有建了触发器但没建主键的表\d一个个翻是不现实的。我的习惯是临时确认用\d需要形成清单或者带条件筛选直接写系统表查询。有一个细节值得提\d显示的表大小只包含主表堆文件不含索引和 TOAST 表。要完整的体积还是得用pg_total_relation_size。我见过有人按\d的数字判断表不大结果索引体积是堆的三倍这个误差在规划清理策略时是要命的。1.2 一条SQL盘出哪些表大、哪些表空接手陌生库我第一件事通常是跑下面这条select c.relname, c.reltuples::bigint as est_rows, pg_size_pretty(pg_total_relation_size(c.oid)) as total_size, pg_size_pretty(pg_relation_size(c.oid)) as heap_size, pg_size_pretty(pg_indexes_size(c.oid)) as index_size from pg_class c join pg_namespace n on n.oid c.relnamespace where c.relkind r and n.nspname not in (pg_catalog, information_schema) order by pg_total_relation_size(c.oid) desc limit 30;这条语句回答三个问题谁最大、索引占比多少、行数是什么量级。reltuples是估算值由ANALYZE或VACUUM更新跟真实行数可能有偏差快速评估够用了要精确值就老实用count(*)但在千万级表上别随便跑。判断索引占比有个经验阈值如果某张表的索引体积超过堆体积的一半而这张表又不是典型的查询密集型只读表八成存在冗余索引或者重复索引。常见情况是两个索引的前导列相同比如(a)和(a, b)前者完全可以被后者覆盖删掉能省下不少写入开销和磁盘。提示relkind r只抓普通表分区表在 PG10 之后是p物化视图是m。查全部占空间的对象时把relkind in (r,p,m)一起写上不然会漏掉分区父表和物化视图。1.3 字段类型和约束的确认方式字段层面的确认我一般直接用information_schema.columnsselect column_name, data_type, character_maximum_length, is_nullable, column_default from information_schema.columns where table_schema public and table_name orders order by ordinal_position;想连注释、是不是自增、有没有生成列一起看pg_catalog那边的信息更全但写起来啰嗦。日常确认类型和默认值information_schema的可读性更好。索引和约束用这两条-- 索引定义 select indexname, indexdef from pg_indexes where schemaname public and tablename orders; -- 约束含主键、唯一、外键、检查 select conname, contype, pg_get_constraintdef(oid) from pg_constraint where conrelid public.orders::regclass;这两条我几乎每次写 upsert 之前都会跑一遍。原因很简单on conflict后面括号里的列必须有唯一索引或者唯一约束托底否则语句直接报错。目测代码里有unique不算数线上表可能是历史遗留、约束被 drop 过、或者是靠应用层保证的逻辑唯一这些情况我都碰到过。写之前花十秒确认比上线后回滚强。2. 写入侧upsert、批量更新与 RETURNING 的组合拳业务代码里写入相关的 SQL翻来覆去就是三种形态有则更新无则插入、按一张临时表批量刷数据、写完之后立刻要拿到受影响的行。这三种 PG 都有对应写法而且都比先查再判断要稳前提是你知道各自的边界在哪。2.1 ON CONFLICT 的两种分支以及并发下最容易踩的死锁最基础的写法insert into user_points (user_id, points, updated_at) values ($1, $2, now()) on conflict (user_id) do update set points user_points.points excluded.points, updated_at now() returning user_id, points;excluded是 PG 给的一个伪表代表本来要插进去的那一行。这个设计让累加类的更新特别顺手不用先 select 一次再 update。另一种分支是do nothing适合重复就跳过的幂等写入配合returning可以判断到底插没插进去——插进去了会返回行跳过了返回空集这个特性做幂等接口很好用。真正需要小心的是并发。假设两个事务同时向同一张表写入一批数据键集合有重叠但顺序不同事务 A 先写id1再写id2事务 B 先写id2再写id1。do update会对冲突行加行级锁A 拿到 1 的锁、B 拿到 2 的锁然后各自等对方释放死锁就这么产生了。日志里会看到deadlock detected。规避办法不复杂批量 upsert 之前按冲突键排个序让所有会话的加锁顺序一致。如果是应用层拼的批量语句在拼之前对数据sort一下就行。另外单条语句里如果同一个键出现两次PG 会直接报ON CONFLICT DO UPDATE command cannot affect row a second time这个错误在从外部导入数据时特别常见——源头数据本身有重复得先去重再写。注意on conflict的推理依赖唯一索引。用表达式唯一索引比如unique ((lower(email)))时冲突目标要写成on conflict (lower(email))直接写列名是匹配不上的。2.2 UPDATE ... FROM 批量更新源表重复行是隐形炸弹拿一张临时表或 staging 表去刷主表是数据修复、离线同步里的高频操作update orders o set status s.status, updated_at now() from import_batch s where o.order_no s.order_no and o.status is distinct from s.status;最后那个is distinct from是我后来加的习惯。不加的话即使值没变也会写一遍产生大量死元组让 autovacuum 白忙活长此以往表就膨胀了。加上之后只更新真正有变化的行写入量能降一个数量级。这里有个很隐蔽的问题如果import_batch里同一个order_no出现多行PG 只会挑其中一行来更新目标行具体挑哪一行是不确定的。它不会报错也不会告诉你有歧义就这么静默地执行了。我第一次遇到时排查了很久——明明源文件里写的是 A更新完变成 B还以为是自己逻辑写错了。后来养成习惯执行前先跑一句select order_no, count(*) from import_batch group by order_no having count(*) 1 limit 50;有重复就先用distinct on (order_no) ... order by order_no, updated_at desc去重取最新那条再去更新。宁可多一步也别让结果不可预期。2.3 RETURNING 与数据搬运类 CTEreturning不只在 insert 上能用update 和 delete 都可以。这个特性让改完之后立刻拿结果变成一次往返update tasks set status running, started_at now() where id ( select id from tasks where status pending order by priority desc, created_at limit 1 for update skip locked ) returning id, payload;这段是典型的简易任务队列出队。for update skip locked让多个消费者并发取任务时不会互相阻塞也不会抢到同一行。比在应用层用分布式锁简单得多小规模场景完全够用。另一种是数据归档用一条语句把旧数据从主表搬到历史表with moved as ( delete from events where created_at now() - interval 180 days returning * ) insert into events_archive select * from moved;含 DML 的 CTE 在 PG 里一定会执行这一点和普通 CTE 不同——PG12 之后普通 CTE 默认可能被内联优化但带insert/update/delete的 CTE 不存在这个问题。用这个模式搬数据好处是原子性要么都删了、要么都没删不会出现删完插入失败导致数据丢失的情况。批量搬的时候别一条语句搬几百万行。大事务会长时间持有锁、撑大 WAL、拖慢复制。我的做法是在where里加limit外面套一层循环每批五千到两万行批次之间 sleep 几十毫秒给 autovacuum 和复制留点喘息空间。3. 分页、执行计划与索引查询慢的真正原因藏在哪里查询慢的原因千奇百怪但落到 PG 上绝大多数能归到三类翻页翻太深、执行计划选错了路、索引根本没被用上。这三件事的排查手段是固定的把动作练成肌肉记忆比临时百度有效率得多。3.1 OFFSET 越翻越慢的本质与 keyset 分页的写法limit 20 offset 8000的意思是按顺序取出 8020 行然后把前 8000 行扔掉。数据库确实做了 8020 行的读取工作只是最后只返回 20 行。页数越靠后白干的活越多这就是为什么第一页 10ms、第一百页 800ms。翻页深的时候索引有时候也救不了——如果走的是索引扫描它得沿着索引一路跳过 8000 个条目虽然比全表扫快但也是线性成本。真正的解法是记住上一页的位置从那里继续往下取select id, created_at, title from articles where (created_at, id) ($1, $2) order by created_at desc, id desc limit 20;$1、$2是上一页最后一行的created_at和id。行值比较(a, b) (x, y)在 PG 里是原生支持的等价于a x or (a x and b y)但写起来短优化器也能用上复合索引。配套索引必须是(created_at, id)这个顺序而且方向要一致create index idx_articles_created_id on articles (created_at desc, id desc);条件是查询的排序方向跟索引一致。如果一会儿desc一会儿asc索引就用不上了。keyset 分页的限制是没法直接跳页——用户想从第 1 页跳到第 50 页做不到因为没有第 50 页的起点这个信息。产品上如果是无限下拉、上一页下一页这种形态这套方案完美如果是带页码的经典分页就得权衡要么接受深翻的性能损耗要么在前端把页码限制在十页以内。3.2 EXPLAIN ANALYZE 先看哪三行explain analyze的输出很长新手容易从头读起。我一般先扫三个地方关注点看什么异常信号估算 vs 实际行数每个节点的rows和actual ... rows差 10 倍以上说明统计信息不准扫描方式Seq Scan/Index Scan/Bitmap Heap Scan小结果集却走 Seq Scan过滤掉的行数Rows Removed by Filter数值很大说明索引选择性差估算行数和实际行数偏差过大是最值得优先处理的问题。优化器是靠统计信息选路的估错了就会选错。常见原因是表刚大批量导入还没analyze、或者某列的数据分布极度倾斜比如status字段 99% 是done。处理方式很直接analyze orders; -- 或者针对单列提高统计精度 alter table orders alter column status set statistics 500; analyze orders;Rows Removed by Filter这一行经常被忽略。它表示索引扫出来一堆行然后逐行判断又扔掉多少。如果这个数远大于最终返回的行数说明索引建得不够精准——比如索引只建在status上而查询条件是status x and created_at ...那扫出来的statusx的行大部分都会被时间条件过滤掉。改成复合索引(status, created_at)就能把过滤下推到索引层少读很多页。还有个细节嵌套循环节点里的actual time是单次循环的平均耗时总耗时得乘以loops。有次我看到一个节点写着actual time0.05..0.08觉得挺快结果loops120000乘出来接近十秒。这个坑我踩过后来养成习惯先看loops再看时间。3.3 索引建了却没用上的六种典型情况索引失效的场景我整理了一张表排查时挨个对现象原因处理方式列上套了函数where lower(email) ...建表达式索引((lower(email)))类型不匹配字符串列和数字比较触发行上的隐式转换参数转成列的类型别反过来复合索引前导列没用上索引是(a, b)查询只按b过滤补建(b)或调整索引顺序模糊匹配前后都带通配符like %foo%无法走 B-tree装pg_trgm扩展建 GIN 索引返回比例太高优化器判断全表扫更便宜这是正常选择别硬改统计信息过期大批量写入后没 analyze定期 analyze或调 autovacuum 参数第一条和第二条是我见过最多的。第二条有个经典形态手机号字段存成varchar查询时写where phone 13800138000没加引号PG 会尝试把列转成数值类型来比较转换发生在列上索引直接失效。加上引号写成13800138000就没事了。这种问题在 ORM 里更隐蔽因为拼参数的时候类型是框架决定的你根本看不到生成的 SQL 长什么样。开log_min_duration_statement把慢语句记下来日志里的语句是最终形态能看清真相。第五条也值得说一句索引没被用上不一定是坏事。如果查询要返回表里 30% 的行走索引反而更慢因为要回表读大量数据页不如顺序扫一遍。看到Seq Scan先别急着加索引先看返回比例。真要优化考虑覆盖索引把查询需要的列都放进索引做成Index Only Scan让回表这一步省掉。4. 时间、JSONB、数组PG里绕不开的三类特色类型用 PG 的业务几乎躲不开这三类类型。它们都是 PG 相对其他关系库的加分项但每个都有自己的脾气写法不对就是性能和正确性双输。4.1 时间戳类型选型与半开区间查询timestamp和timestamptz的区别一句话说清前者存的是墙上时间不带时区信息后者存的是 UTC 时间点显示时按会话时区转换。业务表里我一律用timestamptz因为某个时刻发生的事这个概念天然带时区而timestamp一旦跨时区就全是歧义。唯一适合timestamp的场景是未来某个本地时间点比如闹钟、营业时间表这种确实不该跟时区绑定。时间区间查询有个流传很广的错误写法-- 别这么写 where created_at between 2024-01-01 and 2024-01-31;between是闭区间如果created_at是带时分秒的1 月 31 日当天的数据基本查不到。正确做法是半开区间where created_at 2024-01-01 and created_at 2024-02-01左闭右开既不漏也不重。跨月、跨年、闰月都不用特殊处理这是唯一不会出错的写法。还有一个容易混淆的点now()返回的是事务开始时间不是语句执行时间。同一个事务里调十次now()结果完全一样。要真实当前时间用clock_timestamp()。这个特性在批量写入时其实是好事——一批数据的created_at完全一致便于按批次归档。但如果你在长事务里记录处理完成时间用now()会得到事务开始的时间看起来像是提前完成了这就闹笑话了。生成日期序列做报表补零用generate_seriesselect d::date as day, count(o.id) from generate_series(2024-01-01::date, 2024-01-31::date, 1 day) d left join orders o on o.created_at d and o.created_at d interval 1 day group by d order by d;没有数据的日子也要出现在结果里前端画折线图才不会断。这个写法比在应用层补零靠谱。4.2 JSONB 的取值、包含判断与索引jsonb的操作符分两类-返回 jsonb-返回 text。取字符串用-取子对象继续往下钻用-select >create index idx_users_age on users (((data-age)::int));包含判断用是 jsonb 最常用的操作符配合 GIN 索引效率很高select * from users where data {status: active}; create index idx_users_data on users using gin (data jsonb_path_ops);jsonb_path_ops比默认的jsonb_ops体积小、查询快但它只支持不支持?、?|、?这几个键存在判断。如果业务里要查有没有这个字段就得用默认的jsonb_ops。选哪个取决于查询形态别盲目照抄。展开数组用jsonb_array_elementsselect u.id, item-sku as sku, (item-qty)::int as qty from users u, jsonb_array_elements(u.data-items) as item;这条在解析订单明细时特别好用一行 JSON 直接展开成多行关系数据。注意不是所有 jsonb 列都值得建 GIN 索引。GIN 的写入代价不低如果这张表写入频繁、查询却很少按 JSON 内容过滤索引反而拖后腿。先确认查询模式再决定建不建。4.3 数组的展开、包含与聚合数组类型在 PG 里是一等公民下标从 1 开始这点跟大部分编程语言不同写循环的时候容易差一位。包含判断用左边包含右边重叠判断用select id, tags from articles where tags array[postgres]; select id, tags from articles where tags array[postgres, mysql];是全包含是有交集。做标签筛选时如果是必须包含所有选中标签就用如果是命中任意一个就用。展开和聚合是一对反向操作-- 展开 select id, unnest(tags) as tag from articles; -- 聚合 select tag, count(*) as cnt from articles, unnest(tags) as tag group by tag order by cnt desc limit 20;数组列上建 GIN 索引和都能用上create index idx_articles_tags on articles using gin (tags);有个使用边界要清楚数组适合元素数量少、整体读取的场景比如标签、权限码。如果元素本身需要单独更新、需要关联别的表、需要计数排序那就是关系模型该干的事硬塞进数组只会让查询越来越别扭。我见过把订单明细塞进数组列的后来要做按商品统计销量只能每次展开全表性能惨不忍睹。类型选错了后面的 SQL 怎么写都是补救。5. 卡住了怎么查锁等待、长事务与膨胀的排查动作线上突然大面积超时业务方催着要结论这时候最怕的是乱猜。PG 在可观测性上给的东西很全关键是要知道从哪张视图看起、按什么顺序看。5.1 从 pg_stat_activity 找出谁在堵着谁第一站永远是pg_stat_activityselect pid, usename, state, wait_event_type, wait_event, now() - query_start as duration, left(query, 120) as query from pg_stat_activity where state idle order by duration desc limit 20;几个字段的含义要分清state是会话状态active表示正在执行wait_event_type是等待类型Lock表示在等锁IO表示在等磁盘Client表示在等客户端发数据——看到Client基本可以判断是应用侧的问题不是数据库慢。duration是按query_start算的能直接看出哪条语句跑了很久。找到可疑会话后接着查它在等谁select pid, pg_blocking_pids(pid) as blocked_by, state, left(query, 100) as query from pg_stat_activity where cardinality(pg_blocking_pids(pid)) 0;pg_blocking_pids返回一个数组里面是阻塞当前会话的所有 pid。顺着这个数组往上找就能找到锁链的源头。多数情况下源头只有一两个会话把它处理掉后面一串全解开。pg_locks能看得更细比如锁模式ExclusiveLock、AccessShareLock、锁对象是哪张表哪个事务。但它的输出是关联表的形式要 joinpg_class才看得懂对象名日常排查用pg_blocking_pids更快pg_locks留给需要精确判断锁类型的时候。5.2 终止会话的正确姿势与 idle in transaction 的危害确认了阻塞源头处理手段有两级-- 温和请求取消当前语句事务本身保留 select pg_cancel_backend(12345); -- 强硬直接断开连接事务回滚 select pg_terminate_backend(12345);顺序不能反。先cancel等几秒看是否生效不行再terminate。直接terminate的风险是如果这个会话正在执行一个大批量写入回滚可能要花很长时间期间锁不会释放问题反而拖得更久。比长查询更麻烦的是idle in transaction——会话开了事务执行完语句就不动了事务一直挂着。这种状态持有的锁不会释放还会阻止 vacuum 回收死元组。用下面这条揪出来select pid, state, now() - state_change as idle_duration, left(query, 100) as last_query from pg_stat_activity where state idle in transaction and now() - state_change interval 5 minutes order by idle_duration desc;根因通常在应用侧连接池借出连接后开了事务中间去调外部接口、去读文件忘了提交或回滚。要根治得改代码但可以先把idle_in_transaction_session_timeout设上超时后 PG 会自动断开alter system set idle_in_transaction_session_timeout 60s; select pg_reload_conf();这个参数值别设太小业务里确实有长事务的场景比如批量对账一刀切会误伤。60 秒到 5 分钟之间按业务节奏调。5.3 表膨胀与 autovacuum 的判断依据更新和删除产生的死元组要靠 vacuum 回收。回收不及时表文件越来越大扫描成本跟着涨这就是膨胀。看一眼哪些表积压了死元组select relname, n_live_tup, n_dead_tup, round(n_dead_tup * 100.0 / 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 limit 20;dead_pct超过 20% 且表本身不小就值得关注了。last_autovacuum为空说明从来没被自动清理过——可能表太小没触发阈值也可能长事务一直挡着。autovacuum 的触发阈值是50 0.2 * 表行数也就是 20% 的行变了才触发一次。对大表来说这个比例太高死元组能堆到很夸张的程度。表级调参可以更激进alter table orders set ( autovacuum_vacuum_scale_factor 0.02, autovacuum_vacuum_threshold 1000 );把比例降到 2%大表就能更勤快地被清理。代价是 autovacuum 更频繁地跑占一点 IO。对写入量大的核心表这个交换是划算的。判断实际膨胀程度可以用pgstattuple扩展拿到准确数字create extension if not exists pgstattuple; select * from pgstattuple(orders);里面的dead_tuple_percent是真实比例。这个函数会全表扫描只在大表诊断时偶尔用一次别写进监控定时跑。清理方式上vacuum是常规操作不锁表vacuum full会重写整张表并持有排他锁必须挑低峰期做而且要有足够磁盘空间。生产环境我更倾向用pg_repack这类在线重组工具虽然要多装一个组件但不用停业务。提示pg_stat_user_indexes里的idx_scan是判断索引有没有用的直接依据。连续观察一段时间比如一个完整的业务周期idx_scan仍然是 0 且不是唯一约束托底的索引基本可以判定是废索引删掉能减轻写入负担。注意统计是从上次pg_stat_reset()开始累计的刚重启过的实例数据不具参考性。最后再分享一个我自己常用的习惯每次要动线上的数据之前先用begin;把语句跑一遍看看影响行数rollback;之后再正式执行。这一来一回多花的十几秒救过我很多次——尤其是update忘了写where的时候看着UPDATE 48213这个数字在事务里跳出来冷汗瞬间就下来了但只要还没 commit一切好说。