Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.

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?

You can't ask what's better if you don't have an image in your mind about how many people you'll serve. Take for example any of the three databases you just mentioned - they have to handle various things, such as users connecting to it and actually finding the information on a hard drive. Whatever you do in a program or whatever magic you pull out - limits of hardware are known and sites like twitter or CNN aren't even using databases in traditional way. If by some miracle your site becomes popular tomorrow - you'll have to throw away all you've coded. – N.B. Jan 14, 2013 at 16:20 You don't have to be rude and snap. Sorry if I didn't supply enough info, but I didn't know I guess... I feel like I did supply some information though, Twitter traffic serves a lot of people, millions, CNN, hundreds of thousands. – Whenyouforgetapassword Jan 14, 2013 at 16:36 I'm sorry, but I wasn't rude nor did I snap, I'm just trying to comment on your question to give you a hint that you cant ask what's better and then use big websites as an example. There are simply too many ifs involved and what not. – N.B. Jan 14, 2013 at 16:43 I don't know what ifs are, but anyway it doesn't bother me that much and all of you helped me decide what I will be using so it doesn't matter that much anymore. Weather you were rude or not, doesn't matter, I forgive you and THANKS! ;) – Whenyouforgetapassword Jan 14, 2013 at 16:56

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 up to 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 test suite 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 its performance isn'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 haven't used it in production yet . Maybe others can tell more.

If you, as well as Magnus say it's mostly seamless to switch from Sqlite/3 to MySQL or PostgreSQL for that matter, then i'll just stick with Sqlite3 until that time is reached and continue on what I'm doing and quit all these troubles I'm having on my Database management system decision making :P Thanks :) – Whenyouforgetapassword Jan 14, 2013 at 16:41 There is general misapprehension of sqlite's features and capabilities. sqlite is faster than most enterprise db engines and on average 4x faster than postgresql. sqlite sacrifices complex features in favour of speed and simplicity. One of it's main drawbacks is read/write concurrency in high traffic client/server implementations. Certainly, in development it is a fast, effective and low maintenance option. If combined with an engine-agnostic sql connection module - you can develop using sqlite and switch to an enterprise db at testing. See sqlite.org/whentouse.html – venzen Jun 17, 2013 at 14:11 I'll definitely give it another try, but I've had some trouble setting it up with Rails on my Windows 7. :( If I can't make it work this time, then I don't know what to do. – Whenyouforgetapassword Jan 14, 2013 at 16:17 This should be a comment, not an answer. Nothing is backed up by facts, you're simply stating an opinion. – N.B. Jan 14, 2013 at 16:17 I don't know what you mean, but based on what Magnus and Hisako are saying about the easy converting from Sqlite3, I'll definitely stick to it until I want to change to something else like MySQL or PostgreSQL. Thanks! |m| – Whenyouforgetapassword Jan 14, 2013 at 16:44

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.

I see, so with other words, Twitter shouldn't have chosen Sqlite3, or do you think Sqlite3 would have been able to nicely handle all the "tweets"? – Whenyouforgetapassword Jan 14, 2013 at 16:13 Well, it does depend a bit on the schema they used and there is a hard limit to the size of database files in SQLite. I'd probably have gone for SQLite myself if I'd been in their shoes, seeing as their requirements weren't all that complex. Of course, by now they probably have enough data that they might get close to the size limits imposed by SQLite. On the bright side, converting projects from SQLite to use other databases tends to be rather straightforward, so starting with that isn't much of a problem. – Magnus Jan 14, 2013 at 16:23 If you, as well as Magnus say it's mostly seamless to switch from Sqlite/3 to MySQL or PostgreSQL for that matter, then i'll just stick with Sqlite3 until that time is reached and continue on what I'm doing and quit all these troubles I'm having on my Database management system decision making :P Thanks :) -- this is basically the same post as to Hisako, but you both helped me superbly ;) -- – Whenyouforgetapassword Jan 14, 2013 at 16:41 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.

Honestly I'm trying to avoid MySQL after reading a few articles about MySQL's negative sides. I'm not any fan boy of others and then insulting MySQL users, it's just that it seems so widely used that I want to not use it.Use something a little newer.Though the biggest reason is that I don't know how to set it up with rails nor do I want to, but if I wanted to I wouldn't have known how, but again I could have done some searches and answers would be found. Anyway, I guess MySQL is good, but it just doesn't lure me to it for some dumb reason. I did use it one time though with PHP.:PThanks anyway! – Whenyouforgetapassword Jan 14, 2013 at 16:52 Setting it up with Rails is as simple as installing Mysql, adding the mysql gem to your Gemfile, and finally setting up the database.yml. You could check into the Nosql genre. What you ultimately end up using is whatever is the best solution for your project. We use Oracle but that's only because we have 100's of millions of records and 10,000 different things firing off at the same time. I wouldn't suggest using it unless you're a Fortune 10 company. Even then we could setup a mysql farm and drop Oracle. – Chapley Watson Jan 14, 2013 at 17:00 We? Are you some kind of moderator on StackOverflow. But anyways Magnus and Hitasu told be about the easiness of converting and changing to other databases from Sqlite3, so if I ever need to change to MySQL I'll just swap :D – Whenyouforgetapassword Jan 14, 2013 at 17:03 No, the company I work for is a large corp. You really need to try out as much of the different technologies that you can. It'll come in handy later when you need to figure out what is right for your particular project. – Chapley Watson Jan 14, 2013 at 17:32