Hacker News Reader

Comment by Glench | Hacker News Reader

Whenever SQLite comes up, always have to link to this post about using SQLite in production: https://blog.wesleyac.com/posts/consider-sqlite :)

I'm running 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.

View on HN

> 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!

Yeah, thanks for putting that in perspective! ExtensionPay is still relatively new, too, so I'm glad there's plenty of headroom :)

A more useful metric for the parent comment to include would be the peak throughput in ops/time versus CPU or IO load.

When 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.

We've been using it for https://internetdb.shodan.io and it's been doing a few thousand lookups/ second without problems.

Do you have any tips load-balancing or horizontal scaling of SQLite if you have to have a 2nd server for whatever reason?

I 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.

> 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.

I 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.

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.

We 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.

> We actually use this tool so often that I felt it necessary to contribute to the author's Patreon account.

Thanks heaps for that btw!

Because of our Patrons, we've recently been able to buy an M1 Mac Mini. We should be able to start releasing ARM based macOS builds in the near-ish future. :)

My application Datasette can serve this need: https://datasette.io/

Lots of live demos - two of my favourites are https://github-to-sqlite.dogsheep.net/ and https://global-power-plants.datasettes.com/

Cool, what's your setup / workload look like?

I 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://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_N...

(I'm the author of rqlite)

I'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.

The 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!

I see how SQLite being just a file makes it easy to do lots of things from a developer point of view. However, I'm looking at this from a "business owner" point of view, at which point it's not just a file, it's my clients data. That's a bit scary for me, so I have a bias for "reassuring stuff" like managed databases with automated backups. This is why I'm looking for more info on "the hard parts" of managing SQLite. I'm conscious that this bias might not be based on reality, especially since I don't know well MySQL or PostgreSQL, especially how they store data on files. But as a relatively young developer, I've heard a lot that "data should go in the database" and databases are big scary things that should be managed by people with lots of experience and knowlegde in that role.

I'll take a look at fly.io, thanks.

Just to add to this: offering managed SQLite services isn't really all that much of a thing because essential, as it is just a file, there isn't much to manage.

With databases like postgres you have daemons, config files and more that need an experienced DB-Admin to configure and run. All of that complexity grows exponentially when you include scaling on multiple machines.

With SQLite you essentially need backups. That's about it. A managed SQLite service would essentially just be something along the lines of:

"Tell us which file is your database, we backup it and if you need it restored press this button."

That's a good point. I think this is my lack of experience showing here, mixed with lots of marketing aimed at people like me. After all, you can sell a managed Postgres for a lot more than you can sell a managed SQLite.

Well maybe to you "managed database server" feels more reassuring which is fine, but to me I run my business off SQLite which I've found insanely simple to set up and maintain, so that is more reassuring to me.

And just an aside, I think if you looked into it a bit you'd probably find that while there is a lot of nuance to database fine tuning, you can get most of that without needing to be an expert.

Litestream 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.

That's nice to know, I'll keep an eye out for when it comes out.

Ooh hi Ben, thanks so much for Litestream! Can you say more about the service you're describing and how it will work?

If you want an even-earlier-published article to point to as well, this has been mine: https://crawshaw.io/blog/one-process-programming-notes

There's also the venerable "Taco Bell Programming": 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.

"always have to link" to a post that was written less than a month ago?

Yes 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!"

If 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.

Hi. 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.

Its 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.

Copying 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.

scp 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

CloudBeaver works great for me, though I'm not doing anything intense.

https://github.com/dbeaver/cloudbeaver/

You 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

> Is there something like pgAdmin?

A fairly well used GUI is https://sqlitebrowser.org (a project I help out personally).

You can do that with adminer https://www.adminer.org/

how do you get replication?

I don’t. I have the one server with streaming dB backups. Although apparently litestream might include a way to do live SQLite replication soon.

How do you do encryption, authentication and authorization?

I'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.

How do you manage backups of your database and how often?

I do streaming updates with Litestream: 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.

Reply on news.ycombinator.com