I basically have one big gigantic table (about 1.000.000.000.000 records) in a database with these fields:id, block_id, recordid is unique, block_id is not unique, it contains about 10k (max) records with the same block_id but with different recordsTo simplify my job that deals with the DB I have an API similar to this:Engine e = new Engine(...);// this method must be thread safe but with fine grained locked (block_id) to improve concurrencye.add(block_id, asdf); // asdf up to 1 Kilobyte  max// this must concatenate all the already added records added block_id, and won't need to be bigger than 10Mb (worst case) average will be <5MbString s = e.getConcatenatedRecords(block_id);If I map each block to a file(haven't done it yet), then each record will be a line in the file and I will still be able to use that APIBut I want to know if I will have any peformance gain by using flat files compared to a well tunned postgresql database ? (at least for this specific scenario)My biggest requirement though is that the getConcatenatedRecords method returns stupidly fast (not so with the add operation). I am considering caching and memory mapping also, I just don't want to complicate myself before asking if there is an already made solution for this kind of scenario ?
Super Fast File Storage Engine
java;performance;concurrency;postgres
After some research. I found that these data stores makes for the most part of use cases I have:MVStore of H2 DatabaseMapDBOracle Bekeley DB Java EditionAnd Kyoto Cabinet that seems to have inactive developmentThe interesting part is that all they mostly back the API of java collections (lists, sets, maps...)All these projects allow me to open a file as a data store of huge collections and I can reference them by name, and there can be many collections per file. Each of them are indexed. The idea is that these projects are to be used as a foundation for real databases, you can view them as the data store engine of the database (be it SQL or NoSQL).Because these projects are the foundation for projects like mongodb, h2database and orientdb, then I am sure that if the simplistic datastore approach fits my needs, it will also scale without any problems. Because my partition needs are very simplistic I can also share the load with other nodes.