1. 为什么需要一套能直接用的PostgreSQL SQL合集

做后端开发、数据分析和运维的人，几乎都躲不开PostgreSQL。相比MySQL，PostgreSQL在数据类型、窗口函数、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永远返回NULL；not 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与递归CTE

CTE（Common 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之类的hint？PostgreSQL官方没有这个机制，但可以借助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突然变慢”“应用连不上”“死锁报错”之类的问题，我会按表里列的思路逐条排查。

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沉淀成视图或者查询模板，然后结合参数化查询调用。例如创建一张“今日订单统计视图”，后续数据分析只用查视图，不用关心底层过滤条件是否遗漏。视图不会显著提升性能，但能显著提升可维护性，这本身就是长期价值。