PostgreSQL / 系统课时

建立综合场景性能基线

建立综合场景性能基线

本课把稳定关系型字段与 JSONB 元数据放进同一套可复测工作负载中,固定数据规模、查询参数与采样口径,记录执行计划、缓冲区访问、统计信息和空间占用,为后续索引与 SQL 改造建立可比较的优化前证据。

学习目标

  • 建立覆盖执行时间、扫描规模、缓冲区访问和空间占用的性能基线
  • 区分关系型过滤条件与 JSONB 条件在混合查询中的职责
  • 用固定 SQL 与采样口径保存可重复比较的优化前记录

固定表结构与代表性工作负载

假设 event_store 用普通列保存租户、事件类型和时间等稳定字段,用 metadata 保存来源、国家和标签等动态属性。基线首先要固定数据分布和典型查询,否则后续很容易把偶发的缓存命中或参数差异误判成优化收益。

下面创建 100000 行确定性数据,并以一条混合查询作为主样本:先用 tenant_id、event_type 过滤稳定字段,再用 JSONB 包含运算符 @> 判断来源,最后按时间倒序取最近 50 条。

sql
DROP TABLE IF EXISTS event_store;

CREATE TABLE event_store (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL,
    event_type text NOT NULL,
    occurred_at timestamptz NOT NULL,
    metadata jsonb NOT NULL
);

INSERT INTO event_store (tenant_id, event_type, occurred_at, metadata)
SELECT
    (g % 100) + 1,
    CASE WHEN g % 5 = 0 THEN 'purchase' ELSE 'view' END,
    timestamptz '2026-01-01 00:00:00+00' + g * interval '1 minute',
    jsonb_build_object(
        'source', CASE WHEN g % 3 = 0 THEN 'mobile' ELSE 'web' END,
        'country', CASE WHEN g % 4 = 0 THEN 'US' ELSE 'CA' END,
        'tags', jsonb_build_array('campaign-' || (g % 20))
    )
FROM generate_series(1, 100000) AS g;

ANALYZE event_store;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, occurred_at, metadata
FROM event_store
WHERE tenant_id = 1
  AND event_type = 'purchase'
  AND metadata @> '{"source":"mobile"}'
ORDER BY occurred_at DESC
LIMIT 50;

预期结果

text
DROP TABLE
CREATE TABLE
INSERT 0 100000
ANALYZE
Limit  (actual time=<TIME>..<TIME> rows=50 loops=1)
  ->  Sort  (actual time=<TIME>..<TIME> rows=<VALUE> loops=1)
        Sort Key: occurred_at DESC
        ->  Seq Scan on event_store  (actual time=<TIME>..<TIME> rows=<VALUE> loops=1)
              Filter: ((tenant_id = 1) AND (event_type = 'purchase'::text) AND (metadata @> '{"source": "mobile"}'::jsonb))
              Rows Removed by Filter: <VALUE>
              Buffers: shared hit=<VALUE> read=<VALUE>
Planning Time: <TIME> ms
Execution Time: <TIME> ms

实际节点、耗时、缓冲区读写和行数会随 PostgreSQL 版本、硬件、缓存状态与参数变化,甚至可能出现并行计划。基线要固定记录的是扫描方式、实际行数、过滤规模、缓冲区访问和最终耗时,而不是追求与示例相同的毫秒值。

采集空间与索引基线

执行计划回答“查询怎么跑”,空间统计回答“当前结构付出了多少存储成本”。新增索引前先记录表大小、索引总大小以及现有索引的累计访问次数,后续才能判断查询收益是否伴随了明显的存储与维护成本。

sql
SELECT
    pg_size_pretty(pg_relation_size('event_store')) AS table_size,
    pg_size_pretty(pg_indexes_size('event_store')) AS indexes_size,
    pg_size_pretty(pg_total_relation_size('event_store')) AS total_size;

SELECT
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'event_store'
ORDER BY indexrelname;

预期结果

text
 table_size | indexes_size | total_size
------------+--------------+------------
 <VALUE>    | <VALUE>      | <VALUE>

 indexrelname     | idx_scan | idx_tup_read | idx_tup_fetch
------------------+----------+--------------+---------------
 event_store_pkey | <VALUE>  | <VALUE>      | <VALUE>

pg_stat_user_indexes 是累计统计,可能包含本轮实验之前的访问。正式治理应同时记录采样时间;独立测试环境若需要隔离工作负载,可以按测试流程重置统计后再复测,但不要为了单次实验在生产环境随意清空统计。

补充持续负载证据

单次 EXPLAIN 适合解释访问路径,却不能说明一条 SQL 在持续流量中的调用频率和累计成本。环境已启用 pg_stat_statements 时,应把同一采样窗口内的 calls、total_exec_time、mean_exec_time 和 rows 纳入基线;未启用时则使用现有慢查询日志,不要为了本次演练临时改生产参数。

先确认扩展是否可用:

sql
SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM pg_extension
        WHERE extname = 'pg_stat_statements'
    ) THEN 'enabled'
    ELSE 'not_enabled'
END AS pg_stat_statements_status;

预期结果

text
 pg_stat_statements_status
---------------------------
 <VALUE>

当结果为 enabled 时,再按当前 PostgreSQL 版本对应的 pg_stat_statements 字段采集高成本语句;不同大版本的统计字段可能存在差异,因此通用基线脚本应先确认版本与扩展定义,而不是直接假定字段集合完全一致。

固化可比较的基线记录

一次有效基线至少要把查询指纹、代表性参数、执行计划摘要、实际执行时间、实际返回行数、扫描规模、共享缓冲区命中与读取、表大小、索引大小和采样时间放在同一份记录中。关系型条件与 JSONB 条件也要原样保留,后续才能判断收益来自普通列索引、JSONB 索引还是 SQL 改写。

后续每次新增索引、调整表达式或改写 SQL,都应使用同一组查询、近似数据量和相同采样方法复测。只有优化前后的测量口径一致,性能提升才是可验证的基线差异,而不是一次偶然的“跑得更快”。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

建立综合场景性能基线

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通