5 ms·
Ask HN: 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 6y agoWhen 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.
- iio7 6y agoThanks! The busy_timeout was the heart of the matter :)
- sealeck 6y agoYou 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 https://www.sqlite.org/lockingv3.html. You might also want to look at https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html.
- iio7 6y agoNo. A website running on a single machine is not a client server architecture in the network sense. Also, SQLite has no problems running complex queries.
- deleted 6y ago[deleted]
- enduku 5y agoYou may want to look at SQLite + Litestream [0] [0] litestream.io
- benbjohnson 5y agoLitestream author here. We have a friendly Slack if you need help getting started: https://join.slack.com/t/litestream/shared_invite/zt-n0j4s3ci-lx1JziR3bV6L2NMF723H3Q https://join.slack.com/t/litestream/shared_invite/zt-n0j4s3c...