I'm struggling to find a solution (preferably DBaaS) that I can rely on for storing and querying some 300M rows of data (roughly 100GB).

The data in question is pretty much numeric. There is also one "description" column that I would want to perform full-text search on. There are couple of "category" columns used for filtering as well. I also want to filter/order search results in many ways (10+ different indexes).

There is no need for doing complex joins since the data is pretty much denormalized. The data is updated heavily: some 50M of records are being replaced every day.

I've first tried with DynamoDB, but it can support only up to 5 indexes, and is not capable of doing full-text search at reasonable speed. I've also considered google's BigQuery, but it is designed for "append-only" data. I'm now considering Redshift, but I'm not sure how it will be able to handle such large number of daily updates.

Any advice would be appreciated!

  • 300M rows/ 100GB is not huge by today's standards. We have many DBs of this size on SQL Server, but that's a licensed product, of course. Never versions have "updatable" columnstore indexes which may match your needs. – Michael Green Jun 12 '14 at 11:59
  • i agree that it is not huge, but having 50M UPDATE statements per day would pretty much crash any rdbms on single server.. – Milovan Zogovic Jun 12 '14 at 12:15
  • Have you considered SQL Server on Azure? You can download the performance guidance whitepaper at msdn.microsoft.com/en-us/library/dn248436.aspx, which should be enough to determine if it meets your needs. – dartonw Jun 13 '14 at 4:45

I ended up storing data in DynamoDB and doing daily sync with Redshift. I've tried Redshift with 600M sample data on 4 node cluster and it runs extremely fast. It is exactly what i need.

  • I am curious, how do you handle your daily 50M record updates ? Do you do UNLOAD / LOAD queries, or directly via sql UPDATE or INSERT queries ? Or some other method ? – Benjamin Crouzier Apr 16 '15 at 9:07
  • I'm storing records in AWS DynamoDB. It is endlessly scalable so you can perform as many updates as you want per second. – Milovan Zogovic Apr 16 '15 at 16:07

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.