I've been playing with SQLite for the last couple of days. I'm trying to figure out if it's a suitable replacement for the ad-hoc storage format I've got for Raggle. SQLite has a lot of benefits: it's fast, small (the , and free (public domain). It supports sub-selects, atomic transactions, and there's a complete set of Ruby bindings (which are available as a Gem, but not as a Debian package... go figure). Of course all this goodness doesn't come without caveats. Specifically, here's the one that might be a problem for me:
Locking in SQLite is very course-grained. SQLite locks the entire database. Big database servers (PostgreSQL, Oracle, etc.) generally have finer grained locking, such as locking on a single table or a single row within a table. If you have a massively parallel database application, you should consider using a big database server instead of SQLite.
Source: SQLite FAQ
Raggle isn't a "massively parallel database application", but it can have up to N threads (where N is the number of feeds a user is subscribed to) attempting to write to the feed list simultaneously. I can probably queue database inserts and limit the threads to
SELECTing from their respective tables, but that smacks of hackery, which is what I was trying to avoid in the first place. I guess it's still a better solution than what Raggle does right now. Ah well, C'est la vie.