1. 从“PG常用SQL”说起：为什么值得专门整理一套
先讲个我自己的经历。很多同学一开始接触的是MySQL，语法熟悉了之后，切到PG（PostgreSQL）数据库，第一反应往往是“不就是SQL嘛，能有多大区别”。结果真到生产环境一跑，各种不习惯：limit写法倒是差不多，但类型转换的::符号、ilike关键字、jsonb操作符、generate_series函数、窗口函数那套，都会让人重新翻阅文档。我也经历过这种“一边查文档一边写SQL”的尴尬阶段，后来干脆把日常开发、运维里高频用的PG语句整理成了一份速查清单，随时翻看，省下不少时间。
这篇博文要做的，就是把这套“PG常用SQL”展开讲透。它不仅是一份语句列表，更会解释每条SQL背后的设计逻辑、使用场景、容易踩的坑，以及我在实际项目里总结出的小经验。不论你是刚接触PG的新手，还是已经用了几年但想系统性补漏的同学，都能从中有所收获。
需要提前说明的是，文中涉及的具体语法基于PG 13及以上版本（部分特性在更高版本更稳定，比如date_bin在PG 14引入），但绝大多数内容在PG 9.6到PG 16之间都能通用。如果版本差异较大，我会专门提一句。
2. 连接与基础对象管理：库、模式、表、权限的日常操作
SQL不只是增删改查，在PG里，写任何业务代码之前，你得先把“容器”打理好。这里的“容器”就是数据库集群、数据库实例、schema、表、索引、角色权限这几层。很多同学直接跳进select *，遇到权限报错、表空间不足、schema找错了，才回头补课，这就是本末倒置了。
2.1 数据库与schema：理解PG的两层命名空间
PG的层级关系是：实例 -> 数据库 -> schema -> 表。MySQL没有schema这一层（MySQL的schema通常就是指数据库），这是两者一个很大的思维差异。在PG中，同一台服务器上可以建多个数据库，每个数据库内部又可以划分多个schema，不同schema里的表可以同名。
日常最常用的语句：
-- 查看当前集群里有哪些数据库 SELECT datname FROM pg_database; -- 创建数据库，指定编码和owner CREATE DATABASE mydb ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C' OWNER myuser; -- 切换当前连接 / 查看当前数据库 \c mydb SELECT current_database(); -- 创建schema CREATE SCHEMA IF NOT EXISTS app; -- 查看当前schema搜索路径 SHOW search_path; -- 设置schema搜索路径（会话级），这样我们写表名时就不用带schema前缀 SET search_path TO app, public;这里最值得展开说的是search_path。它决定了当你写SELECT * FROM users时，PG会按什么顺序去哪个schema里找users这张表。默认值是"$user", public，意思就是先找与你当前用户名同名的schema，找不到再去public里找。如果项目里多个业务模块各自用独立schema（比如app、billing、log），很容易出现“同一个连接，查出来的表不是你以为的那张”。我一般会在项目启动时会话里统一把search_path设成业务schema，或者在数据库用户级别用ALTER ROLE appuser SET search_path TO app, public;固定下来，避免开发环境跟生产环境的表名互相干扰。
2.2 表的创建、修改与约束维护
建表是几乎所有项目的第一步。相比MySQL，PG的约束体系更严格，类型系统也更丰富。一个比较典型的业务表：
CREATE TABLE IF NOT EXISTS users ( id BIGSERIAL PRIMARY KEY, -- 自增主键，等价于 BIGINT GENERATED BY DEFAULT AS IDENTITY email VARCHAR(255) NOT NULL UNIQUE, nickname VARCHAR(64) NOT NULL, age INT CHECK (age >= 0 AND age <= 150), -- 字段级检查约束 tags TEXT[] DEFAULT '{}', -- PG原生数组类型 profile JSONB DEFAULT '{}'::jsonb, -- 直接存JSON，而不是用VARCHAR硬憋 created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- 加一列 ALTER TABLE users ADD COLUMN IF NOT EXISTS phone VARCHAR(20); -- 改列类型：注意USING表达了如何从旧值转换到新值 ALTER TABLE users ALTER COLUMN nickname TYPE VARCHAR(128); -- 加约束 ALTER TABLE users ADD CONSTRAINT users_age_check CHECK (age >= 0); -- 删除约束 ALTER TABLE users DROP CONSTRAINT IF EXISTS users_age_check; -- 把普通表改成分区表（PG 12之后支持用ATTACH PARTITION添加分区） ALTER TABLE users ATTACH PARTITION users_2024 FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');这里有一个我刚开始用PG时没搞明白的细节：BIGSERIAL和IDENTITY的区别。SERIAL是一个伪类型，底层其实是创建一个序列绑到列上，插入时不指定值就会走序列。GENERATED ALWAYS AS IDENTITY则是SQL标准里的写法，更严谨一些，而且默认不允许你手工插入固定值，必须用OVERRIDING SYSTEM VALUE才能强制写入，这在做数据修复时比较安全，不容易误覆盖。新版项目我推荐直接用GENERATED ALWAYS AS IDENTITY，但这并不是说BIGSERIAL不能用，只是习惯上一个更偏标准、一个更偏PG传统。
2.3 角色的创建与最小权限授权
权限管理是生产环境绕不开的事。通常我们会建一个只读账号给报表组，再建一个读写账号给应用服务。PG的权限模型基于“角色”（Role），角色可以理解为“用户”和“用户组”的统一体。
-- 创建只读账号 CREATE ROLE readonly_user LOGIN PASSWORD 'safe_password'; -- 授予连接权限和schema使用权限 GRANT CONNECT ON DATABASE mydb 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; -- 创建应用读写账号 CREATE ROLE app_writer LOGIN PASSWORD 'another_password'; GRANT CONNECT ON DATABASE mydb TO app_writer; GRANT USAGE ON SCHEMA public TO app_writer; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_writer; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;这里最容易被忽略的是ALTER DEFAULT PRIVILEGES。如果你们先授权，后建表，新表默认是不带任何权限的，只读账号就会因为“permission denied for table xxx”而报错。我刚工作那年就困在这个报错里将近两个小时，后来才发现问题是“默认权限”这个概念没理解到位。可以把它理解为“未来创建对象的权限模板”，在初始化数据库时把这个模板设好，后面就不用手动追授权了。
还有个排查权限问题常用的视角：\dp（查看表权限）、\du（查看角色），或者用SQL查：
SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name = 'users';3. 增删改查的进阶型简写与RETURNING精要
DML是SQL的核心，但大多数人只用了最简单的INSERT、UPDATE、DELETE。实际上PG在标准SQL之上做了不少语法糖，用熟了能明显减少应用层的代码量。
3.1 INSERT的几种形态：批量、冲突处理、从查询插入
最常见的是单条插入，不多说。下面这些是实际开发中更常用的：
-- 批量插入：values后面可以跟多组，一次一个round trip INSERT INTO users (email, nickname, age) VALUES ('a@example.com', 'A', 20), ('b@example.com', 'B', 21), ('c@example.com', 'C', 22); -- 从另一张表直接灌数据 INSERT INTO users (email, nickname, age) SELECT email, nickname, age FROM temp_users WHERE age IS NOT NULL; -- 冲突处理：ON CONFLICT（这是PG的一大特色，MySQL有类似功能但语义不同） INSERT INTO users (email, nickname) VALUES ('a@example.com', 'NewName') ON CONFLICT (email) DO UPDATE SET nickname = EXCLUDED.nickname, updated_at = now(); -- 如果冲突就什么都不做 INSERT INTO users (email, nickname) VALUES ('dup@example.com', 'Dup') ON CONFLICT (email) DO NOTHING;ON CONFLICT是PG 9.5开始引入的，我强烈建议多业务场景都用它来代替“先查后插”的代码。举个例子，用户注册时如果邮箱已存在，以前的操作是SELECT id FROM users WHERE email=?，查不到再INSERT，查到了就走更新。这样两条SQL之间有天然的时间窗口，并发场景下仍可能插入重复数据。用ON CONFLICT直接把它变成一条原子语句，应用层逻辑大幅简化，性能也更好。
但这里有一个大家容易踩的坑：ON CONFLICT (email)里的字段必须是实际存在的唯一索引或唯一约束。如果你的表在email字段上根本没有UNIQUE约束，PG会直接报there is no unique or exclusion constraint matching the ON CONFLICT specification。所以建表时就得规划好业务上的唯一键。
3.2 UPDATE与DELETE的RETURNING：省一次查询
PG的RETURNING子句可以返回被修改或删除的行。这是PG比起很多数据库用起来更爽的地方之一。
-- 更新后返回最新行 UPDATE users SET age = 30 WHERE id = 123 RETURNING *; -- 删除后返回被删行，常用于“消费队列”语义 DELETE FROM job_queue WHERE id = ( SELECT id FROM job_queue WHERE status = 'pending' ORDER BY created_at LIMIT 1 ) RETURNING *;这种写法非常实用。比如处理消息队列时，我需要“取出并删除”一条任务，传统做法是SELECT然后DELETE，中间容易重复消费。用一个带RETURNING的DELETE，由于DELETE本身对行加了锁，并发消费者不会拿到同一条任务。再比如更新某个资产状态后，前端需要立刻回显最新数据，RETURNING *直接就把新行返回了，少一次网络往返。
要注意的是，RETURNING *会把整行都返回来，如果行内有大数据字段（比如jsonb里囤了大量明细），会白白增加网络传输。这时可以只返回需要的列：
UPDATE users SET age = 31 WHERE id = 123 RETURNING id, email, age;3.3 WITH（CTE）做多步更新：给DML加“流水线”
WITH子句不仅能用在查询里，还能用来串联多个DML，形成一条逻辑上的流水线。简单例子：把归档表数据迁到历史表，同时从主表删除。
WITH moved_rows AS ( DELETE FROM active_orders WHERE status = 'archived' RETURNING * ) INSERT INTO orders_history SELECT * FROM moved_rows;第一次看到这种写法的同学可能会愣一下：先删然后插？但WITH在PG里可以包含INSERT、UPDATE、DELETE，并且后面可以继续接DML。上面这段SQL在一个事务里完成“从active_orders删除并插入history”，如果中间失败，整个回滚，不会出现数据只删了没插进去的状态。这也是迁移数据时非常稳的写法。
CTE还有一个容易忽略的点：它会物化（PG 12之前），也就是说WITH cte AS (SELECT ...)里的查询结果会被缓存快照，CTE在外面被多次引用时只计算一次。PG 12之后优化器多数情况下会自动内联，但如果CTE含INSERT/UPDATE/DELETE、SELECT带RETURNING，它不会被内联，这是由本身语义决定的，不用担心。
4. 查询核心：过滤、排序、分页、聚合的“标准但不平庸”写法
查询本身是个大话题，这里不展开所有细节，而是挑那些真正高频、且容易写错的点讲。
4.1 空值与过滤逻辑：为什么IS NOT DISTINCT FROM值得记住
几乎每个新人都踩过NULL的坑：明明表里有数据，WHERE nickname != 'bot'却漏掉一批NULL。这是因为在SQL的三值逻辑里，NULL != 'bot'的结果不是true，而是NULL，WHERE只接受true，于是NULL行被过滤掉了。
如果业务上希望“只要不是bot都算匹配，包括没有昵称的”，就要用：
SELECT * FROM users WHERE nickname IS DISTINCT FROM 'bot';这个写法等价于：
SELECT * FROM users WHERE (nickname IS NULL AND 'bot' IS NOT NULL) OR nickname = 'bot';显然第一种简洁得多。
另外，在做主键关联时，也会遇到NULL不等值的问题。两个表的关联字段如果允许NULL，a.user_id = b.user_id通常不会把两个NULL拼在一起，需要时用a.user_id IS NOT DISTINCT FROM b.user_id。当数据质量不确定时，它能救你一命。
4.2 分页查询的两种姿势与暗坑
PG的分页最直接是：
SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 40;翻到深页时（比如OFFSET 100000），数据库必须扫描并丢弃前10000行，性能会越来越差。赶时间的人会直接说“数据量不大没关系”，但生产环境排序字段不是唯一索引时，还会有更深的问题。
另一个暗坑是排序不稳定。如果ORDER BY created_at有大量相同值，两次查询同一页可能返回不同记录。因为PG如果没有更细的排序条件，行之间的顺序是不保证的。解决方法是加上唯一键做次级排序：
SELECT * FROM users ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 40;这样每行的最终顺序是确定的，深翻页结果也不容易出现错乱。
对于深分页，我更常用keyset pagination（又称seek method），也就是记住上一页最后一条记录的排序值，用它来做过滤：
-- 第一页：SELECT * FROM users ORDER BY (created_at, id) DESC LIMIT 20; -- 第二页：拿到上一页最后一条的 created_at_last 和 id_last SELECT * FROM users WHERE (created_at, id) < (created_at_last, id_last) ORDER BY created_at DESC, id DESC LIMIT 20;这里(created_at, id) < (a, b)是PG的行值比较语法，会先比created_at再比id，天然契合复合排序。这个方式不管翻多少页，每次扫描都只走索引、直接定位到游标位置，复杂度固定在O(log n)级别，比OFFSET稳定得多。
4.3 GROUP BY与HAVING的正确用法
聚合查询的报错大约有一半来自“select的列没有出现在group by里”。
-- 统计每天用户注册数，只保留注册量大于10的天 SELECT date_trunc('day', created_at) AS day, COUNT(*) FROM users GROUP BY date_trunc('day', created_at) HAVING COUNT(*) > 10 ORDER BY day DESC;PG的GROUP BY支持两种写法：一种是原生表达式，如上例；另一种支持用输出列的别名或序号引用，比如GROUP BY 1。我自己的习惯是尽量在GROUP BY里直接写表达式，因为如果用GROUP BY 1，SQL长了以后容易眼花，维护成本高。
在PG里，可以和聚合函数配合使用的还有FILTER子句，非常香：
SELECT date_trunc('day', created_at) AS day, COUNT(*) AS total_regs, COUNT(*) FILTER (WHERE age >= 18) AS adult_regs, AVG(age) FILTER (WHERE nickname <> '') AS avg_age_with_nickname FROM users GROUP BY 1;这相当于在同一个聚合内做条件计数/平均，不需要拆多个子查询，读起来也直观。类似功能其他数据库要写SUM(CASE WHEN ... THEN 1 ELSE 0 END)，PG的FILTER写起来清爽很多。
4.4 字符串、日期、JSON处理上的高频函数
这块是PG的强项，也是“常用SQL”里最容易产出价值的部分。
字符串方面：
-- 拼接 SELECT concat(first_name, ' ', last_name); -- 注意concat会忽略NULL，||不会 -- 正则替换 / 提取 SELECT regexp_replace('tel:138-0013-8000', '\D', '', 'g'); SELECT substring('abc123xyz' from '[0-9]+'); -- 提取连续数字 SELECT split_part('a,b,c,d', ',', 2); -- 按分隔符取第2段，得到 b -- 模糊匹配推荐ilike，不区分大小写 SELECT * FROM users WHERE nickname ILIKE '%admin%';日期方面，PG的日期函数特别多，我真正每天高频用的主要是：
-- 取今天的开始 SELECT date_trunc('day', now()); -- 把时间戳按任意时区显示 SELECT created_at AT TIME ZONE 'Asia/Shanghai' FROM users; -- 日期加减 SELECT now() + interval '1 day'; SELECT now() - interval '30 minutes'; -- 计算两个日期相差多少天 SELECT date_part('day', now() - '2024-01-01'::timestamptz); -- PG 14新增的date_bin，可以按任意区间“对齐”时间 SELECT date_bin('15 minutes', now(), '1970-01-01');date_bin是PG 14才有的，之前要实现“每15分钟一个桶”得靠date_trunc('minute', now()) - (date_part('minute', now())::int % 15) * interval '1 minute'，看着就头疼。有了date_bin之后一下子干净很多，如果你在PG 14以上版本做时序统计，这个函数绝对高频。
JSONB方面：
-- 在PG里不应该用字符串存JSON，要存就存JSONB SELECT profile->'nickname' AS nickname, -- 得到 jsonb 类型 profile->>'nickname' AS nickname_text, -- 得到 text profile->'items'->0 AS first_item FROM users; -- 判断是否存在某个key SELECT * FROM users WHERE profile ? 'vip'; -- 按jsonb字段过滤 SELECT * FROM users WHERE (profile->>'age')::int > 18; -- 修改jsonb某字段（PG 14之后推荐jsonb_set的lax模式） UPDATE users SET profile = jsonb_set(profile, '{vip_level}', '3', true) WHERE id = 123;jsonb_set的第四个参数true表示“如果路径不存在就创建”，默认是false。很多新手在这个参数上栽过：想更新一个不存在的字段，结果返回原值，没报错也没生效，排查半天。建议设为true，除非你有理由必须阻止字段自动创建。
5. 多表关联与子查询：JOIN的正确打开方式
多表JOIN是数据库查询中绕不开的重点，我在项目评审时看过很多“审阅完所有表再过滤”的低效写法。把JOIN的前置条件加上，能让PG优化器的活好干很多。
5.1 不同类型的JOIN怎么选
内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）、全连接（FULL JOIN）、交叉连接（CROSS JOIN）属于标准SQL内容，不详细展开。但有一点：如果你把前几年从MySQL迁移过来的ON条件从WHERE放到JOIN的ON里，在LEFT JOIN场景下语义是显著不同的。
-- 左边是所有用户，右边是订单聚合；ON里只写关联键 SELECT u.id, u.email, o.order_cnt FROM users u LEFT JOIN ( SELECT user_id, COUNT(*) AS order_cnt FROM orders GROUP BY user_id ) o ON o.user_id = u.id; -- 如果再把“只看已支付订单”放到WHERE里，LEFT JOIN就会被“打回”成INNER JOIN SELECT u.id, u.email, o.order_cnt FROM users u LEFT JOIN ( SELECT user_id, COUNT(*) AS order_cnt FROM orders WHERE status = 'paid' GROUP BY user_id ) o ON o.user_id = u.id;这两条SQL的差别很重要：第一条是“所有用户，以及他们的订单数（如果有的话）”；第二条是“所有用户，以及他们的已支付订单数（如果有的话）”。如果改成在JOIN的ON里加AND o.status = 'paid'，效果一样，但推荐在子查询内过滤，因为这样减少参与聚合的数据量，性能也更友好。
5.2 LATERAL：子查询里引用外层列
很多从其他数据库转过来的同学，一开始不知道LATERAL。它允许子查询引用外层查询的列，非常灵活。我举一个业务上很常见的需求：对每个用户取最近一单订单。
SELECT a.email, b.latest_order_time FROM users a LEFT JOIN LATERAL ( SELECT o.order_time, o.amount FROM orders o WHERE o.user_id = a.id ORDER BY o.order_time DESC LIMIT 1 ) b ON true;这里LATERAL子查询里的WHERE o.user_id = a.id引用到了外层a表的列，所以它叫“横向子查询”。相当于对这个用户循环执行一次“拿最近一单”的查询，但优化器通常能把它改成索引扫描，不会真的逐行循环。如果没有LATERAL，这种“每组取TOP 1”的需求很难表达，要么窗口函数，要么用关联子查询，LATERAL是事故中既清晰又高效的写法。
5.3 EXISTS与IN的选择
在PG里，EXISTS和IN大多数情况下优化器都能等价处理，但有一个容易出错的点是IN后面带子查询时如果结果集里有NULL，NOT IN会返回空结果。这是SQL三值逻辑的经典陷阱。
-- 如果 sub_ids 里有NULL，这条SQL什么都不会返回 SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned_list); -- 正确做法：用 NOT EXISTS SELECT * FROM users u WHERE NOT EXISTS ( SELECT 1 FROM banned_list b WHERE b.user_id = u.id );我在代码审查里见过不止一次因为NOT IN遇到NULL导致“数据诡异变少”的故障。所以只要子查询没法保证不含NULL，我基本默认用NOT EXISTS。
6. 窗口函数：PG最值得炫耀的高级查询能力
窗口函数是PG一个强大的加分项，但在普通项目里用得还不够多。它的核心思想是：不改变现有行数，对每一行在窗口范围内进行聚合或排序。相比GROUP BY会压缩行数，窗口函数在很多场景下能省掉大量自连接和子查询。
6.1 常用的排名与偏移函数
-- 按年龄降序，给每个用户一个组内排名 SELECT email, age, ROW_NUMBER() OVER (ORDER BY age DESC) AS age_rank, RANK() OVER (ORDER BY age DESC) AS age_rank_rank, DENSE_RANK() OVER (ORDER BY age DESC) AS age_rank_dense FROM users;ROW_NUMBER、RANK、DENSE_RANK的差别是：
ROW_NUMBER：不管数值是否相同，给每行唯一连续的序号；RANK：相同值的行并列，即如果3个并列第一，下一名是4，有跳号；DENSE_RANK：并列不跳号，3个并列第一后下一名是2。
选哪个取决于业务。比如比赛排名用RANK，但如果要的是相同成绩并列且不占名次数量，就用DENSE_RANK。而如果只是单纯列行号，就用ROW_NUMBER。
LAG和LEAD是“取上一行/下一行”的值，在做环比、同比时非常顺手：
SELECT day, total_amount, LAG(total_amount, 1) OVER (ORDER BY day) AS prev_day_amount, total_amount - LAG(total_amount, 1) OVER (ORDER BY day) AS diff FROM daily_sales;这个查出来就是“今天比昨天增长了多少”，每天一行，不需要用自连接，也不用复杂子查询。
6.2 分组内部每个分组取批次：ROW_NUMBER的典型用法
这是SQL面试和实际工作中都高频出现的“分组Top N”问题。
WITH ranked AS ( SELECT user_id, order_time, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM orders ) SELECT * FROM ranked WHERE rn <= 3;每个用户的最近3笔订单就出来了。PARTITION BY user_id让窗口函数在每个用户分组内独立排序，然后外层过滤rn <= 3。这个写法比LATERAL在某些场景更直观，且在有分区索引时性能一般也不差。
一个策略建议：当排序字段不是唯一值时，建议在窗口函数的ORDER BY追加一个唯一键（比如id DESC），保证rank结果稳定，不会因为并行计划导致排序抖动。
6.3 聚合窗口：SUM over + 移动平均
窗口函数还能直接在每行上做累计：
SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS cumulative_amount, -- 累计求和 AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d -- 7日移动平均 FROM daily_sales ORDER BY day;这个写法在做经营分析仪表盘时特别常见。需要注意的是，AVG(...) OVER (ORDER BY day)如果默认不写ROWS BETWEEN，PG默认窗口会不会包含整个分区到当前行——实际上是“从分区开始到当前行”的累计平均，而不是“最近N行”。要算“最近7天平均”，必须明确写ROWS BETWEEN 6 PRECEDING AND CURRENT ROW。刚接触窗口函数的时候，十有八九会在这里搞错。
7. 索引、执行计划与慢SQL排查的实用SQL
标题里提到了“PG常用SQL”，对我来说这里面占比最大的一块其实是性能排查相关。应用跑着跑着慢了，总不能靠猜，得用SQL去看。
7.1 索引管理与使用验证
-- 创建索引：普通B-tree CREATE INDEX idx_users_email ON users (email); -- 对查询频率高的表达式建索引 CREATE INDEX idx_users_lower_email ON users (lower(email)); -- 若有大量 `WHERE profile->>'age' > 18` 的查询 CREATE INDEX idx_users_age_jsonb ON users ((profile->>'age')) WHERE profile ? 'age' AND (profile->>'age') ~ '^[0-9]+$'; -- 查看某张表上的索引 SELECT * FROM pg_indexes WHERE tablename = 'users'; -- 强制某条SQL走索引：这是调试用的，别用在生产代码里 SET enable_seqscan = off;索引使用的第一原则是“看执行计划”，用EXPLAIN：
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'a@example.com';ANALYZE会真实执行SQL并统计耗时，BUFFERS会显示读了多少个共享缓冲块。如果看到执行计划还是Seq Scan on users，说明索引没被用上，常见原因有两个：一是选择性太差（比如WHERE age > 0），优化器觉得全表扫更快；二是表达式不匹配（你查WHERE lower(email) = ?，但索引建的是WHERE email = ?，无法使用）。
在PG 11之前，EXPLAIN ANALYZE会真实执行DML，所以分析UPDATE、DELETE时要注意，它会真的改数据。从PG 14开始支持EXPLAIN (ANALYZE, BUFFERS)也能用于DML但默认不执行改动，这个差异挺实用。
7.2 查看慢SQL和活跃会话
生产环境排查慢SQL，我第一个会查pg_stat_activity：
-- 当前所有活跃查询 SELECT pid, usename, application_name, client_addr, state, wait_event_type, wait_event, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC;这条SQL几乎每一版排障都能用。它能告诉你：哪个会话、来自哪个IP、什么应用、跑了多久的SQL。如果发现一个UPDATE跑了几个小时还挂着，可能就要结合锁等待进一步分析。
再看锁的情况：
-- 查看当前锁等待链 SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query, blocking.pid AS blocking_pid, blocking.query AS blocking_query FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid = ANY( pg_blocking_pids(blocked.pid) );这条能把“谁在等待谁”直观列出来。实际生产中很多“卡死”就是两个事务互相持锁，pg_blocking_pids函数专门干这个。
7.3 pg_stat_statements：找最高频SQL的利器
要开启pg_stat_statements，先确认shared_preload_libraries配置是否包含它（要改配置文件并重启实例），然后：
-- 查看耗时最长的SQL（累计视角） SELECT query, calls, total_exec_time / calls AS avg_exec_time, max_exec_time, rows / calls AS avg_rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;注意：PG 13之前这个视图里耗时字段是total_time、mean_time、max_time；PG 14以后改名成了带_exec_time后缀的字段。写脚本时如果发现字段不存在，多半是这个原因。这个视图对性能优化价值很大，除此之外还能配合排序看calls最多的SQL，但那通常反映的是业务调用次数，不一定是性能瓶颈，功耗上需要结合平均耗时一起看。
7.4 VACUUM与表膨胀：数据库也需要“整理房间”
PG的多版本并发控制机制导致被删除或更新的旧版本不会立即物理删掉，而是交给VACUUM清理。表膨胀到一定程度，扫描成本会暴涨。相关SQL：
-- 查看表的死元组比例和最近vacuum时间 SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, seq_scan, idx_scan FROM pg_stat_user_tables WHERE relname = 'users'; -- 手动vacuum（生产环境要选低峰期） VACUUM (ANALYZE, VERBOSE) users; -- 调整autovacuum阈值（会话/实例级） ALTER TABLE users SET (autovacuum_vacuum_scale_factor = 0.05);如果你发现某张表n_dead_tup一直很高，或者seq_scan次数远大于idx_scan，说明这条表可能长期没来得及vacuum，或者高频更新的业务表已经膨胀到了一个比较吓人的体量。我给过一个库存流水表做定期VACUUM，执行完后再跑同一套报表SQL，整体耗时下降了约20%，这个收益是实打实的。
8. 事务、锁与并发控制中不得不会的SQL
数据库并发场景下的SQL，不是只有SELECT和UPDATE。PG在事务隔离级别、行级锁、唯一冲突等方面提供了相对完整的手段，这些SQL务必掌握。
8.1 事务控制与会话级隔离级别
BEGIN; INSERT INTO users (email, nickname) VALUES ('tx@example.com', 'Tx'); SAVEPOINT sp1; UPDATE users SET age = 99 WHERE email = 'tx@example.com'; -- 如果发现这一步错了，可以回滚到保存点，不用整个事务撤销 ROLLBACK TO sp1; COMMIT;SAVEPOINT在长事务里很实用。比如循环检查一批数据，发现其中一条有问题，不必把整个事务回滚，只需回滚到之前的保存点，继续处理剩下的数据。
PG默认隔离级别是READ COMMITTED，可以通过以下方式查看和修改：
SHOW transaction_isolation; SET transaction_isolation_level = 'repeatable read'; BEGIN ISOLATION LEVEL REPEATABLE READ;“可重复读”在许多需要多次读同一快照的场景下很有用，比如生成报表时要保证两次次查询看到的数据一致；但要注意，在这个隔离级别下乐观锁冲突的表现形式是“serialization error”，应用层需要判断这错误码（用SQLSTATE 40001）并做重试。
8.2 FOR UPDATE / FOR SHARE：行级锁的经典用法
PG使用起来标准SQL里面向行级锁定的FOR UPDATE：
BEGIN; SELECT * FROM inventory WHERE product_id = 10 FOR UPDATE; -- 这里行已被锁定，其他事务对同一行的UPDATE或SELECT FOR UPDATE会等待 UPDATE inventory SET stock = stock - 1 WHERE product_id = 10; COMMIT;我经常用它来实现库存扣减、任务分配等场景。比“先查再改”安全得多，因为SELECT ... FOR UPDATE在读到行时就对该行加了排它锁，其他事务要改这一行就得等。如果还需要防止其他事务在锁期间插入相关记录，可以在WHERE里配合... OF或者使用SERIALIZABLE隔离级别，但大多数场景用FOR UPDATE就够了。
另外一个比较细的知识点：FOR UPDATE会把锁持有到事务结束，所以事务里执行完UPDATE后别急着断开连接，要COMMIT或ROLLBACK再释放。否则应用连接池里的连接很容易把锁长时间挂在数据库上，拖垮整个系统。
8.3 死锁与锁超时
PG默认死锁检测会主动终止其中一个事务，报deadlock detected。但锁定等待超时是需要你手动控制的：
SET lock_timeout = '5s';把lock_timeout设置在应用连接层，可以让某个SQL在等待锁超过5秒后自动报错，而不是无限挂起。不过把超时时间设得比较短也会影响一些合法长事务，需要按业务场景斟酌。
真实生产中，我把lock_timeout设为3s、把事务内statement_timeout设为30s，很大程度上应对了很多“假死”问题。应用不再因为某个SQL卡住，而是快速失败后进入重试流程，整体可用性提升不少。
9. 日常运维与元数据查询：比\d更强大的信息集
除了业务SQL，PG的日常运维里也充满了“常用SQL”。我一直强调，数据库的元数据查询是排障的基础。
9.1 数据量统计与膨胀估算
-- 粗略查看每张表的行数与占用空间 SELECT relname AS table_name, n_live_tup AS est_rows, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, pg_size_pretty(pg_table_size(relid)) AS table_size FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;pg_total_relation_size包含表数据 + 索引 + TOAST表，是判断表“真实占用”的最直观口径。如果想对比索引占了多少：
SELECT relname, pg_size_pretty(pg_indexes_size(relid)) AS index_size FROM pg_stat_user_tables ORDER BY pg_indexes_size(relid) DESC;当发现索引占用比表还大时，就值得检查一下是不是建了过多冗余索引（比如一个字段上既有单列索引，又有以它开头的复合索引），这类情况在业务演进时很常见。
9.2 进度视图：长事务与vacuum进行时
PG 9.6之后支持pg_stat_progress_vacuum，从PG 13开始支持pg_stat_progress_create_index。排查时可以直接看：
-- 当前vacuum进度 SELECT * FROM pg_stat_progress_vacuum; -- 当前建索引进度 SELECT * FROM pg_stat_progress_create_index;如果你在深夜执行了CREATE INDEX CONCURRENTLY，并用psql挂断了，可以通过pg_stat_progress_create_index来确认进度。CONCURRENTLY方式建索引不会锁表，但其真正的执行流程比普通建索引要多好几次扫描，中途如果连接断开，索引不会残留损坏，而是会被清理，但这也意味着你需要重新执行一次。这些细节在实际运维中很磨人，提前知道能省很多时间。
9.3 常用系统函数速查
PG有很多很实用但容易被忘掉的内置函数，顺手整理几个：
-- 生成连续数字序列，常用于填充报表、补空号 SELECT generate_series(1, 10); -- 生成连续日期 SELECT generate_series('2024-01-01'::date, '2024-01-10'::date, interval '1 day'); -- 多行值造出一个结果集 SELECT * FROM unnest(ARRAY['a', 'b', 'c']); -- 把查询结果拼成一个数组 SELECT array_agg(email) FROM users WHERE age < 20; -- 字符串聚合（比如把一批物料编号拼起来） SELECT string_agg(nickname, ', ') FROM users; -- 随机采样，效率远高于ORDER BY random() SELECT * FROM users TABLESAMPLE SYSTEM (1);TABLESAMPLE SYSTEM (1)表示按物理存放大致取1%的块，所以它不是严格意义上的随机行，而是随机块。如果只是抽样看看数据形态，这个性能远好于ORDER BY random()（后者要对全表排序）。
10. 内容实用小延伸：从常用SQL到动态SQL与执行策略
学会写SQL只是第一步，如何让这些SQL在业务里跑得更稳，也是从工程师到一个更可靠工程师的分水岭。
10.1 防止SQL注入的规范写法
虽然热词里有“sql注入”，但我还是要专门强调：千万别在业务代码里做字符串拼接SQL来传用户输入。比如：
# 这是反面教材 cur.execute(f"SELECT * FROM users WHERE email = '{email}'")PG的Python驱动psycopg2以及asyncpg里最安全的做法是使用参数占位：
# psycopg2 使用 %s 占位，参数分离传递 cur.execute("SELECT * FROM users WHERE email = %s", (email,))ORM里的参数化查询同理。参数化的核心是“SQL结构与用户输入分离”，不管输入是什么，都会被当作值，而不是SQL命令的一部分。另外，如果要在SQL里动态拼表名或字段名，这些属于标识符，参数化帮不上忙，需要你在白名单层面做校验，绝对不能拿用户输入直接拼。
10.2 动态SQL：EXECUTE在PL/pgSQL里的使用
在存储过程/函数里，经常需要动态拼接SQL。例如根据传入的字段名做分组统计：
CREATE OR REPLACE FUNCTION count_by_column(col_name TEXT) RETURNS TABLE(group_value TEXT, cnt BIGINT) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY EXECUTE format('SELECT %I AS group_value, COUNT(*) FROM users GROUP BY %I', col_name, col_name); END; $$;这里最关键的细节是%I，它来自format()函数，用于安全地格式化标识符。用%I而非%s可以避免动态表名/字段名带来的SQL注入风险。如果直接EXECUTE 'SELECT ' || col_name || ' FROM users'，传入的col_name是一段恶意文本时，后果会很难收拾。
10.3 批量操作时的执行策略
在应用层，很多同学习惯于在循环里逐条执行SQL，例如：
for row in rows: cur.execute("INSERT INTO t (id, name) VALUES (%s, %s)", (row['id'], row['name']))数据量几百条还好，几万条会明显变慢，因为每条SQL都有一次额外的网络往返和事务开销。更常用的方式是批量：
psycopg2.extras.execute_values( cur, "INSERT INTO t (id, name) VALUES %s", [(r['id'], r['name']) for r in rows], page_size=5000 )execute_values底层会一次把多条数据拼成一条INSERT ... VALUES (...),(...)，性能提升非常明显。在数据库侧，批量语句只有一次解析和一次执行，对buffer的利用也更充分。如果有几百万条数据要做同步，还能考虑COPY，那是大数据量导入的终极杀器：
COPY users (id, email, nickname) FROM '/path/to/data.csv' WITH (FORMAT csv, HEADER true);要注意的是，COPY需要在PG服务端（superuser）执行，或者用客户端工具psql的\copy。它的插入速度通常比逐条INSERT快一个数量级以上，如果你在从某个旧库导数据到PG，这个工具值得提前研究。
11. 我个人在实际项目里沉淀的PG使用体会
最后就着“PG常用SQL”这个话题，聊几点长期实践中比较深的感受。
第一，PG的执行计划非常透明，这也是我愿意花时间研究SQL的原因。很多数据库的优化器像黑盒，只能靠“加索引、加缓存”，而PG用EXPLAIN能看到每个算子的预估行数、实际行数、扫描方式、内存使用，基本把“为什么快/慢”写在明面上。日常排查慢SQL时，我建议固定一套方法：先确认锁等待，再看pg_stat_activity，再对目标SQL跑EXPLAIN (ANALYZE, BUFFERS)，从扫描方式开始逐级定位。这个顺序能帮你把大多数问题压缩在10分钟以内。
第二，PG的类型系统和约束设计是值得认真利用的，而不是绕开它。很多从MySQL转过来的团队习惯把JSON直接存TEXT，或者把所有时间都存成VARCHAR，结果绕开了PG最强大的特性。尝到甜头之后，你会发现JSONB的某些场景写入性能甚至比一张宽表好得多，TIMESTAMPTZ+date_trunc的组合能让时间统计逻辑干净不少。
第三，索引不是越多越好，但该建复合索引时一定要建对顺序。索引列顺序很重要：(user_id, created_at)和(created_at, user_id)是两种完全不同的索引，前者适合“查某个用户按时间排序”的场景，后者适合“全局按时间排序”的场景。如果你经常同时查这两个条件，建索引前想一想：先过滤哪个条件，索引列顺序就优先放哪个。
第四，高频更新的表要关注VACUUM和膨胀。平时看着一切正常，但某一天全表扫描突然变慢，或者pg_stat_user_tables里n_dead_tup长期是一个很大的值，这颗“雷”迟早会引爆。该开自动清理就开自动清理，实在不行，就在低峰期手动VACUUM (ANALYZE)，它对在线业务的影响比想象中小很多。
关于PG常用SQL，我还想再分享一个小技巧：用psql时\gset结合查询，可以方便地把一条SQL结果存成psql变量，继续往下查询；写复杂报表或做交互式排查时，这个命令能省掉不少重复手写常量。\gset和\watch是我日常两个最常用的“非标准SQL”工具，有兴趣的同学可以试试。
PG的SQL能力是一个深水区，这篇文章里覆盖到的内容，基本能覆盖日常开发和初阶运维的大部分场景。等哪一天EXPLAIN阅读能力练出来了、pg_stat_statements会用熟、JSONB函数不再需要翻文档，你就会发现PG其实真的很好用。
