postgresql和mysql的区别

postgresql与mysql的区别主要体现在语法方面：

第一，在pgsql上的功能实现比mysql严谨，对表的连接支持更加完整；

第二，pgsql的优化器功能完整，支持索引类型多，而mysql的优化器比较简单，适用于简单的查询操作。

1 架构对比

MySQL：多线程

PostgreSQL：多进程

多线程架构和多进程架构之间没有绝对的好坏，例如oracle在unix上是多进程架构，在windows上是多线程架构。

PG 的有多种集群架构可以选择，plproxy 可以支持语句级的镜像或分片，slony 可以进行字段级的同步设置，standby 可以构建WAL文件级或流式的读写分离集群，同步频率和集群策略调整方便，操作非常简单。

pgsql对于numa架构的支持比mysql强一些，比MYSQL对于读的性能更好一些，pgsql提交可以完全异步，而mysql的内存表不够实用（因为表锁的原因）

2 对存储过程及事务的支持能力

1） MySQL对于无事务的MyISAM表，采用表锁定，一个长时间运行的查询很可能会长时间地阻碍对表的更新，而PostgreSQL不存在这样的问题。

2） PostgreSQL支持存储过程，要比MySQL好，具备本地缓存执行计划的能力；

3） MySQL 4.0.2-alpha开始支持事务的概念，保留无事务的表类型， 为用户提供了更多的选择。

3 稳定性及性能

1）高并发读写，负载逼近极限下，PG的性能指标仍可以维持双曲线甚至对数曲线，到顶峰之后不再下降，而 MySQL 明显出现一个波峰后下滑（5.5版本之后，在企业级版本中有个插件可以改善很多，不过需要付费）

2） PostgreSQL 的稳定性极强， Innodb 等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步，然而很多 MySQL 用户都遇到过Server级的数据库丢失的场景——mysql系统库是MyISAM的，相比之下，PG数据库这方面要好一些。

3） mysql的innodb引擎，可以充分优化利用系统所有内存，超大内存下PG对内存使用的不那么充分（需要根据内存情况合理配置）。从测试结果上看，mysql 5.5的性能提升很大，单机性能强于pgsql，5.6应该会强更多。

4 高可用性

MySQL可以适应24/7运行。在绝大多数情况下，你不需要为MySQL运行任何清除程序。PostgreSQL目前仍不完全适应24/7运行，这是因为你必须每隔一段时间运行一次VACUUM。

innodb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触 发VACUUM，会带来多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀；

5 数据同步方式

1）mysql到现在也是异步复制，pgsql可以做到同步，异步，半同步复制。

2） mysql的同步是基于binlog复制，类似oracle golden gate,是基于stream的复制，做到同步很困难，这种方式更加适合异地复制；pgsql的复制基于wal，可以做到同步复制。同时，pgsql还提供stream复制。

3） MySQL的复制可以用多级从库，但是在9.2之前，PGSQL不能用从库带从库。

4） PG的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。

7 权限控制对比

MySQL允许你定义一整套的不同的数据级、表级和列级的权限，允许你指定基于主机的权限；

MySQL的MERGE表提供了一个独特管理多个表的方法。myisampack可以对只读表进行压缩，此后仍然可以直接访问该表中的行。

7 SQL语句支持能力

1） PG有极其强悍的 SQL 编程能力（9.x 图灵完备，支持递归！），有非常丰富的统计函数和统计语法支持，比如分析函数（ORACLE的叫法，PG里叫window函数）；

2） 支持用多种语言来写存储过程，对于R的支持也很好。这一点上MYSQL就差的很远，很多分析功能都不支持。腾讯内部数据存储主要是MYSQL，但是数据分析主要是HADOOP+PGSQL（听李元佳说过，但是没有验证过）。

3） pgsql对表名大小的处理，只有在SQL语句中，表名加双引号，才区分大小写。

4）在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨；

5）对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强；

6） MySQL采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结构设计存在约束；

7） MySQL的Join操作的性能非常的差，只支持Nest Join，所以一旦数据量大，性能就非常的差。PostgreSQL除了支持nest join外，还支持hash join和 sort merge join；PostgreSQL支持正则表达式查找，MySQL不支持；

8 数据类型支持能力

PostgreSQL可以更方便地使用UDF(用户定义函数)进行扩展。

1）有丰富的几何类型，实际上不止几何类型，PG有大量字典、数组、bitmap 等数据类型, 因此PG 多年来在 GIS 领域处于优势地位。相比之下mysql就差很多，instagram就是因为PG的空间数据库扩展POSTGIS远远强于MYSQL的my spatial而采用PGSQL的。

MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON，其空间索引只能在存储引擎为MYISAM的表中创建，用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL。不同的存储引擎有差别。

MyISAM和InnoDB都支持spatial extensions，但差别在于：如果使用MyISAM，可以建立spatial index，而InnoDB是不支持的。

2） pgsql对json支持比较好，还有很逆天的fdw]功能，就是把别的数据库的表当自己的用；

3） pgsql的字段类型支持的多，有很多mysql没有的类型，但是实际中有时候用到。

4） 一般关系型数据库的字符串有限定长度8k左右，无限长 TEXT 类型的功能受限，只能作为外部大数据访问。而 PG 的 TEXT 类型可以直接访问，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用xml xpath。用PG的话，文档数据库都可以省了。

5） postgresql有grouping sets函数，也是迫使我抛弃mysql第一原因。做报表后台计算，olap/oltp之类的这个函数简直是刚性需求。没有grouping sets函数，我感觉做报表后台计算，简直惨不忍睹。

当然pgsql还有挺多很好用的窗口函数之类，用起来真心爽。mysql做数据报表计算后台最大缺点就是没有grouping sets和一些窗口函数，替代方案很麻烦而且效率低，做很多统计数据各种表连接、外连接等等一大堆，不同数据库之间数据的利用计算。

8） PG支持R-trees这样可扩展的索引类型，可以更方便地处理一些特殊数据。

9）PG可以使用函数和条件索引，使得数据库的调优非常灵活，mysql就没有这个功能，条件索引在web应用中很重要。

9 入库过程容错能力

大批量数据入库，PostgresSQL要求所有数据必须完全满足要求，有一条错误，整个数据入库过程失败；MySQL无此问题。比如，每天1000万行数据，就因为一条打印的不完整，PostgreSQL会直接报错，导致一条也导入不进去。

1000万里面有一行将数字类型的等级打印成了字符串的东西，那么pgsql会非让你找出这一条删掉，然后才能将剩下的数据导入进去。mysql就完全没有这个问题，比如mysql level字段定义的int类型，几千万中有一条数据没注意打印成字符串，mysql会自己给你转成0存储的，不会有任何报错。

10 表组织方式

1） pgsql用继承的方法实现分区表，让分区表的使用不方便且性能差，这点比不上mysql。

2） PG主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量；

3） MySQL分区表的实现要优于PG的基于继承表的分区实现，主要体现在分区个数达到上千上万后的处理性能差异较大。

11 开发接口

对于web应用来说,mysql 5.6 的内置MC API功能很好用，PGSQL差一些。

PG 的“无锁定”特性非常突出，甚至包括 vacuum 这样的整理数据空间的操作，这个和PGSQL的MVCC实现有关系。

12 维护团队

MySQL的背后是一个成熟的商业公司，使得MySQL的开发过程更为慎重；

PostgreSQL的背后是一个庞大的志愿开发组， PostgreSQL的反应更为迅速。这样的两种背景直接导致了各自固有的优点和缺点。

对于一个严肃的商业应用来说，事务的支持是不可或缺的。对于一个严肃的商业应用来说，作为数据库本身，有众多的商业逻辑的存在，此时使用存储过程可以在较少地增加数据库服务器的负担的前提下，对这样的商业逻辑进行封装，并可以利用数据库服务器本身的内在机制对存储过程的执行进行优化。此外存储过程的存在也避免了在网络上大量的原始的SQL语句的传输，这样的优势是显而易见的。

用统一的SQL，去访问其他关系数据库，其他NoSQL数据库，HBase，甚至是各种格式的文件，操作系统信息，在线数据集。