PostgreSQL 关系型数据库

1. Postmaster 监听 TCP 5432 端口
2. 客户端发起连接请求
3. Postmaster Fork 一个 Backend Process
4. Backend Process 执行客户端 SQL，直接返回结果
5. 连接关闭 → Backend Process 退出

Tuple 对于当前事务可见的条件：

1. xmin 已提交 ∧ (xmax == 0 ∨ xmax 未提交 ∨ xmax == 当前事务)
   → 可见（正常数据）

2. xmin 未提交 ∧ xmin == 当前事务
   → 可见（自己插入的未提交数据）

3. xmin 已提交 ∧ xmax 已提交 ∧ xmax ≠ 当前事务
   → 不可见（已被其他事务删除）

4. xmin 为冻结事务（Frozen XID）
   → 始终可见（用于 VACUUM FREEZE 后的表）

Transaction A:
  BEGIN;
  SELECT * FROM t WHERE id = 1;  -- 获取 Snapshot S1
  -- ... 等待 ...
  SELECT * FROM t WHERE id = 1;  -- 获取 Snapshot S2（与 S1 可能不同）
  COMMIT;

Transaction B:
  UPDATE t SET val = 'b' WHERE id = 1;  -- 在 A 的两次查询之间
  COMMIT;

Transaction A:
  BEGIN;
  SELECT * FROM t WHERE id = 1;  -- 获取 Snapshot S1，整个事务使用此 Snapshot
  -- ... 等待 ...
  SELECT * FROM t WHERE id = 1;  -- 使用同一 Snapshot S1
  COMMIT;

触发公式：
  pg_stat_user_tables.n_dead_tup > autovacuum_vacuum_threshold
  + autovacuum_vacuum_scale_factor * pg_class.reltuples

简化：
  死元组 > 50 + 0.1 * 表总行数

Transaction IDs 是一个 32 位环状计数器：
0 → 1 → 2 → ... → 2³²-1 → 0（开始新的环）

问题：
  XID = 3 的事务看到的 XID = 2³²-3 实际上是未来的事务（因环状回卷）
  → 需要冻结（Freeze）将 XID 标记为 FrozenXID（=2），使其对任何事务都可见
  
防回卷策略：
  1. 普通 VACUUM：冻结 age > autovacuum_freeze_min_age 的行
  2. 防回卷 VACUUM：当表达到 autovacuum_freeze_max_age（默认 2 亿）
     时会强制触发 VACUUM（即使 autovacuum 关闭）
  3. 接近回卷时（1.5 亿 ~ 2 亿），PostgreSQL 发出 WARNING：
    "database X may be shut down to avoid wraparound data loss"

-- B-Tree：默认索引，精确匹配和范围查询
CREATE INDEX idx_btree_time ON events (created_at);
SELECT * FROM events WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';

-- BRIN：时序数据大表索引（比 B-Tree 小 100~1000 倍）
CREATE INDEX idx_brin_time ON events USING BRIN (created_at)
  WITH (pages_per_range = 32);
SELECT * FROM events WHERE created_at BETWEEN '2025-01-01' AND '2025-01-02';

-- GIN：全文搜索
CREATE INDEX idx_gin_content ON documents USING GIN (to_tsvector('english', content));

-- GiST：空间查询
CREATE INDEX idx_gist_location ON locations USING GiST (coord);
SELECT * FROM locations WHERE coord <@ '((0,0),(10,10))'::box;

-- Hash：等值查询（B-Tree 也可满足，Hash 更小）
CREATE INDEX idx_hash_email ON users USING HASH (email);

查询类型：
  ├── 精确匹配（=）+ 排序 → B-Tree
  ├── 范围查询（BETWEEN, >, <）→ B-Tree 或 BRIN（时序数据）
  ├── 全文搜索/JSONB/数组 → GIN
  ├── 空间/地理数据 → GiST 或 SP-GiST
  └── 等值唯一查询（不长扫描）→ Hash

                                    QUERY PLAN
---------------------------------------------------------------------------
 Gather  (cost=1000.00..15000.00 rows=5000 width=32)
   Workers Planned: 2
   ->  Parallel Seq Scan on orders  (cost=0.00..14000.00 rows=2083 width=32)
         Filter: (status = 'pending')

Execution Time: 12.345 ms          -- 总执行时间（ANALYZE 额外开销不计入）
Planning Time: 0.234 ms            -- 规划时间
Buffers: shared hit=102 read=5     -- 缓存命中 102 页，磁盘读取 5 页

-- 检查预估 vs 实际行数差异
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE created_at > '2025-01-01';
-- 关键观察：rows=10000（预估）vs actual rows=500000（实际）
-- → 统计信息过旧，执行 ANALYZE

-- 检查是否有 Parallel Seq Scan 替代索引扫描
-- 如果随机页读取成本设置过高，优化器倾向 Seq Scan
SET random_page_cost = 1.1;  -- SSD 环境下

WAL Before Data（先写日志，再写数据）：

1. 事务修改数据页（Shared Buffers 中）
2. 生成 REDO 日志记录 → WAL Buffer
3. 事务提交 → WalWriter 将 WAL Buffer 刷入 WAL Segment（触发 fsync）
4. Checkpointer 在 Checkpoint 后将脏数据页刷入磁盘

恢复原则：
  数据库崩溃后，从最近 Checkpoint 开始重放所有 WAL
  → 保证已提交事务不丢失，未提交事务回滚

LSN = WAL Segment 偏移 + 该 Segment 内字节偏移

示例：0/16B3748 → Segment 偏移 0x16B0000, 页内偏移 0x3748

Primary:
  $PGDATA/pg_wal/          -- WAL 段文件
  $PGDATA/pg_hba.conf      -- 允许复制连接
  $PGDATA/postgresql.conf  -- wal_level = replica 等参数

Standby:
  $PGDATA/standby.signal   -- Standby 模式标记文件（PG12+）
  $PGDATA/pg_wal/          -- 接收的 WAL 段
  $PGDATA/postgresql.conf  -- hot_standby = on

# postgresql.conf
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1024          # MB, 保留的 WAL 量（未使用复制槽时的兜底）
max_replication_slots = 10
hot_standby = on

# pg_hba.conf 添加复制用户
host replication replicator <standby_ip>/32 scram-sha-256

# 创建基础备份
pg_basebackup -h <primary_ip> -D $PGDATA -U replicator -P --wal-method=stream

# standby.signal 文件
touch $PGDATA/standby.signal

# postgresql.conf
primary_conninfo = 'host=<primary_ip> port=5432 user=replicator password=xxx'
primary_slot_name = 'standby_1'
hot_standby = on

# 至少 ANY 1 个 Standby 同步确认
synchronous_standby_names = 'ANY 1 (*)'

# 精确指定同步 Standby（前 n 个）
synchronous_standby_names = 'FIRST 2 (standby1, standby2, standby3)'

# 不配置 = 异步复制（Standby 仅异步追赶）

事务提交延迟 = MAX(本地 WAL fsync, 远端 Standby WAL fsync RTT)

场景：
  本地 fsync:           ~2ms
  跨 AZ (同区域):       RTT ~1-3ms → 总延迟 ~3-5ms
  跨区域 (同大陆):      RTT ~10-30ms → 总延迟 ~12-32ms
  跨洲:                 RTT ~150-300ms → 总延迟 ~152-302ms (不推荐)

# 创建全量基础备份（实时流复制，不停服）
pg_basebackup -h <primary> -D /backup/base_20250801 \
  -U replicator -P --wal-method=stream \
  --format=tar --gzip --compress-level=9

# 增量备份（自备为基础之上，需 pgBACKrest 或 WAL-G）

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
archive_timeout = 60          # 强制归档间隔（秒）

# postgresql.conf (恢复实例)
restore_command = 'cp /archive/%f %p'

# recovery.conf (PG11-) 或 recovery.signal (PG12+)
recovery_target_time = '2025-08-01 14:30:00+08'
recovery_target_xid = '12345678'     # 按事务 ID 恢复
recovery_target_lsn = '0/16B3748'    # 按 LSN 恢复
recovery_target_inclusive = true     # 是否包含目标点

# pgBackRest 验证
pgbackrest --stanza=production check

# 从备份恢复到临时实例
pg_ctl -D /tmp/pg_restore start
# 验证数据完整性
pg_checksums -D /tmp/pg_restore

-- 创建表空间
CREATE TABLESPACE fast_space LOCATION '/ssd/pg_tablespace';
CREATE TABLESPACE archive_space LOCATION '/hdd/archive_tablespace';

-- 在指定表空间创建对象
CREATE TABLE orders (...) TABLESPACE fast_space;
CREATE INDEX idx_orders_time ON orders (created_at) TABLESPACE fast_space;

-- 移动表空间
ALTER TABLE orders SET TABLESPACE archive_space;

-- 查看表空间大小
SELECT
  spcname,
  pg_size_pretty(pg_tablespace_size(spcname))
FROM pg_tablespace;

-- 范围分区（时序数据典型方案）
CREATE TABLE orders (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    amount NUMERIC(10,2),
    customer_id BIGINT
) PARTITION BY RANGE (created_at);

-- 创建分区子表（每月一个分区）
CREATE TABLE orders_202501 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')
    TABLESPACE fast_space;

CREATE TABLE orders_202502 PARTITION OF orders
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01')
    TABLESPACE fast_space;

-- 列表分区
CREATE TABLE customers (
    id BIGSERIAL,
    region TEXT,
    name TEXT
) PARTITION BY LIST (region);

CREATE TABLE customers_apac PARTITION OF customers
    FOR VALUES IN ('CN', 'JP', 'KR');
CREATE TABLE customers_emea PARTITION OF customers
    FOR VALUES IN ('DE', 'FR', 'UK');

-- Hash 分区（均匀分布）
CREATE TABLE sessions PARTITION BY HASH (user_id);
CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 添加新分区
CREATE TABLE orders_202503 PARTITION OF orders
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- 分离分区（转换为独立表）
ALTER TABLE orders DETACH PARTITION orders_202501;

-- 删除分区
DROP TABLE orders_202501;

-- 手动附加分区
ALTER TABLE orders ATTACH PARTITION orders_202503
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- 查询仅扫描 orders_202501 和 orders_202502
EXPLAIN SELECT * FROM orders
WHERE created_at BETWEEN '2025-01-15' AND '2025-02-15';

-- 查看当前所有等待锁的关系
SELECT
  blocked_locks.pid AS blocked_pid,
  blocked_activity.query AS blocked_query,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.query AS blocking_query,
  blocked_activity.wait_event_type || ': ' || blocked_activity.wait_event AS wait_event
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_blocks.relation
  AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
  AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
  AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
  AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
  AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
  AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
  AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid
WHERE NOT blocked_locks.granted;

-- 1. 安装扩展
CREATE EXTENSION postgres_fdw;

-- 2. 创建外部服务器定义
CREATE SERVER pg_remote
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '10.0.1.100', port '5432', dbname 'appdb');

-- 3. 创建用户映射
CREATE USER MAPPING FOR local_user
  SERVER pg_remote
  OPTIONS (user 'remote_user', password 'secret');

-- 4. 创建外部表
CREATE FOREIGN TABLE remote_orders (
    id BIGSERIAL,
    customer_id BIGINT,
    amount NUMERIC(10,2),
    created_at TIMESTAMPTZ
)
SERVER pg_remote
OPTIONS (schema_name 'public', table_name 'orders');

-- 5. 查询（如同本地表）
SELECT * FROM remote_orders WHERE created_at > '2025-06-01';

-- 默认 GIN 索引（支持 @>, ?, ?|, ?&）
CREATE INDEX idx_users_data ON users USING GIN (data);

-- 带路径的 GIN 索引（JSONPATH 查询优化，PG13+）
CREATE INDEX idx_users_data_path ON users USING GIN (data jsonb_path_ops);

-- B-Tree 索引用于 `->>` 操作（仅特定列）
CREATE INDEX idx_users_email ON users ((data->>'email'));

-- 创建索引（to_tsvector + GIN）
CREATE INDEX idx_docs_content ON documents
  USING GIN (to_tsvector('english', content));

-- 搜索
SELECT title, ts_headline('english', content, query) AS highlight
FROM documents,
     plainto_tsquery('english', 'quick brown fox') AS query
WHERE to_tsvector('english', content) @@ query
ORDER BY ts_rank(to_tsvector('english', content), query) DESC
LIMIT 10;

-- 中文分词（需 zhparser 或 jieba 扩展）
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese
  ADD MAPPING FOR n,v,a,i,e,l WITH simple;

CREATE INDEX idx_docs_cn_content ON documents
  USING GIN (to_tsvector('chinese', content));

-- 文档表（关系型 + JSONB + FTS）
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    metadata JSONB NOT NULL,           -- 结构化元数据
    content TEXT NOT NULL,             -- 全文内容
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_docs_metadata ON documents USING GIN (metadata);
CREATE INDEX idx_docs_fts ON documents
  USING GIN (to_tsvector('english', content));

-- 查询：结构化 + 全文混合检索
SELECT id, metadata->>'title' AS title
FROM documents
WHERE metadata @> '{"status": "published", "category": "tech"}'
  AND to_tsvector('english', content) @@ plainto_tsquery('english', 'distributed database')
  AND created_at > '2025-01-01';

无连接池时：
  5000 个应用连接 → 5000 个 Backend Process
  每进程内存 = shared_buffers 中无此连接额外开销
  但：每进程 ~10MB 栈 + 私有内存
  5000 × 10MB ≈ 50GB 内存 → OOM
  
解决方案：
  sidecar 连接池 → 5000 个应用连接 → 连接池中的 50 个 Server 连接 → 50 个 Backend Process
  50 × 10MB = 500MB → 可接受

-- 完整诊断级 EXPLAIN
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING, WAL)
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2025-01-01'
ORDER BY o.created_at DESC;

-- 查看最耗时的前 10 条查询
SELECT
  queryid,
  LEFT(query, 80) AS query_short,
  total_exec_time / calls AS avg_time_ms,
  rows / calls AS avg_rows,
  shared_blks_hit * 100.0 / NULLIF(shared_blks_hit + shared_blks_read, 0) AS hit_ratio,
  calls
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat%'
ORDER BY total_exec_time DESC
LIMIT 10;

-- 查看当前会话的等待事件
SELECT
  pid,
  wait_event_type,
  wait_event,
  state,
  query,
  AGE(clock_timestamp(), query_start) AS query_duration
FROM pg_stat_activity
WHERE state != 'idle'
  AND wait_event IS NOT NULL;

-- 检查未使用的索引
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT indexrelid FROM pg_constraint WHERE conindid = indexrelid
  );

-- 检查冗余索引
SELECT
  pg_size_pretty(SUM(pg_relation_size(indexrelid))) AS total_size
FROM pg_index
WHERE indrelid = 'orders'::regclass;

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements, auto_explain'

-- auto_explain 参数
auto_explain.log_min_duration = '1s'    -- 记录超过 1s 的查询
auto_explain.log_analyze = on           -- 输出 ANALYZE 信息
auto_explain.log_buffers = on           -- 输出 Buffer 信息
auto_explain.log_nested_statements = on -- 记录嵌套语句
auto_explain.log_timing = on            -- 记录时间
auto_explain.log_verbose = on           -- 输出完整信息
auto_explain.sample_rate = 1.0          -- 采样率（生产可设 0.1）

CREATE EXTENSION postgis;

-- 创建空间表
CREATE TABLE locations (
    id BIGSERIAL PRIMARY KEY,
    name TEXT,
    geom GEOMETRY(Point, 4326)  -- WGS84 经纬度坐标系
);

CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

-- 距离查询
SELECT name FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(116.4074, 39.9042), 0.05);
-- 经纬度 (116.4, 39.9) 附近约 5km 的位置