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?
First of all, 3 million rows a month is not that much from a database point of view - if evenly distributed (though I'm sure it's not), it would be about 1 per second. (It is impressive to me from a business point of view though). You don't need Redis's throughput, and you are hitting Redis's limits with respect to memory.
1) This is _exactly_ the use case which relational databases were built and optimized for (fixed schema, OLTP, presumably some analytic queries). You might want to normalize the data a little (e.g. the menu_items, employee_name, pos_type & pos_version), but you don't have to - though this would enable faster & richer querying.
On your schema changes: I bet you'll be much happier using a relational database which takes care of this stuff for you, then you will be implementing it yourself in Redis. You probably won't need partitioning either. If you do end up needing partitioning, I would guess your DB is "trivial" to partition by restaurant.
I suspect you'll also find that a relational database stores the data more efficiently, particularly if you normalize a few things. Your data will probably fit into RAM again, but a relational database can cope even when it doesn't.
2) Any relational database would be a great match for this, and would allow rich querying (e.g. by employee, by date, by restaurant). My personal bias is to prefer Postgres.
3) I don't see what Redis is getting you here. It doesn't support rich querying, is memory bound, and you don't need the throughput it promises.