Appropriate Uses for SQLite

84 points by vince_refiti 11 years ago | 36 comments

pstadler 11 years ago |

  "The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course,
  and as of this writing (2015) it handles about 400K to 500K HTTP requests
  per day, about 15-20% of which are dynamic pages touching the database.
  Each dynamic page does roughly 200 SQL statements. [...]"

Wait... What? They're saying that their dynamic pages are executing 200 SQL statements per request?

Off-topic: I enjoyed reading this article which was posted here a while ago http://www.sqlite.org/testing.html

mutagen 11 years ago | |

200+ queries for a simple dynamic page doesn't surprise me. Once you add some dynamic features it's easy to start sticking all kinds of stuff in the database. Let me check some sites I run:

  Medium complexity Drupal 6 site: 200-300 queries
  Wordpress site with accretion of plugins over the years: 100+ queries
  Drupal 7 site: 100+ queries

Those totals might be inflated due to having administrative menus active.

Many of these are key/value lookups which can be accelerated by serving from Memcached or Redis, although I imagine SQLite dishes them out pretty quickly as well.

127001brewer 11 years ago |

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 11 years ago | |

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 11 years ago | |

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 11 years ago | |

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 11 years ago | | |

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.

mayoff 11 years ago | | |

SQLite's WAL support might alleviate your locking problems.

oliwarner 11 years ago | |

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 11 years ago | |

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

heyalexej 11 years ago |

I was fooled by the name for a long time. "SQLite? That's what you use to store 160 phone book entries or something." How wrong I was.

derekp7 11 years ago | |

I'm using it for the data catalog for one of my projects (a Linux backup tool) which I've grown to something like 20GB, and storing the metadata catalog for 40 systems and about 30 backup sets for each one.

So far the main issues I've been having is I have a lot of complex joins, and it doesn't seem to want to use appropriate indexes all the time. If I'm joining 2 tables on an indexed field, it is ok, but as soon as I add a 3rd or 4th table the performance falls apart. So my current workaround is to join 2 tables into a temporary table, add an index, then join that temp table to a 3rd one.

elcct 11 years ago | | |

So basically RDBMS is turning you into RDBMS

SchizoDuckie 11 years ago | |

I'm using WebSQL / Sqlite to store TV show data on the user's system. works like a charm, databases regularly grow to 10+mb, never had a problem with crashes, speed or anything like it. Plus, everything runs locally <3

Now I really hope that Spartan can implement WebSQL as well...

bengali3 11 years ago | | |

I look forward to using SQLite again. In 2010ish I used it for an airport and aircraft database to be installed with our iOS app for 'find near you' feature. lookup performance was perfect. but did have some performance snags on application start, a server call retrieved any DB records needing to be updated. I found SQLite update statements were slow (10mb DB of 10,000 records, less than 100 updates took 30s on first gen iphones), so instead of retrieving the records that were changed, we modified it to retrieve the latest 3mb (compressed) of the 10mb DB from the server, decompress and replace the single file which turned out to be simpler, more atomic and required much less code.

I really enjoyed the simplicity of it all.

rockdoe 11 years ago | | |

Now I really hope that Spartan can implement WebSQL as well...

Sounds unlikely given that the standard was superseded by IndexedDB.

http://www.html5rocks.com/en/tutorials/webdatabase/websql-in...

Most browsers just use SQLite to back IndexedDB, though.

AdrianRossouw 11 years ago |

There's also mbtiles, which is a standard way of storing map tiles in an sqlite database in blob fields.

https://www.mapbox.com/guides/an-open-platform/#storing-tile...

you can have sqlite databases that are gigs in size and perform great.

baconner 11 years ago |

Can anybody speak to their experience using sqlite for data analysis purposes?

Am I wrong in thinking it's just plain old row oriented storage and not something more aggregate oriented?

k2enemy 11 years ago | |

I use sqlite to store all of my small datasets. Minimally, I think of it as a replacement for zipped CSV files. But it also has the added benefit of a relational structure and SQL.

It is super easy to access from julia, R, python, etc, so instead of importing a CSV and manipulating the data, I find it a lot easier to connect to the sqlite database and use SQL for the a lot of the joining and manipulating.

baconner 11 years ago | | |

I see. So the benefit is mainly in having more relational structure than in actually crunching numbers. Makes sense.

mbrzusto 11 years ago | |

I use it quite a bit for data analysis, in particular for user-defined functions with python. Being able to explore your data (via SQL and it's powerful syntax) in addition to functions and aggregators that I define, is REALLY useful. You could do the whole thing in python (and data imports), but adding the SQL part in is so much easier than building dictionaries and filtering, sorting etc...

zachrose 11 years ago |

Neat. I've also used an in-memory SQLite db for testing ActiveRecord models.

Also, the same list if uses can be applied to nedb as a lightweight alternative to MongoDB.

mike_r_parsons 11 years ago |

Don't get mislead by the "lite" in sqlite. I can do about 200k sql requests per second with a sqlite inmemory db and node.js

tootie 11 years ago |

How does SQLite handle replication? Can I have 10 app server nodes reading from one SQLite DB? NFS?

nmrm 11 years ago | |

From the article: Situations Where A Client/Server RDBMS May Work Better... High Concurrency: SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time.

I've used SQLite over NFS for replication. It worked fine.

ajross 11 years ago | |

The database is just a file, so just copy it (rsync works great for compressing this process over a slower network). If you need to replicate without blocking writes, you can do that with some scripting around filesystem (e.g. btrfs) or block layer (LVM) snapshots.

Though again, if you want true streamed (i.e. per query) replication, features like hot standby, etc... that's not part of the model and you're better served by a server-model RDBMS.

malkia 11 years ago | |

SQLite has a backup api, so not entirely what you might need - but you can get a consistent snapshot without affecting much the db using the backup API:

https://www.sqlite.org/backup.html

The way I've used it (for a db of 300-400mb loaded in-memory in a 64-bit app) was to load the .db in memory, then detect if there are changes outside (notified, or updated the .db file timestamp from a different app using the db), then load a second copy of the .db and compare, then build diffs and report them in some kind of API fashion.

It was used for live update of various level editing data for a game level editor. Possibly not the best fit (too much hierarchical data), but worked nonetheless.

One thing that I miss dearly from SQLite is some form of postgres arrays, but I'm so glad that recursive with has been added recently to SQLite which allows for hierarchical data (child/parent storing ids to each other) to fetch without some extra information.

lukaslalinsky 11 years ago | |

I'm not sure if SQLite writes over NFS work, I wouldn't trust it. Previously I have done SQLite replication by simply rsyncing the database to application servers at certain times.

vamin 11 years ago | | |

Writes definitely work, but concurrent writes will hose the database. SQLite relies on filesystem locking (which NFS does not handle properly) to handle write concurrency.

micro-ram 11 years ago |

sqlite.org SSL Cert just expired

"The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Each dynamic page does roughly 200 SQL statements. [...]"