Should I worry about sqlite filesize?

Question

When writing my own flat file databases I try and keep the file sizes as small as possible, when designing mySQL databases I put all my tables into one database (I'm under the belief that mySQL stores each table in it's own file). I'm new to sqlite and my ethics clash - a whole database stored in one file.

I know the recommended size is about 2GB per database for sqlite, and I don't expect to reach that size, but is there any upside to splitting the database? For example splitting a database into two, one with various settings tables (numerous tables, low number of rows), the other with various content tables (few tables, many rows in each).

I have a good understanding of filesystems, and know I shouldn't bother this much about it, but there is a gut feeling I just cannot shake of wanting to separate the database. Is this a feeling to ignore or run with?

Answer 1

Doesn't sound like your "settings" database would account for enough space to be worth the trouble. You could just as easily keep those settings tables in the primary database and cache the setting values to avoid repeated queries into those tables.

Answer 2

Unless you are going to store lots of data in the database, it really shouldn't matter. If you are going to store a lot of data, including blobs and complex relations, you should not use SQLite anyway - that's what the big database systems are for.

SQLite exists to have a simple, fast and compact way of storing structured data in un-networked, stand-alone applications. You can also use it as a file format for your own applications so you don't have to re-invent the wheel every time. Personally I use it for storing preferences and to be sure that it works across different platforms. You can also use standard tools to manipulate the databases.

You know, there is a term called "over engineering". Sometimes it's just fine to go with a simple, fast approach and separate things into separate tables and modules; sometimes it's not.

• SQLite Internal Limits