How to decide between SQLite database vs. in-memory usage

SQLite have very small footprint when load itself (~250-400Kb). When SQLite work with on-disk database, there is no memory overhead because of it, it don't even meter size of the file (by default database can hold about 1Tb of data in it's tables). And as you said, it's persistent data storage.

In case of in-memory database store, your application wil consume

memory = database file size + small overhead

Also yo will get some amount of limitations with queries you can perform on in-memory database (I can't remember for time I on the street, but you can't use transactions and vacuum)

Use SQLite with on-disk database and you will gain faster data access and lower footprint than trying to use files to store set of data

With SQLite you can quickly and easily query for specific data without having to load the entire dataset into memory and iterate over it.

Basically, if you keep it on the local file system and load it into memory on demand, this will always have a lower (or equal) memory footprint than if you have ALL data loaded into memory at all times. If you keep everything on the file system, this will save memory because you don't have to have everything loaded (into memory) at once, and even when something is loaded it quickly closes. The downside to this is slower access times, translating to decreased real world performance under very heavy loads.

On the other hand, if you keep everything loaded into memory, it will have a much higher memory footprint, compensated by a much faster database access time. You will have better real world performance under very high loads (assuming you can accomodate the memory usage), but a much higher memory footprint.

Your choice should be based on the needs and specs of the system you are operating on. If you aren't sure, try loading everything into memory and see if you have problems on your server during periods of heavy load. If it is not working, go back to the hard drive.