3

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:

  1. 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.

  2. 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 ?

Improve this question
3
  • do you want to consider cloud based solutions such as firebase?
    – Jon Scott
    Oct 19, 2017 at 10:47
  • no i do not think so, i would prefer a zero setup/administration solution, so that everything that is needed can be done from the application code like for example with file based hsqldb
    – user34719
    Oct 20, 2017 at 9:14
  • see also the similar question softwarerecs.stackexchange.com/questions/86630/…
    – rwst
    Mar 22 at 16:57

3 Answers 3

Reset to default
2

For this I'd seriously consider using mongodb even though you say this on a single machine for local access. In addition to your requirements, you probably want to think about ease of backup, etc. - I'd hate to loose a few thousand records, never mind hundreds of thousands or millions....

It is nosql. It has Java drivers/connectors. It works with JSON type data structures when you query or insert/update. For fast retrieval of single items, you can specify "only first return" so after it is found it won't keep looking for others, it is free and open, it installs with an "apt-get install mongodb" on Debian/Ubuntu, it is pretty much hitting all your points.

Improve this answer
2
  • Thank I will try. One thing that bothers me though is that mongodb has to be started as a separate process, right ?
    – user34719
    Oct 20, 2017 at 9:12
  • @nonameyet - it is a daemon process, so yes, it needs to be started at some point.
    – ivanivan
    Oct 20, 2017 at 14:31
1

Postgres

If you are able to represent your object as JSON, then consider using Postgres and its jsonb (“JSON binary”) data type.

This type accepts your JSON input, and then parses it to represent that JSON document in its own internal binary format. This special format allows Postgres to index elements you specify.

So you get the flexibility of semi-structured data as seen in the “NoSQL” products. And you also get the fast indexing and ACID-compliant data-safety of an enterprise-quality relational database.

Improve this answer
2
  • Thank I will try. One thing that bothers me though is that postgres has to be started as a separate process, right ?
    – user34719
    Oct 20, 2017 at 9:12
  • @nonameyet Yes, Postgres runs independently in separate OS processes. The "postmaster" process listens for incoming connections and spawns an OS process to serve that completed connection. Once that connection closes, the process ends. That architecture may change in the future but is true as of Postgres 10.
    – Basil Bourque
    Oct 20, 2017 at 14:52
0

From previous answers I read you don't want a dedicated DB process. So you can look for embedded databases. It seems https://mapdb.org/ might fit your needs.

Improve this answer
1
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Mar 23 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.