PostgreSQL 与 MySQL 的对比优劣大集合

谁践踏了优雅 2022-11-12 11:52 460阅读 0赞

PostgreSQL的Slogan是“**世界上最先进的开源关系型数据库**”

它是一款**一专多长的全栈数据库：在可观的规模内，都能做到一招鲜吃遍天**。

成熟的应用可能会用到许许多多的数据组件（功能）：缓存，OLTP，OLAP/批处理/数据仓库，流处理/消息队列，搜索索引，NoSQL/文档数据库，地理数据库，空间数据库，时序数据库，图数据库。传统架构选型可能会组合使用多种组件，典型的如：Redis + MySQL + Greenplum/Hadoop + Kafuka/Flink + ElasticSearch。在这里MySQL只能扮演OLTP关系型数据库的角色，但如果是PostgreSQL，就可以身兼多职，**One hold them all**，比如：

**OLTP**：事务处理是PostgreSQL的本行

**OLAP**：citus分布式插件，ANSI SQL兼容，窗口函数，CTE，CUBE等高级分析功能，任意语言写UDF

**流处理**：PipelineDB扩展，Notify-Listen，物化视图，规则系统，灵活的存储过程与函数编写

**时序数据**：timescaledb时序数据库插件，分区表，BRIN索引

**空间数据**：PostGIS扩展（杀手锏），内建的几何类型支持，GiST索引。

**搜索索引**：全文搜索索引足以应对简单场景；丰富的索引类型，支持函数索引，条件索引

**NoSQL**：JSON，JSONB，XML，HStore原生支持，至NoSQL数据库的外部数据包装器

**数据仓库**：能平滑迁移至同属Pg生态的GreenPlum，DeepGreen，HAWK等，使用FDW进行ETL

**图数据**：递归查询

**缓存**：物化视图

![30646e50266210a5adad723efbbf6d07.png][]

PostgreSQL知名扩展

在探探的实践中，整个系统就是围绕PostgreSQL设计的。几百万日活，几百万全局DB-TPS，几百TB数据的量级下，数据组件只用了PostgreSQL。直到接近千万日活，才开始进行架构调整引入独立的数仓，消息队列和缓存。这只是验证过的规模量级，进一步压榨PG是完全可行的。

![9efb651bcf459282ae26436dceba6ced.png][]

围绕PostgreSQL的架构演进

因此在一个很可观的规模内，PostgreSQL都可以扮演多面手的角色，一个组件当多种组件使。**虽然在某些领域它可能比不上专用组件**，至少都做的都还不赖。**而单一数据组件选型可以极大地削减项目额外复杂度，这意味着能节省很多成本。它让十个人才能搞定的事，变成一个人就能搞定的事。**

为了不需要的规模而设计完全是白费功夫，实际上这属于过早优化的一种形式。只有当没有单个软件能满足你的所有需求时，才会存在分拆和集成的利弊权衡。集成多种异构技术是相当棘手的工作，如果真有那么一样技术可以满足你所有的需求，那么使用该技术就是最佳选择，而不应试图去集成多个组件来重新实现它。

当业务规模增长到一定量级时，可能最终还是不得不使用基于微服务/总线的架构，将这些功能分拆为多个组件。但PostgreSQL的存在极大地推后了这个权衡到来的阈值，而且在分拆之后依然能继续发挥重要的作用。

当然除了功能强大之外，Pg的另外一个重要的优势就是**运维友好**。有很多非常实用的特性：

*  DDL能放入事务中，删表，TRUNCATE，创建函数，索引，都可以放在事务里原子生效，或者回滚。  
    这就能进行很多骚操作，比如在一个事务里通过RENAME，完成两张表的王车易位。
 *  能够并发地创建或删除索引（不锁表）；为表添加新的空字段不锁表，瞬间完成。  
    这意味着可以随时在线上按需添加移除索引，添加字段，不影响业务。
 *  复制方式多样：段复制，流复制，触发器复制，逻辑复制，插件复制，多种复制方法。  
    丰富的复制支持使得不停服务迁移数据变得无比容易。
 *  提交方式多样：异步提交，同步提交，法定人数同步提交。
 *  FDW的存在让ETL变得无比简单，一行SQL就能解决。
 *  系统视图非常完备，做监控系统相当简单。

除了运维之外，Pg还有一个巨大的优势就是**协议友好**，类BSD/MIT的协议。君不见多少国产数据库，BAT云数据库都是Pg的换皮或二次开发产品。

相比之下，MySQL社区版是GPL协议，要不是GPL传染，怎么这么多基于MySQL改的数据库都开源了。而且MySQL还捏在乌龟壳手里，React改协议的风波算是一个前车之鉴。

当然，要说PG有什么缺点或者遗憾，那还是有几个的，不过也无伤大雅：

*  因为使用了MVCC，数据库需要定期VACUUM，有额外的维护工作。
 *  没有`pt-xxx`那么成熟的MySQL工具脚本。
 *  没有很好的开源**集群**监控方案，需要自己做。
 *  慢查询日志和普通日志是混在一起的，需要自己解析处理。
 *  官方Pg没有很好用的列存储，对数据分析而言算一个小遗憾。

还有一个劣势：MySQL确实是**最流行**的开源关系型数据库，所以Pg招人相对困难。很多时候只好自己培养。不过看DB Engines上的流行度趋势，未来还是很光明的。

![5e74f50bb05adc59f54046be8813bf6c.png][]

DB-Engine Trend Top4, Top3都在走下坡路

我自己比较选型过MySQL和PostgreSQL，难得地在阿里这种MySQL和Java的世界中有过选择的自由。我认为只要有条件**自由选择**，没有任何理由不选PostgreSQL。扛着阻力把PG（还有Go之于Java）用了起来，推了起来。我用它做过很多项目，解决了很多需求（小到算统计报表，大到创收一个小目标）。大多数需求PG单挑就搞定了，极少数可能会再用点NoSQL，比如Redis，Cassandra/HBase。最后实在是对Pg爱不释手，以至于专职去研究PG了。

--------------------

用好Pg也有一个立竿见影的优势：对于很多需求来说，一个人能顶一个小团队，全栈工程师就该用全栈数据库嘛：

*  后端懒得写怎么办，[PostGraphQL][]直接从数据库模式定义生成GraphQL API，自动监听DDL变更，生成相应的CRUD方法与存储过程包装，对于后台开发再方便不过，类似的工具还有PostgREST与pgrest。对于中小数据量的应用都还堪用，省了一大半后端开发的活。
 *  需要用到Redis的功能，直接上Pg，模拟普通功能不在话下，缓存也省了。Pub/Sub使用Notify/Listen/Trigger实现，用来广播配置变更，做一些控制非常方便。
 *  需要做分析，窗口函数，复杂JOIN，CUBE，GROUPING，自定义聚合，自定义语言，爽到飞起。如果觉得规模大了想scale out可以上[citus][]扩展（或者换greenplum）；比起数仓可能少个列存比较遗憾，但其他该有的都有了。
 *  用到地理相关的功能，PostGIS堪称神器，千行代码才能实现的复杂地理需求，[一行SQL轻松高效解决][SQL]。
 *  存储时序数据，[timescaledb][]扩展虽然比不上专用时序数据库，但百万记录每秒的入库速率还是有的。用它解决过硬件传感器日志存储，监控系统Metrics存储的需求。
 *  一些流计算的相关功能，可以用[PipelineDB][]直接定义流式视图实现：UV，PV，用户画像实时呈现。物化视图也很好使。
 *  PostgreSQL的[FDW][]是一种强大的机制，允许接入各种各样的数据源，以统一的SQL接口访问。它妙用无穷：
 *  file\_fdw这种自带的扩展，可以将任意程序的输出接入数据表。最简单的应用就是[监控系统信息][Link 1]。
 *  管理多个PostgreSQL实例时，可以在一个元数据库中用自带的postgres\_fdw导入所有远程数据库的数据字典。统一访问所有数据库实例的元数据，一行SQL拉取所有数据库的实时指标，监控系统做起来不要太爽。
 *  之前做过的一件事就是用[hbase\_fdw][PipelineDB]和MongoFDW，将HBase中的历史批量数据，MongoDB中的当日实时数据包装为PostgreSQL数据表，一个视图就简简单单地实现了融合批处理与流处理的Lambda架构。
 *  使用redis\_fdw进行缓存更新推送；使用mongo\_fdw完成从mongo到pg的数据迁移；使用mysql\_fdw读取MySQL数据并存入数仓；实现跨数据库，甚至跨数据组件的JOIN；使用一行SQL就能完成原本多少行代码才能实现的复杂ETL，这是一件多么美妙的事情。
 *  各种丰富的类型与方法支持：例如[JSON][]，从数据库直接生成前端所需的JSON响应，轻松而惬意。范围类型，优雅地解决很多原本需要程序处理的边角情况。其他的例如数组，多维数组，自定义类型，枚举，网络地址，UUID，ISBN。很多开箱即用的数据结构让程序员省去了多少造轮子的功夫。
 *  丰富的索引类型：通用的Btree索引；大幅优化顺序访问的Brin索引；等值查询的Hash索引；GIN倒排索引；GIST通用搜索树，高效支持地理查询，KNN查询；Bitmap同时利用多个独立索引；Bloom高效过滤索引；能大幅减小索引大小的条件索引；能优雅替代冗余字段的函数索引。而MySQL就只有那么可怜的几种索引。
 *  稳定可靠，正确高效。MVCC轻松实现快照隔离，MySQL的RR隔离等级实现[不完善][Link 2]，无法避免PMP与G-single异常。而且基于锁与回滚段的实现会有各种坑；PostgreSQL通过SSI能实现高性能的可序列化。
 *  复制强大：WAL段复制，流复制（v9出现，同步、半同步、异步），逻辑复制（v10出现：订阅/发布），触发器复制，第三方复制，各种复制一应俱全。
 *  运维友好：可以将DDL放在事务中执行（可回滚），创建索引不锁表，添加新列（不带默认值）不锁表，清理/备份不锁表。各种系统视图，监控功能都很完善。
 *  扩展众多、功能丰富、可定制程度极强。在PostgreSQL中可以使用任意的语言编写函数：Python，Go，Javascript，Java，Shell等等。与其说Pg是数据库，不如说它是一个开发平台。我试过很多没什么卵用但很好玩的东西：数据库**里**的爬虫/ [推荐系统][Link 3] / 神经网络 / Web服务器等等。还有着各种各样功能强悍或脑洞清奇的第三方插件：[https://pgxn.org][https_pgxn.org]。

学PostgreSQL是一件很有趣的事，它让我意识到数据库的功能远远不止增删改查。我学着SQL Server与MySQL迈进数据库的大门。但却是PostgreSQL真正向我展示了数据库的奇妙世界。

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#000000;">特性</td> 
 <td style="border-color:#000000;">MySQL</td> 
 <td style="border-color:#000000;">PostgreSQL</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">实例</td> 
 <td style="border-color:#000000;">通过执行 MySQL 命令（mysqld）启动实例。一个实例可以管理一个或多个数据库。一台服务器可以运行多个 mysqld 实例。一个实例管理器可以监视 mysqld 的各个实例。</td> 
 <td style="border-color:#000000;"> 通过执行 Postmaster 进程（pg_ctl）启动实例。一个实例可以管理一个或多个数据库，这些数据库组成一个集群。集群是磁盘上的一个区域，这个区域在安装时初始化并由一个目录组成，所有数据都存储在这个目录中。使用 initdb 创建第一个数据库。一台机器上可以启动多个实例。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">数据库</td> 
 <td style="border-color:#000000;">数据库是命名的对象集合，是与实例中的其他数据库分离的实体。一个 MySQL 实例中的所有数据库共享同一个系统编目。</td> 
 <td style="border-color:#000000;">数据库是命名的对象集合，每个数据库是与其他数据库分离的实体。每个数据库有自己的系统编目，但是所有数据库共享 pg_databases。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">数据缓冲区</td> 
 <td style="border-color:#000000;">通过&nbsp;innodb_buffer_pool_size&nbsp;配置参数设置数据缓冲区。这个参数是内存缓冲区的字节数，InnoDB 使用这个缓冲区来缓存表的数据和索引。在专用的数据库服务器上，这个参数最高可以设置为机器物理内存量的 80%。</td> 
 <td style="border-color:#000000;">Shared_buffers&nbsp;缓存。在默认情况下分配 64 个缓冲区。默认的块大小是 8K。可以通过设置 postgresql.conf 文件中的 shared_buffers 参数来更新缓冲区缓存。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">数据库连接</td> 
 <td style="border-color:#000000;">客户机使用 CONNECT 或 USE 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。</td> 
 <td style="border-color:#000000;">客户机使用 connect 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">身份验证</td> 
 <td style="border-color:#000000;">MySQL 在数据库级管理身份验证。 基本只支持密码认证。</td> 
 <td style="border-color:#000000;">PostgreSQL 支持丰富的认证方法：信任认证、口令认证、Kerberos 认证、基于 Ident 的认证、LDAP 认证、PAM 认证</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">加密</td> 
 <td style="border-color:#000000;">可以在表级指定密码来对数据进行加密。还可以使用 AES_ENCRYPT 和 AES_DECRYPT 函数对列数据进行加密和解密。可以通过 SSL 连接实现网络加密。</td> 
 <td style="border-color:#000000;">可以使用 pgcrypto 库中的函数对列进行加密/解密。可以通过 SSL 连接实现网络加密。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">审计</td> 
 <td style="border-color:#000000;">可以对 querylog 执行 grep。</td> 
 <td style="border-color:#000000;">可以在表上使用 PL/pgSQL 触发器来进行审计。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">查询解释</td> 
 <td style="border-color:#000000;">使用 EXPLAIN 命令查看查询的解释计划。</td> 
 <td style="border-color:#000000;">使用 EXPLAIN 命令查看查询的解释计划。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">备份、恢复和日志</td> 
 <td style="border-color:#000000;">InnoDB 使用写前（write-ahead）日志记录。支持在线和离线完全备份以及崩溃和事务恢复。需要第三方软件才能支持热备份。</td> 
 <td style="border-color:#000000;">在数据目录的一个子目录中维护写前日志。支持在线和离线完全备份以及崩溃、时间点和事务恢复。 可以支持热备份。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">JDBC 驱动程序</td> 
 <td style="border-color:#000000;">可以从&nbsp;<a href="http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0606khatri/index.html#Resources" rel="nofollow">参考资料</a>&nbsp;下载 JDBC 驱动程序。</td> 
 <td style="border-color:#000000;">可以从&nbsp;<a href="http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0606khatri/index.html#Resources" rel="nofollow">参考资料</a>&nbsp;下载 JDBC 驱动程序。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">表类型</td> 
 <td style="border-color:#000000;">取决于存储引擎。例如，NDB 存储引擎支持分区表，内存引擎支持内存表。</td> 
 <td style="border-color:#000000;">支持临时表、常规表以及范围和列表类型的分区表。不支持哈希分区表。 由于PostgreSQL的表分区是通过表继承和规则系统完成了，所以可以实现更复杂的分区方式。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">索引类型</td> 
 <td style="border-color:#000000;">取决于存储引擎。MyISAM：BTREE，InnoDB：BTREE。</td> 
 <td style="border-color:#000000;">支持 B-树、哈希、R-树和 Gist 索引。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">约束</td> 
 <td style="border-color:#000000;">支持主键、外键、惟一和非空约束。对检查约束进行解析，但是不强制实施。</td> 
 <td style="border-color:#000000;">支持主键、外键、惟一、非空和检查约束。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">存储过程和用户定义函数</td> 
 <td style="border-color:#000000;">支持 CREATE PROCEDURE 和 CREATE FUNCTION 语句。存储过程可以用 SQL 和 C++ 编写。用户定义函数可以用 SQL、C 和 C++ 编写。</td> 
 <td style="border-color:#000000;">没有单独的存储过程，都是通过函数实现的。用户定义函数可以用 PL/pgSQL（专用的过程语言）、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 编写。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">触发器</td> 
 <td style="border-color:#000000;">支持行前触发器、行后触发器和语句触发器，触发器语句用过程语言复合语句编写。</td> 
 <td style="border-color:#000000;">支持行前触发器、行后触发器和语句触发器，触发器过程用 C 编写。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">系统配置文件</td> 
 <td style="border-color:#000000;">my.conf</td> 
 <td style="border-color:#000000;">Postgresql.conf</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">数据库配置</td> 
 <td style="border-color:#000000;">my.conf</td> 
 <td style="border-color:#000000;">Postgresql.conf</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">客户机连接文件</td> 
 <td style="border-color:#000000;">my.conf</td> 
 <td style="border-color:#000000;">pg_hba.conf</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">XML 支持</td> 
 <td style="border-color:#000000;">有限的 XML 支持。</td> 
 <td style="border-color:#000000;">有限的 XML 支持。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">数据访问和管理服务器</td> 
 <td style="border-color:#000000;">OPTIMIZE TABLE&nbsp;—— 回收未使用的空间并消除数据文件的碎片 myisamchk -analyze&nbsp;—— 更新查询优化器所使用的统计数据（MyISAM 存储引擎） mysql&nbsp;—— 命令行工具 MySQL Administrator&nbsp;—— 客户机 GUI 工具</td> 
 <td style="border-color:#000000;">Vacuum&nbsp;—— 回收未使用的空间 Analyze&nbsp;—— 更新查询优化器所使用的统计数据 psql&nbsp;—— 命令行工具 pgAdmin&nbsp;—— 客户机 GUI 工具</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">并发控制</td> 
 <td style="border-color:#000000;">支持表级和行级锁。InnoDB 存储引擎支持 READ_COMMITTED、READ_UNCOMMITTED、REPEATABLE_READ 和 SERIALIZABLE。使用 SET TRANSACTION ISOLATION LEVEL 语句在事务级设置隔离级别。</td> 
 <td style="border-color:#000000;">支持表级和行级锁。支持的 ANSI 隔离级别是 Read Committed（默认 —— 能看到查询启动时数据库的快照）和 Serialization（与 Repeatable Read 相似 —— 只能看到在事务启动之前提交的结果）。使用 SET TRANSACTION 语句在事务级设置隔离级别。使用 SET SESSION 在会话级进行设置。 &nbsp;</td> 
 </tr> 
 </tbody> 
</table>

**MySQL相对于PostgreSQL的劣势：**

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#000000;">MySQL</td> 
 <td style="border-color:#000000;">PostgreSQL</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">最重要的引擎InnoDB很早就由Oracle公司控制。目前整个MySQL数据库都由Oracle控制。</td> 
 <td style="border-color:#000000;">BSD协议，没有被大公司垄断。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">对复杂查询的处理较弱，查询优化器不够成熟</td> 
 <td style="border-color:#000000;">很强大的查询优化器，支持很复杂的查询处理。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join)。</td> 
 <td style="border-color:#000000;">都支持</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;">性能优化工具与度量信息不足 &nbsp;</td> 
 <td style="border-color:#000000;">提供了一些性能视图，可以方便的看到发生在一个表和索引上的select、delete、update、insert统计信息，也可以看到cache命中率。网上有一个开源的pgstatspack工具。 &nbsp;</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;"> InnoDB的表和索引都是按相同的方式存储。也就是说表都是索引组织表。这一般要求主键不能太长而且插入时的主键最好是按顺序递增，否则对性能有很大影响。 </td> 
 <td style="border-color:#000000;"> 不存在这个问题。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;"> 大部分查询只能使用表上的单一索引;在某些情况下，会存在使用多个索引的查询,但是查询优化器通常会低估其成本,它们常常比表扫描还要慢。 </td> 
 <td style="border-color:#000000;"> 不存在这个问题 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;"> 表增加列，基本上是重建表和索引，会花很长时间。 </td> 
 <td style="border-color:#000000;"> 表增加列，只是在数据字典中增加表定义，不会重建表 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;"> 存储过程与触发器的功能有限。可用来编写存储过程、触发器、计划事件以及存储函数的语言功能较弱 </td> 
 <td style="border-color:#000000;"> 除支持pl/pgsql写存储过程，还支持perl、python、Tcl类型的存储过程：pl/perl，pl/python，pl/tcl。 也支持用C语言写存储过程。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;"> 不支持Sequence。 </td> 
 <td style="border-color:#000000;"> 支持 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;"> 不支持函数索引，只能在创建基于具体列的索引。 不支持物化视图。 </td> 
 <td style="border-color:#000000;"> 支持函数索引，同时还支持部分数据索引，通过规则系统可以实现物化视图的功能。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;"> 执行计划并不是全局共享的, 仅仅在连接内部是共享的。 </td> 
 <td style="border-color:#000000;"> 执行计划共享 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;"> MySQL支持的SQL语法(ANSI SQL标准)的很小一部分。不支持递归查询、通用表表达式（Oracle的with 语句）或者窗口函数（分析函数）。 &nbsp;</td> 
 <td style="border-color:#000000;"> 都 支持 </td> 
 </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#000000;"> 不支持用户自定义类型或域(domain) </td> 
 <td style="border-color:#000000;"> 支持。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;"> 对于时间、日期、间隔等时间类型没有秒以下级别的存储类型 </td> 
 <td style="border-color:#000000;"> 可以精确到秒以下。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;"> 身份验证功能是完全内置的，不支持操作系统认证、PAM认证，不支持LDAP以及其它类似的外部身份验证功能。 </td> 
 <td style="border-color:#000000;"> 支持OS认证、Kerberos 认证 、Ident 的认证、LDAP 认证、PAM 认证 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;"> 不支持database link。有一种叫做Federated的存储引擎可以作为一个中转将查询语句传递到远程服务器的一个表上,不过,它功能很粗糙并且漏洞很多 </td> 
 <td style="border-color:#000000;"> 有dblink，同时还有一个dbi-link的东西，可以连接到oracle和mysql上。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;"> Mysql Cluster可能与你的想象有较大差异。开源的cluster软件较少。 复制(Replication)功能是异步的,并且有很大的局限性.例如,它是单线程的(single-threaded),因此一个处理能力更强的Slave的恢复速度也很难跟上处理能力相对较慢的Master. </td> 
 <td style="border-color:#000000;"> 有丰富的开源cluster软件支持。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;"> explain看执行计划的结果简单。 </td> 
 <td style="border-color:#000000;"> explain返回丰富的信息。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;"> 类似于ALTER TABLE或CREATE TABLE一类的操作都是非事务性的.它们会提交未提交的事务，并且不能回滚也不能做灾难恢复 </td> 
 <td style="border-color:#000000;"> DDL也是有事务的。 </td> 
 </tr> 
 </tbody> 
</table>

PostgreSQL主要优势：  
　　1. PostgreSQL完全免费，而且是BSD协议，如果你把PostgreSQL改一改，然后再拿去卖钱，也没有人管你，这一点很重要，这表明了PostgreSQL数据库不会被其它公司控制。oracle数据库不用说了，是商业数据库，不开放。而MySQL数据库虽然是开源的，但现在随着SUN被oracle公司收购，现在基本上被oracle公司控制，其实在SUN被收购之前，MySQL中最重要的InnoDB引擎也是被oracle公司控制的，而在MySQL中很多重要的数据都是放在InnoDB引擎中的，反正我们公司都是这样的。所以如果MySQL的市场范围与oracle数据库的市场范围冲突时，oracle公司必定会牺牲MySQL，这是毫无疑问的。  
　　2. 与PostgreSQl配合的开源软件很多，有很多分布式集群软件，如pgpool、pgcluster、slony、plploxy等等，很容易做读写分离、负载均衡、数据水平拆分等方案，而这在MySQL下则比较困难。  
      3. PostgreSQL源代码写的很清晰，易读性比MySQL强太多了，怀疑MySQL的源代码被混淆过。所以很多公司都是基本PostgreSQL做二次开发的。  
      4. PostgreSQL在很多方面都比MySQL强，如复杂SQL的执行、存储过程、触发器、索引。同时PostgreSQL是多进程的，而MySQL是线程的，虽然并发不高时，MySQL处理速度快，但当并发高的时候，对于现在多核的单台机器上，MySQL的总体处理性能不如PostgreSQL，原因是MySQL的线程无法充分利用CPU的能力。  
     目前只想到这些，以后想到再添加，欢迎大家拍砖。  
  
  
**PostgreSQL与oracle或InnoDB的多版本实现的差别**  
  
PostgreSQL与oracle或InnoDB的多版本实现最大的区别在于最新版本和历史版本是否分离存储，PostgreSQL不分，而oracle和InnoDB分，而innodb也只是分离了数据,索引本身没有分开。  
   PostgreSQL的主要优势在于：  
   1. PostgreSQL没有回滚段，而oracle与innodb有回滚段，oracle与Innodb都有回滚段。对于oracle与Innodb来说，回滚段是非常重要的，回滚段损坏，会导致数据丢失，甚至数据库无法启动的严重问题。另由于PostgreSQL没有回滚段，旧数据都是记录在原先的文件中，所以当数据库异常crash后，恢复时，不会象oracle与Innodb数据库那样进行那么复杂的恢复，因为oracle与Innodb恢复时同步需要redo和undo。所以PostgreSQL数据库在出现异常crash后，数据库起不来的几率要比oracle和mysql小一些。  
   2. 由于旧的数据是直接记录在数据文件中，而不是回滚段中，所以不会象oracle那样经常报ora-01555错误。  
   3. 回滚可以很快完成，因为回滚并不删除数据，而oracle与Innodb，回滚时很复杂，在事务回滚时必须清理该事务所进行的修改，插入的记录要删除，更新的记录要更新回来(见row\_undo函数)，同时回滚的过程也会再次产生大量的redo日志。  
   4. WAL日志要比oracle和Innodb简单，对于oracle不仅需要记录数据文件的变化，还要记录回滚段的变化。  
   **PostgreSQL的多版本的主要劣势在于：**  
   1、最新版本和历史版本不分离存储，导致清理老旧版本需要作更多的扫描，代价比较大，但一般的数据库都有高峰期，如果我们合理安排VACUUM，这也不是很大的问题，而且在PostgreSQL9.0中VACUUM进一步被加强了。  
　　2、由于索引中完全没有版本信息，不能实现Coverage index scan，即查询只扫描索引，直接从索引中返回所需的属性，还需要访问表。而oracle与Innodb则可以;  
  
  
**进程模式与线程模式的对比**  
PostgreSQL和oracle是进程模式，MySQL是线程模式。  
进程模式对多CPU利用率比较高。  
进程模式共享数据需要用到共享内存，而线程模式数据本身就是在进程空间内都是共享的，不同线程访问只需要控制好线程之间的同步。  
线程模式对资源消耗比较少。  
所以MySQL能支持远比oracle多的更多的连接。  
对于PostgreSQL的来说，如果不使用连接池软件，也存在这个问题，但PostgreSQL中有优秀的连接池软件软件，如pgbouncer和pgpool，所以通过连接池也可以支持很多的连接。  
  
**堆表与索引组织表的的对比**  
  
Oracle支持堆表，也支持索引组织表  
PostgreSQL只支持堆表，不支持索引组织表  
Innodb只支持索引组织表  
索引组织表的优势：  
表内的数据就是按索引的方式组织，数据是有序的，如果数据都是按主键来访问，那么访问数据比较快。而堆表，按主键访问数据时，是需要先按主键索引找到数据的物理位置。  
索引组织表的劣势：  
索引组织表中上再加其它的索引时，其它的索引记录的数据位置不再是物理位置，而是主键值，所以对于索引组织表来说，主键的值不能太大，否则占用的空间比较大。  
对于索引组织表来说，如果每次在中间插入数据，可能会导致索引分裂，索引分裂会大大降低插入的性能。所以对于使用innodb来说，我们一般最好让主键是一个无意义的序列，这样插入每次都发生在最后，以避免这个问题。  
由于索引组织表是按一个索引树，一般它访问数据块必须按数据块之间的关系进行访问，而不是按物理块的访问数据的，所以当做全表扫描时要比堆表慢很多，这可能在OLTP中不明显，但在数据仓库的应用中可能是一个问题。  
  
  
  
  
　 PostgreSQL9.0中的特色功能：     
    PostgreSQL中的Hot Standby功能  
    也就是standby在应用日志同步时，还可以提供只读服务，这对做读写分离很有用。这个功能是oracle11g才有的功能。  
  
    PostgreSQL异步提交（Asynchronous Commit）的功能：  
　 这个功能oracle中也是到oracle11g R2才有的功能。因为在很多应用场景中，当宕机时是允许丢失少量数据的，这个功能在这样的场景中就特别合适。在PostgreSQL9.0中把synchronous\_commit设置为false就打开了这个功能。需要注意的是，虽然设置为了异步提交，当主机宕机时，PostgreSQL只会丢失少量数据，异步提交并不会导致数据损坏而数据库起不来的情况。MySQL中没有听说过有这个功能。  
  
     PostgreSQL中索引的特色功能：  
     PostgreSQL中可以有部分索引，也就是只能表中的部分数据做索引，create index 可以带where 条件。同时PostgreSQL中的索引可以反向扫描，所以在PostgreSQL中可以不必建专门的降序索引了。

[30646e50266210a5adad723efbbf6d07.png]: /images/20221022/3bb505c266d54827b4968c8dcc966a27.png
[9efb651bcf459282ae26436dceba6ced.png]: /images/20221022/9e0a3afaa6ef410cb89081d2804671d9.png
[5e74f50bb05adc59f54046be8813bf6c.png]: https://img-blog.csdnimg.cn/img_convert/5e74f50bb05adc59f54046be8813bf6c.png
[PostGraphQL]: https://link.zhihu.com/?target=https%3A//github.com/graphile/postgraphile
[citus]: https://link.zhihu.com/?target=https%3A//www.citusdata.com/
[SQL]: https://link.zhihu.com/?target=https%3A//github.com/Vonng/pg/blob/master/case/knn.md
[timescaledb]: https://link.zhihu.com/?target=http%3A//www.timescale.com/
[PipelineDB]: https://link.zhihu.com/?target=https%3A//github.com/Vonng/hbase_fdw
[FDW]: https://link.zhihu.com/?target=https%3A//wiki.postgresql.org/wiki/Foreign_data_wrappers
[Link 1]: https://link.zhihu.com/?target=https%3A//github.com/Vonng/pg/blob/master/fdw/file_fdw-intro.md
[JSON]: https://link.zhihu.com/?target=http%3A//www.postgres.cn/docs/9.6/datatype-json.html
[Link 2]: https://link.zhihu.com/?target=https%3A//github.com/ept/hermitage
[Link 3]: https://link.zhihu.com/?target=https%3A//github.com/Vonng/pg/blob/master/case/pg-recsys.md
[https_pgxn.org]: https://link.zhihu.com/?target=https%3A//pgxn.org/