I basically have one big gigantic table (about records) in a database with these fields:

id, block_id, record

id is unique, block_id is not unique, it contains about 10k (max) records with the same block_id but with different records

To 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 concurrency
e.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 <5Mb
String 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 API

But 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 ?

  • Have you worked out how big the file would be? Jan 31 '14 at 18:18
  • won't need to be bigger than 10Mb (worst case) average will be <5Mb Jan 31 '14 at 18:20
  • 2
    You said above it'd be a trillion rows (thousand billion). So something is out of sync between that and your 10mb estimate. Jan 31 '14 at 18:58
  • 1
    @DavidHofmann when you consider the "have it in a file" you've considered a nosql solution... just not one with a big name product behind it with significant R&D. Realizing this, it becomes then a question of what nosql solution is best.
    – user40980
    Feb 1 '14 at 0:09
  • 1
    ... or rather, if you are thinking of a file, there's probably a better nosql solution that has a better api that fits your data better than sticking it in a file.
    – user40980
    Feb 1 '14 at 0:15

It seems like your "storage system" whatever that is has a very simple abstraction interface. It essentially boils down to "here's an id, gimme data".

So you can easily define this interface and build your entire app on top of it. Behind the scenes you can continue using PostgreSQL like you do today. And if you want to experiment with flat file storage, it should take you more than 1 or 2 days to implement something very simple that reads/writes files straight on disk (my recommendation is to have 1-3 tiers of directories based on first portion of the ID, so you don't have too many files in one flat directory).

If you do that you can compare the performance straight up and see if it's good enough for you.

However, as Euphoric pointed out, most NOSQL stores where introduced and became popular for the very purpose that you are trying to accomplish. I'm not going to recommend a specific store as that's something for you to decide but some advantages they provide are:

  1. managing storage of huge amounts of small entities by buffering and doing writes in larger chunks. In my experience most file systems can work with very large numbers of files, but not in the most efficient way. For example, if you were to try to delete that many files from disk, unless you just reformat the whole disk, it could take multiple hours just for "rm -rf *" command.
  2. If and when you outgrow the limitations of a single physical box, a lot of NoSQL solutions allow you to scale horizontally which will give you a) more storage, b) redundancy of data so if one host goes down, your storage server is still online and c) faster query times since your clients can load balance where they get the info.

Another option to consider is that potentially your storage and your indexing does not have to be implemented in the same system. You could use a separate indexing product like Solr or Elasticsearch and store the actual data in a NoSQL DB (or a straight-up file system)

  • no need for any server side indexing. For the rest. I think it's the way to go. But all in all, my simplistic postgres schema, 1 table and 3 rows will do similar to any nosql anyway :) Jan 31 '14 at 23:33
  • @DavidHofmann: Last year our startup was deciding which DB to go with, we evaluated PostgreSQL, flat files, Solr, OrientDB, kyotocabiet... some-other-thing-I-forget. For us PostgreSQL turned out to be definitely NOT a way to go. So I'd say use what you have now, but if you put in the abstraction, it'll give you an option to play around with other storage engines. NoSQL products are very limited in their query capabilities, but they do have advantages in other areas. Although the impression I got is that a lot of them are geared towards smaller value sizes (not 5-10 MB)
    – DXM
    Feb 1 '14 at 0:02

Key-value NoSQL databases are practically made for this scenario. In your case, you are looking for something like secondary index on top of key-value store.

I'm not experienced in this field, so I can't tell you concrete implementation or tool to use. But I believe you can find something that can fit your needs.

  • Please provide more details. I know very well about k/v nosql stores. But this thing is to have a file that will be appended over time (concurrent writes must be sincronized). There will be many many different files. And over time reads to those files should be fast. I just want to know if plan text files will be the best or I can use another approach Jan 31 '14 at 18:26
  • Mongo can build indexes like this.
    – tom
    Jan 31 '14 at 18:28

After some research. I found that these data stores makes for the most part of use cases I have:

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

  • oh, there is also github.com/dain/leveldb Feb 4 '14 at 23:35
  • would you mind explaining more on what it does and why do you recommend it as answering the question asked? "Link-only answers" are not quite welcome at Stack Exchange
    – gnat
    Feb 5 '14 at 7:07
  • I added the edit. hope it helps. thanks for pointing out the problem. Feb 5 '14 at 11:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.