1. 为什么说系统函数是PG管理的“第二语言”

干数据库运维这行，最常被问到的问题不是“这个SQL怎么写”，而是“这个库到底怎么了”。排查慢查询时要看执行计划，分析性能瓶颈时要看缓存命中率，定位锁冲突时要看锁等待状态，甚至日常巡检时都要靠统计信息判断表是不是该做VACUUM了。这些操作背后，全部离不开系统函数。说白了，系统函数就是你和PostgreSQL内核对话的接口，你问它要什么数据，它给你什么结果。用得熟练的人，一条SQL就能把问题定位个七七八八；用不熟的人，只能靠猜，靠重启，靠翻日志碰运气。

这份汇总主要面向两类人：一类是刚接手PG运维的开发或DBA，需要快速建立自己的排查工具箱；另一类是已经写过不少SQL但总感觉差点意思的进阶用户，想系统梳理一遍PG提供的能力边界。内容会覆盖类型转换、字符串、日期时间、聚合、窗口、JSON/JSONB、系统信息这几大类，每一类都会给到实际场景中我会怎么用、怎么记、踩过什么坑。全文的SQL示例都是我在测试环境实测过的，PG版本以12到16为主，但绝大多数函数跨版本通用，老版本也能对上号。

这里要先说一个认知问题：PG的系统函数不是给你背的，是给你查的。我从来没见哪个DBA能把几百个函数全记下来，这也不现实。真正高效的做法是掌握每一类函数的“代表选手”，知道它能干什么、参数怎么填、返回值长什么样，然后用到的时候去官方文档或者通过\df命令现查。下面这份汇总，就是把每一类里的代表选手挑出来，附上场景和坑点，你把它当成一张速查表用就好。

2. 日常运维最高频的几类函数，先占个坑

2.1 版本与配置类：接手陌生库的第一件事

不管是接手新项目还是排查老问题，接到一个PG实例，我做的第一件事永远是先看版本，再看配置。版本和配置决定了后面所有排查思路的走向，比如你拿一套为PG 11调优的参数跑到PG 16上，大概率是白忙活。

-- 查看版本号 SELECT version(); -- 查看当前会话的配置参数 SHOW work_mem; SHOW shared_buffers; -- 用函数方式读取配置，适合在SQL里做逻辑判断 SELECT current_setting('work_mem'); SELECT current_setting('max_connections');

current_setting()这个函数我要多说一句，它是可以嵌套到业务SQL里的。比如你写一个自动化巡检脚本，需要根据work_mem的大小决定要不要告警，直接SELECT current_setting('work_mem')拿到的字符串是'4MB'这种格式，如果你要做数值比较，可以用pg_size_bytes()把它转成字节数：

SELECT pg_size_bytes(current_setting('work_mem'));

这个组合拳在自动化运维脚本里非常实用，比你用正则表达式去抠字符串里的数字靠谱得多。

2.2 数据库与模式定位：搞清楚自己到底在哪个库

很多新手会犯一个错误：连上了PG之后，搞不清自己当前在哪个数据库、哪个schema下，然后噼里啪啦执行一串SQL，发现报错“relation does not exist”，排查半天才恍然大悟是schema搞错了。PG的search_path机制跟MySQL的库概念不太一样，这里有几个函数可以帮你快速定位：

-- 当前数据库名 SELECT current_database(); -- 当前用户 SELECT current_user; SELECT session_user; -- 当前schema SELECT current_schema(); -- search_path里所有schema列表 SHOW search_path;

实际工作中，我见过最典型的场景是：同一套数据库里挂了两个环境，dev和test的表结构几乎一样，你连接串里没指定schema，默认按public解析，结果读到的表和预期不一致。所以我在写多环境共存的自动化脚本时，习惯在脚本开头先跑一遍这几个函数做断言，确认当前环境符合预期再继续。虽然有点强迫症，但确实救过我好几次。

2.3 进程与连接视图：锁等待和活跃查询的起点

连接数打满、锁冲突、慢查询堆积，这些经典故障的诊断都要从pg_stat_activity这个视图开始。而这个视图的核心列，很多都依赖系统函数来做格式化处理。

-- 查看当前所有连接的基本信息 SELECT pid, usename, datname, state, wait_event_type, wait_event FROM pg_stat_activity; -- 杀掉指定pid的查询 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND pid <> pg_backend_pid();

pg_backend_pid()返回当前会话自己的PID，排查时需要把自己排除掉，这个细节很多人容易忽略，尤其是自动化清理脚本，一个不小心把自己的连接杀了，那画面太美不敢看。

pg_terminate_backend()对应的是pg_cancel_backend()的升级版。两者区别需要讲清楚：pg_cancel_backend()是发送取消信号，相当于按了Ctrl+C，只能中断正在运行的查询；pg_terminate_backend()是强制断开连接，会把整个会话干掉，适合处理那种卡在锁等待、无法通过取消查询来恢复的连接。判断用哪个，靠的就是看pg_stat_activity里的state列：active状态可以优先尝试cancel，idle in transaction状态的连接要么等它自己提交，要么直接terminate，不然事务持有的锁会一直卡着别人。

3. 类型转换与格式化函数：数据清洗的硬功夫

3.1 类型转换：PG的cast语法其实很讲究

我们要理清一个概念：PG里的类型转换操作符::并不是函数，而是语法糖。但理解它和函数的关系，对后续学习会有帮助。比如'123'::int和CAST('123' AS int)等价，但::写起来更简洁。PG里的类型转换体系比MySQL严格得多，尤其是字符串转数值、数值转字符串、文本转数组，转不过去就会直接报错，而不是悄悄给个0或者null。

-- 字符串转整数 SELECT '123'::int; SELECT CAST('123' AS int); -- 带格式的数字转字符串 SELECT to_char(12345.678, 'FM999,999.990'); -- 字符串转数组 SELECT string_to_array('a,b,c', ','); -- 数组转字符串 SELECT array_to_string(ARRAY['a', 'b', 'c'], ',');

to_char()是做报表和格式化输出时的大杀器，但它的格式模板比较反直觉。常见的有999,999.99表示数值占位，FM前缀表示去掉多余空格，0表示强制补零。我踩过一个坑：直接用to_char(0.85, '99.9')想得到"0.9"，结果返回的是" .9"，那个整数位0被吃掉了。后来才明白需要用to_char(0.85, 'FM90.9')或者用'0.9'模板才能显示出来。

3.2 判断与空值处理：数仓建设里的基本功

数据清洗里最常用的函数，除了类型转换，就是空值判断了。PG的NULL处理和MySQL最大的区别在于：NULL参与的运算结果基本都是NULL，而不是MySQL里那种“和false差不多”的模糊语义。

-- 空值替换 SELECT COALESCE(NULL, 'default'); -- 返回 'default' -- NULLIF：如果两个值相等返回NULL，常用于防止除零 SELECT NULLIF(0, 0); -- 返回 NULL -- 简单条件判断 SELECT CASE WHEN 1 > 0 THEN 'yes' ELSE 'no' END; -- 复杂多分支 SELECT CASE status WHEN '0' THEN '初始化' WHEN '1' THEN '进行中' ELSE '未知' END FROM orders; -- GREATEST和LEAST，注意NULL参与时的行为 SELECT GREATEST(1, 2, 3); -- 返回3 SELECT LEAST(1, NULL, 3); -- 返回NULL，这点很坑

NULLIF防止除零是经典用法：SELECT 1 / NULLIF(column, 0)。但要注意，如果column本身是NULL，NULLIF的结果也是NULL，整个除法结果还是NULL，不会报错也不会返回Infinity，这在某些统计场景下会导致数据缺失而不好排查。我的经验是遇到这种情况要配合COALESCE再包一层：SELECT COALESCE(1 / NULLIF(column, 0), 0)，虽然不优雅，但至少结果可控。

3.3 生成序列与随机数：造数测试的利器

这套函数在测试环境造数据时特别好用。generate_series()可以快速生成连续序列，配合随机函数可以模拟各种分布，省去了写存储过程循环造数的麻烦。

-- 生成1到10的连续整数 SELECT generate_series(1, 10); -- 生成间隔1小时的时间序列 SELECT generate_series('2024-01-01'::timestamp, '2024-01-02'::timestamp, '1 hour'); -- 随机数，范围0到1之间 SELECT random(); -- 随机整数，1到100之间 SELECT floor(random() * 100 + 1)::int; -- 随机抽样，从一张表里取10条 SELECT * FROM orders ORDER BY random() LIMIT 10;

ORDER BY random()在小表上没问题，大表上性能非常差，因为要对全表排序。生产环境做抽样调查时，我一般改用TABLESAMPLE SYSTEM(1)这种方式，或者用主键ID加随机偏移量的方式绕过全表扫描。别拿ORDER BY random()直接怼几十万行的大表，等它跑完你咖啡都凉了。

4. 日期时间与字符串处理：PG最强大的两个武器库

4.1 日期时间的获取与计算：时区是个大坑

日期时间函数是PG的强项，类型系统完整，支持时区感知，相比MySQL的DATE和DATETIME要严谨不少。但正因为严谨，坑也更多，尤其是timestamp with time zone和timestamp without time zone的区分，不懂的人第一次用很容易栽跟头。

-- 当前时间 SELECT now(); SELECT current_timestamp; SELECT clock_timestamp(); -- 当前日期 SELECT current_date; -- 时间间隔计算 SELECT now() + INTERVAL '1 day'; SELECT now() - INTERVAL '30 minutes'; -- 提取日期部分 SELECT EXTRACT(YEAR FROM now()); SELECT EXTRACT(DOW FROM now()); -- 周日为0，周六为6 SELECT date_part('month', now()); -- 日期截断 SELECT date_trunc('hour', now()); SELECT date_trunc('day', now()); SELECT date_trunc('month', now()); -- 日期格式化 SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS');

now()和clock_timestamp()的区别非常关键。now()是事务开始时间，同一个事务里多次调用返回的是同一个值；clock_timestamp()是实时时间，每次调用都不同。做性能测试要记录每个步骤耗时的时候，用clock_timestamp()才不会万年不变。这个细节在排查问题的时候很重要，比如你要看一个事务里两次操作间隔了多久，用now()永远是0。

时区方面有个经典坑：SELECT now()返回的结果带时区偏移，但如果你在current_setting('TimeZone')是'UTC'的会话里，插入一个timestamp without time zone类型的列，它不会做时区转换，直接按字面值存储。这会导致什么后果？你的应用在北京时间下午三点插入一条记录，数据库里存的是15:00而不是UTC的07:00。等到你换一个Asia/Shanghai时区的会话去查，会看到15:00没变，但如果你当初用的是timestamptz类型，结果就会显示23:00。我的建议是：新表一律用timestamptz，除非你有明确的“不用关心时区”的业务需求，否则你的数据早晚要出时区事故。

4.2 字符串函数全集重点：拼接、截取、匹配

字符串处理算是PG日常开发中用得最多的一类函数了。PG的字符串函数数量多、覆盖面广，从基本的拼接、截取、替换，到正则表达式匹配、拆分、音译，基本没有它干不了的活。

-- 拼接 SELECT concat('a', 'b', 'c'); -- 'abc' SELECT 'a' || 'b' || 'c'; -- 同样效果 -- 注意concat和||的区别：concat会忽略NULL，||遇到NULL返回NULL SELECT concat('a', NULL, 'c'); -- 'ac' SELECT 'a' || NULL || 'c'; -- NULL -- 截取 SELECT substring('hello world' FROM 1 FOR 5); -- 'hello' SELECT substr('hello world', 7, 5); -- 'world' -- 大小写 SELECT upper('abc'), lower('ABC'), initcap('hello world'); -- 去除空白 SELECT trim(BOTH 'x' FROM 'xxhelloxx'); -- 'hello' SELECT ltrim(' hello'), rtrim('hello '); -- 替换 SELECT replace('hello world', 'world', 'pg'); -- 正则匹配 SELECT 'abc123' ~ '[0-9]+'; -- true SELECT regexp_matches('abc123', '[0-9]+'); -- 正则替换 SELECT regexp_replace('2024-03-09', '-', '/', 'g'); -- 2024/03/09 -- 拆分 SELECT split_part('a,b,c', ',', 2); -- 'b' -- 字符串长度，注意中文字符 SELECT length('hello'), char_length('你好'), octet_length('你好');

concat和||在NULL处理上的差异是个高频坑。写报表SQL时拼接地址、拼接姓名，经常会遇到某个字段是NULL，用||拼出来整段都是NULL，白忙一场。记住一个原则：拼接字段哪些可能为空，就用concat()；需要严格区分“空字符串”和“NULL”的业务场景，再用||，因为concat会把NULL和空串都当空来处理，无法区分。

regexp_replace的第四个参数是个可选参数，'g'表示全局替换，不写就只替换第一个匹配。很多人知道有这个参数，但不清楚它还有'i'忽略大小写、'n'让.匹配换行符等选项。调试正则的时候，建议先用regexp_matches看看匹配结果，确认正则写对了再上regexp_replace，不然替换结果不符合预期，回头排查正则本身非常痛苦。

4.3 正则表达式的实战案例

正则表达式在PG里不是独立函数，而是作为操作符集成在字符串处理能力里。这段展开讲一个我在数据清洗中实际遇到过的场景：从混合文本里提取订单号。

比如某业务字段存的是“订单号:PO20240309001,金额:199.00元”，需要把订单号部分提取出来。用regexp_match或者substring配正则都行：

-- 方案一：substring配正则 SELECT substring('订单号:PO20240309001,金额:199.00元' FROM 'PO[0-9]+'); -- 方案二：regexp_match返回数组 SELECT regexp_match('订单号:PO20240309001,金额:199.00元', 'PO[0-9]+');

方案一的返回是文本类型，方案二返回是text[]数组。如果确认只会匹配到一个结果，直接取下标[1]就行。实际处理脏数据时，我一般建议先跑一遍SELECT DISTINCT regexp_match(字段, '正则') FROM 表看看有多少种匹配结果，再决定清洗策略，避免正则在极少数异常数据上失效导致漏处理。

5. 聚合函数与窗口函数：从明细到统计的桥梁

5.1 经典聚合函数的使用与陷阱

聚合函数是SQL的基础能力，PG支持的聚合包括count、sum、avg、max、min，还有array_agg、string_agg、json_agg这类扩展聚合。重点说几个容易踩坑的地方。

-- 基本聚合 SELECT count(*), count(1), count(column) FROM orders; -- 去重计数 SELECT count(DISTINCT customer_id) FROM orders; -- 求和、均值 SELECT sum(amount), avg(amount) FROM orders; -- 字符串聚合 SELECT string_agg(name, ',' ORDER BY id) FROM users; -- 数组聚合 SELECT array_agg(id) FROM orders WHERE status = 'pending'; -- 多行转JSON SELECT json_agg(row_to_json(t)) FROM (SELECT * FROM orders LIMIT 10) t;

count(*)和count(column)的区别是个经典考点：count(*)统计行数，包括所有列都为NULL的那行；count(column)只统计该列非NULL的行数。如果该列允许NULL，两个函数的结果会不同，数据分析时用错会导致统计口径偏差。

string_agg的排序语法是PG的独特之处：string_agg(name, ',' ORDER BY id)，它不依赖子查询或窗口函数，直接在聚合时内嵌排序。这个特性的性能要远好于先子查询排序再聚合，我在生成汇总报表时经常用它。注意低版本PG（9.x）里string_agg还不支持DISTINCT去重，高版本才支持，老库升级时要注意SQL兼容性。

array_agg在PG里有个和count一样的问题：如果聚合范围内没有任何数据，返回的是NULL而不是空数组。做前端展示的时候要处理这个差异，不然接口返回null和返回[]可能导致前端逻辑不同。

5.2 窗口函数基础概念：和普通聚合的本质区别

窗口函数和普通聚合函数的本质区别在于：普通聚合会把多行压成一行，窗口函数不会减少行数，每行都保留，同时多出一列计算结果。我用一个直观的例子来说明：

-- 普通聚合：每个部门返回一行 SELECT dept_id, count(*) FROM employees GROUP BY dept_id; -- 窗口函数：每行都保留，同时显示部门人数 SELECT emp_id, dept_id, count(*) OVER (PARTITION BY dept_id) AS dept_cnt FROM employees;

第二个SQL的结果里，每个员工所在部门的员工数都算出来了，但行数没有变化。理解了这个区别，你就能明白为什么窗口函数在“保留明细的同时附带汇总信息”这类需求里这么受欢迎。

5.3 排序编号类窗口函数：ROW_NUMBER、RANK、DENSE_RANK

分组取TopN是窗口函数最经典的场景，也是面试题里的常客。三个排序函数需要分清区别：

-- 按部门分组，按薪资排序，求每组前三名 SELECT emp_id, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dr FROM employees;

• ROW_NUMBER()：纯编号，1、2、3、4，不会重复，适合唯一排序场景。
• RANK()：并列排名会跳号，比如两个人并列第一，下一个就是第三名。
• DENSE_RANK()：并列排名不跳号，两个人并列第一，下一个还是第二。

实际选哪个取决于业务语义。如果只是取TopN，用ROW_NUMBER()然后过滤rn <= N最简单，因为它保证每行都有唯一编号；如果业务上需要显示“并列第一”，那就要用RANK()或DENSE_RANK()。

5.4 移动计算类窗口函数：LAG、LEAD、SUM OVER

做环比、同比、移动平均这类数据分析时，LAG和LEAD是绕不开的两个函数。它们可以在当前行访问上一步或下一步的数据：

-- 对比当前行和上一行的销售额 SELECT order_date, amount, LAG(amount, 1) OVER (ORDER BY order_date) AS prev_amount, amount - LAG(amount, 1) OVER (ORDER BY order_date) AS diff FROM daily_sales ORDER BY order_date; -- 累计求和 SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date) AS cum_sum FROM daily_sales; -- 移动平均：最近3天平均 SELECT order_date, amount, AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM daily_sales;

窗口函数里的ORDER BY在聚合窗口中的意义跟普通ORDER BY完全不同。普通聚合的ORDER BY是排序，窗口聚合里的ORDER BY定义了“窗口的推进方向”，配合ROWS BETWEEN可以精确控制计算范围。没有ORDER BY时，窗口是整个分区；有了ORDER BY后默认窗口是从分区第一行到当前行。这个逻辑不搞清楚，写出来的移动平均就是错的。

5.5 行转列与列转行

PG没有内置的PIVOT函数，不像某些数据库有现成的行列转换语法。但PG提供crosstab()扩展函数和FILTER语法，也能实现同样的效果。日常开发里，我比较推荐用FILTER语法，它更直观，也更好维护：

-- 按月份统计订单数，一列一个月 SELECT count(*) FILTER (WHERE EXTRACT(MONTH FROM order_date) = 1) AS jan_cnt, count(*) FILTER (WHERE EXTRACT(MONTH FROM order_date) = 2) AS feb_cnt, count(*) FILTER (WHERE EXTRACT(MONTH FROM order_date) = 3) AS mar_cnt FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024;

FILTER是PG特有的聚合过滤子句，它比CASE WHEN写起来更干净，执行计划也更容易优化。如果你之前没接触过，强烈建议把这个语法加入自己的工具箱。它的定位是：平时不怎么起眼，但真到行列转换、报表制作的时候，靠纯SQL就能搞定，不用再拉数据到本地用Python二次处理。

6. JSON与JSONB处理函数：半结构化数据的瑞士军刀

6.1 JSON和JSONB的区别

很多从MySQL转PG的人，会用varchar存JSON字符串，或者不知道JSON和JSONB的区别。这里先把概念讲清：PG提供两种JSON类型，json和jsonb。json是原样存储，保留输入格式，包括重复键、键的顺序、多余空格；jsonb是解析后的二进制存储，键会去重、会排序，查询效率更高，但不保留原始格式。日常开发里95%的场景应该选jsonb，只有需要严格保留原始输入文本、且几乎不查询内部字段的场景才考虑json。

-- 两类型的转换 SELECT '{"name":"张三","age":30}'::json; SELECT '{"name":"张三","age":30}'::jsonb; -- 判断字段是否存在 SELECT '{"name":"张三"}'::jsonb ? 'name'; -- true SELECT '{"name":"张三"}'::jsonb ? 'age'; -- false -- 提取字段值 SELECT '{"name":"张三","age":30}'::jsonb ->> 'name'; -- '张三' SELECT '{"name":"张三","age":30}'::jsonb -> 'age'; -- 30 (jsonb类型) -- 路径访问 SELECT '{"a":{"b":[1,2,3]}}'::jsonb #>> '{a,b,1}'; -- '2' -- 数组元素展开 SELECT jsonb_array_elements('[1,2,3]'::jsonb); -- 三行数据 -- 行转json聚合 SELECT jsonb_agg(row_to_jsonb(t)) FROM (SELECT 1 AS id, 'x' AS name) t;

->和->>的区别要分清：->返回JSON类型，->>返回文本类型。如果你后续要接数值计算，记得用->>取出来再::numeric转类型，否则会报类型不匹配的错误。还有一个高频坑是键不存在的情况：->>不存在的键返回NULL，不会报错，这既是好事也是坏事。如果你指望某个字段一定存在，结果它没存在，你的计算结果会静默变成NULL，排查起来费劲。我的习惯是先用?操作符判断键是否存在，再取值。

6.2 JSON对象生成与操作实战

把关系表转成JSON接口返回值，是PG后端开发最常用的技能。row_to_json和jsonb_build_object的组合基本可以覆盖所有场景：

-- 单行转JSON对象 SELECT row_to_json(t) FROM (SELECT id, name, age FROM users WHERE id = 1) t; -- 手动构造JSON对象 SELECT jsonb_build_object('id', id, 'name', name) FROM users WHERE id = 1; -- 嵌套JSON SELECT jsonb_build_object( 'user', jsonb_build_object('id', id, 'name', name), 'orders', (SELECT jsonb_agg(row_to_jsonb(o)) FROM orders o WHERE o.user_id = users.id) ) FROM users WHERE id = 1;

这里要提一个性能建议：能用SQL直接生成的JSON就别在应用层拼接。PG的JSON函数性能很稳定，而且SQL写法不会引入字符串拼接的注入风险。我在项目里见过后端代码里手工拼JSON字符串的悲剧，既难维护又有语法错误隐患，直接在SQL里用jsonb_build_object生成，一行代码解决。

6.3 JSON字段的更新与删除

JSONB的更新和删除操作语法看起来比较简单，但很多人不知道可以用路径方式精确修改嵌套字段：

-- 追加字段 SELECT '{"name":"张"}'::jsonb || '{"age":30}'::jsonb; -- 删除字段 SELECT '{"name":"张","age":30}'::jsonb - 'age'; -- 按路径删除嵌套字段 SELECT '{"a":{"b":1,"c":2}}'::jsonb #- '{a,b}'; -- 按路径更新字段 SELECT jsonb_set('{"a":{"b":1,"c":2}}'::jsonb, '{a,b}', '99'::jsonb, true);

jsonb_set的第四个参数create_missing控制路径不存在时是否自动创建。默认是true，会自动创建中间路径；如果你想严格更新已存在的字段，不存在的路径就不更新，需要显式传false。这个参数容易被忽略，在数据迁移脚本里很关键，搞错了会往数据里塞进一堆不希望的字段，影响下游消费。

7. 系统信息与运维管理函数：DBA的诊断工具箱

7.1 表与索引大小：磁盘空间定位三板斧

线上磁盘告警时，第一件事是找出哪些表占空间最大，哪些索引膨胀得离谱，哪些TOAST表在偷偷吃空间。这一套组合就能搞定大部分排查需求：

-- 单表大小（含索引） SELECT pg_size_pretty(pg_total_relation_size('orders')); -- 表数据大小（不含索引） SELECT pg_size_pretty(pg_relation_size('orders')); -- 索引大小 SELECT pg_size_pretty(pg_indexes_size('orders')); -- 数据库大小 SELECT pg_size_pretty(pg_database_size('mydb')); -- 所有表大小排行 SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size FROM pg_tables WHERE schemaname = 'public' ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC LIMIT 20;

pg_size_pretty()是我最常用的格式化函数，它能自动把字节数转成人类可读的"kB"、"MB"、"GB"，非常贴心。但注意，如果要做排序，不能对pg_size_pretty()的结果排序，因为它是文本，'10GB'和'9GB'按字符串排序会出错。上面那个示例里，排序用的是原始的pg_total_relation_size()函数结果，格式化只放在SELECT列表里，这个顺序千万别搞反。

7.2 统计信息与VACUUM状态：表健康度检查

PG依赖统计信息生成执行计划，统计信息过时会导致执行计划严重走偏。日常巡检里我常用以下函数组合判断一张表是否需要手动触发VACUUM或ANALYZE：

-- 查看表统计信息 SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname = 'orders'; -- 死元组比例过高时，考虑手动VACUUM SELECT relname, n_dead_tup, n_live_tup, round(n_dead_tup * 100.0 / NULLIF(n_live_tup, 0), 2) AS dead_pct FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY dead_pct DESC; -- 是否触发了autovacuum SELECT relname, last_autovacuum FROM pg_stat_user_tables ORDER BY last_autovacuum DESC NULLS LAST;

判断表是否健康的核心指标是n_dead_tup占比。占比超过20%就要关注，超过50%基本可以确定规划的自动清理没跟上，需要手动处理。VACUUM本身不会锁表，不会阻塞业务，在低峰期执行很安全，不必过度顾虑。

如果发现一张频繁更新的表常年没有last_autovacuum时间，优先怀疑是不是autovacuum参数被针对这张表单独禁用了。查询方式是这样：

SELECT reloptions FROM pg_class WHERE relname = 'orders';

返回里如果包含autovacuum_enabled = off，就是表级别把自动清理关掉了。这类问题比较隐蔽，经常是某次大规模导入数据时DBA临时关掉的，之后忘了恢复。每次巡检我发现这种表，都会特别标记出来，因为它意味着后续更新会持续积累死元组，直到表膨胀到不可收拾。

7.3 锁等待与阻塞关系：看穿谁在卡谁

锁问题在PG里是排障硬骨头，好在PG提供的视图和函数已经足够我们快速定位阻塞链：

-- 查看当前锁等待的会话，以及它在等哪个PID SELECT a.pid AS waiter_pid, b.pid AS blocker_pid, a.query AS waiter_query, b.query AS blocker_query, a.wait_event_type, a.wait_event FROM pg_stat_activity a JOIN pg_stat_activity b ON a.pid = b.pid WHERE a.wait_event_type = 'Lock'; -- 这条SQL不是所有人都能一眼看明白，排查锁时更推荐直接查pg_blocking_pids SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid) AS blocking_pids, query FROM pg_stat_activity WHERE wait_event_type = 'Lock';

pg_blocking_pids(pid)是排查锁问题时的心头好。传一个等待中的PID进去，返回的是阻塞它的所有PID数组。配合pg_stat_activity就能快速构建出阻塞链，判断是死锁、是长事务持锁、还是正常的行级锁竞争。

处理锁等待的一般顺序，我总结下来是四步：先看wait_event是什么类型的锁（Lock: relation、Lock: tuple等），再查谁阻塞了它（pg_blocking_pids），然后看阻塞者的事务状态（是否idle in transaction，这种是最坑的），最后根据业务影响决定是pg_cancel_backend还是pg_terminate_backend。如果阻塞者只是active状态在执行一个长SQL，优先pg_cancel_backend；如果阻塞者是idle in transaction，说明事务开了没提交，直接pg_terminate_backend反而更干净。

7.4 索引使用情况：哪些索引该删

索引不是建得越多越好，每个索引都有写放大和存储成本。定期检查索引使用率，删掉从没被用过的索引，是DBA的日常功课：

-- 各索引的扫描次数 SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan ASC; -- 从未被使用的索引 SELECT schemaname, relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY relname, indexrelname;

看到idx_scan = 0的索引，先别急着删，要确认它是不是最近才建的。统计信息是自上次重置（pg_stat_reset()）以来的累计值，如果刚建不久，扫描次数为0很正常。稳妥的做法是观察一到两周，并且查询一下pg_stat_all_indexes的总计数差异，确认确实没用过再DROP。我见过因为误删核心索引导致生产事故的案例，教训就是：删索引前要三思，删完长时间观察。

8. 常见问题与排查技巧实录

8.1 问题速查表

日常运维中，大家问得最多的几类问题，我整理成了一张速查表，遇到问题可以先对着查：

8.2 实战排查案例：锁等待定位过程

拿一个真实发生过的场景来串一遍完整排查过程。某天业务反馈，某个订单更新的接口响应时间从几十毫秒变成十几秒，数据库CPU不高，但连接数在涨。接到问题后：

第一步，先看当前活跃查询和锁等待：

SELECT pid, usename, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state <> 'idle';

结果发现大量会话的wait_event_type = Lock，说明确实存在锁阻塞。

第二步，找到阻塞源：

SELECT pid, pg_blocking_pids(pid) AS blocker_pids, query FROM pg_stat_activity WHERE wait_event_type = 'Lock';

查询结果指向同一个PID，比如是PID 12345。第三步，看这个PID是什么状态：

SELECT pid, state, query, xact_start, backend_start FROM pg_stat_activity WHERE pid = 12345;

发现state是idle in transaction，xact_start是一个小时前——典型的长事务未提交，持有行锁不放。这种情况直接用pg_terminate_backend(12345)干掉这个会话，业务立即恢复。

整个过程不需要看任何日志，一条锁视图SQL串下来就搞定了。这就是系统函数在实际运维里的最大价值：不是为用而用，而是能快速、准确地定位问题源头。

8.3 我对这套函数使用的一些体会

系统函数用久了，最大的感受是PG的调试路径非常透明。MySQL有问题时，很多信息只能靠日志和外部工具来推断，PG则几乎把所有运行状态都暴露在函数和视图里，只要你愿意查，总能查到。这种“可观测性”是PG在复杂业务场景下特别好用的一大原因。

真要给初学者一个建议，就是不要试图一次性把函数大全看完，也不要背函数名，而是“带着问题去查”。今天遇到锁等待，就专门研究pg_stat_activity和pg_blocking_pids；明天做报表，就研究string_agg、date_trunc和FILTER语法。这样学一次用一次，印象最深，也不会觉得枯燥。遇到记不清的函数，随时用\df命令搜索：

-- 在psql里搜索函数 \df *time* \df *json* \df *size*

\df支持通配符模糊匹配，这比去百度查要快得多，而且返回的结果就是当前版本的准确签名。我日常写SQL时遇到模糊的函数名，很少去翻文档，都是靠\df加模糊搜索先定位。

最后再分享一个小技巧：维护SQL脚本时，把常用的系统函数查询语句放在一个专门的SQL文件里，分门别类注释好，用时直接复制改改表名就行。这套“个人函数速查”比任何博客文章都适合自己，因为它是你亲手整理过的、完全贴合你业务场景的产物。项目我做过的，大概就是这样一个积累过程：第一次遇到锁问题，写一条查询，整理进速查文件；第一次做空间分析，再写一条，整理进去。半年之后，你就有一本完全属于自己的PG排障手册了。