CODEWITHSUNDEEP
pythonsqlite
Tango
Tango

Reputation: 669

SQLite: Using One File vs. Many Files

I'm working on a project in Python and using SQLite3. I don't expect to be using any huge number of records (less than some other projects I've done that don't show any notable performance penalty) and I'm trying to decide if I should put the entire database in one file or multiple files. It's a ledger program that will be keeping names of all vendors, configuration info, and all data for the user in one DB file, but I was considering using a different DB file for each ledger (in the case of using different ledgers for different purposes or investment activities).

I know, from here, that I can do joins, when needed, across DBs in different files, so I don't see any reason I have to keep all the tables in one DB, but I also don't see a reason I need to split them up into different files.

How does using one DB in SQLite compare to using multiple DBs? What are the strengths and disadvantages to using one file or using multiple files? Is there a compelling reason for using one format over the other?

Upvotes: 6

Views: 4194

Answers (1)

Googie
Googie

Reputation: 6017

Here are couple of points to consider. Feel free to add more in comments.

Adventages:

  1. You can place each database file on a different physical drive and benefint from parallel read/write operations, making those operations slightly faster.

Disadventages:

  1. You won't be able to create foreign keys across databases.
  2. Views that rely on tables from several databases will require you to attach all databases all the time, using exactly same names for attached databases (querying the view will report an error if the SELECT statement defined inside is incorrect, but it's compiled and validated only when queried).
  3. Triggers cannot operate cross-database, so trigger on some table can query only tables from the same database.
  4. Transactions will be atomic across databases, but only if the main database is neither in WAL mode, or a :memory: database.

In other words, you can achive some speed boost (assuming you have file drives to spere), but you lose some flexibility in database design and it's harder to maintain consistency.

Upvotes: 2

Related Questions