PostgreSQL系统函数清单：运维与性能排查实战

1. 为什么我整理了这份 PG 系统函数清单做数据库管理这行时间越久越会发现一个事实日常运维的很大一部分工作都是靠 SQL 语句里那一串串系统函数撑起来的。不管是新接手一个陌生实例的第一天还是排查线上故障的深夜又或者只是每周例行的巡检打开 psql 敲下的第一条命令里几乎都藏着 PostgreSQL 提供的各种内置函数。这份标题里带“0309”的清单是我某次完整梳理的 PG 常用系统函数汇总笔记。整理它的起因很实际团队里接手的项目越来越多很多 PG 实例来自不同的版本和部署方式大家的巡检脚本风格不一有的查表大小还在用笨办法有的处理字符串还在写一大段自定义逻辑白白浪费 PG 自带的能力。于是我花了几个晚上把平时真正用得上、能解决问题的函数挑出来分门别类做了一次统一整理形成了一套到哪里都能套用的“标准答案”。要说这份清单适合谁我觉得主要是这几类人刚接触 PG 的开发者想知道不用写复杂逻辑、用现成函数就能处理数据常年和数据打交道的运维同学需要一份可以直接复制去跑巡检脚本的函数手册还有那些写后端接口、天天和数据库接口纠缠的朋友很多问题其实一条 SELECT 就能说清楚。这篇文章不打算写成教科书式的函数大全而是把我在实际管理 PG 过程中最常用的函数按场景拆开附上可以直接跑的 SQL 例子顺手把我的使用心得和踩过的坑也放进去。大家拿到手就可以对照着自己的库跑一遍比自己翻官方文档效率高得多。2. 基础系统信息函数一查就知道实例底细2.1 版本、启动时间、角色这些老几样新接手一个 PG 实例第一件事永远是确认版本和环境。PostgreSQL 在这方面提供的函数非常直接基本看一眼就能确定兼容性范围也方便决定后面能做哪些操作。-- 当前内核版本 SELECT version(); -- 当前登录用户、库、schema、客户端来源 SELECT current_user, current_database, current_schema; SELECT inet_server_addr(), inet_server_port(); -- 实例启动时间 SELECT pg_postmaster_start_time(); -- 是否处于流复制备库状态 SELECT pg_is_in_recovery(); -- 当前会话的进程 ID后端 PID SELECT pg_backend_pid();这里我特别想提醒一个容易忽略的点current_user和user在 PG 里其实是一样的但在某些驱动里面会有细微差异。如果你用连接池并且配置了用户映射这时候session_user才是连接发起时那个用户current_user有可能被 SET ROLE 改掉了。这也是很多人在权限排查时明明用了current_user却找不到真实操作人的原因。pg_is_in_recovery()这个函数在巡检高可用集群时特别常用。主库返回false备库返回true。在做故障切换演练时我会先跑一边这个函数确认现在的读写节点状态再决定在哪边执行操作。配合pg_postmaster_start_time()还能大概判断实例是不是刚重启过对定位一些问题有辅助作用。2.2 参数配置也能直接查PG 的系统函数不仅仅能看状态也能查配置参数。current_setting()是管理员的得力帮手比如查某个会话级参数是否被改过SELECT current_setting(work_mem); SELECT current_setting(max_connections); SELECT current_setting(timezone);如果要看参数能不能在会话里动态修改配合pg_settings视图来查会更直观。current_setting()和set_config()这两个函数一个取值、一个改值经常一起出现在运维脚本里。比如要临时给某个分析会话调大work_mem可以直接SELECT set_config(work_mem, 256MB, false);第三个参数false表示这只对当前事务生效提交后即恢复。如果在写自动化脚本时需要临时调优这个函数的价值会立刻体现出来。使用 psql 的时候也可以用类似命令但在函数层面控制更精确。3. 对象和空间管理函数大小、路径、存在性一网打尽3.1 算清楚表和库的“真实体重”数据库膨胀是让每个 DBA 头疼的问题之一。PG 里计算对象大小的函数区分得很细刚开始用容易搞混我直接把常用的整理成对照函数作用说明pg_table_size(regclass)表本身占用包括 TOAST 数据但不含索引pg_indexes_size(regclass)该表关联的索引占用所有索引的总和pg_total_relation_size(regclass)表总占用表数据 TOAST 索引pg_database_size(name)整个数据库占用包含所有表和索引pg_size_pretty(bigint)格式化大小把字节转成 KB/MB/GB 等我最常用的是pg_total_relation_size因为一张表的真实占用必须把索引和 TOAST 都算进去。比如查一张大表SELECT pg_size_pretty(pg_total_relation_size(user_orders));如果只看表本身得到的数字会明显偏小尤其在表上有多个索引时索引占用甚至可能超过表数据本身。做清理或者迁移评估时这个数据就是决策依据。还有个实用技巧遍历当前库所有表一次性看谁占地方大SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname || . || tablename)) AS total_size FROM pg_tables WHERE schemaname NOT IN (pg_catalog, information_schema) ORDER BY pg_total_relation_size(schemaname || . || tablename) DESC LIMIT 20;需要注意pg_total_relation_size()的参数类型是regclass如果直接传字符串PG 会隐式转换但当表不在当前search_path里时必须带 schema 前缀否则会报错或者找到错误的表。3.2 对象存在性判断与文件路径写管理脚本时经常需要判断一个表或一个类型是否存在然后再做逻辑分支。很多人的第一反应是去查pg_class视图但函数to_regclass()更简洁而且不会因为对象不存在而抛错返回NULL即可SELECT to_regclass(public.orders); SELECT to_regclass(public.not_exists_table);用这个函数做条件判断特别干净。比如在初始化脚本里如果表不存在才执行建表语句就可以用它写成一个分值判断。相比于直接拼 SQLto_regclass还会正确处理大小写和特殊字符不用手动加引号。对象文件路径有时候也有用尤其是当你想确认某张表的数据到底落在哪个目录文件时SELECT pg_relation_filepath(user_orders);返回的结果就是当前实例数据目录下对应的物理文件路径。在迁移、备份或者处理表损坏时这个信息能帮助快速定位问题文件。我还经常用pg_column_size()来排查行大小和类型占用问题。比如检查某一行里某个字段到底占了多大空间配合pg_typeof()看字段实际类型SELECT pg_size_pretty(pg_column_size(description)) AS desc_size, pg_typeof(description) FROM user_orders LIMIT 5;如果你对数据类型的存储开销有疑问用这个函数实测是最靠谱的方式。它的输出是字节数再用pg_size_pretty格式化即可。4. 字符串处理函数从解析日志到拼接报表4.1 拼接与拆分管理场景的日常工作PG 的字符串拼接有一个让 MySQL 使用者非常不适应的特性它是用||运算符而不是CONCAT函数。虽然 PG 也支持concat()但两者行为有区别。||遇到NULL会直接返回NULL而concat()会跳过NULL继续拼接这个细节坑过不少人。日常写报表时我最常用的函数大概是string_agg()。它可以把分组内的多行数据拼成一个字符串特别适合把一堆关联 ID 汇总成一行看SELECT user_id, string_agg(order_no, , ORDER BY created_at) AS order_list, count(*) AS order_cnt FROM user_orders WHERE created_at now() - interval 7 days GROUP BY user_id;注意这里我加了ORDER BY而不是只传拼接字段。很多人写string_agg时不排序结果虽然是聚合了但每次执行返回的顺序不稳定在日志分析、报表核对时会让你百思不得其解。这个坑属于“不报错但结果飘忽”的典型。拆分字符串的经典场景是解析日志里的地址、接口路径、来源信息。split_part()比正则简单直观只需要指定拆分的分隔符和目标段数比如SELECT split_part(192.168.1.100:5432, :, 1); -- 192.168.1.100 SELECT split_part(/api/v1/users/detail, /, 3); -- users4.2 正则替换和动态 SQL 安全格式化处理用户输入和日志数据时脱敏是个躲不开的需求。PG 的正则函数在处理这类问题时很方便。最常见的需求是把日志里的身份证、手机号、卡号中间部分打码SELECT regexp_replace( 13812345678, ^(\d{3})\d{4}(\d{4})$, \1****\2 );regexp_replace的第 4 个参数可以传g做全局替换否则只替换第一个匹配。还有一个容易被忽略的参数是r表示^ $按照换行符而不是整个字符串的边界来匹配多行日志场景下很有用。另一类典型场景是动态 SQL 的安全格式化。PG 提供了format()函数很多人只知道它能当sprintf用其实它还专门为 SQL 提供了%I和%L两个格式符格式符作用适合场景%s普通字符串拼接参数值不做 SQL 转义%I标识符表名、列名把外部输入安全地当对象名自动加引号%LSQL 字面量把外部输入安全地当字符串值自动加单引号和转义举个例子如果你根据用户输入动态查表SELECT format(SELECT * FROM %I WHERE id %L, user_orders, abc);%I会自动把表名处理成合法的标识符%L会自动给值加引号并处理里面的特殊字符。用format()处理动态 SQL 比直接字符串拼接安全得多这也是我在管理脚本里非常依赖它的原因。字符串处理里还有一个隐藏得比较深的函数convert_to()和convert_from()分别在二进制和文本之间转换在处理编码问题时会用到不过这个一般归到高级技巧里日常管理用得少。5. 日期时间函数统计、保留、时区一个都不能少5.1 时间获取别再拿 now() 当万能选项PG 里有好多个“获取当前时间”的函数表面上看起来差不多其实行为差异直接影响业务逻辑。函数含义特点now()/transaction_timestamp()事务开始时间同一事务内多次调用返回同一值statement_timestamp()当前语句开始时间同一事务内不同语句值不同clock_timestamp()实时时钟时间同一 SQL 里每次调用都可能不同timeofday()文本格式的实时时间类似clock_timestamp()但返回字符串最容易踩的坑用一句话概括如果你在一个长事务里并且用now()拿时间去参与业务计算那这个时间从事务开头就冻结了。比如一个事务里先插入了一些数据中间做了一次耗时很长的外部调用最后再用now()记录更新时间实际记录的并不是操作完成的时间而是事务开始的时间。这在做数据迁移、批量更新时尤其隐蔽。如果你发现某个批次的数据时间戳全部一样不要怀疑是代码函数的问题先检查是不是在同一个事务里用了now()。5.2 时间处理和聚合统计按时间维度做统计几乎是所有管理报表的必选操作date_trunc()是我使用频率最高的时间函数没有之一。它的作用是把时间截断到指定精度比如-- 按小时聚合订单数 SELECT date_trunc(hour, created_at) AS hour_slot, count(*) AS order_cnt FROM user_orders WHERE created_at now() - interval 24 hours GROUP BY 1 ORDER BY 1;这个函数支持second、minute、hour、day、month、year、week等粒度在报表生成时可以说是万能工具。配合generate_series()还能生成连续的日期序列用来补齐没有数据的空档-- 生成最近 7 天的日期序列 SELECT generate_series( current_date - interval 6 days, current_date, interval 1 day )::date AS day;算时间差的函数也同样重要。age()函数最常用于计算两个时间点之间的间隔比如查用户注册到现在的时间SELECT age(now(), created_at) AS user_age FROM user_profile;如果要提取时间字段的一部分做比较用extract()或date_part()SELECT extract(day FROM now()); SELECT date_part(hour, now());两者语法略有区别但都能完成提取字段的工作。实际使用下来extract()更符合 SQL 标准也更常出现在业务代码中。还有一个处理时区的函数timezone()可以把带时区的时间转换到指定时区在跨区域管理多个实例时很有用。最后提一下格式化函数to_char()它也是管理脚本里的熟面孔但格式串里的掩码需要小心SELECT to_char(now(), YYYY-MM-DD HH24:MI:SS);HH24代表 24 小时制如果写成HH12配合PM也能输出但一旦混用会产生奇怪结果。毫秒部分要用MS不要想当然写成%s。这些都是在实际报表里出过差的。6. 条件逻辑与类型转换写管理逻辑的“调味剂”6.1 空值处理全家桶数据库里的NULL永远是问题源之一。PG 提供了好几个专门为空值设计的函数用对之后可以让逻辑变得非常清爽。COALESCE()返回参数列表中第一个非 NULL 的值是我最常用的默认值处理函数SELECT order_no, COALESCE(remark, 无备注) AS remark FROM user_orders;和它配套的是NULLIF()当两个参数相等时返回NULL。这个函数在防止除零错误时特别有用-- 防止总数为 0 时除法报错 SELECT total_amount, total_amount / NULLIF(order_cnt, 0) AS avg_amount FROM user_stat;还有一个容易被忽视的函数GREATEST()和LEAST()在多个值里取最大最小值。它们对 NULL 的处理也需要注意如果参数里有 NULL结果可能就是 NULL所以经常要配合COALESCE一起使用。条件逻辑方面除了标准的CASE WHENCASE表达式本身就是 PG 函数体系中非常重要的一部分很多人写复杂统计时习惯用代码去判断其实一条 SQL 用CASE就能分出多组SELECT CASE WHEN status paid THEN 已支付 WHEN status canceled THEN 已取消 ELSE 其他 END AS status_label, count(*) AS cnt FROM user_orders GROUP BY 1;6.2 类型转换的正确姿势PG 的类型转换方式有很多种CAST(col AS type)、col::type、type(col)还有各种函数隐式转换。管理脚本里最常用的还是::这种写法简洁直观SELECT order_no::bigint FROM user_orders; SELECT created_at::date FROM user_orders;字符串转数字时有个常见问题如果字符串里混入了字母或空格转换会直接报错整个查询都会挂掉。我曾经在一个数据清洗任务里因为源数据里有几行脏数据导致整批转换失败最终只能写一个函数来慢慢过滤。如果你需要在转换前判断是不是合法的数字可以配合正则先做检查SELECT CASE WHEN amount_text ~ ^\d\.?\d*$ THEN amount_text::numeric ELSE 0 END AS amount FROM raw_data;这里用到了 PG 的正则匹配操作符~配合CASE就能做到安全转换不需要额外写存储过程。类似的还有布尔值的转换、字符串到 JSON 的转换等原则都是一样的先验证再转换不验证就转换后面就要拿时间还债。JSON 处理函数在 PG 12 版本里已经非常强大。-、-、#、#这些操作符用来提取 JSON 字段配合jsonb_array_elements()展开数组可以让管理和抽查半结构化数据特别高效SELECT payload-order_no AS order_no, payload-items-0-price AS first_item_price FROM order_events WHERE payload IS NOT NULL;7. 性能排查和监控从被动救火到主动体检7.1 用函数和统计视图给数据库做“体检”日常管理中最有价值的操作之一就是通过 PG 自带的统计视图和函数给数据库做定期体检。这些视图背后都是函数在支撑查看它们能发现潜在的索引缺失、全表扫描、缓存命中率低等问题。看一张表被全表扫描了多少次、索引扫描了多少次可以发现需要加索引的对象SELECT schemaname, relname, seq_scan, idx_scan, seq_tup_read, CASE WHEN seq_scan idx_scan 0 THEN round(seq_scan::numeric / (seq_scan idx_scan), 3) ELSE 0 END AS seq_scan_ratio FROM pg_stat_user_tables WHERE schemaname public ORDER BY seq_tup_read DESC LIMIT 20;这个查询能快速定位哪些表在频繁全表扫描以及每次扫描读取了多少行。如果seq_tup_read很大说明全表扫描的代价非常高但也不是所有全表扫描都要优化有些小表直接扫反而更快。缓存命中率是另一个核心指标可以用如下 SQL 查看SELECT sum(heap_blks_hit) AS hit, sum(heap_blks_read) AS read, sum(heap_blks_hit)::numeric / NULLIF(sum(heap_blks_hit) sum(heap_blks_read), 0) AS cache_hit_ratio FROM pg_statio_user_tables;如果命中率低于 99%说明很多查询在反复读磁盘需要考虑增大shared_buffers或者优化查询模式。这也是每次巡检必须记录的一项指标。7.2 锁等待和阻塞会话排查管理 PG 时最让人抓狂的莫过于锁等待导致的写阻塞。当一张关键表被长事务锁住后面的所有请求全堵在那里。这个时候pg_blocking_pids()就是救命稻草-- 查看当前所有活动查询以及是谁阻塞了它们 SELECT pid, state, wait_event_type, wait_event, query_start, pg_blocking_pids(pid) AS blocking_pids, query FROM pg_stat_activity WHERE state active AND pid pg_backend_pid();pg_blocking_pids(pid)返回当前 PID 被哪些 PID 阻塞。如果blocking_pids不是空数组你就能顺着找到阻塞源头的会话再用pg_terminate_backend(pid)谨慎终止-- 谨慎使用终止一个阻塞会话 SELECT pg_terminate_backend(12345);这条命令要非常小心它相当于把对应会话直接杀掉了如果那个会话里有未提交事务会回滚。生产环境操作前一定要确认清楚 PID 对应的业务。7.3 历史慢查询排查pg_stat_activity只能看到现在正在执行的 SQL想看历史慢查询标准方案是开启pg_stat_statements这个扩展很多人第一次接触 pg_stat_statements 都是靠它做性能分析。CREATE EXTENSION IF NOT EXISTS pg_stat_statements;开启后可以通过它查询累计执行时间最长的 SQLSELECT queryid, calls, round(total_exec_time::numeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, round(max_exec_time::numeric, 2) AS max_ms, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;需要注意的是pg_stat_statements默认会截断 SQL 文本查询的完整内容可以使用pg_stat_statements的函数查看或者配置降低pg_stat_statements.max和pg_stat_statements.track参数。这里有个使用细节多次调用会累加数据如果想要对比两个时间窗口可以用pg_stat_statements_reset()重置计数器。其实慢查询排查的最终目的不是为了揪出哪条 SQL 慢而是通过分析 SQL 的扫描行数、返回行数、执行计划来反推索引设计和数据分布的问题。EXPLAIN ANALYZE配合函数查看执行计划是必要的EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM user_orders WHERE created_at 2025-01-01;8. 常见报错与函数使用避坑速查这里直接上一份我这些年积累的“血泪速查表”覆盖系统函数使用时最典型的问题问题现象真实原因推荐处理方式用 拼接字段有一行结果为 NULLstring_agg结果顺序每次都不同忘记了ORDER BY子句在聚合内加ORDER BY明确顺序长事务里的now()全是同一时间now()取的是事务开始时间需要“实时时间”时改用clock_timestamp()to_char输出的月份带前导空格格式串使用MM时默认右对齐填充空格使用FMMM去掉填充字符串转数字偶尔报错源数据混入非法字符先正则校验再转换或使用CASE包裹COALESCE报“类型不匹配”多个参数类型不一致手动统一类型比如COALESCE(a::text, b::text)pg_total_relation_size返回很小用的是pg_table_size而忽略了索引和 TOAST确认函数选择pg_total_relation_size才是总和to_regclass找不到表时返回 NULL表不在search_path中也没写 schema始终带 schema 前缀如public.tableEXPLAIN分析时耗时太长生产中误用全量ANALYZE先EXPLAIN确认计划后离线执行ANALYZE时区转换结果和预期差好几个小时timezone()的参数顺序和AT TIME ZONE混淆固定使用timestamp AT TIME ZONE zone写法还有一些容易忽视的细节值得单独强调。pg_column_size()对 NULL 字段返回的是 NULL而不是 0统计时要注意过滤。pg_size_pretty()如果传入负数也会正常返回负大小但现实中不可能有负数如果出现要检查是不是查询逻辑出了问题。current_setting()如果查询一个未知参数会直接报错想安全取值可以用pg_settings视图配合CASE来判断。还有关于split_part()的一个小坑如果源字符串里分隔符数量不足函数不会报错而是返回整串作为最后一个片段。如果你以为取了某个片段就一定是个子串结果可能拿到完整字符串导致后续逻辑判断异常。另外PG 的函数名和参数在不同大版本之间可能有细微变化比如有些函数在 PG 14 之后新增了参数或重载。如果你的集群跨大版本上线前先在低版本实例上跑一遍自己的巡检脚本可以避免“高版本跑得好好的低版本直接报函数不存在”的尴尬。这种兼容性问题在我管理的混合版本环境里非常常见哪怕只是差了三个大版本同一个操作也可能给出完全不同结果。还有一个很多人知道但容易忘的道理函数虽然方便但不是所有操作都必须在 SQL 里完成。比如需要复杂流程的清洗逻辑用存储过程写成的自定义函数维护成本并不低。我见过有些项目把整段业务逻辑揉进函数里结果排查问题时一眼望去全是函数调用栈反而失去了 PostgreSQL 直观、清晰的优势。合适场景才用函数这也是我整理这份清单时一直提醒自己的原则。9. 把这些函数变成自己的巡检模板说回整理这件事本身。清单总结出来之后我把它做成了一个可以直接落地的模板文件每次进行数据库巡检时就跑一遍。模板分成了几个固定板块实例基本信息、连接会话状态、对象空间占用、热点表扫描情况、慢查询性能统计。每个板块都是用上面提到的这些系统函数组装起来的输出格式统一既方便人工看也方便后续接进监控平台。比如实例基础信息可以直接拼成一个结果集SELECT version() AS version, current_database() AS db, current_user AS usr, pg_postmaster_start_time() AS started_at, pg_is_in_recovery() AS is_replica;这样每次执行完所有关键信息都在一行里复制到聊天工具里分享给同事也很直观。对象空间的巡检模板则更像一张报表SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(schemaname || . || relname)) AS total_size, n_live_tup, n_dead_tup, round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 4) AS dead_ratio FROM pg_stat_user_tables ORDER BY pg_total_relation_size(schemaname || . || relname) DESC LIMIT 20;死元组比例一旦过高就说明清理速度跟不上更新频率通常可以结合VACUUM参数调整来缓解。这个模板不需要额外插件在任何 PG 实例上都能直接跑实用性非常高。我个人在实际操作里的最大体会是把这套函数整理成统一模板之后巡检效率提升的不只是一点半点。以前每接手一个新实例都要现查函数名、现试命令后面凡是交接数据库工作直接把模板丢过去对方自己就能跑出结果省掉的沟通成本非常可观。这份“0309”清单只是我在某个时间节点做的沉淀并不代表函数全貌。PostgreSQL 每个大版本都在迭代更新函数库也在不停扩充后面完全可以在此基础上继续往性能监控、自动化巡检、备份恢复校验等方向扩展。把这些基础函数用得滚瓜烂熟之后再去碰更高级的插件和外部工具心里就有底了。