1. 从“PG常用SQL”说起为什么值得专门整理一套先讲个我自己的经历。很多同学一开始接触的是MySQL语法熟悉了之后切到PGPostgreSQL数据库第一反应往往是“不就是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 表的创建、修改与约束维护建表是几乎所有项目的第一步。相比MySQLPG的约束体系更严格类型系统也更丰富。一个比较典型的业务表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 (aexample.com, A, 20), (bexample.com, B, 21), (cexample.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 (aexample.com, NewName) ON CONFLICT (email) DO UPDATE SET nickname EXCLUDED.nickname, updated_at now(); -- 如果冲突就什么都不做 INSERT INTO users (email, nickname) VALUES (dupexample.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 WITHCTE做多步更新给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而是NULLWHERE只接受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不等值的问题。两个表的关联字段如果允许NULLa.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 1SQL长了以后容易眼花维护成本高。在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后面带子查询时如果结果集里有NULLNOT 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 BETWEENPG默认窗口会不会包含整个分区到当前行——实际上是“从分区开始到当前行”的累计平均而不是“最近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;索引使用的第一原则是“看执行计划”用EXPLAINEXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email aexample.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_timePG 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 (txexample.com, Tx); SAVEPOINT sp1; UPDATE users SET age 99 WHERE email txexample.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 UPDATEBEGIN; 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更强大的信息集除了业务SQLPG的日常运维里也充满了“常用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(fSELECT * 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 动态SQLEXECUTE在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_size5000 )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的某些场景写入性能甚至比一张宽表好得多TIMESTAMPTZdate_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其实真的很好用。
PostgreSQL常用SQL速查：从基础语法到性能调优实战
1. 从“PG常用SQL”说起为什么值得专门整理一套先讲个我自己的经历。很多同学一开始接触的是MySQL语法熟悉了之后切到PGPostgreSQL数据库第一反应往往是“不就是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 表的创建、修改与约束维护建表是几乎所有项目的第一步。相比MySQLPG的约束体系更严格类型系统也更丰富。一个比较典型的业务表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 (aexample.com, A, 20), (bexample.com, B, 21), (cexample.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 (aexample.com, NewName) ON CONFLICT (email) DO UPDATE SET nickname EXCLUDED.nickname, updated_at now(); -- 如果冲突就什么都不做 INSERT INTO users (email, nickname) VALUES (dupexample.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 WITHCTE做多步更新给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而是NULLWHERE只接受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不等值的问题。两个表的关联字段如果允许NULLa.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 1SQL长了以后容易眼花维护成本高。在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后面带子查询时如果结果集里有NULLNOT 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 BETWEENPG默认窗口会不会包含整个分区到当前行——实际上是“从分区开始到当前行”的累计平均而不是“最近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;索引使用的第一原则是“看执行计划”用EXPLAINEXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email aexample.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_timePG 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 (txexample.com, Tx); SAVEPOINT sp1; UPDATE users SET age 99 WHERE email txexample.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 UPDATEBEGIN; 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更强大的信息集除了业务SQLPG的日常运维里也充满了“常用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(fSELECT * 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 动态SQLEXECUTE在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_size5000 )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的某些场景写入性能甚至比一张宽表好得多TIMESTAMPTZdate_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其实真的很好用。
