People ask: Can I use SQLite in production? If not, why not? Is the cost of using another RDBMS justified by the benefits? Which RDBMS should I use?
Short answer: Using PostgreSQL is the safe way to go, but sometimes you can use SQLite.
Long answer:
It is said you can’t use SQLite in production because it doesn’t support concurrency (no more than one user can be writing to the database at the same time) and it can’t scale. But let’s be pragmatic. Many applications are used only by a few users. For example, they are used on an intranet of a small company. Or maybe they have some hundreds of read only users, but only ten or twenty users who can write to the database. If you have only ten users who can write to the database, the probability of two of them attempting to write at the same millisecond is very low, and even then, it’s not a big deal; either one of them will wait (for a few milliseconds), or, at the very worst, they’ll get a server error and retry.
I’m using SQLite in production in one application that has no models; all the data is stored elsewhere and is retrieved through a webservice API. The only significant data stored in SQLite is the users’ names and passwords used for login, by django.contrib.auth. It’s hardly three users. Recreating them would be easier than maintaining a PostgreSQL installation. So SQLite it is.
What if your database is small and you don’t have many users, but you store mission-critical data in the database? That’s a hard one. The thing is, no-one really knows if SQLite is appropriate, because no-one is using it for mission-critical data. Thunderbird doesn’t use it for storing emails, but for storing indexes, which can be recreated. Likewise for Firefox. The SQLite people claim it’s appropriate for mission-critical applications, but industry experience on that is practically nonexistent. I’ve never seen corruption in SQLite. I’ve seen corruption in PostgreSQL, but we are comparing apples to oranges. I have a gut feeling (but no hard data) that I can trust SQLite more than MySQL.
If I ever choose to use SQLite for mission-critical data, I will make sure I not just backup the database file, but also backup a plain text dump of the database. I trust plain text dumps more than database files in case there is silent corruption that can go unnoticed for some time.
So, don’t listen to all the hype about big data, load balancing, replication, and all that. Many applications have a small database and accept less than one request per second at rush hour. Trucks have valid use cases, but when I want to carry a couple of books I use a bicycle.
Update:
In an interesting online conversation, Daniel Greenfeld, co-author of Two Scoops of Django, has pointed out the real risk of the database outgrowing SQLite in the future, which will lead to a painful migration experience:
[O]ur personal experience is that SQLite works great in production until it doesn’t. Then it’s a huge pain to migrate off to something, be it PostgreSQL or MySQL (migration via fixtures is painful, if it works at all).
…
[Users]’ll say something like, “Five users can’t break SQLite!”. That’s true, but if their project grows suddenly by management fiat to one hundred users, they might run into difficulties. For what it’s worth, this isn’t far-fetched: I experienced it personally in 2009 and I know several others who have run into the same thing since.
So, keep that in mind. You never know what will happen next year. PostgreSQL is the safe choice.
As for MySQL, I never understood why it has become so popular when there’s PostgreSQL around. My only explanation is it was marketed better. PostgreSQL is more powerful, it is easier, and it has better documentation. Every now and then I hear of silly MySQL problems that are unheard of in PostgreSQL (the latest I heard is the broken Unicode support). If you have a reason to use MySQL, it’s probably that you already know it, or that people around you know it (e.g. it is company policy). Even then, some people insist it’s better to migrate.
This post is largely taken from my book, Deploying Django on a single Debian or Ubuntu server.
Update:
In the post above I write this:
I’ve never seen corruption in SQLite. I’ve seen corruption in PostgreSQL, but we are comparing apples to oranges.
My colleague George trusts PostgreSQL so much he thought I made an error and meant “MySQL”. But no, there’s no error. I’ve seen corruption on a PostgreSQL blob. It had been saved by some software, it was backed up with pg_dump and duplicity, it was restored with duplicity and pg_restore, and some months after the restore I found out one of the blobs was corrupted. Trying to find out what exactly the problem was is considerable work and I haven’t done it because the customer owes me money, but, like George, I trust PostgreSQL so much that I’m confident the error was somewhere else.
I hope it’s clear why I said we are comparing apples to oranges.