HACKER Q&A
📣 iio7

Deploying SQLite on a Production Website


I am considering deploying SQLite on a production website. I know for a fact that the application will never need a client/server architecture and that the data only need to reside on the same machine as the web application because the database is really small in size. It may reach levels of 500K reads a day and equally many writes. However, the writes consists of nothing more than "ratings", i.e. storing a single integer.

I have no experience using SQLite in production, but really love the simplicity. I have read a bit about write locking being a problem.

Do you have any experience to share about deploying SQLite in production? Is multiple writings coming from the same web application an issue?

  👤 justsomeuser Accepted Answer ✓
When SQLite is in WAL mode, writes queue up, but reads are concurrent.

Writes do not actually "queue" in one place, rather each reader will have a busy_timeout, which is basically polling the file to see if it is write-lock-free. If you have a single application, you might want to queue the writes yourself inside the application to prevent polling the file.

Writes typically happen very fast, so as long as your application is not using more than 100% of possible write capacity you would be fine, in theory. WAL mode is typically fast for writes because the OS caches many transactions, and only does an expensive "checkpoint"/fsync once every X transactions.

Try it out, move to another db if it does not work would be my advice.

👤 sealeck
You say "I know for a fact that the application will never need a client/server architecture" but also talk about a "Production Website" - surely a website will by definition need a client-server architecture?

SQLite will probably suffice, so long as you aren't running very complex queries. SQlite has some documentation on its concurrency model - https://www.sqlite.org/lockingv3.html. You might also want to look at https://www.sqlite.org/wal.html.

👤 enduku
You may want to look at SQLite + Litestream [0]

[0] litestream.io