flowchart TD
    A[INSERT 一行數(shù)據(jù)] --> B[Heap Tuple v1: xmin=100, xmax=0]
    B --> C[UPDATE 該行]
    C --> D[Heap Tuple v1: xmin=100, xmax=200 標(biāo)記過期]
    C --> E[Heap Tuple v2: xmin=200, xmax=0 新版本]
    E --> F[DELETE 該行]
    F --> G[Heap Tuple v2: xmin=200, xmax=300 標(biāo)記刪除]
    D --> H[Dead Tuple: 等待 VACUUM 回收]
    G --> H
    H --> I[VACUUM 掃描]
    I --> J[標(biāo)記空間為可復(fù)用: FSM 更新]
    I --> K[更新統(tǒng)計信息: pg_statistic]
    style H fill:#ffebee
    style J fill:#e8f5e9
    style K fill:#e8f5e9

flowchart TD
    A[查詢: WHERE user_id = 12345] --> B[B-Tree Root 節(jié)點]
    B --> C[Branch 節(jié)點: 二分查找確定子節(jié)點]
    C --> D[Leaf 節(jié)點: 找到 CTID 指針]
    D --> E[Heap Fetch: 根據(jù) CTID 讀取行數(shù)據(jù)]
    E --> F{版本檢查}
    F -->|xmin 可見, xmax=0| G[返回數(shù)據(jù)]
    F -->|xmax 已提交| H[沿更新鏈查找新版本]
    H --> G
    style B fill:#e3f2fd
    style D fill:#e3f2fd
    style G fill:#e8f5e9

-- 啟用慢查詢?nèi)罩荆〞捈墑e）
SET log_min_duration_statement = 100;
-- 查看當(dāng)前活躍慢查詢
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - pg_stat_activity.query_start > interval '100 milliseconds'
ORDER BY duration DESC;
-- EXPLAIN ANALYZE 分析執(zhí)行計劃
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT user_id, feature_vector, last_updated
FROM user_features
WHERE tenant_id = 42
  AND last_updated > NOW() - INTERVAL '7 days'
ORDER BY last_updated DESC
LIMIT 100;
-- 創(chuàng)建復(fù)合索引（等值列在前，范圍列在后）
CREATE INDEX CONCURRENTLY idx_user_features_tenant_updated
ON user_features (tenant_id, last_updated DESC);
-- 覆蓋索引消除回表
CREATE INDEX CONCURRENTLY idx_user_features_covering
ON user_features (tenant_id, last_updated DESC)
INCLUDE (feature_vector);

-- 內(nèi)存配置
ALTER SYSTEM SET shared_buffers = '4GB'; -- 系統(tǒng)內(nèi)存25%
ALTER SYSTEM SET effective_cache_size = '12GB'; -- 系統(tǒng)內(nèi)存75%
ALTER SYSTEM SET work_mem = '64MB'; -- 按并發(fā)量計算
ALTER SYSTEM SET maintenance_work_mem = '1GB';
-- WAL與檢查點配置
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET max_wal_size = '4GB';
ALTER SYSTEM SET min_wal_size = '1GB';
ALTER SYSTEM SET wal_buffers = '64MB';
-- 自動清理配置
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.02;
-- 特定大表設(shè)置更激進策略
ALTER TABLE large_feature_table SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005
);
-- 應(yīng)用配置變更
SELECT pg_reload_conf();

-- 緩沖池命中率監(jiān)控
SELECT
    'index hit ratio' AS metric,
    ROUND(
        (sum(idx_blks_hit)::float / NULLIF(sum(idx_blks_hit + idx_blks_read), 0)) * 100,
        2
    ) AS ratio_pct
FROM pg_statio_user_indexes
UNION ALL
SELECT
    'table hit ratio' AS metric,
    ROUND(
        (sum(heap_blks_hit)::float / NULLIF(sum(heap_blks_hit + heap_blks_read), 0)) * 100,
        2
    ) AS ratio_pct
FROM pg_statio_user_tables;
-- 表膨脹檢測
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    ROUND(
        100.0 * pg_total_relation_size(schemaname || '.' || tablename)
        / NULLIF(pg_relation_size(schemaname || '.' || tablename), 0),
        1
    ) AS bloat_ratio_pct
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;