CODEWITHSUNDEEP
sqlite
thameera
thameera

Reputation: 9473

Multiple files for a single SQLite database

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?

Upvotes: 34

Views: 31607

Answers (3)

Henning
Henning

Reputation: 650

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!

Upvotes: 48

Alix Axel
Alix Axel

Reputation: 154513

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.

Upvotes: 5

jschildgen
jschildgen

Reputation: 4189

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.

Upvotes: 8

Related Questions