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

当谈到开源数据库时,MySQL获 得了业界大部分的注意力,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域套接字,以及它的位置。

PostgreSQL中,访问控制的方法是使用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 领先的许多概念只是在非常迟的时候才出现在商业数据库中。

PostgreSQL 是最初的伯克利的代码的一个开放源码的继承人,它支持大部分 SQL:2003 标准并且提供了许多其他现代特性:复杂查询、外键、触发器、视图、事务完整性、多版本并发控制

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

Mysql 在一定量数据后(一般观点是 mysql 单表 200-300万时性能最好,数据再多性能就开始下降),性能下降很快,且大数据量情况下,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的功能,下面简要对比一下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节点版本中。不同选择的的成本基本上是一样的,除非自己全会,依赖别人的成本是差不多的。



该文章最后由 阿炯 于 2019-01-22 21:06:36 更新,目前是第 2 版。