[flagged]
Learning a few things about running SQLite
41–50 of 101 posts
Re: Learning a few things about running SQLite
#42I 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?
Re: Learning a few things about running SQLite
#43[dead]
Re: Learning a few things about running SQLite
#44Is it me or this is one of the worst and knowingly less informed articles that has hit HN in a while?
Don't confuse Julia's humility / accessible writing style for "less informed". She's a deeply knowledgeable programmer who's been doing this for a long time. She works hard to make tech topics feel less intimidating to newcomers.
Re: Learning a few things about running SQLite
#45the best thing about sqlite is that it's one of the few pieces of software where reading the documentation makes you a better engineer instead of just a more confused one
Re: Learning a few things about running SQLite
#46Why not try a real database like Postgres? It's not as light-weight, but when operations get complicated, real databases are much easier to work with. I had a website that started with SQLLite, but when it got complicated enough, I spent two days to migrate the whole thing to Postgres. With current LLM coding agents, it's not that hard.
Honestly, I love PostgreSQL, but now I have another server or service to run. SQLite is just a file and often, that is enough.
PGlite offers the "real database" compiled to WASM, which can then be embedded similarly to SQLite. You don't have to choose between PostgreSQL and "just a file".
Re: Learning a few things about running SQLite
#47Earlier quoted context omitted.
Honestly, I love PostgreSQL, but now I have another server or service to run. SQLite is just a file and often, that is enough.
PGlite offers the "real database" compiled to WASM, which can then be embedded similarly to SQLite. You don't have to choose between PostgreSQL and "just a file".
Yeah pglite is exquisite. Now I don't have to write separate code for client and server side queries.
For example, I have a compiler that compiles a DSL to a DB query which returns a list. Now that query can either be on data in the browser or can be on data on the server. Now I don't have to write the logic twice!
Re: Learning a few things about running SQLite
#48> 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…
this is pretty brilliant, aws cli should sherlock this.
when would you want write only?
Re: Learning a few things about running SQLite
#49Earlier quoted context omitted.
To be fair they also say > Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite.
So about one per second (up to ten, less conservatively). I concur. But if you think your site might ever scale beyond that, do yourself a favor and use Postgres from the get-go.
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.
Re: Learning a few things about running SQLite
#50> 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…
this is pretty brilliant, aws cli should sherlock this. when would you want write only?
For logging, in particular for an environment where you don't want a leaked credential to allow the deletion of any previously recorded files.