416_PostgreSQL高级特性¶

一句话说明¶

PostgreSQL是生信数据库的最佳选择，它的JSON支持、数组类型、全文搜索、扩展插件让它远超普通SQL数据库。

核心知识点¶

PostgreSQL独特优势¶

特性	说明	生信应用
JSON/JSONB	原生JSON存储和查询	存储变长注释数据
数组类型	列可以是数组	存储GO term列表
全文搜索	内置tsvector	文献检索
物化视图	缓存复杂查询结果	预计算统计量
分区表	自动按范围/列表分区	按染色体分区基因组数据
PostGIS	地理信息扩展	蛋白质结构空间查询
pg_trgm	三字组模糊匹配	基因名模糊搜索

实战SQL示例¶

-- ========== 1. JSONB类型：存储灵活的注释数据 ==========
-- JSONB比JSON快（二进制存储，支持索引），推荐使用JSONB

CREATE TABLE variant_annotations (
    variant_id    VARCHAR(50) PRIMARY KEY,
    chrom         VARCHAR(5),
    pos           INT,
    ref_allele    VARCHAR(100),
    alt_allele    VARCHAR(100),
    annotations   JSONB  -- 存储可变的注释信息
);

-- 插入包含嵌套JSON的数据
INSERT INTO variant_annotations VALUES (
    'CHR1_1000_A_T',
    'chr1', 1000, 'A', 'T',
    '{
        "gene": "BRCA1",
        "consequence": "missense_variant",
        "impact": "MODERATE",
        "clinvar": {
            "classification": "Pathogenic",
            "review_stars": 4
        },
        "population_freq": {
            "gnomad_af": 0.0001,
            "1000g_eur": 0.0003
        },
        "prediction_scores": ["SIFT:0.01", "PolyPhen:0.99", "CADD:28.5"]
    }'
);

-- 查询JSON字段（->> 返回文本，-> 返回JSON）
SELECT 
    variant_id,
    annotations->>'gene' AS gene_name,                       -- 取字符串值
    annotations->'clinvar'->>'classification' AS clinvar,    -- 嵌套取值
    (annotations->'clinvar'->>'review_stars')::INT AS stars  -- 转换类型
FROM variant_annotations
WHERE annotations->>'consequence' = 'missense_variant'  -- JSON字段过滤
  AND (annotations->'clinvar'->>'classification') = 'Pathogenic';

-- JSONB包含查询（@>）：找包含特定子文档的行
SELECT variant_id FROM variant_annotations
WHERE annotations @> '{"clinvar": {"classification": "Pathogenic"}}';  -- 包含操作符

-- JSON数组查询（找prediction_scores包含SIFT的条目）
SELECT variant_id FROM variant_annotations
WHERE annotations->'prediction_scores' ? 'SIFT:0.01';  -- ? 检查key存在

-- 为JSONB创建GIN索引（加速 @> 和 ? 查询）
CREATE INDEX idx_annotations_gin ON variant_annotations USING GIN (annotations);

-- ========== 2. 数组类型：存储GO terms ==========
CREATE TABLE gene_go (
    gene_id   VARCHAR(20) PRIMARY KEY,
    gene_name VARCHAR(50),
    go_terms  TEXT[],           -- GO term数组
    pathways  VARCHAR(20)[]     -- 通路ID数组
);

INSERT INTO gene_go VALUES (
    'ENSG00000012048',
    'BRCA1',
    ARRAY['GO:0006281', 'GO:0000724', 'GO:0042769'],  -- 数组字面量
    '{hsa04120, hsa04110}'                             -- 另一种数组语法
);

-- 查询：包含特定GO term的基因（ANY操作符）
SELECT gene_name, go_terms
FROM gene_go
WHERE 'GO:0006281' = ANY(go_terms);  -- ANY检查值是否在数组中

-- 查询：两个基因共有的GO terms（数组相交）
SELECT 
    a.gene_name AS gene1,
    b.gene_name AS gene2,
    a.go_terms & b.go_terms AS shared_go_terms  -- & = 数组交集（需intarray扩展）
FROM gene_go a, gene_go b
WHERE a.gene_id < b.gene_id  -- 避免重复对
  AND array_length(a.go_terms & b.go_terms, 1) > 0;  -- 有共同GO term

-- 为数组创建GIN索引
CREATE INDEX idx_go_terms_gin ON gene_go USING GIN (go_terms);

-- ========== 3. 全文搜索：基因名和描述的模糊搜索 ==========
-- 建立全文搜索索引
ALTER TABLE genes ADD COLUMN search_vector TSVECTOR;

-- 更新搜索向量（将基因名和功能描述合并为可搜索向量）
UPDATE genes SET search_vector = 
    to_tsvector('english',                          -- 使用英语词干提取
        COALESCE(gene_name, '') || ' ' ||
        COALESCE(gene_description, '')
    );

-- 为搜索向量创建GIN索引
CREATE INDEX idx_genes_fts ON genes USING GIN (search_vector);

-- 全文搜索查询（比LIKE更智能，支持词形变换）
SELECT gene_name, gene_description
FROM genes
WHERE search_vector @@ to_tsquery('english', 'breast & cancer & DNA:*')
  -- @@ = 匹配操作符
  -- to_tsquery: 'breast & cancer' = breast AND cancer, 'DNA:*' = DNA开头的词
ORDER BY ts_rank(search_vector, to_tsquery('english', 'breast & cancer')) DESC;
-- ts_rank: 计算相关性得分，用于排序

-- ========== 4. 表分区：按染色体分区基因组数据 ==========
-- 创建分区主表
CREATE TABLE variants (
    variant_id VARCHAR(50),
    chrom      VARCHAR(5)  NOT NULL,
    pos        INT         NOT NULL,
    ref        VARCHAR(50),
    alt        VARCHAR(50)
) PARTITION BY LIST (chrom);  -- 按染色体列表分区

-- 创建各染色体的分区
CREATE TABLE variants_chr1 PARTITION OF variants FOR VALUES IN ('chr1');
CREATE TABLE variants_chr2 PARTITION OF variants FOR VALUES IN ('chr2');
-- ... 
CREATE TABLE variants_chrX PARTITION OF variants FOR VALUES IN ('chrX');
CREATE TABLE variants_chrY PARTITION OF variants FOR VALUES IN ('chrY');

-- 查询时自动路由到对应分区（分区裁剪）
EXPLAIN SELECT * FROM variants WHERE chrom = 'chr1' AND pos BETWEEN 1000 AND 5000;
-- 执行计划只扫描 variants_chr1，不扫其他分区

-- ========== 5. 物化视图：缓存耗时的统计查询 ==========
-- 创建物化视图（真实存储查询结果，不是每次实时计算）
CREATE MATERIALIZED VIEW gene_expression_stats AS
SELECT 
    gene_id,
    COUNT(DISTINCT sample_id) AS sample_count,
    AVG(expression) AS mean_expression,
    STDDEV(expression) AS std_expression,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY expression) AS median_expression
FROM gene_expression
GROUP BY gene_id;

-- 创建索引加速对物化视图的查询
CREATE INDEX ON gene_expression_stats (mean_expression DESC);

-- 查询物化视图（速度极快，因为已预计算）
SELECT * FROM gene_expression_stats WHERE mean_expression > 100 ORDER BY mean_expression DESC;

-- 数据更新后刷新物化视图
REFRESH MATERIALIZED VIEW CONCURRENTLY gene_expression_stats;
-- CONCURRENTLY: 刷新时不锁表，允许并发读（但需要有唯一索引）

面试常问点¶

Q: JSON和JSONB有什么区别？ A: JSON存储原始文本（保留空格/key顺序）；JSONB存储二进制格式（压缩，key去重排序），JSONB支持索引和高效查询，通常选JSONB。
Q: 物化视图和普通视图的区别？ A: 普通视图每次查询都实时执行SQL；物化视图存储实际数据，查询速度快，但需要手动刷新（数据有延迟）。
Q: 什么时候用分区表？ A: 表非常大（百GB以上）且查询通常只涉及特定分区时（如按时间/染色体查询），分区能大幅提升性能（分区裁剪减少扫描量）。
Q: GIN索引和B-tree索引的区别？ A: B-tree适合等值/范围查询（普通列）；GIN（倒排索引）适合JSONB、数组、全文搜索等包含查询，多值类型必选GIN。

速查表¶

-- JSONB操作符
data->>'key'          -- 取字符串值
data->'key'           -- 取JSON值
data @> '{"k":"v"}'  -- 包含子文档
data ? 'key'          -- key是否存在
data #>> '{a,b}'      -- 路径取值

-- 数组操作
col = ANY(array)     -- 值在数组中
array && array2      -- 数组有交集
array_length(arr,1)  -- 数组长度
unnest(array)        -- 展开为多行

-- 全文搜索
to_tsvector('english', text)    -- 建搜索向量
to_tsquery('english', 'word')   -- 建查询
vector @@ query                  -- 匹配
ts_rank(vector, query)           -- 相关性评分