10 Things I Hate About PostgreSQL

在过去的几年里，软件开发社区对流行的开源关系数据库的热爱已经达到了一种狂热的程度。Hacker news有一篇文章[^1]，题为"PostgreSQL是世界上最好的数据库"[^2]，阿谀奉承者们不遗余力地给予无条件的赞美，是这种现象的一个完美的例子。

虽然这些赞美肯定是当之无愧的，但缺乏有意义的反对意见让我感到有些困扰。没有一个软件是完美的，那么PostgreSQL到底有哪些缺点呢？自2003年以来，我一直在生产中使用PostgreSQL，其部署范围从小型（千兆字节）到中等量级再到非常大（~百万兆字节）。我的观点主要来自于创建运行至少是打算持续可用的系统。不用说，通过这些年以来遇到的一些痛苦的生产问题，让我对PostgreSQL的一些特性有了直观体验。

灾难性的事务ID回卷

参照此处[^3]，足以说明，这块硬骨头很难啃。该问题导致过很多起宕机多日的故障，使用Google搜索一下，你会发现有许多人写了关于他们是如何踩雷的。假如没有专业的专家协助，几乎所有的PostgreSQL最终都会遇到这个问题。

在将来的某个时候，XID很可能会过渡到使用64位整数，但在那之前，我们只能使用它。我想，至少我们可以庆幸，有一个进程可以防止它发生，不像一些飞机软件[^4]。

我见

PostgreSQL中的事务ID回卷确实是一个比较令人头疼的问题。在最近release的14版本中，依旧是32位的事务ID，看看zheap是否有可能解决这块难啃的骨头，bite hard！openGuass在这个问题上，倒是妥善地改成了64位，对于专业一点的DBA，处理此类问题会稍有经验。但是稍有不慎，还是容易掉到坑里，比如插件引起的事务ID急剧增加（低版本的pg_show_plans），子事务（每一个子事务都会导致事务ID加1），交互式的ON_ERROR_ROLLBACK等，都会导致事务ID迅速消耗。并且在以前的版本，还会经常面临下面这样的BUG：

ERROR:  found xmin 2088747257 from before relfrozenxid 2810153180

无解，只能做好监控，时刻关注数据库的年龄，并且做好运维，要知悉比如长事务、复制槽、2PC等，都会限制年龄的回收。

Failover可能会丢失数据

如果正在运行的主库突然宕机，普通的流复制几乎肯定会丢失部分已提交的数据。有些人可能会说：这就是异步复制的代价[^5]，但它并不一定是这样的。虽然PostgreSQL支持同步复制优选提交，以实现容错性的持久化，但是假如要控制性能影响的范围[^6]，无疑会增加应用的复杂度。

等待不会占用系统资源，但是事务锁会继续被持有，直到传输被确认。因此，使用同步复制，稍有不慎，就会降低数据库应用程序的性能，因为这会增加响应时间以及加剧资源争用。

这种附加的同步复制优选提交在紧要关头很有用，但我不愿推荐用于通用案例。它有点类似于具有acks=all 和min_isr 的Kafka 的 ISR 复制[^7]，但是事务型关系数据库运行任意查询的复杂性非常微妙。我目前还不知道在大规模下面有过基于优选提交实现的高可用、持久性的应用案例，如果有的话，请告诉我！

在关系型数据库方面，Galera Cluster[^8] 的组复制也是不完善的，但更接近理想。他们甚至鼓励地理分布式复制[^9]，这对于使用优选提交的PostgreSQL复制很可能是灾难性的。

我见

这个我觉得不是太大问题，异步复制和同步复制的区别就在于是否会丢失数据。有人可能会喷，PostgreSQL不支持类似于Oracle的最大可用模式，一主一从强同步主库宕机了，写入会阻塞，但是我觉得这本身就是一个悖论，如果出现问题要降级，不影响写入，那么与异步模式有什么区别？同步模式本身就是要保证故障切换后不丢失数据，阻塞写入就是为了确保数据至少在备库持久化了，不然干脆就用异步，或者使用多个同步备库。

低效率的复制可能会传播故障

流复制[^10]目前在产生部署中是最常用的复制机制，它是物理复制的一种形式，意味着它会复制位于磁盘上的二进制数据本身的更改。

每当需要通过写操作修改磁盘上的数据库页面（8KB）时，即使只是修改一个字节，整个页面的一个副本都会被写入预写日志（write-ahead log，WAL[^11]）。物理流复制利用这个现有的WAL基础设施作为它流式传输到副本的更改日志。

更新：有些人指出PostgreSQL只需要对每个WAL检查点进行一次整页写入（full-page-writes）的操作。这是事实，但在大多数现实世界的系统中，大多数写操作将在检查点之间的一个唯一页面上结束，遵循幂律分布（power law distribution）。但更重要的是：在预测系统行为时，正确的方法是假设更昂贵的情况，特别是当它依赖于难以预测和高度动态的应用程序行为时。

例如，对于物理复制，创建一个超大的索引，会产生大量的WAL日志条目，这很容易造成复制流的瓶颈。基于页面粒度上的读—修改—复制的过程会导致主库上硬件引发的数据损坏更容易传播到备库上，我个人在生产环境中多次目睹了这种情况。

这与逻辑复制相反，逻辑复制只会复制逻辑数据的更改。至少从理论上讲，大型索引的创建只会导致在网络上复制单个命令。虽然PostgreSQL支持逻辑复制[^12]已经有一段时间了，但大多数部署都使用物理流复制，因为它更健壮，有更广泛的支持，而且更容易使用。

我见

full-page-writes全页写的机制，我觉得缺点被过分关注了，全页写本身是对数据库的一种保护，可能有人会说，可以用ZFS、Btrfs这种支持原子写的文件系统，或者ext4打开journal，这种我认为不过是隔靴搔痒，将数据库该做的事转嫁给了文件系统，只不过换了个身份。其实换成PCIE等高端一点的存储，FPI的写性能损失也就在10%以内，至于网上经常看到的，30%左右的性能提升，我认为过于夸大了，应该是基于HDD来测试的，要更大的性能提升，干脆把fsync和synchrounous_commit也一起关了。

至于openGuass改成了类似MySQL的double write，机制有待商榷，可能配合上openGuass的增量检查点，可以起到一定的优化作用，毕竟当年Oracle出现增量检查点功能的时候，也是DBA的巨大福音。

MVCC垃圾回收频繁触发令人头疼

和大多数主流数据库一样，PostgreSQL使用多版本并发控制（MVCC）来实现事务的并发。但是，它的特殊实现方式常常会带来关于垃圾行版本及其清理（VACUUM[^13]）操作上的麻烦。通俗地讲，UPDATE操作会为任何修改过的行创建新的副本（或行版本），而将旧版本保留在磁盘上，直到它们被清除。

虽然这种情况在过去几年里稳步改善，但它是一个复杂的系统，对于第一次接触这个问题的人来说有点像一个黑盒。例如，了解Heap-Only-Tuples（HOT[^14]）以及它何时触发，对于大量的即时更新的工作负载场景中（如在一行中维护一致的计数器列）来说，是成败的关键。默认的autovacuum设置在大多数时候都是有效的，但是一旦当它不起作用时侯，Oh我的上帝。

相反，MySQL和Oracle使用redo和undo日志。他们不需要一个类似的后台垃圾收集进程。它们所做的权衡是，为事务提交和回滚的操作带来了额外的延迟。

在不久的将来，zheap可能会拯救我们[^15]。

我见

目前来看，PostgreSQL新老数据放在一起的设计，弊大于利，vacuum经常会因为各种各样的问题，罢工、懈怠等，到了一定的水位，还需要一个昂贵的AccessExclusiveLock，虽说Oracle当有大量的并发事务操作时回滚段中的旧数据来不及回收，导致回滚段满了，会导致数据库的所有更新操作都被hang住等缺点。还是静静等待Zheap，最好事以一个可插拔式的引擎形式出现，假如非要选型的话

1.比如你有一个日志型的OLTP应用。每秒有大量的并发Session，向数据库中插入大量数据，但这些数据从不Update，或者说很少Update。那么，多版本派的数据库就十分适合了，比如PostgreSQL，但并不是说PostgreSQL更新性能有多么多么不堪，只是相较于UNDO派，要弱那么一点点2.如果对事务的要求没那么高，那么一些NoSQL/NewSQL的数据库也可以考虑。比如，事务不多的事情下，可以考虑MongoDB，大量点查，就上Redis3.如果不需要跨表、跨行的事务，甚至不需要事务，选择面就更多了，像HBase、Cassandra等的插入性能都是不错的。4.如果有一个OLTP交易型的应用，有大量的查询，和A转帐给B这样的交易操作，也就是Update A的余额、再Update B的余额。UNDO派的数据库就比较适合了。毕竟，Update操作时UNDO派更节省资源。

另外，还得考虑存储引擎带来的影响，比如PostgreSQL和Oracle的表是堆表，堆表是无序的，支持更大的数据量。InnoDB的表是索引组织表，索引组织表是要按索引排序的。排序操作会额外带来一些性能损耗，但会提升按主键查询时的性能。

明白各个数据库的优势，和缺点，以及真正的痛点之后，才能更加合理的选型。

每个连接一个进程 = 无法大规模

PostgreSQL为每个连接创建一个进程，而大多数其他数据库使用更高效的连接并发模型。这会导致一个比较困难的调优问题，因为有一个相对较低的阈值，在这个阈值上增加更多的连接会降低性能（大约2个核），最终还有一个更高的阈值（难以估计，高度依赖于工作负载），在这个阈值上性能会直线下降。

当然，使用连接池这个标准方式，可以解决这个问题，但会带来显著的额外架构复杂度。在一个特定的超大的部署体系中，我最终不得不添加第二个pgbouncer[^16]层。一层运行在应用程序服务器上，另一层运行在数据库服务器上。它总共聚合了大约100万个客户端进程的连接。调优需要40%的黑暗艺术，40%的暴力力量和10%的纯粹运气。

进程的可扩展性在每个大版本中都在不断提高，但最终，与像MySQL中使用的线程—连接之类的东西相比，这种架构的性能有一些硬性限制。

关于更多技术深度的探索，可以参考 https://brandur.org/postgres-connections.

我见

这个是一个无法忽视的问题，正如文中所说，引入pgbouncer会使架构变得复杂，额外引入了一个失效点，同时，业务与数据库的连接会多1跳，RT可能会增加，而事务级的连接模式，因为不是同一个事务，那么绑定变量类似的好特性也使用不上了。虽说在v14的版本中，对于GetSnapshotData() 做了较大的改进，海量连接下有了一定的提升，可能除了互联网场景下，大量短链接、点查的情况，让连接池的优先级没有以前那么高了。但是，诚然，引入一个内置的连接池，还是十分之重要的。另外线程模型是否就一定好呢？一个线程挂了，牵一发而动全身，何来健壮性可言。

主键索引占用太多空间

PostgreSQL中的表有一个主键索引和被称为堆的单独行存储[^17]。其他数据库将这些集成在一起[^18]或支持"索引组织表"[^19]。在这种布局中，按照主键查找会直接获取到行数据，而无需二次提取来获取完整的行，因此也不需要额外的CPU和 I/O。

在PostgreSQL中的CLUSTER[^20]命令会根据某个索引重新组织表，以提升性能，但是并不适用于大多数现实世界中的OLTP应用。它会重写整个表，并获取一个排它锁，阻塞任何写入和读取。并且PostgreSQL不为新增的数据维持原布局，因此必须定期运行该操作。因此，只有当您能够定期使数据库长时间离线时，它才真正有用。

但更重要的是，索引组织表可以节省空间，因为索引不需要单独的行数据副本。对于主要由主键覆盖的行较少的表，例如连接表，这样可以很容易地将表的存储占用减少一半。

考虑下面这个表，它存储了任意对象的社交喜好

CREATE TABLE likes (
 object_type INTEGER NOT NULL,
 object_id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
 user_id BIGINT NOT NULL,
 created_at TIMESTAMP WITH TIME ZONE NOT NULL,
 PRIMARY KEY(object_type, object_id, user_id)
);

PostgreSQL将为主键维护一个索引，这个索引是独立于基表存储的。该索引将包含object_type、object_id和user_id列的完整副本。每行28个字节中的20个（~70%）会重复。如果PostgreSQL支持索引组织表，它就不会消耗所有额外的空间。

我见

这个观点就比较片面了，堆表有堆表的优势，索引组织表也有索引组织表的优势。不过PostgreSQL pageheader，固定的23Byte + padding，确实远远大于Oracle，也比Mysql略大。

大版本升级需要停机时间

一些大版本升级[^21]需要数小时的停机时间才能为大型数据库转换数据。使用典型的流复制机制，不可能通过升级副本和进行故障转移来优雅地做到这一点。磁盘上的二进制格式在大版本之间不兼容，因此主副本之间的有线协议实际上也是不兼容的。

希望逻辑复制最终会完全取代流复制，这将启用在线、滚动升级策略[^22]。当我进行大规模水平扩展部署时，我们在自定义基础架构上进行了大量工程投资，以使用额外的基于触发器[^23]的复制系统（也用于分片迁移）进行这些升级，而不会停机。

有些繁琐的复制设置

平心而论，MySQL开箱即用的复制是更麻烦，但相比一些NoSQL存储如MongoDB和Redis，或一些cluster-oriented的复制系统（比如MySQL组复制[^24]和Galera cluster[^25]，从易用性和避免边缘化的角度来看，在PostgreSQL配置复制还有很多需要改进的地方[^26]。虽然理论上逻辑复制为第三方解决方案提供了更大的灵活性来弥补这些缺陷，但到目前为止，使用逻辑复制代替流复制有相当多的问题[^27]。

我见

个人觉得，PostgreSQL流复制配置十分简单，我经常一条命令就搭建起来了一个备库，实在是没有看出来哪里复杂了。

pg_basebackup -Fp -Xs -R -p 5432 -D pgdata && pg_ctl -D pgdata start

荒唐的无Hints机制

Planner hints[^28]允许查询指示查询规划器使用它自己不会使用的策略。PostgreSQL开发团队多年来一直拒绝支持Planner hints，这似乎是一种足够聪明的编译器论证[^29]的形式。

我确实理解他们的推理，这在很大程度上是为了防止用户使用query hints攻击问题，而这些问题应该通过编写适当的查询来解决。然而，当你看着一个生产数据库在突然的、意料之外的查询计划转变下陷入全面崩溃时，这种哲学似乎是残酷的家长式的。

在许多这种情况下，对优化器的一个提示可以在几分钟内缓解问题，为工程团队赢得他们需要的几个小时或几天的时间来对查询进行适当的修复。尽管有一些间接的变通方法涉及禁用某些优化器的策略，但他们是有风险的，绝对不应该在任何时间压力下被使用。

我见

硬伤，PostgreSQL不支持Hint，并且永远不打算支持Hint，正如文中所说，没有Hint，意味着原本可以通过一条小小的hints来解决问题，却需要面临可能的崩溃困境。不过好在有hook，也算一种workaround吧，pg_hint_plans，你值得拥有。

无块压缩

在MySQL中，InnoDB的页面压缩通常可以将存储空间减少一半[^30]，而且从性能的角度来看几乎是 "免费 "的。PostgreSQL会自动压缩大的数值[^31]，但这对于关系型数据库中最常见的数据存储方式来说并不实用。对于大多数RDBMS的使用情况，一行通常是几百个字节或更少，这意味着压缩只有在应用于多行，或在块中时才能真正有效。

对于PostgreSQL的核心数据结构来说，块压缩的确很难实现，但MySQL的InnoDB存储引擎所采用的 "打洞 "策略[^32]在实践中似乎效果不错，尽管有一些缺点[^33]。

2020-04-07更新：Mark Callaghan[^34]，以“MySQL at Facebook”出名，质疑我在这里所说的打洞压缩"实际效果相当好"。事实证明，世界上最大的MySQL安装从未使用过打洞压缩，正如我之前所想的那样。然而，在几年前迁移到MyRocks之前，他们确实成功地使用了老一代InnoDB压缩的一个稍加修改的版本。

虽然打洞压缩似乎对一些人有用，但有一些注意事项使得它不像一个全垒打[^35]。如果你正在运行Percona的MySQL版本，MyRocks是一个更好的选择。如果不是这样，对于闪存上非常重的阅读负载来说，经典的InnoDB表压缩[^36]似乎是一个更安全的选择。马克没有指出任何重大生产问题的具体事例，但他确实指出，他"怀疑文件系统是为每页打孔而设计的，我会害怕费解的故障"。

在PostgreSQL世界中广泛使用的唯一通用的块压缩设置利用了ZFS[^37]，这对人们来说似乎确实相当好用。ZFS是现在Linux中的一个生产级的现实，但肯定带来了一些管理上的开销，而这些开销对于像XFS或ext4这样的 "开箱即用 "的文件系统来说是不存在的。

我见

PostgreSQL是少有的不支持压缩的主流数据库，仅仅有TOAST，不过TOAST是针对变长的超大数据，适用的范围有限（v14增加了lz4的压缩算法），建议社区大佬尽早把这个功能整出来吧。不过还是如前面那样，可以使用文件系统来实现，比如ZFS，以及一些新兴存储，scaleFlux等等。

MySQL的Punch hole是一个需要操作系统和文件系统支持的特性，顾名思义就是在文件中打洞，比如一个大文件中有一部分数据我们是不需要的，就可以通过punch hole特性将其删除，相当于在文件中打了个洞，这个洞是不占用磁盘的。不过Punch hole就是银弹了吗？

Facebook的大神Domas写了一篇博客，认为InnoDB推出这样的压缩特性，使其正在丧失自身的优势，非常值得一读。简单的摘要下：

•无法完美压缩：例如9KB的数据可能需要12kb来存储，取决于block size；•无法压缩Buffer pool, 这是和传统innodb压缩相比，以前的压缩方式可以在内存中只存放压缩页拷贝 （然而也有可能同时存在压缩和解压页），因此用户可能需要去购买iops更高的设备，而oracle正好也卖这些….•punch hole 可能产生的文件碎片化，底层的文件管理更加复杂；•对innodb文件做punch hole可能带来的后果是，使得每个文件的page变成一个独立的segment，文件系统需要单独的journal和metadata来管理。另外也有可能有性能问题：可能比non-sparse的写操作昂贵五倍 （这依赖于具体的内核）；•删除一个拥有几百万个段管理对象的数据文件带来的开销会非常昂贵。

PostgreSQL大管家Bruce momjian也曾写过一篇缺点，中中肯肯，和技术本身没有太大关系，更多是从企业的应用特点和技术栈去考虑的

•Skills in another relational database, and no desire or value to learn Postgres•Custom applications written for another database that you don't want to modify to work with Postgres•Using externally-developed applications, tools, or frameworks that don't support Postgres•Storage of non-transactional or cache data where Postgres's overhead is significant•Multi-host workloads with simple queries where NoSQL is a win•Small, single-user systems, where SQLite is best

后续

你可能还是应该使用PostgreSQL，而不是其他的东西来存储数据，你知道，你最好能保留一段时间。一般来说，我建议从PostgreSQL开始，然后试图找出它为什么不适合你的使用情况。

PostgreSQL是非常成熟的，工程设计良好，功能丰富，通常没有尖锐的边缘，对于绝大多数的使用情况来说，性能相当高。它也没有被一个主导性的企业赞助商所束缚，包括优雅的文档，并且有一个专业的、包容的社区。

好消息是，通过使用Heroku PostgreSQL[^38]、Compose PostgreSQL[^39]、Amazon RDS for PostgreSQL[^40]或Google Cloud SQL for PostgreSQL[^41]等管理数据库服务，可以减少或消除本帖中提出的许多问题所带来的痛苦。如果你能使用这些服务之一，看在所有神圣事物的份上，请你使用吧。

我很自豪地说，我在PostgreSQL的基础上构建了近二十年的软件，尽管它有缺陷，但我仍然是它的坚定支持者。鉴于我多年来目睹了其令人难以置信的开发团队所取得的进展，我可以说，如果不是所有这些问题都会在适当时候得到解决。

原文译自：https://rbranson.medium.com/10-things-i-hate-about-postgresql-20dbab8c2791

链接

1.https://news.ycombinator.com/item?id=22766681

2.https://www.2ndquadrant.com/en/blog/postgresql-is-the-worlds-best-database/

3.https://blog.sentry.io/2015/07/23/transaction-id-wraparound-in-postgres

4.https://arstechnica.com/information-technology/2015/05/boeing-787-dreamliners-contain-a-potentially-catastrophic-software-bug/

5.https://cloudbasic.net/white-papers/synchronous-vs-asynchronous-replication/

6.https://www.postgresql.org/docs/current/warm-standby.html#SYNCHRONOUS-REPLICATION-PERFORMANCE

7.https://www.cloudkarafka.com/blog/what-does-in-sync-in-apache-kafka-really-mean.html

8.https://galeracluster.com/

9.https://galeracluster.com/2015/07/geo-distributed-database-clusters-with-galera/

10.https://wiki.postgresql.org/wiki/Streaming_Replication

11.https://www.postgresql.org/docs/current/wal-intro.html

12.https://www.postgresql.org/docs/12/logical-replication.html

13.https://www.postgresql.org/docs/12/sql-vacuum.html

14.https://github.com/postgres/postgres/blob/master/src/backend/access/heap/README.HOT

15.http://rhaas.blogspot.com/2018/01/do-or-undo-there-is-no-vacuum.html

16.https://www.pgbouncer.org/

17.http://rachbelaid.com/introduction-to-postgres-physical-storage/

18.https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

19.https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/indexes-and-index-organized-tables.html#GUID-DAEC075B-C16D-4A57-898C-70EBCB364F0C

20.https://www.postgresql.org/docs/12/sql-cluster.html

21.https://www.postgresql.org/docs/12/pgupgrade.html

22.https://www.cybertec-postgresql.com/en/upgrading-postgres-major-versions-using-logical-replication/

23.https://bucardo.org/Bucardo/

24.https://dev.mysql.com/doc/refman/8.0/en/group-replication.html

25.https://galeracluster.com/

26.https://wiki.postgresql.org/wiki/Streaming_Replication#How_to_Use

27.https://www.postgresql.org/docs/12/logical-replication-restrictions.html

28.https://mariadb.com/kb/en/index-hints-how-to-force-query-plans/

29.https://wiki.c2.com/?SufficientlySmartCompiler

30.https://www.percona.com/blog/2019/02/11/compression-options-in-mysql-part-2/

31.https://wiki.postgresql.org/wiki/TOAST

32.https://lwn.net/Articles/415889/

33.https://www.percona.com/blog/2017/11/20/innodb-page-compression/

34.http://smalldatum.blogspot.com/

35.http://smalldatum.blogspot.com/2015/10/wanted-file-system-on-which-innodb.html

36.https://dev.mysql.com/doc/refman/8.0/en/innodb-table-compression.html

37.https://www.2ndquadrant.com/en/blog/pg-phriday-postgres-zfs/

38.https://www.heroku.com/postgres

39.https://www.compose.com/databases/postgresql

40.https://aws.amazon.com/cn/rds/postgresql/

41.https://cloud.google.com/sql

参考

http://mysql.taobao.org/monthly/2015/08/01/

https://momjian.us/main/blogs/pgblog/2020.html#September_16_2020

https://rbranson.medium.com/10-things-i-hate-about-postgresql-20dbab8c2791

https://github.com/digoal/blog