下载掌阅APP，畅读海量书库
立即打开
本节主要介绍PostgreSQL数据库与主流数据库MySQL和Oracle的相同点和区别。
可能有人会问，既然已经有一个人气很高的开源数据库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，会让你的工作变得简洁和高效。
从功能上说，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在分钟级别就可以完成安装。