How to decide whether I need to transition away from sqlite [closed]

Question

I'm creating a website using django. It's just about done but it hasn't gone live yet. I'm trying to decide whether SQLite is good enough for the site or if it would be worthwhile to use PostgreSQL now, at the beginning, rather than risk needing to transition to it later. (In this post, I mention PostgreSQL because that's the other contender for me. I'm sure similar analysis could be made with MySQL or Oracle.)

I could use some input from people about how they decide upon what database to use with their django projects.

Here's what I currently understand about this:

• From my experience, SQLite is super easy. I don't need to worry about installing some other dependency for it and it pretty much just works out of the box with django.

• From my research online, it seems that SQLite is able to handle quite a bit of load before becoming a performance bottleneck.

Here's what I don't know:

• What would be involved with me transitioning to PostgreSQL from SQLite? Again, I'm currently in a development-only phase and therefore would not need to transition any database data from SQLite. Is it pretty much just a matter of installing PostgreSQL on the server and then tweaking the settings.py file to use it? I doubt it, but would any of my django code need to change? (I don't have any raw SQL queries - my database access is limited to django's model API.)

• From a performance perspective, is PostgreSQL simply better in every way over SQLite? Or does SQLite have certain advantages over PostgreSQL?

• Performance aside, does using PostgreSQL offer other deployment benefits over SQLite?

Essentially I'm thinking that SQLite is good enough for my little site. What's the odds of it becoming really popular? Probably not that great. SQLite is working for me now and would require no changes from my end. However, I'm concerned that maybe using PostgreSQL from the beginning would be easy and that I'll kick myself a year from now for not making the transition. I'm torn though - if I go to PostgreSQL, perhaps it will be unnecessary hassle on my part with no benefit.

Does anyone have general guidelines for deciding between SQLite and something else?

Thanks!

Answer 1

Here are a few things to consider.

SQLite does not allow concurrent writes. If an insert or an update is issued, the entire database is locked, and even readers are not allowed during a short moment of the actual update. If your application is going to have many users that update its state (posting comments, adding likes, etc), this is going to become a bottleneck. Unpleasant slowdowns will time to time occur even with a relatively small number of users.

SQLite does not allow several processes to efficiently access a database. There can only be one writing process, even if you have multiple CPUs, and even then the locking mechanism is very inefficient. To ensure data integrity you'd need to jump through many hoops, and every update will be horribly slow. Postgres can reorder locks optimally, lock tables at row level or even update without locking, so it will run circles around SQLite performance-wise, unless your database is strictly read-only.

SQLite does not allow data partitioning or even putting different tables to different tablespaces; everything lives in one file. If you have a "hot" table that is touched very often (e.g sessions, authorization, stats), you cannot tweak its parameters, put it on an SSD, etc. You can use a separate database for that, though, if relational integrity is not crucial.

SQLite does not have replication or failover features. If your app's downtime is going to cost you money, you'd better have a hot backup database server, ready to take over if the main server goes down. With Postgres, this is relatively painless; with SQLite, hardly.

SQLite does not have an online backup and point-in-time recovery capability. If data you receive from users is going to cost you money (e.g. merchant orders or user data under a SLA), you better back up your data regularly, even continuously. Postgres, of course, can do this; SQLite cannot.

In short: when your site stops being a toy, you should have switched already. You should have switched some time before your first serious load spike, to iron out any obvious problems.

Fortunately, Django ORM makes switching very easy on the Python side: you mostly change the connection string in settings.py. On the actual database side you'll have to do a bit more: profile your most important queries, tweak certain column types and indexes, etc. Unless you know how to cook Postgres yourself, seek help of people who know; databases have many non-obvious subtleties that influence performance significantly. Deploying Postgres is definitely more tricky than SQLite (though not realy hard); the result is way more functional when it comes to operation / maintenance under load.