PostgreSQL 17 性能实验室：查询、索引与运维调优

# 实验环境
# OS: Ubuntu 24.04 LTS
# CPU: AMD EPYC 7763 16 核
# RAM: 64GB
# Storage: NVMe SSD (Samsung PM9A3)
# PostgreSQL: 17.2

# 用 Docker 搭建 PG 17 实验环境
docker run -d \
  --name pg17-lab \
  -e POSTGRES_PASSWORD=labpass \
  -e POSTGRES_DB=perflab \
  -p 5432:5432 \
  -v pg17_data:/var/lib/postgresql/data \
  --shm-size=4g \
  postgres:17.2-bookworm

# 应用基础性能配置
docker exec -i pg17-lab psql -U postgres -d perflab <<'SQL'
ALTER SYSTEM SET shared_buffers = '16GB';
ALTER SYSTEM SET effective_cache_size = '48GB';
ALTER SYSTEM SET work_mem = '128MB';
ALTER SYSTEM SET maintenance_work_mem = '2GB';
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET max_worker_processes = 16;
ALTER SYSTEM SET wal_buffers = '64MB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
SQL

docker restart pg17-lab

-- 创建测试表
CREATE TABLE lab_orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer NOT NULL,
    product_id integer NOT NULL,
    status varchar(20) NOT NULL DEFAULT 'pending',
    total_amount numeric(12,2) NOT NULL,
    metadata jsonb DEFAULT '{}',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

-- 插入 1000 万条（约需 2 分钟）
INSERT INTO lab_orders (user_id, product_id, status, total_amount, metadata, created_at)
SELECT
    (random() * 100000)::int,
    (random() * 5000)::int,
    (ARRAY['pending','confirmed','shipped','delivered','cancelled'])[1 + (random()*4)::int],
    round((random() * 500 + 10)::numeric, 2),
    jsonb_build_object(
        'channel', (ARRAY['web','app','api'])[1 + (random()*2)::int],
        'region', (ARRAY['kr','us','jp','eu'])[1 + (random()*3)::int]
    ),
    now() - (random() * 365)::int * interval '1 day'
FROM generate_series(1, 10000000);

-- 收集统计信息
ANALYZE lab_orders;

-- 创建索引
CREATE INDEX idx_lab_orders_user ON lab_orders (user_id);

-- 清空缓存（测量 cold start 时）
-- DISCARD ALL;

-- 实验 A: 小规模 IN 子句（10 个值）
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, total_amount
FROM lab_orders
WHERE user_id IN (42, 128, 256, 512, 1024, 2048, 4096, 8192, 16384, 32768);

-- PG 17 结果示例:
-- Index Scan using idx_lab_orders_user on lab_orders
--   Index Cond: (user_id = ANY ('{42,128,...}'))
--   Buffers: shared hit=3847
--   Execution Time: 12.3 ms

-- 实验 B: 大规模 IN 子句（100 个值）
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, total_amount
FROM lab_orders
WHERE user_id IN (
    SELECT (random() * 100000)::int FROM generate_series(1, 100)
);

-- 实验 C: IN 子句 vs ANY(ARRAY[]) 性能对比
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, total_amount
FROM lab_orders
WHERE user_id = ANY(ARRAY[42, 128, 256, 512, 1024, 2048, 4096, 8192, 16384, 32768]);

-- 实验设定: 随 effective_io_concurrency 取值变化的 Seq Scan 性能
-- 测试 1: 默认值 (1)
SET effective_io_concurrency = 1;
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT count(*), avg(total_amount)
FROM lab_orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';

-- 测试 2: NVMe 推荐值 (200)
SET effective_io_concurrency = 200;
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT count(*), avg(total_amount)
FROM lab_orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';

-- 生成大量 dead tuple
UPDATE lab_orders SET updated_at = now()
WHERE id % 5 = 0;  -- 更新 200 万条 -> 产生 200 万个 dead tuple

-- 确认 dead tuple 数量
SELECT n_dead_tup, n_live_tup,
       round(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'lab_orders';
-- 预期结果: dead_pct = 16.67%

-- 执行 VACUUM（观察内存使用量）
SET maintenance_work_mem = '256MB';
VACUUM (VERBOSE) lab_orders;

-- PG 16 输出（对比基准）:
-- INFO: vacuuming "public.lab_orders"
-- INFO: index "idx_lab_orders_user" now contains 10000000 row versions
--   in 68425 pages
-- DETAIL: 2000000 dead row versions cannot be removed yet.
-- CPU: user: 8.42 s, system: 2.15 s, elapsed: 14.73 s
-- dead tuple tracking memory: 152MB

-- PG 17 输出:
-- INFO: vacuuming "public.lab_orders"
-- INFO: finished vacuuming "public.lab_orders":
--   index scans: 1
--   pages: 0 removed, 142857 remain
--   tuples: 2000000 removed, 10000000 remain
-- CPU: user: 5.89 s, system: 1.43 s, elapsed: 9.12 s
-- dead tuple tracking memory: 8MB (19x reduction)

# pgbench 初始化
pgbench -i -s 100 -U postgres perflab

# PG 17 WAL 性能测试: 32 客户端并发写入
pgbench -U postgres -d perflab \
  -c 32 -j 8 -T 60 \
  --protocol=prepared \
  --no-vacuum

# 结果示例 (PG 17):
# transaction type: <builtin: TPC-B (sort of)>
# scaling factor: 100
# number of clients: 32
# number of threads: 8
# duration: 60 s
# tps = 28,456 (without initial connection establishing)
# latency average = 1.124 ms

# 对比: PG 16 在相同配置下的结果
# tps = 15,823
# latency average = 2.023 ms

-- 测试数据: 包含 JSONB 数组的列
CREATE TABLE lab_events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload jsonb NOT NULL
);

INSERT INTO lab_events (payload)
SELECT jsonb_build_object(
    'event_type', 'purchase',
    'items', jsonb_agg(
        jsonb_build_object(
            'sku', 'SKU-' || (random() * 10000)::int,
            'qty', (random() * 5 + 1)::int,
            'price', round((random() * 100)::numeric, 2)
        )
    )
)
FROM generate_series(1, 100000),
     LATERAL generate_series(1, (random() * 4 + 1)::int) AS items(n)
GROUP BY (random() * 100000)::int;

-- 方法 1: 既有的 jsonb_to_recordset（兼容 PG 16）
EXPLAIN (ANALYZE)
SELECT e.id, item.*
FROM lab_events e,
     LATERAL jsonb_to_recordset(e.payload->'items')
         AS item(sku text, qty int, price numeric)
WHERE e.id BETWEEN 1 AND 1000;

-- 方法 2: JSON_TABLE（PG 17 新增）
EXPLAIN (ANALYZE)
SELECT e.id, jt.*
FROM lab_events e,
     JSON_TABLE(
         e.payload, '$.items[*]'
         COLUMNS (
             sku text PATH '$.sku',
             qty integer PATH '$.qty',
             price numeric PATH '$.price'
         )
     ) AS jt
WHERE e.id BETWEEN 1 AND 1000;

-- 测试用表
CREATE TABLE lab_import (
    id integer NOT NULL,
    name text NOT NULL,
    score numeric(5,2)
);

-- 故意生成包含错误的 CSV (Python)
-- import csv
-- with open('/tmp/lab_data.csv', 'w') as f:
--     w = csv.writer(f)
--     for i in range(100000):
--         if i % 1000 == 0:  # 0.1% 错误
--             w.writerow([i, f'name_{i}', 'NOT_A_NUMBER'])
--         else:
--             w.writerow([i, f'name_{i}', round(50 + 50 * (i % 100) / 100, 2)])

-- PG 17: 用 ON_ERROR = ignore 跳过错误行
COPY lab_import FROM '/tmp/lab_data.csv'
WITH (FORMAT csv, ON_ERROR stop);  -- 默认值: 在第一个错误处中断

COPY lab_import FROM '/tmp/lab_data.csv'
WITH (FORMAT csv, ON_ERROR ignore);  -- 跳过错误行并继续执行
-- NOTICE: 100 rows were skipped due to data type incompatibility

-- 确认已导入的行数
SELECT count(*) FROM lab_import;
-- 99900（跳过 100 条）

# ===== 内存 =====
shared_buffers = '16GB'              # RAM 的 25%（以 64GB 为准）
effective_cache_size = '48GB'        # RAM 的 75%
work_mem = '64MB'                    # 每会话排序/哈希用（需考虑并发会话数）
maintenance_work_mem = '2GB'         # VACUUM、CREATE INDEX 用
huge_pages = try                     # shared_buffers 较大时减少 TLB miss

# ===== I/O =====
effective_io_concurrency = 200       # NVMe SSD
maintenance_io_concurrency = 100     # VACUUM/索引构建用的 I/O 并发度
random_page_cost = 1.1               # 以 SSD 为准（HDD 用 4.0）
seq_page_cost = 1.0

# ===== WAL =====
wal_buffers = '64MB'
max_wal_size = '4GB'                 # 拉长检查点间隔
min_wal_size = '1GB'
checkpoint_completion_target = 0.9
wal_compression = zstd               # PG 15+，WAL 体积减少 30-40%

# ===== 并行处理 =====
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_worker_processes = 20
parallel_tuple_cost = 0.01
min_parallel_table_scan_size = '8MB'

# ===== autovacuum =====
autovacuum_max_workers = 4
autovacuum_naptime = '30s'           # 默认 1min -> 30s
autovacuum_vacuum_cost_delay = '2ms' # 默认 2ms

# ===== 统计/监控 =====
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
track_io_timing = on                 # 测量 I/O 耗时（反映到 EXPLAIN BUFFERS）
log_min_duration_statement = 500     # 记录耗时 500ms 以上的查询

-- 1. 缓存命中率（低于 99% 时考虑调大 shared_buffers）
SELECT
    sum(blks_hit) * 100.0 / sum(blks_hit + blks_read) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

-- 2. 按表统计的 Seq Scan vs Index Scan 比例
SELECT
    schemaname, relname,
    seq_scan, idx_scan,
    round(idx_scan * 100.0 / NULLIF(seq_scan + idx_scan, 0), 1) AS idx_pct,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 100
ORDER BY seq_scan DESC
LIMIT 15;

-- 3. 当前正在执行的 long-running 查询
SELECT
    pid,
    now() - query_start AS duration,
    state,
    wait_event_type,
    wait_event,
    left(query, 100) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT LIKE '%pg_stat_activity%'
  AND now() - query_start > interval '10 seconds'
ORDER BY duration DESC;

-- 4. 复制槽延迟（使用 streaming replication 时）
SELECT
    slot_name,
    slot_type,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots;

-- 5. 表体积 Top 10
SELECT
    schemaname || '.' || relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_table_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes_size,
    n_live_tup AS live_rows
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;