Ask HN: Have you used SQLite as a primary database? • Hacker News Board

Re: Ask HN: Have you used SQLite as a primary database?

#251

Post by srcreigh » Mon, Apr 25, 2022, 6:41 PM UTC

jjoonathan wrote: post #186

srcreigh wrote: post #169

Earlier quoted context omitted.

More details please if you have them. What was the throughput? number of transactions? Data size? If you have that thread would be great to see it as well.

It was chugging at ~100 inserts per second on about ~300k rows of The problem itself didn't concern me nearly as much as "no perf tools + no perf foolproofing." That's a rough combo. If a problem this simple required this much debugging, extrapolations to problems of any complexity are terrifying. I knew that simplicity implied limitations, but this lesson taught me that simplicity could also imply danger.

This is a common problem. Bulk inserts should be done within a single txn if possible. The limit isn't insert throughput, but transaction throughput.

I've commented elsewhere here for docs referencing this problem. It's FAQ#19 on the SQLite website.

Was your inserts based on HTTP requests or was it more of a batch process? we're they grouped in txns? Obviously user http requests would be harder to group up, but kudos if your service is handling 100 QPS writes as that's pretty rare level of scale approaching the real "need a beefy concurrent db server" type of problem statement.

Re: Ask HN: Have you used SQLite as a primary database?

#252

Post by TheRealPomax » Mon, Apr 25, 2022, 6:46 PM UTC

deckard1 wrote: post #240

TheRealPomax wrote: post #182

Earlier quoted context omitted.

Please do explain what risk you're thinking of, as anyone smart enough to write their own SaaS would not put resources in the web server's file system tree? You stick your db file in an normal secure location outside the server's root, chmodded appropriately so that it suffers the exact same risks as any other file on the OS. It's no more or less risky than /etc/shadow, while being considerably easier to work with (a…

your app has full access to the SQLite file. MySQL/PostgreSQL have users and permissions. Security is about layers, and SQLite is removing one layer of security. You can, for example, put DELETEs or access to certain tables on a separate user that your web app has no access to. With SQLite, if your app gets hacked then they can do anything with the whole DB they want to. In addition, with a separate DB process you ge…

If your server or API can be exploited, it doesn't matter whether there's an auth layer in between. Your SQL server runs as a service to connect to, your sqlite3 file is a file that you need access to. They're the same kind of layer: you need to break through the server's security to ever get to them directly, and if your app gets hacked such that the hackers gain file system access, then:

1. You're fucked. The end. It doesn't matter whether you were using mysql, postgres, or sqlite3, or S3, or Redis, or any other server your app was connecting to: they can just look at your environment vars.

That's not going to happen "because you're using Sqlite3", that's going to happen because you used some obscure server software, or worse, rolled your own.

People really do seem to put too much faith into "it has a username and password, it's more secure". It's not: if someone has access to your actual server, they have access to everything your server has access to. Sqlite3 is no more or less secure than a dbms daemon (or remote) in that sense.

Re: Ask HN: Have you used SQLite as a primary database?

#253

Post by stormbrew » Mon, Apr 25, 2022, 6:47 PM UTC

I wish more "self-hosted" open source projects would support sqlite out of the box. It's honestly a little ridiculous to, for example, stand up postgres for a 1 person blog on a personal domain. Or even a 10 person mastadon or pleroma instance or whatever.

That said, sqlite used 'badly' can be quite frustrating. Home Assistant, for example, is usually set up on an sd card in a raspi and then runs an sqlite database on it that it dumps massive amounts of very redundant data into as json blobs. Pretty common to have it just randomly lock up because the sd card has trouble with that frequency of writes.

Re: Ask HN: Have you used SQLite as a primary database?

#254

Post by srcreigh » Mon, Apr 25, 2022, 6:48 PM UTC

iampims wrote: post #184

Glench wrote: post #127

Earlier quoted context omitted.

> What is the point of using SQLite under a web service? Take a look at the Consider SQLite post I linked. They address your performance questions too. For me, SQLite was a nice way to simplify launching and running my SaaS business and has had no downsides.

Any tips you can share on how to deploy your saas app without downtime when using sqlite?

For my personal project I'm planning to use a VM with an SSD. Manually I'll use caddy to switch over to a new running backend service with readiness check.

As for scaling if I need it I can increase disk space for the app server or scale out horizontally/vertically. Don't need that yet so I'm waiting for more details in the future to decide how to handle that.

Re: Ask HN: Have you used SQLite as a primary database?

#255

Post by munro » Mon, Apr 25, 2022, 6:52 PM UTC

One thing that really excites me is concurrent writes -- I was poking around the project, and I've seen drh has been working on this for a bit now. [1] [2]

I believe the high level approach he's taking is essentially: 1. Concurrently execute the multiple write transactions in parallel. 2. Sequentially write the changed pages to the WAL. *[3] If a previous transaction causes the next to compute differently (conflict), then rerun that next transaction & then write.

The way to detect if were conflicts is essentially:

1. Keep track of all the b-tree pages accessed before running the transaction 2. Check the WAL if any previously transaction modified one of those b-trees. If so, this means we have to rerun our transaction.

I've seen it done in software transactional memory (STM) systems as well. It's really beautifully simple, but I think there are a lot of devils in the details.

[1] https://github.com/sqlite/sqlite/blob/9077e4652fd0691f45463e...

[2] https://github.com/sqlite/sqlite/compare/begin-concurrent

[3] * Write to the WAL, so that parallel transactions see a static snapshot of the world.

Re: Ask HN: Have you used SQLite as a primary database?

#256

Post by srcreigh » Mon, Apr 25, 2022, 6:53 PM UTC

woah wrote: post #238

divbzero wrote: post #233

Earlier quoted context omitted.

The Consider SQLite post mentions that one of SQLite’s in the past decade as “WAL mode (enabling concurrent reads and writes)”. Does this mean that the official advice to avoid SQLite for concurrent writes [1] is no longer a big concern? [1]: https://www.sqlite.org/whentouse.html

Take this with a huge grain of salt because I am by no means an expert, but I currently am working on some scripts that import a few million rows into SQLite. I am using bash and the sqlite command line. I was getting a lot of concurrent write errors from sqlite (bear in mind i am only doing inserts of separate rows so in theory there is never an actual conflict), so I tried using WAL mode. It actually resulted in mo…

are you ingesting the data under 1 transaction? This is a common SQLite issue as writes aren't slow but transactions are. By default 1 write = 1 txn but you can put millions of writes into 1 txn and get many orders of magnitude speedup

Re: Ask HN: Have you used SQLite as a primary database?

#257

Post by dgb23 » Mon, Apr 25, 2022, 6:59 PM UTC

rapind wrote: post #249

dgb23 wrote: post #235

Earlier quoted context omitted.

Running MySQL/Postgres over SQLite: - needs to be provisioned and configured - needs additional tooling and operational overhead - comes with a _large_ performance overhead that is only won back if you have quite a significant load - especially writes, which means the vast majority of web projects are slower and require more resources than they should. - it makes the whole system more complex by definition It is a co…

Just wanted to add another’s scenario where postgresql has been useful to me. Functions. There are cases where you have expensive operation(s) that reference a lot of persistent data. Even without massive traffic these operations can be prohibitively expensive in the middleware. Leveraging database functions can be a massive performance improvement (100x + for me), especially if your middleware is slow (e.g. rails).…

If you need the expressiveness and power of Postgres then sure, it has also way better JSON support for example, there is generally better tooling for it as well and so on. But in this case, your database becomes it's own _thing_, has much more value outside of being just durability for an application. Like for example Supabase is doing things. That's a very fundamental design decision IMO. I explored this and it is very attractive and robust, but serves different use-cases.

Re: Ask HN: Have you used SQLite as a primary database?

#258

Post by iampims » Mon, Apr 25, 2022, 7:07 PM UTC

srcreigh wrote: post #254

iampims wrote: post #184

Earlier quoted context omitted.

Any tips you can share on how to deploy your saas app without downtime when using sqlite?

For my personal project I'm planning to use a VM with an SSD. Manually I'll use caddy to switch over to a new running backend service with readiness check. As for scaling if I need it I can increase disk space for the app server or scale out horizontally/vertically. Don't need that yet so I'm waiting for more details in the future to decide how to handle that.

Which backend “owns” the sqlite database?

That’s what I haven’t seen mentioned anywhere, if the database is part of the application, how do you switch from one version to another without downtime.

Re: Ask HN: Have you used SQLite as a primary database?

#259

Post by oppositelock » Mon, Apr 25, 2022, 7:07 PM UTC

mekster wrote: post #118

Glench wrote: post #32

Here's an all-time great post about why you might consider SQLite in production with data about performance: https://blog.wesleyac.com/posts/consider-sqlite I use SQLite in production for my SaaS[1]. It's really great — saves me money, required basically no setup/configuration/management, and has had no scaling issues whatsoever with a few million hits a month. SQLite is really blazing fast for typical SaaS workloads…

What is the point of using SQLite under a web service? I thought people complained how MySQL sucks and PostgreSQL rocks for being right and SQLite was nowhere near being right or performant. (Things seem to be getting better with strict column types these days.) I've recently migrated a smallish service from MySQL to PostgreSQL and figured it's quite a work if you're not careful writing by the SQL standard which mean…

SQLite is far faster than Postgres or MySQL, however, the price you pay for this is having a single writer thread, and it's a library incorporated into your process, not a shared DB. It's faster because those other features of a server have a cost as well, particularly the cost of write arbitration.

SQLite is fine when all your load can be served by a single backend process on a single machine. The moment you need multiple backends to handle more load, or the moment you need high availability, you can't do it with SQLite. SQLite has very limited DDL operations, so you also can't evolve your schema over time without downtime. Now, for streaming backups - how do you come back from node failure? You're going to incur downtime downloading your DB.

I run many SQL backed production services, and my sweet spot has become a big honking Postgres instance in RDS in AWS, with WAL streaming based replicas for instant failover in case of outage, and read replicas, also via WAL. This system has been up for four years at this point, with no downtime.

I love SQLite, and use it regularly, just not for production web services.

Re: Ask HN: Have you used SQLite as a primary database?

#260

Post by pengo » Mon, Apr 25, 2022, 7:29 PM UTC

We adopted SQLite for a commercial Windows application suite. the architecture allowed users to export, backup and share databases (useful in the context). I've also used it for a large number of developer desktop utility apps. I'd consider it for web apps which have limited data throughput; I'm not convinced it's a good candidate for high data volumes (but open to changing my mind).