PostgreSQL 深度实战：MVCC架构、高级索引、向量搜索与高可用运维完全指南

-- 查看表膨胀情况
SELECT schemaname, relname, 
       n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- 覆盖索引（Index Only Scan）
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date) 
INCLUDE (total_amount, status);

-- 部分索引（Partial Index）
CREATE INDEX idx_active_users ON users(email) 
WHERE status = 'active';

-- 表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM order_date));

-- GIN 索引（数组、JSONB、全文搜索）
CREATE INDEX idx_articles_tags ON articles USING GIN(tags);
CREATE INDEX idx_logs_data ON logs USING GIN(data jsonb_path_ops);

-- BRIN 索引（大范围数据，小体积）
CREATE INDEX idx_metrics_time ON metrics USING BRIN(timestamp) 
WITH (pages_per_range = 32);

-- 创建全文搜索列（建议在表设计阶段就加上）
ALTER TABLE articles ADD COLUMN tsv tsvector 
GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
    setweight(to_tsvector('english', coalesce(content,'')), 'B')
) STORED;

CREATE INDEX idx_articles_tsv ON articles USING GIN(tsv);

-- 搜索查询
SELECT title, ts_rank(tsv, query) AS rank
FROM articles, plainto_tsquery('english', 'database optimization') query
WHERE tsv @@ query
ORDER BY rank DESC
LIMIT 20;

-- 高亮匹配片段
SELECT ts_headline('english', content, query, 
    'StartSel=, StopSel=, MaxWords=50, MinWords=10')
FROM articles, phraseto_tsquery('english', 'query optimization') query
WHERE tsv @@ query;

-- JSONB 常用运算符
SELECT data->>'name' FROM users WHERE data @> '{"city": "Beijing"}';
SELECT data->'social'->>'github' FROM profiles WHERE data ? 'skills';

-- 更新 JSONB 字段（部分更新）
UPDATE users SET data = jsonb_set(data, '{age}', '30') WHERE id = 1;

-- 批量 JSONB 聚合
SELECT category, jsonb_agg(jsonb_build_object('id', id, 'name', name)) 
FROM products GROUP BY category;

-- JSONB 路径查询（PostgreSQL 12+）
SELECT jsonb_path_query(data, '$.orders[*] ? (@.amount > 100)') 
FROM events;

-- Range 分区（按时间）
CREATE TABLE measurements (
    id BIGSERIAL,
    device_id INT NOT NULL,
    value DOUBLE PRECISION,
    recorded_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (recorded_at);

CREATE TABLE measurements_2024_q1 PARTITION OF measurements
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE measurements_2024_q2 PARTITION OF measurements
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- 自动创建分区（PostgreSQL 16+）
CREATE TABLE logs (
    id BIGSERIAL,
    message TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- 分区裁剪查询
EXPLAIN SELECT * FROM measurements 
WHERE recorded_at >= '2024-01-15' AND recorded_at < '2024-02-15';

-- 主库配置（postgresql.conf）
-- wal_level = replica
-- max_wal_senders = 10
-- wal_keep_size = 1GB
-- hot_standby = on

-- 创建复制用户
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';

-- 备库恢复配置（PostgreSQL 12+ standby.signal）
-- recovery_target_timeline = 'latest'
-- primary_conninfo = 'host=primary_host port=5432 user=replicator'

-- 安装扩展
CREATE EXTENSION vector;

-- 创建文档表
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    embedding vector(1536),  -- OpenAI text-embedding-3-small 维度
    metadata JSONB DEFAULT '{}'
);

-- 创建 HNSW 索引（速度快，召回率高，占用内存较大）
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 200);

-- 相似度搜索
SELECT title, content, 1 - (embedding <=> '[0.01, 0.02, ...]'::vector) AS similarity
FROM documents
WHERE metadata @> '{"category": "tech"}'
ORDER BY embedding <=> '[0.01, 0.02, ...]'::vector
LIMIT 10;

-- 混合搜索：向量相似度 + 关键词过滤
SELECT title, content,
       (0.7 * (1 - (embedding <=> $1::vector)) + 
        0.3 * ts_rank(to_tsvector(content), plainto_tsquery($2))) AS score
FROM documents
WHERE to_tsvector(content) @@ plainto_tsquery($2)
ORDER BY score DESC
LIMIT 10;

# 内存配置
shared_buffers = 16GB                  # 25% of RAM
effective_cache_size = 48GB            # 75% of RAM
work_mem = 256MB                       # 复杂查询排序用
maintenance_work_mem = 2GB             # VACUUM, CREATE INDEX
huge_pages = on                        # 启用大页内存

# WAL 配置
wal_compression = on
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 8GB

# 并行查询
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_parallel_maintenance_workers = 4

# 查询优化
random_page_cost = 1.1                 # SSD 磁盘
effective_io_concurrency = 200         # SSD
jit = on                               # JIT 编译加速

-- 慢查询分析（需开启 pg_stat_statements）
SELECT round(total_exec_time::numeric, 2) AS total_time,
       calls,
       round(mean_exec_time::numeric, 2) AS avg_time,
       round((100 * total_exec_time / SUM(total_exec_time) OVER())::numeric, 2) AS pct,
       query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 查看正在运行的长查询
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle' AND now() - query_start > interval '30 seconds'
ORDER BY duration DESC;

[databases]
mydb = host=127.0.0.1 port=5432 dbname=production

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction          # 事务级复用
max_client_conn = 1000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 3

# 逻辑备份（适合小到中型数据库）
pg_dump -h localhost -U postgres -Fc production_db > backup.dump
pg_restore -h localhost -U postgres -d new_db backup.dump

# 物理备份 + WAL 归档（适合大型数据库）
pg_basebackup -h localhost -D /backup/base -Ft -Xs -P -v

# 连续归档配置
# archive_mode = on
# archive_command = 'cp %p /archive/%f'
# restore_command = 'cp /archive/%f %p'