6

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?

Community
  • 1
  • 1
Tango
  • 649
  • 1
  • 12
  • 29

1 Answers1

2

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.

Googie
  • 5,742
  • 2
  • 19
  • 31
  • I would have thought that one disadvantage to using a large file is that if something goes wrong, I lose the entire data set. Is that not likely to happen? – Tango Dec 01 '14 at 17:18
  • "Something goes wrong", like if your drive breaks? Well, you're right, but for this sake you should do backups cyclically. You don't want to lose even single table to a drive break. That's why you would do backups. Since you would do backups, it doesn't matter if you do backups of a single file or several files. – Googie Dec 02 '14 at 10:24
  • Or the file gets corrupted. I had issues at one point with the DB files locking up. (Turned out that SQLite Browser isn't always well behaved.) – Tango Dec 02 '14 at 13:26
  • Well, you should always have a backup anyway. Even if you loose just one table it's still harmful. – Googie Dec 02 '14 at 14:54
  • Always three backups. Two on site, one encrypted on Amazon. – Tango Dec 03 '14 at 00:57