SQLite for large data sets?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.