SQLite vs. Postgres, at a glance
There’s a general belief that SQLite is a “slow” database and Postgres is “fast,” and many software packages (including FOSS) insist that SQLite is only suitable for testing and doesn’t scale. However, this doesn’t make much sense when you think about it; SQLite is an in-process database so there’s no communications overhead between the service and the database, and because it’s only designed to be accessed from a single process it can make use of optimistic locking to speed up transactions.
Since I was installing postgres for another purpose on my webserver, I decided to quickly see if Publ performs better on Postgres vs SQLite. To test the performance I compared the timing for my website on both doing a full site reindex, and rendering the Atom feed several times (using the debug Flask server and caching disabled).