Large databases in sqlite - file size considerations?

Question

I am using sqlite db, which is very convenient and seems to meet all my needs at the moment.

My db is currently <50MB in size, but now I need to add a new table that will store large text drops, which will cause db to reach 5 GB over the next year.

Can sqlite deal with 5GB size? Any caveats to this compared to mysql?

Answer 1

I'm not a big database expert, but most of the DB work I used used SQLite. In my experience, creating a larger database in itself should not lead to great results. Naturally, you will have more data, so get ready to spend more time requesting it!

Consider this thought experiment: you have a table called mydata that you use all the time in the database. Now you add an unrelated otherdata table. Your queries for mydata are independent of the information in otherdata . Even if you insert GBs of data into otherdata , you won’t feel any real performance improvement when using mydata .

AFAIK, SQLite architecture supports this claim.

Answer 2

SQLite should be perfect for what you want to do. Size really is not a concern. As long as your data file can be on the same computer on which the call is made, you should be fine. If you put it on a network, that's fine, but multi-user access depends on operating system errors when it comes to locking records, etc. Compared to mysql, since you have eliminated the server, you also eliminate network traffic associated with data extraction. this should speed up the process.

-don

Answer 3

As stated in the Sqlite FAQS, Frequently Asked Questions

Take a look at item 12, it says that the maximum sqlite db limit can be up to 140 TB !!