postgresql Sqlite3 vs Postgres vs Mysql - Rails

Sqlite3 vs Postgres vs Mysql - Rails

提问by Whenyouforgetapassword

I guess this has been brought up many times, but I'm bringing it up again!!! Anyway... In Ruby on Rails Sqlite3 is already setup and no extra picking and slicing is needed, but... after numerous readings here and other places, some say it's not scalable while others say it can actually be quite good at that. Some say MySQL is much better for bigger projects, while others think, just go with PostgreSQL. I'm interested in hearing your opinion on this. In two scenarios. One where you are starting a little website for news publishing website like CNN news, and the other scenario where you're creating a website similar to Twitter?

我想这已经被提起过很多次了，但我再次提起它！！！无论如何...在Ruby on Rails Sqlite3 中已经设置好并且不需要额外的挑选和切片，但是...经过这里和其他地方的大量阅读后，有人说它不可扩展，而其他人说它实际上可以很好地做到这一点。有人说 MySQL 对于更大的项目要好得多，而其他人则认为，只需使用 PostgreSQL。我很想听听你对此的看法。在两种情况下。一种是为新闻发布网站（如 CNN 新闻）创建一个小网站，另一种是创建类似于 Twitter 的网站？

采纳答案by Hisako

Highly depends on your application.

高度取决于您的应用程序。

Generally spoken, any writeoperation into a sqlite database is slow. Even a plain :update_attribute or :create may take upto 0.5 seconds. But if your App doesn't write much (killer againstsqlite: write to DB on every request!), SQlite is a solid choice for most web apps out there. It is proven to handle small to medium amounts of traffic. Also, it is a very good choice during development, since it needs zero configuration. It performs also very well in your testsuite with the in-memory mode (except you have thousands of migrations, since it rebuilds from scratch every time). Also, it is mostly seamless to switch from sqlite to, eg MySQL if it's performance ins't enough any longer.

一般来说，对 sqlite 数据库的任何写入操作都很慢。即使是简单的 :update_attribute 或 :create 也可能需要 0.5 秒。但是，如果您的应用程序编写的内容不多（对抗sqlite 的杀手：每次请求都写入数据库！），SQlite 是大多数 Web 应用程序的可靠选择。事实证明，它可以处理中小型流量。此外，它在开发过程中是一个非常好的选择，因为它需要零配置。它在使用内存模式的测试套件中也表现得非常好（除非您有数千次迁移，因为它每次都从头开始重建）。此外，如果性能不再足够，从sqlite 切换到例如MySQL 几乎是无缝的。

MySQL is currently a rock-solid choice. Future will tell what happens to MySQL under Oracle.

MySQL 目前是一个坚如磐石的选择。未来将说明 Oracle 下的 MySQL 会发生什么。

PostgreSQL is the fastest one as far as i know, but i didn't use it in production yet. maybe others can tell more.

PostgreSQL是最快的国家之一，据我所知，但我没有在生产中使用它尚未。也许其他人可以告诉更多。

回答by vector

I'd vote for Postgres, it's consistently getting better, especially performance wise if that's a concern. Taking you up on the CNN and Twitter examples, start out with as solid footing as you can. You'll be glad later on down the road.

我会投票支持 Postgres，它一直在变得更好，尤其是在性能方面，如果这是一个问题的话。带您了解 CNN 和 Twitter 示例，从尽可能坚实的基础开始。以后你会很高兴的。

回答by Magnus

For websites, SQLite3 will suffice and scale fine for anything up to higher middle class traffic scenarios. So, unless you start getting hit by millions of requests per hour, there's no need to worry about SQLite3's performance or scalability.

对于网站，SQLite3 就足够了，并且可以很好地扩展到更高的中产阶级流量场景。因此，除非您开始受到每小时数百万个请求的影响，否则无需担心 SQLite3 的性能或可伸缩性。

That said, SQLite3 doesn't support all those typical features that a dedicated SQL server would. Access control is limited to whatever file permissions you can set for UNIX accounts on the machine with your database file, there's no daemon to speak of and the set of built-in functions is rather small. Also, there's no stored procedures of any kind, although you could emulate those with views and triggers.

也就是说，SQLite3 并不支持专用 SQL 服务器所支持的所有典型功能。访问控制仅限于您可以为具有数据库文件的机器上的 UNIX 帐户设置的任何文件权限，没有守护程序可言，内置函数集相当小。此外，没有任何类型的存储过程，尽管您可以模拟具有视图和触发器的存储过程。

If you're worried about any of those points, you should go with PostgreSQL. MySQL has (indirectly) been bought by Oracle, and considering they also had their own database before acquiring MySQL, I wouldn't put it past them to just drop it somewhere along the line. I've also had a far smoother experience maintaining PostgreSQL in the past and - anecdotally - it always felt a bit snappier and more reliable.

如果您担心以上任何一点，您应该使用 PostgreSQL。MySQL 已经（间接）被 Oracle 收购了，考虑到他们在收购 MySQL 之前也有自己的数据库，我不会把它放在他们身边，只是把它放在线上的某个地方。过去，我在维护 PostgreSQL 方面也有过更顺畅的体验，而且——有趣的是——它总是感觉更快捷、更可靠。

回答by Chapley Watson

DISCLAIMER:My opinion is completely bias as I have used mysql since it first came out.

免责声明：我的观点是完全有偏见的，因为我从它第一次出现就使用了 mysql。

Your question brings in another argument about how your development environment should be setup. A number of individuals will argue that you should be using the same dbms in development as you do in testing/production. This is totally dependent upon what you're doing in the first place. Sqlite will work fine, on development, in most cases.

你的问题带来了关于你的开发环境应该如何设置的另一个争论。许多人会争辩说，您应该在开发中使用与测试/生产中相同的 dbms。这完全取决于您首先在做什么。在大多数情况下，Sqlite 将在开发中正常工作。

I've personally been involved with more sites using MySql and MsSql than Postgres.

与 Postgres 相比，我个人参与了更多使用 MySql 和 MsSql 的站点。

I was involved in a project that scrubbed the National Do-Not-Call list against client numbers. We stored that data locally. Some area codes easily have over 5 million records. The app was initially written in .Net using MsSql. It was "not-so-fast". I changed it to use PHP and MySql (Sad says before I found out about Ruby). It would insert/digest 5 million rows in(about) 3 seconds. Which was infinitely faster than processing it through MsSql. We also stored call log data in tables that would grow to 20 million records in less than a day. MySql handled everything we threw at it like a champ. The processing naturally took a hit when we setup replication but it was such a small one that we ignored it.

我参与了一个项目，该项目根据客户号码清理了国家拒绝来电列表。我们将这些数据存储在本地。一些区号很容易就有超过 500 万条记录。该应用程序最初是使用 MsSql 在 .Net 中编写的。这是“不那么快”。我将其更改为使用 PHP 和 MySql（在我发现 Ruby 之前，Sad 说）。它会在（大约）3 秒内插入/消化 500 万行。这比通过 MsSql 处理它快得多。我们还将通话记录数据存储在表格中，这些表格将在不到一天的时间内增长到 2000 万条记录。MySql 像冠军一样处理了我们扔给它的所有东西。当我们设置复制时，处理自然会受到影响，但它是如此之小以至于我们忽略了它。

It really comes down to your project and what solution fits the need of the project.

这实际上归结为您的项目以及适合项目需求的解决方案。