postgresql 10 新特性-阿里云开发者社区

postgresql 10 新特性

2018-02-23 2285

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云原生数据库 PolarDB MySQL 版，Serverless 5000PCU 100GB

云原生数据库 PolarDB PostgreSQL 版，标准版 2核4GB 50GB

云原生数据库 PolarDB MySQL 版，通用型 2核4GB 50GB

简介：

三大新特性

"better parallelism" (37%)
"logical replication" (32%)
"native partitioning" (31%)

后面的百分比代表人民的呼声。。。

原生分区

语法：

CREATE TABLE table_name ( ... )
   [ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) }

CREATE TABLE table_name
   PARTITION OF parent_table [ (
   ) ] FOR VALUES partition_bound_spec

示例1：

CREATE TABLE padre (
     id         SERIAL NOT NULL,
     nombre     TEXT NOT NULL,
     fch_creado TIMESTAMPTZ NOT NULL
    )
    PARTITION BY RANGE ( id );

CREATE TABLE hijo_0
      PARTITION OF padre (id, PRIMARY KEY (id), UNIQUE (nombre))
      FOR VALUES FROM (MINVALUE) TO (10);

CREATE TABLE hijo_1
      PARTITION OF padre (id, PRIMARY KEY (id), UNIQUE (nombre))
      FOR VALUES FROM (10) TO (MAXVALUE);

示例2：

创建一个book_history分区表，一个月一个分区
创建master表：

CREATE TABLE book_history (
               book_id INTEGER NOT NULL,
               status BOOK_STATUS NOT NULL,
               period TSTZRANGE NOT NULL )
               PARTITION BY RANGE ( lower (period) );

创建分区表：

 CREATE TABLE book_history_2016_09
               PARTITION OF book_history
               FOR VALUES FROM ('2016-09-01 00:00:00') TO ('2016-10-01 00:00:00');
   CREATE TABLE

CREATE TABLE book_history_2016_08
               PARTITION OF book_history
               FOR VALUES FROM ('2016-08-01 00:00:00') TO ('2016-09-01 00:00:00');
   CREATE TABLE

CREATE TABLE book_history_2016_07
               PARTITION OF book_history
               FOR VALUES FROM ('2016-07-01 00:00:00') TO ('2016-09-01 00:00:00');
   ERROR:  partition "book_history_2016_07" would overlap partition "book_history_2016_08"

分区之间不能有范围重叠。

并行查询

可以参考大神(特性贡献者)博客：http://rhaas.blogspot.hk/2017/03/parallel-query-v2.html

Parallel Merge Join: In PostgreSQL 9.6, only hash joins and nested loops can be performed in the parallel portion of a plan. In PostgreSQL 10, merge joins can also be performed in the parallel portion of the plan.
Parallel Bitmap Heap Scan: One process scans the index and builds a data structure in shared memory indicating all of the heap pages that need to be scanned, and then all cooperating processes can perform the heap scan in parallel.
Parallel Index Scan and Index-Only Scan: It's now possible for the driving table to be scanned using an index-scan or an index-only scan.
Gather Merge: If each worker is producing sorted output, then gather those results in a way that preserves the sort order.
Subplan-Related Improvements: A table with an uncorrelated subplan can appear in the parallel portion of the plan.
Pass Query Text To Workers: The query text associated with a parallel worker will show up in pg_stat_activity.
Procedural Languages

逻辑复制

同样围观大神博客（特性贡献者）：
https://blog.2ndquadrant.com/logical-replication-postgresql-10/
逻辑复制算是流复制的一种补充，可以自定义我们需要复制的表。
逻辑复制两个表：users, addresses

wal_level 最少处于logical级别
CREATE PUBLICATION testpub FOR TABLE users, addresses;
其他库上执行
CREATE SUBSCRIPTION testsub CONNECTION 'host=upstream-host dbname=users ...'

PUBLICATION testpub;