用 pgbench 做查询性能回归测试解读 PostgREST 的 test/pgbench 基准测试套件【免费下载链接】postgrestREST API for any Postgres database项目地址: https://gitcode.com/GitHub_Trending/po/postgrestPostgREST 每次对查询生成器Query Builder或请求处理管线进行优化时都面临同一个问题新写法是否真的更快、是否引入了回归test/pgbench目录正是为回答这一问题而存在的基准测试套件它把某个 GitHub issue 涉及的“旧版生成 SQL”与“新版生成 SQL”分别固化下来交给 PostgreSQL 自带的pgbench工具在真实数据库上跑压测用可复现的对比实验验证优化效果。读完本文你将掌握这套基准测试的运行方法、目录组织约定、fixtures.sql的作用以及 1567 / 1652 / 2676 / 2677 四个典型案例中 SQL 写法演进的来龙去脉。一、这套基准测试在做什么用可复现对比守护查询性能test/pgbench/README.md 的开篇只有一句话定位“pgbench tests”。但结合目录内容可以看得很清楚这不是单元测试或集成测试而是针对特定性能问题的回归基准。PostgREST 的核心工作是把 REST 请求翻译成一条复杂的 SQL 语句交给 PostgreSQL 执行相关生成逻辑集中在 src/PostgREST/Query/QueryBuilder.hs、src/PostgREST/Query/SqlFragment.hs 与 src/PostgREST/Query/Statements.hs每当这种翻译方式发生变化就必须证明“新 SQL 不比旧 SQL 慢”。套件的基本思路是把同一个业务场景下优化前的 SQL 与优化后的 SQL 各存一份用pgbench在同一台机器、同一个数据库状态下分别反复执行对比两边的 TPS每秒事务数、延迟等指标判断改动是否值得保留。由于pgbench直接执行的是最终落到数据库的 SQL 文本这套测试绕过了 HTTP 层、Haskell 应用层把变量收敛到“同一条 SQL 怎么写更快”这一核心问题上非常适合做查询计划与执行开销的微观对比。二、运行方法postgrest-with-pg-15 一行起压测README 给出了两个可直接复制的命令它们只差一个文件old.sql与new.sqlpostgrest-with-pg-15 -f test/pgbench/fixtures.sql pgbench -U postgres -n -T 10 -f test/pgbench/1567/old.sql postgrest-with-pg-15 -f test/pgbench/fixtures.sql pgbench -U postgres -n -T 10 -f test/pgbench/1567/new.sql逐段拆解这条命令的每个组成部分片段含义postgrest-with-pg-15项目开发环境提供的辅助命令Nix 开发环境的 shell 封装它会拉起一个带有 PostgreSQL 15 的临时环境把postgrest可执行文件与对应版本的客户端工具放进PATH供测试使用-f test/pgbench/fixtures.sql在启动的 PostgreSQL 实例上先执行fixtures.sql用于准备被测表结构、角色与数据pgbenchPostgreSQL 自带的基准测试工具以多客户端并发方式循环执行脚本并统计吞吐-U postgres以postgres超级用户身份连接数据库-n跳过 pgbench 自带的初始化步骤不执行pgbench默认的账户/分支表初始化因为表结构完全由 fixtures 决定-T 10每个场景跑 10 秒按时间而非固定事务数决定压测时长-f test/pgbench/1567/old.sql指定被测 SQL 脚本文件-f可重复出现便于把多个脚本放入一次压测注意这里的-f出现了两次但含义不同第一个-f是postgrest-with-pg-15的参数表示“先执行这个 SQL 文件初始化”第二个及后续-f是pgbench自身的-f表示“压测时反复执行这个脚本”。两次执行分别用old.sql与new.sql跑同一场景即可得到优化前后的对比结果。fixtures.sql位于 test/pgbench/fixtures.sql内容非常简短但很关键\ir ../spec/fixtures/load.sql ALTER TABLE test.complex_items DROP CONSTRAINT complex_items_pkey; ALTER TABLE test.complex_items ALTER COLUMN field-with_sep DROP NOT NULL;第一行\ir ../spec/fixtures/load.sql递归加载 PostgREST 集成测试用的全套 fixturestest/spec/fixtures/load.sql包括数据库、角色、schema、JWT、JSON Schema、权限与样例数据。后续两条ALTER TABLE则针对基准测试做了两处特殊调整移除complex_items表的主键约束、去掉field-with_sep列的 NOT NULL 约束——目的很明确让该表在压测场景下没有索引与约束干扰把执行计划差异完全暴露给被测 SQL 本身。被反复插入数据的test.complex_items表定义在 test/spec/fixtures/schema.sqlCREATE TABLE complex_items ( id bigint NOT NULL, name text, settings json, arr_data integer[], field-with_sep integer default 1 not null );这张表集中了 PostgREST 处理 JSON 负载时最“刁钻”的数据形态数组字段integer[]、带连字符的列名field-with_sep、可空的 JSON 字段——非常适合用来压测 JSON 反序列化路径的生成代码。三、目录结构约定目录名 GitHub issue 编号README 的 “Directory structure” 一节给出了本目录最重要的组织规则The directory name is the issue number on github.即每个子目录名对应一个 GitHub issue 编号目录内固定包含一对文件old.sql该 issue 修复前PostgREST 生成或人为构造的 SQLnew.sql该 issue 修复后PostgREST 生成的 SQL。把目录名与 issue 绑定意味着每份压测脚本都保留了完整的“问题上下文”——读者可以从 issue 讨论中回溯当时为什么要改 SQL把 old/new 并列存放则意味着每次回归都可以一键复现“修复前 vs 修复后”的性能对比。当前仓库中共有四个场景覆盖两类操作目录操作类型核心主题根据 SQL 内容推断test/pgbench/1567RPC 调用get_projects_belowJSON 参数解析与函数调用方式的改写test/pgbench/1652RPC 调用get_projects_below同一场景下另一轮查询形状的优化test/pgbench/2676批量插入complex_items去掉冗余的 JSON 类型判断分支test/pgbench/2677批量插入complex_items用 LATERAL 子查询重构 CTE 链被调用的get_projects_below是 fixtures 里的一个简单 SQL 函数定义在 test/spec/fixtures/schema.sqlCREATE FUNCTION get_projects_below(id int) RETURNS SETOF projects LANGUAGE sql AS $_$ SELECT * FROM test.projects WHERE id $1; $_$;四、RPC 调用场景1567 与 1652 的 SQL 演进4.1 场景 1567从“子查询 LIMIT 1”到 LATERAL 直连test/pgbench/1567/old.sql 是 issue 1567 的“旧写法”。它先用 CTE 链条把 JSON 载荷解析成参数表再从pgrst_args里取第一行作为函数入参WITH pgrst_source AS ( WITH pgrst_payload AS (SELECT {id: 4}::json AS json_data), pgrst_body AS ( SELECT CASE WHEN json_typeof(json_data) array THEN json_data ELSE json_build_array(json_data) END AS val FROM pgrst_payload), pgrst_args AS ( SELECT * FROM json_to_recordset((SELECT val FROM pgrst_body)) AS _(id integer) ) SELECT get_projects_below.* FROM test.get_projects_below(id : (SELECT id FROM pgrst_args LIMIT 1)) ) SELECT null::bigint AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, coalesce(json_agg(_postgrest_t), [])::character varying AS body, nullif(current_setting(response.headers, true), ) AS response_headers, nullif(current_setting(response.status, true), ) AS response_status FROM (SELECT projects.* FROM pgrst_source AS projects) _postgrest_t;注意两个技术细节旧版为兼容“载荷可能是单个对象也可能是数组”先用json_typeof判断、再用json_build_array把单个对象包成数组形成pgrst_body函数入参通过(SELECT id FROM pgrst_args LIMIT 1)这种相关子查询获得——PostgreSQL 需要为这个子查询单独规划执行节点且每次函数求值都可能重新扫描参数集。test/pgbench/1567/new.sql 则用LATERAL 子查询重构了整个参数传递链路WITH pgrst_source AS ( SELECT pgrst_call.* FROM ( SELECT {id: 4}::json as json_data ) pgrst_payload, LATERAL ( SELECT CASE WHEN json_typeof(pgrst_payload.json_data) array THEN pgrst_payload.json_data ELSE json_build_array(pgrst_payload.json_data) END AS val ) pgrst_uniform_json, LATERAL ( SELECT * FROM json_to_recordset(pgrst_uniform_json.val) AS _(id integer) LIMIT 1 ) pgrst_body, LATERAL test.get_projects_below(id : pgrst_body.id) pgrst_call ) SELECT null::bigint AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, coalesce(json_agg(_postgrest_t), [])::character varying AS body, nullif(current_setting(response.headers, true), ) AS response_headers, nullif(current_setting(response.status, true), ) AS response_status FROM (SELECT projects.* FROM pgrst_source AS projects) _postgrest_t;新写法的关键改进在于把“参数解析”和“函数调用”都变成 LATERAL 数据流中的一环pgrst_uniform_json负责统一对象/数组形态pgrst_body负责解析出参数行pgrst_call直接以pgrst_body.id为入参调用函数。相比旧版的相关子查询这种写法让规划器更容易把函数调用“内联”进查询执行流程减少了额外的子计划求值。4.2 场景 1652同一 RPC 场景的进一步收敛test/pgbench/1652/old.sql 与 1567 的 new 版几乎同构——它同样使用 LATERAL 链解析参数并调用函数但注意它有一个LIMIT 1单独作用在json_to_recordset上与 1567 的 new 版一致而外层get_projects_below的调用方式也是id : pgrst_body.idWITH pgrst_source AS ( SELECT pgrst_call.* FROM ( SELECT {id: 4}::json as json_data ) pgrst_payload, LATERAL ( SELECT CASE WHEN json_typeof(pgrst_payload.json_data) array THEN pgrst_payload.json_data ELSE json_build_array(pgrst_payload.json_data) END AS val ) pgrst_uniform_json, LATERAL ( SELECT * FROM json_to_recordset(pgrst_uniform_json.val) AS _(id integer) LIMIT 1 ) pgrst_body, LATERAL test.get_projects_below(id : pgrst_body.id) pgrst_call ) SELECT null::bigint AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, coalesce(json_agg(_postgrest_t), [])::character varying AS body, nullif(current_setting(response.headers, true), ) AS response_headers, nullif(current_setting(response.status, true), ) AS response_status FROM (SELECT projects.* FROM pgrst_source AS projects) _postgrest_t;从 SQL 文本上看1652 的 old 与 1567 的 new 基本相同而 test/pgbench/1652/new.sql 与其 old 版仅在函数调用方式上有细微差别id : (SELECT id FROM pgrst_args LIMIT 1)oldvsid : pgrst_body.idnew。这说明 1567 与 1652 记录了同一类 RPC 调用在两个连续优化迭代中的中间态与最终态——1567 先完成“CTE 参数表 → LATERAL 数据流”的改造1652 再进一步把“LIMIT 1 子查询取参”收敛为“直接引用上一级 LATERAL 的列”。这类逐步收敛正是性能回归测试最适合守护的场景每一步改动都能用 old/new 对比验证收益。五、批量插入场景2676 与 2677 的 JSON 处理简化5.1 场景 2676去掉冗余的 json_typeof 判断test/pgbench/2676/old.sql 是插入场景的旧写法整体包在INSERT ... RETURNING外包查询里。它的载荷解析先做了一次json_typeof判断再走json_to_recordsetWITH pgrst_source AS ( INSERT INTO test.complex_items(arr_data, field-with_sep, id, name) SELECT pgrst_body.arr_data, pgrst_body.field-with_sep, pgrst_body.id, pgrst_body.name FROM (SELECT [{id: 4, name: Vier}, {id: 5, name: Funf, arr_data: null}, {id: 6, name: Sechs, arr_data: [1, 2, 3], field-with_sep: 6}]::json AS json_data) pgrst_payload, LATERAL (SELECT CASE WHEN json_typeof(pgrst_payload.json_data) array THEN pgrst_payload.json_data ELSE json_build_array(pgrst_payload.json_data) END AS val) pgrst_uniform_json, LATERAL (SELECT arr_data, field-with_sep, id, name FROM json_to_recordset(pgrst_uniform_json.val) AS _(arr_data integer[], field-with_sep integer, id bigint, name text) ) pgrst_body RETURNING test.complex_items.* ) SELECT AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, array[]::text[] AS header, coalesce(json_agg(_postgrest_t), []) AS body, nullif(current_setting(response.headers, true), ) AS response_headers, nullif(current_setting(response.status, true), ) AS response_status, AS response_inserted FROM (SELECT complex_items.* FROM pgrst_source AS complex_items) _postgrest_t;test/pgbench/2676/new.sql 直接删掉了pgrst_uniform_json这层 LATERAL——不再用json_typeof判断对象/数组而是把载荷原样交给json_to_recordsetWITH pgrst_source AS ( INSERT INTO test.complex_items(arr_data, field-with_sep, id, name) SELECT pgrst_body.arr_data, pgrst_body.field-with_sep, pgrst_body.id, pgrst_body.name FROM (SELECT [{id: 4, name: Vier}, {id: 5, name: Funf, arr_data: null}, {id: 6, name: Sechs, arr_data: [1, 2, 3], field-with_sep: 6}]::json AS json_data) pgrst_payload, LATERAL (SELECT arr_data, field-with_sep, id, name FROM json_to_recordset(pgrst_payload.json_data) AS _(arr_data integer[], field-with_sep integer, id bigint, name text) ) pgrst_body RETURNING test.complex_items.* ) SELECT AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, array[]::text[] AS header, coalesce(json_agg(_postgrest_t), []) AS body, nullif(current_setting(response.headers, true), ) AS response_headers, nullif(current_setting(response.status, true), ) AS response_status, AS response_inserted FROM (SELECT complex_items.* FROM pgrst_source AS complex_items) _postgrest_t;这个改动的背景可以从 PostgreSQL 的行为推断json_to_recordset本身对 JSON 数组的每个元素展开成行PostgREST 生成的插入路径实际总以数组形式提交载荷因此外层的“对象则包成数组”判断在插入场景是冗余的。删除这层判断后JSON 解析链少了一个 LATERAL 节点和一次json_typeof/json_build_array求值执行计划更扁平。5.2 场景 2677用 LATERAL 数据流取代多层 CTEtest/pgbench/2677/old.sql 展示了插入场景的另一种旧形态——没有用 LATERAL而是把pgrst_payload、pgrst_body写成独立的 CTE再用json_to_recordset((SELECT val FROM pgrst_body))从 CTE 里取数WITH pgrst_payload AS (SELECT [{id: 4, name: Vier}, {id: 5, name: Funf, arr_data: null}, {id: 6, name: Sechs, arr_data: [1, 2, 3], field-with_sep: 6}]::json AS json_data), pgrst_body AS ( SELECT CASE WHEN json_typeof(json_data) array THEN json_data ELSE json_build_array(json_data) END AS val FROM pgrst_payload) INSERT INTO test.complex_items(arr_data, field-with_sep, id, name) SELECT arr_data, field-with_sep, id, name FROM json_to_recordset ((SELECT val FROM pgrst_body)) AS _ (arr_data integer[], field-with_sep integer, id bigint, name text) RETURNING test.complex_items.*而 test/pgbench/2677/new.sql 与 2676 的 new 版几乎完全一致——同样是去掉json_typeof判断、用 LATERAL 链完成“载荷 → 解析 → 插入”并包上 PostgREST 标准的响应封装外层查询。从代码对比可以看出2677 记录的是把CTE 风格的插入查询改写为LATERAL 风格的迭代而 2676 记录的是在此之上进一步精简 JSON 类型判断的迭代。两个 issue 编号并存说明这两类改动是在不同时间点独立提交、分别验证的。六、响应封装层的统一形态所有场景共享的收尾模板细心的读者会发现四个场景的所有 SQL除 2677 的 old 版因纯INSERT ... RETURNING而略有不同都共享同一段“响应封装”尾部模板SELECT AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, array[]::text[] AS header, -- 或 coalesce(json_agg(_postgrest_t), [])::character varying AS body coalesce(json_agg(_postgrest_t), []) AS body, nullif(current_setting(response.headers, true), ) AS response_headers, nullif(current_setting(response.status, true), ) AS response_status, AS response_inserted FROM (SELECT complex_items.* FROM pgrst_source AS complex_items) _postgrest_t;这段模板与 PostgREST 实际运行时生成的响应查询形态一一对应response.headers/response.status通过current_setting读取 GUC由 src/PostgREST/Response/GucHeader.hs 等模块负责写入page_total用count(_postgrest_t)计算当前页行数body用json_agg聚合结果集外层再包一层对pgrst_source的引用。这也从侧面印证这些压测 SQL 并不是随手写的玩具脚本而是与 PostgREST 线上生成的查询保持同构的“影子查询”因此压测结果对真实性能有直接参考价值。七、如何新增一个 pgbench 基准场景结合目录约定与已有用例新增一个场景的流程是找到问题 issue把目录名定为 GitHub issue 编号例如test/pgbench/issue编号/固化新旧 SQL在目录中分别放入old.sql改动前的查询与new.sql改动后的查询。注意让两个文件覆盖完全相同的业务场景与载荷且都遵循 PostgREST 响应封装模板确保对比变量只有“SQL 写法”本身确认 fixtures 覆盖检查 test/pgbench/fixtures.sql 及其引入的 test/spec/fixtures/load.sql 是否包含被测表与函数。若需要压测新表需注意当前 fixtures 对complex_items的处理模式去掉主键与 NOT NULL消除索引干扰跑对比postgrest-with-pg-15 -f test/pgbench/fixtures.sql pgbench -U postgres -n -T 10 -f test/pgbench/issue/old.sql postgrest-with-pg-15 -f test/pgbench/fixtures.sql pgbench -U postgres -n -T 10 -f test/pgbench/issue/new.sql解读结果pgbench的汇总输出会给出 TPS 与平均延迟对比 old/new 两轮结果确认新 SQL 没有明显回退。建议多次运行、取中位数或均值并保持-T时长一致。八、总结这套套件在 PostgREST 质量体系中的位置PostgREST 的常规测试体系test/spec 下的 Haskell 集成测试、test/io 下的端到端测试负责验证正确性——行为是否符合预期、响应是否与快照一致而 test/pgbench 这套基准套件补上了性能维度它不关心返回结果对不对只关心“同样的活新写法跑得是否更快、更稳”。把目录命名为 issue 编号、把新旧 SQL 并列存放的设计让每次查询生成器的改动都有一份“可随时重跑的性能证据”从而在引入更复杂、更健壮的 SQL 生成逻辑时能够确认没有以牺牲执行效率为代价。对想深入 PostgREST 查询生成的读者建议将这里的压测 SQL 与 src/PostgREST/Query/QueryBuilder.hs、src/PostgREST/Query/SqlFragment.hs 对照阅读你会发现 2676/2677 中“去掉json_typeof判断、改用 LATERAL”的演进正对应查询构建器里 JSON 载荷处理逻辑的逐步简化1567/1652 中 RPC 参数传递的收敛也对应函数调用代码生成路径的优化。SQL 文本与 Haskell 源码相互印证是理解 PostgREST 性能优化史最直接的一条路径。【免费下载链接】postgrestREST API for any Postgres database项目地址: https://gitcode.com/GitHub_Trending/po/postgrest创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考