PostgreSQL 与 MySQL 相比，优势何在？

Pg 没有 MySQL 的各种坑

MySQL 的各种 text 字段有不同的限制, 要手动区分 small text, middle text, large text... Pg 没有这个限制, text 能支持各种大小.

按照 SQL 标准, 做 null 判断不能用 = null, 只能用 is null

the result of any arithmetic comparison with NULL is also NULL

但 pg 可以设置 transform_null_equals 把 = null 翻译成 is null 避免踩坑

不少人应该遇到过 MySQL 里需要 utf8mb4 才能显示 emoji 的坑, Pg 就没这个坑.

MySQL 的事务隔离级别 repeatable read 并不能阻止常见的并发更新, 得加锁才可以, 但悲观锁会影响性能, 手动实现乐观锁又复杂. 而 Pg 的列里有隐藏的乐观锁 version 字段, 默认的 repeatable read 级别就能保证并发更新的正确性, 并且又有乐观锁的性能. 附带一个各数据库对隔离级别的行为差异比较调查:

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

MySQL 不支持 OVER 子句, 而 Pg 支持. OVER 子句能简单的解决 "每组取 top 5" 的这类问题.

几乎任何数据库的子查询 (subquery) 性能都比 MySQL 好.

更多的坑:

不少人踩完坑了, 以为换个数据库还得踩一次, 所以很抗拒, 事实上不是!!!

Pg 不仅仅是 SQL 数据库

它可以存储 array 和 json, 可以在 array 和 json 上建索引, 甚至还能用表达式索引. 为了实现文档数据库的功能, 设计了 jsonb 的存储结构. 有人会说为什么不用 Mongodb 的 BSON 呢? Pg 的开发团队曾经考虑过, 但是他们看到 BSON 把 ["a", "b", "c"] 存成 {0: "a", 1: "b", 2: "c"} 的时候就决定要重新做一个 jsonb 了... 现在 jsonb 的性能已经优于 BSON.

现在往前端偏移的开发环境里, 用 Pg + PostgREST 直接生成后端 API 是非常快速高效的办法:

postgREST 的性能非常强悍, 一个原因就是 Pg 可以直接组织返回 json 的结果.

它支持服务器端脚本: TCL, Python, R, Perl, Ruby, MRuby ... 自带 map-reduce 了.

它有地理信息处理扩展 (GIS 扩展不仅限于真实世界, 游戏里的地形什么的也可以), 可以用 Pg 搭寻路服务器和地图服务器:

它自带全文搜索功能 (不用费劲再装一个 elasticsearch 咯):

不过一些语言相关的支持还不太完善, 有个 bamboo 插件用调教过的 mecab 做中文分词, 如果要求比较高, 还是自己分了词再存到 tsvector 比较好.

它支持 trigram 索引.

trigram 索引可以帮助改进全文搜索的结果:

trigram 还可以实现高效的正则搜索 (原理参考

)

MySQL 处理树状回复的设计会很复杂, 而且需要写很多代码, 而 Pg 可以高效处理树结构:

它可以高效处理图结构, 轻松实现 "朋友的朋友的朋友" 这种功能:

它可以把 70 种外部数据源 (包括 Mysql, Oracle, CSV, hadoop ...) 当成自己数据库中的表来查询:

心动不如行动

PostgreSQL的Slogan是“世界上最先进的开源关系型数据库”

它是一款一专多长的全栈数据库：在可观的规模内，都能做到一招鲜吃遍天。

成熟的应用可能会用到许许多多的数据组件（功能）：缓存，OLTP，OLAP/批处理/数据仓库，流处理/消息队列，搜索索引，NoSQL/文档数据库，地理数据库，空间数据库，时序数据库，图数据库。传统架构选型可能会组合使用多种组件，典型的如：Redis + MySQL + Greenplum/Hadoop + Kafuka/Flink + ElasticSearch。在这里MySQL只能扮演OLTP关系型数据库的角色，但如果是PostgreSQL，就可以身兼多职，One hold them all，比如：

OLTP：事务处理是PostgreSQL的本行

OLAP：citus分布式插件，ANSI SQL兼容，窗口函数，CTE，CUBE等高级分析功能，任意语言写UDF

流处理：PipelineDB扩展，Notify-Listen，物化视图，规则系统，灵活的存储过程与函数编写

时序数据：timescaledb时序数据库插件，分区表，BRIN索引

空间数据：PostGIS扩展（杀手锏），内建的几何类型支持，GiST索引。

搜索索引：全文搜索索引足以应对简单场景；丰富的索引类型，支持函数索引，条件索引

NoSQL：JSON，JSONB，XML，HStore原生支持，至NoSQL数据库的外部数据包装器

数据仓库：能平滑迁移至同属Pg生态的GreenPlum，DeepGreen，HAWK等，使用FDW进行ETL

图数据：递归查询

缓存：物化视图

在探探的实践中，整个系统就是围绕PostgreSQL设计的。几百万日活，几百万全局DB-TPS，几百TB数据的量级下，数据组件只用了PostgreSQL。直到接近千万日活，才开始进行架构调整引入独立的数仓，消息队列和缓存。这只是验证过的规模量级，进一步压榨PG是完全可行的。

因此在一个很可观的规模内，PostgreSQL都可以扮演多面手的角色，一个组件当多种组件使。虽然在某些领域它可能比不上专用组件，至少都做的都还不赖。而单一数据组件选型可以极大地削减项目额外复杂度，这意味着能节省很多成本。它让十个人才能搞定的事，变成一个人就能搞定的事。

为了不需要的规模而设计完全是白费功夫，实际上这属于过早优化的一种形式。只有当没有单个软件能满足你的所有需求时，才会存在分拆和集成的利弊权衡。集成多种异构技术是相当棘手的工作，如果真有那么一样技术可以满足你所有的需求，那么使用该技术就是最佳选择，而不应试图去集成多个组件来重新实现它。

当业务规模增长到一定量级时，可能最终还是不得不使用基于微服务/总线的架构，将这些功能分拆为多个组件。但PostgreSQL的存在极大地推后了这个权衡到来的阈值，而且在分拆之后依然能继续发挥重要的作用。

当然除了功能强大之外，Pg的另外一个重要的优势就是运维友好。有很多非常实用的特性：

• DDL能放入事务中，删表，TRUNCATE，创建函数，索引，都可以放在事务里原子生效，或者回滚。
  这就能进行很多骚操作，比如在一个事务里通过RENAME，完成两张表的王车易位。
• 能够并发地创建或删除索引（不锁表）；为表添加新的空字段不锁表，瞬间完成。
  这意味着可以随时在线上按需添加移除索引，添加字段，不影响业务。
• 复制方式多样：段复制，流复制，触发器复制，逻辑复制，插件复制，多种复制方法。
  丰富的复制支持使得不停服务迁移数据变得无比容易。
• 提交方式多样：异步提交，同步提交，法定人数同步提交。
• FDW的存在让ETL变得无比简单，一行SQL就能解决。
• 系统视图非常完备，做监控系统相当简单。

除了运维之外，Pg还有一个巨大的优势就是协议友好，类BSD/MIT的协议。君不见多少国产数据库，BAT云数据库都是Pg的换皮或二次开发产品。

相比之下，MySQL社区版是GPL协议，要不是GPL传染，怎么这么多基于MySQL改的数据库都开源了。而且MySQL还捏在乌龟壳手里，React改协议的风波算是一个前车之鉴。

当然，要说PG有什么缺点或者遗憾，那还是有几个的，不过也无伤大雅：

• 因为使用了MVCC，数据库需要定期VACUUM，有额外的维护工作。
• 没有pt-xxx那么成熟的MySQL工具脚本。
• 没有很好的开源集群监控方案，需要自己做。
• 慢查询日志和普通日志是混在一起的，需要自己解析处理。
• 官方Pg没有很好用的列存储，对数据分析而言算一个小遗憾。

还有一个劣势：MySQL确实是最流行的开源关系型数据库，所以Pg招人相对困难。很多时候只好自己培养。不过看DB Engines上的流行度趋势，未来还是很光明的。

我自己比较选型过MySQL和PostgreSQL，难得地在阿里这种MySQL和Java的世界中有过选择的自由。我认为只要有条件自由选择，没有任何理由不选PostgreSQL。扛着阻力把PG（还有Go之于Java）用了起来，推了起来。我用它做过很多项目，解决了很多需求（小到算统计报表，大到创收一个小目标）。大多数需求PG单挑就搞定了，极少数可能会再用点NoSQL，比如Redis，Cassandra/HBase。最后实在是对Pg爱不释手，以至于专职去研究PG了。

用好Pg也有一个立竿见影的优势：对于很多需求来说，一个人能顶一个小团队，全栈工程师就该用全栈数据库嘛：

• 后端懒得写怎么办，PostGraphQL直接从数据库模式定义生成GraphQL API，自动监听DDL变更，生成相应的CRUD方法与存储过程包装，对于后台开发再方便不过，类似的工具还有PostgREST与pgrest。对于中小数据量的应用都还堪用，省了一大半后端开发的活。
• 需要用到Redis的功能，直接上Pg，模拟普通功能不在话下，缓存也省了。Pub/Sub使用Notify/Listen/Trigger实现，用来广播配置变更，做一些控制非常方便。
• 需要做分析，窗口函数，复杂JOIN，CUBE，GROUPING，自定义聚合，自定义语言，爽到飞起。如果觉得规模大了想scale out可以上citus扩展（或者换greenplum）；比起数仓可能少个列存比较遗憾，但其他该有的都有了。
• 用到地理相关的功能，PostGIS堪称神器，千行代码才能实现的复杂地理需求，一行SQL轻松高效解决。
• 存储时序数据，timescaledb扩展虽然比不上专用时序数据库，但百万记录每秒的入库速率还是有的。用它解决过硬件传感器日志存储，监控系统Metrics存储的需求。
• 一些流计算的相关功能，可以用PipelineDB直接定义流式视图实现：UV，PV，用户画像实时呈现。物化视图也很好使。
• PostgreSQL的FDW是一种强大的机制，允许接入各种各样的数据源，以统一的SQL接口访问。它妙用无穷：
• file_fdw这种自带的扩展，可以将任意程序的输出接入数据表。最简单的应用就是监控系统信息。
• 管理多个PostgreSQL实例时，可以在一个元数据库中用自带的postgres_fdw导入所有远程数据库的数据字典。统一访问所有数据库实例的元数据，一行SQL拉取所有数据库的实时指标，监控系统做起来不要太爽。
• 之前做过的一件事就是用hbase_fdw和MongoFDW，将HBase中的历史批量数据，MongoDB中的当日实时数据包装为PostgreSQL数据表，一个视图就简简单单地实现了融合批处理与流处理的Lambda架构。
• 使用redis_fdw进行缓存更新推送；使用mongo_fdw完成从mongo到pg的数据迁移；使用mysql_fdw读取MySQL数据并存入数仓；实现跨数据库，甚至跨数据组件的JOIN；使用一行SQL就能完成原本多少行代码才能实现的复杂ETL，这是一件多么美妙的事情。
• 各种丰富的类型与方法支持：例如JSON，从数据库直接生成前端所需的JSON响应，轻松而惬意。范围类型，优雅地解决很多原本需要程序处理的边角情况。其他的例如数组，多维数组，自定义类型，枚举，网络地址，UUID，ISBN。很多开箱即用的数据结构让程序员省去了多少造轮子的功夫。
• 丰富的索引类型：通用的Btree索引；大幅优化顺序访问的Brin索引；等值查询的Hash索引；GIN倒排索引；GIST通用搜索树，高效支持地理查询，KNN查询；Bitmap同时利用多个独立索引；Bloom高效过滤索引；能大幅减小索引大小的条件索引；能优雅替代冗余字段的函数索引。而MySQL就只有那么可怜的几种索引。
• 稳定可靠，正确高效。MVCC轻松实现快照隔离，MySQL的RR隔离等级实现不完善，无法避免PMP与G-single异常。而且基于锁与回滚段的实现会有各种坑；PostgreSQL通过SSI能实现高性能的可序列化。
• 复制强大：WAL段复制，流复制（v9出现，同步、半同步、异步），逻辑复制（v10出现：订阅/发布），触发器复制，第三方复制，各种复制一应俱全。
• 运维友好：可以将DDL放在事务中执行（可回滚），创建索引不锁表，添加新列（不带默认值）不锁表，清理/备份不锁表。各种系统视图，监控功能都很完善。
• 扩展众多、功能丰富、可定制程度极强。在PostgreSQL中可以使用任意的语言编写函数：Python，Go，Javascript，Java，Shell等等。与其说Pg是数据库，不如说它是一个开发平台。我试过很多没什么卵用但很好玩的东西：数据库里的爬虫/ 推荐系统 / 神经网络 / Web服务器等等。还有着各种各样功能强悍或脑洞清奇的第三方插件：https://pgxn.org。
  

学PostgreSQL是一件很有趣的事，它让我意识到数据库的功能远远不止增删改查。我学着SQL Server与MySQL迈进数据库的大门。但却是PostgreSQL真正向我展示了数据库的奇妙世界。

两年前的原答案：

上面说的都很齐全了。
没有人说FDW(Foreign data wrappers)吗？那我提一下吧。
第一次看到惊为天人：用统一的SQL，去访问其他关系数据库，其他NoSQL数据库，HBase，甚至是各种格式的文件，操作系统信息，在线数据集。

瞬间爆炸好吗，各种数据库大一统万岁！

举个栗子，redis_fdw：