PostgreSQL技术进阶 + 关注
手机版

PostgreSQL 11 小记

  1. 云栖社区>
  2. PostgreSQL技术进阶>
  3. 博客>
  4. 正文

PostgreSQL 11 小记

东明 2018-11-20 11:32:35 浏览1491 评论1

摘要: ## 关于 PostgreSQL [PostgreSQL](https://en.wikipedia.org/wiki/PostgreSQL) 是世界上最先进的开源数据库。 PostgreSQL 最早可追溯到 1973 年,当时加州大学伯克利分校的两位科学家,[Michael Stonebraker](https://en.

关于 PostgreSQL

PostgreSQL 是世界上最先进的开源数据库。

PostgreSQL 最早可追溯到 1973 年,当时加州大学伯克利分校的两位科学家,Michael Stonebraker 和 Eugene Wong 受 1970 年 Edgar F. Codd 发表的论文 A Relational Model of Data for Large Shared Data Banks 以及 IBM 开发的关系数据库系统 System R 的启发,开发了一个研究性质的关系数据库系统 Ingres),该项目后续衍生出了众多现在依然耳熟能详的数据库系统,如 Sybase,微软的 SQL Server 等。

PostgreSQL 并没有直接继承自 Ingres,而是 1985 年 Michael Stonebraker 为解决当时数据库系统遇到的各种问题而重新开发的,借鉴了 Ingres 的思想,使用 POSTQUEL 作为其查询语言,命名为 POSTGRES(即 post-Ingres)。1994 年,伯克利大学的两位研究生 Andrew Yu 和 Jolly Chen 将 POSTQUEL 替换为 SQL 查询语言,将其命名为 Postgres95,将其前端交互式控制台命名为 psql。1995 年 5 月 5 日,发布第一版(0.01),同年 9 月 5 日发布 1.0 版本。

1996 年 7 月 8 日,PostgreSQL 有了第一个非大学的开发服务器用于开源开发,Bruce Momjian 和 Vadim B. Mikheev 加入,这一天也是 PostgreSQL 的生日[1]。同年,将其重命名为 PostgreSQL,以体现其支持 SQL。PostgreSQL 官网 PostgreSQL.org 与同年 10 月 22 日开始运营。PostgreSQL 的第一个版本于 1997 年 1 月 29 号发布,版本号是 6.0

PostgreSQL 以 PostgreSQL License 开源,如今已经吸引了全球众多用户、贡献者、公司和组织参与到开源社区中,保持几乎每年发布一个大版本的节奏向前推进。

PostgreSQL 11

以上简单介绍 PostgreSQL 的历史,有兴趣的同学可以查阅相关文献,几乎贯穿整个关系数据库系统的发展史。言归正传,2018 年 10 月 18 日 PostgreSQL 发布了 11 版本,该版本的主要特性如下:

  • 提升分区功能

    • 支持 HASH 分区,目前已经支持 RANGE,LIST 和 HASH 三种分区方式
    • 分区表支持主键,外键, 索引和触发器
    • 为不符合任何分区的数据提供 default 分区
    • UPDATE 分区键可以将其移动至合适的分区
    • 在计划和执行阶段,增强分区裁剪策略,提升 SELECT 性能
  • 提升并行能力

    • 并行创建 B-tree 索引
    • CREATE TABLE ... AS, CREATE MATERIALIZED VIEW 和特定的 UNION 查询支持并行
    • 优化并行 HASH JOIN 和 并行顺序扫描
  • 存储过程中支持嵌入事务
  • 可选的 Just-in-Time (JIT) 编译选项,提升表达式计算性能
  • 支持 SQL:2011 标准中窗口函数对应的所有 window frame 选项
  • 覆盖索引可以使用 INCLUDE 子句包含其他的非索引字段

从分区表和并能能力的增强可以看出,PostgreSQL 11 对于大数据量下的数据管理和查询能力都在提升,在存储空间不成为瓶颈的情况下,PostgreSQL 正努力将单机性能发挥到极致。

分区表

分区与 sharding

PostgreSQL 的分区与为解决传统数据库系统扩展问题的 sharding 方案是很相似的,区别在于前者将一张逻辑表划分为多个物理表存储在当前实例上,后者将多个物理表分布在不同的实例上;两者均通过水平切分将大表拆成小表,通过该方法,前者突破单表的大小限制,后者突破单实例的大小限制。

其实,借助 PostgreSQL 的分区能力以及 postgres_fdw 能力,完全可以将分区表中不同的分区存储在不同的外部表中,这些外部表可以是 PostgreSQL,也可以是 MySQL,甚至其他数据库类型。不妨来看一下在 PostgreSQL 中创建外部表的语法:

CREATE FOREIGN TABLE foreign_table (
        id integer NOT NULL,
        data text
)
        SERVER foreign_server
        OPTIONS (schema_name 'some_schema', table_name 'some_table');

以上语法如果可以支持指定多个 SERVER,建表语句支持 PARTITION BY,是否就实现了跨多实例的 sharding 方案呢?其实目前是支持手动配置外部分区表的,只是使用起来会比较繁琐,另外 pg_shardman 也做了同样的事情,如果 PostgreSQL 能从语法上直接支持使用起来就方便多了。

关于 PostgreSQL 内置 sharding 的信息可以参考这个wiki

另外分区表已经支持了默认分区,离支持自动扩展分区还远么?参考 Oracle 的 interval partition

分区表与性能

借助分区表和 postgres_fdw 可以实现表存储空间的扩展,但如果单机性能达到瓶颈,大数据量下的复杂查询依然很难满足企业用户 OLAP 的场景。因此,PostgreSQL 在性能方面做了很多努力。

并行 JOIN

两个分区表 JOIN,如果满足分区方式相同,分区数量相同,并且 JOIN 条件是分区键的等值连接等条件,则可以对每个分区分别做 JOIN,然后 UNION(对应计划树节点为 APPEND) 在一起,而每个分区的 JOIN 是可以并行执行的。可以参考德哥的分区表智能并行JOIN,性能提升约 2.4 倍。

image.png

并行聚合

与并行 JOIN 类似,在每个分区上分别做局部聚合(可并行),最后再做一次整体聚合,可以参考德哥的分区表智能并行聚合、分组计算

postgres_fdw 和 下推

分区表和外部表下推优化有所增强,可以将更多的操作下推至外部表执行:

  • INSERT, UPDATE, 和 COPY 操作可以下推至外部表执行。
  • 有 JOIN 的 UPDATE 和 DELETE 可以下推至外部表执行,之前仅能下推没有 JOIN 的 UPDATE 和 DELETE。
  • 可以下推聚合操作至外部表执行。

性能增强

性能增强方面,除以上提到的下推优化之外,在并行方面也做了增强:

  • 部分 DDL 支持并行,如创建索引,CREATE TABLE .. AS, SELECT INTO, and CREATE MATERIALIZED VIEW,并行排序和排序可以参考德哥并行排序、并行索引的测试文章。
  • 允许 LIMIT 传递到 Parallel worker 上执行,进而减少返回的行数。
  • HASH JOIN 支持共享哈希表,之前每个 Parallel worker 有自己的一个哈希表副本。

值得一提的是,PostgreSQL 11 中增加了对 JIT 的支持,目前仅支持表达式计算,对于有复杂表达式计算的 OLAP 场景应该会有不小的性能提升。

用户体验

用户体验方面,有一些比较有意思的点:

  • psql 可以使用 exitquit 退出了,有多少用户第一次使用 psql 时不知道如何退出?与 MySQL 相比,PostgreSQL 还有很多命令不是很直观的,这个后续可以整理下常用命令的对应关系。
  • 执行 ALTER TABLE .. ADD COLUMN .. DEFAULT .. 使用常量(非 NULL)作为默认值时,不用在执行的时候重写整张表了,具体实现方法待学习验证。

总结

本文简单记录 PostgreSQL 11 中个人比较关注的点。总体来讲,PostgreSQL 在分区表的支持和性能的提升上都给我很大的惊喜,非常期待 PostgreSQL 在 HTAP 场景中的应用,后续会继续探索具体的实现机制。

PostgreSQL 最新的 feature 和 patch 可以在 commitfestgit 上查看,也可以订阅邮件列表来了解社区同仁的动态或者提出问题,参与到社区中;更多的咨询可以到官网查阅。

更多 PostgreSQL 11 的特性可以参考官网 release notes 和德哥的 PostgreSQL 11 preview 系列 文章(搜索 PostgreSQL 11 preview)。

Reference

  1. Happy Birthday, PostgreSQL!
  2. A Brief History of PostgreSQL
  3. https://www.postgresql.org/docs/11/static/release-11.html
  4. https://www.postgresql.org/about/news/1894/
  5. https://en.wikipedia.org/wiki/PostgreSQL
  6. https://en.wikipedia.org/wiki/Ingres_(database)
【云栖快讯】阿里巴巴小程序繁星计划,20亿补贴第一弹云应用免费申请,限量从速!  详情请点击

网友评论