用于 Web 应用程序的 MySQL 与 PostgreSQL

MySQL vs PostgreSQL for Web Applications

提问by cnu

I am working on a web application using Python (Django) and would like to know whether MySQL or PostgreSQL would be more suitable when deploying for production.

我正在使用 Python (Django) 开发一个 Web 应用程序，想知道在部署生产时，MySQL 还是 PostgreSQL 更适合。

In one podcastJoel said that he had some problems with MySQL and the data wasn't consistent.

在一个播客中，Joel 说他在使用 MySQL 时遇到了一些问题，并且数据不一致。

I would like to know whether someone had any such problems. Also when it comes to performance which can be easily tweaked?

我想知道是否有人遇到过这样的问题。此外，当涉及到可以轻松调整的性能时？

回答by Josh

A note to future readers:The text below was last edited in August 2008. That's nearly 11 years ago as of this edit. Software can change rapidly from version to version, so before you go choosing a DBMS based on the advice below, do some research to see if it's still accurate. Check for newer answers below.

给未来读者的提示：下面的文字最后一次编辑是在 2008 年 8 月。那是近 11 年前的编辑。软件可能会随版本快速变化，因此在您根据以下建议选择 DBMS 之前，请先进行一些研究，看看它是否仍然准确。在下面查看更新的答案。

---

---

Better?

更好的？

MySQL is much more commonly provided by web hosts.

MySQL 通常由 Web 主机提供。

PostgreSQL is a much more mature product.

PostgreSQL 是一个更加成熟的产品。

There's this discussion addressing your "better" question

有这个讨论解决你的“更好”的问题

Apparently, according to this web page, MySQL is fast when concurrent access levels are low, and when there are many more reads than writes. On the other hand, it exhibits low scalability with increasing loads and write/read ratios. PostgreSQL is relatively slow at low concurrency levels, but scales well with increasing load levels, while providing enough isolation between concurrent accesses to avoid slowdowns at high write/read ratios. It goes on to link to a number of performance comparisons, because these things are very... sensitive to conditions.

显然，根据此网页，当并发访问级别较低且读取多于写入时，MySQL 速度很快。另一方面，随着负载和写入/读取比率的增加，它表现出低可扩展性。PostgreSQL 在低并发级别下相对较慢，但随着负载级别的增加而扩展良好，同时在并发访问之间提供足够的隔离以避免在高写/读比率下速度变慢。它继续链接到许多性能比较，因为这些东西对条件非常......敏感。

So if your decision factor is, "which is faster?" Then the answer is "it depends. If it really matters, test your application against both." And if you really, really care, you get in two DBAs (one who specializes in each database) and get them to tune the crap out of the databases, and then choose. It's astonishing how expensive goodDBAs are; and they are worth every cent.

因此，如果您的决策因素是“哪个更快？”那么答案是“这取决于。 如果真的很重要，请针对两者测试您的应用程序。”如果您真的非常关心，您会得到两个 DBA（一个专门从事在每个数据库中）并让他们调整数据库中的废话，然后选择。令人惊讶的是，优秀的DBA 是多么昂贵；和他们是值得每一分钱。

When it matters.

重要的时候。

Which it probably doesn't, so just pick whichever database you like the sound of and go with it; better performance can be bought with more RAM and CPU, and more appropriate database design, and clever stored procedure tricks and so on - and all of that is cheaper and easier for random-website-X than agonizing over which to pick, MySQL or PostgreSQL, and specialist tuning from expensive DBAs.

它可能不会，所以只需选择您喜欢的任何数据库并使用它；更好的性能可以通过更多的 RAM 和 CPU、更合适的数据库设计、巧妙的存储过程技巧等等来购买——所有这些对于 random-website-X 来说比选择 MySQL 或 PostgreSQL 更便宜、更容易，以及来自昂贵的 DBA 的专业调优。

---

---

乔尔还在那个播客中说，评论会回来咬他，因为人们会说 MySQL 是一堆废话——乔尔无法取回countcount一行。轶事的复数不是数据。 He said他说：

MySQL is the only database I've ever programmed against in my career that has had data integrity problems, where you do queries and you get nonsense answers back, that are incorrect.

MySQL 是我在我的职业生涯中使用过的唯一一个存在数据完整性问题的数据库，在那里你进行查询并得到无意义的答案，这是不正确的。

and he also said:

他还说：

It's just an anecdote. And that's one of the things that frustrates me, actually, about blogging or just the Internet in general. [...] There's just a weird tendency to make anecdotes into truths and I actually as a blogger I'm starting to feel a little bit guilty about this

这只是一个轶事。这是令我沮丧的事情之一，实际上，关于博客或一般的互联网。[...] 只是有一种奇怪的倾向将轶事变成真相，实际上作为博主，我开始对此感到有些内疚

回答by Summer

Just chiming in many months later.

几个月后才响起。

The geographical capabilities of the two databases are very, very different. PostgreSQL has the exceptional PostGIS extension. MySQL's geographical functionality is practically zero in comparison.

这两个数据库的地理功能非常非常不同。PostgreSQL 具有出色的 PostGIS 扩展。相比之下，MySQL 的地理功能几乎为零。

If your web service has a location component, choose PostgreSQL.

如果您的 Web 服务具有位置组件，请选择 PostgreSQL。

回答by Grey Panther

I haven't used Django, but I have used both MySQL and PostgreSQL. If you will be using your database only as a back-end for Django, it doesn't matter that much, because it will abstract away most of the differences. PostgreSQL is a little more scalable (it doesn't hit the brick wall as fast as MySQL as data size / client count increases).

我没用过 Django，但我用过 MySQL 和 PostgreSQL。如果您将数据库仅用作 Django 的后端，则没有那么重要，因为它会抽象出大部分差异。PostgreSQL 的可扩展性更强一些（随着数据大小/客户端数量的增加，它不像 MySQL 那样快速地撞墙）。

The real difference comes in if you are doing a new system. Then I'ld recommend PostgreSQL hands down, because it has a lot more features which make your DB layer much more customizable so that you can fine-tune it to any requirements you might have.

如果你在做一个新系统，真正的区别就出现了。然后我会毫不犹豫地推荐 PostgreSQL，因为它具有更多功能，可以使您的数据库层更加可定制，以便您可以根据您可能有的任何要求对其进行微调。

回答by Greg Hewgill

Although it's a bit out of date, it would be worth reading the MySQL Gotchaspage. Many of the items listed there are still true, to the best of my knowledge.

尽管它有点过时，但值得阅读MySQL Gotchas页面。据我所知，那里列出的许多项目仍然是真实的。

I use PostgreSQL.

我使用 PostgreSQL。

回答by Lance Rushing

I use both extensively. My choice for a particular project boils down to:

我广泛使用两者。我对特定项目的选择归结为：

• Licensing - Are you going to distribute your app (IANAL)
• Existing Infrastructure and Knowledge Base
• Any special sauceyou have to have.

• 许可 - 您是否打算分发您的应用程序 (IANAL)
• 现有基础设施和知识库
• 你必须有任何特殊的酱汁。

By special sauceI things like:

通过特殊的酱汁，我喜欢：

• easy/cheap replication = MySQL
• Huge dataset problems with small results = PostgreSQL. Use the language extensions, and have very efficient data operations. (PL/Python, PL/TCL, PL/Perl, etc)
• Interface with R Statistical Libraries = PostgreSQL PL/Ravailable in debian/ubuntu

• 简单/便宜的复制 = MySQL
• 小结果的巨大数据集问题= PostgreSQL。使用语言扩展，并且有非常高效的数据操作。（PL/Python、PL/TCL、PL/Perl 等）
• 与 R 统计库的接口 = PostgreSQL PL/R在 debian/ubuntu 中可用

回答by SCdF

Well, I don't think you should be using a different database brand in anything past development (build, staging, prod) as that will come back to bite you.

好吧，我认为您不应该在过去的任何开发（构建、暂存、生产）中使用不同的数据库品牌，因为这会反过来咬您。

From how I understand it PostgreSQL is a more 'correct' database implementation while mySQl is less correct (less compliant) but faster.

从我的理解来看，PostgreSQL 是一个更“正确”的数据库实现，而 mySQl 不太正确（不太合规）但速度更快。

So if you are pretty much writing a CRUD application mySQL is the way to go. If you require certain features out of your database (if you're not sure then you don't) then you may want to look into postgreSQL.

因此，如果您几乎要编写 CRUD 应用程序，那么 mySQL 就是您要走的路。如果您需要数据库中的某些功能（如果您不确定则不需要），那么您可能需要查看 postgreSQL。

回答by tslocum

If you are writing an application which may get distributed quite a bit on different servers, MySQL carries a lot of weight over PostgreSQL because of the portability. PostgreSQL is difficult to find on less than satisfactory web hosts, albet there are a few. In most regards, PostgreSQL is slower than MySQL, especially when it comes to fine tuning in the end. All in all, I'd say to give PostgreSQL a shot for a short amount of time, that way you aren't completely avoiding it, and then make a judgement.

如果您正在编写一个可能在不同服务器上分布相当多的应用程序，由于可移植性，MySQL 比 PostgreSQL 更重要。PostgreSQL 很难在不太令人满意的网络主机上找到，尽管有一些。在大多数方面，PostgreSQL 都比 MySQL 慢，尤其是在最后的微调方面。总而言之，我会说在短时间内给 PostgreSQL 一个机会，这样你就不会完全避免它，然后再做出判断。

回答by Stephen Cox

Thank you. I've used Django with MySQL and it's fine. Choose your database on the features you need. Hard to compare MySQL and Postgres. Better to compare Postgress to SQl Server.

谢谢你。我已经将 Django 与 MySQL 一起使用，它很好。根据您需要的功能选择您的数据库。很难比较 MySQL 和 Postgres。最好将 Postgress 与 SQL Server 进行比较。

回答by marcospereira

@WolfmanDragon

@狼人龙

PostgreSQL has (tiny) support for objects, but it is, by nature, a relational database. From its about page:

PostgreSQL 对对象有（微小的）支持，但它本质上是一个关系数据库。从它的关于页面：

PostgreSQL is a powerful, open source relationaldatabase system.

PostgreSQL 是一个功能强大的开源关系数据库系统。

回答by WolfmanDragon

MySQL is a relational database management systemwhile PostgreSQL is an object-relational database management system. PostgreSQL is suited well for C++ or Java developers, as it gives us more control over how queries are written. ORDBMS also gives us Objects and User Defined Types. The SQL queries themselves are much closer to the ISO standards than MySQL.
Do you need an ORDBMS or a RDBMS? That will better answer your question.

MySQL 是关系型数据库管理系统，而 PostgreSQL 是对象关系型数据库管理系统。PostgreSQL 非常适合 C++ 或 Java 开发人员，因为它让我们可以更好地控制查询的编写方式。ORDBMS 还为我们提供了对象和用户定义的类型。SQL 查询本身比 MySQL 更接近 ISO 标准。
您需要 ORDBMS 还是 RDBMS？那会更好地回答你的问题。