I'm in the process of rebuilding a MVP after having successfully proven it's place in the market. It's a small BI web app that collects POS sales transaction from shops and provides users with interesting sales data - such as sales trends, revenue forecasts and projections etc.
We started off with a MySQL database and only a few store, but now that we've grown to past 25 stores, our transactions table in the database has grown to 20+ million rows and we can see quite a bit of performance decrease - especially when grouping sales by store, month and SKU. With another 50+ stores due to onboard soon, we're soon going to be hitting 100s of millions of records in that table so are looking at other potential database/storage alternatives.
We have thought about utilising a time series database such as influxdb, or even postgres.
Any help or advice on choosing a good database/storage engine for large volumes of sales transactions would be helpful?
Note: Our current DB is a "db.r3.large" Amazon RDS - 2 cpus and 15Gb memory.
