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

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

#241

Post by hampelm » Mon, Apr 25, 2022, 5:29 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…

Thanks for this comment! I had tried some bulk imports into SQLite and mostly gave up after hitting similar limits, thinking I was doing something wrong with the configuration.

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

#242

Post by manish_gill » Mon, Apr 25, 2022, 5:37 PM UTC

srcreigh wrote: post #163

manish_gill wrote: post #39

One of my previous employers was using SQLite as a large distributed database - they had their own custom sharding strategy, but essentially the idea was to shard A accounts * B tables * C num_of_days with a .db file for every shard. When I first came and saw it, it...did not sound right. But I didn't want to be the guy who comes in and says "you are doing it wrong" month 1. So I went along with it. Of course, eventu…

Data loss is a pretty serious problem. Do you have any more information about the situation? Could it have been in the hand spun partitioning logic instead of SQLite? What was the ingestion throughout roughly?

Unfortunately I don't have numbers on hand. We approximated our Postgres would ingest around 1 TB over the course of a year, later I think? I could be wildly wrong.

It's been more than 5 years but from what I remember, it definitely was _not_ the partitioning logic (the sharding just meant we had a huge amount of files that were hard to organise). But a single consumer doing heavy writes on a single SQLite file would see enough traffic that pretty soon you would start to see errors and your writes would start to break.

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

#243

Post by alberth » Mon, Apr 25, 2022, 5:49 PM UTC

Jason_Gibson wrote: post #209

alberth wrote: post #80

a. I'm surprised no one has mentioned WAL2 + BEGIN TRANSACTION, both of which are in separate branches with the plan to be merged into main. Even though SQLite can handle 99% of peoples use cases, WAL2 + BEGIN TRANSACTION will greatly close that last 1% gap. b. Expensify has created a client/server database based on SQLite called https://bedrockdb.com and years ago it was scaling to 4M+ qps https://blog.expensify.com…

Do you mean 'BEGIN CONCURRENT'? https://sqlite.org/cgi/src/doc/begin-concurrent-pnu-wal2/doc... Where did you see that the plan is to bring those into the mainline distribution?

Whoops, yes - meant BEGIN CONCURRENT (I can't update my original post).

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

#244

Post by andix » Mon, Apr 25, 2022, 5:57 PM UTC

samwillis wrote: post #5

Simon Willison has written about using SQLite for a "Baked in data" architecture which is a super interesting method for some situations: https://simonwillison.net/2021/Jul/28/baked-data/ As he notes https://www.mozilla.org/ uses this pattern: > They started using SQLite back in 2018 in a system they call Bedrock ... Their site content lives in a ~22MB SQLite database file, which is built and uploaded to S3 and then…

If your database is just 22MB, probably even MS Access 2000 will perform adequately.

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

#245

Post by FR10 » Mon, Apr 25, 2022, 5:59 PM UTC

theshrike79 wrote: post #18

My solution path for databases has been like this for a good decade: 1) Sqlite 2) Self-hosted Postgres 3) Big Boy Database, with an $$$ cost. (AWS Aurora, Oracle, etc). Most projects never leave the Sqlite level. Only one has left the Postgres level so far.

Im using SQLite for several personal projects as well, if you were to migrate to Postgres how would you go about it? Any tools/service you recommend?

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

#246

Post by jksmith » Mon, Apr 25, 2022, 6:07 PM UTC

jksmith wrote: post #158

I'm using for a suite of commercial desktop products and it's working out really well. You'll need to figure your multiple reader/single write connection pools, and graceful shutdowns in your custom server to avoid a data file corruption. This stuff you wouldn't normally do with a db server, but the discovery has made for some great learning and provided food for all kinds of load balancing and distributed db designs…

BTW, the only corruption I've run into with sqlite is plug-pullers with transaction frames still open, killing a debug session, the usual shenanigans that are improper with basic NTFS.

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

#247

Post by [deleted] » Mon, Apr 25, 2022, 6:09 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…

[deleted]

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

#248

Post by oliwarner » Mon, Apr 25, 2022, 6:28 PM UTC

Used it in several Django projects, up to around 2M pageviews a day but most of that cached read.

I usually drop it because I need something that Postgres has or does better, or it's a write heavy site.

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

#249

Post by rapind » Mon, Apr 25, 2022, 6:31 PM UTC

dgb23 wrote: post #235

mekster wrote: post #160

Earlier quoted context omitted.

Which part of running MySQL instead SQLite is over engineering?

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

I’ve used SQLite in production once and it worked great. But that was a very simple app. For more complex (but not always higher traffic) I’m leaning more and more on postgresql and less on my middleware, like moving business logic to the database when it makes sense.

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

#250

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

jjoonathan wrote: post #204

barrkel wrote: post #192

Don't be afraid of a database process. They are not scary, and are certainly less scary to scale up than whatever you might need to do with SQLite. There's more help available and better tooling. SQLite may shine in edge cases where you know you can outperform a regular database server and you know why, and you could build everything either way. SQLite could be a way to e.g. decentralize state, using local instances…

> tooling Yes, I learned this the hard way. I understood that simplicity meant limitations, but I did not understand that simplicity meant danger until SQLite burned me. If your perf tanks, you don't want to have to spend days putting timers all around someone else's codebase. Caveat: SQLite may be better these days -- my incident happened in 2013 -- but I spent more time tracking that one SQLite issue than I have sp…

do you have any details about the situation you were facing?