Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite Is All You Need

dbpro.app

Post reply on HN
Print view View original

41–50 of 66 posts

Re: SQLite Is All You Need

#41

Post by masklinn »

inigyou wrote: post #20
chii wrote: post #18

Earlier quoted context omitted.

> SQLite is all you need if it really is all you need. most use cases are really capable of being satisfied by sqlite, but the "architect" imagines they need more (or is preparing for the potential).

If you could conceivably use multiple processes at once to access the database, and not just as an edge case, you need something more than sqlite. Sqlite does support multi-process access correctly, but the performance is abysmal as it locks the entire file for any write transaction. Client/server databases have much smarter concurrency.

It’s perfectly fine if your write throughput is low, especially in wal mode. Multiprocess does not actually change much if anything, even in multithreaded mode you want every thread to have its own connection and to have a good handle on who is writing when.

Re: SQLite Is All You Need

#42

Post by chasd00 »

inigyou wrote: post #26
upmostly wrote: post #2

Author here. This started because I read Evan Hahn's STRICT tables post [1] last week and got curious how far "just use SQLite" actually holds up under real load, not toy benchmarks. So I built a small social app (Chirp: 50k users, 1M posts, ~2.5M follows) in one SQLite file, put it behind a plain Node server, and load tested it properly: real HTTP, real JSON serialization, autocannon hammering it over sockets. The w…

This comment was shadowbanned because an LLM wrote it, and HN uses an LLM detector.

> WAL vs the old rollback journal isn't a minor tuning knob, it's the whole story.

That gave it away for me. I still appreciate the information contained in the reply but it was obvious. FWIW I don’t mind LLM content as long as I’m learning something.

Re: SQLite Is All You Need

#43

Post by echoangle »

petcat wrote: post #40
masklinn wrote: post #36

Earlier quoted context omitted.

In fairness, sqlite is perfectly happy with Julian dates or Unix timestamps (that’s the affinity of a column typed “datetime” in non-strict mode) and timestamp(tz) are nothing to write home about except in complaint.

so now we've traded a text column for an int column that still can't validate that the number is actually externally consistent with the real world.

Is there an invalid unix timestamp? What is there to validate?

Re: SQLite Is All You Need

#44

Post by Xenoamorphous »

petcat wrote: post #21
chii wrote: post #18

Earlier quoted context omitted.

> SQLite is all you need if it really is all you need. most use cases are really capable of being satisfied by sqlite, but the "architect" imagines they need more (or is preparing for the potential).

> the "architect" imagines they need more (or is preparing for the potential) I guess I am one of those "architects" that imagines they need an actual date/time storage class instead of some stringly-typed text column that I hope will contain a parsable ISO8601 datetime string when I try to read it back. Hipp said that it will never be added because it will bloat the size of the embedded object. Because that is what…

> I guess I am one of those "architects" that imagines they need an actual date/time storage class instead of some stringly-typed text column that I hope will contain a parsable ISO8601 datetime string when I try to read it back.

To be honest if you're using JSON at any point in your stack you have the same issue.

Re: SQLite Is All You Need

#45

Post by simonw »

audunw wrote: post #3

The tests and the numbers are interesting. The LLM writing style? Insufferable. It really wouldn’t have taken much effort to cut out the worst of the AI fluff, and maybe add some human touches

"No version upgrades, no connection limits, no pooler, no failover drill, no separate thing to monitor, no separate thing to pay for"

Inspired me to finally build (well, vibe code) a cliché detector https://tools.simonwillison.net/llm-cliche-highlighter

Re: SQLite Is All You Need

#46

Post by ndr »

bendangelo wrote: post #25
mikeocool wrote: post #19

I love SQLite, but my clients expect minimal data loss and downtime when one of my servers goes down. How are people solving that issue with SQLite? Everytime I’ve investigated it, it seems like the state of the art is not very battle tested WAL shipping solutions. If I’m setting something like that up, suddenly running Postgres with its battle tested replication starts to look not so much more complicated in compari…

litestream is good for this.

I use it in several projects. You're still open to a few seconds of possible downtime (depending how often you flush the WAL to your remote location, and how bursty your writes are), and you need to have all of your db on disk on the process doing the reads, but it's been great.

Re: SQLite Is All You Need

#47

Post by f3b5 »

chii wrote: post #18
petcat wrote: post #10

> the Postgres container you spun up out of habit was never needed These posts need to stop comparing their contrived use-cases for SQLite to Postgres. Sure, SQLite is all you need if it really is all you need . But Postgres does so much more than just act as a data dump with an SQL engine on top. Dr. Hipp himself even said that SQLite does not, and will never, compete with the likes of Postgres. It competes with fop…

> SQLite is all you need if it really is all you need. most use cases are really capable of being satisfied by sqlite, but the "architect" imagines they need more (or is preparing for the potential).

I was such a big sqlite fan that I used it for my data-intensive startup. Once I actually started scaling it up I ran into crippling file system race conditions. This was because we were hosted on a distributed file system in the cloud which I learned is very very bad for sqlite.

So I had to migrate the production db under live load from sqlite to mysql which was a quite ...intense week. I still like sqlite but I'd be wary of using it again for a usecase like mine.

Re: SQLite Is All You Need

#48

Post by petcat »

echoangle wrote: post #43
petcat wrote: post #40

Earlier quoted context omitted.

so now we've traded a text column for an int column that still can't validate that the number is actually externally consistent with the real world.

Is there an invalid unix timestamp? What is there to validate?

Well for one we should probably validate that the number is smaller than the total life of the physical universe.

SQLite will gladly store a u64::MAX as a "unix timestamp" despite it being about 300x larger than the number of seconds that the universe and everything in it has existed.

Try reading that back in any application date/time code and your app probably crashes immediately.

Re: SQLite Is All You Need

#49

Post by luke5441 »

rmunn wrote: post #30

There's some bad advice in the article: "Backups are a file copy. [...] you can back up a live SQLite database, under write load, without stopping anything." This is straight out of section 1.2 of https://www.sqlite.org/howtocorrupt.html . Yes, you can do that, and sometimes you will end up with a valid, non-corrupt backup. But it's timing-dependent: lose the race and you'll end up backing up a partially written tran…

At some point I thought I would be clever and just backup the file while no transaction is active (or manual WAL checkpointing and no WAL checkpointing active).

Ran into 2.2. https://www.sqlite.org/howtocorrupt.html -- the backup was close()ing the database file descriptor and canceling the SQlite locks.

Re: SQLite Is All You Need

#50

Post by scrlk »

fsuts wrote: post #38
Cthulhu_ wrote: post #28

Earlier quoted context omitted.

https://sqlite.org/wal.html presumably.

But like any message board it will get peak time hits So lunchtime in America there will be multiple people posting comments at the same time And Sqllite only allows one writer, so what happens if multiple people send their comments at once So are they using ultra fast storage on the host or queues or other

SQLite with WAL on a standard NVMe drive will easily do thousand of writes per second.
Post reply on HN