I am looking for a good solution to store and retrieve a large set (let's say a few hundred million or even up to a billion) of data items consisting mostly of strings (but a few numeric fields as well) to/from disk from a Java server application. The number of data fields on the items is not fixed, so for example the corresponding Java object contains collections (Set,Map,List) of Strings. I am retrieving the data from an external source and pre-processing it within my application and need a way to store it in the pre-processed format.
Later I need to be able to load the relevant parts of the data into memory as quickly as possible. There are also a few processes where the data is further modified in which case I need to update selected fields of many / all items, but this does not happen very frequently.
My requirements are:
- Mapping between Java objects and persistent data (This does not change frequently so if it requires some work, thats ok)
- Fast serialization / writing to disk that also works in batches. I am not able to hold all items in memory so I need to persist them in batches, i.e. be able to add new items to the existing ones.
- Fast retrieval of single items (by id) and also for many/all items. I think I could hold an index of the item ids in memory for the queries I need, so it would be ok if the storage is a simple key-value store without any search facility. Another scenario will be using a Lucene index of the items and query the index first for text searching and then retrieving the items from the storage solution.
- Transaction handling: I need a save way to protect the read/write operations for concurrent access and I need to be able to apply changes in a transaction such that all changes are rolled back if something goes wrong.
- Easy deployment, I would prefer some file based approach that does not require starting an external database etc. My application runs on a single machine, so I am not looking for a distributed data store that requires lots of infrastructure.
- Must be free / open source. The license needs to allow using the storage in commercial applications as well.
So far I tried:
Writing all items to a single JSON file using Jackson: This makes reading/writing sufficiently fast, but obviously there is no easy way to retrieve single items or add to the existing ones. Also there is no transaction support etc.
Hibernate with HSQLDB in file mode: This makes object mapping, deployment and transaction handling easy, but the insert / load performance is very poor when inserting / loading many objects (x10 slower than the Json approach). I am still a bit surprised that reading/writing is so slow. First I was using some @ElementCollections / merge tables for the collection types within the items, which seemed even slower, later I serialized the collections to json and stored them in a varchar column, which improved the performance but is still slow. Not sure if SQL is the right approach here because I do not really have a fixed number of columns and do not need any relations between items.
Any recommendations ?