Multiple files for a single SQLite database

Question

Afaik, SQLite stores a single database in a single file. Since this would decrease the performance when working with large databases, is it possible to explicitly tell SQLite not to store the whole DB in a single file and store different tables in different files instead?

Answer 1

The question is about three years old but I landed here when I searched for the very same question. Late I found out, that it IS possible:

Use

sqlite3.exe MainDB.db

ATTACH DATABASE 'SomeTableFile.db' AS stf;

Access the table from the other database file

SELECT * FROM stf.SomeTable;

You can even join over several files

SELECT * FROM MainTable mt JOIN stf.SomeTable st ON (mt.id = st.mt_id); 

https://www.sqlite.org/lang_attach.html

tameera said there is a limit of 62 attached databases but I never hit that limit so I can't confirm that.

The big advantage besides some special cases is that you limit the fragmentation in the database files and you can use the VACUUM command separately on each table!

Answer 2

If you don't need a join between these tables you can manually split the DB and say which tables are in which DB (=file).

I don't think that it's possible to let SQLite split your DB in multiple files, because you connect to a DB by telling the filename.

Answer 3

SQLite database files can grow quite large without any performance penalties.

The things that might degrade performance are:

• file-locking contention
• table size (if using indexes and issuing write queries)

Also, by default, SQLite limits the number of attached databases to 10.

Anyway, try partition your tables. You'll see that SQLite can grow enormously this way.