Menu
I have a reasonably large data set and would like to store it in a file rather than a RDBMS.
The main table in the data set is just over 1M rows, 30 columns and about 600Mb in size as a CSV.
I'm considering SQLite. Is SQLite worth investigating for data sets of this size?
581
Very large datasets And even if it could handle larger databases, SQLite stores the entire database in a single disk file and many filesystems limit the maximum size of files to something less than this.
SQLite database files have a maximum size of about 140 TB. On a phone, the size of the storage (a few GB) will limit your database file size, while the memory size will limit how much data you can retrieve from a query. Furthermore, Android cursors have a limit of 1 MB for the results.
Sqlite is ok for mobile application and small applications but I would avoid it for larger projects. Go for something like MySQL, SQL Server (Windows) or Postgre. SQLite can be really slow in big projects. It depends on what are your wanting to do and what a "large project" in your view is.
The theoretical maximum number of rows in a table is 264 (18446744073709551616 or about 1.8e+19). This limit is unreachable since the maximum database size of 281 terabytes will be reached first.
SQLite will handle that file just fine; make sure to import the records in a transaction so that it doesn't spend a lot of time creating indexes until everything is imported.
163
You already have your answer but I'd like to share my current experiment: I've dumped billions of records worth 793 GiB of data into a single SQLite database and read queries are still surprisingly fast (under 1m).
Creation time took little over 22 hours and the post-index creation takes about 4 hours per column.
32
I investigated SQLite recently for a similar application. The SQLite documentation states that SQLite databases can be terabytes in size, and that the primary limitation of SQLite is concurrency (many users at the same time). Although we didn't go this direction (we have our own binary storage format), I was pretty confident that SQLite could handle files of this size.
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
