PostgreSQL与MySQL两大开源数据库比较
2010-03-20 14:57:23 阿炯

当谈到开源数据库时,MySQL获 得了业界大部分的注意力,它是一个易于使用的数据库,同时有许多开源的Web应用程序都是直接在它上面开发的。另外一种主要的开源数据库是PostgreSQL,虽然它也是众所周知的,但是却没有获得像MySQL所得到的认可。这是很不幸的,因为在这两者中,相比MySQL,PostgreSQL能提供更加安全、更加可靠、数据也更加完整的服务。

但这同样也有一定的缺陷。PostgreSQL对于设置和使用的要求比较高,它利用的是特殊权限、底层操作系统的安全性以及数据库内提供的角色 (roles)和特权。如果你对这些东西不够了解的话,会使得PostgreSQL的使用变得困难。但一旦你掌握了它们,你就可以像使用MySQL一样很容易的使用PostgreSQL。

与MySQL相类似,PostgreSQL的工作基于这样一种原则,即特定的用户有特定的数据访问权限。在PostgreSQL里,这些被称之为 角色(roles),通过采用CREATE ROLE, ALTER ROLE, and DROP ROLE这些语句可以创建和管理它们。和MySQL不同的是,这些角色可以映射和绑定到系统的用户,这就意味着它可以利用不同形式的体系认 证:ident server authentication、LDAP server authentication、PAM和Kerberos。而对于本地连接,也可以通过使用这些文件体系的权限来确定谁可以访问Unix域套接字,以及它的位置。访问控制的方法是使用pg_hba.conf。对于身份识别,也可采用pg_ident.conf;这可以用于将数据库用户映射到本地用户。假如用户joe是允许访问的PostgreSQL用户数据库joe和电子商务。pg_hba.conf文件包含如下:
# TYPE      DATABASE      USER      CIDR-ADDRESS      METHOD
local      all      all            ident map=esite
host     all      all      127.0.0.1/32      ident map=esite

而pg_ident.conf可能会包括:
# MAPNAME    SYSTEM-USERNAME    PG-USERNAME
esite    joe    joe
esite    joe    ecommerce
esite    postgres    joe

这就允许系统用户joe作为joe或者ecommerce.来访问数据库。它也允许系统postgres用户以joe的身份连 接到数据库。同时强化对于名字为esite识别方法的映射类型,如在pg_ident.conf中所定义的那样。这就意味着在本地类型(Unix域套 接字)和本地TCP/IP地址(127.0.0.1)中,只有joe和postgres能够连接到数据库。没有其他的用户有权来访问它。

这种识别方法是一种很好的方式,用于控制哪一个本地用户可以连接到哪一个数据库。这种方法只对本地主机(TCP/IP 或者UNIX  域套接字)的连接起作用,而对于远程控制是无效的。虽然对于那些习惯使用MySQL的人来说这看起来似乎有点混乱,但目前对具有这种认证精度的数据库的需求确是实实在在的。MySQL只支持基于登录证书的身份验证,而这些证书只由数据库本身进行储存和管理。

另一方面,PostgreSQL不仅允许这种采用密码机制的身份验证,而且允许采用无密码的身份验证。这些验证机制包括像讨论的认证机制、PAM(其中规定了许多有意思的验证方案)以及LDAP 和 Kerberos。Kerberos是一种在MySQL使用多年的方式(事实上,出现于2004年11月的MySQL错误#6733就需要得到 Kerberos的支持)。许多公司的偏好Kerberos和LDAP密码存储目录,这就使得PostgreSQL成为了一个引人关注的数据库。

还有其他很多特色使得PostgreSQL更加适用于企业。虽然安全性很重要,但除此之外PostgreSQL对于数据完整性、访问控制、ACID 规则以及其他一些重要方面的关注和支持,都很好的解释了在众多的数据库管理者中为什么PostgreSQL享有如此高的赞誉。

相关阅读

PostgreSQL是以加州大学伯克利分校计算机系开发的 POSTGRES,版本 4.2为基础的对象关系型数据库管理系统(ORDBMS)。POSTGRES 领先的许多概念只是在非常迟的时候才出现在商业数据库中。其最初的伯克利的代码的一个开放源码的继承人,它支持大部分 SQL:2003 标准并且提供了许多其他现代特性:复杂查询、外键、触发器、视图、事务完整性、多版本并发控制

同样,PostgreSQL 可以用许多方法扩展,比如:通过增加新的:数据类型、函数、操作符、聚集函数、索引方法、过程语言。并且,因为许可证的灵活,任何人都可以以任何目的免费使用,修改,和分发 PostgreSQL,不管是私用,商用,还是学术研究使用。

Mysql 在一定量数据后(一般观点是 mysql 单表 200-300万时性能最好,数据再多性能就开始下降),性能下降很快,且大数据量情况下,mysql 稳定性/数据可靠性是问题。MySQL 在 1998 年就提供了 Windows 版本,而 PostgreSQL 则到了 2005 年才正式推出。之前读到的原因是 Windows 早期的版本一直无法很好支持 PostgreSQL 的进程模型。

1.连接 PG,一定需要指定数据库,而 MySQL 就不需要。psql 大家碰到的问题是尝试连接时报错 FATAL Database xxx does not exist。而 mysql 碰到的问题是连接上去后,执行查询再提示 no database selected。

2.访问控制的配置,首先 PG 和 MySQL 都有用户系统,但 PG 还要配置一个额外的 pg_hba (host-based authentication) 文件。

3.MySQL 的层级关系是:实例 -> 数据库 -> 表,而 PG 的关系是:实例(也叫集群)> 数据库 > Schema > 表。PG 多了一层,而且从行为表现上,PG 的 schema 类似于 MySQL 数据库,而 PG 的数据库类似于 MySQL 的实例。PG 的这个额外层级在绝大多数场景是用不到的,大家从习惯上还是喜欢用数据库作为分割边界,而不是 schema。所以往往 PG 数据库下,也就一个 public schema,这多出来的一层 schema 就是额外的负担。

4.因为上面机制的不同,PG 是无法直接做跨库查询的,早年要通过 dblink 插件,后来被 FDW (foreign data wrapper) 取代。

5.PG 有更加全面的权限体系,数据库对象都有明确的所有者,但这也导致在做测试时,更经常碰到权限问题。

LAMP 技术栈:Linux + Apache + MySQL + PHP,诞生于 1998 年,和互联网崛起同步,LAMP 技术栈的普及也带火了 MySQL。这个技术栈的绑定是如此之深,所以时至今日,MySQL 官方客户端 MySQL Workbench 也还是不及 phpMyAdmin 流行。Mark Callaghan 一开始在 Google 的 MySQL 团队,他们给生态做了很多贡献,后来 Google 内部开始用 Spanner 替换 MySQL,Mark 他们就跑到了 Facebook 继续做,又进一步发展了 MySQL 的生态,像当时互联网公司都需要的高可用方案 MHA (Master High Availability) 就是 Mark 在 FB 时期打磨成熟的。当时整个互联网技术以 Google 为瞻,传播链差不多是 Google > Facebook/Twitter > 国内互联网大厂 > 其他中小厂。MySQL 在互联网公司的垄断就这样形成了。

有了大量使用后,自然就有人去解决碰到的各种问题。先是 InnoDB 横空出世,解决了事务和性能问题。主从,中间件分库分表方案解决了海量服务的扩展和高可用问题。各种 MySQL 相关书籍,培训资料也冒了出来,应该不少人都读过高性能 MySQL (High Performance MySQL) 这本书。业界有 Percona 这样专注于做 MySQL 技术咨询的公司,他们还研发了一系列工具,比如做大表变更的 pt-online-schema-change(后来 GitHub 还发布了改良版 gh-ost),做备份的 xtrabackup。

国内也做了不少的贡献,阿里给上游贡献了许多 replication 的改进。SQL 审核优化这块,有去哪儿研发的 Inception,小米团队的 SOAR。Parser 有 PingCAP 的 MySQL Parser。相对而言 PG 在工具链的生态还是差不少,比如 PG 生态里没有开箱即用的 Parser,没有 Parser 也就无法做 SQL 审核。Bytebase 在实现相关功能时,就只能从头开始做。当然这也成为了 Bytebase 产品的核心竞争力,我们是市面上对 PG 变更审核,查询脱敏支持最好的工具,除了大表变更外,功能完全对标 MySQL。


Postgresql 8.x 的官方说明如下:
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

mysql 目前只是适合跑 web 应用,对于海量数据存储、数据仓库不合适。

postgresql 在国内早有人做大型商业应用。对于大型应用,PostgreSQL 还是合适的。以下是PostgreSQL 中文官方手册维护者 何伟平 laser 对于一个 大型应用 的回复:

怎么说呢,实际上,我现在手头就有一个庞大的数据库,数据+索引已经超过500G了,数据总量超过30亿行数据,每天会忙12小时左右。

基本上,我觉得,首先、检查你的IO投资,不要在硬盘上吝啬。

第二、仔细分析自己的瓶颈是什么, 很多事后,我们并不一定需要database replicate。

第三、适当使用数据的切割。

简单归结一句话: 适当的硬件投资和规划加上合适的软件结构,具体的事情需要具体分析。

介绍一下我们那个500G的大库:
单机HP DL385,16G内存和6块SCSI磁盘,20块SATA磁盘盘阵,
盘阵是HP DL320S,(MSA1500),相当便宜。

我们的构造是SCSI是RAID5,跑XFS,SATA,RAID5,跑EXT3,目前,性能非常满意(我们的角度),有些update语句,一次会更新几百万行数据,那么我们有些程序,一天要更新几十次,基本上也可以在1000s之内完成。每天vacuum一次,在低负载的时段,大概需要120min~200min,用slony做数据的备份,备份到一台大硬盘的IDE机器上(1.5T硬盘,别惊讶,现在750G硬盘才3500块钱。)。这台机器是数据挖掘的,并发数不多,所以我们没有做负载方面的均衡。

Mysql 不适应大量数据,密集运算,重型负载应用。至少在目前,开源数据库的质量还不能与商业数据抗衡。

在 2006-11-29 有个国外第3方服务器机构(Tweakers.net)做的 Mysql 4.1.20 ,Mysql 5.1.20a 与 PostgreSQL 8.2 的对比性能测试(在不同配置的机器上运行),你可以参考。

Tweakers.net, a dutch community of online tweakers, benchmarked their potential new server with PostgreSQL 8.2 vs several versions of MySQL 4.1.20 and MySQL 5.1.20a

图文测试统计报告可占击此处

如果要求更高,推荐使用性能优异的 bizgres 集群;2亿的单表,slect crount(*) from table; 来全表扫描。

同配置单主机硬件:内存8G,每台机器是两个双核的AMD86,磁盘Raid0+1。Oracle 10g用了50秒,postgresql的普通集群用来一分40秒。改用Bizgers 这个 PostgreSQL  高性能集群(使用)后,速度是 Oracle 的4倍。 mysql超过1亿行慢得和蜗牛一样。


MySQL 和 PostgreSQL 的全方位比较
以下链接的文章从 7 个方面详细阐述了 MySQL 和 PostgreSQL 这两个开源数据库之间的差异。
这 7 个方面包括:架构、性能、ACID 事务、功能特性、授权协议、开发、文化

这篇文章没有任何偏向性,完全基于客观的角度对二者进行比较,文章较长,如果你正在为使用 MySQL 或者是 PostgreSQL 而烦恼,不妨阅读一下。

MySQL_vs_PostgreSQL


为了说明PostgreSQL的功能,下面简要对比一下它与MySQL数据库之间的差异:
1. 对子查询的优化表现不佳
2. 对复杂查询的处理较弱
3. 查询优化器不够成熟
4. 性能优化工具与度量信息不足
5. 审计功能相对较弱
6. 安全功能不成熟,甚至可以说很粗糙.没有用户组与角色的概念,没有回收权限的功能(仅仅可以授予权限).当一个用户从不同的主机/网络以同样地用户名/密码登录之后,可能被当作完全不同的用户来处理.没有类似于Oracle的内置的加密功能
7. 身份验证功能是完全内置的.不支持LDAP,Active Directory以及其它类似的外部身份验证功能
8. Mysql Cluster可能与你的想象有较大差异
9. 存储过程与触发器的功能有限
10. 垂直扩展性较弱
11. 不支持MPP(大规模并行处理)
12. 支持SMP(对称多处理器),但是如果每个处理器超过4或8个核(core)时,Mysql的扩展性表现较差
13. 对于时间、日期、间隔等时间类型没有秒以下级别的存储类型
14. 可用来编写存储过程、触发器、计划事件以及存储函数的语言功能较弱
15. 没有基于回滚(roll-back)的恢复功能,只有前滚(roll-forward)的恢复功能
16. 不支持快照功能
17. 不支持数据库链(database link).有一种叫做Federated的存储引擎可以作为一个中转将查询语句传递到远程服务器的一个表上,不过,它功能很粗糙并且漏洞很多
18. 数据完整性检查非常薄弱,即使是基本的完整性约束,也往往不能执行
19. 优化查询语句执行计划的优化器提示非常少
20. 只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join)
21. 大部分查询只能使用表上的单一索引;在某些情况下,会存在使用多个索引的查询,但是查询优化器通常会低估其成本,它们常常比表扫描还要慢
22. 不支持位图索引(bitmap index).每种存储引擎都支持不同类型的索引.大部分存储引擎都支持B-Tree索引
23. 管理工具较少,功能也不够成熟
24. 没有成熟能够令人满意的IDE工具与调试程序.可能不得不在文本编辑器中编写存储过程,并且通过往表(调试日志表)中插入记录的方式来做调试
25. 每个表都可以使用一种不同的存储引擎
26. 每个存储引擎在行为表现、特性以及功能上都可能有很大差异
27. 大部分存储引擎都不支持外键
28. 默认的存储引擎(MyISAM)不支持事务,并且很容易损坏
29. 最先进最流行的存储引擎InnoDB由Oracle拥有
30. 有些执行计划只支持特定的存储引擎.特定类型的Count查询,在这种存储引擎中执行很快,在另外一种存储引擎中可能会很慢
31. 执行计划并不是全局共享的,,仅仅在连接内部是共享的.
32. 全文搜索功能有限, 只适用于非事务性存储引擎. Ditto用于地理信息系统/空间类型和查询.
33. 没有资源控制.一个完全未经授权的用户可以毫不费力地耗尽服务器的所有内存并使其崩溃,或者可以耗尽所有CPU资源.
34. 没有集成商业智能(business intelligence), OLAP **数据集等软件包.
35. 没有与Grid Control类似的工具.
36. 没有类似于RAC的功能.如果你问”如何使用Mysql来构造RAC”,只能说你问错了问题.
37. 不支持用户自定义类型或域(domain).
38. 每个查询支持的连接的数量最大为61.
39. MySQL支持的SQL语法(ANSI SQL标准)的很小一部分.不支持递归查询、通用表表达式(Oracle的with 语句)或者窗口函数(分析函数).支持部分类似于Merge或者类似特性的SQL语法扩展,不过相对于Oracle来讲功能非常简单.
40. 不支持功能列(基于计算或者表达式的列,Oracle11g 开始支持计算列,以及早期版本就支持虚列(rownum,rowid)).
41. 不支持函数索引,只能在创建基于具体列的索引.
42. 不支持物化视图.
43. 不同的存储引擎之间,统计信息差别很大,并且所有的存储引擎支持的统计信息都只支持简单的基数(cardinality)与一定范围内的记录数(rows-in-a-range). 换句话说,数据分布统计信息是有限的.更新统计信息的机制也不多.
44. 没有内置的负载均衡与故障切换机制.
45. 复制(Replication)功能是异步的,并且有很大的局限性.例如,它是单线程的(single-threaded),因此一个处理能力更强的Slave的恢复速度也很难跟上处理能力相对较慢的Master.
46. Cluster并不如想象的那么完美.或许我已经提过这一点,但是这一点值得再说一遍.
47. 数据字典(INFORMATION_SCHEMA)功能很有限,并且访问速度很慢(在繁忙的系统上还很容易发生崩溃).
48. 不支持在线的Alter Table操作.
49. 不支持Sequence.
50. 类似于ALTER TABLE或CREATE TABLE一类的操作都是非事务性的.它们会提交未提交的事务,并且不能回滚也不能做灾难恢复.Schame被保存在文件系统上,这一点与它使用的存储引擎无关.

PostgreSQL数据库可以解决以上问题中的:
1. 对子查询的优化表现不佳
2. 对复杂查询的处理较弱
3. 查询优化器不够成熟
PostgreSQL完全支持SQL-92标准,对SQL的支持也很全面,可以支持复杂的SQL查询。

4. 性能优化工具与度量信息不足
PostgreSQL提供了执行计划和详细的cost值,可以方便看到SQL的执行效率。

9. 存储过程与触发器的功能有限.
PostgreSQL提供了完善的存储过程和触发器支持。

11. 不支持MPP(大规模并行处理)
而PostgreSQL是类似Oracle数据库的架构,是多进程的架构,而不像MySQL是多线程的架构,所以能支持MPP。

18. 数据完整性检查非常薄弱,即使是基本的完整性约束,也往往不能执行。
PostgreSQL提供完善的数据完整性检查机制,支持外键。

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

21. 大部分查询只能使用表上的单一索引;在某些情况下,会存在使用多个索引的查询,但是查询优化器通常会低估其成本,它们常常比表扫描还要慢.
PostgreSQL 数据不存在这个问题,假设表T的两个字段col1的col2上有两个索引,idx_1和idx_2,那么select * from t where col1=:a and col2=:b;查询时,PostgreSQL数据库有可能把这个查询转化为select * from t where col1=:a intersect select * from t where col2=:b,这样两个索引都可以使用上。

25. 每个表都可以使用一种不同的存储引擎.
26. 每个存储引擎在行为表现、特性以及功能上都可能有很大差异.
27. 大部分存储引擎都不支持外键.
28. 默认的存储引擎(MyISAM)不支持事务,并且很容易损坏.
29. 最先进最流行的存储引擎InnoDB由Oracle拥有.
30. 有些执行计划只支持特定的存储引擎.特定类型的Count查询,在这种存储引擎中执行很快,在另外一种存储引擎中可能会很慢.
PostgreSQL只有一种存储引擎,所以不存在上面的情况。而PostgreSQL支持完善的事务。

32. 全文搜索功能有限, 只适用于非事务性存储引擎. Ditto用于地理信息系统/空间类型和查询.
PostgreSQL数据库支持全文搜索,支持更多类型的索引,如B-tree,R-tree, Hash, GiST, GIN,R-tree,GIST,GIN索引可用于空间类型和查询。

37. 不支持用户自定义类型或域(domain).
PostgreSQL支持丰富的类型,同时也支持自定义类型。

39. MySQL支持的SQL语法(ANSI SQL标准)的很小一部分.不支持递归查询、通用表表达式(Oracle的with 语句)或者窗口函数(分析函数).支持部分类似于Merge或者类似特性的SQL语法扩展,不过相对于Oracle来讲功能非常简单.
这些PostgreSQL数据库都支持,如窗口函数。

41. 不支持函数索引,只能在创建基于具体列的索引.
PostgreSQL支持函数索引

49. 不支持Sequence.
PostgreSQL支持sequence

50. 类似于ALTER TABLE或CREATE TABLE一类的操作都是非事务性的.它们会提交未提交的事务,并且不能回滚也不能做灾难恢复.Schame被保存在文件系统上,这一点与它使用的存储引擎无关,而PostgreSQL不存在这个问题。

PostgreSQL 的一些你可能不知道的功能


PostgreSQL包含许多重要的功能。他们中的许多人都非常知名。其他人可以是非常有用的,但没有广泛赞赏。以下是我们首选的PostgreSQL功能,您可能没有仔细看过,但实际上应该这样做,因为它们可以帮助您更快地将代码投入生产,使操作更轻松,并且通常可以使用更少的代码和劳动来完成任务。

发布/订阅通知
PostgreSQL带有一个简单的非持久基于主题的发布 - 订阅通知系统。它不是Kafka,但功能确实支持常见用例。关于特定主题的消息可以广播给正在监听该主题的所有连接的订阅者。这些消息被Postgres服务器推送给侦听客户端。轮询不是必需的,但您的数据库驱动程序应支持异步向应用程序传递通知。通知由主题名称和有效负载组成最多约8000个字符。有效载荷通常是一个JSON字符串,但它当然可以是任何东西。您可以使用NOTIFY命令发送通知:
NOTIFY 'foo_events','{"userid":42,"action":"grok"}'

或者 pg_notify 函数:
SELECT pg_notify'foo_events','{"userid":42,"action":"grok"}';

订阅发生在 LISTEN 命令中,但通常您必须使用驱动程序特定的API。

表继承
假如有一张叫 "invoices发票" 的表。你现在想支持 "freeoa invoicesFreeOA发票",这种发票在原来的发票之上添加了一些字段。该如何建模?是在 invoices 表中添加若干可空字段,还是增加一个可空的 JSON 字段?不妨试试继承功能:
CREATE TABLE invoices (
    invoice_number   int  NOT NULL PRIMARY KEY,
    issued_on        date NOT NULL DEFAULT now()
);

CREATE TABLE freeoa_invoices (
    department_id    text NOT NULL
) INHERITS (invoices);

上述模型反映出了FreeOA发票就是发票,但比发票多一些属性的情况。上面的 "freeoa_invoices" 表总共有 3 列:
test=# \d invoices
                  Table "public.invoices"
     Column     |  Type   | Collation | Nullable | Default
----------------+---------+-----------+----------+---------
 invoice_number | integer |           | not null |
 issued_on      | date    |           | not null | now()
Indexes:
    "invoices_pkey" PRIMARY KEY, btree (invoice_number)
Number of child tables: 1 (Use \d+ to list them.)

test=# \d freeoa_invoices
            Table "public.freeoa_invoices"
     Column     |  Type   | Collation | Nullable | Default
----------------+---------+-----------+----------+---------
 invoice_number | integer |           | not null |
 issued_on      | date    |           | not null | now()
 department_id  | text    |           | not null |
Inherits: invoices

为它添加数据行就跟独立表一样:
INSERT INTO invoices (invoice_number) VALUES (100);

INSERT INTO freeoa_invoices(invoice_number, department_id) VALUES (101, 'DOD');

不过观察一下 SELECT 时的情况:
test=# SELECT * FROM freeoa_invoices;
 invoice_number | issued_on  | department_id
----------------+------------+---------------
            101 | 2018-06-19 | DOD
(1 row)

test=# SELECT * FROM invoices;
 invoice_number | issued_on
----------------+------------
            100 | 2018-06-19
            101 | 2018-06-19
(2 rows)

子表添加的编号为 101 的发票,也父表中也列出来了。这样做的好处是在父表中进行的各种算法In完全以忽略子表的存在。从这个文档可以了解到更多关于 PostgreSQL 继承方面的内容。

外部数据包装器

可以有一张虚表用来指向另一个PostgreSQL实例吗?或者另一个SQLite、MongoDB、Redis甚至其它的数据库?这个功能叫做外部数据包装器(FDW),它提供一个标准化的方法来存取和操作连接到Postgres服务器的外部数据源。有各种各样的FDW实现让你可以连接到不同的数据源,它们通常被打包为扩展插件。标准Postgres分发包中有一个postgres_fdw扩展,它可以让你连接到其它Postgres服务器。例如,你可以移动一张大表到其它服务器,同时在本地建立一张虚表(正确的术语叫做"外部表"):
-- install the extension (required only once)
CREATE EXTENSION postgres_fdw;

-- big_server is our big, remote server with the migrated table
CREATE SERVER big_server FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '10.1.2.3', dbname 'big_db');

-- create a user mapping for the app user
CREATE USER MAPPING FOR app_user SERVER big_server
  OPTIONS (user 'remote_user', password 'remote_pass');

-- and finally create the virtual table invoices -> big_db.public.invoices
CREATE FOREIGN TABLE invoices (
  invoice_num int NOT NULL PRIMARY KEY
  -- other columns omitted for brevity
) SERVER big_server;

这个Wiki有一个很好的列表列出了许多FDW的有效实现。

除了可以从其它服务器存取数据,FDW也被用作实现交互存储层,比如 cstore_fdw。还有一个dblink扩展,它是另一种用来存取远程PostgreSQL数据的实现。

拆分表

从版本 10 开始,PostgreSQL 原生支持将一个表拆分成多个子表,其拆分基于对一列或多列数据的计算来进行。这一功能可以让一个巨大的表在物理上存储于多个表中,改善DML性能和存储管理。下面演示了如何创建拆分表,该演示会为每个月的数据增加一张表:
-- the parent table
CREATE TABLE invoices (
    invoice_number   int  NOT NULL,
    issued_on        date NOT NULL DEFAULT now()
) PARTITION BY RANGE (issued_on);
 
-- table for the month of May 2018
CREATE TABLE invoices_2018_05 PARTITION OF invoices
  FOR VALUES FROM ('2018-05-01') TO ('2018-06-01');

-- table for the month of June 2018
CREATE TABLE invoices_2018_06 PARTITION OF invoices
  FOR VALUES FROM ('2018-06-01') TO ('2018-07-01');

子表必须由人工或通过程序创建,这个创建过程不会自动发生。你可以在父级表中查询或插入数据,PostgreSQL 会自动到子表中去进行操作,来看一下:

先插入两行数据:
test=# INSERT INTO invoices VALUES (10042, '2018-05-15');
INSERT 0 1
test=# INSERT INTO invoices VALUES (43029, '2018-06-15');
INSERT 0 1

可以看到数据实际被插入到了子表中:
test=# SELECT * FROM invoices_2018_05;
 invoice_number | issued_on
----------------+------------
          10042 | 2018-05-15
(1 row)

test=# SELECT * FROM invoices_2018_06;
 invoice_number | issued_on
----------------+------------
          43029 | 2018-06-15
(1 row)

但在父表中也可以完成查询,返回合并的结果:
test=# SELECT * FROM invoices;
 invoice_number | issued_on
----------------+------------
          10042 | 2018-05-15
          43029 | 2018-06-15
(2 rows)

拆分方法与继承相似在父表级别查询,但也存在一些区别比如在拆分父表中没有保存数据。你可以在这个文档中阅读到更多相关内容。已经进入 Beta 阶段的 PostgreSQL 11 对这一功能会有所改进。

区间类型

你以前与温度范围、日程表、价格区间或类似的数值范围打过交道吗?如果是,那你就会有这样的经验:看似简单的问题总会导致你抓耳挠腮并且经常深夜调试bug。以下是一个包含区间列的表和一些数值:
CREATE TABLE prices (
    item  text,
    price int4range -- int4range is a range of regular integers
);

INSERT INTO prices VALUES ('mouse',    '[10,16)');
INSERT INTO prices VALUES ('keyboard', '[20,31)');
INSERT INTO prices VALUES ('joystick', '[35,56)');

在错配方括号中的数值代表半开区间。以下是一个查询语句,它可以找出在价格区间15$~30$中的所有项,使用了&&操作符(区间交错):
test=# SELECT * FROM prices WHERE price && int4range(15,30);
   item   |  price
----------+---------
 mouse    | [10,16)
 keyboard | [20,31)
(2 rows)

为了让你印象深刻,你可以尝试一下使用无区间类型的查询语句有多难(试试就好)。

区间类型非常强大 --- 这里还有操作符函数,你也可以定义你自己的区间类型,甚至还可以索引它们。为了学习更多关于区间的知识,你可以看看这篇文章,还有这篇

数组类型

PostgreSQL很久以前就已经支持数组类型了。数组类型可以精简应用代码并可以简化查询操作。以下是一个在表中使用数组列的例子:
CREATE TABLE posts (
    title text NOT NULL PRIMARY KEY,
    tags  text[]
);

假设每一行代表一篇博客,每篇博客又都有一个标签集,下面是我们如何列出所有带"postgres"和"go"标签的博客的代码:
test=# SELECT title, tags FROM posts WHERE '{"postgres", "go"}' <@ tags;
               title               |          tags
-----------------------------------+------------------------
 Writing PostgreSQL Triggers in Go | {postgres,triggers,go}
(1 row)

这里数组类型的使用使我们的数据模型更精确,同时也简化了查询操作。Postgres数组总是与操作符和函数一起出现,其中也包括集合函数。你也可以基于数组表达式创建索引。

触发器

当对表中的行进行插入、更新或删除操作时,你能请求PostgreSQL执行一个特殊的函数,这个函数甚至可以在插入过程中修改值。你可以点击这里了解更多关于触发器的信息。以下是一个例子:当创建用户时,触发器发出通知并写入稽核日志。

-- a table of users
CREATE TABLE users (
  username text NOT NULL PRIMARY KEY
);

-- an audit log
CREATE TABLE audit_log (
  at          timestamptz NOT NULL DEFAULT now(),
  description text NOT NULL
);

-- the actual function that is executed per insert
CREATE FUNCTION on_user_added() RETURNS TRIGGER AS $$
BEGIN
  IF (TG_OP = 'INSERT') THEN
    -- add an entry into the audit log
    INSERT INTO audit_log (description)
        VALUES ('new user created, username is ' || NEW.username);
    -- send a notification
    PERFORM pg_notify('usercreated', NEW.username);
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- set the function as an insert trigger
CREATE TRIGGER on_user_added AFTER INSERT ON users
  FOR EACH ROW EXECUTE PROCEDURE on_user_added();

现在,如果你尝试增加一个新用户,一个稽核日志记录将会被自动添加。

test=# INSERT INTO users VALUES ('alice');
INSERT 0 1
test=# SELECT * FROM audit_log;
                at             |             description
-------------------------------+-------------------------------------
 2018-06-19 04:00:30.672947+00 | new user created, username is alice
(1 row)

pg_stat_statements

这是一个扩展插件,默认在PostgreSQL分发包中就已经包含了,只是默认没有启用。这个扩展记录了每条执行语句的健康信息,包括执行时长、内存使用、磁盘IO初始化等。对于需要了解和调试查询性能的场景它是不可或缺的一个扩展。安装和启用这个扩展的开销非常小,它也非常易于使用,因此没有理由不在你的生产server中使用这个扩展。

哈希,GIN还有BRIN索引

PostgreSQL中默认的索引类型是B-Tree,有记录表示也有其他类型。其他索引类型在非常不常见的情况下非常有用。特别是设置散列,GIN和BRIN类型的索引可能只是解决您的性能问题:

散列:与具有固有排序的B树索引不同,散列索引是无序的,只能执行相等匹配查找。然而,散列索引占用更小的空间并且比平等匹配的B树更快。另外,请注意,在PostgreSQL 10之前,不可能复制散列索引;它们未被记录。

GIN:GIN是一个倒排索引,它基本上允许单个键的多个值。 GIN索引对索引数组,JSON,范围,全文搜索等非常有用。

BRIN:如果您的数据具有特定的自然顺序例如时间序列数据,并且您的查询通常只适用于其中的一小部分范围,那么BRIN索引可以以很小的开销加快查询速度。 BRIN索引维护每个数据块的范围,允许优化器跳过包含不会被查询选中的行的块。

全文搜索

PostgreSQL也很好地支持全文搜索,甚至支持除英语之外的语言。

OneSQL对两者的观点 : 对PostgreSQL与MySQL的一点认知、感觉和比较

以前的常用网络名是anysql,算是国内很早搞数据库的,从98年开始接触Oracle数据库,2001年考的ocp和ocd证书,从2008年开始从研究单台Oracle转型开搞互联网支付的数据库架构,2013年开始从源代码层面切入MySQL和PostgreSQL。Oracle自然不用说它了,全宇宙最优势的单机数据库,有最好的CBO,最早支持分区表,非常好的综合成本。

不过今天只讨论一下MySQL和PostgreSQL,国内搞MySQL的人基本认识,综合来讲是非常务实的工程师作风,老老实实优化MySQL代码;搞PostgreSQL的也都基本认识,非常非常的有自信,类似于宗教一样了,认为PostgreSQL的技术优势天下无敌,但也没有搞清楚为何应用上没有MySQL那么广泛,在这个事情上有些郁闷,提交代码的人看起来比MySQL要少。这么多废话,现在正式开讲个人对MySQL和PostgreSQL的几点印象:

从易用性角度来讲,MySQL比PostgreSQL简单,默认带一个数据库,无须设置什么参数,入门文档中基本不讲概念,直接创建表就可以开始用了。个人花在PostgreSQL安装、登录、阅读入门文档上的时间要比MySQL多。最方便的是很早就支持Windows平台,毕竟个人电脑上用Windows的最多,导致“懂”MySQL运行维护的人要比PostgreSQL多。

如果对第一条不服,可以看一下所用的插件,MySQL基本上将必须的插件统一管理起来,包含在一个分支下面,比如Percona & MariaDB,而PostgreSQL则需要自己下载源代码进行编译,缺少Percona & MariaDB这样的角色。我们用户下载OneSQL for PostgreSQL后,还要自己下载6个插件源代码,自行编译,为何不能直接下载就包含这些插件呢?真正有时间折腾的人是少数DBA,绝大多数的DBA和开发,还是简单一点好。

源生的逻辑复制支持,阿里和淘宝很早就遇到读流量过多的问题,一直在寻求数据逻辑复制的方案,biti大师在2007年初就拉了一个团队在b2b搞Oracle日志解析,也找过本人,只是我觉得单机Oracle的研究没有出路,去支付宝做数据库架构更有意思,在2009年和Oracle开发团队一起吃饭时,曾经提过Redo Log格式开放一下的建议,2007年还在eBay时也向Oracle提过Active Dataguard的思路,可惜Active Dataguard的技术出来太晚了,当时的Shardplex非常贵,并且还有版本兼容性的问题(非官方出品),Oracle Logical Data Guard又不够稳定,MySQL刚好出现在这个空档时期,迎合了国内外互联网场景的刚需,抓住了除eBay以外的互联网公司业务场景,PostgreSQL则一直不支持逻辑复制。

用Oracle时,互联网企业普遍遇到了连接问题,2006年在eBay时将Oracle的连接数调到了20000个一直觉得eBay的Oracle DBA团队非常牛x,后来到支付宝后觉得这是个重大的问题。基于多进程模型的并发,在创建新连接时成本非常高,Oracle数据库每秒新建200个连接的话,基本上SYS cpu会很高,想必PostgreSQL也是如此,同时单个连接的内存成本过高,当时我们按8MB一个连接估计,20000个连接直接空了160GB的内存;而基于线程并发的MySQL,每秒钟建立3000个连接也没有太大的cpu消耗,每个连接只需要数百kb的内存;并且多进程的调度成本比多线程的调度成本高太多,否则Oracle 12c在Linux/Unix上也就不用推出线程模型了。PostgreSQL的两个中间件pgbouncer和pgpool最广泛的应用场景是用来减少PostgreSQL后端的连接数,就反映了这个问题。还没有好好测试过多线程机制的Oracle,感觉应当是很无敌的。

MySQL技术力量更加集中,由于国内外大型互联网企业的界入,并且在迫切的业务压力前面,做出了非常实用性改进,从MySQL 4.1开始的每一个版本都极大进改进了性能和稳定性。MySQL官方(MySQL AB、SUN、Oracle)充分合并了各个业务公司的代码,形成了非常统一的版本,可能是由于GPL协议的原因,也可能参与方都是业务为主的公司。而PostgreSQL的力量感觉就非常分散了,可能和BSD协议有关,也有可能是缺少巨头企业的参与,以技术为生存基础的小企业不易将技术完全公开,所以基于PostgreSQL的小众商业版本非常多。

应用架构的进化,“所有鸡蛋不放在一个篮子中”是一条非常好的架构准则。小型机也管过数十台,遇到过cpu、内存、主板、供电模板等的损坏,磁盘的IO拌动则是每周都发生。即使是当时最强的小型机和Oracle,当足够集中时,数据层的小蝴蝶翅膀一动,最上层应用就是地震,没分到多个篮子中时,好几次是通过逐步开放流量的方式进行恢复的。即然这样,就分篮子吧,分得足够小时,MySQL的弱点就不重要了,加上业务场景和应用都是新建的,适应一下偏弱的MySQL完全不是问题。如果不是新兴的互联网业务场景,也无法推动MySQL的应用。

个人觉得逻辑复制和线程模型是MySQL适合互联网高并发业务关键的两个技术点,在合适的时间提供了刚需的技术;非技术方面,当然最重要的是大公司的参与和版本的统一,这与MySQL一直在商业公司(SUN、Oracle)的领导下有关,大公司都是商业公司的付费客户,自然需要优先考虑这些客户的需求,就自然而然能形成统一的版本了。再来聊聊Pger们针对MySQL的几个技术点:
MySQL缺少对Hash Join的支持,这的确是MySQL的一个硬伤。不过PostgreSQL的Hash Join实现好象也没有Oracle那么高效,也只是比MySQL好而已,可能是PostgreSQL的存贮层不够优秀。

MySQL缺少并行查询的支持,PostgreSQL直到9.6版本才支持并行查询,这个只是事后的优势,已经错过了互联网发展期了。在分篮子之后,架构上按鸡蛋并行在很大程度上解决了这个问题。

PostgreSQL有更丰富的插件,这个我不理解为PostgreSQL的优势,更将它理解成缺少强有力的版本发行商,非常不利于PostgreSQL的推广。因为MySQL同样支持插件结构,其多存贮引挚结构,让MySQL可以用定制化存贮插件去胜任专业领域的需求,而InnoDB是一款非常好的面对通用业务场景的存贮引挚。

MySQL缺少物理复制。这个问题没有那么严重,Amazon和Alibaba都基于MySQL的生态推出了基于物理层复制的结构。并且足够稳定的逻辑复制和物理复制并没有多少差别,相对于文件的存贮格式来讲都是逻辑的,区别只是Bug的多少以及Apply的性能而已。

MySQL缺少对json和gis的支持,这个在MySQL 5.7后也有了,虽然过去的gis应用很多都在PostgreSQL上,可是MySQL的应用基数大,官方出品估计增量会很快。

应当看到MySQL也有非常好的趋势,不一定是技术上的先进性,但可能会更关键,个人体会以下几点:
MySQL 5.7增加了json和gis的支持,说明官方的效率还是很高的,要有的总会有的。

MySQL网络协议简单,现在还主要在使用MySQL 4.1时代的文本协议,使之非常适合在协议层透明解决SQL路由的问题,让应用架构变得简单。同时做过MySQL和PostgreSQL的协议中间件,应当非常有说话的权利的。

InnoDB是非常好的存储引挚,一开始就是向Oracle学习的,没有PostgreSQL的垃级回收(vacumm)问题。

商业公司(Oracle)为后盾,对于云计算企业来讲,这个可能是弱势,但对于绝大多数企业来讲是优势,这和找保险是一个道理。

据个人观察及测试,MySQL和PostgreSQL的各种性能测试数据,基本上都差不多。特别场景测试的结果,无法反映在正式场景下,比如应用和DB在同一台服务器上、严格控制数据库的连接数。后续如何,就要看下一波新业务新应用中,谁能抓住要点了,有可能还是MySQL,也有可能是PostgreSQL,也许又回到Oracle的Sharding节点版本中。不同选择的的成本基本上是一样的,除非自己全会,依赖别人的成本是差不多的。


无独有偶,在钱魏Way的个人站点上看到他对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。


根据 2023 年 Stack Overflow 调研,Postgres 已经取代 MySQL 成为最受敬仰和渴望的数据库。在以下几个维度进行了比较:
许可证 License
性能 Performance
功能 Features
可扩展性 Extensibility
易用性 Usability
连接模型 Connection Model
生态 Ecosystem
可运维性 Operability

基于最新的主要版本 Postgres 15 和 MySQL 8.0 (使用 InnoDB)。在本对比中使用 Postgres 而不是 PostgreSQL,尽管 PostgreSQL 才是官方名称。

许可证 License
MySQL 社区版采用 GPL 许可证。Postgres 发布在 PostgreSQL 许可下,是一种类似于 BSD 或 MIT 的自由开源许可。即便 MySQL 采用了 GPL,仍有人担心 MySQL 归 Oracle 所有,这也是为什么 MariaDB 从 MySQL 分叉出来。

性能 Performance
对于大多数工作负载来说,Postgres 和 MySQL 的性能相当,最多只有 30% 的差异。无论选择哪个数据库,如果查询缺少索引,则可能导致 x10 ~ x1000 的降级。 话虽如此,在极端的写入密集型工作负载方面,MySQL 确实比 Postgres 更具优势。

功能 Features
对象层次结构
MySQL 采用了 4 级结构:实例、数据库、表、列。

Postgres 采用了 5 级结构:
实例(也称为集群)、数据库、模式 Schema、表、列。

ACID 事务
两个数据库都支持 ACID 事务,Postgres 提供更强大的事务支持。

安全性
Postgres 和 MySQL 都支持 RBAC。Postgres 支持开箱即用的附加行级安全 (RLS),而 MySQL 需要创建额外的视图来模拟此行为。

查询优化器
Postgres 的查询优化器更优秀。

复制
Postgres 的标准复制使用 WAL 进行物理复制。MySQL 的标准复制使用 binlog 进行逻辑复制。Postgres 也支持通过其发布/订阅模式进行逻辑复制。

JSON
Postgres 和 MySQL 都支持 JSON。Postgres 支持的功能更多:更多操作符来访问 JSON 功能,允许在 JSON 字段上创建索引。

CTE (Common Table Expression)
Postgres 对 CTE 的支持更全面:
在 CTE 内进行 SELECT, UPDATE, INSERT, DELETE 操作;在 CTE 之后进行 SELECT, UPDATE, INSERT, DELETE 操作。

MySQL 支持:
在 CTE 内进行 SELECT 操作;在 CTE 之后进行 SELECT, UPDATE, DELETE 操作。

窗口函数 (Window Functions)
窗口帧类型:MySQL 仅支持 Row Frame 类型,允许定义由固定数量行组成的帧;而 Postgres 同时支持 Row Frame 和范围帧类型。

范围单位:MySQL 仅支持 UNBOUNDED PRECEDING 和 CURRENT ROW 这两种范围单位;而 Postgres 支持更多范围单位,包括 UNBOUNDED FOLLOWING 和 BETWEEN 等。

性能:一般来说,Postgres 实现的 Window Functions 比 MySQL 实现更高效且性能更好。

高级函数:Postgres 还支持更多高级 Window Functions,例如 LAG (), LEAD (), FIRST_VALUE (), and LAST_VALUE ()。

可扩展性 Extensibility
Postgres 支持多种扩展。最出色的是 PostGIS,它为 Postgres 带来了地理空间能力。此外还有 Foreign Data Wrapper (FDW),支持查询其他数据系统,pg_stat_statements 用于跟踪规划和执行统计信息,pgvector 用于进行 AI 应用的向量搜索。

MySQL 具有可插拔的存储引擎架构,并诞生了 InnoDB。但在如今的 MySQL 中,InnoDB 已成为主导存储引擎,因此可插拔架构只作为 API 边界使用,而不是用于扩展目的。

在认证方面,Postgres 和 MySQL 都支持可插拔认证模块 (PAM)。

易用性 Usability
Postgres 更加严格,而 MySQL 更加宽容:
MySQL 允许在使用 GROUP BY 子句的 SELECT 语句中包含非聚合列;而 Postgres 则不允许。
MySQL 默认情况下是大小写不敏感的;而 Postgres 默认情况下是大小写敏感的。
MySQL 允许 JOIN 来自不同数据库的表;而 Postgres 只能连接单个数据库内部的表,除非使用 FDW 扩展。

连接模型 Connection Model
Postgres 采用在每个连接上生成一个新进程的方式工作。而 MySQL 则在每个连接上生成一个新线程。因此,Postgres 提供了更好的隔离性,例如,一个无效的内存访问错误只会导致单个进程崩溃,而不是整个数据库服务器。另一方面,进程模型消耗更多资源。因此,在部署 Postgres 时建议通过连接池(如 PgBouncer 或 pgcat)代理连接。

生态 Ecosystem
常见的 SQL 工具都能很好地支持 Postgres 和 MySQL。由于 Postgres 的可扩展架构,并且仍被社区拥有,近年来 Postgres 生态系统更加繁荣。对于提供托管数据库服务的应用平台,每个都选择了 Postgres。

可运维性 Operability
由于底层存储引擎设计问题,在高负载下,Postgres 存在臭名昭著的 XID wraparound 问题。对于 MySQL,在 Google Cloud 运营大规模 MySQL 集群时遇到过一些复制错误。

这些问题只会在极端负载下发生。对于正常工作负载而言,无论是 Postgres 还是 MySQL 都是成熟且可靠的。数据库托管平台也提供集成备份/恢复和监控功能。

总的来说,Postgres 有更多功能、更繁荣的社区和生态;而 MySQL 则更易学习并且拥有庞大的用户群体。 观察到与 Stack Overflow 结果相同的行业趋势,即 Postgres 在开发者中变得越来越受欢迎。但根据实际体验,精密的 Postgres 牺牲了一些便利性。在一个组织内部共存 Postgres 和 MySQL 也是很常见的情况。



该文章最后由 阿炯 于 2023-11-07 14:29:34 更新,目前是第 2 版。