sqlite as a production database for a low-traffic site?

Question

I'm considering using sqlite as a production database for a site that would receive perhaps 20 simultaneous users but with the potential for a peak that could be many multiples of that (since the site would be accessible on the open internet and there's always a possibility that someone will post a link somewhere that could drive many people to the site all at once).

Is sqlite a possibility?

I know it's not an ideal production scenario.

I'm only asking if this is within the realm of being a realistic possibility.

Answer 1

I think it would depend mostly on what your read/write ratio will be. If it's mostly reading from the database, you may be okay. Multi-user writing in SQLite can be a problem because of how it locks the database.

Answer 2

SQLite doesn't support any kind of concurrency, so you may have problems running it on a production website. If you're looking for a 'lighter' database, perhaps consider trying a contemporary object-document store like CouchDB.

By all means continue to develop against SQLlite, and you're probably fine to use it initially. If you find your application has more users down the track, you're going to want to transition to postgres or mysql however.

The author of SQLlite addresses this on the website:

SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.

SQLite will normally work fine as the database backend to a website. But if you website is so busy that you are thinking of splitting the database component off onto a separate machine, then you should definitely consider using an enterprise-class client/server database engine instead of SQLite.

So I think the long and short of it is, go for it, and if it's not working well for you, making the transition to an enterprise class database is fairly trivial anyway. Do take care over your scheme however, and design your database with growth and efficiency in mind.

---

Here's a thread with some more independent comments around using SQLite for a production web application. It sounds like it has been used with some mixed results.

From personal experience, I remember back in the dark days of web development using MS Access and Filemaker Pro that file locking was a persistent and painful problem. SQLlite may handle this more elegantly, but essentially it's the same problem, and I doubt much would have changed.

Answer 3

Depends on the usage of the site. If most of the time you're just reading data, you can pretty much use anything for a DB and cache the data in the application to achieve good performance.

Answer 4

I'd avoid making overly conservative assumptions about any website's traffic in general and I would definitely avoid SQLite for a production database on a web server where you never know what kind of traffic you may have.

If you're interested in very low footprint, totally self-managed database systems, I can recommend Sybase SQL Anywhere. There's a free web edition.

Answer 5

We often use SQLite for internal databases; The employee directory, our calendar of events, and other intranet services all run on lightweight databases. It would be major overkill to be running these apps at the scale we do on a "real" database like mySQL. This is especially true when you factor in that they're running along side 4 other virtual machines on a single mid-range computer.

At one point we had an outward facing site that ran on an sqlite db for months with only a single reboot required. Obviously, it was very low traffic, but it putted along nicely for what it did.

Answer 6

If it was me - I'd just use MySQL. We can be too precious about these things. I've had MySQL running on a NetGear ReadyNAS which has very limited memory (256 MB RAM I think) and a 32 MHz (you read that right) processor. It's fine. I've had it running on a low end Windows PC with about a 200 MHz processor and little RAM. Both cases serving web pages. Admittedly not with 20 simultaneous users.

But, in principle, if your hardware is reasonable and the database content is not ridiculously large, something like MySQL is free, easy to use and setup, fast, seems to scale well. I'd just go for that and devote my precious time to something else.

Answer 7

I am using it in a very low traffic web server (it is a genomic database) and I don't have any problems. But there are only SELECT statements, no writing to the DB involved.

Answer 8

People speack about concurrency problems, but sqlite has a way to cache the incoming requests and have them wait for some time. It doesn't timeout immediately?

I've read things about the default timeout setting begin zero, meaning it times out immediately and that's a nonsense. Maybe people didn't adjust this setting?