PostgreSQ及与MySQL对比

1.什么是PostgreSql：

PostgreSQL是一个功能强大的开源对象关系型数据库系统，他使用和扩展了SQL语言，并结合了许多安全存储和扩展最复杂数据工作负载的功能。PostgreSQL的起源可以追溯到1986年，作为加州大学伯克利分校POSTGRES项目的一部分，并且在核心平台上进行了30多年的积极开发。

PostgresSQL凭借其经过验证的架构，可靠性，数据完整性，强大的功能集，可扩展性以及软件背后的开源社区的奉献精神赢得了良好的声誉，以始终如一地提供高性能和创新的解决方案。PostgreSQL在所有主要操作系统开始使用PostgreSQL从未如此简单。

PostgreSQL与MySQL对比

都属于开放源码的一员，性能和功能都在高速地提高和增强。MySQL AB的人们和PostgreSQL的开发者们都在尽可能地把各自的数据库改得越来越好，所以对于任何商业数据库使用其中的任何一个都不能算是错误的选择。

PostgreSQL : 免费

原则: 对于一个数据库，稳定性和速度并不能代表一切。对于一个成熟的数据库，稳定性肯定会日益提供。而随着硬件性能的飞速提高，速度也不再是什么太大的问题。
 

下面是PostgreSQL中的各种功能介绍：

数据类型：

    基本类型：Integer, Numeric, String, Boolean
    结构类型：Date/Time, Array, Range, UUID
    文档类型：JSON/JSONB, XML, Key-value(Hstore)
    几何类型：Point, Line, Circle, Polygon
    自定义类型：Composite, Custom Types

数据的完整性

1. 唯一性，不为空
2. 主键
3. 外键
4. 排除约束
5. 显式锁定，咨询锁定

并发性，性能

1. 索引：
2. 高级索引
3. 复杂的查询计划期/优化器
4. 交互
5. 多版本并发控制(MVCC)
6. 读取查询的并行化和构建B树索引
7. 表分区
8. Sql标准中定义的所有事物隔离级别，包括Serializable
9. 即时表达式汇编(JIT)

1. 可靠性，灾难恢复
   1. 预写日志（WAL）
   2. 复制：异步，同步，逻辑
   3. 时间点恢复（pitr），主动备用
   4. 表空间

 

1. 安全性
   1. 身份验证：GSSAPI, SSPI, LDAP, SCRAM-SHA-256, 证书等
   2. 强大的访问控制系统
   3. 列和行级安全性

    可扩展性
        存储的功能和程序
        程序语言：PL/PGSQL, Perl, Python (more)
        外部数据包装器：使用标准SQL接口连接到其他数据库或流
        许多提供附加功能的扩展，包括PostGIS

 

    国际化，文本搜索
        支持国际字符集，例如通过ICU校对
        全文检索

 

PostgreSQL对比Mysql：

    对比Mysql：
        PostgreSQL的稳定性极强，Innodb等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足的进步，然而很多Mysql用户都遇到过Server级的数据库丢失的场景---Mysql系统库是MyISAM的，相较而言，PG数据库在这方面要好一些。
        任何系统都有他的性能极限，在高并发读写，负载逼近极限下，PG的性能指标仍然可以维持双曲线甚至对数曲线，到顶峰之后不再下降，而MySQL明细出现一个波峰后下滑。
        PG多年在GIS领域处于优势地位，因为它有丰富的几何类型，实际上不止几何类型，PG中有大量的字典、数组、bitmap等数据类型，相比之下MaySQL就差很多，insagram就是因为PG的空间数据库扩展POSTGIS远远强于MySQL的my spatial而采用PGSQL的。
        PG的“无锁定”特性非常突出，甚至包括vacuum这样的整理数据空间的操作，这个和PGSSQL的MVCC实现有关系。
        PG的可以使用函数和条件索引，这使得PG数据库的调优非常灵活，mysql就没有这个功能，条件索引在web应用中很重要。
        PG有极其强悍的SQL编程能力，有丰富的统计函数和统计语法支持，比如分析函数（Oracle的叫法，PG里面叫Window函数），还可以用多种语言来写存储过程，对于R的支持也很好。这一点上MySQL就差的很远，很多分析功能那个都没有，腾讯内部数据存储主要是Mysql，但是主要的数据分析就是Hadoop+PGsql。
        PG的有很多中集群架构可以选择，plproxy可以支持语句级的镜像或者分片，slony可以进行字段级的同步设置，standby可以构建WAL文件级或者流式的读写分离集群，同步频率和集群策略调整方便，操作非常简单。
        一般关系型数据库的字符串有限定长度8k左右，无限长Text类型的功能受限，只能作为外部大数据访问。而PG的TEXT类型可以直接方法，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用xml xpath。用PG的话，文档数据库就可以省略了
        对于web应用来说，复制的特性很重要，Mysql到现在也是异步复制，pgsql可以做到同步，异步，半同步复制。还有mysql的同步是基于binlog复制，类似oracle golden gate, 是基于stream的复制，做到同步很困难，这种方式更加适合异地复制，pgsql的复制基于wal，可以做到同步复制。同时pgsql还提供stream复制。
        Pgsql对于numa架构的支持要比mysql强一些，比mysql对于读的性能要好些，pgsql提交可以完全异步，而mysql的内存表不够实用（表锁原因）

    点赞 1
    收藏
    分享

第二颗大白菜
发布了245 篇原创文章 · 获赞 107 · 访问量 42万+
他的留言板
关注
PostgreSQL与MySQL对比

阅读数 1万+

PostgreSQL与MySQL对比都属于开放源码的一员，性能和功能都在高速地提高和增强。MySQLAB的人们和PostgreSQL的开发者们都在尽可能地把各自的数据库改得越来越好，所以对于任何商业数... 博文
来自： tiandao2009的博客
PostgreSQL入门简介    

阅读数 1万+

一、PostgreSQL数据库安装1）yum安装# yum install postgresql2）验证postgresql安装# rpm -qa | grep postgresqlpostgresq... 博文
来自： 飞翔的博客
postgresql数据库安装

阅读数 1377

postgresql安装1，前往官网下载网址：https://www.postgresql.org/download/ 2，选择windows安装包installer如果网速慢，可以使用我的网盘资源，... 博文
来自： David的博客
关于PostgreSQL中自定义sql函数语法

阅读数 1953

在项目中需要用到一个sql递归操作，而且此sql片段在持久层使用较多，所以干脆直接把其sql功能封装成sql函数：CREATE OR REPLACE FUNCTION "public"."get_su...
 

 

 

 架构对比

MySQL： 多线程

PostgreSQL： 多进程

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

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

 

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[2]功能，就是把别的数据库的表当自己的用；

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的反应更为迅速。这样的两种背景直接导致了各自固有的优点和缺点。