PostgreSQL中Hash主键与BigInt代理主键的读写性能对比

一、读写性能差异 #

1. 写入性能 #

两种方案的写入开销差异核心在于索引维护特性：

• 用key作为主键：key是随机的SHA256Hex字符串（64字符），主键B树索引的插入完全随机，会频繁触发B树页分裂，带来额外IO操作与索引结构调整，写入延迟更高，且数据量越大，页分裂的性能损耗越明显。
• 代理主键+key唯一索引：自增bigint代理主键的索引插入是有序的，几乎不会触发页分裂，维护成本极低。虽需额外维护key列的唯一索引（该索引插入同样随机，会产生页分裂开销），但整体来看，有序主键的低开销加上单个随机唯一索引的开销，通常比单个随机主键索引的总开销更小，写入性能更优。

2. 读取性能 #

你的核心操作是通过key查询数据，两种方案的读取路径几乎一致：

• key作为主键：直接通过主键B树索引定位数据的ctid，再访问堆表获取完整行，仅需一次索引查找+一次堆访问。
• 代理主键+key唯一索引：通过key的唯一索引定位ctid，再访问堆表获取数据，同样是一次索引查找+一次堆访问。
  由于key列的索引（主键或唯一索引）条目大小完全相同（64字节key+ctid），两种方案的读取性能几乎无差异。

二、索引碎片化判断的正确性 #

你的判断正确，两种方案都会受到索引碎片化影响，仅影响的索引不同：

• key作为主键：主键B树索引因随机插入频繁页分裂，会产生大量内部空闲空间，碎片化程度高，长期运行后会导致索引体积膨胀、读写IO开销增加。
• 代理主键+key唯一索引：自增代理主键的索引几乎无碎片化，但key列的唯一索引同样因随机插入产生严重碎片化，影响范围仅局限于该唯一索引。

额外建议 #

若写入量较大，优先选择代理主键+唯一索引方案，可有效降低写入时的页分裂开销；若存储资源有限，key作为主键的方案总索引存储量更小，但需定期重建主键索引（如执行REINDEX INDEX idx_key;）缓解碎片化问题。

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