PostgreSQL 12 新特性-SQL、配置参数调整

PostgreSQL 12 在 SQL 功能和配置参数方面引入了许多重要的新特性和调整，下面为你详细介绍：

SQL 相关新特性

1. 索引增强

• B 树索引改进
  • 降序索引优化：在 PostgreSQL 12 中，降序 B 树索引的性能得到了显著提升。降序索引和升序索引的性能差异大幅减小，这意味着在使用 ORDER BY ... DESC 语句时，数据库可以更高效地利用降序索引。
  • 索引合并：支持对多个 B 树索引进行合并扫描，允许数据库在执行查询时同时使用多个索引来满足查询条件，从而提高查询性能。例如：

 

-- 创建两个索引
CREATE INDEX idx_col1 ON your_table (col1);
CREATE INDEX idx_col2 ON your_table (col2);

-- 查询可以同时利用这两个索引
SELECT * FROM your_table WHERE col1 = 'value1' AND col2 = 'value2';

• 表达式索引支持部分索引：现在可以在表达式索引上创建部分索引，这对于过滤出特定条件的数据非常有用。例如：

CREATE INDEX partial_expr_idx ON your_table ((col1 + col2)) WHERE col3 > 10;

2. 窗口函数增强

• 窗口函数支持 FILTER 子句：允许在窗口函数中使用 FILTER 子句来过滤行，从而只对满足特定条件的行应用窗口函数。例如：

SELECT 
    col1,
    SUM(col2) FILTER (WHERE col3 = 'value') OVER (PARTITION BY col1)
FROM 
    your_table;

3. 分区表增强

• 分区修剪改进：PostgreSQL 12 对分区修剪算法进行了优化，能够更准确地判断哪些分区可以被排除在查询之外，从而减少不必要的分区扫描。
• 支持列表分区的 DEFAULT 子分区：在列表分区中，可以定义一个 DEFAULT 子分区，用于存储不匹配其他分区条件的数据。例如：

CREATE TABLE partitioned_table (
    id int,
    category text
) PARTITION BY LIST (category);

CREATE TABLE partition_1 PARTITION OF partitioned_table FOR VALUES IN ('category1');
CREATE TABLE partition_2 PARTITION OF partitioned_table FOR VALUES IN ('category2');
CREATE TABLE partition_default PARTITION OF partitioned_table DEFAULT;

4. CTE 增强

• CTE 支持 MATERIALIZED 关键字：可以使用 MATERIALIZED 关键字强制将公共表表达式（CTE）的结果物化，避免在查询中多次计算相同的 CTE。例如：

WITH MATERIALIZED cte AS (
    SELECT * FROM your_table WHERE col1 = 'value'
)
SELECT * FROM cte JOIN another_table ON cte.id = another_table.id;

配置参数调整

1. 并行查询相关参数

• max_parallel_workers_per_gather：默认值从 2 增加到 4。这个参数控制每个 Gather 或 Gather Merge 节点可以启动的最大并行工作进程数，增加默认值可以让更多的查询有机会使用并行查询来提高性能。

2. 逻辑复制相关参数

• max_logical_replication_workers：默认值从 4 增加到 10。这个参数控制逻辑复制中最大的工作进程数，提高默认值可以支持更多的逻辑复制任务。

3. 内存管理相关参数

• shared_buffers：官方文档建议在有足够内存的系统上，将 shared_buffers 设置为系统内存的 25%。这是 PostgreSQL 用于缓存数据块的共享内存区域，适当增加该参数可以减少磁盘 I/O，提高查询性能。

4. 自动清理相关参数

• autovacuum_max_workers：默认值从 3 增加到 8。这个参数控制自动清理进程的最大数量，增加默认值可以加快自动清理的速度，减少表膨胀的风险。