5 ms·
Whenever SQLite comes up, always have to link to this post about using SQLite in production: https://blog.wesleyac.com/posts/consider-sqlite https://blog.wesley
by Glench 5y ago
Whenever SQLite comes up, always have to link to this post about using SQLite in production: https://blog.wesleyac.com/posts/consider-sqlite https://blog.wesleyac.com/posts/consider-sqlite :)
I'm running https://extensionpay.com https://extensionpay.com off SQLite and a $5/month DigitalOcean box and it's serving around 3 million requests a month with absolutely no issues and seriously low CPU load. I'm kind of astonished, frankly.
- nicoburns 5y ago> 3 million requests a month with absolutely no issues and seriously low CPU load That's just under 1 request per second, so I can't say I'm surprised. Even if traffic is spiky that would leave you with a lot of headroom!
- Glench 5y agoYeah, thanks for putting that in perspective! ExtensionPay is still relatively new, too, so I'm glad there's plenty of headroom :)
- ryanianian 5y agoA more useful metric for the parent comment to include would be the peak throughput in ops/time versus CPU or IO load.
- rexreed 5y agoDo you have any tips load-balancing or horizontal scaling of SQLite if you have to have a 2nd server for whatever reason?
- Glench 5y agoI don't. My current understanding is that even though there are sharding / replication projects out there for SQLite, it's usually a better idea to just go with a typical database server when you need many machines. But a lot of sites never get to that point, especially given how fast servers and SSDs are these days, so SQLite seems like it can get you further than you might realize.
- bob1029 5y ago> But a lot of sites never get to that point, especially given how fast servers and SSDs are these days, so SQLite seems like it can get you further than you might realize. SQLite on top of NVMe with WAL mode enabled is about as fast as it gets. We've been doing this in production for years now. We don't have a scenario where we would find ourselves writing more than 2 gigabytes per second to DB/disk, nor do we ever think we would encounter one with our product, so we have committed ourselves to single instance SQLite architecture. Saves a lot of time and frustration when you can accept your circumstances and go all-in on simpler solutions.
- hu3 5y agoI asked OP this and would appreciate your input as well. What tool do you use to access production database remotely? Say you need to browse some data, perform queries. Is there something like pgAdmin? I'm interested in SQLite and I'd like yo know more.
- Glench 5y agoI just scp the whole database to my local machine and use "db Browser for SQLite" (a mac app) :) Would love to hear if there are other server-based db browsers for SQLite, though.
- bob1029 5y agoWe do not permit direct access to any application databases across any network. If something in one of our SQLite databases needs to be adjusted in an operational context, it needs to be done by way of some administrative interface that we have integrated into the product. For offline troubleshooting/QA/Dev, we would simply grab a copy of the database from wherever and we would analyze it using DB Browser for SQLite. We actually use this tool so often that I felt it necessary to contribute to the author's Patreon account. It really is an amazing tool. Try reading/editing a binary field containing an image or other file in SSMS... Having extreme discipline with your schema, queries, indexes, etc. is critical for being able to operate like this. If you are uncertain of what should live in what table, you probably just want to work against a hosted database engine until the whole team can agree on one standard path that you can burn into your codebase. We did NOT start with SQLite for all the things. We had to make a lot of mistakes with hosted SQL providers before getting to this level of confidence.
- eatonphil 5y agoI just learned about ZeeSQL yesterday which is SQLite+Redis. It's proprietary but if I understand it correctly (the marketing is terribly unspecific about their primary goal) it let's you scale SQLite across all your Redis nodes. https://zeesql.com/ https://zeesql.com/
- otoolep 5y agohttps://github.com/rqlite/rqlite https://github.com/rqlite/rqlite provides a form of horizontal scaling for reads, but it's not a drop in replacement for SQLite. https://github.com/rqlite/rqlite/blob/master/DOC/READ_ONLY_NODES.md https://github.com/rqlite/rqlite/blob/master/DOC/READ_ONLY_N... (I'm the author of rqlite)
- eatonphil 5y ago"always have to link" to a post that was written less than a month ago?
- Glench 5y agoYes indeed! Even though it's only been a month I keep posting about it in HN threads about SQLite and people keep going "wow I never thought about that!"
- hu3 5y agoHi. What tool do you use to access production database remotely? Say you need to browse some data, perform queries. Is there something like pgAdmin? I'm interested in SQLite and I'd like yo know more.
- tmp_anon_22 5y agoIts SQLite so you would just copy the file and download it to a local machine for analysis through any number of tools. Presumably your setup would already be doing this as a form of backing up the data periodically.
- Glench 5y agoCopying and pasting my answer from another subthread: I just scp the whole database to my local machine and use "db Browser for SQLite" (a mac app) :) Would love to hear if there are other server-based db browsers for SQLite, though.
- jprd 5y agoCloudBeaver works great for me, though I'm not doing anything intense. https://github.com/dbeaver/cloudbeaver/ https://github.com/dbeaver/cloudbeaver/
- eatonphil 5y agoscp is the way I implemented querying a remote SQLite database under the hood in my data ide. Now that I think of it though I should probably add some clarification to docs or the UI that you're querying a local copy of the database and that your edits won't affect the original. https://github.com/multiprocessio/datastation https://github.com/multiprocessio/datastation
- irq-1 5y agoYou can use the command line for sqlite on the server. There are lots of GUI tools if you copy the file local. Here's one I use https://sqlitestudio.pl https://sqlitestudio.pl
- justinclift 5y ago
- _wldu 5y agoIf you setup Sqlite properly (Remote Clients <-> RESTful API <-> DB) and use WAL mode, you can run a DB backed website fairly cheap. It will be performant, reliable and simple to maintain as well.
- Zababa 5y agoI'll be honest, I would consider running SQLite in production, at least for my personal stuff, but the part that scares me is that there's no "managed SQLite" offering and I would have to manage it myself. Litestream seems like a great option for people like me, but I'd like to hear some experience report on it, especially on more complex case, and how it fails. Another problem (which Litestream might solve) is that SQLite is not compatible with Heroku, that I use since I don't have much ops/sysadmin experience. I feel like people saying that SQLite makes things easier often have already some experience in sysadmin/ops stuff, which isn't my case. This is not a criticism of SQLite in any way, just an expression of my own limitations and why it makes it harder for me to consider SQLite. I'll gladly take any learning resources about that.
- benbjohnson 5y agoLitestream author here. I'm working on a serverless option that'll run as a managed service later this year. I think there's a huge opportunity to get a simple option for folks running on ephemeral platforms like Heroku, Google Cloud Run, & Cloudflare Workers.
- Glench 5y agoThe conservative approach — I like it! Yeah it's not for everyone but it's a real delight if your needs align with what SQLite offers. > there's no "managed SQLite" offering It's just a file :) You can copy it to a remote server or use Litestream or whatever you want to do with a file. > SQLite is not compatible with Heroku Yeah, that's pretty annoying. Apparently fly.io is similar to Heroku but lets you use persistent disks — I've never used it though. I'd love to see more "managed app server + sqlite" options, since a lot of the times the "managed database server" option from cloud hosts is kind of expensive. Send emails to Heroku and DigitalOcean and Google Computer platform. Tell the world!
- achillean 5y agoWe've been using it for https://internetdb.shodan.io https://internetdb.shodan.io and it's been doing a few thousand lookups/ second without problems.
- buryat 5y agohow do you get replication?
- Glench 5y agoI don’t. I have the one server with streaming dB backups. Although apparently litestream might include a way to do live SQLite replication soon.
- logankeenan 5y agoHow do you manage backups of your database and how often?
- Glench 5y agoI do streaming updates with Litestream: https://litestream.io/ https://litestream.io/ I recently set it up after putting it off for a while and it took like 5 minutes. It was great. And the backups are currently costing me 0 cents per month on backblaze.
- deleted 5y ago[deleted]
- bborud 5y agoWhen I develop server software I always include support for SQLite and PostgreSQL so I can run the server entirely stand-alone if I want to. Choosing whether to use the embedded database or connecting to PostgreSQL is a command line switch. I have been using SQLite in an IoT application where the measurement of a long-running test instance has 65 million rows in the main data table and is 3.6Gb in size....and the response time running queries against the database isn't bad. I'm starting to question of I actually need PostgreSQL for a lot of situations.
- nerdyadventurer 5y agoHow do you do encryption, authentication and authorization?
- Glench 5y agoI'm not sure what that has to do with my choice of database. I just do it the way every web app does those things.
- gen220 5y agoIf you want an even-earlier-published article to point to as well, this has been mine: https://crawshaw.io/blog/one-process-programming-notes https://crawshaw.io/blog/one-process-programming-notes There's also the venerable "Taco Bell Programming": http://widgetsandshit.com/teddziuba/2010/10/taco-bell-programming.html http://widgetsandshit.com/teddziuba/2010/10/taco-bell-progra.... It all boils down to unix principles, I guess: use the most simple (but not simplistic) tools for the job.