1. 为什么需要一套能直接用的PostgreSQL SQL合集做后端开发、数据分析和运维的人几乎都躲不开PostgreSQL。相比MySQLPostgreSQL在数据类型、窗口函数、JSON支持、索引类型上的能力强出不少但也正是因为功能多日常写SQL时反而容易犯迷糊同样的需求可能有四五种写法到底哪种最稳有些语法在MySQL里用惯了换到PostgreSQL上却报错或者结果不对问题出在哪这篇合集就是冲着“直接用”去的。我把自己在真实项目中反复用到、踩过坑、验证过的PostgreSQL常用SQL整理成一套可以随时翻的备忘录覆盖库表管理、基础增删改查、字段处理、正则匹配、日期转换、聚合窗口函数、CTE递归、索引调优、执行计划分析、备份恢复和常见故障排查。适合三类人刚接触PostgreSQL、需要快速上手写业务SQL的开发者从MySQL迁过来、想搞清楚语法差异和写法习惯的人需要维护现有数据库、排查慢SQL和死锁问题的运维同学先说一个核心体会PostgreSQL的SQL能力太强强到很多人反而被“选项多”困住了。比如同样做字符串拼接有concat、||、string_agg不同场景下性能差异很大同样的分页为什么有人越翻越慢同样的去重distinct on和row_number()差别在哪。这些细节不搞清楚照搬别人的SQL很容易出幺蛾子。下面我按“从库表到查询、从查询到调优、从调优到恢复”这条线把高频而且真正用得上的部分逐一拆开讲并附上为什么这么写以及有哪些坑。2. 库表管理与基础操作——先把手头的“数据仓库”收拾干净2.1 数据库与SCHEMA常用操作日常开发中一个实例里会建多个库一个库里又会按业务拆多个schema。PostgreSQL里“库”和“schema”是两层概念库之间完全隔离schema之间在同一个库内共享权限模型。实际操作中我习惯把“子系统”用schema隔离而不是用库隔离因为这样可以用一套连接串访问多个业务模块跨模块join也方便。-- 创建数据库 CREATE DATABASE mydb WITH ENCODING UTF8 LC_COLLATE en_US.UTF-8 LC_CTYPE en_US.UTF-8 TEMPLATE template0; -- 切换到目标库 \c mydb -- 创建schema CREATE SCHEMA IF NOT EXISTS biz; -- 修改schema所有权 ALTER SCHEMA biz OWNER TO app_user; -- 查看所有schema及属主 SELECT nspname, nspowner::regrole FROM pg_namespace; -- 设置默认schema搜索路径 SET search_path TO biz, public;这里踩过的坑是创建库的时候如果模板不是template0编码和collation可能继承模板库的设置导致排序规则不符合预期。LC_COLLATE影响order by对字符串的排序规则比如中文排序在某些环境下会按照拼音在某些环境下按照二进制编码如果不确定就统一用en_US.UTF-8或C避免后续统计时出现“感觉排得不顺”的问题。另外search_path在高版本里还支持$user前缀建议把它放在最前面这样每个用户默认看到自己的schema业务隔离更清晰。2.2 表结构与字段修改PostgreSQL对表结构的修改基本都支持在线DDL但不代表没有代价。比如alter table ... add column如果加了default值在早期版本里会重写整张表新版虽然做了优化但是对超大数据量的大表依然要评估影响。更稳妥的做法是先加字段再分批次用后台任务回填默认值最后再考虑加约束。-- 创建业务表 CREATE TABLE IF NOT EXISTS biz.order_info ( id BIGSERIAL PRIMARY KEY, order_no VARCHAR(64) NOT NULL, user_id BIGINT NOT NULL, amount NUMERIC(12,2) NOT NULL DEFAULT 0, status SMALLINT NOT NULL DEFAULT 0, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- 增加字段 ALTER TABLE biz.order_info ADD COLUMN IF NOT EXISTS remark TEXT; -- 修改字段类型注意可能触发全表扫描或重写 ALTER TABLE biz.order_info ALTER COLUMN amount TYPE NUMERIC(14,2); -- 设置/删除默认值 ALTER TABLE biz.order_info ALTER COLUMN status SET DEFAULT 1; ALTER TABLE biz.order_info ALTER COLUMN status DROP DEFAULT; -- 修改字段名 ALTER TABLE biz.order_info RENAME COLUMN remark TO comment; -- 添加约束 ALTER TABLE biz.order_info ADD CONSTRAINT uk_order_no UNIQUE (order_no); ALTER TABLE biz.order_info ADD CONSTRAINT chk_status CHECK (status IN (0,1,2)); ALTER TABLE biz.order_info DROP CONSTRAINT IF EXISTS chk_status; -- 添加索引后面会细讲 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_order_user_id ON biz.order_info (user_id);ALTER TABLE ... ADD COLUMN IF NOT EXISTS是我个人强烈建议用的写法尤其是在自动化脚本、定时任务、迁移脚本里。因为重复执行不会报错可以省去很多判断逻辑。另外要注意ALTER TABLE里如果同时修改多个属性最好分成多条独立语句执行因为不同步骤可能持有不同的锁级别混在一起反而增加死锁和锁等待的概率。2.3 数据插入与批量装载批量写入是业务开发中最常见的操作之一。PostgreSQL里批量插入最常见的写法是insert into ... values (...), (...), (...)这一点跟MySQL一致但更推荐用unnest或者copy来提升效率尤其是几万条以上的数据。-- 普通批量插入 INSERT INTO biz.order_info (order_no, user_id, amount, status) VALUES (A001, 1001, 99.00, 0), (A002, 1002, 199.00, 0); -- 从查询结果插入 INSERT INTO biz.order_info (order_no, user_id, amount, status) SELECT order_no, user_id, amount, 0 FROM tmp_import WHERE valid_flag 1; -- 使用unnest按数组批量插入适合大量数据 INSERT INTO biz.order_info (order_no, user_id, amount, status) SELECT * FROM unnest( ARRAY[B001,B002,B003], ARRAY[2001,2002,2003], ARRAY[10.00,20.00,30.00], ARRAY[0,0,0] ) AS t(order_no, user_id, amount, status); -- 如果应用端能生成文件建议用copy速度最快 -- 应用侧执行COPY biz.order_info FROM path/to/file.csv WITH (FORMAT CSV, HEADER true);用unnest时有几个细节一是数组类型必须严格匹配列类型比如ARRAY[10.00,20.00]会被推断成numeric但是ARRAY[10,20]是integer插入到numeric列没问题反过来就不行二是如果其中一个数组是NULL整条生成的逻辑就可能错位用之前最好先做补全。copy虽然快但有权限限制普通用户不一定能直接操作服务端文件这时可以用pg_copy之类的客户端命令或者\copy元命令绕过。3. 日常查询与过滤——能少一次回表就多一分效率3.1 条件过滤与去重PostgreSQL的where写法和标准SQL基本一致但有几个值得注意的地方NULL的判断要使用is null因为 null永远返回NULLnot in遇到NULL子查询时结果容易掉数据这是SQL标准行为很多人在这上面吃过亏。-- 常规过滤 SELECT order_no, user_id, amount FROM biz.order_info WHERE status 0 AND amount 100 AND created_at now() - interval 7 days; -- 注意NULL陷阱 -- 错误示范如果sub_status存在NULL下面查询会过滤掉正常记录 SELECT * FROM biz.order_info WHERE sub_status NOT IN (1,2,3); -- 正确写法 SELECT * FROM biz.order_info WHERE sub_status IS DISTINCT FROM ALL (1,2,3); -- 去重的两种方式 -- 方式一distinct简单但不够灵活 SELECT DISTINCT user_id FROM biz.order_info WHERE status 1; -- 方式二distinct on按某列分组后每组取一行 SELECT DISTINCT ON (user_id) user_id, order_no, amount FROM biz.order_info WHERE status 1 ORDER BY user_id, created_at DESC;DISTINCT ON是PostgreSQL的特色语法用好了非常香。比如“查每个用户最近一笔订单”不需要写子查询一个DISTINCT ON加ORDER BY就搞定。但它有一个硬性要求ORDER BY的开头部分必须包含DISTINCT ON括号里的字段否则会报错或者结果不符合直觉。另外DISTINCT ON的列不会自动排序所以如果你不关心分组内顺序可以单独只排那一列减少排序开销。3.2 模糊匹配与字符处理模糊匹配是LIKE还是正则需要根据场景选。LIKE不走普通索引除非使用pg_trgm扩展正则表达式功能强但计算代价更高不适合用在大表的过滤条件上。-- 基础模糊匹配 SELECT * FROM biz.order_info WHERE order_no LIKE A%; SELECT * FROM biz.order_info WHERE order_no LIKE %001%; -- 忽略大小写 SELECT * FROM biz.order_info WHERE lower(order_no) LIKE lower(%a%); -- 正则匹配 SELECT * FROM biz.order_info WHERE order_no ~ ^A[0-9]{3}$; SELECT * FROM biz.order_info WHERE order_no !~ ^B; -- LIKE的转义写法 SELECT * FROM biz.product WHERE name LIKE %\\_%; -- 字符串拼接 SELECT concat(order_no, -, status) FROM biz.order_info LIMIT 5; SELECT order_no || - || status FROM biz.order_info LIMIT 5; -- 聚合拼接 SELECT user_id, string_agg(order_no, , ORDER BY created_at DESC) FROM biz.order_info GROUP BY user_id;这里要特别说下string_agg它是把多行字符串聚合成一个字符串非常实用。比如把某个用户的所有订单号拼成一列直接一条group by就能完成回到应用层再处理list。要注意的是string_agg的结果默认是text如果拼的内容太多要注意内存和字段长度限制。还有一个经常被忽略的坑聚合时用ORDER BY是PG的扩展能力别去依赖MySQL里那种group_concat的写法两边语法不同。3.3 时间与日期处理PostgreSQL的日期时间处理是我见过最强大的没有之一。它同时支持timestamp with time zone和timestamp without time zone但这两者之间的隐含转换经常造成数据偏差。线上环境我强烈建议统一用timestamptz也就是带时区的时间戳应用层传过来的时间都转成UTC存储查询时按本地时区展示。-- 当前时间 SELECT now(); SELECT current_timestamp; SELECT clock_timestamp(); -- 时间加减 SELECT now() - interval 1 day; SELECT now() interval 30 minutes; SELECT now() - interval 2 hours; -- 时间字段按天/月聚合 SELECT date_trunc(day, created_at) AS day, count(*) FROM biz.order_info GROUP BY date_trunc(day, created_at) ORDER BY day DESC; -- 计算两个时间的间隔 SELECT age(now(), created_at) FROM biz.order_info LIMIT 5; -- 时间格式化 SELECT to_char(created_at, YYYY-MM-DD HH24:MI:SS) FROM biz.order_info LIMIT 5; -- 从时间戳提取字段 SELECT EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at), EXTRACT(DOW FROM created_at) FROM biz.order_info LIMIT 5;date_trunc在报表统计里使用频率极高。它可以把时间截断到秒、分钟、小时、天、周、月、年。做报表时按天统计就用date_trunc(day, created_at)按周统计就用date_trunc(week, created_at)。需要注意PG里一周的开始默认是周一跟一些团队习惯的周日开始不一样统计周报时要先确认口径否则容易跟业务方扯皮。4. 聚合、窗口函数与CTE——一条SQL解决复杂分析4.1 聚合查询聚合函数除了常用的count、sum、avg、max、min外PostgreSQL还提供array_agg、json_agg、jsonb_agg、string_agg等高级聚合。它们可以把多行数据聚合成数组、JSON或字符串在很多场景下直接取代了“先查询再应用层组装”的写法。-- 基础聚合 SELECT status, count(*), sum(amount), avg(amount) FROM biz.order_info GROUP BY status; -- 常见的having SELECT user_id, sum(amount) AS total FROM biz.order_info WHERE created_at date_trunc(month, now()) GROUP BY user_id HAVING sum(amount) 1000; -- 聚合为数组 SELECT user_id, array_agg(order_no ORDER BY created_at DESC) AS order_list FROM biz.order_info GROUP BY user_id LIMIT 10; -- 聚合为JSON SELECT user_id, json_agg(json_build_object(order_no, order_no, amount, amount)) FROM biz.order_info GROUP BY user_id LIMIT 10; -- 同时算多个聚合注意filter语法 SELECT status, count(*) AS total_cnt, count(*) FILTER (WHERE amount 100) AS high_amount_cnt FROM biz.order_info GROUP BY status;FILTER子句是PostgreSQL的亮点。你可以在一个count或sum里加一个类似where的子句实现“分组内按条件统计”不需要写多个case when也不需要用left join把多个统计结果关联起来。它的可读性和性能都更好。不过要注意FILTER不是标准SQL换数据库时要改写法。4.2 窗口函数要点窗口函数是PostgreSQL的“王炸”功能也是数据分析场景里最值得学的部分。它和普通聚合函数最大的区别是聚合会把多行合并成一行窗口函数保留每一行同时还能看到分组后的聚合值。我用得最多的是row_number()、rank()、lag()、lead()、sum() over()这几类。-- 分组排序取前N名 SELECT user_id, order_no, amount, row_number() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn FROM biz.order_info; -- 取每个用户金额最高的订单 SELECT * FROM ( SELECT user_id, order_no, amount, row_number() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn FROM biz.order_info ) t WHERE rn 1; -- 累计求和running total SELECT created_at::date AS dt, amount, sum(amount) OVER (ORDER BY created_at::date) AS running_total FROM biz.order_info ORDER BY created_at::date; -- 同环比用lag取上一周期的值 SELECT created_at::date AS dt, sum(amount) AS daily_total, lag(sum(amount)) OVER (ORDER BY created_at::date) AS prev_day_total FROM biz.order_info GROUP BY created_at::date ORDER BY created_at::date; -- 移动平均近7天 SELECT created_at::date AS dt, avg(amount) OVER (ORDER BY created_at::date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d FROM biz.order_info;窗口函数有两个容易犯的错。第一row_number()和rank()的区别row_number总是1、2、3连续编号rank遇到并列会跳过后续编号比如并列第一会出现1、1、3。需要稳定行号用row_number需要排名语义用rank或dense_rank。第二over()里的order by会影响窗口的计算范围如果只写over(partition by user_id)整个分组是一个窗口如果加了order by默认的窗口范围是“从分区第一行到当前行”很多时候这跟预期不一样要小心。实在不确定就显式写ROWS BETWEEN那一段宁可长一点也别让结果跑偏。4.3 CTE与递归CTECTECommon Table Expression在PostgreSQL中非常常用。它可以让你把一条复杂查询拆成多个可读的步骤也方便调试。注意高版本PostgreSQL对CTE默认有“物化/内联”优化的改变老版本CTE会被物化成临时结果新版本在部分情况下会把它内联到下层的SQL中这会影响性能表现。所以写CTE的时候不要理所当然地认为它能当“临时表”用。-- 基础CTE WITH recent_order AS ( SELECT * FROM biz.order_info WHERE created_at now() - interval 24 hours ) SELECT user_id, count(*) FROM recent_order GROUP BY user_id; -- 多次引用CTE观察是否物化 WITH t AS ( SELECT user_id, amount FROM biz.order_info WHERE status 1 ) SELECT * FROM t t1 JOIN t t2 ON t1.user_id t2.user_id LIMIT 10; -- 递归CTE生成1到10的序列 WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n 1 FROM t WHERE n 10 ) SELECT n FROM t; -- 递归CTE查询部门树典型场景 WITH RECURSIVE dept_tree AS ( SELECT dept_id, parent_dept_id, dept_name FROM biz.dept_info WHERE dept_id 1 UNION ALL SELECT d.dept_id, d.parent_dept_id, d.dept_name FROM biz.dept_info d JOIN dept_tree dt ON d.parent_dept_id dt.dept_id ) SELECT * FROM dept_tree;递归CTE是我处理树形结构时的首选。比如部门层级、目录树、评论楼中楼一条WITH RECURSIVE就能把整棵子树拉出来比在应用层递归查数据库优雅太多也少了很多次网络往返。唯一需要注意的是递归必须有终止条件否则会形成死循环。我一般的做法是让递归列带上层级深度并在递归部分加上WHERE depth 10之类的限制防止脏数据把查询拖垮。5. 索引、执行计划与性能调优5.1 索引的类型与创建时机PostgreSQL支持B-tree、Hash、GIN、GiST、SP-GiST、BRIN等索引。日常OLTP业务里90%的场景用B-tree就够全文检索和JSON/数组查询用GIN地理位置用GiST一般配合PostGIS超大表按物理顺序存储后用BRIN有明显收益。-- 常规B-tree索引 CREATE INDEX CONCURRENTLY idx_order_user_id ON biz.order_info (user_id); CREATE INDEX CONCURRENTLY idx_order_created_at ON biz.order_info (created_at DESC); -- 复合索引注意列顺序 CREATE INDEX CONCURRENTLY idx_order_user_created ON biz.order_info (user_id, created_at DESC); -- 唯一索引 CREATE UNIQUE INDEX CONCURRENTLY uk_order_no ON biz.order_info (order_no); -- 部分索引只给符合条件的行建索引体积小很多 CREATE INDEX CONCURRENTLY idx_order_active ON biz.order_info (created_at) WHERE status 0; -- 表达式索引对函数结果建索引 CREATE INDEX CONCURRENTLY idx_order_lower_no ON biz.order_info (lower(order_no)); -- GIN索引支持数组和JSON CREATE INDEX CONCURRENTLY idx_product_tags ON biz.product USING gin (tags); -- 查看表索引 SELECT * FROM pg_indexes WHERE tablename order_info;建索引最重要的原则是“贴合查询”。where里的等值条件、order by里的排序字段、join的关联字段通常都适合进索引。复合索引的列顺序非常讲究等值条件的列放在前面范围条件的列放在后面因为从第二个列开始B-tree的定位能力会因为前面列的范围扫描而快速减弱。比如WHERE user_id 1 AND created_at 2024-01-01复合索引应该是(user_id, created_at)反过来建成(created_at, user_id)之后user_id等值条件没法快速定位。这一点是很多人建索引无效的元凶。5.2 查看执行计划PostgreSQL的优化器会基于统计信息生成执行计划。要判断SQL快不快、索引有没有被用上核心手段就是看执行计划。-- 查看执行计划不真正执行 EXPLAIN SELECT * FROM biz.order_info WHERE user_id 1001; -- 执行并显示实际耗时和行数 EXPLAIN ANALYZE SELECT * FROM biz.order_info WHERE user_id 1001; -- 格式化输出 EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM biz.order_info WHERE user_id 1001; -- 分析表的统计信息防止统计信息过旧导致错误计划 ANALYZE biz.order_info;看执行计划时我先看有没有Seq Scan全表扫描。如果是大表而且过滤性很好全表扫描通常意味着索引没建对或者没被使用再看Index Scan和Bitmap Index Scan的区别。Index Scan适合返回行数少的场景Bitmap Index Scan适合返回行数较多的场景。还有一个高频问题EXPLAIN显示的rows估算和实际值差异巨大这时候八成是统计信息过期了跑一次ANALYZE就能大幅改善。5.3 一个容易被忽略的坑参数化查询与缓存计划在开发环境一切正常一到生产环境就慢这种问题通常和执行计划缓存有关。PostgreSQL默认使用通用计划generic plan当SQL语句通过PREPARE或者某类驱动以参数化方式提交时优化器会根据参数值的分布情况选择一个通用计划。如果数据分布非常不均匀比如status字段99%是0、1%是1通用计划可能在全表扫描和索引扫描之间反复摇摆。解决办法有几类给对应字段建立更精准的部分索引引导优化器选择索引扫描更新表的统计信息必要时提高default_statistics_target在关键查询里使用FORCE INDEX之类的hintPostgreSQL官方没有这个机制但可以借助pg_hint_plan扩展生产环境慎用用EXECUTE ... USING参数化时注意评估计划缓存的影响我踩过最典型的坑是某表按user_type字段查询90%数据是普通用户10%是VIP。通用计划选择全表扫描VIP用户查询时瞬间变成几千万行的扫描。后来加了一个WHERE user_type vip AND created_at ...的复合部分索引问题才解决。6. 恢复、备份与日常维护6.1 备份与恢复备份恢复是数据库的底线能力。PostgreSQL备份方式主要分逻辑备份和物理备份。逻辑备份用pg_dump适合中小库、单库迁移和表级备份物理备份用pg_basebackup适合大库和需要时间点恢复PITR的场景。# 逻辑备份整个库 pg_dump -h 127.0.0.1 -U app_user -d mydb -F c -f /backup/mydb_20250101.dump # 备份指定表 pg_dump -h 127.0.0.1 -U app_user -d mydb -t biz.order_info -F c -f /backup/order_info.dump # 恢复 pg_restore -h 127.0.0.1 -U app_user -d mydb -c /backup/mydb_20250101.dump # 使用psql恢复纯SQL备份 # psql -h 127.0.0.1 -U app_user -d mydb -f /backup/mydb.sql如果要保证备份的一致性建议用pg_dump -F c自定义格式不仅体积小还支持并行恢复和选择性恢复。恢复时如果目标库已有部分数据加上-c先清理数据库对象再重建比较稳妥否则主键冲突会很烦。6.2 常规维护数据库不是建完就完事的日常维护需要关注几件事膨胀、死元组、统计信息、未使用索引和连接数。-- 查看死元组数量和表膨胀情况 SELECT relname, n_dead_tup, n_live_tup, round(n_dead_tup * 100.0 / (n_live_tup n_dead_tup 1), 2) AS dead_pct FROM pg_stat_user_tables ORDER BY dead_pct DESC; -- 手动触发vacuum VACUUM (VERBOSE, ANALYZE) biz.order_info; -- 查看膨胀最严重的表需要扩展 SELECT * FROM pgstattuple(biz.order_info); -- 查看连接数 SELECT count(*), state FROM pg_stat_activity GROUP BY state; SELECT current_setting(max_connections); -- 终止某个卡死的查询 SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state active AND pid pg_backend_pid(); -- 如果取消不了强制终止 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid 12345;vacuum是PostgreSQL特有的机制因为MVCC多版本控制会让被更新、删除的行留下死元组。死元组多了表膨胀查询变慢索引变大。自动autovacuum默认开着但大表、频繁update的表经常来不及回收需要手动介入。生产环境执行VACUUM不会锁表可以放心跑但VACUUM FULL会锁表并重写表务必在维护窗口执行。7. 故障排查与实战速查7.1 常见问题速查表下面这份速查表是我实际工作中总结出来的。每次遇到“SQL突然变慢”“应用连不上”“死锁报错”之类的问题我会按表里列的思路逐条排查。现象可能原因建议排查手段同一SQL有时快有时慢统计信息过期 / 数据分布不均匀执行ANALYZE查看执行计划查询走了索引还是慢索引选择性差 / 回表太多查看Index Scan的rows估算考虑部分索引或复合索引更新删除卡住锁等待查pg_stat_activity中wait_event和lock相关视图插入越来越慢表膨胀 / 索引膨胀查看死元组比例执行VACUUM连接数打满连接泄漏 / 配置过小查pg_stat_activity按client_addr聚合查应用连接池配置大量deadlock报错应用事务加锁顺序不一致检查业务代码中多个事务获取锁的先后顺序磁盘占用异常增大WAL日志积累 / 膨胀查pg_wal目录查看VACUUM状态与WAL归档设置CTE查询性能异常新版本CTE内联策略改变用EXECUTE或MATERIALIZED控制CTE行为7.2 一个真实的排查案例有一次线上订单表查询从几十毫秒突然变成十几秒应用侧收到一堆慢SQL告警。我当时第一反应是看执行计划结果发现user_id字段上的索引没走走了Seq Scan。再查统计信息发现这张表已经超过6小时没有autovacuum了n_dead_tup在大几千行优化器的估算严重失真。手动执行ANALYZE后执行计划恢复正常查询速度降到几十毫秒。但为了根治我又查了autovacuum的配置发现这张大表的autovacuum_vacuum_threshold和autovacuum_vacuum_scale_factor沿用了默认值对于上亿行的表默认的触发阈值需要很久才能触发一次。调整了表的autovacuum参数后问题再没复现过。7.3 排查锁等待与死锁当出现应用卡死、接口超时时大概率是锁等待。PostgreSQL的锁等待不会无限期默认lock_timeout是0就是一直等。为了不让一个坏SQL拖垮整个库建议在数据库层设置合理的锁超时-- 设置会话锁超时 SET lock_timeout 5s; -- 查看当前等待锁的会话 SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event_type Lock; -- 查看锁信息 SELECT * FROM pg_locks WHERE NOT granted; -- 找到阻塞源头 SELECT blocking.pid AS blocking_pid, blocked.pid AS blocked_pid, blocked.query AS blocked_query FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid ANY(pg_blocking_pids(blocked.pid));死锁出现时PostgreSQL会自动检测并回滚其中一个事务应用侧会收到deadlock detected的错误。这类问题根因几乎都是业务代码里多个事务获取锁的顺序不一致。比如事务A先更新订单再更新用户事务B先更新用户再更新订单两条路径就可能互相等待。解决方案是统一业务中的加锁顺序或者在允许的情况下用select ... for update提前显式锁定行减少隐式锁带来的不确定性。8. 持续更新维护与个人经验这一节说点“方法论”层面的东西。为什么会有“持续更新中”这个标题因为PostgreSQL本身的版本迭代很快9.6、10、11、12、13、14、15、16、17几乎每一代都有语法和行为上的优化。有些SQL在旧版本上是痛点新版本已经自动解决比如ALTER TABLE ... ADD COLUMN带默认值的行为、CTE的内联策略变化、MERGE语句的引入。所以这套合集不应该是一篇写完就扔的文章而是跟着版本走、跟着实际踩坑记录走的活文档。我自己的维护方式是每当在项目中遇到一个“查了文档、试了多遍才搞定”的SQL场景就把它记录到一个固定的笔记里按模块分类库表操作、查询过滤、聚合分析、性能调优、备份恢复、故障排查每一条都附带当时的业务背景和最终可以跑通的版本。过一段时间再回头审视如果发现同类的写法出现了两次就说明这个场景足够高频值得专门写一节展开讲。最后再分享一个很小的技巧。很多人在实际维护中会反复使用某些“带参数的查询”比如按时间范围查订单、按状态统计数量。如果你发现同一类查询每天要写很多遍建议把标准SQL沉淀成视图或者查询模板然后结合参数化查询调用。例如创建一张“今日订单统计视图”后续数据分析只用查视图不用关心底层过滤条件是否遗漏。视图不会显著提升性能但能显著提升可维护性这本身就是长期价值。