Maximum of tables a SQLite database file supports

Question

Even after reading SQLite limits I could not find the maximum number of tables a SQLite database file can hold. So, I'd like to know if

1. There is a maximum number of tables a SQLite database can hold?
2. It is an issue to have thousands of small tables in a SQLite database file?
3. Many tables in a SQLite database file can impact performance of queries?

Answer 1

The list of limits in SQLite is documented at this page. There is no maximum number of tables per database given, so there is likely no limit implemented by SQLite. There is a limit of 64 tables per JOIN.

4. Maximum Number Of Tables In A Join

SQLite does not support joins containing more than 64 tables. This limit arises from the fact that the SQLite code generator uses bitmaps with one bit per join-table in the query optimizer.

SQLite uses an efficient query planner algorithm and so even a large join can be prepared quickly. Hence, there is no mechanism to raise or lower the limit on the number of tables in a join.

15. Maximum Number Of Tables In A Schema

Each table and index requires at least one page in the database file. An "index" in the previous sentence means an index created explicitly using a CREATE INDEX statement or implicit indices created by UNIQUE and PRIMARY KEY constraints. Since the maximum number of pages in a database file is 2147483646 (a little over 2 billion) this is also then an upper bound on the number of tables and indices in a schema.

Whenever a database is opened, the entire schema is scanned and parsed and a parse tree for the schema is held in memory. That means that database connection startup time and initial memory usage is proportional to the size of the schema.

Are the table identical in structure? If so, it's generally considered a better practice to store them in a single table with an identifying column.

Answer 2

I believe the number of tables is constrained only by the size of the database. There can be at most 2,147,483,646 pages in a single SQLite database. So I'd guess that would also be the maximum number of tables in a single SQLite database.

That's based on the assumption that database pages are used only for tables, which probably isn't a very useful assumption.

Answer 3

To answer your questions 2 and 3, although having multiple tables with similar structure goes against the principles of database normalisation, there are many practical reasons why it would be preferred over a single table or virtual table - the biggest of course being than in SQLite it's much easier to drop tables than columns. It also takes up less space than having "tableX" in every row of a single table if you take the simple approach and don't do "proper" normalized relational tables.

In terms of performance, you won't see any issues with using hundreds of thousands of tables compared to a single table with hundreds of thousands entries in the "table" column, and that column indexed. In fact the index on that single normalized table could be far larger than the table indexing mechanisms SQLite uses, and less efficient.

Having said all of that, i cannot with a healthy conscience end this post without saying that much like exec() being used to assign variables with variable names being a common beginner mistake in programming, making multiple tables which should be in a single normalized table (virtual or otherwise) is a common beginner mistake in database architecture. There are, in both areas, instances where the circumstances make using exec or many tables the correct option. If for example, your data is all very similar but you are sure you will not be doing any joining whatsoever on the data, then many tables is fine. Just make sure that you really do see the data as totally unrelated, despite being of a similar structure.