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.

  • 1
    What indexes do you have? What is your current average response time and what is your target response time? Can you share the query (maybe we can help you speed that up)? – Erik Aug 30 '16 at 0:03
  • 1
    You need Summary Tables. – Rick James Aug 30 '16 at 4:39

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.