Postgres 9.1 vs Mysql 5.6 InnoDB？

Postgres 9.1 vs Mysql 5.6 InnoDB?

提问by RandomWhiteTrash

Simple question - what would better for a medium/big size database with requirement for compatibility with ACID in 2012.

简单的问题 - 对于 2012 年要求与 ACID 兼容的中/大型数据库，什么会更好。

I have read it all (well most) about mySQL vs pgSQL but most of those posts relate to version 4,5.1 and 7,8 respectively and are quite dated (2008,2009). Its almost 2012 now so I guess we could try and take a fresh look at the issue.

我已经阅读了所有（大部分）关于 mySQL 与 pgSQL 的文章，但其中大部分帖子分别与版本 4,5.1 和 7,8 相关并且已经过时（2008,2009）。现在快到 2012 年了，所以我想我们可以尝试重新审视这个问题。

Basically I would like to know if there is anything in PostgreSQL that out-weights ease of use, availability and larger developer/knowledge base of MySQL.

基本上我想知道 PostgreSQL 中是否有任何东西超过了 MySQL 的易用性、可用性和更大的开发人员/知识库。

Is MySQL's query optimizer still stupid? Is it still super slow on very complicated queries?

MySQL 的查询优化器还傻吗？在非常复杂的查询上它仍然超级慢吗？

Hit me! :)

打我！:)

PS. And don't send me to goggle or wiki. I am looking for few specific points not an overview + I trust StackOverflow more than some random page with 'smart guy' shining his light.

附注。不要把我送到护目镜或维基。我正在寻找一些特定的要点而不是概述 + 我相信 StackOverflow 胜过一些随机页面，“聪明人”闪耀着他的光芒。

Addendum

附录

Size of the project: Say an ordering system with roughly 10-100 orders/day per account, couple of thousand accounts, eventually, each can have several hundred to several thousand users.

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

Better at: being future proof and flexible when it comes to growing and changing requirements. Performance is also important as to keep costs low in hardware department. Also availability of skilled workforce would be a factor.

更擅长：在不断增长和不断变化的需求方面具有前瞻性和灵活性。性能对于保持硬件部门的低成本也很重要。熟练劳动力的可用性也是一个因素。

OLTP or OLAP: OLTP

OLTP 或 OLAP: OLTP

采纳答案by Richard Huxton

Is MySQL's query optimizer still stupid? Is it still super slow on very complicated queries?

MySQL 的查询优化器还傻吗？在非常复杂的查询上它仍然超级慢吗？

Allquery optimizers are stupid at times. PostgreSQL's is less stupid in most cases. Some of PostgreSQL's more recent SQL features (windowing functions, recursive WITH queries etc) are very powerful but if you have a dumb ORM they might not be usable.

所有的查询优化器有时都是愚蠢的。在大多数情况下，PostgreSQL 并不那么愚蠢。PostgreSQL 的一些最近的 SQL 特性（窗口函数、递归 WITH 查询等）非常强大，但如果你有一个愚蠢的 ORM，它们可能无法使用。

Size of the project: Say an ordering system with roughly 10-100 orders/day per account, couple of thousand accounts, eventually, each can have several hundred to several thousand users.

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

Doesn't sound that large - well within reach of a big box.

听起来没有那么大 - 在一个大盒子的范围内。

Better at: being future proof and flexible when it comes to growing and changing requirements.

更擅长：在不断增长和不断变化的需求方面具有前瞻性和灵活性。

PostgreSQL has a strong developer team, with an extended community of contributors. Release policyis strict, with bugfixes-only in the point releases. Always track the latest release of 9.1.x for the bugfixes.

PostgreSQL 拥有强大的开发团队，以及广泛的贡献者社区。发布政策是严格的，只在点发布中修复错误。始终跟踪最新版本的 9.1.x 以进行错误修正。

MySQL has had a somewhat more relaxed attitude to version numbers in the past. That may change with Oracle being in charge. I'm not familiar with the policies of the various forks.

MySQL 过去对版本号的态度稍微宽松一些。这可能会随着 Oracle 的负责而改变。我不熟悉各种分叉的政策。

Performance is also important as to keep costs low in hardware department.

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

I'd be surprised if hardware turned out to be a major component in a project this size.

如果硬件成为这种规模项目的主要组成部分，我会感到惊讶。

Also availability of skilled workforce would be a factor.

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

That's your key decider. If you've got a team of experienced Perl + PostgreSQL hackers sat around idle, use that. If your people know Lisp and MySQL then use that.

那是你的关键决定者。如果您有一群经验丰富的 Perl + PostgreSQL 黑客闲置，请使用它。如果你的人知道 Lisp 和 MySQL，那么就使用它。

OLTP or OLAP: OLTP

OLTP 或 OLAP：OLTP

PostgreSQL has always been strong on OLTP.

PostgreSQL 在 OLTP 方面一直很强大。

My personal viewpoint is that the PostgreSQL mailing list are full of polite, helpful, knowledgeable people. You have direct contact with users with Terabyte databases and hackers who have built major parts of the code. The quality of the support is truly excellent.

我个人的观点是 PostgreSQL 邮件列表充满了礼貌、乐于助人、知识渊博的人。您可以直接接触使用 TB 数据库的用户和构建代码主要部分的黑客。支持的质量确实非常出色。

回答by a_horse_with_no_name

PostgreSQL is a lot more advanced when it comes to SQL features.

PostgreSQL 在 SQL 功能方面要先进得多。

Things that MySQL still doesn't have (and PostgreSQL has):

MySQL 仍然没有的东西（而 PostgreSQL 有）：

• deferrable constraints
• check constraints(MySQL 8.0.16added 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 workwith 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
• partial index
• multi column statistics
• full text search on transactional tables(MySQL 5.6 supports this)
• GIS features on transactional tables
• EXCEPT or INTERSECT operator
• 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 = xor
  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 booleandata type
  _{(treating any expression that can be converted to a non-zero number as "true" is nota proper boolean type)}

• 可延迟约束
• 检查约束（MySQL 8.0. 16添加了它们，MariaDB 10.2 有它们）

• 完全外连接
  MySQL 默默地使用具有一些语法变化的内连接：https:
  //rextester.com/ADME43793
  

• 横向连接

• 正则表达式不适用于 UTF-8（已在MySQL 8.0 中修复）
• 正则表达式不支持替换或子字符串（MySQL 8.0引入）
• 表函数 ( select * from my_function())
• 公用表表达式（随MySQL 8.0引入）
• 递归查询（在MySQL 8.0 中引入）
• 可写 CTE
• 窗口函数（在MySQL 8.0 中引入）
• 基于函数的索引
• 部分索引
• 多列统计
• 事务表上的全文搜索（MySQL 5.6 支持此功能）
• 事务表上的 GIS 功能
• EXCEPT 或 INTERSECT 运算符
• 不能在同一个 select 语句中两次使用临时表
• 您不能在子选择中使用正在更改（更新/删除/插入）的表

• 您不能创建使用派生表的视图（自 MySQL 8.0 起可能）

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

• 语句级读一致性。需要例如：
  update foo set x = y, y = x或
  update foo set a = b, a = a + 100
• 事务性 DDL
• DDL 触发器
• 排除约束
• 键/值存储
• 索引完整的 JSON 文档
• SQL/JSON 路径表达式（自 Postgres 12 起）
• 范围类型
• 域
• 数组（包括数组索引）
• 管理用户权限的角色（组）（MariaDB有它们，在 MySQL 8.0 中引入）
• 并行查询（自 Postgres 9.6 起）
• 并行索引创建（自 Postgres 11 起）
• 用户定义的数据类型（包括检查约束）
• 物化视图
• 自定义聚合
• 自定义窗口函数
• 正确的boolean数据类型
  _{（将任何可以转换为非零数字的表达式视为“true”都不是正确的布尔类型）}

When it comes to Spatial/GIS features Postgres with PostGIS is also much more capable. Hereis a nice comparison.

当涉及到空间/GIS 功能时，带有 PostGIS 的 Postgres 也更强大。这是一个很好的比较。

Not sure what you call "ease of use" but there are several modern SQL featuresthat I would not want to miss (CTEs, windowing functions) that would define "ease of use" for me.

不确定您所说的“易用性”是什么，但我不想错过一些现代 SQL 功能（CTE、窗口函数），它们将为我定义“易用性”。

Now, PostgreSQL is not perfect and probably the most obnoxious thing can be, to tune the dreaded VACUUM process for a heavy write database.

现在，PostgreSQL 并不完美，而且可能是最令人讨厌的事情，为大量写入数据库调整可怕的 VACUUM 过程。

回答by Roman Pekar

As an addition to @a_horse_with_no_name answer, I want to name some features which I like so much in PostgreSQL:

作为@a_horse_with_no_name 答案的补充，我想列举一些我非常喜欢 PostgreSQL 的功能：

• arrays data type;
• hstore extension- very useful for storing key->valuedata, possible to create index on columns of that type;
• various language extensions- I find Python very useful when it comes to unstructured data handling;
• distinct onsyntax - I think this one should be ANSI SQL feature, it looks very natural to me (as opposed to MySQL group by syntax);
• composite types;
• record types;
• inheritance;
• Version 9.3 features:
  • lateral joins- one thing I miss from SQL Server (where it called outer/cross apply);
  • native JSON support;
  • DDL triggers;
  • recursive, materialized, updatableviews;

• 数组数据类型；
• hstore 扩展- 对于存储key->value数据非常有用，可以在该类型的列上创建索引；
• 各种语言扩展——我发现 Python 在处理非结构化数据时非常有用；
• distinct on语法 - 我认为这应该是 ANSI SQL 特性，对我来说它看起来很自然（与 MySQL 按语法分组相反）；
• 复合类型；
• 记录类型；
• 继承；
• 9.3 版特点：
  • 横向连接- 我从 SQL Server 中想念的一件事（它称为外/交叉应用）；
  • 原生 JSON 支持；
  • DDL 触发器；
  • 递归的、物化的、可更新的视图；

回答by Sergey Grigoriev

PostgreSQL is a more mature database, it has a longer history, it is more ANSI SQL compliant, its query optimizer is significantly better. MySQL has different storage engines like MyISAM, InnoDB, in-memory, all of them are incompatible in a sense that an SQL query which runs on one engine may produce a syntax error when executed on another engine. Stored procedures are better in PostgreSQL.

PostgreSQL 是一个更成熟的数据库，它的历史更长，更符合 ANSI SQL，它的查询优化器明显更好。MySQL 有不同的存储引擎，如 MyISAM、InnoDB、in-memory，它们都是不兼容的，因为在一个引擎上运行的 SQL 查询在另一个引擎上执行时可能会产生语法错误。PostgreSQL 中的存储过程更好。