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

31–40 of 101 posts

Re: Learning a few things about running SQLite

#31

Post by dukeyukey »

fragmede wrote: post #30
allknowingfrog wrote: post #27

Earlier quoted context omitted.

Is this something that the authors of SQLite actually claim? I don't think anyone else can decide what something is meant for.

Once you release software to the world, the world can choose use it however it wants. Still, the author's of SQLite document their intention for it to be used locally on their "when to use" page. > SQLite strives to provide local data storage for individual applications and devices. https://www.sqlite.org/whentouse.html

To be fair they also say

> Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite.

Re: Learning a few things about running SQLite

#32

Post by wwind123 »

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

Re: Learning a few things about running SQLite

#33

Post by inigyou »

striking wrote: post #5

> Maybe one day I’ll learn to read a query plan. With SQLite's `.expert` mode you can delay that day a little longer: https://www.sqlite.org/cli.html#index_recommendations_sqlite... sqlite> CREATE TABLE x1(a, b, c); -- Create table in database sqlite> .expert sqlite> SELECT * FROM x1 WHERE a=? AND b>?; -- Analyze this SELECT CREATE INDEX x1_idx_000123a7 ON x1(a, b); 0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (…

Looks similar to EXPLAIN QUERY PLAN: https://sqlite.org/eqp.html

Raw EXPLAIN dumps bytecode, which is usually much more verbose than you want. EXPLAIN QUERY PLAN dumps a summary.

Re: Learning a few things about running SQLite

#34

Post by andrewaylett »

formerly_proven wrote: post #24
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.

> That doesn't block writers (when the writer uses WAL) Neither does VACUUM INTO or ".backup" (which uses the backup API) or sqlite3_rsync or litestream.

I seem to recall having trouble with the read only flag and the backup API, but it's quite likely the problem was mine rather than with SQLite.

Anyway, the sync-friendly output is the really important part for me, because it means I can point borg at the zstd-compressed file and it'll only need to store what's changed.

Re: Learning a few things about running SQLite

#35

Post by inigyou »

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…

At this point you're building your own networked database using sqlite just as a backing store. You should really reconsider if it's easier than using something designed for it.

If your entire system is sharded by username anyway for other reasons, then maybe what you've described works for you.

Re: Learning a few things about running SQLite

#36

Post by inigyou »

allknowingfrog wrote: post #27
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.

Is this something that the authors of SQLite actually claim? I don't think anyone else can decide what something is meant for.

The authors have explained that sqlite is meant to compete with fopen more than it's intended to compete with Postgres.

Re: Learning a few things about running SQLite

#37

Post by inigyou »

dukeyukey wrote: post #31
fragmede wrote: post #30

Earlier quoted context omitted.

Once you release software to the world, the world can choose use it however it wants. Still, the author's of SQLite document their intention for it to be used locally on their "when to use" page. > SQLite strives to provide local data storage for individual applications and devices. https://www.sqlite.org/whentouse.html

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.

Re: Learning a few things about running SQLite

#38

Post by inigyou »

datadrivenangel wrote: post #3

"Maybe one day I’ll learn to read a query plan." Query plans aren't that hard to read! [0] 0 - https://xkcd.com/2501/

They might mean the output from EXPLAIN which is definitely hard to read in sqlite as it shows you the transaction bytecode.

Re: Learning a few things about running SQLite

#39

Post by abrookewood »

wwind123 wrote: post #32

Why 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.
Post reply on HN