6 ms·
> sqLite - This is described as being unsuitable for > production by it's makers. I would doubt that is what the creators of SQlite intend one to take away fr
by sdoering 6y ago
> sqLite - This is described as being unsuitable for
> production by it's makers.
I would doubt that is what the creators of SQlite intend one to take away from their documentation:
"SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.
> The SQLite website (https://www.sqlite.org/ https://www.sqlite.org/) uses SQLite
> itself, of course, and as of this writing (2015) it handles
> about 400K to 500K HTTP requests per day, about 15-20% of
> which are dynamic pages touching the database. Dynamic
> content uses about 200 SQL statements per webpage. This
> setup runs on a single VM that shares a physical server
> with 23 others and yet still keeps the load average below
> 0.1 most of the time." (Source: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html)
So SQlite is quite a valid choice if you need a database and stay below ~500k HTTP requests per day. So it probably is "production ready" for many a site.
- Pete-Codes 6y agohey, I posted the article. Good to know! I'm sure I had read it somewhere on their site. Maybe it was just from other developers that I got the impression it wasn't meant from production. Thanks
- DZittersteyn 6y agoI also used to have this impression, I think I got this from Rails and Django docs, maybe you read something similar? > Rails comes with built-in support for SQLite, which is a lightweight serverless database application. While a busy production environment may overload SQLite, it works well for development and testing. Rails defaults to using a SQLite database when creating a new project, but you can always change it later. > By default, the configuration uses SQLite. If you’re new to databases, or you’re just interested in trying Django, this is the easiest choice. SQLite is included in Python, so you won’t need to install anything else to support your database. When starting your first real project, however, you may want to use a more scalable database like PostgreSQL, to avoid database-switching headaches down the road.
- jamie_ca 6y agoThe Django docs word it better - the chief problem with SQLite is that it's a _local_ database. If you outgrow a single server, you will now need to also migrate your database to something that can handle multiple external clients, or else figure out another way of making writes on one server be available to read on the other servers.
- mytherin 6y agoSQLite runs in airplanes [1]. I would say that is all that needs to be said on whether or not SQLite is production-ready :) In fact, I would argue SQLite is the most well-tested and production ready database system out there. [1] https://www.sqlite.org/famous.html https://www.sqlite.org/famous.html
- alfonsodev 6y agoI've found that Apple uses sqlite too, for Safari history and if I recall correctly macOS Notes app too.
- jannes 6y agoApple built Core Data on top of sqlite. There are probably a lot more Apple apps using it than just Safari and Notes. https://developer.apple.com/documentation/coredata/ https://developer.apple.com/documentation/coredata/ https://en.wikipedia.org/wiki/Core_Data https://en.wikipedia.org/wiki/Core_Data
- alfonsodev 6y agoahh that explains it, very good to know, thank you.
- nikon 6y agoiMessage too
- vsareto 6y ago> Flame is a malware spy program that is reported to make heavy use of SQLite. Maybe we need an /infamous.html page
- RHSeeger 6y agoSQLite is staggeringly productions ready. I think the trick is to think of it less like a traditional database and more like a file format with an SQL front end. From the website [1] > SQLite is used by literally millions of applications with literally billions and billions of deployments. It's also worth noting (at least, imo) that it's automated test suite is fantastic. I seems to recall it has considerably more test code than actual library code. [1] https://www.sqlite.org/famous.html https://www.sqlite.org/famous.html
- Cthulhu_ 6y agoAs long as SQLite doesn't catastrophically break under load it's fine for production, but only as long as it has a single client; traditional database engines can deal with multiple applications connecting to them. I'm using sqlite for a production application as well, but its load will be in the range of - at best - thousands per day. The only complexity will be that there has to be a secondary 'fallback' server; the current application just copies the whole .db file over to the secondary server after certain events.
- bravura 6y agoIf I have a simple Flask or Django app, backed by sqlite, how do I make sure I only have one client? If I use a prerolled Apache or Nginx frontend (which may or may not be set up with multiple threads or whatever), and have very very low traffic, how do I make sure I'm not corrupting it with multiple writes?
- robjan 6y agoSQLLite will lock the db while you are writing to it to prevent this happening
- lukeschlather 6y agoIt's probably fine to leave Apache or Nginx with multiple threads (probably even a good idea) but you need to make sure that uwsgi or whatever Python server you're using on the backend only has one process. If you can somehow architect things so a single process is doing writes (but leave multiple processes doing reads) that's likely even better. However I would suggest doing a lot of testing. It's possible multiple write processes isn't the end of the world below some threshold.
- itroot 6y agoYou can have several clients. If you do not have long-live write transactions, performance will be fine.
- franciscop 6y agoThousands per day, let's be generous and say 9,000 queries per day. In 24h and 3,600s/h that is ~0.1 req/s. Are you saying that SQLite cannot deal with 1 request every ~10 seconds? Or were you referring to 900,000 queries when you said "thousands"? FWIW the typican front-page article in HN sees around 10-30k visits in a single day.
- weddpros 6y agoHN: "A single server with SQLite is perfect for typical production workloads, it's so robust" Also HN: "Database X can fail in Jepsen tests during partitions, it's unusable" I see HA as a mandatory part of what makes a system production ready...
- iMerNibor 6y agoFor many websites it's perfectly acceptable to go down for a few hours (or however long it takes you to notice, spin up a new vm/server and restore a backup) should stuff go wrong, which is fairly unlikely if you're just running a few servers. It's likely the additional overhead worrying about HA is not worth it, not to mention the real possibility of HA just not working properly in actual failure scenarios
- weddpros 6y agoI agree not every website needs high availability... but I wouldn't bet my $600k/yr business on a single VM's availability and reliability. One day, his server is going to die and be replaced by his host, or his disks will need replacing or a RAID array will fry... It seems like his business could at least afford an HA setup on a cloud provider. Moving to any hosted db with backups, updates and redundancy could be worth it. As for HA failing, it's still less likely than not-HA failing. Also an HA setup allows for maintenance and upgrades without downtime, which is way better than the very common "we don't upgrade if it's working, because it could break".
- WJW 6y agoRunning on a single server does not mean no backups and uptime monitoring is in place. If the hardware fails, you get a ping on the channel of your choice, manually provision a new VM then download the latest sqlite backup from your backup provider. Easy to make a checklist or a script for this, too. Also there is a third option between HA (with its increased cost and complexity) and "we don't upgrade if it's working, because it could break", which is "take the site down for a few minutes, do the upgrade, bring it back up". It's not for every site, but there's a range of sites for which that is fine.
- nickjj 6y agoYep. I recently had Jon Calhoun on as a guest in my Running in Production podcast the other week where he talked about using SQLite to power his video course platform (it's focused on Go development). He has 15,000+ active users and talks about how SQLlite hasn't been a performance issue. His whole stack is on a $5 / month DO server. Here's a direct link to the SQLite part: https://runninginproduction.com/podcast/42-creating-a-video-course-hosting-platform-to-learn-go#37:53 https://runninginproduction.com/podcast/42-creating-a-video-... I also have another episode coming out next week where SQLite is being used in a custom hardware appliance running on a Raspberry Pi-like device that is shipped to villages in the African Congo to give villagers affordable access to email. I wonder if the creator of SQLite had that use case in mind!
- mcintyre1994 6y agoThanks for sharing this, your podcast sounds really interesting - subscribed :)
- aphistic 6y agoSQLite is incredibly ubiquitous and used in BILLIONS of devices and applications around the world. Just because it's not used in web applications as much doesn't mean it's not production ready. It's used in many hardened systems, which I think they'd avoid if it wasn't production ready.