MySQL & PostgreSQL choice

背景

本周末阿里巴巴PG技术峰会将有一个MySQL & PostgreSQL的讨论环节，旨在揭示各自的优缺点，为用户在做数据库选型时提供参考。报名的同学不要错过，未报名参加的同学届时可以关注一下会议结束后发布的文章。

本文主要参考wikivs上的MySQL vs PostgreSQL（这个文章目前来说是互联网上相对公正的比较文章，而且内容也跟随社区会不断更新），针对MySQL和PostgreSQL各个方面进行比较，PostgreSQL部分根据个人见解进行补充，本文附件提供了一份PostgreSQL较为详细的介绍内容包括：

“发展历程，圈子;

特性;

如何了解源码;

如何跟踪内核;

进程结构，文件结构;

如何做压力测试;

版本升级;

备份恢复;

高可用;

读写分离;

分布式、水平扩展;

数据挖掘;

扩展功能;

TODO和软肋;

性能优化方法;

数据库安全;

benchmark;

学习资料;”

如果你正好在做数据库选型的话，本文可能对你有所帮助。

进入正题，本文将分几个方面对两种数据库产品进行一一叙述，MySQL部分完全参考wikivs上的介绍，PostgreSQL部分有所补充。

本文将在以下方面对PostgreSQL和MySQL展开叙述，如果MySQL方面有误，烦请指出，谢谢。

# 架构 #  
  
# 性能 #  
 ## 复杂查询场景 ##  
 ## 性能特性 ##  
  
# 多核扩展能力 #  
  
# IO扩展能力 #  
  
# count(*) #  
  
# 事务系统 #  
  
# 连接扩展能力和线程池 #  
  
# benchmark #  
  
# ACID实现承诺 #  
  
# 特性 #  
 ## 图形化管理工具 ##  
 ## 简化使用或严格限制 ##  
 ## Insert Ignore / Replace ##  
 ## 约束 ##  
 ## 字段默认值 ##  
 ## 存储过程 ##  
 ## 触发器 ##  
  
# 复制和高可用 #  
  
# 数据类型 #  
  
# 子查询 #  
  
# JOIN #  
  
# 高级索引 #  
  
# 表分区 #  
  
# CTE语法 #  
  
# 分析函数 #  
  
# 序列 #  
  
# 诊断和性能管理 #  
  
# 其他特性 #  
  
# 许可 #  
  
# 社区分支 #  
  
# 支持服务 #  
  
# 用户群体和流行度 #  
  
# 与Oracle的兼容性 #  

架构

MySQL

SQL层和数据存储层剥离，支持多种存储引擎。例如InnoDB, MyISAM, NDB。每种存储引擎适用于不同的场景，例如InnoDB支持行锁，支持ACID。而NDB则属于内存存储引擎的一种。用户可以根据自己的需求选择不同的存储引擎，但是请注意，每种存储引擎有各自的优缺点，务必了解清楚。在一个数据库中使用多种存储引擎也可能给事务一致性带来一定的问题。   ### PostgreSQL  
目前仅支持一种存储引擎，适合大多数的应用场景，如果需要用到某些特殊场景，可以使用FDW或者UDF来扩展PostgreSQL的存储引擎方面的能力，例如使用FDW可以将mongoDB,redis,MySQL等非常多的外部数据源作为本地表直接使用，使用UDF则可以满足在数据库中直接操纵外部数据源的目的。  
  
参考  
  
https://wiki.postgresql.org/wiki/Fdw  
 
http://blog.163.com/digoal@126/blog/static/1638770402015622360849/  

性能

性能的比较是最难说清的，应用场景的不同，优化水平的不同，环境的不同都是性能评测的干扰因素。

wikivs中提到MySQL是为简单应用设计初衷，PostgreSQL是为功能和可靠性设计初衷。所以MySQL在简单查询场景比较快，PostgreSQL在复杂查询场景比较快。(i.e. MySQL fast in simple operations, PostgreSQL more reliable and faster in complex operations)，不过实际上PostgreSQL在简单查询场景并不慢，可以参考一下我之前写的一系列PostgreSQL和Oracle在简单查询中的性能测试文章。

http://blog.163.com/digoal@126/blog/static/163877040201541104656600/

http://blog.163.com/digoal@126/blog/static/16387704020154431045764/

复杂查询场景

MySQL 在复杂查询场景较弱，可能体现在SQL执行计划生成方面，以及对查询的支持方面，如PG支持HASH JOIN,MERGE JOIN,NESTLOOP JOIN，MySQL目前仅支持NESTLOOP。

PostgreSQL 在复杂查询场景的处理能力较强，得益于良好的执行计划体系，CBO还是做得比较到位的，支持的因子：

# - Planner Cost Constants -  
  
#seq_page_cost = 1.0                    # measured on an arbitrary scale  
#random_page_cost = 4.0                 # same scale as above  
#cpu_tuple_cost = 0.01                  # same scale as above  
#cpu_index_tuple_cost = 0.005           # same scale as above  
#cpu_operator_cost = 0.0025             # same scale as above  
effective_cache_size = 8GB  

IO处理能力的因子可以设置到表空间级别，对于多种IOPS能力不一的存储混合使用的场景该设置尤为有效。

支持的优化手段：

遗传算法，对于超出一定数量的JOIN表，有效避免穷举带来的生成计划的开销。

# - Genetic Query Optimizer -  
  
#geqo = on  
#geqo_threshold = 12  
#geqo_effort = 5                        # range 1-10  
#geqo_pool_size = 0                     # selects default based on effort  
#geqo_generations = 0                   # selects default based on effort  
#geqo_selection_bias = 2.0              # range 1.5-2.0  
#geqo_seed = 0.0                        # range 0.0-1.0  

强制JOIN顺序，强制子查询关联提升，设置数据采样粒度，分区表约束优化等

# - Other Planner Options -  
  
#default_statistics_target = 100        # range 1-10000  
#constraint_exclusion = partition       # on, off, or partition  
#cursor_tuple_fraction = 0.1            # range 0.0-1.0  
#from_collapse_limit = 8  
#join_collapse_limit = 8                # 1 disables collapsing of explicit  
                                        # JOIN clauses  

性能特性

PostgreSQL

1. 支持绑定变量，在OLTP型业务中可以有效的降低SQL解析的开销。

2. 基于成本的优化器，统计信息丰富，如包括行数，页数，列柱状图，MCV，MCF，数组类型统计数组元素的MCV,MCF以及柱状图。空值比例，唯一值数量或比例，平均长度，值分布与物理顺序的相关性。对于继承表的父表，统计包括所有继承表在内的以上所有统计信息。

3. 支持多种索引，函数索引，部分索引，多列索引，多列复杂索引。

4. 支持多种索引扫描方法，btree, hash, gist, spgist, gin, brin。支持自定义索引扫描操作符。

 btree支持=,>,<,>=,<=的检索以及排序，  
   
 hash支持=的检索，  
   
 gist是一种平衡树形结构的索引开发框架，支持任意索引模型例如B-trees, R-trees等都可以在GIST中实现（PG原生支持的GIST索引包括几何类型的方位查询，距离查询，几何体的相交，相邻，包含等查询，全文检索类型的包含检索，多维数据类型的空间信息检索，KEY-VALUE类型的检索，模糊检索等），  
   
 sp-gist是一种空间分区的GIST索引开发框架，适用于非平衡的数据结构，例如quad-trees, k-d trees, radix trees (tries)。目前PG原生支持几何点类型的方位查询，包含查询，等于查询；范围类型的相交，包含，大于，小于，等于等查询；文本类型的比较。  
   
 brin是一种针对流式数据开发的块索引，记录了每个或连续的数据块分组的数据范围，是一种LOSSY索引，在检索时只能检索到数据块，还需要在数据块的数据中执行过滤。  
   
 gin是一种处理混合数据结构的索引开发框架，PG原生支持例如全文分词，数组，JSON类型的包含，相交，等查询。  

5. 支持index only scan, index scan, bitmap index scan(从索引检索大量数据，并按物理顺序排序后再从heap page按顺序返回).

6. 索引支持nestloop join, hash join, merge join.

7. 支持变长字段的自动压缩存储。tuple内压缩，tuple外压缩TOAST。4种字段的存储格式plain,main,external,extended

8. 支持unlogged 表，不记录WAL。

9. 支持数据库缓存预热，OS缓存预热。对于需要快速将数据加载到缓存的应用场景非常有用。

10. 支持异步提交，不影响数据库数据一致性。同时异步事务提交可以细化到事务级别，数据库级别，用户级别。对用户来说可以为可靠性要求高的事务选择同步提交，可靠性要求低性能要求高的事务选择异步提交。

11. 支持基于WAL的异步流复制，同步流复制。在网络带宽足够的情况下对上游节点的性能影响在5%以内。

   在异地容灾，HA，读写分离等场景中可以使用。用于提供数据安全性，可用性，数据库读性能。对于0数据丢失的应用场景也尤为有效。  

MySQL

支持多种存储引擎  
  
支持ANSI SQL 99的子集，以及扩展。  
  
结果缓存，如果表的数据未变更，重复的查询不需要去表里面查询数据。直接返回前一条SQL的结果。  
  
支持range,list,hash,set表分区  
  
支持基于SQL语句，或行的逻辑复制。  
  
网络协议压缩  
  
SSL  
  
触发器  
  
游标  
  
可更新的视图  
  
存储过程  
  
unicode支持  

针对MySQL的对应特性的PostgreSQL补充：

除了不支持多存储引擎，其他PG均原生支持或有其他实现方法。

1. 使用pgmemcached和触发器可以实现类似MySQL的query cache。但是对用户来说会更复杂。

2. 支持基于规则或触发器的分区，触发器分区可以实现任意形式的分区，但是需要注意提升触发器函数的效率。

3. 网络协议压缩，PG可使用SSL的压缩功能达到同样的目的，使用合理的加密算法可以在性能和安全性方面权衡。

4. 基于SQL语句或ROW的复制，PG 9.4已经实现逻辑复制协议，可以自定义decode实现基于ROW的逻辑复制。或者使用第三方的复制解决方案，例如slony, londiste3, bucardo, trigger等。

5. PG支持各种外部函数语言来扩充数据库端的编程能力，例如pl python, pl perl, pl tcl, pl R, pl java, pl javascript, pl lua, C等等。

MySQL各种存储引擎介绍：

MyISAM，表级别锁，在单线程的表扫描，索引扫描等操作比InnoDB更快。不支持ACID的事务，持久化，数据恢复，外键等特性。建议仅用于重度读应用场景。

InnoDB，是一个ACID兼容存储引擎，支持行锁，使用UNDO来实现多版本并发控制。修改数据时先将原有数据拷贝到UNDO buffer，然后在原有数据上进行修改（当修改后的记录超出原有长度时，需要扩展或发生行迁移），使用UNDO BUFFER可以有效的减少UNDO 的IO，因为在事务提交后老版本的UNDO可以不再需要（除非有repeatable read的事务需要用到它，具体MySQL是否实现这一点不清楚）。（而PostgreSQL是产生多版本来实现MVCC的，所以需要垃圾回收进程，会产生额外的IO开销以及WAL日志，包括后续的FREEZE操作也将产生WAL日志。在实际的性能测试中发现只要IOPS能力足够，PG的垃圾回收对性能并不会造成太大的性能干扰，从今年的PG全球开发者大会透入的信息来看将来PG可能会使用新的并发控制手段。）

innodb需要使用隐式主键，其他索引将指向这个主键，所以innodb的索引扫描实际上需要经过两次索引扫描。

为了提高插入速度，InnoDB支持secondary index buffer来异步处理索引。对于在索引字段的流式写入可用合并大量的索引更新IO来提高性能。对于非流式写入，性能提升并不明显。同时使用异步索引合并有一个问题，查询时如果遇到过时的索引页，需要先合并索引才能使用这个索引。

innodb支持数据压缩。

NDB，一种内存集群存储引擎，使用2PC存储多份数据来实现数据的高可用，当所有节点都提交了再返回给用。支持自动节点恢复，支持在线添加节点。相比其他引擎，NDB的复杂JOIN性能会比较弱，（高版本的NDB引擎可能会有所改善）。

PostgreSQL没有NDB类似的存储引擎，但是可以通过FDW来实现类似功能，例如fdw for redis。fdw for MySQL。

多核扩展能力

现在的CPU频率提升没有以前那么快，但是核数是越来越多，一台服务器有128个核也不足为奇。所以数据库在这种多核服务器上的性能显得尤为重要。

通常MySQL在多核机器上需要通过部署多实例来利用服务器的处理能力，5.6在多核的扩展能力上有了进步。

而PostgreSQL社区致力于打造企业级的数据库，对于多核的计算能力自然是重点方向。

老唐在高性能的服务器上对MySQL和PostgreSQL做过测试，MySQL持续的强负荷运行容易hang住，而PostgreSQL不会出现这样的问题。

IO扩展能力

PostgreSQL支持异步的客户端API，有40%的性能提升，同时服务端也支持异步提交，分组提交来降低IO请求，提升高并发的应用场景性能。

http://www.postgresql.org/docs/devel/static/libpq-async.html

MySQL 5.5版本开始 ， innodb引擎支持Linux的AIO，libaio userspace library

http://dev.mysql.com/doc/innodb/1.1/en/innodb-performance-aio-linux.html

count(*)

MySQL

MyISAM引擎对于不带WHERE条件的count(*)查询可以在瞬间完成，原因是这个存储引擎记录了表的条数作为一个常量查询。对于带where条件的查询，还是需要扫描索引来提升速度。

innodb引擎，不管带不带where条件，都需要索引来提升速度，但是因为innodb的索引中存储了版本信息，所以不需要扫描heap page来获得记录是否可用或可见的状态，因此不需要扫描heap page，只扫描index page。

PostgreSQL

由于PG的索引中不包含版本信息，9.2以前的版本，count(*)不仅仅需要扫描索引，还需要扫描HEAP page来获得版本信息判断数据的可见性。从9.2开始，引入了visibility map记录了对所有会话可见的heap 页信息，在这些page中的记录，扫描index page即可，不需要扫描heap page。因此9.2开始支持index only scan技术，减少HEAP PAGE的扫描。

对于不带where条件的count(*)，PG没有存储计数器，所以至少需要扫描整个索引来获得记录数。当然你可以通过触发器来实现计数器（为了降低锁冲突，你可以使用多条记录来计数）。

事务系统

MySQL

通过UNDO来实现并发控制，当更新记录时，首先将老的版本写入UNDO BUFFER，然后在当前HEAP PAGE中更新当前的记录，如果存储超出原来的记录位置，将发生行迁移，这种情况下需要连带更新索引。

弊端：1. 对于变长字段的更新，如果更新后的值长度超出原来的值，那么就会引发行迁移以及索引的更新。2. 频繁的更新或者一次更新大量记录时，UNDO BUFFER如果不够，可能导致性能严重下降。3. 回滚速度比较慢。

UNDO BUFFER足够大的情况下，可以有效的减少UNDO的写IO，因为事务提交后，如果没有其他事务（如repeatable read级别的事务）引用这个记录，那么这个UNDO记录是可以从buffer中清理掉的，而不需要写到UNDO磁盘中。

分组提交，在高并发的请求场景可以有效的降低REDO的写频度。

PostgreSQL

通过多版本来实现并发控制，当发生更新时，会产生一个新的版本。PG会尽量将这条新版本记录放到当前数据块中，使用本数据块内的地址引用来检索这条新的记录，这种情况下可以减少索引的更新（即HOT）。

弊端，需要回收垃圾，XID需要轮询使用，需要FREEZE，产生更多的REDO 日志。

好处，1. 回滚速度很快，因为老的版本并没有删除。2. 可以有效的实施更高的隔离级别如repeatable read, serialized隔离级别。3. 锁粒度可以很细。

优化手段，对于需要频繁更新或删除的表，尽量不要使用宽表，或者说不需要变更的字段不要放到这个表中，使用PK来引用。这样可以有效的减少垃圾TUPLE的长度。（当然这种做法也使得查询效率下降，因为访问拆分后的原来宽表上的所有字段需要扫描更多的数据块）

连接扩展能力和线程池

MySQL，线程模式，并且使用了线程cache来提升新建连接的效率。所以对于短连接业务MySQL的效率很高。

PostgreSQL，进程模式，每个连接对应一个数据库服务端进程，每次新建连接时PostgreSQL需要fork一个backend process，不适用于短连接。使用连接池可以有效的弥补 PostgreSQL的连接开销，例如pgbouncer连接池。但是需要注意使用连接池时，如果使用了prepared statement, 请使用session模式，其他模式不适合，因为其他模式的server pool复用时创建prepared name的连接可能已经分配到其他客户端或者不在了。

benchmark

因为优化水平，测试模型的不同，测试数据可能 有一定的偏颇，所以不提供这个对比。

PostgreSQL 可以参考一下我之前写的一系列PostgreSQL和Oracle在简单查询中的性能测试文章。

http://blog.163.com/digoal@126/blog/static/163877040201541104656600/

http://blog.163.com/digoal@126/blog/static/16387704020154431045764/

或者其他的一些测试文章:

http://blog.163.com/digoal@126/blog/static/16387704020141013115219217/

http://blog.163.com/digoal@126/blog/static/163877040201341441042613/

http://blog.163.com/digoal@126/blog/static/1638770402013414549515/

MySQL 请自行测试。

ACID实现承诺

ACID即数据库的原子性，一致性，隔离性，持久性。

PostgreSQL提供完整的ACID支持。

MySQL则和存储引擎有关，innodb引擎支持ACID，但是当你使用了多个存储引擎时，这个特性将被破坏，例如一个事务中包含了多种存储引擎的表的操作，又或者不同的存储引擎间的数据表有主外键关联并且有级联操作时，在恢复时可能出现数据的不一致。原因是触发器是在SQL层实现，而外键是在存储引擎层面实现导致。

NDB集群同样提供ACID兼容，通过存储多份数据来实现，但是请注意，多份数据都是存储在内存中的。

特性

图形化管理工具

MySQL

MySQL Workbench

http://dev.mysql.com/downloads/workbench/6.1.html

PostgreSQL

pgAdmin或其他的开源或商业GUI工具

http://www.pgadmin.org/

https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools

简化使用或严格限制

例如MySQL对于varchar(2)类型，如果插入的值超出长度限制，会自动截断并告警。而PostgreSQL则直接返回错误。

如果你在PostgreSQL要实现同样的需求，可以参考如下文章，自动截断超出部分的字符串：

http://blog.163.com/digoal@126/blog/static/16387704020153214036162/

Insert Ignore / Replace

存在则更新或忽略，不存在则插入。

PostgreSQL 9.5开始支持这个特性，此前需要在事务中或函数中处理类似需求。

http://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT

MySQL支持这个特性。

约束

MySQL仅支持非空，唯一，主键约束。在innodb和NDB引擎中还支持外键约束。对于CHECK约束，MySQL直接忽略。对于数据完整性要求严格的场景不适用(尽管可以使用触发器来代替CHECK约束，但是某些极端场景仍然可能造成问题)。

MySQL外键约束的支持也不够完善，例如不支持级联操作。不支持跨存储引擎的外键。不支持延迟约束检测（事务结束时检测）

PostgreSQL支持完成的约束，包括非空，唯一，主键，外键，级联外键操作，延迟约束检查（PG通过事务隔离级别和原子性来保证数据的完整性），实时约束检查，函数约束，触发器，域约束类型等。

对于数据要求严谨的场景，建议使用PostgreSQL。

字段默认值

MySQL仅仅支持NOW作为时间字段的默认值。

PostgreSQL支持任意函数（包括sql,c,plpgsql,plR,plpython,pljava,…所有支持的函数），或常量作为默认值。

存储过程

PostgreSQL支持多种函数语言，例如C, plperl, plpython, pltcl, pljava, pljavascript, plR, plproxy, plpgsql, 等.

这些函数可以在数据库中任意使用，例如支持用于创建触发器函数，在触发器函数中被调用，在其他函数中调用等。同时支持在线调试，大部分函数语言支持online code。支持用于创建操作符等。

安全方面，支持security definer, invoker权限设定。支持语言本身的使用权限设置，函数的权限设置。

成本优化方面，支持函数的成本设定，在WHERE条件中使用时根据函数的成本因子可以

支持函数返回复合类型，表类型，游标，任意类型，结果集等。

支持函数使用动态参数，复合参数，游标参数等。

MySQL通过插件可以安装外部语言，支持一些常用的语言例如php, python。但是这些语言只能在SQL中使用，不能被用在存储过程或触发器中。

触发器

MySQL，

不是所有的存储引擎都支持触发器。  
  
注意MySQL的触发器只能通过SQL语句触发，所以当调用不产生SQL的API时，例如NDB API（http://dev.mysql.com/doc/refman/5.1/en/triggers.html），不会触发。  
  
另外需要注意触发器的级联更新不会被触发，因为触发器是在innodb引擎层面实现的，而不是数据库层面。  
  
使用MySQL触发器需要注意以上两种情况，你可能会错过本应该触发的场景。（PostgreSQL不存在这些问题）  
  
5.7以前，MySQL在同一个触发事件下，只能创建一个触发器，例如maximum one ON UPDATE BEFORE and one ON UPDATE AFTER trigger。（PostgreSQL不存在这些问题）  

PostgreSQL，

支持使用任意用户定义的函数作为触发器函数，在触发器函数中也支持用户定义的任意函数。  
  
支持向触发器传递参数，支持在触发器函数中使用上下文相关的环境变量，例如事件，什么时候出发，触发器函数名称，用户，客户端IP，客户端端口，触发的对象，SCHEMA，数据库，等等。  
  
支持在一个事件上创建多个触发器（触发顺序和触发器名字的ASCII排序相关），支持在多个事件上使用同一个触发器。  
  
支持延迟触发，例如在事务提交前触发。（MySQL不支持延迟触发）  
  
支持列级触发器，(MySQL不支持)  
  
支持按条件触发触发器函数，例如WHEN OLD.x <> NEW.x，(MySQL不支持)  
  
支持视图触发器，  
  
支持级联触发，  
  
除了支持触发器，还支持rule.  
  
支持在视图上创建触发器或rule。  

复制和高可用

PostgreSQL，

基于WAL的复制，可以复制整个集群，或者复制部分集群（例如表空间），也可以从WAL解出ROW后进行过滤复制（类似MYSQL的BIN LOG复制）

支持4种事务写WAL级别，分别代表

异步提交，

等待本地XLOG持久化，

等待本地XLOG持久化以及同步流复制节点xlog WRITE，

等待本地XLOG持久化，以及同步流复制节点XLOG持久化。

用户可以根据事务的可靠性要求以及性能要求选择合适的事务提交级别。

参考代码：

src/include/access/xact.h

typedef enum  
{  
        SYNCHRONOUS_COMMIT_OFF,         /* asynchronous commit */  
        SYNCHRONOUS_COMMIT_LOCAL_FLUSH,         /* wait for local flush only */  
        SYNCHRONOUS_COMMIT_REMOTE_WRITE,        /* wait for local flush and remote  
                                                                                 * write */  
        SYNCHRONOUS_COMMIT_REMOTE_FLUSH         /* wait for local and remote flush */  
}       SyncCommitLevel;  

src/backend/replication/syncrep.c

	/*  
         * Set the lsn first so that when we wake backends they will release up to  
         * this location.  
         */  
        if (walsndctl->lsn[SYNC_REP_WAIT_WRITE] < MyWalSnd->write)  
        {  
                walsndctl->lsn[SYNC_REP_WAIT_WRITE] = MyWalSnd->write;  
                numwrite = SyncRepWakeQueue(false, SYNC_REP_WAIT_WRITE);  
        }  
        if (walsndctl->lsn[SYNC_REP_WAIT_FLUSH] < MyWalSnd->flush)  
        {  
                walsndctl->lsn[SYNC_REP_WAIT_FLUSH] = MyWalSnd->flush;  
                numflush = SyncRepWakeQueue(false, SYNC_REP_WAIT_FLUSH);  
        }  

延迟监控：

postgres=# \d pg_stat_replication   
          View "pg_catalog.pg_stat_replication"  
      Column      |           Type           | Modifiers   
------------------+--------------------------+-----------  
 pid              | integer                  |   
 usesysid         | oid                      |   
 usename          | name                     |   
 application_name | text                     |   
 client_addr      | inet                     |   
 client_hostname  | text                     |   
 client_port      | integer                  |   
 backend_start    | timestamp with time zone |   
 backend_xmin     | xid                      |   
 state            | text                     |   
 sent_location    | pg_lsn                   |   接收到的XLOG位置  
 write_location   | pg_lsn                   |   write结束的XLOG位置  
 flush_location   | pg_lsn                   |   flush到持久化存储的XLOG位置  
 replay_location  | pg_lsn                   |   已恢复的位置  
 sync_priority    | integer                  |   
 sync_state       | text                     |   

使用pg_xlog_location_diff(pg_current_xlog_insert_location(),?)可以计算出延迟XLOG的字节数。

PostgreSQL 从9.0开始支持流复制和hot_standby，使用流复制可以方便的实现容灾，HA，读写分离。

（注意，不产生WAL的操作时无法通过基于WAL的复制方法来同步的，例如HASH索引，unlogged table, temp table都无法使用流复制来同步数据）

使用第三方插件，可以实现集群的部分物理复制，例如walbouncer。

从9.4开始，PostgreSQL的WAL信息中包含了逻辑复制需要的信息，例如row，基于此，可以实现基于WAL的逻辑复制。(类似MySQL的binlog复制)

使用第三方插件，也可以实现基于触发器或前端SQL分发的逻辑复制。例如pgpool-II, londiste3, slony-I, bucardo等.

MySQL

支持语句级别(SBR)或者行级别(RBR)的逻辑同步，不支持块级别的物理同步模式。

对于SBR，可能导致复制异常，所以现在innodb 和ndb存储引擎都使用RBR。

MySQL的逻辑复制支持异步模式，或者半同步模式。半同步模式指当事务提交时，至少有一个SLAVE接收到了这个事务的binlog（但不需要等待这个BINLOG在SLAVE上完成apply）。

MySQL同样有第三方的复制插件：

http://codership.com/products/galera_replication

http://code.google.com/p/tungsten-replicator/

数据类型

PostgreSQL，

支持数字，货币，字符串，字节流，时间，布尔，枚举，几何，网络，比特，全文检索，UUID，XML，JSON，数组，范围等数据类型。以及这些数据类型的操作方法，索引访问方法等。

PostgreSQL还支持自定义数据类型，操作符，以及索引访问方法。

8.1. Numeric Types  
  8.1.1. Integer Types  
  8.1.2. Arbitrary Precision Numbers  
  8.1.3. Floating-Point Types  
  8.1.4. Serial Types  
8.2. Monetary Types  
8.3. Character Types  
8.4. Binary Data Types  
  8.4.1. bytea Hex Format  
  8.4.2. bytea Escape Format  
8.5. Date/Time Types  
8.6. Boolean Type  
8.7. Enumerated Types  
8.8. Geometric Types  
  8.8.1. Points  
  8.8.2. Lines  
  8.8.3. Line Segments  
  8.8.4. Boxes  
  8.8.5. Paths  
  8.8.6. Polygons  
  8.8.7. Circles  
8.9. Network Address Types  
  8.9.1. inet  
  8.9.2. cidr  
  8.9.3. inet vs. cidr  
  8.9.4. macaddr  
8.10. Bit String Types  
8.11. Text Search Types  
  8.11.1. tsvector  
  8.11.2. tsquery  
8.12. UUID Type  
8.13. XML Type  
8.14. JSON Types  
8.15. Arrays  
8.17. Range Types  
8.18. Object Identifier Types  
8.19. pg_lsn Type  
8.20. Pseudo-Types  

更多扩展类型见：

http://postgis.org/

http://pgxn.org/

http://www.postgresql.org/docs/9.4/static/contrib.html

如HyperLogLog数据类型（唯一值统计），GIS数据类型（地理位置信息），cube(多维类型), earth（地球模型）, ltree(树形结构), hstore（key-value类型）, pg_trgm（模糊数据类型）,citext(不区分大小写的数据类型)等。

对不同的业务，选择合适的数据类型，可以简化应用程序的设计，提高数据的访问效率。例如使用iprange来为智能DNS解析加速IP地址就近寻址。

MySQL，

支持数字，时间，字符串，空间类型。

11.1 Data Type Overview     [+/-]  
11.2 Numeric Types     [+/-]  
11.3 Date and Time Types     [+/-]  
11.4 String Types     [+/-]  
11.5 Extensions for Spatial Data     [+/-]  
11.6 Data Type Default Values  
11.7 Data Type Storage Requirements  
11.8 Choosing the Right Type for a Column  
11.9 Using Data Types from Other Database Engines  

MySQL支持unsigned int, PostgreSQL可以通过自定义域来实现同等功能。

例子：

postgres=# create domain uint32 as int constraint unsigned check(VALUE>=0);  
CREATE DOMAIN  
postgres=# create table test(id uint32);  
CREATE TABLE  
postgres=# insert into test values (-1);  
ERROR:  value for domain uint32 violates check constraint "unsigned"  
postgres=# insert into test values (0);  
INSERT 0 1  
postgres=# insert into test values (100);  
INSERT 0 1  

子查询

MySQL的子查询支持较弱，性能存在问题，同时不支持在视图中使用子查询。

PostgreSQL不存在这样的问题。

JOIN

MySQL不支持full outer join，同时不支持merge join, hash join，只支持nest loop join。

PostgreSQL都支持，所以在复杂SQL的处理场景，PG会更加胜出。

高级索引

Index Type	MySQL	PostgreSQL
Hash indexes	InnoDB, NDB and MEMORY engines support Hash indexes	PostgreSQL supports Hash indexes, though as of 8.1 they are never faster than b-tree indexes [25]
Multiple Indexes	MySQL supports multiple indexes per query.[26]	PostgreSQL supports multiple indexes per query.
Change Buffering (also called insert buffer)	InnoDB has a feature to delay building index pages of non unique secondary indexes when the index is too large to fit in memory. This allows it to merge modifications, and in many cases reduce IO considerably.但是对于更新的数据检索，需要先对buffer数据合并，再检索。	Postgresql does not support this feature. 实际上gin支持类似特性，并且不存在需要对更新数据进行索引合并后查询的问题。实际应用往往应该在插入，更新，查询的混合场景进行比较才比较公平。
Full-Text Indexes	MySQL comes with full-text search for InnoDB and MyISAM storage engines. Prior to version 5.6 only the MyISAM storage engine supported this feature.[27] A 3rd party add-on to MySQL, Sphinx Fulltext Search Engine allows it to support full-text searches on storage engines which do not natively support it.	PostgreSQL 8.2 has full text search in the tsearch2 module. PostgreSQL 8.3 integrates tsearch2 into the core: “TSearch2, our cutting-edge full text search tool, has been fully integrated into the core code, and also has a cleaner API.” [28] 扩展定制能力更强。
Partial Indexes	MySQL does not support partial indexes. PostgreSQL supports partial indexes: A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries for only those table rows that satisfy the predicate. Partial indexes are a specialized feature, but there are severalsituations in which they are useful. One major reason for using a partial index is to avoid indexing common values. Since a query searching for a common value (one that accounts for more than a few percent of all the table rows) will not use the index anyway, there is no point in keeping those rows in the index at all. This reduces the size of the index, which will speed up queries that do use the index. It will also speed up many table update operations because the index does not need to be updated in all cases.	PostgreSQL , PostgreSQL 8.2.6 Documentation: Chapter 11. Indexes例如: create index idx on tbl(id) where col=’abc’;对于只需要对某些条件频繁查询的场景尤为有效。
Prefix Indexes	MySQL supports prefix indexes. Prefix indexes cover the first N characters of a string column, making the index much smaller than one that covers the entire width of the column, yet still provide good performance characteristics.	With PostgreSQL, prefix indexes are a particular case of Expression Indexes (see below).
Multi-column Indexes	MySQL is limited to 16 columns per index. [29]And not all storage engines provide multi-column indexes. PostgreSQL is limited to 32 columns per index. [30]	可以在编译PG软件前，通过修改pg_config_manual.h实现增加列数的目的。
Bitmap Indexes	MySQL has no bitmap indexes but achieves similar functionality using its “index_merge” feature.	PostgreSQL supports the ability to combine multiple indexes at query time using bitmap indexes.
Expression Indexes	Expression Indexes can be emulated in MySQL by adding a precomputed column and using a trigger to maintain it.	PostgreSQL allows you to create indexes based on expressions (which may include calls to immutable functions). This is very handy in case there is a table with relatively stable data (not a lot of inserts / updates) and will often be running a query which involves an expensive calculation - the expression itself can be indexed thus eliminating the need of computing it at query runtime. 表达式索引在某些应用场景非常有用，例如将数据通过函数转换为范围类型，建立GIST索引。
Non-blocking CREATE INDEX	Dependent on the storage engine. Some engines (such as NDB Cluster and InnoDB Plugin) support online add/drop index (no locks taken). If the engine doesn’t support online add/drop index, a write exclusive lock is required and the table copied.	PostgreSQL supports the ability to create indexes without locking the table for writes.
Covering Indexes	MySQL supports covering indexes, which allow data to be selected by scanning the index alone without touching the table data. This is advantageous with large tables that have many millions of rows.	Covering indexes were added to PostgreSQL 9.2 结合VM，使用index only scan。

PostgreSQL支持btree,hash,gist,sp-gist,gin,brin几种索引扫描方法，几乎覆盖了所有的数据类型。

同时GIN,GIST,SP-GIST,BRIN索引有开发的API，可以对自定义的数据类型创建对应的索引访问方法，在特殊的应用场景非常有效，例如地理位置信息，化学，数组，生物学，空间模型等应用场景尤为有效。

PG为索引提供API的目的是，让用户关注数据，而非数据库的实现，所以开发索引访问方法需要对数据的理解而不是数据库。

表分区

MySQL，

支持HASH,RANGE,LIST,KEY分区，支持range,list与hash,key的组合分区。

一个表最多支持1024个分区。

PostgreSQL，

理论上支持所有的分区类型，因为目前PostgreSQL通过触发器和继承来实现分区，只要将逻辑写在函数中即可。

PG的分区表没有个数现在。

目前PostgreSQL的分区对性能损耗较大，今年PG全球开发者大会对这个问题提出了讨论，未来的版本将会改进创建分区表的方式，不再需要通过触发器来实现。

CTE语法

MySQL不支持。

PostgreSQL支持CTE语法，支持DML，递归等。

例子：

分析函数

PostgreSQL支持诸多分析函数，

聚合

例子，用于做线性回归预测

窗口

GROUPING SETS, CUBE and ROLLUP

http://blog.163.com/digoal@126/blog/static/16387704020154269591874/

MySQL不支持。

序列

PostgreSQL支持，MySQL不支持。

诊断和性能管理

MySQL，提供类似Oracle 等待事件类似的性能视图用于诊断。

PostgreSQL，提供语句级的统计视图包括（调用次数，CPU耗时，全局、本地缓存命中率，IO耗时等），提供表级别统计信息（包括HEAP 、INDEX 、TOAST PAGE的读和命中数，插入，更新，删除，HOT记录数，live记录，dead记录，全表扫描次数，索引扫描次数，全表扫描返回的记录数，索引扫描获取的记录数，最后的自动、非自动垃圾回收，统计分析的时间，以及对应的次数。），提供索引级别统计信息（索引块读次数，命中次数，扫描次数，获取的行数）。但是这里需要注意读（不区分OS层CACHE命中），HINT只说明数据库的SHARED BUFFER命中。

如果要让PostgreSQL输出类似Oracle等待时间的视图，需要使用第三方工具，例如oprofile, stap。

其他特性

PostgreSQL，

Limit	Value
Maximum Database Size	Unlimited
Maximum Table Size	32 TB
Maximum Row Size	1.6 TB
Maximum Field Size	1 GB
Maximum Rows per Table	Unlimited
Maximum Columns per Table	250 - 1600 depending on column types
Maximum Indexes per Table	Unlimited

1. 使用pgagent进行数据库任务调度。

2. 支持多种函数语言（如python,perl,php,java,c,r,tcl,proxy），对于服务端编程的重度用户，可以为其提供非常好的编程接口，服务端处理有利有弊，利的话如可以缩短数据访问路径，提供更高的安全性（都封装为函数）。弊端主要是扩展性，代码维护性，可移植性差，耦合度高。

3. 支持多种外部表插件，可以访问几乎所有的数据源，（支持自定义handler，实现对外部数据源的访问）。对于企业中有多种数据库品牌的数据融合，跨库访问有很好的左右，对数据迁移也很有效。

4. 支持自定义扫描方法，例如结合存储特性使用自定义的扫描方法，如AIO，RDMA都可以使用这种接口来实现，在不修改核心代码的情况下，为用户定制硬件环境提供很好的API接口。例如你可能要做基于IB的存储分离一体机解决方案，那么这会是个不错的切入点。

5. 支持自定义采样方法，自定义采样方法，可以有效的利用生产环境按照用户自己的需要创建测试环境，例如在里面可以做数据的转换，随机数据的抽取等。

6. 支持遗传算法，对于超出10个表的关联，关联顺序有10!种，加上关联方法，可能有更多种plan 方法，这时生成执行计划会变得额外消耗CPU资源，为了提高效率，PG使用遗传算法来解决这个关联的问题。

7. 支持通过gist,gin,sp-gist,brin接口自定义索引。

8. 支持自定义数据类型，自定义数据访问操作符。例如特殊的应用场景，需要定义特殊的数据类型和操作符来实现对数据的管理。比如 地理位置信息数据，图形识别数据，IP地址数据，异构数据，生物信息，声波数据，指纹数据等。利用PG的这种API，可以解决目前RDBMS没有涉及的领域的数据管理问题。

许可

PostgreSQL是类BSD许可，允许用户以任何目的免费使用PostgreSQL，甚至一行代码都不该，你可以闭源卖给其他用户使用。只要在你的产品许可中包含如下代码：

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT,   
SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE   
OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN   
ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.  
THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT   
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.   
THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS   
NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.  

因为这个许可，使得很多数据库厂商会基于PG做一定的定制，打包为新的数据库产品。国内就有很多这样的产品。

MySQL 分商业版和社区版，社区版为GPL 许可, 允许免费使用，但是如果你要分发你的代码，你可以选择开源，或者闭源，如果选择闭源则必须向ORACLE支付费用。

The MySQL Community Edition includes:  
Pluggable Storage Engine Architecture  
Multiple Storage Engines:  
InnoDB  
MyISAM  
NDB (MySQL Cluster)  
Memory  
Merge  
Archive  
CSV  
and more  
MySQL Replication to improve application performance and scalability  
MySQL Partitioning to improve performance and management of large database applications  
Stored Procedures to improve developer productivity  
Triggers to enforce complex business rules at the database level  
Views to ensure sensitive information is not compromised  
Performance Schema for user/application level monitoring of resource consumption  
Information Schema to provide easy access to metadata  
MySQL Connectors (ODBC, JDBC, .NET, etc) for building applications in multiple languages  
MySQL Workbench for visual modeling, SQL development and administration  
Available on over 20 platforms and operating systems including Linux, Unix, Mac and Windows.  

社区分支

MySQL，分支较多。

PostgreSQL，只有一个主分支，代码托管在git.postgresql.org。

支持服务

PostgreSQL是纯社区产品，但是前面说了，有很多厂商是基于PG来开发的，所以研发人员不少，至少在国内比MySQL多。

国际上也有专门做PG支持的公司，包括PostgreSQL社区核心成员所在的公司，例如2nd, PGexpert, EDB(EDB也是PostgreSQL的商业化产品之一)，saleforce，华为等。

MySQL，从Oracle就可以买到支持，另外国内也有很多公司在做MySQL的解决方案。

用户群体和流行度

MySQL，在WEB类业务上非常流行。

PostgreSQL，用户在各个行业都有。

  生物制药 {Affymetrix(基因芯片), 美国化学协会, gene(结构生物学应用案例), …}  
  电子商务 { CD BABY, etsy(与淘宝类似), whitepages, flightstats, Endpoint Corporation …}  
  学校 {加州大学伯克利分校, 哈佛大学互联网与社会中心, .LRN, 莫斯科国立大学, 悉尼大学, …}  
  金融 {Journyx, LLC, trusecommerce(类似支付宝),  日本证券交易交所, 邮储银行, 同花顺…}  
  游戏 {MobyGames, …}  
  政府 {美国国家气象局, 印度国家物理实验室, 联合国儿童基金, 美国疾病控制和预防中心,  美国国务院, 俄罗斯杜马…}  
  医疗 {calorieking, 开源电子病历项目, shannon医学中心, …}  
  制造业 {Exoteric Networks, 丰田, 捷豹路虎}  
  媒体 {IMDB.com, 美国华盛顿邮报国会投票数据库, MacWorld, 绿色和平组织, …}  
  零售 {ADP, CTC, Safeway, Tsutaya, Rockport, …}  
  科技 {Sony, MySpace, Yahoo, Afilias, APPLE, 富士通, Omniti,  Red Hat, Sirius IT, SUN, 国际空间站, Instagram,  Disqus, 去哪儿, 腾讯, 华为, 中兴, 斯凯, 云游, 阿里 …}  
  通信 {Cisco, Juniper, NTT(日本电信), 德国电信, Optus, Skype, Tlestra(澳洲电讯),  中国移动…}  
  物流 {SF}  
  More : http://www.postgresql.org/about/users/  

应用场景跨度

### 1 任意维度实时圈人      
### 2 时序数据实时处理    
### 3 时间、空间、业务 多维数据实时透视    
### 4 独立事件相关性分析    
### 5 海量关系实时图式搜索      
### 6 社交业务案例      
### 7 流式数据实时处理案例      
### 8 物联网      
### 9 海量全文检索      
### 10 海量模糊、正则查询案例      
### 11 海量数组、图像特征数据 实时检索      
### 12 数据清洗、采样、脱敏、批处理、合并      
### 13 空间数据应用案例    
### 14 金融业务      
### 15 异步消息应用案例      
### 16 海量冷热数据分离     
### 17 倒排索引案例      
### 18 Greenplum 案例      
### 19 综合应用案例    
### 20 数据订阅      
### 21 读写分离    
### 22 水平扩展      

以上场景案例参考

《PostgreSQL、Greenplum 应用案例宝典《如来神掌》 - 目录》

与Oracle的兼容性

MySQL，语法支持较弱，功能相比Oracle有较大差距，兼容性较差。

PostgreSQL，PostgreSQL的语法与Oracle非常类似，并且大多数的Oracle功能都支持，所以兼容性较好。而PostgreSQL的商业版本之一EDB致力于做Oracle的兼容性，几乎可以做到超过99%的兼容，因此大多数的应用从Oracle 迁移到EDB都不需要用改动代码。

PostgreSQL补充：#

自定义操作符，UDF，扩展插件

自定义类型，数据挖掘

其他可以参见本文附件PPT。

参考

1. http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL

2. PostgreSQL 1 Day pptx

digoal’s 大量PostgreSQL文章入口