We are building a SaaS backend for restaurants using Rails. We integrate directly with POS, so each POS keeps sending customer orders that we store for later processing. we have this POS integration going at about 1,000 locations which send us about 3 Million individual customer orders on monthly basis.

for this write-heavy app, we store all orders in redis which is working beautifully. We are growing at incredible pace, we keep adding new restaurants with hundreds of locations that keep sending us crazy amount of data. Except there is one problem -- redis keeps running out of memory every month! As, everything which doesn't have to be in memory is in memory.

This is why we contemplating to switch to mysql. As we really don't need to keep all data in memory. here are we numbers of current redis database:

      used_memory_human:39.83G 
      dbsize: 34706870

Here is what we store in redis as Hash:

      id - integer
      location_id - integer
      stored_at  - timestamp
      token - string
      transaction_no - integer
      menu_items - string(comma seprated list of all menu items that customer ordered along with their price & Qty)
      order_amount - decimal
      order_subtotal_amount - decimal
      order_amount_payable - decimal
      order_datetime - timestamp
      employee_id - integer
      employee_name - string
      pos_type - string
      post_version - string
      restaurant_id - integer

So, looking for some advice on:

1. moving from redis to mysql is good idea? how will it effect us in long run as we will need to keep updating our indexes & partition scheme to cater to huge demand.

2. What other databases(relational or non-relational) would be suited for this use case than redis?

3. Or we are all wrong, as redis is made for storing this type of data. so, we just keep using redis & upgrading our machines every month?

user:	ayushghosh
created:	October 19, 2014
karma:	1