I'm going to preface this with pointing out that I only need somewhat persistent data. The purpose of this database platform would be to support statistical analysis in R. I usually build my tables from csv files I get from clients and query those tables to build flat files to dump into R. I can either import a .csv type file or run a query from R. So, essentially I'm performing a lot of inner and outer joins on the entire data set to get the resulting output I need. To date, my databases haven't exceeded 5-10GB. I may have projects in the near future that will be larger but I don't see anything that would exceed memory. I need maximum speed for a little while.

To admit a little guilt - I would be happy with sqlite if it supported full joins (without getting too hacky) and if it had good multi-core support. I like it's simplicity - it just doesn't perform well enough. Or I'm too ignorant.

Options I have explored are:

  • PostgreSQL in a ramdisk - unsure if a ramdisk would actually be necessary but I've seen a lot of info on the topic.

  • Using MySQL memory tables - I haven't looked to see if other databases have a similar feature. I'm sure they do.

  • McObject's eXtremeDB - It doesn't quite seem like a good fit for me. It's designed to be an embedded DB.

  • VoltDB - I was excited about this option until I read that they don't quite have outer joins and self joins working. Their SQL seems a little too limited.

I'm switching from my laptop (running ubuntu) which frequently overheats to an Amazon EC2 instance which I can scale up as much as I need. Thus the need for good multi-core support. I'll likely build my tables in an on-demand instance and do the heavy querying in spot instances. My laptop has already conditioned me for periodic shut-downs so, I'm not too worried about that. I've already built an instance with R and have been having fun playing with AWS for other projects over the last few months.

I'm not beholden to any specific database platform however, I have reached a point of information paralysis. Reasonable solutions and things to consider will be very helpful. I'm not looking for a step-by-step how to - that's what Google and the rest of stack exchange is for. I've also been avoiding Amazon's RDC service for this. I'm not exactly sure why - probably so I can use spot instances.

I'm also open to the idea that I'm looking at my problem all wrong. Should I abandon SQL all together?

  • 1
    Postgres in a ramdisk could even be slower. Your data is in the ramdisk (RAM) and when you query this data it is also in OS memory buffer (RAM). I don't think this would make things faster. – edze May 30 '13 at 20:42
  • I'll definitely keep an eye on memory usage while I play around with this. It didn't dawn on me that the data might be duplicated in memory. – Leif Hanson Jun 12 '13 at 0:15
  • My experience with Postgres and RAMDISK are similar to @edze. Ramdisk does not make queries faster. I also do not think using standard databases (PostgreSQL) is an issue. If you increase RAM available to Postgres then queries are fast if you set indexes on the foreign keys. Postgres is usually slow on huge updates but COPY for insertion and SELECT are super fast. – Alexandros Jan 22 '14 at 4:21

Your Answer

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

Browse other questions tagged or ask your own question.