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

Learning a few things about running SQLite

jvns.ca

Post reply on HN
Print view View original

91–100 of 101 posts

Re: Learning a few things about running SQLite

#91

Post by noctune »

bspammer wrote: post #73
simonw wrote: post #2

> I’ve been backing up to AWS, which is always a pain because it’s annoying to navigate the AWS console to generate credentials. I got so annoyed with that a few years ago that I ended up building a whole tool just to solve that one problem: uvx s3-credentials create my-existing-s3-bucket This spits out read-write credentials that are scoped JUST for that bucket. You can add --read-only or --write-only to have creden…

A more general solution for dealing with complex AWS services is learning just enough terraform to let LLMs do the rest. It also makes it much easier to tear stuff down later as you won’t need to remember what you created.

Or cloudformation, if it is just for AWS anyhow.

Re: Learning a few things about running SQLite

#94

Post by Kalanos »

gabeio wrote: post #22
Kalanos wrote: post #16

It's great! However, it's only meant for local systems. Once you need to connect over a network or robustly handle simultaneous requests, you need something like postgres.

> However, it's only meant for local systems. Once you need to connect over a network or robustly handle simultaneous requests That’s not really accurate any longer. Mostly depends on how you layout your tables & files. If you shard the databases then multiple machines can act as writers for their shard. You can also split read requests from write requests and have read only machines scale up/down as much as you’d li…

sharding locally defeats the purpose of sharding; splitting it up across machines to get more resources

Re: Learning a few things about running SQLite

#95

Post by Kalanos »

tomjen3 wrote: post #55
Kalanos wrote: post #16

It's great! However, it's only meant for local systems. Once you need to connect over a network or robustly handle simultaneous requests, you need something like postgres.

Use WAL (yes this should be the default, or at least explained much better) and you can have one writer, many readers. Don't move to the network unless you have to - every single request gets massively slowed down because it has replaced local reads with network connections. Of course if you are building a startup you must consider scaling.

I tried WAL, but like you said, it froze up during multiple writes (when creating records, not updating). I guess what I needed was a write queue - not sure if that exists.

Re: Learning a few things about running SQLite

#96

Post by andrewaylett »

arjie wrote: post #42
andrewaylett wrote: post #12

I run my backups like this: OUT="${i}.sql.zst" PART="${OUT}.part" sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" - mv "${PART}" "${OUT}" That doesn't block writers (when the writer uses WAL), and gives me a dump that's compressed well while also being easy to sync. My Home Assistant DB is 1.8GB, my dump is 286MB compressed, and I'd guess 90% of that is consistent from one day to the next.

What do you backup from your Home Assistant? The default backups are huge, but I finally settled for just the config and I leave the videos and caches off. I also leave off all the HACS downloaded repos. I'm wondering if I'm missing out by doing what I'm doing. What's in the DB that makes the HA DB that big? You keep lots of historical time-series?

I've got quite a lot of historical data, yes. I don't back up `media` or `share`, but I do back up everything else using the automatic backup feature. Automatic backups are around 11GB each. That includes all the "apps", which includes a whole load of data that's not really related to HA :).

The SQLite backup script is part of my separate backup routine that predates HA's backups, and eventually makes it into a Borg backup hosted on rsync.net. Belt and braces.

Re: Learning a few things about running SQLite

#97

Post by phil294 »

inigyou wrote: post #72
xetera wrote: post #67

Earlier quoted context omitted.

In my experience, SQLite explain plans are by far the most useless of any database out there. No concept of costing, no buffer information, no explain analyze. It's almost like they don't want you looking at it.

sqlite competes with fopen. If you don't like it, use a real database.

...performance-wise. Not in general. It obviously does more than just opening a file.

Also, you don't get to choose your competitors. SQLite is single-writer focused and an embedded library, but it's a sophisticated database engine after all, so comparisons with other "real databases" are more than appropriate. This meme really needs to die. Even more so when all you're comparing is query plan debugging output where there is no justification for unreadable output.

Re: Learning a few things about running SQLite

#98

Post by deterministic »

An additional note running SQLite in production:

SQLite gets really slow when using very large BLOB's (100+ MB). I ended up having to store the BLOB's externally and refer to them from the SQLite DB. Not ideal of course (the BLOB's are not transacted) but works OK in practice using hashing/checks etc. to detect and handle invalid BLOB's.

Re: Learning a few things about running SQLite

#99

Post by arjie »

andrewaylett wrote: post #96
arjie wrote: post #42

Earlier quoted context omitted.

What do you backup from your Home Assistant? The default backups are huge, but I finally settled for just the config and I leave the videos and caches off. I also leave off all the HACS downloaded repos. I'm wondering if I'm missing out by doing what I'm doing. What's in the DB that makes the HA DB that big? You keep lots of historical time-series?

I've got quite a lot of historical data, yes. I don't back up `media` or `share`, but I do back up everything else using the automatic backup feature. Automatic backups are around 11GB each. That includes all the "apps", which includes a whole load of data that's not really related to HA :). The SQLite backup script is part of my separate backup routine that predates HA's backups, and eventually makes it into a Borg…

Thank you for responding. Yeah I found that the HA automatic backup system creates extra large backups.

I have only my own version of your SQLite backup. I suppose I shall learn the risks of this method with the first failure haha!

Re: Learning a few things about running SQLite

#100

Post by rafabulsing »

maccard wrote: post #81
rafabulsing wrote: post #49

Earlier quoted context omitted.

The vast, vast majority of websites never see anything even close to that, so it's a safe bet unless you have some specific reason to expect it to reach that kind of traffic, or you are dealing with workloads that SQLite really does not handle well, e.g. many concurrent writes. And if your workload is mostly reads, then you probably can use a cache layer, which allows SQLite to go further still.

Adding a cache layer to keep using the wrong database is an architecture failure - if you use Postgres from the get go you will not need the cache until way way later on anyway. (Side note, I’m team MySql but the same point applies)

What exactly is wrong about a solution that perfectly fits the most common use case out there, at a fraction of the cost/complexity?
Post reply on HN