PostgreSQL 索引与存储访问方法

解释 PostgreSQL heap table access method 与 B-tree、Hash、GIN、GiST、SP-GiST、BRIN、Bloom、HNSW 和 IVFFlat 的选择边界

PostgreSQL 不采用 MySQL 那种为普通表频繁选择 InnoDB/MyISAM 的使用模型。绝大多数表使用核心 heap table access method；索引通过独立的 index access method 和 operator class 决定支持哪些查询。

Table Access Method 不是日常调优开关

PostgreSQL 提供 Table Access Method API，允许扩展或定制构建实现新的表存储方式，但普通应用的默认仍是 heap：

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at timestamptz NOT NULL,
  payload jsonb NOT NULL
) USING heap;

通常省略 USING heap。选择新 table access method 会进入 WAL、MVCC、VACUUM、backup、replication、extension 和 major upgrade 的关键路径，不能像切换 query hint 一样随意尝试。

查看当前实例暴露的 access method：

SELECT
  amname,
  CASE amtype
    WHEN 't' THEN 'table'
    WHEN 'i' THEN 'index'
    ELSE amtype::text
  END AS access_method_type
FROM pg_am
ORDER BY amtype, amname;

核心索引访问方法

PostgreSQL 18 核心提供 B-tree、Hash、GiST、SP-GiST、GIN 和 BRIN；bloom 是随 PostgreSQL 提供但需要 CREATE EXTENSION bloom 的 module。官方边界见 Index Types。

类型	优先场景	重要边界
B-tree	`=`、范围、排序、unique、前缀模式匹配	默认选择；复合列顺序与 operator class 决定可用查询
Hash	单列等值比较	只支持 `=`；B-tree 通常更通用，采用前需有测量收益
GIN	JSONB、array、全文检索和多值内容	更新和构建成本较高；行为取决于 operator class
GiST	range、几何、PostGIS、nearest-neighbor	是可扩展框架，不是单一算法；必须匹配 operator/operator class
SP-GiST	trie、quad-tree、k-d tree 等空间分区结构	适合具有可分区结构的数据；不是 GiST 的通用替换
BRIN	与物理顺序高度相关的超大追加表	保存 block range 摘要；相关性差时会读取大量 heap block
Bloom extension	多列任意组合的等值过滤	lossy、需要 recheck；不支持 range、unique 或搜索 `NULL`；自带 operator class 仅覆盖 `int4` 与 `text`

索引名称不如 operator class 重要

GIN、GiST、SP-GiST 和 BRIN 是框架。真正决定支持哪些 operator、排序和数据类型的是 operator class。看到“使用 GIN”仍不足以复现一个索引设计。

常见工作负载映射

等值 / 范围 / 排序 / unique     → B-tree
JSONB contains / array member   → GIN
PostgreSQL FTS                  → GIN（通常）
range / GIS / nearest neighbor  → GiST 或匹配的 SP-GiST
超大、按时间物理追加             → BRIN
向量 ANN                        → pgvector HNSW / IVFFlat

HNSW 与 IVFFlat 来自 pgvector，不是 PostgreSQL 核心 index method。它们需要单独验证 recall、filter、memory、build time、WAL、replica lag 与 extension upgrade。

PostgreSQL 内置全文检索支持 parser、dictionary、ranking、highlight 和 GIN/GiST index。内置配置不自动解决所有语言的分词；例如中文通常需要额外 tokenizer/extension 或应用侧预处理，不能只创建一个 GIN 就宣称搜索质量成立。

创建前先匹配查询

-- 普通业务过滤与排序
CREATE INDEX CONCURRENTLY orders_customer_time_idx
ON orders (customer_id, placed_at DESC);

-- JSONB 包含查询：payload @> '{"status":"paid"}'
CREATE INDEX CONCURRENTLY events_payload_gin_idx
ON events USING gin (payload jsonb_path_ops);

-- 时间与物理写入顺序高度相关的大表
CREATE INDEX CONCURRENTLY events_time_brin_idx
ON events USING brin (occurred_at);

jsonb_path_ops 更专注于 @>、@?、@@ 等路径/包含查询，并不支持默认 jsonb_ops 的全部 operator。索引 DDL 必须从真实 query shape 反推。

创建后记录 plan 与尺寸：

SELECT
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'events'
ORDER BY pg_relation_size(indexrelid) DESC;

再使用 EXPLAIN (ANALYZE, BUFFERS) 比较实际行数、heap block、recheck、排序与写入代价。完整步骤见索引与 EXPLAIN。

原生能力优先

需求	先验证 PostgreSQL 原生	仍不足时再评估
模糊匹配	FTS、`pg_trgm` contrib、表达式/GIN/GiST index	外部搜索或 BM25 extension
任务互斥	transaction、`FOR UPDATE SKIP LOCKED`、advisory lock	专用队列与 workflow 系统
时间生命周期	原生 partition、BRIN、scheduled cleanup	pg_partman、TimescaleDB
跨库访问	`postgres_fdw`、logical replication	CDC 平台或独立同步系统
分析	materialized view、partition、parallel query	pg_duckdb、pg_mooncake、warehouse
向量检索	没有核心 vector type/index	pgvector 或专用向量系统

“原生优先”不是拒绝扩展，而是减少不必要的 binary、license、backup 和 upgrade 依赖。扩展候选见 PostgreSQL 扩展生态选型。

Table Access Method 不是日常调优开关

核心索引访问方法

常见工作负载映射

创建前先匹配查询

原生能力优先

On this page