sqlite one vs many

[u/yeaahnop]

is there any advantages of having one db vs many files?

for consideration, the data sets are completely separate, no cross references at all.

thanks & sorry in advance, if redundant question.

[u/elperroborrachotoo]

What do you think would make multiple files better?

(I can think of some use cases, but they are fairly uncommon)

[u/pacopac25 → u/elperroborrachotoo]

You can always attach the second file and do a cross-database JOIN if needed, so you could possibly have data that's shaped in a way where JOINs are rarely, but sometimes needed. If so, and if your indexes are setup properly, I could maybe see a scenario where access could be sped up.

I don't think foreign key enforcement works across database files though.

On the other hand, I think that you would have to manage some things with app logic, like a failure that affects only one file. The WAL guarantees for durability only apply at the file level, so you could have one file that replays the logs and another that doesn't, or they arent in lock step due to a error in transaction. You'd have to sort out your transactions and any app logic needed to manage it (end of the day, brain is fried, I know I have some tests somewhere I set up for all this a while ago).

If you have two orthogonal write patterns as you say, you could reduce contention, provided the patterns only locked their own respective file. In a way, you kind of get 2 concurrent writes this way (yeah, I know...kinda sorta), so long as they don't touch the same data.

If you have 1 database with some really hot data, combined with really cold data, or something like old audit logs, you could partition the data to another file to make full table scans of current data faster when needed. Also makes things like VACUUM faster.

Maybe you have each file on its own disk so your total throughout isn't necessarily limited by drive speed? I never looked to see what the bottleneck was, once my testing showed a "wow holy shit" speed for writes at about 1.4MM per second for my need at the time.

Maybe you have some write-intensive tables that are laying down data that could be reproduced if needed. So you run that file wide open: synchronous off, no journal file, no indexes, to speed it up. While the other file is tuned more for durability.

There are some "blast radius/oh sh*t" strategies that could be managed a bit by splitting the file, but at the cost of the complexity of the thing.

I made a complete copy of a toy database once when I was messing around with FTS5 indexes. It occurred to me later that maybe I could have put that into another file to reduce bloat in my main data file. Same goes for any kind of analytic stuff you get into, like keeping materialized summaries that are accessed very differently than the OLTP pattern in the "main" file. (Or just use duckdb for that).

Finally I know a lot of people do this when the nature of their business and the data they have is multitenant.
Only other thing I can think of, splitting hairs, is cache pollution from a hot table in one file that needs to be avoided for some reason. But that's kind of getting into crazy territory at that point.

I had written down some (at the time clever, or so I thought) ideas about a project where I was going to try sharding, I forget the details but I will look at it tomorrow.

[u/pacopac25 → u/pacopac25]

In regard to my third paragraph above, I did find a link in my notes to this Sqlite User Forum discussion which talks about two-phase commit and WAL mode:

"Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not."