Sqlite3 vs Postgres vs Mysql - Rails [closed]

Question

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?

Answer 1

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.

Answer 2

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.

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.

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.

Answer 3

Highly depends on your application.

Generally spoken, any write operation 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 against sqlite: 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.

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

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

Answer 4

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

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.

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

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.

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