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

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

#201

Post by tlamponi » Mon, Apr 25, 2022, 4:00 PM UTC

Yes, our clustered, real-time configuration file system uses sqlite as sole backing store.

https://pve.proxmox.com/pve-docs/chapter-pmxcfs.html

https://git.proxmox.com/?p=pve-cluster.git;a=tree;f=data/src...

When it was written by our CTO over 10 years ago he tried every DB solution available, that is those that somewhat fit the picture, only sqlite survived any test thrown at them, if setup as documented it handles a pulling the power plug in any situation, at least in our experience.

It may need to be noted that the DBs are only used locally, we synchronize commits ourselves via a distributed FSM, that's mostly transforming the Extended Virtual Synchrony corosync provides to simple Virtual Synchrony.

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

#202

Post by bob1029 » Mon, Apr 25, 2022, 4:01 PM UTC

therealdrag0 wrote: post #188

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…

> hits a month Is not a very useful performance metric. What is your peak hits per second?

I love watching people use "x per month" as some sort of architecture selection argument, especially when these arguments conclude in a proud justification of cloud sprawl.

There are single node/CPU solutions that can process 10-100 million business events per second. I am almost certain that no one logged into HN right now has a realistic business case on their plate that would ever come close to exceeding that capacity.

E.g.: https://lmax-exchange.github.io/disruptor/disruptor.html

This stuff isn't that hard to do either. It's just different.

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

#203

Post by AtNightWeCode » Mon, Apr 25, 2022, 4:01 PM UTC

SQLite is a horrible database in comparison to Postgres, MSSQL, Mysql and so on. There is no reason to use it as a primary db. There are many other areas were SQLite really shines though.

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

#204

Post by jjoonathan » Mon, Apr 25, 2022, 4:05 PM UTC

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 spent spinning up postgres instances since then.

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

#205

Post by baq » Mon, Apr 25, 2022, 4:06 PM UTC

yes, used sqlite and failed miserably at multi-process access to the same db file. no DBMS on top of it, so to be expected. moved to postgres.

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

#206

Post by JohnBooty » Mon, Apr 25, 2022, 4:14 PM UTC

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…

This was so inspiring to read. It's a very balanced take about the pros and cons of using SQLite vs. Postgres at scale.

I say "inspiring" because using SQLite reminds me of the simplicity and productivity from coding for the "early web" that lost 10-15 years ago. The days when you could spin up a website without worrying about a bunch of ancillary services and focus on the app itself.

For me, SQLite's lack of online schema changes seems like perhaps the biggest blocker to actual production. I've never had a production project where the schema didn't change a lot.

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

#207

Post by mdp2021 » Mon, Apr 25, 2022, 4:20 PM UTC

sgbeal wrote: post #112

mdp2021 wrote: post #33

I do not quite understand the premises: > Given the complexity Which complexity? It is the simplest possible widespread, reliable and effective solution. Which makes it a primary choice. > it seems like there are use cases or needs here that I'm not seeing On the contrary, the use cases for the traditional Relational DB engines are defined: when you need a concurrency manager better than filesystem access. (Or maybe…

> ... the possibility of recursion was implemented only recently, etc). Noting that "recently" was August 2014 (version 3.8.6), according to https://sqlite.org/oldnews.html . The "missing" right/full join types just hit trunk this past week and are still being debugged: https://sqlite.org/src/info/f766dff012af0ea3

> Noting that "recently" was August 2014

Right. This as an issue has little to do with deployment the way the submitter intended, and has to do with software that was implemented using specific static versions of SQLite and that some sometimes use as no better alternative meanwhile emerged. The "delay" is more evident (more "daily present") to said users, and has little to do with new products.

Nonetheless, given that, I would first check which subset of SQL you may need - I am not sure on how much overlapping you have with other mainstream products.

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

#208

Post by qorrect » Mon, Apr 25, 2022, 4:23 PM UTC

uuyi wrote: post #25

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…

I designed something that used a local SQLite database on the client and a remote postgresql instance as the master. It used read and write queues at each end for sync and was eventually consistent. Unfortunately it was far too advanced for the org and no one else understood it so it was canned in favour of a connected solution under the guise of ubiquitous internet access being available. This is proving to be a poo…

Hey I actually do the same in a mobile app. I dump everything into a local database, when they are connected to the internet it syncs, lets it work offline.

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

#209

Post by Jason_Gibson » Mon, Apr 25, 2022, 4:27 PM UTC

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?

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

#210

Post by JohnBooty » Mon, Apr 25, 2022, 4:29 PM UTC

dotancohen wrote: post #143

Karunamon wrote: post #123

Earlier quoted context omitted.

Maybe this is a failure of imagination on my part, but won't most people be using ORMs? Again, talking about the use case of the average application that's light enough to get away with SQLite, it doesn't seem like you would need to be hand writing queries.

In my experience ORMs add a layer of complexity, instead of removing one. It's nice to e.g. have a "Pythonic" interface in Python, but when working close to the data I far prefer to write a concise, clear query instead of trying to remember some ORM syntax or what they're calling VARCHARS in this particular ORM, or how they're representing JOINS, or if the condition will be on the ON clause or the WHERE clause, or ho…

Wrote code for many years sans ORMs.

Two features I enjoy in ActiveRecord and other ORMs, and why I would consider them a good standard practice for most things that aren't "toy" projects.

1. Easy chainability. In ActiveRecord you can have scopes like `User#older_than_65` and `User.lives_in_utah` and easily chain them: `User.older_than_65.lives_in_utah` which is occasionally very useful and way more sane than dynamically building up SQL queries "by hand."

2. Standardization. Maintenance and ongoing development (usually the biggest part of the software lifecycle) tend to get absolutely insane when you have N different coders doing things N different ways. I don't love everything ActiveRecord does, but it's generally quite sane and you can drop a new coder into a standard Rails project and they can understand it quickly. On a large team/codebase that can equate to many thousands or even millions of dollars worth of productivity.

    I far prefer to write a concise, clear query instead 
    of trying to remember some ORM syntax

100% agree.

ActiveRecord strikes a good balance here IMO. An explicit goal of ActiveRecord is to make it painless to use "raw" SQL when desired.

On non-toy projects, I think a policy of "use the ORM by default, and use raw SQL for the other N% of the time when it makes sense" is very very sane.