postgresql性能好原因的简单介绍

MySQL与PostgreSQL相比哪个更好

MySQL

创新互联-云计算及IDC服务提供商，涵盖公有云、IDC机房租用、成都联通服务器托管、等保安全、私有云建设等企业级互联网基础服务，沟通电话：028-86922220

MySQL声称自己是最流行的开源数据库。LAMP中的M指的就是MySQL。构建在LAMP上的应用都会使用MySQL，如WordPress、Drupal等大多数php开源程序。MySQL最初是由MySQL AB开发的，然后在2008年以10亿美金的价格卖给了Sun公司，Sun公司又在2010年被Oracle收购。Oracle支持MySQL的多个版本：Standard、Enterprise、Classic、Cluster、Embedded与Community。其中有一些是免费下载的，另外一些则是收费的。其核心代码基于GPL许可，由于MySQL被控制在Oracle，社区担心会对MySQL的开源会有影响，所以开发了一些分支，比如： MariaDB和Percona。

PostgreSQL

PostgreSQL标榜自己是世界上最先进的开源数据库。PostgreSQL的一些粉丝说它能与Oracle相媲美，而且没有那么昂贵的价格和傲慢的客服。最初是1985年在加利福尼亚大学伯克利分校开发的，作为Ingres数据库的后继。PostgreSQL是完全由社区驱动的开源项目。它提供了单个完整功能的版本，而不像MySQL那样提供了多个不同的社区版、商业版与企业版。PostgreSQL基于自由的BSD/MIT许可，组织可以使用、复制、修改和重新分发代码，只需要提供一个版权声明即可。

MySQL与PostgreSQL的对比

MySQL的背后是一个成熟的商业公司，而PostgreSQL的背后是一个庞大的志愿开发组。这使得MySQL的开发过程更为慎重，而PostgreSQL的反应更为迅速。这样的两种背景直接导致了各自固有的优点和缺点。

PostgreSQL相对于MySQL的优势

1)不仅仅是关系型数据库

除了存储正常的数据类型外，还支持存储：

array，不管是一位数组还是多为数组均支持

json(hStore)和jsonb，相比使用text存储接送要高效很多

json和jsonb之间的区别

jsonb和json在更高的层面上看起来几乎是一样的，但在存储实现上是不同的。

json存储完的文本，json列会每次都解析存储的值，它不支持索引，但你可以为查询创建表达式索引。

jsonb存储的二进制格式，避免了重新解析数据结构。它支持索引，这意味着你可以不使用指定的索引就能查询任何路径。

当我们比较写入数据速度时，由于数据存储的方式的原因，jsonb会比json稍微的慢一点。json列会每次都解析存储的值，这意味着键的顺序要和输入的时候一样。但jsonb不同，以二进制格式存储且不保证键的顺序。因此，如果你有软件需要依赖键的顺序，jsonb可能不是你的应用的最佳选择。使用jsonb的优势还在于你可以轻易的整合关系型数据和非关系型数据， PostgreSQL对于mongodb这类的基于文档的数据库是个不小的威胁，毕竟如果一个表中只有一列数据的类型是半结构化的，没有必要为了迁就它而整个表的设计采用schemaless的结构。

2)支持地理信息处理扩展

PostGIS 为PostgreSQL提供了存储空间地理数据的支持，使PostgreSQL成为了一个空间数据库，能够进行空间数据管理、数量测量与几何拓扑分析。在功能上，和MYSQL对比，PostGIS具有下列优势：

O2O业务场景中的LBS业务使用PostgreSQL + PostGIS有无法比拟的优势。

3)可以快速构建REST API

PostgREST 可以方便的为任何 PostgreSQL 数据库提供完全的 RESTful API 服务。

4)支持树状结构

支持R-trees这样可扩展的索引类型，可以更方便地处理一些特殊数据。MySQL 处理树状的设计会很复杂, 而且需要写很多代码, 而 PostgreSQL 可以高效处理树结构。

5)有极其强悍的 SQL 编程能力

支持递归，有非常丰富的统计函数和统计语法支持。

MySQL：支持 CREATE PROCEDURE 和 CREATE FUNCTION 语句。存储过程可以用 SQL 和 C++ 编写。用户定义函数可以用 SQL、C 和 C++ 编写。

PostgreSQL：没有单独的存储过程，都是通过函数实现的。用户定义函数可以用 PL/pgSQL(专用的过程语言)、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 编写。

6)外部数据源支持

可以把 70 种外部数据源 (包括 Mysql, Oracle, CSV, hadoop …) 当成自己数据库中的表来查询。Postgres有一个针对这一难题的解决方案：一个名为“外部数据封装器(Foreign Data Wrapper，FDW)”的特性。该特性最初由PostgreSQL社区领袖Dave Page四年前根据SQL标准SQL/MED(SQL Management of External Data)开发。FDW提供了一个SQL接口，用于访问远程数据存储中的远程大数据对象，使DBA可以整合来自不相关数据源的数据，将它们存入Postgres数据库中的一个公共模型。这样，DBA就可以访问和操作其它系统管理的数据，就像在本地Postgres表中一样。例如，使用FDW for MongoDB，数据库管理员可以查询来自文档数据库的数据，并使用SQL将它与来自本地Postgres表的数据相关联。借助这种方法，用户可以将数据作为行、列或JSON文档进行查看、排序和分组。他们甚至可以直接从Postgres向源文档数据库写入(插入、更细或删除)数据，就像一个一体的无缝部署。也可以对Hadoop集群或MySQL部署做同样的事。FDW使Postgres可以充当企业的中央联合数据库或“Hub”。

7)没有字符串长度限制

一般关系型数据库的字符串有限定长度8k左右，无限长 TEXT 类型的功能受限，只能作为外部大数据访问。而PostgreSQL的 TEXT 类型可以直接访问，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用xml xpath。MySQL 的各种text字段有不同的限制，要手动区分 small text, middle text, large text… PostgreSQL 没有这个限制，text 能支持各种大小。

8)支持图结构数据存储

没有具体使用过，具体可以自己搜索下。参考链接：

9)支持窗口函数

窗口函数提供跨行相关的当前查询行集执行计算的能力。仅当调用跟着OVER子句的聚集函数，作为窗口函数;否则它们作为常规的聚合函数。窗口也是一种分组，但和 group by 的分组不同。窗口，可以提供分组之外，还可以执行对每个窗口进行计算。可以相像成是group by 后，然后对每个分组进行计算，而不像Group by ，只是单纯地分组。MySQL 不支持 OVER 子句, 而PostgreSQL支持。OVER 子句能简单的解决 “每组取 top 5” 的这类问题。MySQL支持的SQL语法(ANSI SQL标准)的很小一部分。不支持递归查询、通用表表达式(Oracle的with 语句)或者窗口函数(分析函数)。

10)对索引的支持更强

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

MySQL：取决于存储引擎。MyISAM：BTREE，InnoDB：BTREE。

PostgreSQL：支持 B-树、哈希、R-树和 Gist 索引。

InnoDB的表和索引都是按相同的方式存储。也就是说表都是索引组织表。这一般要求主键不能太长而且插入时的主键最好是按顺序递增，否则对性能有很大影响。PostgreSQL不存在这个问题。

索引类型方面，MySQL取决于存储引擎。MyISAM：BTREE，InnoDB：BTREE。PostgreSQL支持 B-树、哈希、R-树和 Gist 索引。

11)集群支持更好

Mysql Cluster可能与你的想象有较大差异。开源的cluster软件较少。复制(Replication)功能是异步的并且有很大的局限性。例如，它是单线程的(single-threaded)，因此一个处理能力更强的Slave的恢复速度也很难跟上处理能力相对较慢的Master。

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

另外，PostgreSQL的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。对于WEB应用来说，复制的特性很重要，mysql到现在也是异步复制，pgsql可以做到同步，异步，半同步复制。还有mysql的同步是基于binlog复制，类似oracle golden gate,是基于stream的复制，做到同步很困难，这种方式更加适合异地复制，pgsql的复制基于wal，可以做到同步复制。同时，pgsql还提供stream复制。

12)事务隔离做的更好

MySQL 的事务隔离级别 repeatable read 并不能阻止常见的并发更新, 得加锁才可以, 但悲观锁会影响性能, 手动实现乐观锁又复杂. 而 PostgreSQL 的列里有隐藏的乐观锁 version 字段, 默认的 repeatable read 级别就能保证并发更新的正确性, 并且又有乐观锁的性能。

13)对于字符支持更好一些

MySQL 里需要 utf8mb4 才能显示 emoji 的坑, PostgreSQL 没这个坑。

14)对表连接支持较完整

对表连接支持较完整，MySQL只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join)。PostgreSQL都支持。

15)存储方式支持更大的数据量

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

16)时间精度更高

MySQL对于时间、日期、间隔等时间类型没有秒以下级别的存储类型，而PostgreSQL可以精确到秒以下。

17)优化器的功能较完整

MySQL对复杂查询的处理较弱，查询优化器不够成熟，explain看执行计划的结果简单。性能优化工具与度量信息不足。

PostgreSQL很强大的查询优化器，支持很复杂的查询处理。explain返回丰富的信息。提供了一些性能视图，可以方便的看到发生在一个表和索引上的select、delete、update、insert统计信息，也可以看到cache命中率。网上有一个开源的pgstatspack工具。

18)序列支持更好

MySQL 不支持多个表从同一个序列中取 id, 而 PostgreSQL 可以。

19)对子查询支持更好

对子查询的支持。虽然在很多情况下在SQL语句中使用子查询效率低下，而且绝大多数情况下可以使用带条件的多表连接来替代子查询，但是子查询的存在在很多时候仍然不可避免。而且使用子查询的SQL语句与使用带条件的多表连接相比具有更高的程序可读性。几乎任何数据库的子查询 (subquery) 性能都比 MySQL 好。

20)增加列更加简单

MySQL表增加列，基本上是重建表和索引，会花很长时间。PostgreSQL表增加列，只是在数据字典中增加表定义，不会重建表.

MySQL相对于PostgreSQL的优势

1)MySQL比PostgreSQL更流行

流行对于一个商业软件来说，也是一个很重要的指标，流行意味着更多的用户，意味着经受了更多的考验，意味着更好的商业支持、意味着更多、更完善的文档资料。易用，很容易安装。第三方工具，包括可视化工具，让用户能够很容易入门。

2)回滚实现更优

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

3)在Windows上运行更可靠

与PostgreSQL相比，MySQL更适宜在Windows环境下运行。MySQL作为一个本地的Windows应用程序运行(在 NT/Win2000/WinXP下，是一个服务)，而PostgreSQL是运行在Cygwin模拟环境下。PostgreSQL在Windows下运行没有MySQL稳定，应该是可以想象的。

4)线程模式相比进程模式的优势

MySQL使用了线程，而PostgreSQL使用的是进程。在不同线程之间的环境转换和访问公用的存储区域显然要比在不同的进程之间要快得多。

进程模式对多CPU利用率比较高。进程模式共享数据需要用到共享内存，而线程模式数据本身就是在进程空间内都是共享的，不同线程访问只需要控制好线程之间的同步。

线程模式对资源消耗比较少。所以MySQL能支持远比PostgreSQL多的更多的连接。但PostgreSQL中有优秀的连接池软件软件，如pgbouncer和pgpool，所以通过连接池也可以支持很多的连接。

5)权限设置上更加完善

MySQL在权限系统上比PostgreSQL某些方面更为完善。PostgreSQL只支持对于每一个用户在一个数据库上或一个数据表上的 INSERT、SELECT和UPDATE/DELETE的授权，而MySQL允许你定义一整套的不同的数据级、表级和列级的权限。对于列级的权限， PostgreSQL可以通过建立视图，并确定视图的权限来弥补。MySQL还允许你指定基于主机的权限，这对于目前的PostgreSQL是无法实现的，但是在很多时候，这是有用的。

6)存储引擎插件化机制

MySQL的存储引擎插件化机制，使得它的应用场景更加广泛，比如除了innodb适合事务处理场景外，myisam适合静态数据的查询场景。

7)适应24/7运行

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

8)更加试用于简单的场景

PostgreSQL只支持堆表，不支持索引组织表，Innodb只支持索引组织表。

索引组织表的优势：表内的数据就是按索引的方式组织，数据是有序的，如果数据都是按主键来访问，那么访问数据比较快。而堆表，按主键访问数据时，是需要先按主键索引找到数据的物理位置。

索引组织表的劣势：索引组织表中上再加其它的索引时，其它的索引记录的数据位置不再是物理位置，而是主键值，所以对于索引组织表来说，主键的值不能太大，否则占用的空间比较大。

对于索引组织表来说，如果每次在中间插入数据，可能会导致索引分裂，索引分裂会大大降低插入的性能。所以对于使用innodb来说，我们一般最好让主键是一个无意义的序列，这样插入每次都发生在最后，以避免这个问题。

由于索引组织表是按一个索引树，一般它访问数据块必须按数据块之间的关系进行访问，而不是按物理块的访问数据的，所以当做全表扫描时要比堆表慢很多，这可能在OLTP中不明显，但在数据仓库的应用中可能是一个问题。

总结

MySQL从一开始就没有打算做所有事情，因而它在功能方面有一定的局限性，并不能满足一些先进应用程序的要求。MySQL对某些功能(例如引用、事务、审计等)的实现方式使得它与其他的关系型数据库相比缺少了一些可靠性。对于简单繁重的读取操作，使用PostgreSQL可能有点小题大做，同时性能也比MySQL这样的同类产品要差。除非你需要绝对的数据完整性，ACID遵从性或者设计复杂，否则PostgreSQL对于简单的场景而言有点多余。

如何你确定只在MySQL和PostgreSQL中进行选择，以下规则总是有效的：

如果你的操作系统是Windows，你应该使用MySQL。

当绝对需要可靠性和数据完整性的时候，PostgreSQL是更好的选择。

如果需要数据库执行定制程序，那么可扩展的PostgreSQL是更好的选择。

你的应用处理的是地理数据，由于R-TREES的存在，你应该使用PostgreSQL。

如果你对数据库并不了十分了解，甚至不知道事务、存储过程等究竟是什么，你应该使用MySQL。

PostgreSQL与MYSQL哪个性能好

要看调优如何。MySQL相对伯克利大学的PostgreSQL而言，还是属于较轻量级的DBMS，PG是

“对象关系数据”目前9.1版支持许多非常有效的概念，比如表继承、窗函数、灵活的触发器等等，且有许多语言作为拓展。抛开功能，数据库的性能和调优密切相关。

MySQL和PG的调优均是很复杂的。就查询而言，对经常查询的字段建立索引、分区降低索引树规模、建立部分索引（比如仅对工资高于2000元的用户建立个人所得税的索引）、使用精心设计的SQL等手段有利于提高查询效率。比如在一个按bill_time 分区的海量表中，使用 select max(id) from table 要比 select max(id) from table where bill_time = now() + '-12 hours' 慢得多。就插入、更新而言，索引越多、外键越多的表越慢是很正常的。一般，要避免一条条的更改记录，特别是在ADO之类的应用中，建议批量执行删改。在极端条件下，可以删除索引后增删改，而后重建索引。

国外特别是日本用PostgreSQL要多得多，资料也很丰富。国内MySQL用的多。PostgreSQL是原生支持地理信息系统的，PostGIS模块非常强大，是著名开源GIS OpenStreetMap 的基础。

MySQL与PostgreSQL比较 哪个数据库更好

特性 MySQL PostgreSQL

实例 通过执行 MySQL 命令（mysqld）启动实例。一个实例可以管理一个或多个数据库。一台服务器可以运行多个 mysqld 实例。一个实例管理器可以监视 mysqld 的各个实例。

通过执行 Postmaster 进程（pg_ctl）启动实例。一个实例可以管理一个或多个数据库，这些数据库组成一个集群。集群是磁盘上的一个区域，这个区域在安装时初始化并由一个目录组成，所有数据都存储在这个目录中。使用 initdb 创建第一个数据库。一台机器上可以启动多个实例。

数据库 数据库是命名的对象集合，是与实例中的其他数据库分离的实体。一个 MySQL 实例中的所有数据库共享同一个系统编目。 数据库是命名的对象集合，每个数据库是与其他数据库分离的实体。每个数据库有自己的系统编目，但是所有数据库共享 pg_databases。

数据缓冲区 通过 innodb_buffer_pool_size 配置参数设置数据缓冲区。这个参数是内存缓冲区的字节数，InnoDB 使用这个缓冲区来缓存表的数据和索引。在专用的数据库服务器上，这个参数最高可以设置为机器物理内存量的 80%。 Shared_buffers 缓存。在默认情况下分配 64 个缓冲区。默认的块大小是 8K。可以通过设置 postgresql.conf 文件中的 shared_buffers 参数来更新缓冲区缓存。

数据库连接 客户机使用 CONNECT 或 USE 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。 客户机使用 connect 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。

身份验证 MySQL 在数据库级管理身份验证。 基本只支持密码认证。 PostgreSQL 支持丰富的认证方法：信任认证、口令认证、Kerberos 认证、基于 Ident 的认证、LDAP 认证、PAM 认证

加密 可以在表级指定密码来对数据进行加密。还可以使用 AES_ENCRYPT 和 AES_DECRYPT 函数对列数据进行加密和解密。可以通过 SSL 连接实现网络加密。 可以使用 pgcrypto 库中的函数对列进行加密/解密。可以通过 SSL 连接实现网络加密。

审计 可以对 querylog 执行 grep。 可以在表上使用 PL/pgSQL 触发器来进行审计。

查询解释 使用 EXPLAIN 命令查看查询的解释计划。 使用 EXPLAIN 命令查看查询的解释计划。

备份、恢复和日志 InnoDB 使用写前（write-ahead）日志记录。支持在线和离线完全备份以及崩溃和事务恢复。需要第三方软件才能支持热备份。 在数据目录的一个子目录中维护写前日志。支持在线和离线完全备份以及崩溃、时间点和事务恢复。 可以支持热备份。

JDBC 驱动程序 可以从 参考资料 下载 JDBC 驱动程序。 可以从 参考资料 下载 JDBC 驱动程序。

表类型 取决于存储引擎。例如，NDB 存储引擎支持分区表，内存引擎支持内存表。 支持临时表、常规表以及范围和列表类型的分区表。不支持哈希分区表。 由于PostgreSQL的表分区是通过表继承和规则系统完成了，所以可以实现更复杂的分区方式。

索引类型 取决于存储引擎。MyISAM：BTREE，InnoDB：BTREE。 支持 B-树、哈希、R-树和 Gist 索引。

约束 支持主键、外键、惟一和非空约束。对检查约束进行解析，但是不强制实施。 支持主键、外键、惟一、非空和检查约束。

存储过程和用户定义函数 支持 CREATE PROCEDURE 和 CREATE FUNCTION 语句。存储过程可以用 SQL 和 C++ 编写。用户定义函数可以用 SQL、C 和 C++ 编写。 没有单独的存储过程，都是通过函数实现的。用户定义函数可以用 PL/pgSQL（专用的过程语言）、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 编写。

触发器 支持行前触发器、行后触发器和语句触发器，触发器语句用过程语言复合语句编写。 支持行前触发器、行后触发器和语句触发器，触发器过程用 C 编写。

系统配置文件 my.conf Postgresql.conf

数据库配置 my.conf Postgresql.conf

客户机连接文件 my.conf pg_hba.conf

XML 支持 有限的 XML 支持。 有限的 XML 支持。

数据访问和管理服务器 OPTIMIZE TABLE —— 回收未使用的空间并消除数据文件的碎片

myisamchk -analyze —— 更新查询优化器所使用的统计数据（MyISAM 存储引擎）

mysql —— 命令行工具

MySQL Administrator —— 客户机 GUI 工具 Vacuum —— 回收未使用的空间

Analyze —— 更新查询优化器所使用的统计数据

psql —— 命令行工具

pgAdmin —— 客户机 GUI 工具

并发控制 支持表级和行级锁。InnoDB 存储引擎支持 READ_COMMITTED、READ_UNCOMMITTED、REPEATABLE_READ 和 SERIALIZABLE。使用 SET TRANSACTION ISOLATION LEVEL 语句在事务级设置隔离级别。 支持表级和行级锁。支持的 ANSI 隔离级别是 Read Committed（默认 —— 能看到查询启动时数据库的快照）和 Serialization（与 Repeatable Read 相似 —— 只能看到在事务启动之前提交的结果）。使用 SET TRANSACTION 语句在事务级设置隔离级别。使用 SET SESSION 在会话级进行设置。

MySQL相对于PostgreSQL的劣势：

MySQL

PostgreSQL

最重要的引擎InnoDB很早就由Oracle公司控制。目前整个MySQL数据库都由Oracle控制。

BSD协议，没有被大公司垄断。

对复杂查询的处理较弱，查询优化器不够成熟

很强大的查询优化器，支持很复杂的查询处理。

只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join)。

都支持

性能优化工具与度量信息不足

提供了一些性能视图，可以方便的看到发生在一个表和索引上的select、delete、update、insert统计信息，也可以看到cache命中率。网上有一个开源的pgstatspack工具。

InnoDB的表和索引都是按相同的方式存储。也就是说表都是索引组织表。这一般要求主键不能太长而且插入时的主键最好是按顺序递增，否则对性能有很大影响。

不存在这个问题。

大部分查询只能使用表上的单一索引;在某些情况下，会存在使用多个索引的查询,但是查询优化器通常会低估其成本,它们常常比表扫描还要慢。

不存在这个问题

表增加列，基本上是重建表和索引，会花很长时间。

表增加列，只是在数据字典中增加表定义，不会重建表

存储过程与触发器的功能有限。可用来编写存储过程、触发器、计划事件以及存储函数的语言功能较弱

除支持pl/pgsql写存储过程，还支持perl、python、Tcl类型的存储过程：pl/perl，pl/python，pl/tcl。

也支持用C语言写存储过程。

不支持Sequence。

支持

不支持函数索引，只能在创建基于具体列的索引。

不支持物化视图。

支持函数索引，同时还支持部分数据索引，通过规则系统可以实现物化视图的功能。

执行计划并不是全局共享的, 仅仅在连接内部是共享的。

执行计划共享

MySQL支持的SQL语法(ANSI SQL标准)的很小一部分。不支持递归查询、通用表表达式（Oracle的with 语句）或者窗口函数（分析函数）。

都 支持

不支持用户自定义类型或域(domain)

支持。

对于时间、日期、间隔等时间类型没有秒以下级别的存储类型

可以精确到秒以下。

身份验证功能是完全内置的，不支持操作系统认证、PAM认证，不支持LDAP以及其它类似的外部身份验证功能。

支持OS认证、Kerberos 认证 、Ident 的认证、LDAP 认证、PAM 认证

不支持database link。有一种叫做Federated的存储引擎可以作为一个中转将查询语句传递到远程服务器的一个表上,不过,它功能很粗糙并且漏洞很多

有dblink，同时还有一个dbi-link的东西，可以连接到oracle和mysql上。

Mysql Cluster可能与你的想象有较大差异。开源的cluster软件较少。

复制(Replication)功能是异步的,并且有很大的局限性.例如,它是单线程的(single-threaded),因此一个处理能力更强的Slave的恢复速度也很难跟上处理能力相对较慢的Master.

有丰富的开源cluster软件支持。

explain看执行计划的结果简单。

explain返回丰富的信息。

类似于ALTER TABLE或CREATE TABLE一类的操作都是非事务性的.它们会提交未提交的事务，并且不能回滚也不能做灾难恢复

DDL也是有事务的。

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中可以不必建专门的降序索引了。

为什么PostgreSQL比MongoDB还快

PostgreSQL9.4带来了全新的NoSQL特性，并且根据EnterpriseDB的测试，其加载，插入和查询的性能都已经几倍于MongoDB了。

虽然我是PG的铁杆粉丝，但是关系数据库背负了ACID的重型装甲，在性能上居然能打败轻装上阵的NoSQL数据库总觉得有点离谱。

所以我在自己的环境里验证了一下EnterpriseDB的测试结果，并且小探一下PG取胜的原因。

1. EnterpriseDB的测试结果

以下是EnterpriseDB的测试结果(数据量为5000万)

（还可以参考这篇译文： ）

2. 我的验证结果

测试观点

为了使测试结果更加单纯，我准备单纯比拼CPU消耗(尽量排除IO和网络的干扰)，设定以下测试条件。

1）所有数据都要放进内存

2）C/S都跑在同一台单机上

所以，只在单机上进行10万条小数据量的测试。

注）EnterpriseDB的测试环境是32G内存的Amazon Web Services M3.2XLARGE实例，总数据量超过内存了。

测试环境

测试环境为个人PC上的VMware虚拟机

PC

CPU:Intel Core i5-3470 3.2G(4核)

MEM:6GB

SSD:OCZ-VERTEX4 128GB(VMware虚拟机所在磁盘，非系统盘)

OS:Win7

VMware虚拟机

CPU:4核

MEM:1GB

OS:CentOS 6.5

PG:PostgreSQL 9.4.0(shared_buffers = 428MB,其他是默认值)

MG:MongoDB 3.0.2

测试步骤

测试步骤非常简单,可以参考：

但是，在测试前，有些东西要改。

1)把数据量减小到10万

pg_nosql_benchmark-master/pg_nosql_benchmark:

declare -a json_rows=(10000000)

==

declare -a json_rows=(100000)

2)修改mongo的一处脚本(注)

pg_nosql_benchmark-master/lib/mongo_func_lib.sh:

collectionsize="$(echo ${output}|awk -F"," '{print $5}'|cut -d":" -f2)"

==

collectionsize="$(echo ${output}|awk -F"," '{print $6}'|cut -d":" -f2)"

注)pg_nosql_benchmark原来是基于MongoDB 2.6设计的，MongoDB 3.0的db.json_tables.stats()输出可能变了，所以这边要修改一下。

如何提高postgresql查询性能

一、使用EXPLAIN：

PostgreSQL为每个查询都生成一个查询规划，因为选择正确的查询路径对性能的影响是极为关键的。PostgreSQL本身已经包含了一个规划器用于寻找最优规划，我们可以通过使用EXPLAIN命令来查看规划器为每个查询生成的查询规划。

PostgreSQL中生成的查询规划是由1到n个规划节点构成的规划树，其中最底层的节点为表扫描节点，用于从数据表中返回检索出的数据行。然而，不同

的扫描节点类型代表着不同的表访问模式，如：顺序扫描、索引扫描，以及位图索引扫描等。如果查询仍然需要连接、聚集、排序，或者是对原始行的其它操作，那

么就会在扫描节点"之上"有其它额外的节点。并且这些操作通常都有多种方法，因此在这些位置也有可能出现不同的节点类型。EXPLAIN将为规划树中的每

个节点都输出一行信息，显示基本的节点类型和规划器为执行这个规划节点计算出的预计开销值。第一行(最上层的节点)是对该规划的总执行开销的预计，这个数

值就是规划器试图最小化的数值。

这里有一个简单的例子，如下：

复制代码 代码如下:

EXPLAIN SELECT * FROM tenk1;

QUERY PLAN

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

Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)

EXPLAIN引用的数据是：

1). 预计的启动开销(在输出扫描开始之前消耗的时间，比如在一个排序节点里做排续的时间)。

2). 预计的总开销。

3). 预计的该规划节点输出的行数。

4). 预计的该规划节点的行平均宽度(单位：字节)。

这里开销(cost)的计算单位是磁盘页面的存取数量，如1.0将表示一次顺序的磁盘页面读取。其中上层节点的开销将包括其所有子节点的开销。这里的输出

行数(rows)并不是规划节点处理/扫描的行数，通常会更少一些。一般而言，顶层的行预计数量会更接近于查询实际返回的行数。

现在我们执行下面基于系统表的查询：

复制代码 代码如下:

SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';

从查询结果中可以看出tenk1表占有358个磁盘页面和10000条记录，然而为了计算cost的值，我们仍然需要知道另外一个系统参数值。

复制代码 代码如下:

postgres=# show cpu_tuple_cost;

cpu_tuple_cost

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

0.01

(1 row)

cost = 358(磁盘页面数) + 10000(行数) * 0.01(cpu_tuple_cost系统参数值)

下面我们再来看一个带有WHERE条件的查询规划。

复制代码 代码如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 7000;

QUERY PLAN

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

Seq Scan on tenk1 (cost=0.00..483.00 rows=7033 width=244)

Filter: (unique1 7000)

EXPLAIN的输出显示，WHERE子句被当作一个"filter"应用，这表示该规划节点将扫描表中的每一行数据，之后再判定它们是否符合过滤的条

件，最后仅输出通过过滤条件的行数。这里由于WHERE子句的存在，预计的输出行数减少了。即便如此，扫描仍将访问所有10000行数据，因此开销并没有

真正降低，实际上它还增加了一些因数据过滤而产生的额外CPU开销。

上面的数据只是一个预计数字，即使是在每次执行ANALYZE命令之后也会随之改变，因为ANALYZE生成的统计数据是通过从该表中随机抽取的样本计算的。

如果我们将上面查询的条件设置的更为严格一些的话，将会得到不同的查询规划，如：

复制代码 代码如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 100;

QUERY PLAN

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

Bitmap Heap Scan on tenk1 (cost=2.37..232.35 rows=106 width=244)

Recheck Cond: (unique1 100)

- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)

Index Cond: (unique1 100)

这里，规划器决定使用两步规划，最内层的规划节点访问一个索引，找出匹配索引条件的行的位置，然后上层规划节点再从表里读取这些行。单独地读取数据行比顺

序地读取它们的开销要高很多，但是因为并非访问该表的所有磁盘页面，因此该方法的开销仍然比一次顺序扫描的开销要少。这里使用两层规划的原因是因为上层规

划节点把通过索引检索出来的行的物理位置先进行排序，这样可以最小化单独读取磁盘页面的开销。节点名称里面提到的"位图(bitmap)"是进行排序的机

制。

现在我们还可以将WHERE的条件设置的更加严格，如：

复制代码 代码如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 3;

QUERY PLAN

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

Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.00 rows=2 width=244)

Index Cond: (unique1 3)

在该SQL中，表的数据行是以索引的顺序来读取的，这样就会令读取它们的开销变得更大，然而事实上这里将要获取的行数却少得可怜，因此没有必要在基于行的物理位置进行排序了。

现在我们需要向WHERE子句增加另外一个条件，如：

复制代码 代码如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 3 AND stringu1 = 'xxx';

QUERY PLAN

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

Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.01 rows=1 width=244)

Index Cond: (unique1 3)

Filter: (stringu1 = 'xxx'::name)

新增的过滤条件stringu1 = 'xxx'只是减少了预计输出的行数，但是并没有减少实际开销，因为我们仍然需要访问相同数量的数据行。而该条件并没有作为一个索引条件，而是被当成对索引结果的过滤条件来看待。

如果WHERE条件里有多个字段存在索引，那么规划器可能会使用索引的AND或OR的组合，如：

复制代码 代码如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 100 AND unique2 9000;

QUERY PLAN

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

Bitmap Heap Scan on tenk1 (cost=11.27..49.11 rows=11 width=244)

Recheck Cond: ((unique1 100) AND (unique2 9000))

- BitmapAnd (cost=11.27..11.27 rows=11 width=0)

- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)

Index Cond: (unique1 100)

- Bitmap Index Scan on tenk1_unique2 (cost=0.00..8.65 rows=1042 width=0)

Index Cond: (unique2 9000)

这样的结果将会导致访问两个索引，与只使用一个索引，而把另外一个条件只当作过滤器相比，这个方法未必是更优。

现在让我们来看一下基于索引字段进行表连接的查询规划，如：

复制代码 代码如下:

EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 100 AND t1.unique2 = t2.unique2;

QUERY PLAN

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

Nested Loop (cost=2.37..553.11 rows=106 width=488)

- Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)

Recheck Cond: (unique1 100)

- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)

Index Cond: (unique1 100)

- Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244)

Index Cond: ("outer".unique2 = t2.unique2)

从查询规划中可以看出(Nested

Loop)该查询语句使用了嵌套循环。外层的扫描是一个位图索引，因此其开销与行计数和之前查询的开销是相同的，这是因为条件unique1

100发挥了作用。 这个时候t1.unique2 =

t2.unique2条件子句还没有产生什么作用，因此它不会影响外层扫描的行计数。然而对于内层扫描而言，当前外层扫描的数据行将被插入到内层索引扫描

中，并生成类似的条件t2.unique2 = constant。所以，内层扫描将得到和EXPLAIN SELECT * FROM tenk2

WHERE unique2 = 42一样的计划和开销。最后，以外层扫描的开销为基础设置循环节点的开销，再加上每个外层行的一个迭代(这里是 106

* 3.01)，以及连接处理需要的一点点CPU时间。

如果不想使用嵌套循环的方式来规划上面的查询，那么我们可以通过执行以下系统设置，以关闭嵌套循环，如：

复制代码 代码如下:

SET enable_nestloop = off;

EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 100 AND t1.unique2 = t2.unique2;

QUERY PLAN

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

Hash Join (cost=232.61..741.67 rows=106 width=488)

Hash Cond: ("outer".unique2 = "inner".unique2)

- Seq Scan on tenk2 t2 (cost=0.00..458.00 rows=10000 width=244)

- Hash (cost=232.35..232.35 rows=106 width=244)

- Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)

Recheck Cond: (unique1 100)

- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)

Index Cond: (unique1 100)

这个规划仍然试图用同样的索引扫描从tenk1里面取出符合要求的100行，并把它们存储在内存中的散列(哈希)表里，然后对tenk2做一次全表顺序扫

描，并为每一条tenk2中的记录查询散列(哈希)表，寻找可能匹配t1.unique2 =

t2.unique2的行。读取tenk1和建立散列表是此散列联接的全部启动开销，因为我们在开始读取tenk2之前不可能获得任何输出行。

此外，我们还可以用EXPLAIN ANALYZE命令检查规划器预估值的准确性。这个命令将先执行该查询，然后显示每个规划节点内实际运行时间，以及单纯EXPLAIN命令显示的预计开销，如：

复制代码 代码如下:

EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 100 AND t1.unique2 = t2.unique2;

QUERY PLAN

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

Nested Loop (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1)

- Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1)

Recheck Cond: (unique1 100)

- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37

rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1)

Index Cond: (unique1 100)

- Index Scan using tenk2_unique2 on tenk2 t2

(cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1

loops=100)

Index Cond: ("outer".unique2 = t2.unique2)

Total runtime: 14.452 ms

注意"actual time"数值是以真实时间的毫秒来计算的，而"cost"预估值是以磁盘页面读取数量来计算的，所以它们很可能是不一致的。然而我们需要关注的只是两组数据的比值是否一致。

在一些查询规划里，一个子规划节点很可能会运行多次，如之前的嵌套循环规划，内层的索引扫描会为每个外层行执行一次。在这种情况下，"loops"将报告

该节点执行的总次数，而显示的实际时间和行数目则是每次执行的平均值。这么做的原因是令这些真实数值与开销预计显示的数值更具可比性。如果想获得该节点所

花费的时间总数，计算方式是用该值乘以"loops"值。

EXPLAIN ANALYZE显示的"Total runtime"包括执行器启动和关闭的时间，以及结果行处理的时间，但是它并不包括分析、重写或者规划的时间。

如果EXPLAIN命令仅能用于测试环境，而不能用于真实环境，那它就什么用都没有。比如，在一个数据较少的表上执行EXPLAIN，它不能适用于数量很

多的大表，因为规划器的开销计算不是线性的，因此它很可能对大些或者小些的表选择不同的规划。一个极端的例子是一个只占据一个磁盘页面的表，在这样的表

上，不管它有没有索引可以使用，你几乎都总是得到顺序扫描规划。规划器知道不管在任何情况下它都要进行一个磁盘页面的读取，所以再增加几个磁盘页面读取用

以查找索引是毫无意义的。

二、批量数据插入：

有以下几种方法用于优化数据的批量插入。

1. 关闭自动提交：

在批量插入数据时，如果每条数据都被自动提交，当中途出现系统故障时，不仅不能保障本次批量插入的数据一致性，而且由于有多次提交操作的发生，整个插入效

率也会受到很大的打击。解决方法是，关闭系统的自动提交，并且在插入开始之前，显示的执行begin

transaction命令，在全部插入操作完成之后再执行commit命令提交所有的插入操作。

2. 使用COPY：

使用COPY在一条命令里装载所有记录，而不是一系列的INSERT命令。COPY命令是为装载数量巨大的数据行优化过的，它不像INSERT命令那样灵

活，但是在装载大量数据时，系统开销也要少很多。因为COPY是单条命令，因此在填充表的时就没有必要关闭自动提交了。

3. 删除索引：

如果你正在装载一个新创建的表，最快的方法是创建表，用COPY批量装载，然后创建表需要的任何索引。因为在已存在数据的表上创建索引比维护逐行增加要快。当然在缺少索引期间，其它有关该表的查询操作的性能将会受到一定的影响，唯一性约束也有可能遭到破坏。

4. 删除外键约束：

和索引一样，"批量地"检查外键约束比一行行检查更加高效。因此，我们可以先删除外键约束，装载数据，然后在重建约束。

5. 增大maintenance_work_mem：

在装载大量数据时，临时增大maintenance_work_mem系统变量的值可以改进性能。这个系统参数可以提高CREATE

INDEX命令和ALTER TABLE ADD FOREIGN KEY命令的执行效率，但是它不会对COPY操作本身产生多大的影响。

6. 增大checkpoint_segments：

临时增大checkpoint_segments系统变量的值也可以提高大量数据装载的效率。这是因为在向PostgreSQL装载大量数据时，将会导致

检查点操作(由系统变量checkpoint_timeout声明)比平时更加频繁的发生。在每次检查点发生时，所有的脏数据都必须flush到磁盘上。

通过提高checkpoint_segments变量的值，可以有效的减少检查点的数目。

7. 事后运行ANALYZE：

在增加或者更新了大量数据之后，应该立即运行ANALYZE命令，这样可以保证规划器得到基于该表的最新数据统计。换句话说，如果没有统计数据或者统计数据太过陈旧，那么规划器很可能会选择一个较差的查询规划，从而导致查询效率过于低下。