Postgres 9.1与Mysql 5.6 InnoDB相比如何？

Question

简单问题-2012年与ACID兼容的中大型数据库应该选择哪个更好。

我已经阅读了有关MySQL和pgSQL的大部分文章，但这些帖子大多与版本4、5.1和7、8有关，并且相当陈旧（2008年，2009年）。现在已经快到2012年了，因此我想我们可以重新审视这个问题。

基本上，我想知道PostgreSQL是否有任何超越MySQL易用性、可用性和更大开发者/知识库的优势。

MySQL的查询优化器仍然愚蠢吗？在非常复杂的查询上仍然非常慢吗？

回答我！ :)

PS。不要把我送到goggle或wiki。我正在寻找一些具体的观点，而不是概述+我比一些随意网页上的“聪明人”更相信StackOverflow。

附录

项目规模: 假设一个订单系统每个帐户大约有10-100个订单/天，几千个帐户，最终每个帐户可以拥有几百到几千个用户。

更好的是: 在未来证明自己是灵活的，并且可以应对不断增长和变化的需求。性能也很重要，以在硬件部分保持低成本。此外，有熟练人员的可用性也是一个因素。

OLTP或OLAP: OLTP

- RandomWhiteTrash

---

要想得到有用的回答，您需要更准确、更精确地定义一些东西。例如，“better”和“moderately”这样的词汇。是指更好地让您的DBA有时间休息，还是对于那些最近通过MySQL考试的新员工更好？是适用于 CD 集合索引，还是适用于拥有 1000 万用户的消息应用程序？OLTP，OLAP？ - ptomli

对于您来说，什么是中等/大型数据库？ - Hugues Van Landeghem

Answer 1

PostgreSQL在SQL功能方面更加先进。

MySQL仍然没有的一些功能(PostgreSQL都有):

• deferrable constraints

• check constraints (MySQL 8.0.16 added them, MariaDB 10.2 has them)

• full outer join
  MySQL silently uses an inner join with some syntax variations:
  https://rextester.com/ADME43793
  

• lateral joins

• regular expressions don't work with UTF-8 (Fixed with MySQL 8.0)

• regular expressions don't support replace or substring (Introduced with MySQL 8.0)

• table functions ( select * from my_function() )

• common table expressions (Introduced with MySQL 8.0)

• recursive queries (Introduced with MySQL 8.0)

• writeable CTEs

• window functions (Introduced with MySQL 8.0)

• function based index (supported since MySQL 8.0.15)

• partial index

• INCLUDE additional column in an indexes (e.g. for unique indexes)

• multi column statistics

• full text search on transactional tables (MySQL 5.6 supports this)

• GIS features on transactional tables

• EXCEPT or INTERSECT operator (MariaDB has them)

• you cannot use a temporary table twice in the same select statement

• you cannot use the table being changed (update/delete/insert) in a sub-select

• you cannot create a view that uses a derived table (Possible since MySQL 8.0)

    create view x as select * from (select * from y);
  

• statement level read consistency. Needed for e.g.:
  update foo set x = y, y = x or
  update foo set a = b, a = a + 100
  

• transactional DDL

• DDL triggers

• exclusion constraints

• key/value store

• Indexing complete JSON documents

• SQL/JSON Path expressions (since Postgres 12)

• range types

• domains

• arrays (including indexes on arrays)

• roles (groups) to manage user privileges (MariaDB has them, Introduced with MySQL 8.0)

• parallel queries (since Postgres 9.6)

• parallel index creation (since Postgres 11)

• user defined data types (including check constraints)

• materialized views

• custom aggregates

• custom window functions

• proper boolean data type
  _{(treating any expression that can be converted to a non-zero number as "true" is not a proper boolean type)}

说到空间/GIS功能，PostgreSQL搭配PostGIS也更加强大。这里是一个不错的比较。

不确定您所说的"易用性"是什么，但有几个现代SQL特性我不想错过（CTEs、窗口函数），这些对我来说定义了"易用性"。

现在，PostgreSQL并不完美，可能最让人讨厌的事情是为重写数据库调整可怕的VACUUM进程。

- user330315

---

不错的答案 - 很高兴看到这样的比较也在被维护！另一个维护全面最新比较的网站似乎是这里 - 在撰写本文时（2020/03/20），该网站上次更新时间为2020/03/04。 - Vérace

有一个你错过了，我很惊讶 - PostgreSQL 产生的 EXPLAIN (ANALYZE, BUFFERS) <SQL 查询文本> 的输出要优秀得多 - 尽管 8.0.20 看起来可能会缩小差距！ - Vérace

Answer 2

编辑：我在十多年前回答了这个问题。人们显然仍然阅读它，并偶尔点击点赞按钮。虽然我认为广义上说“PostgreSQL比MySQL聪明一些”的说法是正确的，但请不要依赖这么久远的回答来了解当前软件版本的详细信息。

---

MySQL的查询优化器还是很愚蠢吗？在非常复杂的查询上仍然非常慢吗？

所有的查询优化器有时都会变得愚蠢。PostgreSQL的查询优化器在大多数情况下较为聪明。一些最新的SQL功能（窗口函数、递归WITH查询等）非常强大，但如果你使用的是一个愚蠢的ORM，可能无法使用这些功能。

项目规模：假设一个订单系统，每个账户每天大约有10-100个订单，几千个账户，最终每个账户可能有几百到几千个用户。

听起来并不是很大——完全可以通过一个大型服务器来处理。

更好的方面：在未来发展和需求变化时具有良好的可扩展性和灵活性。

PostgreSQL拥有一个强大的开发团队，并有许多贡献者组成的社区。发布政策严格，点版本只进行错误修复。始终跟踪9.1.x系列的最新版本以获取错误修复。

MySQL过去对版本号有一种相对宽松的态度。随着Oracle接管，这种情况可能会改变。我对各个分支的政策不太熟悉。

性能对于保持硬件成本低也很重要。

如果硬件在这个规模的项目中占据主要组成部分，我会感到惊讶。

同时，熟练的劳动力的可用性也是一个因素。

这是你的关键决策者。如果你有一支经验丰富的Perl + PostgreSQL黑客团队闲置着，就使用他们。如果你的人员懂Lisp和MySQL，那就使用它们。

OLTP还是OLAP：OLTP

PostgreSQL在OLTP方面一直表现强劲。

我个人认为，PostgreSQL邮件列表上充满了礼貌、乐于助人、知识渊博的人。你可以直接与拥有TB级数据库的用户和构建代码主要部分的黑客联系。支持的质量真的非常出色。

- Richard Huxton

---

@Richard 另一个要点是，Postgres 的 alter 查询在后台运行。 - ravi404

Answer 3

作为对@a_horse_with_no_name回答的补充，我想列举一些我在PostgreSQL中非常喜欢的功能:

• 数组数据类型；
• hstore扩展 - 用于存储键->值数据非常有用，可以在该类型的列上创建索引；
• 各种语言扩展 - 对于处理非结构化数据，我发现Python非常有用；
• distinct on语法 - 我认为这应该是ANSI SQL功能，它对我来说看起来非常自然（与MySQL group by语法相反）；
• 组合类型；
• 记录类型；
• 继承；
• 版本9.3的功能：
  • lateral joins - 这是我从SQL Server中错过的一个功能（在SQL Server中称为outer/cross apply）；
  • 原生JSON支持；
  • DDL触发器；
  • 递归、物化和可更新视图；

- Roman Pekar

---

我最喜欢Postgres的一点是（截至撰写本文时），就分组而言，它是SQL标准实现中最准确的（据我所知）。如果您在分组时不将列包含在聚合函数或group by子句中，也不依赖于group by子句中包含的列，那么您无法按列进行分组。因此，如果您在group by子句中包含一个表的主键列，则不必包含该表中的所有其他列。在SQL Fiddle上的示例。 - GarethD

@GarethD 哇！我不知道那个。现在我更希望我的下一份工作是PostgreSQL！ - Roman Pekar

MySQL也允许您这样做，但它还允许您简单地省略任何您不关心的列。在结果中，这些列的值未定义，除非您所描述的情况，这是可以预期的。 - rich remer

是的 - DISTINCT ON 是一种没有 sql_mode = ONLY_FULL_GROUP_BY 的 MySQL GROUP BY，但至少开发人员/数据库管理员需要明确它。现在，在MySQL中，您可以使用 FIRST_VALUE() 模拟它 - 甚至不应该有一个选项来使用没有 ONLY_FULL_GROUP_BY 的 GROUP BY - 这应该是从第一天开始就应该（唯一）工作的方式！ - Vérace

Answer 4

PostgreSQL是一种更成熟的数据库，它有着更长的历史，更符合ANSI SQL标准，并且其查询优化器显著更好。MySQL有不同的存储引擎，如MyISAM、InnoDB、内存等，它们在某种程度上是不兼容的，因为在一个引擎上运行的SQL查询，在另一个引擎上执行时可能会产生语法错误。在PostgreSQL中，存储过程更好。

- Sergey Grigoriev

---

从技术上讲，PostgreSQL的历史确实更长，但由于两者都是在1995年5月首次发布（根据维基百科），这似乎并不重要。我不认为PostgreSQL更成熟，这更多是不同设计目标的问题。 - mc0e

@mc0e：问题在于 MySQL 的一些最初的设计目标忽略了关系型数据库管理系统背后的关键原则。 - user1071847

MySQL 有设计目标？哈哈！ - Vérace