[127001brewer]

SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database.

I am curious to know how many people here solely use SQLite to power the back-end of their web application(s), especially when the page states, "SQLite does not compete with client/server databases."

(Or is the page referring to content-management-system-type websites?)

[Spidler]

SQLite scales very well for reading data, actually. If your site is single-write:many-read (most are) it works brilliantly.

If you however cause writes when people open pages, look at content. Anything from hit counters to tracking behaviour. Then SQLite suddenly starts to scale pretty badly.

The same goes with how your processing works. Multiple processes that may all at some point cause writes? This is bad. Threads inside a process? That works.

[techtalsky]

The main limitation is number of clients here. If you have a RESTful API, some ETL loaders, and several webservers running on EC2 all talking to your database, then you need a real client-server architecture.

However, if you're running your website on Apache, on a single webserver, then there's really only ONE client for your database, in which case SQLite works great, even if there's a heck of a lot of load.

SQLite is fundamentally a C library talking to a binary file format, so it's orders of magnitude faster than making a network connection to a client and then issuing SQL.

I've run medium-sized websites on an MVC framework talking to SQLite MANY times, and it works great.

[marcosdumay]

On work, my area has a conflicting relationship with IT, thus we are often required to use unusual setups...

Well, we've tried distributing applications that needed to share a DB backend. We tried a MS Access backend first, but it stopped working after about 5 people were using it. Then we migrated to SQLite, it handled well up to near 50 people, then its over-restrictive locking become a problem. Luckly by that time we got hold of an Postgres server.

Low to medium traffic websites don't usualy have much more than 10 working threads, so yes, I've used a lousely similar setup, and it worked. It'll depend on how much time is spent on DB access vs. local processing, and how fast the DB is accessed by those servers, so YMMV.

[tracker1]

It's worth noting that you can split your data among multiple database files (effectively on-disk sharding) to alleviate contention... in-memory record caching and mostly read scenarios will also reduce contention.

There's a LOT you can do with SQLite... not to mention that with simple read caching and reduced writes you can get a lot of performance out of SQLite. Highly interactive web applications, I wouldn't expect to handle more than 50 users on a single system backed by SQLite, as you mention... with SSD, you may get a couple more.

[marcosdumay]

Yes, we start splitting the data at one point. But don't take those 50 users limit too seriously, it was more a consequence of bad networking infrastructure than of SQLite.

I have no idea how far SQLite would go on a nice network with a sane file sharing protocol.

[tracker1]

If you aren't having to do many writes, it will fly for thousands of users... when you have to do a lot of writes, then it will slow to a crawl. I've seen distributed Access based database apps that handled several hundred simultaneous users before.

[mayoff]

SQLite's WAL support might alleviate your locking problems.

[oliwarner]

Yeah, I use it for fair-traffic CMS sites and even webapps. The trick is exactly the same as any other RDBMS: cache everything as much as possible.

Django makes this really easy (it's the default). It's a shame other projects aren't on flexible ORMs. I'd love to be able to deploy WordPress and Drupal sites without dicking around creating databases.

[hobs]

I think it is more about multi-client databases because you only have one writer at a time (so not multiple clients updating data).