PolarDB PostgreSQL版支持通过控制台添加列存索引只读节点和直接使用列存索引插件这两种方式来开启和使用列存索引。您可以根据实际业务场景选择最适合您的使用方式。
前提条件
集群版本:
PostgreSQL 16(内核小版本为2.0.16.8.3.0及以上)
PostgreSQL 14(内核小版本为2.0.14.10.20.0及以上)
原表必须包含主键,且在创建列存索引时需要将主键列加入列存索引中。
wal_level
参数的值需设置为logical
,即在预写式日志WAL(Write-ahead logging)中增加支持逻辑编码所需的信息。说明您可以通过控制台设置wal_level参数。修改该参数后集群将会重启,请在修改参数前做好业务安排,谨慎操作。
使用限制
一张表只能创建一个列存索引。
不支持修改列存索引。如需在列存索引中添加列,请重建列存索引。
开启列存索引功能
对于不同的PolarDB PostgreSQL版内核版本,开启列存索引的方式不同:
创建列存索引
1. 为指定列或所有列创建列存索引
语法说明
-- 为部分列创建索引
CREATE INDEX [ CONCURRENTLY ] csi_index_name ON table_name USING CSI(column1,column2,...);
-- 为所有列创建索引
CREATE INDEX [ CONCURRENTLY ] csi_index_name ON table_name USING CSI;
参数说明
参数 | 描述 |
CONCURRENTLY | 可选,使用并发模式创建索引。不会阻塞原表的写入操作,适用于对线上已有表的索引创建。 |
csi_index_name | 自定义的列存索引名称。 |
table_name | 需要创建列存索引的表名称。 |
column1,column2,... | 指定创建列存索引的表的具体列,如不指定则为所有列创建索引。 |
示例
准备基础测试表
sales
。CREATE TABLE sales (sale_id int primary key, name CHAR(10), amount int);
创建列存索引。由于一张表只能创建一个列存索引,请选择执行以下任一语句以为指定表创建列存索引。
指定列创建列存索引
CREATE INDEX idx_csi_sales ON sales USING CSI(sale_id, amount);
所有列创建列存索引
CREATE INDEX idx_csi_sales ON sales USING CSI;
使用并发模式为指定列创建列存索引
并发模式下创建列存索引不会阻塞原表的写入操作,适用于对线上已有表进行索引创建。
CREATE INDEX CONCURRENTLY idx_csi_sales ON sales USING CSI(sale_id, amount);
创建索引成功后可使用以下语句查看索引情况:
SELECT * FROM pg_indexes WHERE tablename = 'sales';
2. 查看索引创建进度
为数据量较大的表创建列存索引可能耗时较长,可通过以下方式查看索引创建的进度。同时,您可以调整相关参数以提升索引创建的速度。
SELECT * FROM pg_stat_progress_create_index;
3. (可选)取消索引创建
当创建索引耗时较长,且创建索引的进程对现有业务产生了影响,您可通过pg_cancel_backend
或pg_terminate_backend
取消索引的创建,函数中需要的pid
可通过2. 查看索引创建进度获取。
SELECT pg_cancel_backend(pid);
SELECT pg_terminate_backend(pid);
4. 查看索引大小
可以使用pg_relation_size
函数查看索引大小,并结合pg_size_pretty
函数以更友好的方式进行显示。
SELECT * FROM pg_size_pretty(pg_relation_size('csi_index_name'));
例如,查看之前创建的列存索引占用的存储空间大小。
SELECT * FROM pg_size_pretty(pg_relation_size('idx_csi_sales'));
5. (可选)配置列存索引数据实时性
PolarDB PostgreSQL版集群不仅支持对历史数据创建列存索引,还能够自动将行存表中的新数据同步至列存索引。当使用CREATE INDEX
语句为表创建列存索引后,之后新增的数据将实时写入列存索引,且无需对该过程进行额外操作。
为满足某些业务场景下的行列数据实时性的要求,您可以通过调整参数polar_csi.update_interval
和polar_csi.update_batch_count
来提升列存索引的实时性。
使用列存索引
1. 配置参数
在创建完列存索引后,可以通过如下参数来控制SQL查询语句是否使用列存索引。
参数 | 说明 |
| 是否允许查询语句使用列存索引,取值如下:
|
| 查询代价阈值,是否使用列存索引。如果查询代价小于当前设置阈值,使用行存引擎,反之使用列存引擎。
说明 将值设置为0时,所有查询语句都将优先采用列存索引。不建议在实际业务中将值设置为0,请根据行存引擎与列存引擎的负载情况动态调整该值。 |
2. 配置参数生效范围
全局范围
您可以通过控制台设置polar_csi.enable_query参数为on
,让所有数据库中的查询语句都会尝试使用列存索引。
会话级
会话内的所有查询都会尝试使用列存索引。
SET polar_csi.enable_query = ON;
SQL语句级
会话内的单个SQL查询语句使用列存索引,可通过Hint方式实现。
依赖pg_hint_plan插件的能力,需要在目标数据库执行CREATE EXTENSION pg_hint_plan;
以安装该插件。
/*+ SET (polar_csi.enable_query on/off) */ SELECT COUNT(*) FROM sales;
3. 查看SQL是否使用列存索引
通过Explain
或Explain Analyze
等工具打印查询计划,出现CSI Executor
关键词时即表示该查询已经使用列存索引。
EXPLAIN SELECT COUNT(*) FROM sales;
返回结果如下:
QUERY PLAN
--------------
CSI Executor
(1 row)
4. (可选)配置列存索引数据查询一致性
PolarDB PostgreSQL版列存索引支持两种查询一致性级别,以满足不同业务需求。
最终一致性(默认):适用于写入负载高,但对数据实时性要求低的查询。
强一致性:在列存数据与行存数据完全一致后再返回查询结果。
PolarDB PostgreSQL版的列存索引实时性可以达到秒级,但当写入负载过大时,列存索引的数据延迟会升高。默认的一致性级别为最终一致性,所以当写入负载过大,且SQL查询使用列存索引时,可能无法查出最新的数据。
为满足某些业务场景下的行列强一致性要求,您可以通过将参数polar_csi.forward_replay_wait调整为强一致性读(on
)来实现。当查询时,系统会等待列存索引的数据与行存数据一致时才会执行。
删除与重建列存索引
由于当前PolarDB PostgreSQL版集群不支持修改列存索引,若您需要在已有的列存索引中添加其他指定列,您可以选择删除索引重新创建或重建列存索引。
删除列存索引
语法说明
DROP INDEX csi_index_name;
参数说明
参数 | 描述 |
csi_index_name | 需要删除的列存索引名称。 |
示例
删除之前创建的列存索引。
DROP INDEX idx_csi_sales;
重建列存索引
语法说明
REINDEX INDEX csi_index_name;
参数说明
参数 | 描述 |
csi_index_name | 需要重建的列存索引名称。 |
示例
重建之前创建的列存索引。
REINDEX INDEX idx_csi_sales;
调整列存索引参数提升性能
提升索引创建速度
索引创建速度与多个参数有关,如下所示:
参数名 | 取值范围 | 默认值 | 描述 |
polar_csi.memory_limit | 1~1048576 | 1024 | 列存索引可以使用的内存大小,单位:MB。 内存阈值越大,索引创建越快。请根据集群规格来调整该参数的值,建议不超过集群内存的20%,否则可能造成OOM的风险。 |
polar_csi.flush_count | 2048~20480000 | 204800 | 批次提交的行数。 增加行数能够提高创建效率,但同时也需要占用更多内存。 |
提升列存索引的实时性
PolarDB在以下版本中新增了以下参数。通过调整这些参数,您可以提高行列转换的速度,从而提升列存索引数据的实时性。
PostgreSQL 14:内核小版本2.0.14.13.28.0及以上。
PostgreSQL 16:无相关参数。
参数名 | 取值范围 | 默认值 | 作用 |
polar_csi.update_interval | 0~3600 | 3 | 定期更新时间间隔,单位:秒。 增大更新间隔可以合并同类型的小事务,从而在事务数量较多时提高数据更新效率。 |
polar_csi.update_batch_count | 1024~4294967295 | 100000 | 批量更新行数的阈值。 一个更新事务的最大行数,增加该阈值可以提升数据更新的效率。 |
提升查询速度
查询性能主要与以下参数有关,可通过调整参数值提升查询性能:
参数名 | 取值范围 | 默认值 | 作用 |
polar_csi.exec_parallel | 1~512 | 2 | 列存索引执行单条查询语句的并行度。 一般来说并行度越大性能越好。与集群资源规格相关,请根据集群规格来调整该参数的值,建议不超过计算节点的CPU核数。 |
polar_csi.memory_limit | 1~1048576 | 1024 | 列存索引可以使用的内存,单位:MB。 请根据集群规格来调整该参数的值,建议不超过集群内存的20%,否则可能造成OOM的风险。 |
polar_csi.cost_threshold | 1~1000000000 | 50000 | 查询代价阈值,是否使用列存索引。如果查询代价小于当前设置阈值,使用行存引擎,反之使用列存引擎。 当该参数的取值过大时,会导致耗时的SQL语句无法使用列存索引,查询较慢。当该参数的取值过小时,会导致简单SQL使用列存索引,降低系统的并发度。请根据实际业务负载进行调整。 说明
|
polar_csi.forward_replay_wait | on|off | off | 列存索引数据查询一致性级别,取值如下:
|