1
-- 使用 pageinspect 扩展查看元组头部信息
2
CREATE EXTENSION IF NOT EXISTS pageinspect;
3

4
-- 假设表 test 的数据在 0 号数据块
5
SELECT lp_off, t_xmin, t_xmax, t_ctid
6
FROM heap_page_items(get_raw_page('test', 0));
7

8
-- 示例输出：
9
--  lp_off | t_xmin | t_xmax | t_ctid
10
-- --------+--------+--------+--------
11
--     816 |    100 |      0 | (0,1)    -- 由事务100插入，未被修改
12
--     800 |    100 |    102 | (0,3)    -- 由事务100插入，被事务102更新，新版本在(0,3)
13
--     784 |    102 |      0 | (0,3)    -- 由事务102插入（更新的新版本）

1
// 简化的快照结构
2
typedef struct SnapshotData {
3
    TransactionId xmin;   // 当前所有活跃事务中最小的事务ID
4
    TransactionId xmax;   // 下一个将分配的事务ID
5
    TransactionId *xip;   // 活跃事务ID列表（xmin ~ xmax 之间）
6
    uint32      xcnt;     // 活跃事务数量
7
} SnapshotData;

1
-- 事务 T1：插入一行
2
INSERT INTO accounts (id, balance) VALUES (1, 1000);
3
-- 堆表中产生元组：(t_xmin=100, t_xmax=0, balance=1000)
4

5
-- 事务 T2：更新该行
6
UPDATE accounts SET balance = 2000 WHERE id = 1;
7
-- 堆表中现在有两个元组：
8
--   旧版本：(t_xmin=100, t_xmax=102, t_ctid=(0,2))  ← Dead Tuple
9
--   新版本：(t_xmin=102, t_xmax=0, balance=2000)     ← 当前可见版本
10

11
-- 事务 T3：删除该行
12
DELETE FROM accounts WHERE id = 1;
13
-- 新版本也变成 Dead Tuple：
14
--   (t_xmin=102, t_xmax=103, t_ctid=(0,2))  ← Dead Tuple

1
-- 查看 AutoVacuum 相关参数
2
SHOW autovacuum_vacuum_threshold;    -- 默认 50
3
SHOW autovacuum_vacuum_scale_factor; -- 默认 0.2 (20%)
4
SHOW autovacuum_analyze_threshold;   -- 默认 50
5
SHOW autovacuum_analyze_scale_factor;-- 默认 0.1 (10%)
6

7
-- VACUUM 触发条件：
8
-- dead_tuples > autovacuum_vacuum_threshold +
9
--               autovacuum_vacuum_scale_factor * reltuples
10
-- 即：死元组数 > 50 + 20% × 表行数
11

12
-- ANALYZE 触发条件：
13
-- changed_tuples > autovacuum_analyze_threshold +
14
--                  autovacuum_analyze_scale_factor * reltuples
15
-- 即：变更行数 > 50 + 10% × 表行数

1
-- 对频繁更新的大表设置更激进的 VACUUM 策略
2
ALTER TABLE hot_table SET (
3
    autovacuum_vacuum_scale_factor = 0.05,   -- 5% 死元组即触发
4
    autovacuum_analyze_scale_factor = 0.02    -- 2% 变更即更新统计信息
5
);

1
-- 日常维护：使用普通 VACUUM（不阻塞）
2
VACUUM accounts;
3

4
-- 紧急修复：使用 VACUUM FULL（阻塞 + 重写）
5
VACUUM FULL accounts;
6

7
-- 更好的替代方案：pg_repack（在线重建，不阻塞）
8
-- 需要安装扩展：CREATE EXTENSION pg_repack;
9
-- pg_repack -d mydb -t accounts;

1
-- 核心 VACUUM 调优参数
2
autovacuum_max_workers = 4          -- AutoVacuum 工作进程数（默认3）
3
autovacuum_naptime = 30s            -- 检查间隔（默认1min）
4
autovacuum_vacuum_cost_limit = 2000 -- 每轮 I/O 限额（默认200）
5
autovacuum_vacuum_cost_delay = 2ms  -- 达到限额后的休眠时间（默认20ms）
6

7
-- 手动 VACUUM 的 I/O 节流
8
vacuum_cost_limit = 200             -- 每轮 I/O 限额
9
vacuum_cost_delay = 0               -- 默认不休眠（手动执行优先级高）

1
-- 查看表的 HOT 更新统计
2
SELECT n_tup_ins, n_tup_upd, n_tup_hot_upd,
3
       round(n_tup_hot_upd::numeric / NULLIF(n_tup_upd, 0) * 100, 2) AS hot_ratio
4
FROM pg_stat_user_tables
5
WHERE relname = 'accounts';
6

7
-- 示例输出：
8
--  n_tup_ins | n_tup_upd | n_tup_hot_upd | hot_ratio
9
-- -----------+-----------+---------------+-----------
10
--      10000 |      8000 |          7200 |     90.00

1
-- 设置 fillfactor 为 80%，预留 20% 页空间给 HOT 更新
2
ALTER TABLE accounts SET (fillfactor = 80);
3
VACUUM FULL accounts;  -- 需要重建表使 fillfactor 生效

1
-- 创建 B-tree 索引（默认类型）
2
CREATE INDEX idx_users_name ON users (name);
3

4
-- 支持的查询模式
5
SELECT * FROM users WHERE name = 'Alice';           -- 等值
6
SELECT * FROM users WHERE name > 'Alice';            -- 范围
7
SELECT * FROM users WHERE name LIKE 'Ali%';          -- 前缀
8
SELECT * FROM users ORDER BY name;                   -- 排序
9
SELECT * FROM users WHERE name = 'Alice' AND age > 20; -- 多列

1
-- 地理空间索引（需要 PostGIS 扩展）
2
CREATE INDEX idx_locations_geo ON locations USING gist (geom);
3

4
-- 范围类型索引
5
CREATE INDEX idx_reservations_period ON reservations USING gist (during);
6

7
-- 查询示例：查找与某区域重叠的记录
8
SELECT * FROM reservations
9
WHERE during && '[2026-04-01, 2026-04-30]'::daterange;
10

11
-- 查找附近的位置
12
SELECT * FROM locations
13
WHERE ST_DWithin(geom, ST_MakePoint(116.4, 39.9)::geography, 5000);

1
-- 数组索引
2
CREATE INDEX idx_articles_tags ON articles USING gin (tags);
3

4
-- 查询：包含特定标签的文章
5
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'database'];
6

7
-- JSONB 索引
8
CREATE INDEX idx_events_data ON events USING gin (data);
9

10
-- 查询：JSONB 中包含特定键值对
11
SELECT * FROM events WHERE data @> '{"type": "login"}'::jsonb;
12

13
-- 全文搜索索引
14
CREATE INDEX idx_docs_content ON documents USING gin (to_tsvector('english', content));
15

16
-- 全文搜索查询
17
SELECT * FROM documents
18
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'postgresql & index');

1
-- 创建 BRIN 索引（指定块范围大小）
2
CREATE INDEX idx_logs_time ON logs USING brin (created_at)
3
    WITH (pages_per_range = 32);
4

5
-- 查询：时间范围查询
6
SELECT * FROM logs
7
WHERE created_at BETWEEN '2026-04-01' AND '2026-04-30';
8

9
-- BRIN 索引大小对比
10
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
11
FROM pg_indexes WHERE tablename = 'logs';
12

13
-- 示例输出：
14
--        indexname        |  size
15
-- ------------------------+--------
16
--  idx_logs_time_btree    | 214 MB   ← B-tree
17
--  idx_logs_time_brin     | 288 kB   ← BRIN（小 700 倍！）

1
-- 电话号码前缀索引
2
CREATE INDEX idx_phones_number ON phones USING spgist (phone_number);
3

4
-- 路由前缀匹配
5
SELECT * FROM routing_table
6
WHERE ip_range >>= '192.168.1.0/24'::inet;

1
-- 顺序扫描代价 ≈ 顺序读页数 × seq_page_cost + 行数 × cpu_tuple_cost
2
-- 假设表有 10000 页、500000 行
3
-- Seq Scan Cost = 10000 × 1.0 + 500000 × 0.01 = 15000
4

5
-- 索引扫描代价 ≈ 随机读页数 × random_page_cost + 索引条目数 × cpu_index_tuple_cost
6
--                + 堆表行数 × cpu_tuple_cost
7
-- 假设 B-tree 深度 3、返回 100 行
8
-- Index Scan Cost = 3 × 4.0 + 100 × 0.005 + 100 × 0.01 = 13.5

1
-- SSD 环境下调低随机读代价
2
ALTER SYSTEM SET random_page_cost = 1.1;
3
SELECT pg_reload_conf();

1
-- 强制使用特定 Join 方式（仅调试用）
2
SET enable_nestloop = off;
3
SET enable_hashjoin = off;
4
-- 此时优化器只能选 Merge Join
5

6
-- 恢复默认
7
RESET enable_nestloop;
8
RESET enable_hashjoin;

1
-- 查看表的统计信息
2
SELECT attname, n_distinct, null_frac, avg_width
3
FROM pg_stats
4
WHERE tablename = 'users';
5

6
-- 示例输出：
7
--  attname | n_distinct | null_frac | avg_width
8
-- ---------+------------+-----------+----------
9
--  id      |         -1 |         0 |        8
10
--  name    |      -0.25 |         0 |       16
11
--  city    |        100 |      0.05 |       12
12

13
-- n_distinct < 0 表示比例（-0.25 = 25% 不同值）
14
-- n_distinct > 0 表示绝对数量

1
-- 手动触发统计信息收集
2
ANALYZE users;
3

4
-- 增加统计信息精度（默认 100，最大 10000）
5
ALTER TABLE users ALTER COLUMN city SET STATISTICS 500;
6
ANALYZE users;

1
-- 基本执行计划
2
EXPLAIN SELECT * FROM users WHERE city = 'Beijing';
3

4
-- 带实际执行时间
5
EXPLAIN ANALYZE SELECT * FROM users WHERE city = 'Beijing';
6

7
-- 带 I/O 统计（最常用）
8
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE city = 'Beijing';
9

10
-- 示例输出：
11
--  QUERY PLAN
12
--  ---------------------------------------------------------------
13
--  Index Scan using idx_users_city on users
14
--    (cost=0.29..8.31 rows=1 width=72)
15
--    (actual time=0.015..0.016 rows=1 loops=1)
16
--    Index Cond: (city = 'Beijing'::text)
17
--    Buffers: shared hit=4
18
--  Planning Time: 0.089 ms
19
--  Execution Time: 0.032 ms

1
CREATE TABLE hot_test (
2
    id SERIAL PRIMARY KEY,
3
    status TEXT,
4
    updated_at TIMESTAMP DEFAULT NOW()
5
);
6

7
INSERT INTO hot_test (status) SELECT 'pending' FROM generate_series(1, 100);
8

9
-- 更新所有行
10
UPDATE hot_test SET status = 'shipped', updated_at = NOW();
11

12
-- 查看 HOT 更新统计
13
SELECT relname, n_dead_tup, n_live_tup, n_tup_upd, n_tup_hot_upd
14
FROM pg_stat_user_tables WHERE relname = 'hot_test';

1
 relname  | n_dead_tup | n_live_tup | n_tup_upd | n_tup_hot_upd
2
----------+------------+------------+-----------+---------------
3
 hot_test |          0 |        100 |       100 |            57

1
VACUUM hot_test;
2

3
SELECT relname, n_dead_tup, n_live_tup
4
FROM pg_stat_user_tables WHERE relname = 'hot_test';

1
 relname  | n_dead_tup | n_live_tup
2
----------+------------+------------
3
 hot_test |          0 |        100

1
SELECT pg_size_pretty(pg_relation_size('hot_test')) as size;

1
 size
2
-------
3
 16 kB