PostgreSQL中Columnstore index创建及与partition、Cluster index结合咨询

1. 能否在PostgreSQL中创建列存储索引 #

原生PostgreSQL官方稳定版（截至PostgreSQL 16）没有内置列存储索引能力，需要通过第三方扩展实现该功能，主流适用扩展包括cstore_fdw、pg_columnstore，时序场景下也可选用timescaledb提供的列式压缩存储能力。

2. 列存储索引与分区、聚簇索引结合的操作方法 #

以下以使用最广泛的cstore_fdw、pg_columnstore两个扩展为例说明操作步骤：

• 前置准备：安装对应扩展后在数据库中启用
  以cstore_fdw为例，执行以下命令完成初始化：

  -- 启用扩展
  CREATE EXTENSION cstore_fdw;
  -- 创建对应外部服务
  CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;
  

• 与分区表结合操作
  你可以直接将列存储表作为分区子表挂载到普通分区父表下，同时实现分区管理和列存加速，示例代码如下：

  -- 创建行式父分区表
  CREATE TABLE sales (
      sale_id INT,
      sale_date DATE,
      amount NUMERIC,
      region TEXT
  ) PARTITION BY RANGE (sale_date);
  
  -- 创建列存储分区子表，挂载到父表
  CREATE FOREIGN TABLE sales_2024 PARTITION OF sales
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
  SERVER cstore_server
  OPTIONS (compression 'pglz');
  

  后续直接对父表sales做增删改查即可，数据库会自动路由到对应列存分区，不需要额外调整业务代码。
• 与聚簇索引结合操作
  列存储表本身不支持PostgreSQL原生行式CLUSTER聚簇操作，可通过两种方式实现类似聚簇的效果：
  1. 数据写入前先按需要聚簇的字段（通常是高频过滤、关联字段）排序，再写入列存储表，列存会按排序后的顺序组织数据块，查询对应字段范围时扫描效率会提升3~10倍，排序导入操作无额外配置，导入提前排序好的数据源即可。
  2. 若使用pg_columnstore扩展，可直接在创建表时指定SORT KEY，即可实现列存场景的类聚簇索引能力，示例如下：

  CREATE TABLE sales (
      sale_id INT,
      sale_date DATE,
      amount NUMERIC,
      region TEXT
  ) USING columnstore
  SORT KEY (sale_date)
  PARTITION BY RANGE (sale_date);
  

注意：不要对列存储表执行原生CLUSTER命令，该命令仅支持普通行式堆表，对列存表执行会报错。

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