|什么是PostgreSQL？比MySQL、Oracle强在哪

导读：本文主要给大家介绍什么是PostgreSQL数据库、PostgreSQL有哪些强大的功能及其目前的一些应用情况，以便大家对PostgreSQL有一个初步的认识。
作者：唐成
来源：华章科技

本文插图
01 什么是PostgreSQL
PostgreSQL数据库是功能强大的开源数据库，它支持丰富的数据类型（如JSON和JSONB类型、数组类型）和自定义类型。
PostgreSQL数据库提供了丰富的接口，可以很方便地扩展它的功能，如可以在GiST框架下实现自己的索引类型，支持使用C语言写自定义函数、触发器，也支持使用流行的编程语言写自定义函数。
PL/Perl提供了使用Perl语言写自定义函数的功能，当然还有PL/Python、PL/Java、PL/Tcl等。
02 PostgreSQL数据库的优势
PostgreSQL数据库具有以下优势：

PostgreSQL数据库是目前功能最强大的开源数据库，它是最接近工业标准SQL92的查询语言，至少实现了SQL:2011标准中要求的179项主要功能中的160项（注：目前没有哪个数据库管理系统能完全实现SQL:2011标准中的所有主要功能）。
稳定可靠：PostgreSQL是唯一能做到数据零丢失的开源数据库。目前有报道称国内外有部分银行使用PostgreSQL数据库。
开源省钱： PostgreSQL数据库是开源的、免费的，而且使用的是类BSD协议，在使用和二次开发上基本没有限制。
支持广泛：PostgreSQL 数据库支持大量的主流开发语言，包括C、C++、Perl、Python、Java、Tcl以及PHP等。
PostgreSQL社区活跃：PostgreSQL基本上每3个月推出一个补丁版本，这意味着已知的Bug很快会被修复，有应用场景的需求也会及时得到响应。

本文插图
03 PostgreSQL应用现状和发展趋势
PostgreSQL目前在国外很流行，特别是近几年，使用PostgreSQL数据库的公司越来越多。
日本电报电话公司（NTT）大量使用PostgreSQL替代Oracle数据库，并且在 PostgreSQL之上二次开发了Postgres-XC ， Postgres-XC是对使用者完全兼容PostgreSQL接口的share-nothing 架构的数据库集群。
亚信科技（AsiaInfo）在Postgres-XC的基础上开发了AntDB数据库， AntDB是一款面向金融、电信、政务、安全、能源等行业的分布式事务型关系数据库产品。它具备集群自动高可用、秒级在线扩缩容、异地容灾、SQL语句级自定义分片、分布式事务和MVCC等功能，且具有强大的Oracle兼容性。 AntDB完全兼容PostgreSQL数据库。
腾讯在PosgreSQL-XC基础上开发了TBase分布式数据库。相较于Postgres-XC ，其稳定性得到了较大提高，同时TBase通过在内核中创造性地引入 GROUP 概念，提出了双Key分布策略，有效地解决了数据倾斜的问题；它根据数据的时间戳，将数据分为冷数据和热数据，分别存储于不同的存储设备中，有效地解决了存储成本的问题。
网络电话公司Skype也大量使用了PostgreSQL ，并贡献了如下与PostgreSQL数据库配套的开源软件。

PL/Proxy：PostgreSQL中的数据水平拆分软件。
pgQ：使用PostgreSQL的消息队列软件。
Londiste：用C语言实现的在PostgreSQL数据库之间进行逻辑同步的软件。

全球最大的CRM软件服务提供商Salesforce也开始使用PostgreSQL ，并招募了PostgreSQL内核开发者Tom lane 。
著名的图片分享网站Instagram也大量使用了PostgreSQL 。
2012年，美国联邦机构全面转向PostgreSQL阵营；法国也正积极推动政府机构采用PostgreSQL数据库来取代商业数据库。分页标题
在国内，越来越多的公司开始使用PostgreSQL ，如斯凯网络的后台数据库使用的基本都是PostgreSQL数据库，去哪儿网（qunar.com）和平安科技也大量地使用了PostgreSQL数据库。
主流的云服务提供商如亚马逊、阿里云、腾讯云、华为云也都提供了PostgreSQL的云数据库服务。
更多关于PostgreSQL数据库的现状信息可见PostgreSQL官方网站：
http://www.postgresql.org/
04 PostgreSQL数据库与其他数据库的对比
本节主要介绍PostgreSQL数据库与主流数据库MySQL和Oracle的相同点和区别。
1. PostgreSQL与MySQL数据库的对比

本文插图
可能有人会问，既然已经有一个人气很高的开源数据库MySQL了，为什么还要使用PostgreSQL？这主要是因为不同的数据库有不同的特点，应该为合适场景选择合适的数据库。在一些应用场景中，使用MySQL有以下几大缺点。

复杂SQL支持弱

在MySQL 8.0之前，多表连接查询的方式只支持“Nest Loop” ，不支持Hash JOIN和Sort Merge JOIN（注：MySQL8.0版本开始支持Hash JOIN ，但不完善，有一些问题。
另因为MySQL无完善的基于COST的优化器（CBO），长期来说也会存在一定的问题），不仅如此，它对很多SQL语法都不支持，子查询性能比较低。例如， MySQL不支持单独的sequence ，有公司为此还专门开发了统一序号分发中心的软件。

性能优化工具与度量信息不足

如果MySQL在运行过程中出现问题，性能监控数据较少，维护人员要准确定位问题存在一定的困难。

MySQL的复制是异步或半同步的逻辑同步，这存在两个问题

一是在大事务下会导致比较大的延迟；二是容易导致数据库的不一致，原因是逻辑复制容易导致数据的不一致性，而MySQL的双层日志会让这个问题变得更复杂，即主备库的复制是通过逻辑层的binlog来实现的，但在存储引擎InnoDB下还有物理的Redo Log层，整个过程比较复杂，比较难保证主备库之间完全一致。
由于有两层日志（binlog日志和InnoDB的Redo日志），因此也很难做到Master/Slave在异常切换过程中的零数据丢失。一些第三方公司改造MySQL源代码以实现同步复制，但这些方案要么是没有开源，要么是已开源却又不是很稳定，所以，对于普通用户来说，如何实现零数据库丢失的同步复制是一个令人头疼的问题。

在线操作功能较弱

很多在线DDL需要重建表，代价很大，有一些操作还会锁表。一些大的互联网公司或者修改MySQL源码来实现在线DDL功能，或者通过上层架构来解决这个问题，如先在Slave数据库上把DDL做完，然后把应用从Master库切换到Slave库，再到原先的Master上把DDL做完。
对于第一种方法，需要公司有很强的MySQL研发能力，第二种方法则需要公司有较强的开发能力，能设计出较强的应用架构。这对于一些中小型公司来说不太容易实现。

难以写插件来扩展MySQL的功能

虽然用UDF ，或通过外部动态库中的函数来扩展部分功能，但能扩展的功能很有限。如MySQL比较难访问其他数据库中的数据。
相对MySQL的这些弱点， PostgreSQL有以下几个优点。

功能强大

支持所有主流的多表连接查询的方式，如“Nest loop”“Hash JOIN”“Sort Merge JOIN”等；支持绝大多数的SQL语法，如CTE（MySQL8.0之前不支持CTE）。
PostgreSQL是笔者见过的对正则表达式支持最强、内置函数也是最丰富的数据库。它的字段类型还支持数组类型。除了可以使用PL/PGSQL写存储过程外，还可以使用各种主流开发语言的语法（如Python语言的PL/Python、Perl语言的PL/Perl来写存储过程）。分页标题
这些强大的功能可以大大地节约开发资源。很多开发人员在PostgreSQL上做开发时，会发现数据库已实现很多功能，甚至有一些业务功能都不再需要写代码来实现了，直接使用数据库的功能即可解决问题。

性能优化工具与度量信息丰富

PostgreSQL数据库中有大量的性能视图，可以方便地定位问题（比如可以看到正在执行的SQL ，可以通过锁视图看到谁在等待，以及哪条记录被锁定等）。 PostgreSQL中设计了专门架构和进程用于收集性能数据，既有物理I/O方面的统计，也有表扫描及索引扫描方面的性能数据。

在线操作功能好

PostgreSQL增加空值列时，本质上只是在系统表上把列定义上，无须对物理结构做更新，这就让PostgreSQL在加列时可以做到瞬间完成。 PostgreSQL还支持在线建索引的功能，在创建索引的过程可以不锁更新操作。

从PostgreSQL9.1开始，支持同步复制（synchronous replication）功能，通过Master和Slave之间的复制可以实现零数据丢失的高可用方案。

可以方便地写插件来扩展PostgreSQL数据库的功能

PostgreSQL提供了安装、编写插件的整体框架，如提供了create extension等SQL语句以方便地装载插件；写一个动态库可以很方便地给PostgreSQL添加函数；提供了外部数据源（FDW）的框架和编程接口，根据此框架和编程接口可以方便地编写访问其他数据库和外部数据源的插件。
现在针对已有的常见外部数据源，如Oracle、MySQL、SQL Server等数据库都有了第三方插件，通过这些第三方插件可以在PostgreSQL数据库中方便地访问外部数据。另外， PostgreSQL还提供了钩子函数的接口，可以实现更强大功能的插件，如pg_pathman分区表的插件、citus分库分表的插件等。
另外，由于MySQL对SQL语法支持的功能较弱，基本上不适合做数据仓库。虽然也有些厂商开发了MySQL数据仓库的存储引擎（如Infobright），但这个方案只是解决了部分数据仓库的问题， SQL功能弱的问题还是无法完全解决。
而且Infobright的社区版本在功能上有很多限制，如不支持数据更新、不支持太多的并发执行（最多支持十几个）等。而PostgreSQL不仅支持复杂的SQL ，还支持大量的分析函数，非常适合做数据仓库。
PostgreSQL数据库中还有一些支持移动互联网的新功能，如空间索引。 PostGIS是最著名的一个开源GIS系统，它是PostgreSQL中的一个插件，在PostgreSQL中使用它很方便。通过PostGIS也可以很方便地解决LBS中的一些位置计算问题。
综上所述， PostgreSQL数据库是一个功能强大，又带有移动互联网特征的开源数据库。
如果你仅仅是想把数据库作为一个简单的存储软件（一些大的互联网公司就是这样），一些较复杂的功能都想放在应用中来实现，那么选择MySQL或一些NoSQL产品都是合适的。如果你应用的数据访问很简单（如大多数的博客系统），那么后端使用MySQL也是很合适的。
但是如果你的应用不像博客系统那么简单，又不想消耗太多的开发资源，那么PostgreSQL是一个很明智的选择。最有说服力的例子就是图片分享公司Instagram ，在使用“Python+PostgreSQL”架构后，只是十几个人就支撑了整个公司的业务。
在数据库中使用PostgreSQL的感觉就像在开发语言中使用Python ，会让你的工作变得简洁和高效。
2. PostgreSQL与Oracle数据库的对比

本文插图
从功能上说， PostgreSQL可以与Oracle数据库媲美。 Oracle数据库是目前功能最强大的商业数据库， PostgreSQL则是功能最强大的开源数据库。 Oracle在集群功能如RAC、ASM方面比较强，但PostgtreSQL也有一些比Oracle强的特性，如在索引和可扩展等方面。分页标题
PostgreSQL与Oracle有很多相似之处，它们都是使用共享内存的进程结构，客户端与数据库服务器建立一个连接后，数据库服务器就启动一个进程来为这个连接服务。这与MySQL的线程模型不一样。
PostgreSQL与Oracle一样， PostgreSQL的WAL日志与Oracle的Redo日志都是用于记录物理块数据的变化的，这与MySQL的binlog是不一样的。
PostgreSQL在主备库方面非常完善，可以搭建同步备库、异步备库、延迟备库，在同步备库中可以配置数据同步到任意个备库上。只读备库在查询与应用日志的冲突解决方面提供了更多的参数控制，让DBA更容易控制只读备库的查询冲突。在配置备库的过程中， PostgreSQL比Oracle简单很多，备库的搭建也更灵活。
PostgreSQL与Oracle的不同之处在于， PostgreSQL有更多支持互联网特征的功能。如PostgreSQL数据类型支持网络地址类型、XML类型、JSON类型、UUID类型以及数组类型，且有强大的正则表达式函数，如where条件中可以使用正则表达式匹配，也可以使用Python、Perl等语言写存储过程等。
另外， PostgreSQL更小巧。 PostgreSQL可以在内存很小的机器上完美运行起来，如在512MB的云主机中，而Oracle数据库基本要在数GB的云主机中才可以运行起来。
Oracle安装包动辄几个GB以上级别，而PostgreSQL的安装包只有几十MB大小。 PostgreSQL在任何一个环境都可以轻松地安装。 Oracle数据库安装花费的时间是在小时级别，而PostgreSQL在分钟级别就可以完成安装。
关于作者：唐成，拥有20多年的数据库、操作系统、存储领域的工作经验。目前担任中启乘数科技CTO ，从事高性能、高可靠、高可用的分布式数据库的研究工作以及数据库内核的开发工作。历任过阿里巴巴的高级数据库专家和网易杭州研究院开发专家。
本文摘编自《PostgreSQL修炼之道：从小工到专家》（第2版），经出版方授权发布。

本文插图
延伸阅读《PostgreSQL修炼之道：从小工到专家》
【|什么是PostgreSQL？比MySQL、Oracle强在哪】推荐语：PostgreSQL中国社区数据库专家撰写， PostgreSQL数据库领域经典著作，第2版全面升级。