标签

PostgreSQL , Oracle , 兼容性 , 18c

---

背景

Oracle 18c 发布了诸多新特性，其中大部分Oracle 18c的重大特性，在早期版本的PostgreSQL数据库已支持。

如下

一、开发特性

1 图式搜索

Oracle 18c

SQL-Based Property Graph Query and Analytics

PostgreSQL 8.4+ (2009 year+)

1、2009年PostgreSQL社区版本8.4，加入CTE递归语法的支持，支持图式搜索。

2、2017年3月，基于PostgreSQL的专业图数据库AgensGraph发布1.0，兼容标准SQL和图式搜索语法openCypher。

AgensGraph supports ANSI-SQL and openCypher ().

2 JSON 增强

Oracle 18c

SQL Enhancements for JSON

支持行转JSON。

PostgreSQL 9.2+ (2012 year+)

2012年PostgreSQL社区版本9.2，加入JSON的支持。

9.2已支持行转JSON

row_to_json(record [, pretty_bool])

除此之外，PostgreSQL还支持嵌套JSON，K-V转JSON，K-V类型。支持数组类型，LTREE，RANGE，XML，TS，IP 等类型，对开发更加的友好。

3 空间数据sharding

Oracle 18c

支持空间数据字段作为SHARDING字段

PostgreSQL 8.2+ (2007 year+)

1、早在2008年以前，就可以使用plproxy来实现PostgreSQL sharding，在skype内核心数据库用的就是plproxy做的sharding。sharding算法完全由用户控制，不管是空间数据还是什么数据，都可以支持。

2、除了plproxy分库分表以外，PostgreSQL社区还有一些NewSQL或MPP架构的数据库或插件，例如postgres-xl, antdb, citusDB, Greenplum：

3、2016年发布的PostgreSQL 9.6与pg_pathman插件，支持外部表继承，同时支持外部表的查询下推，支持表达式分区。这些特性决定了用户可以使用PostgreSQL 9.6+, pg_pathman, postgis(st_geohash)来实现空间字段的sharding。

4 Text: Automatic Background Index Maintenance

Oracle 18c

后台自动维护文本索引。

PostgreSQL 8.2+ (2007 year+)

PostgreSQL 从8.2 (2007年)开始，支持全文检索，全文检索索引。索引自动维护，不需要人工干预。

PostgreSQL GIN索引不仅可用于全文检索字段，同时还支持其他多值类型，实时build+pending list(autovacuum)，可调整pending list size合并写入。写入与查询互不堵塞，兼顾了写性能和读性能。

实际上PostgreSQL的设计理念是开放接口，所以索引接口也是开放的，因此我们看到PostgreSQL已支持的索引接口就达9种之多（hash,btree,gin,gist,spgist,brin,bloom,rum,zombdb），覆盖普通类型，多值类型，空间类型，时序数据，多字段任意组合搜索，全文检索，时空搜索等。

5 Text: Wildcard Search

Oracle 18c

支持通配符匹配。

PostgreSQL 8.3+ (2008 year+)

严格来说，PostgreSQL 8.2以前的版本已经支持了通配符搜索，(支持正则表达式)。

从8.3 (2008年)开始，支持pg_trgm，因此能够支持通配符搜索的索引检索加速。目前Oracle还做不到模糊查询、正则查询、全文检索的索引加速。PostgreSQL可以做到。

6 Text: Concurrent DML Support

Oracle 18c

PostgreSQL 8.2+ (2007 year+)

PostgreSQL 从8.2 (2007年)开始，支持全文检索，全文检索索引。索引自动维护，不需要人工干预。

PostgreSQL GIN索引不仅可用于全文检索字段，同时还支持其他多值类型，实时build+pending list(autovacuum)，可调整pending list size合并写入。写入与查询互不堵塞，兼顾了写性能和读性能。

二、sharding

1 User-Defined Sharding Method

Oracle 18c

PostgreSQL 8.2+ (2007 year+)

1、早在2008年以前，就可以使用plproxy来实现PostgreSQL sharding，在skype内核心数据库用的就是plproxy做的sharding。sharding算法完全由用户控制，不管是空间数据还是什么数据，都可以支持。

三、Big Data and Data Warehousing

1 Analytic View FILTER FACT and ADD MEASURE Keywords

聚合前，对输入聚合函数的数据进行过滤，也就是说支持基于条件聚合。并不是WHERE条件，请注意。

Analytic views provide a fast and efficient way to create analytic queries of data stored in existing database tables and views.

Analytic views organize data using a dimensional model. They allow you to easily add aggregations and calculations to data sets and to present data in views that can be queried with relatively simple SQL.

Oracle 18c

PostgreSQL 9.4+ (2014 year+)

aggregate_name (expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]  aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]  aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]  aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ]  aggregate_name ( [ expression [ , ... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ]

除了FILTER，PostgreSQL聚合还支持ORDER BY，即输入到聚合函数中的记录可以按顺序进入，从而影响聚合结果。

PostgreSQL 将开放存储接口、压缩接口，意味着以分析视图为代表的维度组织形式存储将来可以支持。

2 Data Mining

Oracle 18c

Exponential Smoothing

Random Forest

Neural Network

Explicit Semantic Analysis extension to classification

PostgreSQL 8.4+ (2009 year+)

PostgreSQL 支持语言接口扩展，例如支持 plpython 存储过程语言。因此社区提供了madlib插件，可以用于在PostgreSQL内部使用plpython函数接口实现机器学习。

1、madlib

如果你用的是PostgreSQL 9.6+，则支持并行计算，性能更加。而如果你用的是greenplum（基于PostgreSQL的MPP数据库）那么就可以支持多机并行的机器学习。

2、可视化分析软件orange3，同样支持python扩展

3、流式计算pipeline ，支持流式计算。阅后即焚或insert on conflict实现实时或批量计算。

4、估值计算hll插件，实现估值计算

四、General

1 Manual termination of run-away queries

Oracle 18c

A SQL query consuming excessive resources, including parallel servers, can be terminated using the ALTER SYSTEM CANCEL SQL statement.

This feature enables the database administrator to terminate a runaway SQL query using a SQL statement. This frees the system resources consumed by the runaway SQL query so that other SQL queries can use them.

PostgreSQL 8.0+ (2005 year+)

Name	Return Type	Description
pg_cancel_backend(pid int)	boolean	Cancel a backend's current query
pg_terminate_backend(pid int)	boolean	Terminate a backend

重点是runaway sql的检查。这个功能可以结合OS来判定。因为PG是基于进程的模型，只要看进程的资源消耗和统计就可以。可以用cgroup进行统计。

2 Inline External Tables

Oracle 18c

PostgreSQL 8.4+ (2009 year+)

PostgreSQL 从8.4开始支持FDW接口，即foreign data wrapper。对目标数据源实现这个接口，就可以访问该数据源。作为PostgreSQL的一张外表来使用。因此在PostgreSQL中访问Oracle的数据是非常轻松的。

目前可访问的数据源不完全列表如下，包括 “普通数据库，NOSQL，文件，网络服务，大数据服务，搜索引擎，操作系统，对象存储，空间数据库” 等，只要有网络接口的数据源，都可以访问。

• Generic SQL Database Wrappers

• Specific SQL Database Wrappers

• NoSQL Database Wrappers

• File Wrappers

• Geo Wrappers

• LDAP Wrappers

• Generic Web Wrappers

• Specific Web Wrappers

• Big Data Wrappers

• Column-Oriented Wrappers

• Scientific Wrappers

• Operating System Wrappers

• Exotic Wrappers

• RDS PG OSS 外部表文档：

• HDB PG OSS 外部表文档：

3 Approximate Top-N Query Processing

Oracle 18c

PostgreSQL 9.0+ (2010 year+)

使用hyperloglog插件，PostgreSQL可以实现概率计算，包括count distinct的概率计算。

五、Container Database Architecture

Container Database Architecture

Oracle 18c

PostgreSQL 8.0+ (2005 year+)

PostgreSQL设计之初就是PDB模式，即一个实例，多个数据库。围绕数据库，支持多种操作，如：

1、克隆数据库

CREATE DATABASE name      [ [ WITH ] [ OWNER [=] user_name ]             [ TEMPLATE [=] template ]             [ ENCODING [=] encoding ]             [ LC_COLLATE [=] lc_collate ]             [ LC_CTYPE [=] lc_ctype ]             [ TABLESPACE [=] tablespace_name ]             [ ALLOW_CONNECTIONS [=] allowconn ]             [ CONNECTION LIMIT [=] connlimit ]             [ IS_TEMPLATE [=] istemplate ] ]

例如

create database new_db with template old_db;

2、DUMP 数据库

pg_dump。     采用repeatable read隔离级别导出，库级别一致。

3、IMPORT 数据库

pg_restore

4、快照功能，(9.2+ )

pg_export_snapshot()，支持导出当前快照。

5、订阅数据库，(10+ )

通过订阅功能，支持数据库、表级别的逻辑订阅。

6、内核层面理论是也可以实现针对库级的WAL。

六、Globalization

PostgreSQL 10+ (2017 year+)

支持ICU库

The ICU library has versioning that allows detection of collation changes between versions.

It is enabled via configure option --with-icu. The default still uses the operating system's native collation library.

七、分区

1 Parallel Partition-Wise SQL Operations

Oracle 18c

PostgreSQL 11+ (2018 year+)

PostgreSQL 11支持分区表的智能JOIN。

在PostgreSQL 11前，通过dblink异步调用，可以实现同样功能，不过需要用户自己关注分区定义是否一致。

2 Online Merging of Partitions and Subpartitions

Oracle 18c

The ONLINE keyword is specified to enable concurrent DML operations while a partition maintenance operation is ongoing.

PostgreSQL 9.5+ (2015 year+)

pg_pathman ，通过这个插件，可以实现高效的分区管理。

自动或手动分区管理  Both automatic and manual partition management;    自动新增分区，无需人工干预  Automatic partition creation for new INSERTed data (only for RANGE partitioning);    支持字段或表达式分区  Partitioning by expression and composite key;    将单表转换为分区表时，不堵塞DML  Non-blocking concurrent table partitioning;    partition_table_concurrently(relation   REGCLASS,                               batch_size INTEGER DEFAULT 1000,                               sleep_time FLOAT8 DEFAULT 1.0)    分区表执行计划自动根据分区定义进行优化  Effective query planning for partitioned tables (JOINs, subselects etc);    分区表支持外部数据源  FDW support (foreign partitions);

合并和分裂分区接口例子

merge_range_partitions(partitions REGCLASS[])    split_range_partition(partition      REGCLASS,                        split_value    ANYELEMENT,                        partition_name TEXT DEFAULT NULL)

如果是PostgreSQL 10+，则可以使用SQL语法来绑定和解绑分区，结合订阅，DDL事务功能，可以实现在线合并、分裂分区。

逻辑订阅, ddl 支持事务, detach, attach

3 Modifying the Partitioning Strategy

PostgreSQL 10+ (2018 year+)

通过逻辑订阅来实现，分区定义的修改，不堵塞dml

1、db2创建新表    2、db1创建发布    3、db1将旧表添加到发布    4、db2创建订阅    5、db2创建发布    6、db2将新表添加到发布    7、db1创建新表    8、db1创建订阅    9、db1切换新、旧表    （目前可能需要借助一个临时库绕一圈，同一数据库中无法订阅）

八、安全

1 Integration of Active Directory Services with Oracle Database

Oracle 18c

PostgreSQL 8.2+ (2007 year+)

PostgreSQL 8.2即引入了LDAP的认证。

除了LDAP，PostgreSQL还支持多种认证接口：

20.3.1. Trust Authentication    20.3.2. Password Authentication    20.3.3. GSSAPI Authentication    20.3.4. SSPI Authentication    20.3.5. Ident Authentication    20.3.6. Peer Authentication    20.3.7. LDAP Authentication    20.3.8. RADIUS Authentication    20.3.9. Certificate Authentication    20.3.10. PAM Authentication    20.3.11. BSD Authentication

小结

PostgreSQL是一个全栈数据库，不输于现在的商用数据库，有非常弹性的许可协议(like BSD)，有非常广泛的行业群众基础，生态健全。

下面是一篇较为全面的PostgreSQL数据库介绍：

参考