1. 先说点实在的这份合集到底解决什么问题做后端开发和数据库运维这几年我最大的感受就是PostgreSQL 的功能太强了但常用的就那几十条 SQL每次都去翻官方文档实在浪费生命。昨天刚写过的查询过两周又要想半天语法MySQL 转过来的同事天天把 LIMIT 1 挂在嘴边完全忘了 PostgreSQL 的 FETCH FIRST 才是标准写法。这种零散的记忆碎片攒着攒着就丢了一大半。所以我决定把日常工作中真正高频使用的 SQL 整理成一份合集按照使用场景分类每一条都附上适用场景和踩坑记录。这份合集不是教科书不是让你背语法而是给你一种关键时刻能直接抄作业的底气。适用对象包括三类人后端开发者每天都在跟数据打交道需要快速查询表结构、分析慢查询、处理数据更新。数据库运维/ DBA需要排查连接异常、检查锁冲突、监控数据库膨胀状态。数据分析师或刚接触 PostgreSQL 的转岗同学对系统表不熟悉想快速找到别人口中那条查询数据量的 SQL。从 PostgreSQL 9.6 到 15、16 我都实际用过这套合集里的绝大多数 SQL 都能跨版本运行个别新特性我会单独标出最低版本要求。文章会持续更新每一批都在原有的分类下补充新的实战语句。2. 查信息、看家底连接库表的基础查询集合2.1 版本、库与连接数检查拿到一套陌生的环境第一件事永远是搞清楚我在哪、库有多大、连接有没有打满。我刚接手一套业务系统的时候第一步就是跑这些查询-- 当前 PostgreSQL 版本 SELECT version(); -- 当前连接数 SELECT count(*) FROM pg_stat_activity; -- 各库大小单位 MB SELECT datname, pg_size_pretty(pg_database_size(datname)) AS db_size FROM pg_database ORDER BY pg_database_size(datname) DESC;这里有个容易踩的坑连接数不要只看count(*)很多连接可能是 idle 状态完全不影响性能。要判断连接是否够用得看state的分布SELECT state, count(*) AS conn_count FROM pg_stat_activity GROUP BY state ORDER BY conn_count DESC;正常情况下大部分连接应该是idle或active。如果出现大量idle in transaction那基本就是应用代码里事务没及时提交这种问题常见于使用连接池忘记关闭事务的情况。pg_size_pretty()这个函数是 PostgreSQL 独有的能把字节数自动转成 KB、MB、GB 这种可读格式比我之前用 MySQL 时自己写除法换算方便太多了。库大小的查询在做迁移评估、容量规划时特别有用。2.2 表大小、行数与膨胀判断业务表到底有多大、多少行、膨胀了多少这是 DBA 和开发者都关心的问题。下面这条 SQL 会把当前模式下的表按总大小排序一次性看清楚所有表的体重SELECT schemaname AS schema_name, tablename AS table_name, pg_size_pretty(pg_total_relation_size(schemaname || . || tablename)) AS total_size, pg_size_pretty(pg_relation_size(schemaname || . || tablename)) AS table_size, pg_size_pretty(pg_total_relation_size(schemaname || . || tablename) - pg_relation_size(schemaname || . || tablename)) AS index_size FROM pg_tables WHERE schemaname NOT IN (pg_catalog, information_schema) ORDER BY pg_total_relation_size(schemaname || . || tablename) DESC;注意pg_total_relation_size包含表数据加索引加 TOASTpg_relation_size只算表本身的堆文件。两者之差基本就是索引占用的空间。这是判断到底是表大还是索引大的最快手段。关于行数count(*)在大表上会全表扫描生产环境不建议频繁执行。PostgreSQL 的统计信息里维护了一个行数估算值可以这样拿SELECT relname AS table_name, n_live_tup AS estimate_rows FROM pg_stat_user_tables WHERE relname your_table_name;这个数字不是精确值可能有百分之几甚至更多的偏差但用来做容量评估足够。如果要精确行数只能count(*)建议在业务低峰期执行。我在一次大促前评估订单表容量时就是用这个统计信息快速判断出了哪个分区表膨胀最严重省去了扫表的耗时。2.3 表结构与字段信息开发同学最常问的问题是这张表的字段都有啥PostgreSQL 里查字段信息靠的是information_schema.columns或者系统表pg_attribute。日常我用后者更多因为可以顺便拿到注释SELECT a.attname AS column_name, format_type(a.atttypid, a.atttypmod) AS data_type, col_description(a.attrelid, a.attnum) AS column_comment FROM pg_attribute a WHERE a.attrelid your_table_name::regclass AND a.attnum 0 AND NOT a.attisdropped ORDER BY a.attnum;这里有个细节a.attnum 0是为了排除系统隐藏列NOT a.attisdropped排除被删除的列。如果你写过 ALTER TABLE DROP COLUMN就知道 PostgreSQL 其实不是物理删除列而是标记一下所以这个过滤条件不能少。查外键关系的 SQL 也经常用特别是拆表、拆库的时候SELECT tc.constraint_name, kcu.column_name AS source_column, ccu.table_name AS target_table, ccu.column_name AS target_column FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name kcu.constraint_name JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name tc.constraint_name WHERE tc.constraint_type FOREIGN KEY AND tc.table_name your_table_name;在数据迁移时外键关系是必须提前梳理的不然导入顺序一错就会报主键冲突。3. 索引与性能诊断的黄金搭配3.1 查看索引与索引使用率索引是 PostgreSQL 性能的核心。我见过不少线上表索引建得密密麻麻但实际查询根本没走到。一条 SQL 看出一张表的所有索引SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size, indexdef FROM pg_indexes WHERE tablename your_table_name;indexdef列会输出完整的 CREATE INDEX 语句这对排查索引走了没走非常有帮助因为你可以看到她到底是 B-tree、GIN 还是 BRIN。比看索引更重要的是看索引的使用率SELECT schemaname, relname AS table_name, indexrelname AS index_name, idx_scan AS index_scan_times, idx_tup_read AS rows_read_by_index, idx_tup_fetch AS rows_fetched_by_index FROM pg_stat_user_indexes ORDER BY idx_scan ASC;执行频率排在最后的索引大概率是没用的索引。我处理过一次线上磁盘占用告警排查下来发现某张订单历史表有 6 个索引其中 3 个的idx_scan是 0。跟业务方确认后直接 DROP磁盘压力瞬间缓解。当然删除索引前一定要在测试环境复现查询计划确认没有 SQL 依赖这个索引。3.2 慢查询与正在执行的 SQLPostgreSQL 排查慢查询第一站永远是pg_stat_statements。这个模块默认没开启需要配置shared_preload_libraries pg_stat_statements并重启库。开启后查询累计耗时最长的 SQLSELECT queryid, calls AS exec_count, round(total_exec_time::numeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS avg_ms, round(max_exec_time::numeric, 2) AS max_ms, query FROM pg_stat_statements WHERE query NOT LIKE %pg_stat_statements% ORDER BY total_exec_time DESC LIMIT 20;注意版本差异PostgreSQL 13 之前的时间字段叫total_time、mean_time13 之后改成了total_exec_time、mean_exec_time。我在 11 升 14 的时候被这个变化坑过一次脚本直接报字段不存在。如果不想等统计模块也可以直接抓正在执行的慢 SQLSELECT pid, now() - query_start AS duration, state, wait_event_type || : || wait_event AS wait_info, query FROM pg_stat_activity WHERE state active AND query NOT LIKE %pg_stat_activity% ORDER BY duration DESC;这个查询非常实用。生产环境突然 CPU 飙升十有八九是某条 SQL 在裸跑大表用这条 SQL 一看就知道是谁在捣乱。duration字段用now() - query_start计算比query_start裸看直观得多。3.3 锁冲突与死锁快速定位业务卡住了大概率不是慢查询而是锁冲突。PostgreSQL 查锁的经典视图是pg_locks但直接看pg_locks太碎我习惯用下面的关联查询直接找出谁堵了谁SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query, blocking.pid AS blocking_pid, blocking.query AS blocking_query, blocked.wait_event_type || : || blocked.wait_event AS wait_info FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocked.pid ANY( SELECT pid FROM pg_stat_activity WHERE pg_blocking_pids(blocked.pid) ARRAY[blocked.pid] ) WHERE blocked.wait_event_type Lock;这段 SQL 写起来有一点绕我解释一下思路pg_blocking_pids(blocked.pid)是 PostgreSQL 自带的函数返回阻塞指定 pid 的进程数组。通过数组包含操作符找到阻塞源再 self join 拿到两边各自的完整 SQL。拿到阻塞源后对症下手。state idle in transaction的会话往往是只开了事务没提交可以直接pg_terminate_backend(blocking_pid)。但话放在前面终止连接是外科手术式操作先通知业务方确认不是正在跑的关键任务再动手。3.4 表膨胀与 VACUUM 状态表膨胀是 PostgreSQL 特有的运维问题。更新和删除都会产生死元组autovacuum 干活不及时表就会越来越肿。查膨胀状态SELECT relname AS table_name, n_live_tup AS live_rows, n_dead_tup AS dead_rows, CASE WHEN n_live_tup 0 THEN round(n_dead_tup::numeric / n_live_tup, 2) ELSE 0 END AS dead_ratio FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;dead_ratio超过 0.2 就该关注了超过 0.5 说明 autovacuum 已经跟不上了。这时可以手动执行VACUUM (VERBOSE, ANALYZE) your_table_name;来兜底。注意生产环境执行 VACUUM 会消耗 IO尽量在业务低峰期做。4. 高频 DML更新、删除、去重的最佳实践4.1 INSERT ... ON CONFLICT 做 upsert 的妙用PostgreSQL 的ON CONFLICT语法是我从 MySQL 转过来后觉得最好用的功能之一。一条 SQL 搞定存在就更新不存在就插入再也不用先 SELECT 判断再决定走 INSERT 还是 UPDATEINSERT INTO user_points (user_id, points, updated_at) VALUES (1001, 50, now()) ON CONFLICT (user_id) DO UPDATE SET points user_points.points EXCLUDED.points, updated_at now();EXCLUDED是 PostgreSQL 里的特殊行引用表示那些因为冲突而被丢弃的候选插入行。上面的写法可以用在积分累加场景同一用户有新积分进来直接叠加。如果只想忽略冲突、不做更新就写成DO NOTHING。这里有一个隐藏的坑ON CONFLICT需要唯一索引或主键支撑。冲突判断依据就是你在括号里写的那个字段上的唯一约束。如果没建唯一索引执行时直接报there is no unique or exclusion constraint matching the ON CONFLICT specification。所以建表时一定要把业务上唯一的字段声明成唯一约束。4.2 用 CTE 串联更新步骤告别嵌套子查询复杂业务逻辑里经常需要先算出一个结果集再根据结果更新主表。用 WITH 子句能大幅提高可读性WITH recent_orders AS ( SELECT customer_id, sum(amount) AS total_amount FROM orders WHERE created_at now() - interval 7 days GROUP BY customer_id ) UPDATE customers c SET last_7d_order_amount r.total_amount FROM recent_orders r WHERE c.id r.customer_id;PostgreSQL 的 UPDATE 支持FROM子句可以直接跟另一个表或 CTE 做关联更新。这在 MySQL 里写起来要麻烦得多。同类写法还可以用在删除上DELETE FROM ... USING ... WHERE ...。4.3 删除重复数据只保留一条业务表缺唯一约束导致重复数据这种场景我见过太多次了。CTID 是 PostgreSQL 里的物理行标识每一行都有独立的 ctid利用它删除重复记录特别巧妙DELETE FROM user_emails e USING user_emails e2 WHERE e.email e2.email AND e.ctid e2.ctid;原理e.ctid e2.ctid表示在同一个 email 分组下保留 ctid 最大的那条删除其余所有。这比先查出来再逐条 DELETE 效率高一个数量级。执行前务必先跑一遍 SELECT 确认要删的数量SELECT count(*) FROM user_emails e JOIN user_emails e2 ON e.email e2.email WHERE e.ctid e2.ctid;4.4 批量删除历史数据控制单次删除大小一次性 DELETE 几百万行数据通常会锁表锁很久还把 WAL 日志撑爆。稳妥的方案是分批删除。用ctid范围或者时间窗口都可以-- 每批删除 1 万条循环执行直到影响行数为 0 DELETE FROM audit_logs WHERE created_at now() - interval 90 days AND ctid IN ( SELECT ctid FROM audit_logs WHERE created_at now() - interval 90 days LIMIT 10000 );分批删除为什么有效因为每次 DELETE 事务都很短锁持有时间就短其他业务查询的阻塞时间会显著下降。ctid子查询的方式能保证每次删除的行分散在物理存储上避免连续删除造成页面热点。我自己在大促后清理过期订单流水时会配合pg_sleep在每批之间暂停几秒给主从同步留出缓冲时间。循环脚本可以用 shell 或任何语言写核心就是一批删完看影响行数不为 0 就继续。4.5 RETURNING 拿到操作后的实时数据UPDATE 或 INSERT 之后需要立刻用新数据继续业务逻辑很多人会选择重新 SELECT 一次。其实 PostgreSQL 的RETURNING可以直接把变更后的行返回UPDATE inventory SET quantity quantity - 5 WHERE product_id 99 RETURNING product_id, quantity AS new_quantity;应用层代码里就可以省掉一次查询。比如扣库存后要返回剩余数量给前端不用再查库代码路径短了一半。5. 函数与特殊类型日期、JSON、数组的高频玩法5.1 日期时间处理date_trunc 与 intervalPostgreSQL 的日期时间函数非常强大date_trunc用来按天、按小时、按月分组统计特别方便SELECT date_trunc(day, created_at) AS day, count(*) AS order_count FROM orders GROUP BY date_trunc(day, created_at) ORDER BY day DESC LIMIT 30;date_trunc支持minute、hour、day、week、month、quarter、year等粒度做报表时几乎取代了繁琐的DATE_FORMATGROUP BY组合。注意它返回的是 timestamp 类型不是字符串所以排序和后续计算都更自然。算两个时间点之间的天数差SELECT extract(epoch FROM (now() - created_at)) / 86400 AS days_ago FROM orders ORDER BY created_at DESC LIMIT 1;extract(epoch FROM interval)会把时间间隔转成秒数再除以 86400 换算成天。这个方法比直接减时间再转天数要精确得多不受夏令时等时区变化影响。5.2 JSONB 操作取值、更新、查询一次说清PostgreSQL 的 JSONB 类型几乎是半个文档数据库。日常操作主要集中在四个运算符-返回 JSONB 类型用于继续链路操作。-返回文本类型用于普通字符串比较。# 或 #根据路径取深层值。判断 JSONB 是否包含某个结构。实际场景从字段里取用户信息SELECT >UPDATE user_events SET data jsonb_set(data, {address, city}, 新城市, true) WHERE id 1;这里有个 version 注意点jsonb_set的第四个参数create_missing控制路径不存在时是否自动创建。PostgreSQL 14 之前必须显式传14 之后默认值变更为true不传也能自动创建。这是升级后行为变化的典型案例代码在 13 上能跑到 14 上行为突然不一样了。5.3 数组与聚合unnest、array_agg、string_aggunnest是处理数组和 JSON 数组的利器。比如某张 tags 字段存的是逗号分隔的字符串想拆成多行查统计SELECT tag, count(*) AS tag_count FROM user_articles, unnest(string_to_array(tags, ,)) AS tag GROUP BY tag ORDER BY tag_count DESC;LATERAL是隐式发生的unnest在 FROM 中展开后自然与主表形成关联。string_to_array负责把 CSV 转数组再由unnest展开成行。反过来把多行聚合成一行用array_agg或string_aggSELECT order_id, string_agg(product_name, 、 ORDER BY product_name) AS product_list FROM order_items GROUP BY order_id;string_agg的排序参数是可选的但建议总是带上。不指定顺序时字符串拼接顺序不可控常见于多并发执行时结果不稳定。6. 权限、备份与日常维护的必背 SQL6.1 用户与权限快速查询新同学接手系统搞不清谁对哪个库有什么权限下面这条直接列出所有非系统用户的角色和归属SELECT r.rolname AS role_name, r.rolsuper AS is_super, r.rolcreatedb AS can_create_db, r.rolcanlogin AS can_login, ARRAY(SELECT b.rolname FROM pg_auth_members m JOIN pg_roles b ON b.oid m.roleid WHERE m.member r.oid ) AS member_of FROM pg_roles r WHERE r.rolname NOT LIKE pg_% ORDER BY r.rolname;给业务账号只读权限是标配操作GRANT CONNECT ON DATABASE your_db TO readonly_user; GRANT USAGE ON SCHEMA public TO readonly_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user; -- 对将来新建的表也自动授予权限 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;ALTER DEFAULT PRIVILEGES这行特别关键。如果只执行前三条新表创建后只读账号依然看不到。我在一个数据分析平台上吃过这个亏报表账号头一天还能查第二天开发建了新表查询直接报权限不足。找了一圈原因才发现漏了默认权限。6.2 VACUUM 与 ANALYZE 不只是命令很多人把 VACUUM 和 ANALYZE 当成同一个概念其实分工不同。VACUUM 回收死元组占用的存储空间ANALYZE 更新统计信息供查询计划器使用。二者经常连用但触发时机不一样-- 只更新统计信息不加锁适合频繁执行 ANALYZE your_table_name; -- 回收空间并更新统计信息依旧在线不会锁表阻塞读写 VACUUM (ANALYZE, VERBOSE) your_table_name;关于 VACUUM 有个经典误解VACUUM 不回收空间给操作系统而是交还给 PostgreSQL 内部。所以执行完成后用ls -lh看文件大小可能没变化只有pg_size_pretty(pg_total_relation_size(...))变小才说明真正回收了。真要缩文件给操作系统只能VACUUM FULL但这会拿到 ACCESS EXCLUSIVE 锁生产环境必须极其谨慎我通常只在维护窗口使用。Tracking autovacuum 是否卡住看这个视图SELECT schemaname, relname, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables WHERE last_autovacuum IS NULL OR last_autoanalyze IS NULL;长时间没有 autovacuum 记录的表要么是大表因长事务被跳过要么是配置问题导致 worker 不足。这是排查表越来越肥的起点。6.3 快速生成批量清理脚本的思路维护环境里经常要批量对几十张表执行同样操作比如把某模式下所有表的所有者改掉SELECT format(ALTER TABLE %I.%I OWNER TO new_owner;, schemaname, tablename) FROM pg_tables WHERE schemaname public;把查询结果导出成 SQL 脚本再执行即可。format函数里的%I是安全的标识符占位符会自动处理表名中的特殊字符防止注入问题。同理批量授权、批量开启 RLS、批量检查膨胀都能用这种查询生成 SQL的模式。批量给所有表添加外键约束也可以先把候选语句查出来人工审一遍再执行比手写几十条 UPDATE 安全得多。7. 生产环境高频报错的排查实录7.1 连接数打满remaining connection slots报错信息一般是FATAL: sorry, too many clients already或者remaining connection slots are reserved for non-replication superuser connections。这是生产环境最常见的故障之一。排查思路是固定的先看当前连接数分布找出占用大头SELECT state, count(*) FROM pg_stat_activity GROUP BY state;重点找idle in transaction会话SELECT pid, state, query_start, xact_start FROM pg_stat_activity WHERE state idle in transaction;找到问题会话后跟业务方确认再终止SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state idle in transaction AND now() - xact_start interval 10 minutes;这里谈一个经验点与其等连接满了再清理不如在应用层设置连接池最大连接数并给 PostgreSQL 的max_connections留出 20% 的冗余量。连接池打满和数据库打满处理难度完全是两个级别。7.2 主键冲突背后的序列断层插入数据时报duplicate key value violates unique constraint ...很多新人第一反应是数据重复但还有一种隐蔽原因表主键用了序列但序列的当前值小于表内已有最大 id。这种问题多发生在手工导数据、跨环境迁移数据之后。排查语句SELECT nextval(your_table_id_seq); -- 当前序列值 SELECT max(id) FROM your_table_name; -- 表内实际最大 id如果序列值小于等于表内最大 id修复方式SELECT setval(your_table_id_seq, (SELECT max(id) FROM your_table_name));注意 setval 的第二个参数是最小目标值执行后下一次 nextval 会返回 max1。这种坑在迁移数据时特别常见我在恢复备份后就踩过之后养成了迁移后必查序列的习惯。7.3 慢查询与执行计划异常的处理路径某条 SQL 昨天还快今天突然慢了第一反应不是加索引而是看执行计划变没变EXPLAIN ANALYZE SELECT ...;关键看 Seq Scan vs Index Scan 的占比。统计信息过期导致错误选择全表扫描的情况十有八九是 ANALYZE 没跟上。这时先执行一遍ANALYZE your_table_name;再重查执行计划很多时候问题直接消失。如果 ANALYZE 之后依然走全表扫描再思考是不是条件列的数据分布倾斜或者索引本身选择性不足用pg_stats查看字段分布SELECT attname, n_distinct, most_common_vals, most_common_freqs FROM pg_stats WHERE tablename your_table_name AND attname the_column;一张表的数据只有几百行时索引扫描反而不如全表扫描快这是优化器基于代价做出的合理选择无需强行干预。7.4 字符集与编码相关的隐性问题PostgreSQL 的库是在初始化时确定编码的最常见的默认值是 UTF8。乱码问题大多发生在客户端连接编码不对而不是数据库本身出问题。遇到乱码先看SHOW client_encoding; SHOW server_encoding;如果二者不一致执行SET client_encoding TO UTF8;再重新查询。另一种情况是locale和排序规则不一致导致的索引失效常见于从旧库克隆数据而不是逻辑导入。这类问题排查起来特别隐性页面端显示正常排序顺序却是乱的。稳妥做法同构迁移时保持 LC_COLLATE / LC_CTYPE 一致异构迁移完成立即抽查中文字段排序和索引走没走。8. 这份合集的使用方式与维护计划我个人在使用和维护这份合集时的几条真实体会或许对你有参考价值。体会一SQL 合集不是越全越好而是越场景化越好。这份文档里我坚持每条 SQL 都配上什么时候用、为什么不这么写就会出问题。有了场景锚点记忆效率会大幅提升。你在收藏别人整理的大段文档时也可以尝试用自己的业务场景重新注释一遍。体会二系统表和视图的记忆有规律可循。pg_stat_*系列全是运行时统计比如pg_stat_activity当前活跃会话、pg_stat_user_tables表的统计信息、pg_stat_user_indexes索引使用统计。pg_*不带stat的是元数据。information_schema是标准 SQL 视图跨数据库兼容性好。我用一个本地笔记本把这些视图的关系画出来之后查任何系统信息都能快速定位正确视图。体会三这些 SQL 可以落成监控脚本。我目前是把关键几条比如连接数分布、死锁阻塞链、表膨胀比例做成了定时巡检任务每天凌晨跑一遍异常就推告警。效果比事后处理强很多。这份合集我不打算一次性写完而是按专题持续分批补充。下一批计划补以下方向分区表维护相关的管理 SQL包括分区拆分的流程和查各分区数据占比。逻辑复制和订阅状态监控排查复制延迟的专用语句。更细的锁等待分析区分行锁、页锁、咨询锁以及锁超时配置建议。SQL 调优时使用EXPLAIN各类选项的实际解读案例。你可以把这篇文章当作一个长期维护的手册来用每次遇到新问题、沉淀出可复用的语句我都会同步更新进来。如果有你觉得特别实用但这里没有的语句也欢迎在评论区留言下一批更新我会优先补上。