[simonw]

Multi-GB is tiny these days.

I didn't say anything about concurrent access. SQLite with WAL mode is fine for that these days for dozens of concurrent readers/writers (OK only one writer gets to write at a time, but if your writes queue for 1-2ms who cares?) - if you're dealing with hundreds or thousands over a network then yeah, use a server-based database engine.

[da_chicken]

Multi GB is tiny, but that doesn't make SQLite magically better at large queries of multi GB databases. That's why DuckDB has been getting more popular.

[benjiro]

Sqlite != DuckDB... two totally different DB types. One is a row based, the other is a column based database. Both run different workloads and both can handle extreme heavy workloads.

[da_chicken]

Yes, that's the point I'm making. If SQLite didn't ever struggle with databases in the GB ranges, then there wouldn't be much call to replace it with DuckDB. The fact that there's significant value in an OLAP RDBMS suggests that SQLite is falling short.

[benjiro]

The problem is not SQLite struggling with databases in GB range. It does that with ease. OLAP requires a different database structure, namely column storage (preferably with compacting / compression / and other algorithms).

That is DuckDB its selling point. You want data analyzing, you go DuckDB. You want oltp you go SQLite. Or combine both if you need both.

Even postgres struggles with OLAP dataloads, and that is why we have solutions like TimescaleDB / postgres plugin. That ironically uses postgres rows but then packs information as column into columns row fields.

That does not mean that postgres is flawed working with big data. Same with Sqlite... Different data has different needs, and has nothing to do with database sizes.

[brulard]

I always had troubles having multiple processes get write access to the sqlite file. For example if I have node.js backend work with that file, and I try to access the file with different tool (adminer for example) it fails (file in use or something like that). Should it work? I don't know if I'm doing something wrong, but this is my experience with multiple projects.

[dathinab]

There are multiple aspects to it:

- sqlite is a bit like a RWLocked database either any number or readers xor exactly one writer and no readers

- but with WAL mode enabled readers and writers (mostly) don't block each other, i.e. you can have any number of readers and up to one writer (so normally you want WAL mode if there is any concurrent access)

- if a transaction (including implicit by a single command without "begin", or e.g. upgrading from a read to a write transaction) is taking too long due to a different processes write transaction blocking it SQLITE_BUSY might be returned.

- in addition file locks might be used by SQL bindings or similar to prevent multi application access, normally you wouldn't expect that but given that sqlite had a OPEN_EXCLUSIVE option in the past (which should be ignored by half way modern impl. of it) I wouldn't be surprised to find that.

- your file system might also prevent concurrent access to sqlite db files, this is a super obscure niche case but I have seen it once (in a shared server, network filesystem(??) context, probably because sqlite really doesn't like network filesystems often having unreliable implementations for some of the primitives sqlite needs for proper synchronization)

as other comments pointed out enabling WAL mode will (probably) fix your issues

[Groxx]

They can't write concurrently, but generally speaking yes, they can: https://sqlite.org/faq.html#q5

Your throughput will be much worse than a single process, but it's possible, and sometimes convenient. Maybe something in your stack is trying to hold open a writable connection in both processes?

[cyanydeez]

PRAGMA journal_mode = WAL;

[simonw]

That is because the default SQLite mode is journal, but for concurrent reads and writes you need to switch it to WAL.

[brulard]

I use WAL basically everywhere. I thought that would fix my problem some time ago, but it didn't

[simonw]

Are you seeing SQLITE_BUSY errors?

Those are a nasty trap. The solution is non-obvious: you have to use BEGIN IMMEDIATE on any transaction that performs at least one write: https://simonwillison.net/tags/sqlite-busy/

[asa400]

This is correct, and one of the things I tell anybody who is considering using SQLite to watch out for. The busy timeout and deferred write transactions interact in a really non intuitive way, and you have to use BEGIN IMMEDIATE for any transaction that performs any writes at all, otherwise SQLite gives up and throws an error without waiting if another traction is writing when your traction attempts to upgrade from a read to a write.

[brulard]

Thanks for the direction. I thought SQLite was limited in how multiple processes can access the db files, but now I see the problem is on my end. Btw. I'm a fan of your AI/LLM articles, thanks for your awesome work.

[Asmod4n]

An average human being can produce around 650MB of text during a while work lifetime when doing nothing but write text 4 hours per weekday without any interruptions.

Saying multi gigabyte databases for single user usage is the norm feels insane to me.

[simonw]

Have you seen the size of the database of email you've received?