Agreed. And the database can grow surprisingly large (multi GB with many millions of rows) and it still performs quite well.

This surprises me. Though I've used it with great success for several small projects, my only experience with SQLite on anything remotely large was a Rails app with maybe a million records in the DB. Over time, performance became very slow and switching to MySQL made a world of difference.

At the time, I attributed it to SQLite, but now I'm wondering if it was Rails or (more likely) my inexperience at optimizing performance of the app at that time.

It was about three years ago, though, so I am sure performance in SQLite (and Rails, too, for that matter) has also likely improved a lot during that time.

In SQLite, autocommit is on, so if you weren't explicitly setting your transactions, you might have been commit after each update. Also, the standard disk cache is 2MB, so if you think your DB should have more memory than that, up it with default_cache_size.

Ah, I wasn't, so that combined with pilif's comments regarding the file being locked for writes would make a lot of sense as to why things were slowing down.

Thanks for the info.

Write operations to a SQLite file lock the whole file. It's possible that your rails app had enough users doing enough writes so that the processes had to constantly wait.

As long as you are the only user, performance should be constant regardless of file size (minus fragmentation issues)

It depends a lot on the complexity of queries.

Implementing a B-tree is not easy but no black magic either (I did it for my diploma theses). Same for a hash join. And these two things are really all you need to have reasonable performance for simple selects and joins on tables of almost any size.

But if your queries get more complex, the query execution plan starts to make a huge difference - and a query optimizer is black magic, as anyone who's wrestled with Oracle's can attest. I doubt SQLite can compete in that area.