MySQL 与 PostgreSQL？我应该为我的 Django 项目选择哪个？

MySQL vs PostgreSQL? Which should I choose for my Django project?

提问by rmh

My Django project is going to be backed by a large database with several hundred thousand entries, and will need to support searching (I'll probably end up using djangosearch or a similar project.)

我的 Django 项目将得到一个包含数十万个条目的大型数据库的支持，并且需要支持搜索（我可能最终会使用 djangosearch 或类似的项目。）

Which database backend is best suited to my project and why? Can you recommend any good resources for further reading?

哪个数据库后端最适合我的项目，为什么？你能推荐任何好的资源以供进一步阅读吗？

采纳答案by Van Gale

As someone who recently switched a project from MySQL to Postgresql I don't regret the switch.

作为最近将项目从 MySQL 切换到 Postgresql 的人，我并不后悔切换。

The main difference, from a Django point of view, is more rigorous constraint checking in Postgresql, which is a good thing, and also it's a bit more tedious to do manual schema changes (aka migrations).

从 Django 的角度来看，主要区别在于 Postgresql 中更严格的约束检查，这是一件好事，而且手动更改架构（又名迁移）也有点乏味。

There are probably 6 or so Django database migration applications out there and at least one doesn't support Postgresql. I don't consider this a disadvantage though because you can use one of the others or do them manually (which is what I prefer atm).

可能有 6 个左右的 Django 数据库迁移应用程序，至少有一个不支持 Postgresql。我不认为这是一个缺点，因为您可以使用其他方法之一或手动执行它们（这是我更喜欢 atm 的方式）。

Full text search mightbe better supported for MySQL. MySQL has built-in full text search supported from within Django but it's pretty useless (no word stemming, phrase searching, etc.). I've used django-sphinxas a better option for full text searching in MySQL.

MySQL可能更好地支持全文搜索。MySQL 内置了 Django 支持的全文搜索，但它非常无用（没有词干、短语搜索等）。我使用django-sphinx作为在 MySQL 中进行全文搜索的更好选择。

Full text searching is built-in with Postgresql 8.3 (earlier versions need TSearch module). Here's a good instructional blog post: Full-text searching in Django with PostgreSQL and tsearch2

全文搜索内置于 Postgresql 8.3（早期版本需要 TSearch 模块）。这是一篇很好的教学博客文章：使用 PostgreSQL 和 tsearch2 在 Django 中进行全文搜索

回答by oivvio

For whatever it's worth the the creators of Django recommend PostgreSQL.

对于任何值得，Django 的创建者都推荐 PostgreSQL。

If you're not tied to any legacy system and have the freedom to choose a database back-end, we recommend PostgreSQL, which achives a fine balance between cost, features, speed and stability. (The Definitive Guide to Django, p. 15)

如果您不受任何遗留系统的束缚并且可以自由选择数据库后端，我们推荐 PostgreSQL，它在成本、功能、速度和稳定性之间实现了良好的平衡。（Django 权威指南，第 15 页）

回答by vartec

large database with several hundred thousand entries,

拥有数十万条目的大型数据库，

This is not large database, it's very small one.

这不是大数据库，它是非常小的一个。

I'd choose PostgreSQL, because it has a lot more features. Most significant it this case: in PostgreSQL you can use Python as procedural language.

我会选择 PostgreSQL，因为它有更多的功能。最重要的是这种情况：在 PostgreSQL 中，您可以使用 Python 作为过程语言。

回答by Joonas Pulakka

Go with whichever you're more familiar with. MySQL vs PostgreSQL is an endless war. Both of them are excellent database engines and both are being used by major sites. It really doesn't matter in practice.

选择你更熟悉的那个。MySQL vs PostgreSQL 是一场无休止的War。它们都是出色的数据库引擎，并且都被主要站点使用。在实践中真的没有关系。

回答by Kedare

Even if Postgresql looks better, I find it has some performances issues with Django:

即使 Postgresql 看起来更好，我发现它与 Django 有一些性能问题：

Postgresql is made to handle "long connections" (connection pooling, persistant connections, etc.)

Postgresql 用于处理“长连接”（连接池、持久连接等）

MySQL is made to handle "short connections" (connect, do your queries, disconnect, has some performances issues with a lot of open connections)

MySQL 用于处理“短连接”（连接、执行查询、断开连接、有很多打开连接的性能问题）

The problem is that Django does not support connection pooling or persistant connection, it has to connect/disconnect to the database at each view call.

问题是 Django 不支持连接池或持久连接，它必须在每次视图调用时连接/断开与数据库的连接。

It will works with Postgresql, but connecting to a Postgresql cost a LOT more than connecting to a MySQL database (On Postgresql, each connection has it own process, it's a lot slower than just popping a new thread in MySQL).

它将与 Postgresql 一起使用，但连接到 Postgresql 比连接到 MySQL 数据库花费更多（在 Postgresql 上，每个连接都有自己的进程，这比在 MySQL 中弹出一个新线程要慢得多）。

Then you get some features like the Query Cache that can be really useful on some cases. (But you lost the superb text search of PostgreSQL)

然后你会得到一些特性，比如查询缓存，这些特性在某些情况下非常有用。（但是你失去了 PostgreSQL 出色的文本搜索功能）

回答by Emile Bergeron

All the answers bring interesting information to the table, but some are a little outdated, so here's my grain of salt.

所有的答案都带来了有趣的信息，但有些答案有点过时，所以这是我的看法。

As of 1.7, migrationsare now an integral feature of Django. So they documented the main differences that Django developers might want to know beforehand.

从 1.7 开始，迁移现在是 Django 的一个组成部分。因此，他们记录了 Django 开发人员可能想事先了解的主要差异。

Backend Support

Migrations are supported on all backends that Django ships with, as well as any third-party backends if they have programmed in support for schema alteration (done via the SchemaEditorclass).

However, some databases are more capable than others when it comes to schema migrations; some of the caveats are covered below.

PostgreSQL

PostgreSQL is the most capable of all the databases here in terms of schema support; the only caveat is that adding columns with default values will cause a full rewrite of the table, for a time proportional to its size.

For this reason, it's recommended you always create new columns with null=True, as this way they will be added immediately.

MySQL

MySQL lacks support for transactions around schema alteration operations, meaning that if a migration fails to apply you will have to manually unpick the changes in order to try again (it's impossible to roll back to an earlier point).

In addition, MySQL will fully rewrite tables for almost every schema operation and generally takes a time proportional to the number of rows in the table to add or remove columns. On slower hardware this can be worse than a minute per million rows - adding a few columns to a table with just a few million rows could lock your site up for over ten minutes.

Finally, MySQL has reasonably small limits on name lengths for columns, tables and indexes, as well as a limit on the combined size of all columns an index covers. This means that indexes that are possible on other backends will fail to be created under MySQL.

SQLite

SQLite has very little built-in schema alteration support, and so Django attempts to emulate it by:

• Creating a new table with the new schema
• Copying the data across
• Dropping the old table
• Renaming the new table to match the original name

This process generally works well, but it can be slow and occasionally buggy. It is not recommended that you run and migrate SQLite in a production environment unless you are very aware of the risks and its limitations; the support Django ships with is designed to allow developers to use SQLite on their local machines to develop less complex Django projects without the need for a full database.

后端支持

Django 附带的所有后端以及任何第三方后端都支持迁移，如果它们已编程以支持模式更改（通过SchemaEditor类完成）。

但是，在架构迁移方面，某些数据库比其他数据库更强大；下面介绍了一些注意事项。

PostgreSQL

就模式支持而言，PostgreSQL 是这里所有数据库中最强大的；唯一需要注意的是，添加具有默认值的列将导致表的完全重写，时间与其大小成正比。

出于这个原因，建议您始终使用 null=True 创建新列，因为这样它们将被立即添加。

MySQL

MySQL 不支持围绕模式更改操作的事务，这意味着如果迁移失败，您将不得不手动取消更改以便重试（不可能回滚到更早的点）。

此外，MySQL 将针对几乎每个模式操作完全重写表，并且通常需要与表中的行数成比例的时间来添加或删除列。在较慢的硬件上，这可能比每百万行一分钟更糟糕 - 在只有几百万行的表中添加几列可能会将您的站点锁定超过十分钟。

最后，MySQL 对列、表和索引的名称长度以及索引涵盖的所有列的组合大小有相当小的限制。这意味着在其他后端可能的索引将无法在 MySQL 下创建。

SQLite

SQLite 几乎没有内置的模式更改支持，因此 Django 尝试通过以下方式模拟它：

• 使用新架构创建新表
• 复制数据
• 丢弃旧桌子
• 重命名新表以匹配原始名称

这个过程通常运行良好，但它可能很慢并且偶尔会出现错误。除非您非常了解风险及其局限性，否则不建议您在生产环境中运行和迁移 SQLite；Django 附带的支持旨在允许开发人员在他们的本地机器上使用 SQLite 来开发不太复杂的 Django 项目，而无需完整的数据库。

回答by Jonatan Littke

When a migration fails in django-south, the developers encourage you not to use MySQL:

当 django-south 中的迁移失败时，开发人员鼓励您不要使用 MySQL：

! The South developers regret this has happened, and would
! like to gently persuade you to consider a slightly
! easier-to-deal-with DBMS (one that supports DDL transactions)

回答by peufeu

To add to previous answers :

要添加到以前的答案：

• "Full text search might be better supported for MySQL"

• “MySQL 可能更好地支持全文搜索”

The FULLTEXT index in MySQL is a joke.

MySQL 中的 FULLTEXT 索引是个笑话。

• It only works with MyISAM tables, so you lose ACID, Transactions, Constraints, Relations, Durability, Concurrency, etc.
• INSERT/UPDATE/DELETE to a largish TEXT column (like a forum post) will a rebuild a large part of the index. If it does not fit in myisam_key_buffer, then large IO will occur. I've seen a single forum post insertion trigger 100MB or more of IO ... meanwhile the posts table is exclusiely locked !
• I did some benchmarking (3 years ago, may be stale...) which showed that on large datasets, basically postgres fulltext is 10-100x faster than mysql, and Xapian 10-100x faster than postgres (but not integrated).

• 它仅适用于 MyISAM 表，因此您会丢失 ACID、事务、约束、关系、持久性、并发性等。
• INSERT/UPDATE/DELETE 到较大的 TEXT 列（如论坛帖子）将重建索引的很大一部分。如果在 myisam_key_buffer 中放不下，那么就会发生大 IO。我见过一个论坛帖子插入会触发 100MB 或更多的 IO ......同时帖子表被独家锁定！
• 我做了一些基准测试（3 年前，可能是陈旧的......），它表明在大型数据集上，基本上 postgres 全文比 mysql 快 10-100 倍，Xapian 比 postgres 快 10-100 倍（但未集成）。

Other reasons not mentioned are the extremely smart query optimizer, large choice of join types (merge, hash, etc), hash aggregation, gist indexes on arrays, spatial search, etc which can result in extremely fast plans on very complicated queries.

其他未提及的原因是极其智能的查询优化器、大量的连接类型选择（合并、散列等）、散列聚合、数组上的要点索引、空间搜索等，这些可以在非常复杂的查询上产生极快的计划。

回答by awithrow

Will this application be hosted on your own servers or by a hosting company? Make sure that if you are using a hosting company, they support the database of choice.

此应用程序会托管在您自己的服务器上还是托管公司？如果您使用的是托管公司，请确保他们支持您选择的数据库。

回答by Meros

There is a major licensing difference between the two db that will affect you if you ever intend to distribute code using the db. MySQL's client libraries are GPL and PostegreSQL's is under a BSD like license which might be easier to work with.

如果您打算使用 db 分发代码，这两个 db 之间存在主要的许可差异，这会影响您。MySQL 的客户端库是 GPL 的，而 PostegreSQL 是在类似 BSD 的许可下使用的，这可能更容易使用。