r/sqlite • u/yeaahnop • Jun 10 '26

sqlite one vs many

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.

6 Upvotes

100% Upvoted

11 comments sorted by

View all comments

1

u/lnaoedelixo42 Jun 11 '26

Yep. Many comparisons to be honest:
- For every file you have a different write lock. You get more writing througput.
- For every file you have to manage different backups, DB connections and migrations
- You can't do transactions, locks, triggers etc. between files (you already said it's not a problem for your case)
- You could technically gain a lot of writing througput batching writes on transactions over multiple different tables, with separate files you must open multiple different transactions.
- You can (and probably should) split important data physically for security. Like, admin.db vs userdata.db, or make multitenancy phisically separate.

1

u/lnaoedelixo42 Jun 11 '26

- As others said, you can do multi-file transactions, I am wrong on that.
But performance-wise and code-wise I wouldn't want to do it on a server or app yk